IFTTT + Google Sheets + Monzo = Then what?

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

Does the Plus integration come in the same format as if you export the data manually? Or is there more/different data?

Pretty sure its the same as the CSV export of ‘all time’ data.

1 Like

This is such a great help, thank you! I’ve managed to export everything over and it seems to work perfectly!

I’m going to be a big nerd and tinker with this all weekend and look into building my own dashboard. Works with my joint account too (which is split equally between my partner and I) so nice addition, too.

Greatly appreciate your time on this one Lewis :+1:

1 Like

Yeah, as Lewis said the same headings are used in both CSV and the Plus imports - columns A-P.

1 Like

Success!

1 Like

Hurrrah!

Also, @revels - thinking about your comment in the now locked thread -

Gosh that is like Round Up on Steroids - for the ultimate £X.00 balance in all pots!

What would it need?

Bill Pots balance updating - that should work fine in Google Sheets.
Quick sum to tell it to to round the £8.26 and work out the difference - should work.
Monitor a cell to move that £0.74 from Bill Pot to Main Balance - should work.

Maybe then you either need to monitor the same cell twice with two integrations - so the move £ in / move £ back out fire off at the same time - that might not be reliable.

Or then you have a second cell in your Google Sheet to watch for the £0.74 coming back into your balance - how would you do that? You’d need to know the pot and also match a previous transaction (+8.26) so it didn’t fire for that, but would fire for the £0.74.

Not sure what that last bit would look like, but if there’s a reliable way to spot that and not confuse it for the first transaction, I reckon so.

Feeling is that since DD’s are taken out at the same time in the morning, if you have multiple on the same day, the IFTTT trigger might miss one of them.

Final Option - IFTTT Platform allows you multiple actions - so you just monitor one cell, and it’s used as the ingredient for both Move Money In and Money Out

1 Like

I use google sheets to transfer 10% of a card spend to a saving pot. Now because of your formula and examples it might be possible to do it of direct debits too! :thinking:

1 Like

Could you set it to run once a day from that pot, say 10am, once you know everything has gone?

You’ve got the balance of the pot, =round, in another cell, then do some more magic afterwards?

1 Like

Is it just balance of Pot = Rounded you’re after, or, it’s appropriately rounded after each transaction?

i.e. three £2.50 DD’s go out.

Would you want your pot total to have reduced by £9 (£2.5, rounded to £3, x 3), or £8 (£2.5 x 3 = £7.5, rounded to £8).

If the former, you’d probably need a few rows to catch each one.

But either way, I think the way you’d force the move by time, is to use =now() - and have (forgetting the syntax…) if now >= today 10:00 and < today 10:10 then, sum(the cells with rounding in)

Then you monitor that cell with IFTTT.

1 Like

In your example I’d want it rounded to £8.

I need to have a look at this as then I’d be a complete neat freak!

I downloaded all my data to have a play with the formatting, to start with I was “This is a lot and I don’t need it” but with a simple SUMIF I’ve got the totals of all my pots and I’m converted!

1 Like

Right got it!

This is what I reckon

Target Date would be ‘=today()’

Total Outgoing - is your sumif to find everything that is going from your bill pot, and is a pot transfer. Probably should also check that this is an outgoing transfer (i.e negative or not).

The Magic Bit - will display the rounded value if it’s between the two target times you specify in the relevant cells below.

'=if(and(timevalue(now())>timevalue(R29),timevalue(now())<=timevalue(S29)),Q27,"")

Adjust references as needed.

Monitor the cell in “the magic bit” and at your targetted time it will trigger IFTTT.

1 Like

You are a genius! Thank you

I’ll need to read that a couple of times I think :rofl:

Hah! If it helps I can share a google sheet with some references in?

2 Likes