IFTTT + Google Sheets + Monzo = Then what?

I might actually get time this weekend to look at putting myself a sheet together or at least start the basics.

Not really wanted to get my head down into it but need to make a start or I’ll never do it.

1 Like

Hey all,

Apologies if this has been asked already, but is there a ‘dummies guide’ to setting up my own dashboard that I could follow anywhere, specifically for the Monzo Plus export? I’ve decided to try and dedicate some time to creating one (been using a rough budgeting spreadsheet with no real data behind it until now), but to be honest I’m completely lost :sweat_smile: not really even sure where to start.

Any help/links would be much appreciated :slight_smile:

I have a template that I’ve been working on, just waiting for the okay to post it up for everyone to use.

Hopefully it’ll be this week!

What sort of thing do you want it to show you?

3 Likes

In addition to what @revels said, there’s also a few threads / comments in the forum with various bits of advice around setting them up. More tips, tricks and ideas of how to’s than full on Dashboards.

I can link to a few useful ones if that helps, but there’s a lot of good suggestions that’s come up in this current thread since about July time if you fancy some light scrolling.

Edit: Also this thread has a lot of top tips in: Useful uses for Monzo Plus Live Data Export? Share your tips!

Ah amazing! Main thing I’m after is a starting point/template that I can then tweak to my needs. Main things I’m looking to track are:

  • How I’m spending against my budgets month on month
  • Forecasting for savings pots based on avg amount going in monthly
  • If possible, tracking the monthly amount of automated savings going into other apps (Moneybox, Plum, Chip, etc.)

Looks like a few of these might be covered in the thread already so will see what I can do with that :slight_smile:

1 Like

Can anyone tell me how to pull my a list of transactions from a particular category along with date and amount? I think I need a vLookup maybe?

You can do it with unique/filter

I’ll work out the formula for you in the morning, I’m not quite at the stage where I can write it off the top of my head!

So basically a filtered list of, say the “Grocery” category, that would output something like:

Date Merchant Amount
28-Jan Sainsbury £3
29-Jan Tesco £2
etc etc

The simple way is just with FILTER, but it will show data you might not want, e:g

image

In Cell B2, enter your category you want to filter.

In the yellow cell, A4, enter:

=FILTER('Monzo Transactions'!B:H,'Monzo Transactions'!G:G=B2)

(This is assuming you have a sheet called Monzo Transactions, and it’s structured in the same way as the data as exported)

If you wanted you can add multiple to the end of this.

If you want just the Date/Merchant/Amount, (i.e. data from non adjacent columns in the raw data) you need to use QUERY, which I can share an example of later!

1 Like

For a =QUERY version, it’s a bit more complicated to explain but here is a formula that will give you a simple output:

=query(FILTER('Monzo Transactions'!B:H,'Monzo Transactions'!G:G=B2),"select Col1,Col4,Col7",-1)

Just to break it down:

First the FILTER formula - this filters a data set based on conditions you give it;

'Monzo Transactions'!B:H

is the section of your data table that contains Date through £ amount.

'Monzo Transactions'!G:G 

is the column that contains “Category”

 'Monzo Transactions'!G:G = B2

B2 = the cell where you type the Category. This part of the formula is the condition to Filter on, you can add additional conditions in the same format to the end of the formula.

All that will give you a table that looks like the first comment I posted.

Then, the QUERY formula - QUERY allows you to do more complex selection, filtering, sorting and display-ing of data based on what’s called Google Query Language, I think. It’s a bit less inuitive but this is what is happening here:

First, we’re passing the data output from the Filter formula into the QUERY one, (so the table from my first comment is our data set).

Then,

"select Col1,Col4,Col7"

This is the Google Query Language part; what we are telling the QUERY function to do, is Select (show) the data found in Column 1 (Date), Column 4 (Merchant) and Column 7 (£) of our original data table. You can also add in a “sort by Col4”, etc, to sort the data set.

All that combined will give you a data table like this:

image

1 Like

Thanks Ben, that really helped I now have a nice chart showing my wages over time.
Is it also possible to do a negative search to bring back all categories excluding X?

1 Like

I think if you change this part to <> B2

That will exclude it

1 Like

And to add to what Revels said, if you wanted to say search by Merchant instead of Category, you can do that too by replacing

 'Monzo Transactions'!G:G = B2

for E:E instead (for Merchant), or any other column you might want to filter by

To add another way to skin this particular cat, in addition to what @BritishLibrary and @Revels have said, adding a Slicer Filter charts and tables with Slicers - Docs Editors Help could work well for you, especially if you just need a quick check on how much is being spent on a particular category, or at a particular merchant within a date range.

They have the advantage of not requiring complex formulas, but do use them on a copy of your data, otherwise confusion can arise. They are easy to set up, easy to configure, and easy to switch off.

1 Like

Did I see someone on this thread share a mortgage repayment spreadsheet a while back?

If so, any chance of resharing it? I’m keen to politely stealing in

Very American-based, but it may be easily adjusted for GBP:

https://www.mortgagecalculator.org/download/excel.php

EDIT: There’s also this:

1 Like

I don’t recall it being shared if this is what you’re referring to?

That’s the one!!

@Lewis_P any chance of sharing it or making it public? I would find it very useful!

1 Like

Me too :raised_hand:

1 Like

I made a mortgage overpayment calculator myself recently too.

If anyone’s interested I can put it into Google Sheets?

1 Like

If it’s useful to any one (@Jonny2 / @Ordog ?) - I’ve put my version of something similar on Google Sheets here:

Hopefully you can save a copy of that?

It’s doing two things - 1 will show you the difference if you overpay each month in your total paid / when you pay off the mortgage (first sheet has a comparison graph of Total £, second sheet just has data with headings that might be useful.

2nd thing - is - assuming most people will re-mortgage at the end of a fixed rate period - you can enter some (hypothetical) rates for your future mortgages, and it will adjust the rates etc as you enter each mortgage period.

If you just want a comparison of a single mortgage, enter two rows in the main table - 1 with the fixed rate period / %, the 2nd with the total remaining term in Months, with the standard rate.

The charts went a bit wonky with an upload to Google but hopefully that can be useful to someone? Wasn’t really intended for sharing so please excuse the slightly dodgy set up and formatting!

The “no overpayment” tab needs no input - it will take it all from the first sheet :slight_smile:

Shout if any of it doesn’t make sense and I can try and explain inputs better!

Edit: Hmm the main graph broke, will see if I can fix but it’s currently missing the mortgage total value as it decreases over the term, which was on the 2nd axis in Excel. Happy to share the excel file direct if anyone wants that.

3 Likes