Transactions affecting Budgets

Ask Anything
1

Hopefully I can make sense out of this so that you can make some sense out of this :slight_smile:

What I’m trying to address here is that I have budgets that are dependent on transactions throughout the month. For example, I have a hobby that makes money. That money varies per month. I want that money to be rolled into my budget. (Three things happens with that money: 20% goes to charity. 30% goes to fun and to put back into the hobby. 50% goes into my regular budget.) Budgets as designed, as far as I know, can’t handle that.

The optimal solution would be that my Categories page can read from my Transactions so that when I get that income and categorize it as Hobby Income, the other 3 categories (Charity, Fun, Paycheck) get updated.

And thanks to ChatGPT I figured it all out- so figured I’d share!

For each category, in place of an amount in the monthly budget cell, I put in the following:

=(SUMIFS(Transactions!$E:$E, Transactions!$D:$D, $A$39, Transactions!$Q:$Q, R1)*0.3)

wherein E is the Amount Column in the Transactions Sheet, D is the Category Column in the Transactions Sheet, A39 is the Category I want to track, Q is the new field that I created to match the date with R of the Month/Year Column in the Category Sheet.

What the above equation is doing is looking for transactions that match the category and month, adding all those rows up, and multiplying it by 0.3 (this is for my “fun” budget). I then do that for my charity budget too and my paycheck budget (both of which start with “X +” because they both have starting points unlike the fun budget which is completely dependent on how much actual income I make from the hobby.

One more thing I had to do to streamline this was add a Google Script to automatically add “MMM YYYY” to Column Q whenever a row was added to the Transactions Sheet. Here’s the code for that:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  
  // Check if the sheet is "Transactions"
  if (sheet.getName() === "Transactions") {
    var editedRow = e.range.getRow();
    var editedColumn = e.range.getColumn();
    
    // Monitor column B (which is column 2)
    if (editedColumn === 2 && editedRow > 1) { 
      var date = new Date(); 
      var monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
      var formattedDate = monthNames[date.getMonth()] + " " + date.getFullYear();
      
      // Set the formatted date in column Q (which is column 17)
      sheet.getRange(editedRow, 17).setValue(formattedDate);
      
      // Set the format for the cell to "MMM YYYY"
      sheet.getRange(editedRow, 17).setNumberFormat("MMM YYYY");
    }
  }
}

I added this workaround as opposed to changing existing columns because I don’t know what columns are used elsewhere and didn’t want to mess with them. It’s possible that you could modify the Month column in the Categories sheet and then use the month column in the transaction sheet, but again, I didn’t want to take chances with messing things up.

This, of course, could work, for anyone with a variable income that budgets based on their income.

Any thoughts, questions or critiques welcome.

2 Likes
2

This is a cool way to dynamically adjust budgets.

I do something similar for my target savings goals on the savings goal tab for my emergency savings goal.

I want the average of the past 12 months spending but only want to keep 6 months in liquid savings so set the goal to change according to those parameters

3 Likes
3

Which sheet are you using for “Savings Goal” tab? Is that a Community Solution?

1 Like
4

It’s a fairly new tiller provided and supported sheet Introducing the Tiller Savings Goal Tracker

2 Likes