Build financial statements from a ledger export

You have a ledger export — journal lines or a trial balance, each row carrying an account code and a debit/credit pair or a signed balance — and you want statement cubes: a balance sheet, a P&L. The mechanics are a Link through a mapping table, and they are the easy part.

The hard part is that every failure mode is silent. A code no rule matches is skipped without an error. A wrong sign flips a line. Two wrong lines can offset so the grand total still looks right. This guide is the method that catches all of that before any data lands in a cube. The running example is a French FEC (plan comptable général account classes), but the method applies to any chart of accounts.

1. Establish the ground truth before writing any rule

The export usually travels with its own statements — a Balance tab, a Bilan or P&L built by the accountant, a prior-year report. That document is the specification: it fixes the line labels, each line's account range, the presentation sign, and the expected value.

Before encoding a single mapping rule, reconstruct every target line directly from the trial balance — sum the account balances over the line's range — and check the result against the statement. Only when each line reproduces do you translate the ranges into rules. If no target statement exists, write down the expected per-line values first and have them confirmed. The first tie-out must happen outside the cube, not after the numbers are in it.

2. Route sign-straddling ranges with a split, not a hand-built list

Prefix rules (70* → Revenue) are the workhorse. But some ranges route by the sign of each account's balance, not by its code — and there a prefix rule is guaranteed wrong. Classic cases on a French balance sheet:

Don't try to catch these by listing which accounts happen to sit on which side this year and hand-coding a rule per account — a customer who flips from receivable to prepaid next month, or a new client's chart of accounts, breaks a hand-built list silently. Instead, give the mapping table's row for that range a credit branch: turn on Split by balance sign and set the Debit balance and Credit balance destinations. Each account's balance then picks its own branch every run, forever.

A split row only means anything against a stock — a bank account's split destination has to be decided by its closing balance, not by the sign of one journal line. So build the Bilan link as a Data-Table link in Closing balances mode, not Movements, with the Account column set to the ledger's account column (CompteNum in an FEC) and the Auxiliary column set to CompAuxNum. The auxiliary column matters: without it, one customer's credit note nets against another customer's receivable under the same 411000 account before the split ever sees a sign — exactly the compensation PCG art. 121-3 forbids. With it, each customer keeps their own balance and picks their own branch.

Opening balances have to already be in the source. A closing balance is a running total from the ledger's opening entries, so the à-nouveaux journal (AN) rows — or the ledger's full history — must be part of what the link reads. Unlike the P&L link, which filters the AN rows out (they're not a period movement), the Bilan link keeps them in: they're exactly what seeds the running total from zero. If the ledger legitimately has no opening entries — a continuous general ledger since inception — turn on Continuous ledger on the balance key instead of expecting the AN rows to appear.

One exercice per link run. The running total resets per accumulation group, so if the source mixes more than one fiscal year's periods into one group, the Bilan link fails outright rather than double-counting an opening balance on top of last year's closing balance. Map an Exercice column so each year gets its own group, or split the source by year — or, for a genuinely long first exercice, turn on Continuous ledger, which reads chronologically across years instead of resetting per period group. It needs the date column mapped to periods (not a period column), since only a date says which year a row belongs to.

Set closing aggregation on every Bilan line. A balance-sheet line has to aggregate the last period's balance over a quarter or a year, not sum twelve month-end balances into one. The wizard flags any mapped line that isn't set this way and offers a one-click Set closing aggregation fix — take it on every line the split (and every other Bilan line) writes to.

When something on the Bilan looks wrong, drill down into the cell: for a closing-balance link the drill-down explains the number by account and by branch — which accounts contributed, at what balance, and whether each landed on the debit or the credit branch.

3. Set the sign per destination line, never by a global convention

The mapping table's sign column is decided by how the destination line presents, not by a blanket "credit-normal → invert" table:

• Revenue (7*): invert, so credit balances display positive.
• Charges (6*): pass through (=), debit balances are already positive.
• Contra items keep their natural sign so they net against their line: rebates granted (709) invert into negative revenue; rebates obtained (609) pass through as a negative charge; a research tax credit (699) stays negative so the tax line adds back to net income; accumulated depreciation (28/29) passes through so it reduces gross assets.

Applying one sign rule uniformly to an account class is how a technically credit-normal contra account ends up increasing the line it should reduce.

4. Order the rules: specific before catch-all

Exact codes beat patterns, and among patterns the first listed wins — see mapping tables. That ordering is how you split one class across statement sections. Example: a P&L that routes 790000–791599 to operating income (reprises, transferts de charges) but 791600+ to financial income. The table lists the narrow patterns first — 7916*, 7917*, 7918*, 7919*, 792* … 799* — and the catch-all 79* last, so it only receives what the specific rules let through.

5. Prove coverage, then preview

A source code matching no rule is skipped without an error. So before the first run:

• Enumerate every in-scope prefix (classes 6–7 for a P&L, 1–5 for a balance sheet) and confirm each one hits some rule.
• Set the destination dimension on the mapping table, so destination codes that don't exist produce a warning instead of a silent skip.
• Preview the Link (preview_cube_link). The skip count must be explainable — out-of-scope classes plus zero-value rows, nothing else — and every previewed cell must equal the line value you reconstructed in step 1.

6. Tie out every line, not just the total

A matching grand total can hide two offsetting errors. Compare every statement line: input lines against the preview, subtotals against the cube's formula items. Only at zero variance on all lines do you run the Link. Then read the cube back (get_cube_data includes computed formulas) and repeat the comparison — this second pass verifies the formula wiring, not just the mapping.

7. Compute cross-statement ties — never retype them

Net income on the P&L must equal the result line inside equity on the balance sheet. Retyping it invites rounding drift: a hand-entered -7 382 497 against a computed -7 382 497.09 leaves the balance sheet nine cents out of balance — small, visible, and real. Compute the tie instead:

• If the balance-sheet dimension is free-form, use a formula item or a reference.
• If it is hierarchical — where formula items are not allowed — use a one-cell cube Link from the P&L's net-income item into the balance-sheet result item.

Where the model allows it, add a check row (Check = TotalAssets − TotalLiabilitiesAndEquity) so the tie is asserted continuously rather than inspected once. It has to be a formula item to assert anything, and something has to evaluate it — see Finding silent errors.

8. Re-running when the ledger changes

Keep the Links in replace mode so re-runs are idempotent — a replace Link clears the cells it no longer produces. And run in dependency order: the ledger → statement Links first, then the P&L → balance-sheet tie Link. A tie Link re-run before the P&L refresh carries the stale result forward.

For the matching mechanics — exact vs. pattern, case rules, signs — see Translating codes — Mapping tables. For transfer modes, preview and revert, see Populating cubes — Links & integrations.