IFTTT + Google Sheets + Monzo = Then what?

Now I’m at a computer it’s a bit easier. So what do I have…

Probably the most basic one first and half of what you’ve already got, to track spend by category across my different pay periods. Part of the reason I did this was because there wasn’t the custom ones in Monzo.

Then a line graph of it, which really is just groceries miles ahead of everything else!

monzo2

Then to track by merchant by pay period, which is basically just the top one, but looking at the merchant rather than cat. To spot my monthly clothes habit!

monzo3

And total at that merchant. I have a formula here that will automatically add a new merchant to the bottom of the list, but Tesco won’t be added again.

monzo4

Funky graph to my total spending and then try and keep the next month under the previous one! It was going well!

And this one I use to keep an eye on when I’m spending my “fun money” to see that if I’ve gone crazy at the start of the month then I need to calm down.

monzo6

I also track days that I spend £0.00 and days under £5. The latter tends to be me being wasteful and buying snacks to/from work when I really don’t need to.

Happy to try and help with setting up anything for anyone that wants similar things. You might need some helper columns within it, but all of this automatically updates when I spend, I don’t have to do anything extra to it.

2 Likes

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;

  1. 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”)

  1. 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.
  1. 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);
    image

  2. 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;

  1. 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!

18 Likes

May be true, but I learned some stuff, so thank you :pray:

1 Like

This is literally amazing. I did not know this function existed.

2 Likes

Seems to work really well too. It sometimes introduces a little bit more lag (a minute or two) between transactions occurring and appearing in the list. But that isn’t a big deal for my purposes (might be if you’re relying on it to trigger pot transfers).

I’ve made a way to do an Automatic Salary Sweep when you get paid, using Sheets and IFTTT. Let’s say on Payday you want to move all your leftover money into a Pot. You can do it now.

In your Monzo Transaction Google Sheet, set up a little table like so:

image

Yellow Cells = Formula, Green is the final outcome you want.

Name - enter how your salary payment looks when you get paid. (If this is not consistent, can do this other ways)

Time frame - Enter the formula ‘=today()-1’ -

Value - a Sum If that will look for Name and Date:

=sumifs('Monzo Transactions'!H:H,'Monzo Transactions'!E:E,[Name Cell],'Monzo Transactions'!B:B,">"&[Date Cell])

Account Balance - sums everything in your account - displays blank if no recent pay day is found.

=if([Value Cell]>0,sum('Monzo Transactions'!H:H),"")

Buffer Amount - Will tweak the final “action amount” - if you want to say move an extra 200 more than your balance, enter 200 here. Or a negative to move less than.

Action Amount - a sum of the relevant cells - and this is what tells IFTTT what to move. Either displays a value, or is blank.

=if([Acc Balance]<>"",[Value]-[Balance]+[Buffer],"") 

(replace with relevant cell ref)

Done. So on Pay Day, this should look something like the screenshot above. Basically the cell in Green will update with the “excess” and you then go to tell IFTTT what to take out.

In IFTTT:

  • set up a “When a cell changes in Google Sheets”,
  • enter your Green Cell ref in the relevant box,
  • Add Monzo Move Money to a Pot as the ‘That’
  • And for the amount, use the ingredient {{value}} - which will be your green amount.

Presto, your balance Pre-Payday will move into a pot, leaving your main balance with just your salary.

You can do all sorts of little searches to do more automatic money moving.

7 Likes

Forgive me, I’m very much a novice… Would you be willing to share a copy of your Google Sheet but remove your import link so none of your data is there but we have the template?

1 Like

I would, but its so intertwined with all of the other data I track that it probably break and won’t work for you. I’ll take a look this evening and see whether I can put together a template.
For context, I use the same spreadsheet to track a tonne of stuff because I’m a crazy data hoarder haha;
My utilities, including gas, electric and water;
image
My solar output;


My car mileage and state of charge:

And a bunch of health metrics like stepcounts, weight, cycling activity, average heart rate, hours of sleep.

5 Likes

Any chance you’re able to help me with producing something like this?! It’s gorgeous :heart_eyes: haha!

Ah, thank you so much - no to worry if you can’t or don’t have the time.
WOW and I thought I liked tracking things :cold_sweat:

Course I can!

Are you using the standard IFTTT or Plus?

Tried having a go but I keep getting an ERROR when I do the first formula.
Might be because my pots have the word Pot after them already, so in monzo it’s named Bus Pass Pot, so in the Sheet it would need to say Bus Pass Pot Pot? :man_shrugging:t2:

I was doing it in my phone, but I’ll take another look when I’m on my computer later.
Really fascinating stuff though!

1 Like

Thank you!

I’m using Plus - can use IFTTT if needed

1 Like

Yes that’s right in the google sheet it displays as “[Pot Name] Pot” so you might need to Double Pot.

Nudge if you can’t get it to work!

1 Like

I’ll be Plus next week so let me get it all set back up and I’ll see if I can give you the template.

1 Like

Still says Formula Parse Error in red underneath

Can you do a screen grab of the formula?

I copied it exactly how you written it in the post above :blush:

Ahhhh

Just tried copying and pasting myself. I think pasting between GSheets and the Forum, it changes the character from an actual apostrophe to a “left quotation” which is causing the error. Either copy and paste the below, or manually tweak all of the ‘ to ’

Or try:

=-SUMIFS('Monzo Transactions'!H:H,'Monzo Transactions'!D:D,"Pot transfer",'Monzo Transactions'!E:E,A2)
1 Like

Ok. Who knows whether this will actually work… but I’ve mutilated my spreadsheets to try to craft a version that will work for others. Its based off a few assumptions though (since its based on my circumstances);

  • That you have both a personal and a joint account.
  • That you mostly use the joint account for spending and saving.
  • That you work on the assumption that you own HALF of the joint account balance.
  • That you use the CSVs from monzo (either via auto updates with monzo plus or copy pasted into this template).
  • That you care about monthly comparisons of balances (rather than 4 weekly, or whatever).

So, here is the link. First thing you’ll need to do is go to ‘File’ then ‘Make a copy’;
image

When you open it, you should see 4 sheets.
image

  • For the Raw Personal you can edit the URL in the formula found in the yellow box; so that it points at your monzo transactions google sheet (alternatively follow step below for both personal and joint).
  • For the Raw Joint you will need to export data from within the app (I typically email it to myself then open in gmail, then copy paste into this section). Include the headings.
  • The Money Dashboard should then populate with charts showing balance over time etc. These charts are pulling their figures from one of the six hidden sheets that contain pivot tables. These pivot tables mostly look at the Helper Sheet and arrange the information in an order that makes sense for creating charts. You will likely need to adjust some things to make it look right. I had balance data going back 10 years so if you only have a couple months data I have no idea if it will work properly.
  • The Helper Sheet creates a tidy list of balances against dates. The overall balance listed here is the sum of the main account and main pots, plus half of the joint account/pots. This sheet also contains a bunch of additional columns like ‘Year’, ‘Month’ etc that are used in the pivot tables on the hidden sheets.

Troubleshooting;
If the charts look weird or empty, first place to look should be the ‘Helper Sheet’.

  • Check that the balances are populated properly.
  • Try removing the rows up until the date of you got your monzo account
  • If a particular chart looks broken, you can investigate by finding the hidden sheet that it references and checking that the table makes sense.
  • Try checking the pivot tables on the hidden sheets for any ‘filters’ that shouldn’t be there or are incorrect. e.g. “That’s weird its only showing data for 2019, oh its because the pivot table is filtered to only show 2019 for some reason”
  • You will likely need to adjust the formatting on the charts as this might break if you have more or less data that I had in it.
  • Feel free to message me on here, but keep in mind that its notoriously difficult to troubleshoot other people’s spreadsheet woes :slight_smile:

If you’re interested in this kind of stuff, I highly recommend you try to build your own dashboards from scratch rather than relying on this. The process of building the formulas yourself means that if something goes wrong you know how to fix it. I’ve been iterating on these spreadsheets for years and years, starting from basically nothing and no experience.

Let me know how you get on!

7 Likes