I shared some of my google sheets on here previously, but figured I’d do a run through much the same as @Revels just did. This isn’t going to be a tutorial and probably won’t be very easy to follow, as there are far too many parts to it but it will hopefully give you some ideas. All of this is automated. The only thing I need to do is occasionally export joint account data. Personal account is now updated automatically, yay! I will try to answer questions if people have any;
- To prep the data; First thing to note is that I don’t use the original google sheet from the automatically updated spreadsheet that monzo create. Instead I reference it in my spreadsheet using the following formula. This allows me to manipulate that sheet (add columns to the start) without worrying about it breaking with updates:
=IMPORTRANGE(“URL GOES HERE”,“Monzo Transactions!A:P”)
- To make the data more useful; I add a bunch of additional columns ahead of the data to include the following fields;
Year, Quarter, Month, Day of Month, Week, Day of week, Main balance, Pot Balance, Pot Transactions, Amount Flipped
Most of these are self explanatory and either reference the date, sum the transactions up to that date to obtain the main balance or sum the pot transactions to obtain the pot balance.
Having these fields extracted makes it significantly easier to create useful pivot tables and charts that can compare month on month, year or year, day by day etc. And to see a running balance.
-
To get a nice simple list of dates and balances; On a separate sheet I have a column of incrementing dates, and columns for account and pot balances. Account and pot balances are pulled from the main raw data by looking up the date, then looking for the corresponding balance for that date (column H and I in the picture above). If it can’t find a balance for that date (i.e. no transactions on that date), then it just shows the balance from the previous date. The result looks something like this (with amounts redacted);
-
To compare month on month: You need a way to indicate the current month, the previous month and the month before that. I do that on the simplified sheet mentioned above. I run something similar to the following formula that essential asks whether the month/year of a particular date is the same as the month/year of today’s date. I do similar formulas to work out whether its the previous month, or the month before that. Instead of asking whether its the same as today’s month, you ask whether its the same as today’s month -1, then -2.
= if(month(N1)=month(TODAY()),if(year(n1)=year(TODAY()),“Current Month”,))
I also add the ‘Day of the month’ column to the sheet mentioned above. Using the following;
=day(N1)
The balance, the day of the month and whether its the current/previous/previous previous month is everything you need to create a filtered pivot table and chart like this where green line is current month, grey is last month and dotted line is month before that;
- Add a year column to do year on year comparisons;
Appendices;
I do a bunch more formulas and pivot tables to achieve the following;
Category spending per month;
Category spending as percentage of overall spending;
Breakdown of Bills per month:
Location of spending:
I tag my lunch expenses so that I can do comparisons like this:
And a bunch more. Sorry this isn’t a proper tutorial on how to do it, but like I said hopefully it gives you some ideas. Any questions let me know!