IFTTT + Google Sheets + Monzo = Then what?

I use IFTTT to add my Monzo transactions to a spreadsheet and then I track various things but I’m curious to what else I can look at, how, what graphs people have etc.

I track…

Spend per category. Because they are just imported to a sheet, it’s easy to change so I have a few extra on top of the usual Monzo ones.

Spend per merchant. Transactions per merchant. Got to reduce McDonalds! Which admittedly is quite easy the moment.

I’ve just added a cumulative spend tracker, basically like the old pulse graph but layered with my pay periods so it’s easy to see where I am vs other months.

I also count days that I spend £0 and days below £5.

Anyone have anything weird and nerdy they want to show off? Obviously hide the actual numbers etc if you want to.

1 Like

I’d love to jump in and do all the flashy stuff with Monzo data, but I only use a Joint Account which isn’t supported with IFTTT. It was being worked on but then the unfortunate circumstances hit.

1 Like

There was a post in the Revolut thread that I thought was a good way of looking at my data, so I’ve recreated it with Google Sheets.

I find it interesting looking at the data like this :nerd_face:

Fuel one makes nice reading. Not sure where the extra money has gone though!

Changed to Co-Op for shopping as it’s local and not as busy as the big Tesco. Once the chaos had subsided a bit, reverted back.

But overall, my spending is fairly consistant, some bigger peaks when I buy more extravagant things or went on holiday (remember those? :unamused:)

1 Like

I use IFTTT and Google Sheets to put 10% of what I spend on card into a pot :smiley:

Minus the IFTTT for the Plus gang now, this is probably a good time for a bump!

So… Now that you can get a full Accounting in Google Sheets…

Are you planning any new integrations?

I was thinking I should now be able to make a running balance of any and all of my pots, by adding a few helper sheets.

With the “Cell Changes in a Google Sheet”, you could do clever things like -

  • Top Up a pot if it gets low…
  • Notify yourself if a pot gets too low.
  • Sweep your balance on the day of payday… maybe?

Any other clever thoughts?

So I’ve got an integration working that now can happen with Plus.

Basically, it is a “Pot Balance Actioner” - whereby when a named pot hits a specific balance, an action of your choosing will trigger. That can be moving money, an notification, change of colour of a Hue bulb. Whatever, as long as the action is in IFTTT.

In my case, what I’ve made is a trigger to auto-top up my “Coffee” pot, if my balance hits <£x.

(You could equally set this to send you a notification below £50, etc)

Things needed:

  • Set up Monzo to Google Sheets export.
  • In the Google Sheet, make a new tab, and drag it to Tab Position 1 (this is important).
  • Set up the sheet like so:
Pot Name Balance Action Value
Bill Pot
AMEX Pot
Coffee Pot

(It’s important to have the word “Pot” after the name of your pot).

In the balance column, add the following formula; and this should update the balance to what you have today in said pot:

=-SUMIFS(‘Monzo Transactions’!H:H,‘Monzo Transactions’!D:D,“Pot transfer”,‘Monzo Transactions’!E:E,A2)

(This will sum the total transaction amount (col H) for any transaction that matches “Pot Transfer” type, and matches the name of the pot).

All being well, because this data goes back to the start of the account, this should be a real time balance of your Pot.

In “Action Value” column, you can add any formula that effectively marks the “change” of value. You can set it up with a text value if you wanted to receive a notification, or a £ value if you want to move money around your pots, etc. Or any number of more complex formula.

In my case, for my Coffee Pot example, I want to add £30 if my coffee pot falls below £20.

So I add the formula

=if(C5<30,20,“”)

i.e, if Coffee Pot balance <£30, then display £20, otherwise Blank.

Then move to IFTTT and create a new applet for Google Sheets - pick the option “Cell Updated in a Google Sheet”.

Enter “Monzo Transactions” as the file name
And the relevant cell your Action value is in as your cell reference.

For the Action, in my case I want to move money into a pot, so action = Monzo, move money, and use the “Value” ingredient from the Google Sheet trigger.

As this Monzo Transaction sheet updates instantly, once my Coffee Balance moves <£30, the monitored cell updates, and the trigger fires, moving money into my pot.

You could so so many variants of this - e.g. on the first of the month, move ALL my money but £X into my Savings Pot. etc etc.

Or you could base it on when you spend £1000 at McDonalds, play your “Party” playlist on a connected device. Or if you’ve had 217 Scissor Emojis in your transaction field, set your Hive thermostat to 28oC.

Hope someone finds some use out of this!

4 Likes

I’m going to take a look at Google Sheets as I really like the idea of a near live auto-update offered by #newPlus.

I build dashboards and do lots of analysis in Excel for work so interested in learning a bit what Google Sheets can do. Must admit, I trialled it for about 5 minutes last night and nearly threw the laptop out the window as I couldn’t get it to recognise the transaction date as being in UK format rather than US, but I was tired and short-tempered so fresh eyes over the weekend perhaps :grinning:

Personally, I see mileage in this for drilling into my big areas of spend that I religiously hashtag or categorise yet the Monzo app doesn’t quite give me the detail I need:

  • what is my weekly TFL spend; are there any trends I can spot where I save money like particular days I’m out of the office or travel off-peak;

  • is my average #bigshop cost coming down - I’ve changed to fortnightly supermarket trips but is it saving me money;

  • what’s my spend / savings profile over the year and are there any months I can adjust to improve this?

  • how much does my #running and #gig (both Entertainment :grinning:) obsession actually cost me and when during the year do I need to start saving a bit?

  • perhaps a return of the Pulse graph… :open_mouth:

I’m intrigued how this goes.

(Edit for typo)

As someone who gets massive OCD issues when I get interest paid on my Savings Pots and the balance is no longer a round figure, this is possibly the greatest thing ever.

1 Like

That’s interesting now you say that - I’ve just looked through the Google Sheet list and I can’t see any specific transactions for any interest-bearing pots come up.

I guess because the interest is paid directly to the pot they are not listed.

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!

19 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: