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 an allocation 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 allocation 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. Check every range for sign splits before trusting a prefix rule

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:

The mechanical test: within a candidate range, list each account's balance. If the balances straddle zero and the target statement routes the two sides to different lines, stop — that range needs per-account (or per-side) rules immediately, not as a fallback after a bad first run.

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

The allocation 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 allocation 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 allocation 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.

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 Mapping codes — Allocation tables. For transfer modes, preview and revert, see Populating cubes — Links & integrations.