If you’re anything like me, you are someone that likes to optimize their lives with spreadsheets, automations, budgets, plans. No surprises (or as few as possible) is the idea. Ongoing optimization, deep understanding of the factors at play, then working the problem through in a way that is sustainable in the day-to-day. I also like to think of myself as someone who knows how to put together a well-crafted search engine query, or in these days, to even ask @grok to find something I’m looking for.

Why is it then, that there seems to be a complete gap in the consumer market for personal finance-oriented software products? Sure there are solutions like @ynab, @moneydance and @mint (RIP), but they weren’t a perfect fit for someone who likes to automate, integrate, and build on top of. There is also the added challenge in Canada at least, that our banking and financial services industries are woefully behind in terms of open access to customers’ own financial information. We are in the earliest of stages of making progress in this area, but I am doubtful that our Open Banking initiatives will result in a set of API endpoints offered by all institutions that can be used by customer and software developer alike.

Problem Statement

I’d like some software to track my day-to-day spending/budgeting, my ongoing savings goals/progress, my investments, my net worth, taxes, and to identify opportunities for improvement, and financial planning. A great deal of research has been done on all of these areas, and there are lots of excellent business-focused applications to support these activities, but nothing that really seems well-suited to personal use.

I would also like all of my transactions to be captured, classified, and factored in as automatically as possible. In a world of recurring daily investment contributions using fractional shares, or credit cards used to pay for $1 purchases, the sheer volume of transaction data one person generates in 2024 is prohibitively high for any solution which does not implement this. Trust me, I have tried. The reality, though, is that the exciting insights that can be derived through automation and modelling requires transaction-level data to be available somewhere in a standardized form.

The solutions that I have found to date that offer integrations with banks and financial services have offered imperfect page-scraping options that do not handle multi factor authentication well. The connections to these services often break, and sometimes the institution changes things so drastically that the platform will completely lose support.

My (Imperfect) Solution

As sad as it is to say, the most reliable way to collect transaction data across financial institutions has been transaction email alerts that these institutions natively offer. Combining this simple technology with Gmail and a little-known Google product called Google Apps Script, it is possible to detect and do something with incoming mail that matches a certain set of criteria.

Enter the good ol’ budget spreadsheet. It’s quite simple, one sheet for budget lines. Amount per year/month/day tracking income and expenses. Another sheet set up for transactions which have a column relating to a budget line. Not double-entry accounting, but with email transaction data being rock solid, I shouldn’t get too far off the mark. Besides, perfect is the enemy of the good when it comes to budgeting in my mind. An additional sheet contains a “configuration” defining different transaction alert emails I can expect to receive, and includes a before/after value for where to find the name of the vendor, and the price, along with the “from” email and subject line. Finally, in a nice added touch, another sheet marries vendor names with budget categories so I can automatically categorize those expenses.

The result (which, admittedly requires the ability to write Javascript), is a Google Sheet which tracks my budget, automatically captures and categorizes my expenses thereby keeping my “current” and “remaining” spend in each category up to date without any effort on my part.

If the vendors of personal finance software built their transaction gathering on this model, they might be far more successful than they have been to date, at least when it comes to Canadian financial institutions.

If you’re interested in learning more about how I set this up, getting some pointers on building it yourself, please don’t hesitate to reach out!