Case Study 01
Automating My Family Finance Report
Automated monthly finance report for a multi-bank household — parses statements, enriches transactions with AI, and delivers a Google Slides deck and email with a single command.
Automation // 2026
The What
A personal automation that runs once a month: drop all bank statements into a Google Drive inbox, run a single command, and receive a polished Google Slides deck plus an email summary covering the full household's finances across multiple European banks.
The parser reads PDFs, XLSX, and XLS files — one format per bank — validates them, calls Gemini to categorise every transaction and write a narrative, updates a Google Sheets history database, and writes report_data.json. A time-driven Apps Script trigger picks up that file, fills a slide template with the data, refreshes linked charts, sends the email, and archives everything.
Possible next steps are automating file ingestion using open banking APIs and adding an agent layer to answer natural-language questions.
Tools: Claude Code, Google AI Studio, Google Stitch
The Why
"The best financial system is the one you actually use."
Owning bank accounts across different countries makes it genuinely hard to get a consolidated picture of household finances. Updating a spreadsheet manually every month is the kind of task that starts disciplined and gradually stops happening. The goal here was a system that produces a shareable, visual summary with no manual data entry — something easy enough to actually run every month and presentable enough to share.
TECH STACK
Parser
Python 3.14+ with uv
AI Enrichment
Gemini API (gemini-3-flash-preview) for categorisation & narrative summary
Data Storage
Google Sheets API via service account
Report & Delivery
Google Apps Script — slide deck generation and email dispatch
Input Formats
PDF, XLSX, XLS (one per bank)
Key
Learnings
Gemini in Drive vs Gemini API
The first attempt used the Gemini API directly in Google AI Studio to read and summarise documents from a Drive folder. While Gemini is great at summarizing files in Drive, I failed to produce reliable structured extraction from PDFs using the API. Moving to a Python parser gave full control over the extraction logic and made the output schema predictable.
Polling Over Drive onChange Triggers
Apps Script's time-driven trigger polls the Inbox every 5 minutes rather than reacting to a Drive onChange event. onChange is unavailable for standalone scripts, so polling was the only viable option — and it keeps the architecture simple with no webhook plumbing.
Archiving report_data.json for Future Use
The structured JSON output of each parsing run is archived rather than deleted. This preserves a queryable history of every month's transactions — useful for future agent layers or manual audits without re-parsing the original statements.