Build the year's workbook

The Excel workbook is the source of truth for the year's bookkeeping. Everything downstream — PDF reports, statutory annual report, ERP push — derives from it.

This skill builds regnskab{YYYY}/{YYYY} {Company}-vN.xlsx. Use the xlsx plugin skill for the actual workbook construction.

Versioning

• First iteration of a year: write -v1.xlsx.
• Each subsequent revision (after user feedback or correction): copy the latest version to -v{N+1}.xlsx and edit there. Never edit a prior version in place.
• The highest N is always the active workbook.

This way the user can see exactly what changed between iterations.

Mandatory pre-reads

1. settings/business.md — company name, registration number, fiscal year.
2. countries/{country}/rules.md — posting rules. Section 1–3 lists which accounts are in active use; section 4 is the line-by-line rules; section 5 is the workbook layout.
3. countries/{country}/chart_of_accounts.md — chart structure and account dimensions.
4. regnskab{YYYY}/note{YYYY}.md — year-specific adjustments.
5. The previous year's workbook (highest -vN.xlsx) — opening balances come from its closing balances.

Workbook layout

The exact sheet list comes from countries/{country}/rules.md § 5. For DK that is:

1. Saldobalance — kontonr., kontonavn, primo, debet, kredit, ultimo.
2. Bogføring (or Journal) — Bilag, Dato, Tekst, Modkonto, Debet, Kredit, Konto.
3. Moms — empty if VAT-exempt.
4. Notes — note{YYYY}.md content + følgenoter.
5. Recon {Bank} — one per external account.
6. Skatteopgørelse — bridge from accounting to taxable result.
7. Årsregnskab — collapsed P&L and balance sheet.

The scripts/import_xlsx_to_sqlite.py reader requires at minimum:

• A sheet called Journal (or Bogføring — both names are accepted) with these column headers in row 1: Bilag, Dato, Tekst, Konto nr., Konto navn, Debet, Kredit, Note, Bilag-fil.
• A sheet called Saldobalance with: Type, Konto nr., Konto navn, Primo …, Debet …, Kredit …, Ultimo ….

Other sheets are not required by the scripts but are required by the country's rules.md.

Posting flow

1. Open the previous year's workbook. Read closing balances from the Saldobalance sheet.
2. Create the new workbook from templates/workbook_template.xlsx (if present) or build sheets from scratch.
3. Write opening balances on Saldobalance!Primo.
4. For every line in every input markdown, apply the rules in rules.md § 4 to determine debit + credit accounts. If a transaction does not fit any rule, halt and ask the user.
5. Append the lines to the Bogføring sheet with sequential Bilag IDs grouped by source document.
6. Build the year-end adjustments listed in note{YYYY}.md:

• Year-end mark-to-market on portfolios.
• FX translation on foreign-currency cash.
• Crypto valuation.
• Proposed dividend → equity reservation.
• Tax provision (compute on Skatteopgørelse sheet first, then book on Bogføring).

1. Update Saldobalance!Debet / Kredit / Ultimo from the journal sums.
2. Build / update the Recon {Bank} sheets — each must net to zero.
3. Build the Årsregnskab sheet: collapse dimensions, group by P&L vs. balance sheet, compute prior-year comparatives.

Posting rules — non-negotiable

From countries/{country}/rules.md § 4. The most common ones across countries:

• Voucher on every line. No journal entry without a Bilag ID and a reference in Bilag-fil (unless system-generated — see below).
• Double-entry. Debit equals credit per entry; trial balance nets to zero.
• Securities at cost on the balance sheet, with year-end mark-to-market through P&L.
• Use only accounts allowed in `rules.md`. If a transaction does not fit, halt and ask rather than guess.
• Numbers and dates use the country's required format (e.g. kr. 10.000,00 and ddmmyyyy for DK).

System-generated entries (no Bilag-fil required, but flagged as is_system=1 for the bilagsoversigt):

• Primosaldi (opening balances).
• Year-end mark-to-market.
• FX translation at year-end.
• Tax provision.
• Resultatdisponering (year-end appropriation).

Output

regnskab{YYYY}/{YYYY} {Company}-vN.xlsx — the workbook itself.

After building, always invoke scripts/import_xlsx_to_sqlite.py {YYYY} to update regnskab{YYYY}/ledger.db. The reports skill needs that database.

Verification (before handing off to generate-reports)

• [ ] Saldobalance nets to zero (Σdebet = Σkredit and primo + debet − kredit = ultimo per account).
• [ ] Every journal line has a Bilag ID.
• [ ] Every non-system journal line has a Bilag-fil reference that points to a real file in input/.
• [ ] Each Recon {Bank} sheet nets to zero.
• [ ] Tax provision was computed using the country's current rate.
• [ ] Proposed dividend from note{YYYY}.md is reserved on the correct equity account.