How can I see the history of a category in the Savings Budget

Google Sheets
1

I would like to see the ‘history’ of a category in the Savings Budget. For instance, I have a category named Car Repair. I would like to see the amounts for Savings, Budget, Actual and Available (from the Savings Budget) for each of the past six months for Car Repair.

Is there an existing solution for this? If not, where is the data stored for these amounts, so that I could see about creating my own solution?

2

@DannyD , I don’t think there’s an existing solution for seeing this. I can see why it would be useful though.

If I have the time I’ll try to build it out, but if you want to do it yourself here’s where you get the data:

  • Savings: this is the most challenging one. All of the data comes from the Transactions sheet and the Budget Journal. You need to add up all of the transactions in the Transactions sheet that happened before the month in question in that category, then from the Budget Journal add all of the Savings (Type column) adjustments that happened before the month in question for that category.
  • Budget: The easiest one, this is just straight from the categories sheet.
  • Actual: Add up all of the transactions in the current month for that category (from the Transactions sheet)
  • Available: Budget - Actual + Savings = Available

Hope this helps!

1 Like
4

As @matt says, all of the data is there, but pulling it together is not trivial.

@matt is correct that Savings is the hardest part. There are ~26 columns (P:AQ) required in the hidden part of the Savings Budget template to calculate the Savings (in real time) for the currently selected month. You’d essentially need to replicate these queries, lookups and calculations— for each month— to pull this off. It is possible but it will be complicated, take a lot of real estate and won’t be particularly performant (though there is probably some redundancy in those hidden columns such that many months of calcs can be done a little more lean-ly than a single month).

I agree that Budget, Actual and Available are easy— you can just borrow formulas from the Yearly Budget sheet.

Let us know if you decide to go for it, @DannyD. Would be cool to have a Yearly Savings Budget!