loading...

October 27, 2019

Google Sheets, the Personal Finance GOAT

Google Sheets, the Personal Finance GOAT

Just over a year ago, I decided it was time to finally ditch the budgeting apps by migrating my budgeting into a personal ledger – created with Google Sheets. I spent the last year tracking the basics – net worth, categorized spending, balance changes – until recently, when I took on the challenge of seeing if it was possible to recreate most of the same features that personal finance apps offer from within Google Sheets.

Tl;dr – it’s very possible.

If you want to simply skip ahead to read how I am using Google Sheets today, scroll down to ‘My Finances in Google Sheets, Today’.

This isn’t a radical concept in any way – lots of people use Excel to manage their spending. But most personal finance apps fail to do what they set out to – kill excel and convince someone that tiles and charts within an iOS app are better.

Last Friday, in the midst of working on my spreadsheet and fiddling with Twilio’s API, I shared the below tweet:

I had triggered a text to be sent to my cell every time a new transaction was added to my spreadsheet. With every new purchase, I would get a text calling myself a dumbass along with where I made the purchase, how much I spent, and which account was used.

Multiple people reached out asking for a write-up on how I was using sheets for my spending – and how I was able to shame myself via text message for overspending – so I decided to write this post.

A Quick Background

If a personal finance app exists, there is a 99%+ chance that I have willingly handed over my bank usernames and passwords.

Personal Capital, Mint, NerdWallet, Penny (before it was bought and killed), TrueBill, Trim, Level Money, Hiatus, Clarity Money, PocketGuard, Empower, Mvelopes, Albert, multiple chatbots (if they even deserve that title), Fudget, Dave (which isn’t actually a budgeting tool and I once forgot to cancel and got charged $1), Morgan Stanley (did you know they link external accounts via Yodlee?), Wally, Olivia.. the list goes on and on.

I’ll admit, I am cynical when it comes to any new personal finance tool, but I do give each new app a real shot. Many are solving for very specific use cases (like couples’ finances or debt paydown) and they are helping hundreds of thousands of people, but it’s still a headache to onboard and try something new, only to be disappointed by the lack of value after getting set up.

So, let me start with the three primary issues I continually struggle with, across the majority of PFMs:

  • Poor data quality and normalization of transactions
  • Incorrect, predictive insights (see above)
  • Non-relevant notifications

Mint solved (1) and kind of (2) for me, and for a while, was my go-to spending tool. Although everyone in the fintech world enjoys calling them ‘antiquated’ while pitching their version of ‘Mint 2.0’, the service just works.

I stuck with Mint for a long time because a few years back, after Intuit sunsetted their aggregation API for non-Intuit products (how they link to your banks get your transactions), independent apps were stuck migrating to either Plaid or Yodlee, while Mint continued to leverage it’s corporate overlord’s connections. This led to higher fidelity transaction quality and fewer connectivity issues – a recurring issue when most banks don’t offer APIs, leaving scraping as the only other option to pull transaction data.

I parted ways with Mint after their new redesign. Yes, it’s prettier (although it seriously lacks in usability) but it’s also greedier. It went from being useful to spammy – their Northstar seemingly shifted to monetization by funneling users into product recommendations – including products that I had already connected to Mint.

The Initial Switch to Google Sheets

My must-haves with any personal finance management platform:

  • Net worth tracker: what are my assets – liabilities and can I calculate future expected earnings and expenses to project my net worth over time
  • Multiple logins: my wife and I need to access the same tool without sharing one login
  • Aggregated transactions: I need to be able to see everything from all 15 accounts (automatic updating is a nice to have)
  • Flexible categories: I don’t need 100 categories, but I also don’t want to be limited to 5 because an app thinks that is more ‘digestible’

My nice-to-haves:

  • Real-time collaboration
  • iOS and Android: most of my finances are done on desktop
  • Transaction tags
  • Relevant notifications: did I get hit with a fee, overspend, etc

I knew I could solve all of my must-haves using Google Sheets, so after Mint, I decided to go the manual route – downloading CSVs directly from my banks (15 accounts and 5 logins) and dumping them into a ‘finances’ spreadsheet. From there, it was a quick CTRL+SHIFT+H to find and replace and normalize categories. A few more sheets and I was calculating net worth, monthly spending per category, had an ongoing list of balances per account along with balance changes, and more.

The first version of my net wroth tracker (founders, take note – up and to the right)

Once a month, I would block time off to download CSVs from each account at all of my banks and migrate the data into sheets. From there, every other sheet would update automatically from a category hierarchy I created – which accounted for the category variations at each bank.

It was a more tedious process compared with using a free app that handled aggregation automatically, but worth it.

My Finances in Google Sheets, Today

After spending a year manually importing transactions, I went on the search for a tool that could sync to my bank accounts automatically and feed transaction data into my Google Sheet.

I ended up finding TillerMoney (to my knowledge, there isn’t any other only tool that has a Google Sheets integration) and spent a weekend rebuilding all of my budgeting sheets on top of their functionality.

Between their standard templates, a few Google Sheets formulas, and Automate.io, I’m at a point where my spreadsheet offers 99% of the same functionality as most personal finance apps. Below, I’ve documented all of the different “features”.

A couple of quick disclaimers:
(1) I sanitized my sheet – these are all fake balances, accounts, and transactions, for the purpose of sharing.
(2) I have no relationship with Tiller. I’m a happy, paying customer, and they have made my life easier when it comes to managing my finances in a Google Sheet

Setup:

Aggregated Transactions
I have 15 active accounts. 9 credit cards, 2 checking accounts, 1 investment account, 1 high yield savings, and a mortgage. At the foundation of my budgeting, I have to be able to see and analyze all of my transactions in one place.

Prior to Tiller, I was manually updating one spreadsheet with rows of transactions spanning all of the different institutions (as I mentioned earlier). Between logging into each bank account, downloading a CSV with transactions from the last import date, and normalizing, the whole process took about an hour. It’s updated automatically now:

Categories
Categories are probably the most important thing for me – I spent 4.5 years of my life building better credit card reward recommendations, which involved mapping millions of transactions to 700+ merchant categories codes. They’re at the root of how I budget and think about my spending, so flexibility is key. Below is a glimpse into how my categories are set up:

I set up my spreadsheet to have a total of 44 categories – all of them round up into a “group”. I differentiate between 4 groups: discretionary, bills, living, and transfer, but I plan to make groups the top-level category (ie: food, with groceries and restaurants rolling up). Budgets are set per category and each of the groups will sum the total spending and budget per all of its children categories – shown later in this post.

Core Sheets:
With transactions and categories built and automated, I’m able to create nearly any view that you would expect from a personal finance tool. The ones that I find useful are below:

Insights (Tiller Template)

The ‘Money Insights’ tab is the first thing I see when I open up my spreadsheet. I’m now using a Tiller pre-made template to analyze transactions, but prior to this I had a sheet for calculations with:

  • A list of all unique merchants with unique(range of transactions)
  • Columns with a simple countif(range, merchant) to get frequency and sumif(range, merchant) to get spend
  • Query(range, n rows) to pull back the top merchants by spend and frequency

If you wanted to get a bit more insightful, you could have an ‘exclude’ list to ignore line items, such as credit card payments, or solve all of the above with pivot tables.

Tiller added in sparklines to return back the single bar charts for assets, liabilities and net worth, which is really all I need to look at when I open my spreadsheet.

Net Worth
The net worth formula is straightforward. Take your assets – checking, savings, investments, properties, autos, etc – and then subtract away any liabilities – credit card debt, student loans, personal loans, mortgage, auto loans, etc.

Over time, we accumulate A LOT of accounts (at least I do, anyway) and so I need to see the list of accounts and their balances in the simplest way possible. I calculate net worth based on what the total balances are at the end of each month and then do a quick comparison to the prior month to calculate the percentage increase or decrease.

I think most people start budgeting without first tracking whether their net worth is increasing or decreasing, which is a better place to start versus analyzing lots of categories and working bottom-up. If your net worth is going up, great, you are spending less than what you are making. If your net worth is decreasing, you can at least focus on change – whether you’re not making enough or spending too much. Obviously macro conditions, such as the housing and investment markets could throw this off.

Projecting Net Worth
Every single personal finance app has been terrible at projecting my future income and expenses, a.k.a., my future projected net worth. This is due to a whole bunch of reasons, but the gist is that data quality via third party aggregators is still not consistent enough to accurately categorize spending. Additionally, my influx of cash isn’t always fixed – bonuses, tax refunds, purchase refunds, etc. can change, and my expenses are variable – I really don’t know how much I am going to spend in a particular month. But the one tool that can flexibly accommodate random line items – google sheets!

A sheet I created to add in future income and expenses

The above probably seems trivial, but the ability to create a new sheet, add rows, and fill those in with whatever names and amounts I want, is key for my understanding of my personal spending. I have yet to be able to do this in another app without it impacting every other feature and netting out to be totally worthless.

Monthly Budget
The last of my ‘core sheets’, which I use nearly every time I look at my spending, is my monthly budget.

I keep my budget simple – how much do I want to spend in a given month on a particular category, how much have I spent so far, and how much do I have left to spend?

Tiller is helpful because it builds the monthly budget out of the box with a template, and there isn’t much more to do beyond choosing the categories you would like to use and then updating your monthly budget. The gray highlighted rows are the groups (mentioned earlier – I plan to convert into top-level categories: food, home, pets, other)

This sheet also powers the mean texts I send to myself when I overspend, and nice texts when I money comes into an account (more on this below).

Add Ons:
So with the ‘feature’ sheets created, I essentially have everything that I believe I need in order to accurately analyze my personal financial situation. However, I haven’t yet replaced all of the useful features that many personal finance apps have. That’s where add ons come in.

Zestimate Script (Tiller)
Tiller providing a Google Sheets script that pulls in my Zestimate is actually the main thing that converted me from a trial user into a paying user. It works like this:

  • Copy their Google Sheet script which integrates sheets with the Zestimates API
  • Replace some variables, like your personal Zillow API tokens & addresses of the properties you own
  • Save and run
  • Your home’s Zestimate is automatically inserted in your sheet as an asset and updated as the home value changes

As a new homeowner, I thought this was the coolest feature I had seen from a PFM and it got me excited to see what else the company would provide down the road.

SMS Notifications (Automate.io)
With notifications being at the root of most personal finances (and their ‘value add’) I decided to recreate this and make it more relevant.

There are two sheets set up which trigger notifications for income and expenses. One grabs categories, spend, budget, and remaining at that point in time, and then updates when new transactions are piped in. If the spend > budget, a text is triggered. The other sheet looks for transactions related to income and updates, triggering an income text.

I did this by using Automate.io – the free plan handles more than enough requests – along with Nexmo – an SMS API that costs ~$.006 a message and I preloaded $10. The logic created in Automate.io triggers a custom SMS that fills in variables from my Google Sheet on a row update or change. With my transactions feed being updated automatically through Tiller, my SMS triggers happen automatically.

What’s Next

I value flexibility first, which is why Google Sheets has been my personal finance GOAT. Instead of relying on a dev team to push new features out, most of what I’m hoping to accomplish can be solved in a matter of hours with a few tweaks to a spreadsheet.

Over time, I plan to add more features, such as subscription tracking (still noodling on the best way to calculate this), projected spend and income based on prior numbers, alerts before annual fees are charged, rewards calculations, and others.

Google sheets competes with every personal finance app, but no personal finance app competes with Google Sheets

Dave Ramsey, probably

close

šŸ‘‹ Hey you

Sign up to receive my random thoughts in your inbox.

Posted in ProductivityTaggs: