You can also set up IFTTT to export all card transactions. It doesn’t cover every transaction type like plus or downloading the CSV though
Does anyone have any experience in taking the monzo CSV and producing projections for 2021? Can google sheets look at a set of data and extrapolate what the data would look like for the future? It feels like there should be a neat little formula somewhere for doing this haha.
I’m looking at my graphs (like the one below) and thinking it shouldn’t be too difficult to visualize the growth for another year.
I guess I could manually figure out the average growth per month and plot that against future dates in a ‘Projected Balance’ column, but that feels a bit tedious. I would rather be able to do a content aware fill for future values (to steal a photoshop analogy).
Edit:
I think for now I will create a column for ‘projected balance’ that does the following;
1 - Looks at the difference in balance for the 12 months leading up to the previous row and divides it by 365
2 - Multiplies that value by how many days in the future the row is and adds it to the last known balance (if its not in the future, it should adopt the true balance)
Edit 2:
So I realise I kind of answered my own question with this post, but would still welcome any thoughts on alternative ways to achieve a projection for bank balances. Here’s where I’m at with it;
Edit 3:
This is turning into a conversation with myself haha. But I’ve thought some more about this and I think what I actually need to do is work out the average change in balance for a given day of the month and apply that to future dates. That should allow for a slightly more accurate projection that better reflects the bumping incline (rather than the smooth incline shown above).
The other issue with my method above is that its self-correcting; so if I start spending more than I should, the projection will just reduce in real time. Which is great for seeing the impact of spending, but not great if I want to track against my projection to see how well I’m doing. So I’ll have to give that some thought too.
3 Likes
I’ve never tested it myself, but I wonder whether the FORECAST function could help you? I imagine you’ll want a more bespoke solution though.
1 Like
I’ve not yet done this with my financial data, but sometimes refer back to this page when making basic forecasts for worky stuff:
Might be some useful nuggets of info if nothing else
1 Like
Thanks @BritishLibrary and @RichardL, I tried out the Forecast function in google sheets but it seems to lack the so called ‘seasonality’ that the excel forecast.ets function can do. So I pulled my data temporarily into excel, ran the forecast data sheet, then pulled the results back into google sheets. I think I’ve now got what I was looking for;
I can now compare my current balance to this forecast and see whether I’m ahead or behind which will be really useful at a glance. I could even set up an IFTTT alert if my balance goes a certain amount under this forecast.
2 Likes
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 
not really even sure where to start.
Any help/links would be much appreciated 
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
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
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:
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?
2 Likes
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 - Google 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:
2 Likes