Using a Transactions Query to Generate your Categories Budget

What is the goal of your workflow? What problem does it solve, or how does it help you?
In some categories, the costs vary over time, and may not be easy to predict. A good example would be electricity and gas usage. I live in Wisconsin, so we tend to consume more electricity and gas during the winter then the summer. When it comes to budgeting, how should I decide what to set for each month? I’ve taken to basing it off of past usage. Instead of having to look back at past usage and update the Categories sheet each year, I’ve found a way to automate the process.

How did you come up with the idea for your workflow?
After automating my paycheck category, I looked for other things that could be automated.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
Using a query, we can look in our Transactions sheet for transactions in specific categories that occur during specific months. If you have multiple years of data, you could even get the ‘average’ or ‘maximum’ values that occurred in that month. For my budget, I use the most I’ve spent each month, since I’d rather have left over funds than not enough. I’m using the following formula:
=IFNA(-(QUERY(Transactions!$B$2:$E,"SELECT MIN(E) WHERE D = '" & $A80 & "' AND Month(B)+1=" & MONTH(G$1) & " LABEL MIN(E) ''")),"")

Paste this formula into the first monthly column in your Categories sheet (for me it’s column G). The formula looks to the cell at the top of the column to get the month, and to the cell in column A to get the category (update the $A80 to reflect the row you’re pasting it into). It then looks in the Transactions sheet (in mine Date is in B, Category in D, and Amount in E) for transactions that match that category and month (regardless of year), and chooses the most costly (remember, the transactions are negative, so MIN finds the smallest number, which is the highest cost). It then reverses that number from negative to positive, since our budget uses positive numbers, and if nothing is found, IFNA tells it to leave the cell blank. Note that this formula will find only the single ‘largest’ transaction, so it’s assuming this category has one bill per month.

If you wanted to do the same, but base it on the average instead of the maximum, use this formula:
=IFNA(-(QUERY(Transactions!$B$2:$E,"SELECT AVG(E) WHERE D = '" & $A80 & "' AND Month(B)+1=" & MONTH(G$1) & " LABEL AVG(E) ''")),"")

If you just want to base yours on last year, not multiple years, and/or you have multiple bills in the category that you’d like to total up, you could use the following formula:
=IFNA(-(QUERY(Transactions!$B$2:$E,"SELECT SUM(E) WHERE D = '" & $A80 & "' AND Year(B)=" & YEAR(G$1)-1 & " AND Month(B)+1=" & MONTH(G$1) & " LABEL SUM(E) ''")),"")
This adds up all the transactions in that category for that month during last year.

Anything else you’d like people to know?
This can be used on any category where you want to base your budget on existing transactions, but is most useful on those that aren’t the same every month (those are easy to just type in and leave as is).

Is it ok for others to copy, use, and modify your workflow?
Absolutely, please use and give feedback and suggestions!

If you said yes above, please make a copy of your workflow and share the copy’s URL:
No template needed, all instructions included above.

This is a great simplification for building a budget, @jpfieber. I built something sort of similar with the Budget Builder workflow, but this seems vastly simpler and easy to deploy locally on a category-by-category basis.

Well done and thanks for sharing!

Randy

I tried your Budget Builder and found it very useful, I’m sure that’s where I got some of the inspiration for the solution I shared.

@jpfieber,
That’s a nice one formula solution. QUERY() is very powerful.

This line you wrote in your explanation makes sense.

It reminds me of an old joke:

Q: How many accountants (or budgeters) does it take to change a light bulb?

A: Well, how many did it take last year.

:slight_smile:
Jon

2 Likes

Thanks for this. I currently have a “last month’s spending” column, so this is a nice addition.
Two notes:
You might want to call out that $A80 should be changed to the row number you paste the formula in (and then you can drag down.
Also, your section on using AVG still uses the MIN formula.
(I also added an if type = “Income” don’t make it negative, that way once you paste this across your entire category sheet, the income will be positive.)

1 Like

@yossiea Thanks for the correction, suggestion, and tip!

This is very cool! I live in Vermont and can relate to that seasonality in my utility and heating bills. Looking forward to trying this!