That’s a very good suggestion actually!
I went one step further and used the IMPORTRANGE function to take the raw data to a separate sheet (rather than a separate tab).
This has been a whole new relearning experience for me. I haven’t used Sheets in a long time as I’ve been using Numbers for the easier data input on mobile devices.
Not going to go too overboard with the compliments for it, but I was actually quite surprised how well I got on fiddling with my Monzo sheet in Sheets on my mobile to use up some time waiting. Not going to make a habit of it, but certainly would not be put off. Used the time to work out filters and the like. Copying down 10000 rows is a bit painful though!
If your employer name isn’t the same every month, but similar (Mine randomises the first 6 digits each month) you can use this
=FILTER(‘Monzo Transactions’!B:B,search(“Employer”,‘Monzo Transactions’!B:B))
Mind linking to where you found your info on how to create a chart etc ? I’m trying to google and so far just getting the general google options to get info for one cell in a different sheet lol
The Sheets Help articles are a really good starting point.
Find your entries that you have tagged by pasting this in cell A2 of a new sheet:
=SORT(UNIQUE(FILTER(‘Monzo Transactions’!E2:E10000, REGEXMATCH(‘Monzo Transactions’!L2:L10000, “#”))), 1, TRUE)
Change the # to a #correct, for example, if you have used tags more widely than I have - I only use them for merchant data corrections
Then paste this into cell B2 of that sheet and fill down sufficiently for all entries in the column A:
=TRANSPOSE(UNIQUE(FILTER(‘Monzo Transactions’!L2:L10000, REGEXMATCH(‘Monzo Transactions’!E2:E10000, $A1))))
Wonder if there is a way to use this new Monzo Transactions sheet to work out the balance of accounts/pots etc. Guess you’d just need to plumb in the starting balances and then add/subtract using the transactions?
As all transactions are there, then it’s just a simple sum for your account, and then sumif with your pot name for pots.
Just note that if you call your pot “Rainy Day Pot” then it adds Pot on again, so it will need to be Rainy Day Pot Pot"
Thanks for this! Are you able to explain the pot balance a bit more? I managed to do a sum of the account, but don’t understand the sumif for the pot. Thanks
I have a summary tab, in cell B5, I have the name of my pot and then in C5, I have this
=-sumif(‘Monzo Transactions’!E:E,B5,‘Monzo Transactions’!H:H)
That should give you the balance of that pot as it is currently.
Great! Thanks very much. That saved me a lot of time.
Wondering if anyone can give me a quick guide to my formula below (suppose to be a formula showing my pot totals for the month)
Currently I have:
=SUMIF(‘Monzo Transactions’!G2:G, “=Transport”,‘Monzo Transactions’!H2:H)
And this shows my total amount in that pot from when i started Monzo (which makes sense)
I tried adding this:
To get it to only show my Month’s worth of spending in that particular pot. However it throws out an error:
Any idea what I’m doing wrong ?
Sorry for the basic questions
The problem there the apostrophe character - once it get’s pasted into the Forum, it changes from “apostrophe” to “left quotation”.
If you replace all the ’ with a freshly typed ’ it should work.
And just a quick note it doesn’t capture any interest in interest bearing pots, so you may need to add that in manually.
Does the interest go to the pot that generates it?
So if I have £500 split between 4 pots, do each of them get 1/4 of the interest? Or does it just go into my main account?
Ah I meant specific Savings Pots that generate their own interest. The interest is paid directly to those pots and it is that that is not tracked.
Assuming New Plus interest works the same as Old Plus interest, the payment should come direct to your main balance regardless of where your £ is. And that payment is properly accounted for!
Ahh amazing. I’ll change that over tonight - cheers mate!
So I tried to change it:
=SUMIFS(‘Monzo Transactions’!G:G,"=Transport",‘Monzo Transactions’!H:H,">="&EOMONTH(TODAY(),-1)+1,‘Monzo Transactions’!B:B,"<="&EOMONTH(TODAY(),0)))
Still no luck - gives out “formula pass error”
I’lll try and do a bit more googling