10/10 for this suggestion. Many thanks
I had never heard of Google Data Studio until your comment
It was an absolute breeze to connect it up to Google Sheets and pull in the data from the Monzo Transactions sheet
Creating a report based on it is a bit beyond my comprehension right now, except for the Google Maps plugin based off the Address column e.g.
Yeah it’s really good when you’ve got your dataset finalised and just want to visualise in a different way.
It works best off nicely structured tables.
Some of it’s not as intuitive as Sheets, but a nice addition nonetheless!
Trying to get my head around dimensions, dimension metrics and metrics… Urgh
A reminder to me that this sort of thing was my historic weakest area of skill back when doing Maths!
Give me some code to really finesse instead! 
So been playing around a bit in the sheet today - and I notice that the time of transaction will always be GMT (or I guess more specifically, UTC), which makes sense.
But just trying to play around with transaction data and because of the GMT switch to BST, and back, every 6 months the times of transactions are effectively ‘wrong’ by an hour in summer time.
Just wondered if anyone knows a sensible way to convert into ‘local time’ based on whether or not it was BST or not.
I’m trying to reduce the need for reference lists - but can’t think of a way to do it neatly without.
Any thoughts?
Add a helper column that checks whether the date is between start of BST and end of BST, and if it is take the value in the time column and adjust it accordingly. Otherwise just take the value in the time column.
edit. oh the date changes every year? Then your formula might need to look up the year and then check. Personally I would just stick to a consistent date every year and just deal with the fact that its off by an hour for a couple days of the year.
In a new column, BST - UTC and then add the variance back on to UTC?
Right found the solution!
Basically because the date shifts year to year it’s a bit of a pain. And as far as I know Google doesn’t have any inherent ability to deal with time zones?
So a column for “Adjusted Time”:
=[monzo-time]+(--AND([monzo-date]>=EOMONTH(DATE(YEAR([monzo-date]),3,1),0)+1-WEEKDAY(EOMONTH([monzo-date],0)+1-1),[monzo-date]<EOMONTH(DATE(YEAR([monzo-date]),10,1),0)+1-WEEKDAY(EOMONTH([monzo-date],0)+1-1))/24)
Where the stuff in [squares] is the relevant cell in your sheet.
The formula basically takes the [time] from your transaction, then adds it to the output of an AND statement.
The “–” ahead of and forces True/False to 1 or 0.
This And statement checks if the date is > the last sunday of March (found via EOMONTH and WEEKDAY) in the year of the transaction, AND < last sunday of October.
If that is correct, it returns TRUE, or 1, so it is BST. And then adds 1/24 to the time, which is the decimal equivalent of 1 hour.
Nice, that’s a neat way to automatically find the start and end of BST!
What does – do? (like the one circled below)
Ahh the “–”. Double negative or double unary operation.
Basically, if you’re doing any form of TRUE/FALSE evaluation, the – will force True / False to 1/0.
It’s great if you’re trying to mix operations that return true/false, with any mathematical stuff.
In this case, the AND formula would return TRUE or FALSE - it forces that into 1 or 0, which I can use to add to the time.
If you went without it in this one, you’d need to strucutre the AND formula around an IF statement.
This page explains it well - and has a few useful examples
Damn, that’s super useful and would streamline pretty much all of my formulas haha. One day I’ll go through my spreadsheets applying this method.
I find that an “annoying” thing when you realise the long winded way you did something, the helper columns etc. Don’t need them now!
Has anyone here yet had a play with the “QUERY” function in Google Sheets? You can unleash some real power with it.
It’s a neat little way to do all sorts of filtering / searching of your data - without getting any Pivot Tables out. You can really customise the order and structure of data quite well with it.
Here’s a nice primer:
Anyway, I needed to have one Cell that showed the Latest Direct Debit to [Merchant] in it, and this was a nice neat way to do it without varying degrees of vlooksups and countifs.
It’s often my go to function to avoid a huge mess of SUMIFS. I also used it to merge my old manual data set for non Monzo accounts with the Monzo transaction feed, so my other queries, pivot tables etc all read from one data set.
My next job is to use it with drop down lists to summarise spending at defined merchants.
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:
Sorry - total Sheets newbie here. Loving this powerful tool but it’s almost too powerful for me!
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!
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:
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.
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!
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;
