The monthly close is the part of budget work nobody writes about: the checklist of things that have to be true before numbers get published — data is fresh, exceptions are cleared, variances are flagged, balances foot. I built it as software: four template workbooks, six VBA macros, and Python twins of the two macros that do actual math.
The whole thing lives in budget-workbench, and every sample figure in it is synthetic — generated from one seed for a fictional agency, no real TAS codes, no internal data.
The four workbooks
| Workbook | Sheets | Job |
|---|---|---|
budget_formulation | Formulation · Assumptions · CrossChecks | driver-based formulation (lines × object classes) with stated assumptions |
execution_tracker | Execution · ByPeriod | SF-133-style grid: budget authority, obligated, outlay, unliquidated balance, pacing % |
variance_scorecard | Scorecard · Variance | KPI scorecard plus flagged variance detail |
reconciliation_workbook | Data · Exceptions · Summary | the two ledger blocks and the reconciliation output |
The sheet layouts are contracts: the Data sheet holds marker cells
(A, B; BUDGET, ACTUALS) with headers on the marker row, and the macros
find their blocks by those markers. Rename a column without updating the
macro and the macro reads the wrong thing — so the contract is documented in
the build script’s docstring, on both sides.
The six macros
| Macro | What it does |
|---|---|
RunReconciliation | outer-joins ledger A and B on (fund control point, object class), bands the difference against a $5,000 tolerance, writes Exceptions + Summary |
BuildVarianceReport | budget vs actuals by line × object class, $ and % variance, amber above 10%, red above 25% |
MonthlyCloseChecklist | runs the five close checks and appends a dated pass/fail entry to the CloseLog tab |
EnforceLedgerFormat | the house format on any sheet: bold ledger-green headers on a double gold rule, tabular currency, frozen top row |
ExportScorecardImage | exports the scorecard chart as a PNG for the close package |
LoadFileD_CSV | ingests a USAspending File D CSV — the bridge from the public pipeline into Excel |
The close checklist is five named checks, each an auditable constant:
- data date present and not older than 35 days
- no
MATERIALorMISSINGrows open on Exceptions - no unflagged variance above 25%
- execution pacing (obligated ÷ budget authority) within sanity bounds
- the identity unliquidated balance = obligated − outlayed, within a dollar
Why the Python twins exist
Alt+F8 is not reproducible. If the only implementation of the reconciliation
logic lives in a VBA module, then the number in the Exceptions tab is
trust-me. So the two computational macros have Python twins —
recon.py and variance.py — that mirror the VBA one-for-one: same join,
same $5,000 tolerance banding, same 10%/25% flags. A reviewer can diff the two
implementations line for line, and I can run the whole close on a laptop with
no Excel at all:
python3 python/make_data.py # regenerate all synthetic data (one seed)
python3 python/build_templates.py # rebuild the four workbooks
python3 python/recon.py # → reconciliation_report.xlsx
python3 python/variance.py # → variance_report.xlsx + waterfall.png
The committed workbooks/output/ files are the twins’ reference outputs —
what the macros should produce, checked into the repo.
The synthetic-data contract
make_data.py uses one seed (20260911) and generates everything: two
fund-ledger files with deliberate matches, sub-tolerance noise, material
breaks, and one-sided rows; budget and actuals by line × object class
(standard federal object classes, 11.1 through 95.0); and a File D-shaped
sample for the ingest macro. Re-running it produces byte-identical CSVs, so
every workbook and every report in the repo traces back to one reproducible
dataset — which is also the whole CUI story: there’s nothing internal to
leak, because nothing real was ever in it.
The next post moves to the mechanics: what a sequester, a CR, and a lapse actually do to the money.
— Joanna