Every month I’d spend hours copying transaction details from bank alert emails into a spreadsheet. Multiple credit cards, a savings account - each with its own email format. I finally automated the entire thing using Google’s free tools.
What It Does
The system has two components working together:
Component 1 - The Email Parser runs in the background every hour. It scans Gmail for new transaction alert emails, sends them to Google’s Gemini AI to extract the details (amount, merchant, category, date, card), and logs everything into Google Sheets - organized into monthly tabs. It sleeps between 11 PM and 6 AM since there are rarely transactions then.
Component 2 - The Insights Dashboard is a web app that reads from the same sheet. It shows spending summaries, daily trends, category breakdowns, top merchants, and card-wise analysis. It also has an AI-powered insights panel that gives a financial health score, month-end spending projections, saving opportunities, and anomaly detection - all generated by Gemini AI on demand.
How It Works
Every hour, the parser checks Gmail for unprocessed bank alert emails using a label-based filter. It strips the HTML from those emails, batches all the plain text bodies into a single Gemini AI prompt, and gets back a structured JSON array with every transaction parsed. The parsed data is deduplicated against what’s already in the sheet, then written in one batch call. The emails are labeled as processed so they’re never picked up again.
The dashboard is a separate web app. You open it in a browser, authenticate with an API key, and see your spending data visualized - charts, tables, filters. You can switch between calendar month view and billing cycle view (20th to 20th). The AI insights button triggers a Gemini analysis that gives you actionable recommendations based on your actual spending patterns.
What Gets Extracted
From each transaction email, the AI extracts: transaction date, card or account identifier, debit/credit type, amount, merchant name, spending category (Food, Fuel, Shopping, Bills, etc.), payment channel (POS, UPI, Online, ATM), available balance, and reference number.
All from plain email alerts - no bank API access, no SMS parsing, no third-party app.
Smart Design Choices
One AI call per batch - Instead of calling Gemini for each email separately, all emails are combined into a single prompt. 20 emails processed in one API call.
Three-layer duplicate prevention - Gmail labels prevent re-fetching, Message ID checks prevent re-writing, and the write-then-label ordering makes the entire pipeline safe to re-run anytime.
Fully config-driven - API keys, bank email addresses, batch size, AI model - everything is stored in configuration. Adding a new bank or switching AI models requires no code changes.
Zero cost - The entire system runs on Google Apps Script within free tier limits. No servers, no subscriptions, no cloud bills.
The Dashboard Highlights
- Summary cards with total spend, daily average, and peak spending day
- Daily spending bar chart and category doughnut chart
- Card-wise breakdown tiles
- Top merchants ranked by spend
- Calendar month and billing cycle (20th-20th) toggle
- AI-powered health score, predictions, and saving recommendations
Results
In the first month, the system processed over 120 transactions across all accounts with zero duplicates and zero manual effort. The AI correctly categorized food delivery, fuel, shopping, and utility payments without any manual rules or regex patterns.
What’s Next
- Monthly spending summary auto-generation
- Budget alerts via Telegram when daily spend exceeds a threshold
- AI-powered overspend warnings
- Multi-year automatic sheet management
Built With
Google Apps Script, Gmail API, Google Sheets, Gemini AI (2.5 Flash for parsing, 3 Flash for insights), Chart.js, HTML/CSS/JS - all running within Google’s free tier.