A ledger that keeps itself current
Year:
2026
Service:
Ready-made Solutions
Industry:
Fintech
Team:
One builder with an AI assistant
A scheduled daily sync moves transactions from a personal finance platform API into a controlled spreadsheet ledger. Idempotent runs, three-layer de-duplication, a status page, 71 unit tests and a documented security audit.
Introduction
A finance owner wanted the books current every morning without touching a spreadsheet. The budget itself was not the problem: it lived in a workbook with its own formulas, pivots and charts, refined over more than five years. The problem was the daily copy work between the finance app and that workbook, and the quiet errors that come with it.
Budget Autopilot is the small system built to remove that work. A scheduled job reads new transactions through a personal finance platform's read-only API and appends them to a dedicated sheet of the ledger, leaving everything else in the workbook untouched. It is a modest scope done to production standards: idempotent, tested, audited and observable. It shows how we treat even a small automation.
Challenge
Daily finance data looks simple until it has to be trusted.
Duplicates and late edits. Transactions arrive late, get edited or are fetched twice. A naive import corrupts totals silently.
A workbook that must not break. Years of formulas and charts depend on the existing sheets. The automation may add rows in one place and change nothing else.
Sensitive data. Financial records and an API token are involved. Routing them through an additional cloud service was ruled out as a design decision.
Silent failure. A job that stops on day twelve is worse than no job, because the owner keeps trusting stale numbers.
A method that lived in habit. The owner's budgeting rules - what counts as a need, how refunds, transfers between own accounts and foreign currency are treated - existed only as practice. An AI assistant could not apply them consistently without a written version.
The brief was therefore not "write a script". It was: make the ledger current each morning, prove each run, and keep the data on the owner's side.
Solution
The design turns on where the sync runs, how a run can be repeated and who classifies.
Run locally, not in a cloud service. The sync runs natively on the owner's machine under the operating system scheduler, so it adds no third party between the finance platform and the owner's own storage. API access is read-only: the tool cannot move money or edit transactions.
Make every run repeatable. A run can be repeated at any time without creating duplicates. Record IDs are checked in three layers: the state file, the ledger sheet itself and the current run. Each run re-fetches a seven-day overlap to catch late or edited entries.
Separate data delivery from judgement. The script delivers raw rows. Classification stays a decision taken under written rules, captured in an AI skill.
What was built:
Sync core in Python (requests, openpyxl): paging, retries with a capped wait, atomic state writes, and distinct exit codes for configuration, authentication and runtime errors.
Status page - one self-contained HTML file regenerated after every run, including failed runs, showing data freshness and the last ten runs.
Health-check command with six checks, from token expiry to the scheduler job and API reachability.
Read-only ledger audit covering schema, duplicate IDs and date range.
Budgeting skill - the owner's method written down as 22 budget buckets, derived from 85 monthly sheets, so an AI assistant works with the ledger consistently.
Test suite - 71 unit tests in pytest with all HTTP mocked.
The tool was built with an AI assistant and then audited with it against a threat model covering confidentiality, integrity, availability and accountability.
Result
What follows is verified in the code and its documentation, plus one statement from the owner.
Daily operation - the sync is scheduled once a day, in the morning; the owner confirms it runs daily in production.
71 unit tests cover normalisation, paging, authentication errors, state handling, ledger writes, log redaction and the status page.
Security audit: 11 findings, 8 fixed in code, one requiring an action by the owner and two informational. Fixes include secret redaction in logs, owner-only file permissions and atomic state writes. Measures consciously not taken are listed with reasons.
Evidence for every run - status page, run history and log, including failures.
Automatic categorisation and a finance dashboard are roadmap items, not part of this delivery. The owner holds everything: code, tests, audit report, roadmap and the budgeting skill, with the data kept on the owner's own machine and storage. The same pattern fits any ledger that must be current, provable and private.
