Useful uses for Monzo Plus Live Data Export? Share your tips!

My current charts. Going to implement JA as that’s where all the bills get paid from.

If any of you need help, feel free to hit me up.

3 Likes

These look great. I’m curious what method you used to split the data across the last three months?
When I did it, I created a helper sheet that streamlined the data to give me balances against dates. Then I added a column that checked whether a particular dates’ month was this month, last month or the month before. Then, in a pivot table for that sheet, I had the month-checking column set to be the ‘column’ and the balances set as the values to give me something like this;


Which I could use to create graph like this for the account balances and pot balances for my current and joint accounts;
image
I’m sure there’s a more straight forward method though…

1 Like

=TEXT((A2),"mmm")

Or you could do it with =>start and <=end etc.

That will give you the first three chars of the month name. I have my pay dates to period as it’s not quite even enough otherwise. This way it’s obvious for when I turn into a payday millionaire.

That’s pretty much how I did it
Using a helper sheet to list daily balance, income and outgoing’s
I then used
=FILTER('Monzo Transactions'!B:B,search("employer",'Monzo Transactions'!E:E))
to get a list of paydays. From that I used a VLOOKUP to grab the last 3 paydays and then a payday column to mark whether it was “CURRENT”, “LAST”, “LAST2” or “OLDER”.

From that I made a pivot table to bring in dates vs current balance/expenditure

As for the expenditure charts, I made a chart that organises it all by month

Still very much a WIP, but coming along nicely I think

5 Likes

I really need to crack on with the dashboard I want. I’ve got so many things I want to do with it but I’m being incredibily lazy.

Cool, yeah remarkably similar to my approach.
Though using paydays to identify the last three months is much slicker than what I do. Definitely going to steal that method. The way I do it breaks around new year because it doesn’t cater for the fact that sometimes the previous month is last year…
Here’s a snapshot of what I’m doing;

One thing it looks like you might be missing is tracking overall pot balance in addition to the account balance. If you’re curious, I do this with more helper columns that look like this;

.

There’s more info and my template here;

Yeah, Pot and overall savings balance is something I’m looking to add next along with JA stats.
I’ve got savings accounts with other providers (LISA, Stocks and Shares, plus a few others), which I’m interested in seeing if I can automate in any way.

For the JA, I’m currently resorting to importing a csv that I exported from the summery tab. It seems to have the same information as the auto export from the main account, so I should be able to do everything with the JA data, with few changes.

I’ll definitely be taking a look at your sheet

EDIT: Your “Current Month” if you just type true,false instead of "CURRENT","FALSE" it will treat the cell as a Boolean value instead of text. Meaning you can do IF(x, "Do This") rather than IF(x=y, "Do This")

2 Likes

This is all very clever.
I wouldn’t know where to start :joy:

1 Like

I love all these tips, and I am learning so much :slight_smile:

Is there a way to use the FILTER formula, to find all the pots, group them individually and then display the value of each pot?

I know how to do that by using SUMIF by entering the pots name manually, in order to find it all, but it would be great if somehow filter can do it all automatically.

So lets say if you create a new pot, the filter function would then auto populate the new pot below all the others.

I hope this make sense. Or if you have another solution to it, that would be great :blush:

You could do. I’ve got something similar set up for merchants, if I spend at a place I’ve used before, it will add it to the list, if I haven’t been there before, it gets added to the bottom of the list. Which is basically what you’re looking for but in pot format.

I’m not sure I 100% understand this formula because I had to google and then adapt, but…

You need a index match along with a countif, so that if it already exists, >0, it isn’t added.

My way really isn’t that tidy as I have a few helper columns to get there where it can definitely be made neater.

I have copied my data out so these probably won’t line up like you’d expect…

This will get the name of the merchant if I’m using my card. You could just adjust this to look at pots.

=if(Input!C:C=“Card payment”,Input!D:D,"")

And then because I only care about 2020

=IF(Input!J3=“2020”,E3,"")

And then it gets more complex. As they can be rows and rows apart, this condenses them

=IF(ROWS(F$2:F2)>COUNTA(F:F),"",INDEX(F:F,SMALL(IF(F$2:F$10000<>"",ROW(F$2:F$10000)),ROWS(F$2:F2))))

and then the final stage

=INDEX(Helper!$G$2:$G$5000, MATCH(0,COUNTIF($B$1:B2,Helper!$G$2:$G$5000), 0))

:man_shrugging:

I’d also recommend playing around with Google Data Studio - you can connect directly to a Sheet and build a nice snazzy dashboard with your data :raised_hands:

1 Like

I might take a slightly different approach to @revels here.

If you pair the UNIQUE and FILTER formula together, you can get a list of all your pots. I use this formula in my Reference sheet of my dashboard so I can get lots of lists I can use elsewhere.

=unique(FILTER('Monzo Transactions'!E:E,'Monzo Transactions'!D:D = "Pot transfer"))

(this is assuming your data is structured in the same was as the export sheet - E:E is the Pot Name columns, D:D is the Payment Type column).

This will list all your pots that you have ever moved money to, in order they first appear in your sheet.

You then want a formula in the cell next door that is along the lines of this from Revel’s post - copy that down the sheet a bit, and it will always show a number if a pot exists. If [cell] <> “”, then…

It will update live, so every pot you’ve ever made will show up

2 Likes

That’s very clever and much much better than my way of doing it!

I managed to get the names of the pots, which is exactly what I wanted, so thank you for that :slight_smile: but now I have difficulties adding the amount in each of the pots in the cell next to their name.

I use the same column structure that is on the original Monzo Transaction sheet.

I wish I had the same knowledge that you guys have :blush: but I am learning so much.

Once you’ve got the list from Ben’s formula, then you just need a SUMIF next to it

So if your list is on a separate sheet and starts in A1, paste this next to it

=-sumif(‘Monzo Transactions’!E:E,A1,‘Monzo Transactions’!H:H)

Got it to work, I was doing exactly that formula, but somehow I kept getting an error. I thought it was gonna be that easy… :blush: seriously, thank you so much!!! super helpful.

1 Like

I don’t recommend you do that sumif if you’ve changed it from pots to merchants :sob: :sob: :sob:

I must own at least 3 Tesco scores by now :sob: :sob: :sob:

3 Likes

My most valuable use of Data so far: Emoji Frequnecy:

The frequency that every unique emoji combination has appeared in my transaction history since 2017 (for emojis appearing more than 10 times).

There’s a bit of a weird thing going on where some emoji are interpreted as unique data (two coffees) but share the same data, or have two different values (two apples).

Either way, I get the tube too much, and I drink too much coffee and beer.

Wonder what this looks like in the 4 months pre-lockdown vs 4 months post.

Also I think I need to make a “Useless uses for Monzo Plus Data Export” thread…

6 Likes

Ok so I did the pre-post lockdown emoji. Interesting pattern!

Not a single tube journey.
Grocery habits changed significantly.
Coffee habits dropped also.
Beer, about the same…

I’m also a bit concerned that this emoji appears in my top 10 list! :eagle:

6 Likes

This should be in “Year in Monzo” :laughing:

3 Likes