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

What is this witchcraft?! Any pointers on how you merge data with it?

Yep! This is why I haven’t weighed in on any of the formula discussions because I pretty much exclusively use query and Google App Script.

Man… I am so lost with this data now! I’d love a template just to show graphically my spend. I’m playing around with the data but it looks… bad. Also I’m struggling with two things:

  1. The transactions all being -£X.XX which is meaning my sum totals for each category are negative…
  2. Split transactions - is it possible to split these up automatically on the sheets?

Sorry - total Sheets newbie here. Loving this powerful tool but it’s almost too powerful for me!

1 Like

To stop it being minus, change your formula from =sum( to =-sum(

(Or whichever action you’re using)

Ah I was running off a pivot table, which doesn’t give me that choice.

I sense this topic is more for the users who know what they are doing!

1 Like

There’s a couple of threads about the data export - but loads of people (myself included) willing to help out.

There’s also this thread: IFTTT + Google Sheets + Monzo = Then what?

Has lots of other useful bits of data integration and a few Pointers. @lewisp has posted a helpful template too for getting started.

For your questions:

  1. in a pivot table, you can do this with cell formatting - basically force the negatives to show as positive. Let me find an example and come back.

  2. split transactions - it is theoretically possible - it needs a “script” to do, which I haven’t seen anyone attempt yet. It’s on my list to try and work out. If I work it out I’ll let you know!

1 Like

It technically does. You just have to replace the value box in the pivot table builder with something called a calculated value and then put the reference as =amount*-1
(That’s from memory so might not be 100%)

edit: nearly had the name right, its called a ‘Calculated Field’ and should end up looking like this in the pivot table settings;
image

And to do this for formatting, if you go to:

Format > Number > More Formats > Custom

And enter

[red]-0.00;0.00;-

It will invert the values around the way you want. This is only visual mind - but it’s useful for tables etc.

1 Like

Don’t be silly! I’m reasonably handy with Excel, but the two guys above are ninjas!

Don’t be put off, there’s always people willing to help.

3 Likes

I think if I were to rate how good I am at spreadsheets I’d give myself a #VALUE!

7 Likes

Oh don’t start this, spreadsheet jokes are where I excel!

5 Likes

Guys lets stop this. I don’t have time =now() to get into this.

2 Likes

But I do have a joke for you.

How many excel users does it take to correctly format a cell?

Sunday, 01 January 1900

7 Likes

Here’s what I do. It’s a bit convoluted, but simple enough.

First thing is to arrange both data sets so the columns match up and the column headers match (so ColA is the date in both etc). I do this with two queries. For the Monzo data set it was

=QUERY([Range],"select B,D,G,E,H,L")

Which gave me the columns I wanted (date, name, category etc) in the order I wanted them.

I made sure that the credit card column headings were the same as the Monzo ones and then did the same query for the credit card data, with each query on a different sheet.

Then the magic!

In a new sheet;

=query({Range1;Range2},"Select * where Col1 is not null Order by Col1",1)

So it makes an array from the two ranges, then selects all the columns, but not any blank rows, and sorts them by ColA (date column). The 1 at the end tells the query that there is a header row.

I then protected and hid the sheets, so that the only sheet I can input data is the original credit card sheet. Whenever I do this, or whenever Monzo update their data, the final query updates, and I then run other queries and pivot tables from this.

Hope that helps!

2 Likes

You can do calculated fields in Pivot Tables which would accomplish the same thing.

Edit; Sorry, you’ve already had an answer. :+1:

Ahh gotchya - so your first Query filters your data in the way and order you want them.

Then number two selects for “anything” - * - but not blanks.

So the critical part of the 2nd query is that the arrays have to both be structured in the same order.

Theoretically then, you could use this to merge a Joint Monzo CSV export to the live Personal export.

Sure.

I’m not sure if you’d be able to tell which account the transactions came from (I don’t have a JA, so haven’t seen the output), if that mattered.

I don’t know if they ‘have’ to be, or what happens if they aren’t, but it seemed sensible that there was less opportunity for error if they were.

That’s a good point, there’s nothing in the raw export data that I can see that could be used to distinguish between personal and joint account data.

Hello - sorry if this is slightly off-topic but as this is an active thread, I thought it would be better to add in here rather than start a new thread for what is a pretty simple question that will probably only need one answer.

If I export to Google Sheets and then go back into the app and change the categories of a bunch of historic transactions (into new custom categories), will all of the historic data in Sheets update to reflect this? Or does the “live” data updates just incrementally add new transactions?

Essentially I’m trying to figure out if I ought to go through and do my category changes before I export?

EDIT for clarification and a bonus one for a typo