Engineering a budget-versus-actual workbook and a budget research assistant
Did we budget for this? was a question that kept getting answered from memory, against a general ledger export of roughly 25,000 transactions and several competing versions of the same detail sheet. The fix wasn't just a better workbook. It was a queryable record that could name the exact cell and the exact email thread behind an answer, and say plainly when it couldn't.
Category
Financial reporting and evidence-based Q&A.
What I was trying to accomplish
A workbook that compares budget to actual in a way you can drill into, plus a repeatable way to answer did-we-budget-for-this questions with evidence instead of memory.
The data challenges
An approved budget, a roughly 25,000-transaction general ledger export in a legacy spreadsheet format, and several departmental versions of the same detail sheet that had to be reconciled to confirm which one was actually final.
The result
A workbook built with drill-down grouping and links from each summary row to its transactions, plus a project that could answer specific budget questions citing the exact cell and the exact email thread, and say plainly when the paper trail stopped rather than guessing. A recalculation step that silently broke internal links turned into a three-stage build to fix it for good.
Technical details
Tools: claude.ai Projects, Claude Cowork, Python (openpyxl, xlrd), LibreOffice (headless recalculation), Microsoft 365 email search
I owned a set of lines in the firm's annual operating budget and regularly fielded questions like did we budget for this, and what was decided. The answers were spread across an approved budget file, several departmental versions, a general ledger export, and months of email.
For the workbook, Claude built it in Python: a summary tab, a full budget-versus-actual tab with pro-rated budget and heat-map variance formatting, a view of my own lines, and a raw transaction tab, with grouping for drill-down and links from each summary row to its transactions. For questions, I used a claude.ai Project loaded with the budget workbooks plus email search, so an answer could cite the exact cell and the exact email thread.
The most interesting challenge was technical: the step that recalculates formulas in a headless environment silently broke the internal links. The fix was a three-stage build, create the workbook, recalculate, then inject the links by editing the worksheet XML directly. Other challenges were ordinary but easy to get wrong: the obvious search term appeared in none of nine budget files, and several versions of the same detail sheet existed, so Claude confirmed which one was final by checking that its total tied exactly to the approved budget line.
Pull up a chair.
Let's talk through where things stand, and where they could go.