Sort of related, I have a graph that tracks when I spend on lunch (for two), how much I spend, and where;
2 Likes
I love the consistent subway cluster around the £11 mark, at 12:15ish and 13:15ish! Guessing that’s your daily haunt?
Yeah, we either break for lunch at 12, or 1 and subway is about 15 minute walk away so that check out haha! Seeing this graph a while back caused us to change our habits a bit since its clear that if we have lunch pretty much anywhere else we spend less. Also this graph gives a clue as to how much ‘eating out’ money we’ve saved during lockdown!
2 Likes
Out of interest, any chance you could give a hint as to how you set that graph up? Trying to recreate a similar theme - but not sure the best way?
From the chart it looks like you’re not aggregating any time - probably being dim but I can’t figure how to get my data in a form that would work 
Sure, in my case the trick is that I tag my lunches with #lunch
Then I have a pivot table with the following settings;
And the following filters;
Without these you just get a table of all transactions, split across merchant and time. I personally filter for just workdays, and just the last 2 years. Then I filter the notes to contain my lunch tag. You might be able to achieve something similar by filtering by category.
From there I create a scatter graph with Time as X axis and the merchants as the series.
2 Likes
Amazing Thank you!
@Lewis_P - any idea why after a certain date - the Helper Sheet has the exact same data?
This lasts all the way until today! So two years at £2,034… I’m using the personal tab rather than the joint
1 Like
Hi @Coysh
The balances assume that if there is no data for a particular date (and if that date is in the past) then the amount hasn’t changed and it will display the same amount as the previous cell. So my guess is that your data in the raw data sheets is perhaps misaligned or incomplete or something.
Also, to get the most out of the graphs, I suggest putting your personal account data into the joint account sheet instead. You’ll need to tweak the formula in columns R and S of the helper sheet so that they don’t half the amount (look for and delete the /2 in the formulas).
Take a look in the tab that imports the Monzo data - I think the helper cells in there stop about row 3,000. They’re hidden in columns A onwards.
When I was playing with the template I had the same thing - fixed easily enoug though
Ah yeah! That sounds like its the problem. Those sheets don’t cater for enough entries.
@Coysh. To fix open the raw data sheets, select a row of the helper formulas (that may be hidden within a group), then click and drag the blue square all of the way down to the end of the sheet (see below);
If the sheet isn’t large enough, you may need to add more rows, then repeat the steps above.
2 Likes
Turns out all the formulas in columns A-L ended on row 3,000 which is where November 5th 2019 was. Fixed by expanding the formulas downwards
EDIT: ^Seems like I worked it out slower that you both!
2 Likes
You might also find all of the pivot tables in the hidden sheets need their reference updated to read past row 3000.
What’s everyone’s favourite chart/graph?
For me this one is the most valuable and useful;
It shows me the state of my joint account balance (green) compared with last month (grey) and the month before (dotted grey). This gives me an easy reference to tell how I’m doing and whether I’m in line with typical spending for the month. I guess its reminiscent of the old pulse graph.
Back in the days before covid, when I was just a wee lad, I found this weekly lunch spend graph the most valuable as it changed our eating habits and reduced our typical joint lunch spend per week from £50-£60 to more like £40 (still a crazy figure that we want to bring down further);
I also have a joint savings tracker that gives me a good indication of the rate at which we’re able to save at the moment;
3 Likes
Anyone else had any issues with the sheet updating over the weekend?
The sheet doesn’t think I spent any money on Saturday or Sunday, but a direct debit that went out this morning is on my list
Mines worked fine over the weekend.
I’ve had a similar problem. I have one pivot table which references an unlimited range A:F, and another one which I can’t seem to make unlimited. Whenever I change the range, it reverts back to A:F7054, which should give me a couple of extra years, but isn’t ideal. Especially as in a couple of years, I’ll have forgotten all about it.
Just tried a few of mine / setting up some new ones - all of mine stay as the unlimited reference.
Even if I go back and change one that’s set up as a range, it doesn’t ‘force’ it back.
Can you try recreating the Pivot from scratch?
1 Like
Ah, the Sheets version off turning it off and turning it back on!
Thanks, that worked perfectly. 
1 Like
So here’s my super basic dashboard!
(I’ve just hidden some of the numbers)
Most of it is obvious what it is. Yellow boxes are my pots.
My bill pot to make sure there’s enough in there, if I just dismiss a notification about a direct debit going up or something, the green box will go red if there’s going to be less than £5 left in there.
Sounds really obvious, but one way I improved my spending is tracking the amount of days I don’t spend anything and also the amount of transactions I make under £5. A drink here, sweets, snacks I don’t need etc etc. Just little purchases but can easily be £100 a month!
My budget tracker. I give myself a budget for the month for fun spends, so basically anything that isn’t essential and here I can see that I’m being sensible and not too PayDayMillionaire with my money.
Then my spend per day in a bar chart.
I hate that it looks like a 5 year old did it with a very limited number of colours, but it’ll do for now!
4 Likes