This is interesting, I’m trying to work out how exactly that would work. Would there be a way to make it look at a certain set of pay dates depending on the month? In my mind this still involves updating the formula to look at different pay dates every month, but I’m not sure I’ve fully understood either
I’m going to have a proper look at this very soon, when I can actually work out how I want this data to be structured for me… But some initial use cases for me:
- A set of category based searches that look at Category x Month in a table (INDEX/MATCH) to look at my monthly spending habits (and plotted).
- Same, but for Merchant.
- Integration with an IFTTT trigger - “When Cell is Updated do X”… With a few calculations to do things like… Sweep my leftover balance on PayDay, or top up a Pot when it runs low, etc.
- Track my average balance similar to the Pulse graph, but including Pots too.
- Calculate a “burn rate”, or, how long, my current balance could maintain my average spending for.
I would set up a table
P1 - 1/1/20 - 27/1/20
P2 - 28/2/20 - 29/3/20
Then where you have the categories, I’d have P1 P2 etc across the top.
Then you can sumif cat, your pay dates etc.
I’ll post some more with some formula screenshots of how I have mine in the morning if it will help.
That would be really helpful, thank you. I’ve already updated the categories in my formula to refer to a cell rather than typing out the category which makes a lot more sense
It all depends what you want to see really, but if the data is there, you can pick it out.
On my Monzo Transactions Google Sheet, I have created two tabs:
- Main (Regular monthly costs eg. direct debits): This is so I can look at a quarterly view and compare it annually to see what areas are getting too expensive
- Spending (Budgeted spending each month eg. Eating Out): This is so I can see what I spend my money on each month and whether it returns the perceived value to me in whatever way.
With the custom categories I can now automatically pull these dashboards without having to input it manually, all I have to do is tag transactions in Monzo.
The ones which denote a Monzo category have the emoji.
Really cool. Thanks for sharing.
I’ve created a template that will allow you to have the following graphs;
Balance comparisons for the past three months (including personal acc., joint acc. and combined):
Joint account balance comparisons for the past three years:
All time balance split by personal and joint (inc. pots):
Spending breakdown per month, split by category:
And a few others. More details including a tutorial on how to set it up can be found in my other post here:
I’ve had some success using the ‘workday’ formula. Which you can use to calculate the last working day of the previous month, then do a count to today’s date to see how many days its been since your last payday then sum the values for that many days back in time. It gets complicated if you want to factor in bank holidays but its doable with a separate lookup table of bank holiday dates. Also breaks for the days between pay day and the end of the month. Check out the template I mentioned in my comment above for an example of using this formula.
Just multiply the figure by -1 instead. So the formula would be;
This checks whether the value in B3 is zero, if it is it remains zero, otherwise it multiplies B3 by -1
You really want to avoid having to manipulate the raw data to get the analysis you want.
You could streamline this by swapping ‘Entertainment’ to a cell reference that contains the word Entertainment e.g. A3 in your table. This is a more versatile approach that means that if you ever want to sum a new custom category, you can use the same formula and add a new row for that new category.
Do you always want your search to run Actual Pay Day to Actual Pay Day (vs, say, 29th of each month?)… And is your company name always consistent in your feed? If so…
You need a Helper column on a reference sheet, but if you use the Formula FILTER, you can search for “Company Name”, and return each date that you match it.
In cell M2 (or wherever, just adjust formula) enter:
=FILTER('Monzo Transactions'!B:B,'Monzo Transactions'!E:E=M1)
And enter your company name in cell M1, and this should return all the dates where Company Name appears.
You can then use that list to be your end of Month date in your other columns.
Same here, and that would be my biggest advice for starting out
That way you can be sure that the source data is left intact, will not get broken by experimentation and will continue to update as expected
Having linked across the columns I need, in the order I want them, to another sheet I can then tinker to my heart’s content
Thus far I have been working on counting and sorting my merchants, which has dredged up a fair few historic merchant data sins to work on
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?