Universal Financial Reporting Engine

Monthly financial reporting in smaller finance teams is mostly the same work repeated: export a table from the accounting system, rebuild the same pivots, find what moved, explain it and paste charts into a document. The tools that automate it usually need to be told, per customer, which column is the date, which is the account and which is the amount.

I wanted to see how much of that could be done with no configuration at all. The result is a Python engine that takes a CSV or Excel file it has never seen and produces a full variance report.

The first version only worked on one dataset

The prototype analysed product performance on one fixed dataset with hard-coded column names. Everything downstream, the variance maths, the charts and the report, assumed that shape. The second version, the “universal” one, came from removing that assumption.

Turn every table into one shape

The core idea is a canonical format. Whatever the input looks like, it is melted into one long table with four columns: period, entity, metric name and metric value. Entities with several dimensions, such as an account within a category within a region, become one composite key. Once data is in this shape, nothing downstream ever refers to an input column by name.

1. 1Detect schemaScore columns as time, entity, measure or text
2. 2CanonicaliseMelt to period, entity, metric, value
3. 3Classify metricsFlow, level or ratio; price, volume, discount
4. 4VariancePeriod-over-period change, price and volume split
5. 5ChartsTrends, top movers, distribution, heatmaps
6. 6ReportTemplate narrative and a ReportLab PDF

Detecting the schema with rules

Schema detection is a scoring heuristic, not a model. A column whose name contains “date”, “month” or “period” scores highest as the time axis, then columns with a date type, then columns whose first values parse as dates. Columns named like “category”, “account” or “region”, or text columns with few distinct values, become entities. Numeric columns become measures, except integer columns where almost every value is unique, which are probably IDs.

Metrics are then classified by name: flows such as revenue, levels such as balances, and ratios, plus a driver category of price, volume, quality or discount. Revenue-like flows are marked as decomposable.

Variance, and a simplified price and volume split

For each metric the engine compares each period with the one before it, in absolute and percentage terms, per entity. For decomposable metrics it splits the change into a price effect, a volume effect and their interaction, using the first price-like and volume-like metrics it finds as proxies. That is a simplification, and the code says so.

What the sample run exposed

The repository includes the output of a run on a 100,000-row accounting file. It worked end to end, producing 300,000 canonical rows, 3,438 variance records across 1,107 entities, six charts and a seven-page PDF. Reading that report carefully also shows exactly where “schema-agnostic” stops being true.

• An ID was treated as a metric. A reference-number column passed the ID filter, so it appeared as the top mover with a 230 percent increase.
• Daily dates were used as periods. With no rule for reporting grain, “period over period” meant consecutive days, not months.
• Transactions were not summed. Taking the first row per entity and period is fine for pre-aggregated data and wrong for a transaction ledger.
• Ranking ignored sign. The “largest increases” list could include negative changes when few records existed in the latest period.
• There were no accounting semantics. Debits and credits were treated as generic levels.

What I’d change

The next version would add those guards: stricter ID detection, period bucketing to months or quarters, aggregation rules per metric type, sign-aware rankings and debit and credit handling. With those in place, a model writing commentary on top of the computed figures becomes much safer, because the numbers it describes are already right.