How I automated my budget tracking (Part 2)

Hello — if you find yourself here thinking ‘WHERE IS PART 1?!’, check it out here and then come back!

Alright, time to get serious — you’ll remember (hopefully) where we left off last time, with The Great Budget Update formed in my mind but not in reality. In this post I’ll try to go through how I set things up and in roughly what order, and if I remember I’ll try adding in part of my thought process throughout.

Fair warning — the last part of this (the automated data entry) will get a bit technical. Of course, there’s no obligation for anyone to follow that (or any of what I say), but just FYI. Then again, I imagine most of my posts will be somewhat technical since that’s what I enjoy so be warned I guess?

Structure

So the first thing I had to do was set up a new Google Sheet, and for this I knew I needed to change how I was recording each expense. I couldn’t have separate sheets/tabs for each month, and I couldn’t keep editing formulas to add every day’s new expenses. It was incredibly inflexible (unflexible isn’t a word?) and didn’t really allow me to layer any kind of analysis on top.

With that, I decided to go for a main sheet that was just going to be a raw data dump, with each transaction having its own row; basically a database. Then I thought about what information I need to record for each transaction — what could I ever want to filter by in the future? It was important to think ahead, because adding stuff later would be a pain, but I also didn’t want to overdo it because each of those columns would still need to be filled out by yours truly (at this moment that was all still manual).

Here’s what I ended up with:

• Date (this might be a bit duh, but it was actually an upgrade — all I had before was month, and even that was wrong most of the time)
• Category (Food, Internet, A/C, Petrol, Outings, etc.)
• Type (Income or Expense)
• Amount (this one is actually duh)
• Notes (any details about the expense — usually ‘this was for Mrs. See’)
• Month
• Year

As I write this now (3 years after the fact), I realize the last two are actually redundant since I have date and that contains the month and year. I might actually go and remove them if I ever find the time. See, always learning new things!

Build it out

This was pretty simple — I just made a new Google Sheet, and created a tab called ‘Transactions’ and made a nice looking table like so:

Now we’re talking!

Much prettier than its predecessor, I’m sure you’ll agree. There were a few lazy-friendly additions to this:

• Category was a dropdown that read from a different sheet — here I basically added every category I could think of, but of course if something new ever came up I could easily add to the list. Like in the future when I buy an Aston Martin for example — that could be a new category of its own.
• Type was a VLOOKUP based on the Category value — again, I had this all set up on the different sheet, so all I had to do was fill in Category with the dropdown and it would automatically fill in the Type. If anyone cares, the formula for that was =IFERROR(VLOOKUP(B2,Lookup!$A$1:$B$350,2,FALSE),"") just so that it looked nice and clean and didn’t throw up an ugly #REF! error for rows that I hadn’t filled in yet.

The Lookup sheet — keeping it simple.

Let’s get Analytical

What I wanted to do next was add some charts and stuff based on the raw data I was now going to be recording. Now Google Sheets admittedly isn’t the best tool for visualization, and I have thought about using Data Studio (but haven’t gotten around to that yet), so I’ll share what I did manage to do using the tools that are available in Sheets.

Broad themes for what I wanted to see here was:

• How much have I spent vs earned overall
• What am I spending on?
• The ability to drill deeper into particular month(s)

Here is part of my ‘Overall Summary’ tab:

Sorry, lots of censoring was needed here.

This covered points 1 and 2 — the top left charts were something I didn’t know about before I started this exercise, but they’re called ‘sparklines’ and you can basically have a tiny little chart within a cell, instead of creating a separate chart and having a relatively ugly box that covers whatever is behind it. This looks more ‘at home’ and works for a simple bar chart which was fine for me in this case.

The formula for creating the green bar was =SPARKLINE(A14,{"charttype","column”;"ymin",0;"ymax",MAX(A14:B14);"firstcolor","#0f4d09"}) — basically we’re creating a column chart, the first argument specifies the value I want to chart (total income in this case), I can specify the y-axis range and then the color of the bar as well. Not really necessary for you to know but in case anyone reads this for the nerdy bits.

Underneath these charts I had simple pivot tables that read from my raw transactions sheet — I just filtered one for the ‘Income’ Type and the other for ‘Expense’, sorted them in descending order, made them look nice and that was it. Now I could easily see at a glance where my money was going (obviously too much has been thrown in Investments over the years — let’s not talk about that).

The last part was the month drilldown — that was another sheet that had a couple of sparklines, pivots and a chart like so:

Oh look! A month where I actually saved some money.

Very simple — if I wanted to look at a certain month I had to change the pivot filter which was a little annoying, but I don’t really use this sheet much honestly, so it hasn’t been enough of a pain to improve that.

The ACTUAL last part (just remembered) was a time series — here I just wanted to see income/expenses/savings over time, spot if anything was out of shape. Again, standard pivot from the raw data with different columns and then a chart built from the pivot:

Look at aaaall thaaaat data!

Charts like this one are pretty much why I love doing stuff like this. I can now look back at 3 years of my life and go ‘oh, what on earth was I doing in August 2020?’ and then check that specific month to see what I did with my money back then (spoiler alert: it was investing — of course. A black hole where money has been going to die recently).

On a serious note, I really feel like this is something everyone who earns any kind of money should be doing. If things ever get tough (and it sure looks like the economy is heading that way) the first thing you need to do is assess your spending and cut where possible, and it’s impossible to do that if you haven’t been keeping track of what, where, and when you’re spending your money. The sooner you start tracking your budgets like this the better, and I guess that’s part of the reason I’m writing about it. Hopefully someone who reads this (now or 10 years from now if Medium is still around) can take that first step and have it be a little less intimidating or confusing than it might have otherwise been because of these articles. I am also considering making a ‘blank’ version of this template and then making that available for anyone to copy — hopefully that might come with the end of this particular series.

Guess what?

It’s now past midnight and this post has become wayyyy too long, so I’m going to leave the actual data entry automation that I promised earlier for Part 3. I swear this wasn’t planned, I thought I’d be able to get through everything in one part but it seems I write a lot more than I talk. Got to keep Gen Z’s attention spans in mind and I feel like this has already been a lot of information to absorb.

So join me in Part 3 (whenever I get around to it) to see how I automated my data entry to complete the (sort of) final piece of The Great Budget Update puzzle. See you then!

-Saw