Self-auditing household budget workbook
A spreadsheet that builds, checks and repairs itself, logs scheduled debits on its own and says how much to move where each pay cycle, with no external service.
Problem
A household budget spreadsheet with recurring, quarterly and irregular bills across several accounts had formulas that drifted, broke at year end and needed figures typed in twice.
Goal
A spreadsheet that builds itself, checks and repairs itself, logs scheduled debits on its own and says how much to move where each pay cycle, with no external service.
My role
Sole designer and developer, including the verification harness used to test the repairs.
Solution
One script bound to a spreadsheet that scaffolds a complete multi-sheet budgeting system and adds a custom menu of maintenance tools: month rollover, a self-healing audit, a sync against a canonical bill list, income fill from the transaction log, a funding helper and a daily automatic-debit logger.
How it works
A setup routine scaffolds a set of linked sheets: settings, a transaction ledger, per-pay-cycle and monthly views, balances, a debt schedule and a dashboard.
A daily trigger logs each scheduled debit on its day, guarded by a last-run date so it fires at most once per month per item.
The audit checks that every sheet, formula and validation exists, repairs formula drift (for example updating formula references at the year rollover) without clobbering references the spreadsheet legitimately rewrote, and reports what it fixed separately from what needs a person.
The list sync plans additions and changes against the canonical bill list, shows a summary first and never deletes a row it does not recognise.
The funding helper computes each cycle's transfers, accounting for transfer fees and showing its arithmetic.
Technical challenges
Idempotent scheduled writes with no database.
Year-rollover bugs hidden inside formulas.
Comparing month-aware bills by formula rather than by displayed value, so they do not look changed every month.
Sync semantics that never destroy unknown data.
Testing spreadsheet automation at all: a harness runs the real audit in a sandbox against a fake spreadsheet service with the clock pinned, asserting repair, that a second run changes nothing, and report text, plus mutation testing to prove the checks can fail.
Capabilities
- One-click workbook scaffolding
- Per-pay-cycle budgeting
- Quarterly and month-specific bill scheduling
- Automatic debit logging
- Debt schedule and dashboard
- Self-audit with automatic repair
- Safe list sync
- Transfer planning with fee gross-up
Technology
Impact
A fragile, hand-maintained spreadsheet became one that rebuilds, repairs and updates itself, double entry disappeared, and each pay cycle's transfers became a computed answer. No metrics are claimed.
Skills demonstrated
- Spreadsheet automation engineering
- Idempotent job design
- Defensive data syncing
- Testing hard-to-test platforms
- Personal-finance domain modelling