Manual Account Balance Entries

[bobcat]

Hello good people!

I’ve been using Tiller for several months now and so far, I’ve been able to do a lot of customization with it to fit my needs. I promise I’ve tried searching the community page for another post with this similar situation but I seem to have come up short so am posting here.

I currently have a Loan/Mortgage Amortization schedule on a sheet in my Tiller workbook and a line-item on the Balance History sheet that looks up the current Mortgage balance based on today’s date. (No, the mortgage is not with an institution that I can auto-import into Tiller). The date of the Balance History line-item uses a TODAY formula so that my net worth will always reflect my mortgage balance at any point in time.

The issue I run into handling it this way is that it skews the change in Net Worth when comparing to a previous date since there are not multiple Mortgage balance entries on my Balance History sheet.

QUESTION
Is there a way to automate the addition of a new Balance History line-item on the last or first day of each month so that I’ll have monthly balance snapshots for my Mortgage rather than the single line-item using the TODAY function?

Thank you all in advance for your help!

[Mark.S]

This Apps Script function is pretty close to what you want. It worked for me.
Extensions > Apps Script

Probably would want to modify the:
var sheet = spreadsheet.getActiveSheet();
to something like:
var mortgageSheet = spreadsheet.getSheetByName('Mortgage');

and adjust the script to match your sheet.

function MoveToBalanceHistory() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var balanceHistorySheet = spreadsheet.getSheetByName('Balance History');

  // get number of rows with a valid date
  var dates = sheet.getRange('B3:B').getValues();
  var numRows = dates.findIndex((row) => !row[0]);

  // Insert numRows at the top of Balance History sheet
  balanceHistorySheet.insertRowsBefore(balanceHistorySheet.getRange('2:2').getRow(), numRows);

  // Copy Account balances to Balance History sheet
  sheet.getRange(3, 1, numRows, 15).copyTo(balanceHistorySheet.getRange('A2:O2'), SpreadsheetApp.CopyPasteType.PASTE_NORMAL);
  balanceHistorySheet.activate();
}

Then, create a Time-driven Trigger that executes the script on the day of the month you want.

How did you get your balance history from Mint into Tiller?

For anyone in the same situation as me, I created a few scripts to help with this process. I had 89 accounts that I needed to import the balance history for, so after downloading them from Mint using the tools mentioned, I still needed to format them and copy them into the balance history sheet. Here are the steps I followed Create the manual account with the Tiller extension if needed Import the balance history into a new sheet Run the “PrepareMintAccountBalance” automation while on the new s…

[bobcat]

Thank you for your help Mark! I use Apps Script a lot for other things and am not sure why I didn’t think about going in that direction. I’ll check out your script and see if I can adapt it to my needs. Thanks again!

[KyleT]

Hi @bobcat - I like your idea and now I’m contemplating something similar for a car loan. As of now, I update the monthly balance each month manually and paste it into the Balance History manually. But it would be nice to achieve your suggestion of a no touch solution since the balance is known ahead of time.

Your question got me to thinking… Apps Script is a good idea and also came to my mind as I’ve been considering it as a method to more quickly load my numerous manual account balance updates on a monthly basis.

But I’ll throw another idea out there as a more low-tech solution. Since you will eventually have line item entries in the Balance History for each payment milestone on the loan, you could just pre-load them all at once at the bottom of the sheet, and make them “activate” only when they come due. Similar to how you’re using TODAY(), you would compare the date of each payment with today and then turn the data on when it’s time. Below is a screenshot of the idea where you can see payments in 2023-2024 are active and the rest are sitting waiting (empty of data, but with formulas underneath):

I think my Balance History sheet is an older version so you may have different column headings, but the idea is that the date and most of the data only becomes visible when it’s time. When a sheet Fill runs via Tiller, a sort is going to occur and the relevant “active” lines are going to take their place in the main data field and the others will hang out at the bottom. I did test it on a live sheet and the Tiller fill will delete the line if you don’t have some data somewhere in it (even with formulas present), so that’s why I have a zero balance for the inactive ones.

I haven’t included all of the formulas here unless you want to see more, then I can share an example sheet. But for the Date column it looks something like this:

=LET(pmt, XLOOKUP(O9818,AmortSch!$G$2:$G,AmortSch!$E$2:$E), IF(pmt<=TODAY(),XLOOKUP(O9818,AmortSch!$G$2:$G,AmortSch!$E$2:$E),IFERROR(1/0)))

I’m looking up the date and balance columns in the Amortization schedule where I’ve placed the same identifier as shown in the last column of the screenshot. I believe that a solution could be made without this XLOOKUP by encoding the dates and balance formula within the Balance History formulas, but the extra column does give an added benefit of quickly locating those rows.

I haven’t tested this extensively but I expect to try it out with my loan. I’ll report back if I find any issues.

[bobcat]

Kyle - great thinking and this is the road I initially went down (pre-loading all of the future balances by date) but I did not think of writing a formula to activate them only when the balance date is <= TODAY…nice idea!

I had backed off of this approach because (I didn’t have that formula) the dates would mess up some App Sheet graphs I’m using in my mobile app that simply look for the “most recent” date in my sheets to pull current balances. If I implement this formula approach to activate based on Today’s date, I think I may be able to make that work since the date would not be populated in the table until due.

I have a script working now that inserts 3 lines for 3 different balances I want populated on the 1st of every month but I’m a big proponent of the KISS approach and may revert back to your solution…the simpler the better in my opinion. I’ll play around with it the next few days…thank you for your suggestion!

[KyleT]

Hi @bobcat - Great, let us know how it goes if you end up reverting. Speaking of keeping it simple, I realized that the formula that I provided can be simplified by reusing the LET variable:

=LET(pmt, XLOOKUP(O9818,AmortSch!$G$2:$G,AmortSch!$E$2:$E), IF(pmt<=TODAY(),pmt,))

This applies to the date column. The balance lookup would presumably be in a different column and need an additional XLOOKUP or VLOOKUP.

I’m not sure if these will weigh on performance of the sheet, but you could eventually do a copy paste values of the ones that have come to pass.

I’ll report back if I find any issues when I finish implementing it myself. Good luck!