IFTTT + Google Sheets + Monzo = Then what?

Monzo Chat
181

One view I like, is a “monthly spend by category” overview, so I can see Monthly, my err, category spending (a bit ronseal there…) - and compare month to month - always a useful indictor for me

e.g:

image

You can get all of your Categories in a list by using this in Column A

=unique('Monzo Transactions'!G:G)

And a SUMIF like so to tot up that months spending:

=sumifs(‘Monzo Transactions’!$H:$H,‘Monzo Transactions’!$G:$G,$A5,‘Monzo Transactions’!$B:$B,">="&AD$3,‘Monzo Transactions’!$B:$B,"<"&AE$3)

(Where AD3 / AE3 are the start dates of each month).

Just beware of the “Free Plan” limitation if you’re using not IFTTT pro. There’s a link I made in another thread to an applet you can use that doesn’t impact your Free count, if you need it.

1 Like
182

I’ll give that a go now :smiley: It’s tricky because all your columns are different to mine so I need to figure out which one you mean in your formulas.

I haven’t changed the column order from what Monzo provide, so can only assume you have?

183

Weird, should be no changes in this example - I’m using an import to a different sheet, but copying columns A-P directly.

I have these headings:

image
image1582×70 8.44 KB

So in the formula above;

G -> Category column
H -> Amount
B -> Date

Unless Monzo have changed how the import feeds in?

184

Ahhh ignore me - it’s me :see_no_evil:

In my initial IMPORTRANGE I did B:P so left off the first column which is Transaction ID.

1 Like
185

I’m stuck with this one now…

So I have my column that is a list of all my transactions using:

=unique('Monzo Transactions'!G:G)

But it’s the second part I’m struggling with:

=sumifs(‘Monzo Transactions’!$H:$H,‘Monzo Transactions’!$G:$G,$A5,‘Monzo Transactions’!$B:$B,">="&AD$3,‘Monzo Transactions’!$B:$B,"<"&AE$3)

So the bit I’ve bolded (A5) is referencing the cell next to it to get the category name. Therefore I have to put this formula next to every category and change that part of the formula to A6, A7, A8 and so on. Surely there is a formula to reference the cell to it’s left so I can just drag it down? (will Google it shortly).

Then the problem with this is if I add a new custom category it won’t automatically add this formula next to it. Unless I’m doing it wrong :thinking:

186

So cell you enter the UNIQUE formula into should auto expand any time you add a new category to Monzo. (It also gets caught out if you change category names after the fact as the data doesn’t retroactively update).

If you drag the formula down from the first cell, it should auto update the A5 to A6, A7, A8 etc. (Same for across the columns too)

The only bit I would do is add more rows of this formula than you currently need - that way when you add a new category the data is already there.

187

Yep that’s fine, it does that.

This is the bit I was referencing. So you’re saying I should drag it right down (say 100 rows) to cover any new categories I add?

188

Yep! Exactly that. Nothing else needs to be changed. The sheet will auto update to include any new categories as you make them.

Little test:

Before:
image

Added a new category:

image

And the formula in Column AK goes all the way down to like, 100 rows - or whatever the max custom categories were.

And if you want you can right click on a selection of rows and “Group” them - will hide them until you need to expand.

189

Ahh ok, that’s fine :smiley:

I just wanted to make sure I wasn’t doing anything wrong. I was also curious if there was a smarter way of doing it so it would be automated.

I was Googling and found that if I use this

INDIRECT("RC[-1]",FALSE)

It will reference the cell to the left. So I was going to play and see if I could then do this for the entire column but only do the sum if the category cell isn’t blank.

Might just be me massively overthinking it and making it super complicated because yours works just fine but all those £0 bug me :smiley: :see_no_evil:

190

Here you can solve that by adding some formatting to make anything £0.00 display as blank;

If you go to Format > Number > More Formats > Custom Number Formats - you can change the appearance of numbers.

Formats are separated by “;” going Positive;Negative;Zero;Text.

If you set this to

[green]£0.00;[red]-£0.00;""

it will display like so:

image

Other [colours] are available; http://dmcritchie.mvps.org/excel/colors.htm

You can also wrap the SUMIFs formula in another formula, to only sum, if the result is >0:

=IF([original sumifs formula]<>0,[original sumifs formula],"")

Will show blank if the result is £0.00

1 Like
191

Brilliant thank you - I’ll do the SUMIF approach I think.

I’m currently trying to adapt your formula here:

=sumifs(‘Monzo Transactions’!$H:$H,‘Monzo Transactions’!$G:$G, $A5 ,‘Monzo Transactions’!$B:$B,">="&AD$3,‘Monzo Transactions’!$B:$B,"<"&AE$3)

To exclude a few payments like my Salary. So where name = ‘employername’ because that’s messing up some categories.

I’m learning a lot from merging and Googling based on past formulas supplied in here :grin:

Edit: Also… I seem to be getting some odd figures for my ‘General’ category. Looking at summary in the app nothing is categorised under there. Any ideas? :confused:

192

That’s interesting. I just looked in my own summary for General and it’s off too.

As a quick guess, I think summary in app only includes spending not the sum total of spending and income. I think that accounts for most of it.

193

I found that my salary payment was under General but as you said, it wasn’t displayed under summary in the app. Hense why I wanted to exclude it.

I wonder what else the app is sticking in there without telling us :thinking:

I’m also finding that ‘finances’ is another one that is £0 in summary but has a value in my spreadsheet.

194

The other thing I realised - the way Split categories are handled breaks the ability to Sum stuff super accurately.

In the sheet, the first category assigned is the main one in the category column, so any calculations will be based on that, and not the split

Perhaps the app accounts for it better.

There’s a way around it though. Just once again messy…

Edit: and also in the app you can “exclude from summary” on certain transactions. Won’t affect the export at all - so maybe it’s that?

195

Hi guys,

Im looking for some guidance on how to use IFTTT i am completely new to it and have no experience with coding at all!

There are a few applets that i would like to connect to my Monzo.

I initially tried to set up the applet “log my card purchases to a spreadsheet”

Honestly i havent got a clue what im doing with this!

I have attempted to connect but i get an error message come up. See photo.

Can anyone help with this? A step by step guide maybe?

Thanks in advance

image
image1125×2436 313 KB

196

I think the “Spreadsheet name” in this screenshot is the problem.

In IFTTT, anything in {curly brackets} is a variable - and I don’t think it wants a Variable in the spreadsheet name box.

There is a box later that asks for the spreadsheet format, and that’s where you’d put all those things.

Keep the Sheet Name as just Monzo, and it should work.

197

Does anyone have a good way they’ve found to track their “net wealth” or anything?

Basically what I want to see is that I’m better off at the end of the month than I was at the end of last. I’d love something as simple as;

In - £1000
Out - £999.97

You’re now 3p richer!

But with pots, which I use a lot, it gets more difficult because transferring £100 to a pot, is technically a -£100 in my transaction list, but I haven’t actually spent it yet.

1 Like
198

I think I set something that at least does the in/out bit, without the change, in mine.

It should be trivial to do with SUMIFs I think.

Gimme 5 and I’ll see what I did.

Edit: Assuming you only want Monzo and not other ‘connected’ accounts of course.

199

Not with Monzo yet unfortunately.

I use YNAB to track everything including Monzo.
This includes property value, mortgage, ISA’s , Child Trust Funds, Bank accounts, credit cards, etc.

I’m dying to get rid of it, but it does things very, very well (for me, at the moment)

1 Like
200

I use the emma app. It has a section for Net worth.