Four Financial Statements, Four Export Formats
An accounting report is a tree pretending to be a table. Getting one onto a screen is straightforward. Getting the same one into CSV, Excel and PDF without the three disagreeing is the actual work, and it is where this codebase and I both slipped.
- Role
- Backend developer
- Where
- Vertex Special Technologies
- When
- 2025
What it needed to do
An accounting platform has to produce the four statements every bookkeeper expects, for a chosen business and a chosen date range, and let people take them away.
- General LedgerEvery transaction, per account, with opening and closing balances
- Trial BalanceDebit and credit per account, as of a date
- Profit & LossIncome and expense over a period
- Balance SheetAssets, liabilities and equity, at a point in time
Taking it away means four things, not one. The application reads JSON. An accountant wants the Excel file, because the first thing they do is add a column of their own. A client gets sent the PDF. And something upstream, usually a spreadsheet nobody admits to, wants the CSV.
Why this is not serialisation
A chart of accounts is a hierarchy. An account has a parent, a group's figure is its own plus everything beneath it, and every group needs a Total row underneath its children. That is the report. It is not a list of rows that happens to be indented.
So the awkward part is not writing a file. It is that CSV has no nesting, Excel has indentation but not structure, and PDF has whatever HTML you give it. Three formats with three different amounts of expressiveness, and one tree that has to survive all three and still add up to the same numbers.
Totals are rows, not metadata
When the tree is built, each group gets a synthetic “Total {account}” child appended after its real children. A total is a line an accountant reads in sequence, so it has to exist as a row in the output rather than as a property of the group.
Depth has to be carried, not inferred
Once flattened, a row has no parent to ask. So each row carries a path built from the names above it, and depth is read back out of that path. Crude, and it means one representation works for every renderer.
One pipeline, then three renderers
Every export runs the same four steps before it touches a file format. Query, build the tree, group by account type, flatten. Only then does the format matter.
- 1One SQL query per report, parameterised by business and date range, summing journal entry lines per account.
- 2The flat rows are folded into a tree by parent, and every group rolls up its children and gains a Total row.
- 3Top-level accounts are grouped under their account type, which is the section heading a reader expects.
- 4The tree is flattened back into a list, each row carrying its path, whether it has children, and whether it is a closing total.
The point of steps two and four is that a tree is built and then deliberately thrown away again. What survives is a flat list where the structure has been reduced to three fields. That list is the only thing the renderers see, which is the reason three formats can produce the same numbers at all.
What each format actually needs
JSON
The API response itself. The tree stays a tree, because the front end draws it as one: rows expand, groups collapse, and the client needs the nesting intact to do that.
CSV
An array of string arrays through CsvHelper. No styling exists in the format, so depth has to be encoded some other way: section headings go uppercase on their own row, and everything else is flat.
Excel
EPPlus. Amounts are written as numbers rather than formatted strings, because the first thing an accountant does with the file is sum a column. Depth becomes cell indentation, groups and totals get colour bands, section headings are merged rows.
The report is built as HTML and posted to a headless-browser service that returns the bytes. Header and footer templates carry the company name, period, and page number of total.
The tradeoff: PDF over HTTP
Generating the PDF somewhere else means a financial report can now fail because a different service is having a bad day.
The PDF is not drawn in .NET. The report is rendered to HTML, posted to a small service that runs a headless browser, and the bytes come back. That is a network hop and a second thing to deploy, inside a request a user is waiting on.
It was still the right call. The layout language already existed. Column widths, zebra striping, indentation, a header repeating on every page, a footer counting pages, the tenant's logo: all of that is a stylesheet, and none of it is a stylesheet in a drawing API. The alternative was either a paid PDF library or hand-positioning cells and rediscovering pagination from first principles.
The PDF looks like the report
Because it is the report. The same markup and the same CSS produce the screen view and the printed one, so the two cannot drift apart in the way a separately implemented layout always eventually does.
Numbers, not strings, in Excel
Excel is the one format where the file is not the end of the process. Writing an amount as pre-formatted text produces a spreadsheet that looks right and cannot be summed, which is worse than one that looks plain.
Where it drifted
Four reports and three file formats is twelve renderers. Each one lives as a private method on the service that owns its report, and each report grew its own flatten step, its own CSV builder, its own worksheet builder and its own HTML builder. Nothing is shared between them but the interface that writes the bytes at the very end.
That is fine on the day it is written, and it is a slow leak afterwards. Reading back through it, the same problem shows up in three different disguises.
The same column, spelled differently
The General Ledger lets the caller choose columns. The Excel and PDF renderers know them by their printed abbreviations; the CSV renderer knows them by their long names, and offers one the other two do not have. So the same request produces a different set of columns depending on which format is asked for, and a name valid in one path is not recognised in another.
A column order that disagrees with itself
In one report the CSV writes its two amount columns in the opposite order to the header row above them, and to the Excel and PDF versions of the same report. Two lines of code in two different methods, which is precisely the bug that duplication is for.
A constant that is not a constant
“Accounting Basis: Cash” is written into the output in nine separate places, once per renderer that prints a header block. It is correct in all nine. It is also a business rule stored as nine string literals, and the day it becomes a setting is the day one of them is missed.
None of these are hard problems. They are all the same problem: there is no single definition of what a row of a financial report is, so twelve places each hold their own opinion, and the opinions have quietly stopped matching.
What I would do differently
One row model, four writers
A report is a title, a header block, a set of columns, and rows that know their depth and their kind. Every one of the four statements fits that, and every format is then a function from it to bytes. Twelve renderers collapse into four writers and four queries, and a column can only be defined once.
Test the formats against each other
Every defect above would have been caught by one test: run a report, export it three ways, assert the three carry the same columns in the same order with the same totals. The existing tests check that the service resolves and that bad input throws. Neither of those can notice two columns being swapped.