Hello,
I have a few old accounts that are no longer being used, however, I am still tracking them in my sheet so I can use their value in my Net Worth. I wanted to find a way to move that balance from the old account to the account that I moving that money into, however looking for solutions here I couldn’t find a supported workflow for this (please correct me if a solution exists).
So I decided to use App Scripts to create a little script to do just that. I had little to no experience with this workflow, but used the wonderful Chat GPT to do this, and it has worked in my scenarios.
Script
function consolidateAccounts(fromAccount, toAccount) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("Balance History");
var data = sheet.getDataRange().getValues();
// Determine column indices based on the first row
var colIndices = getColumnIndices(data[0]);
var fromAccountIndices = [];
var updatedRows = [];
// Locate all instances of fromAccount and organize by week for faster access
var fromAccountsByWeek = {};
for (var i = 1; i < data.length; i++) {
if (data[i][colIndices.Account] === fromAccount) {
var week = data[i][colIndices.Week];
if (!fromAccountsByWeek[week]) {
fromAccountsByWeek[week] = [];
}
fromAccountsByWeek[week].push({ row: i, date: new Date(data[i][colIndices.Date]) });
}
}
// Update balances for each toAccount instance
for (var i = 1; i < data.length; i++) {
if (data[i][colIndices.Account] === toAccount) {
var toAccountDate = new Date(data[i][colIndices.Date]);
var toAccountWeek = data[i][colIndices.Week];
var closestFromAccount = findClosestFromAccount(fromAccountsByWeek[toAccountWeek], toAccountDate);
if (closestFromAccount !== null) {
var fromBalance = getNumericBalance(data[closestFromAccount.row][colIndices.Balance]);
var toBalance = getNumericBalance(data[i][colIndices.Balance]);
var newBalance = fromBalance + toBalance;
sheet.getRange(i + 1, colIndices.Balance + 1).setValue("$" + newBalance.toFixed(2));
updatedRows.push(i + 1);
console.log("Updated " + toAccount + " at row " + (i + 1) + " from $" + toBalance.toFixed(2) + " to $" + newBalance.toFixed(2));
}
}
}
// Remove all instances of fromAccount
Object.values(fromAccountsByWeek).flat().reverse().forEach(function(account) {
sheet.deleteRow(account.row + 1);
console.log("Removed " + fromAccount + " at row: " + (account.row + 1));
});
}
function getColumnIndices(headerRow) {
var indices = {};
headerRow.forEach(function(name, index) {
indices[name] = index;
});
return indices;
}
function findClosestFromAccount(fromAccountEntries, toAccountDate) {
if (!fromAccountEntries) {
return null;
}
return fromAccountEntries
.filter(entry => entry.date <= toAccountDate)
.sort((a, b) => b.date - a.date)[0] || null;
}
function getNumericBalance(balance) {
if (typeof balance === 'number') {
return balance;
} else if (typeof balance === 'string') {
return parseFloat(balance.replace(/[^0-9.-]+/g, ""));
} else {
console.error("Invalid balance format");
return 0;
}
}
// Example usage
consolidateAccounts("Mid Term Savings Account", "Savings Account");
Technical Overview
This script is designed to automate financial data processing within a specific Tiller document document. It systematically updates the balance of one type of account (toAccount) by consolidating it with the balance of another account (fromAccount). Here’s an overview of its functionality:
- Sheet Selection:
- The script operates on a specific sheet named “Balance History” within the Tiller document.
- Account Processing:
- The script locates all instances of the
fromAccountand organizes them by the week for efficient access. - For each entry of the
toAccount, the script finds the closest precedingfromAccountentry that either falls within the same week or is the closest by date. - It then updates the balance of the
toAccountby adding the balance from the identifiedfromAccount.
- Data Update and Removal:
- The script updates the balance directly in the
Balance Historyfor each processedtoAccountentry. - After updating, it removes all instances of the
fromAccountfrom the sheet.
- Logging: Throughout its execution, the script logs detailed information about the updates and removals it performs, aiding in transparency and traceability.
Log Example
9:24:08 AM Notice Execution started
9:24:10 AM Info Updated Savings Account at row 330 from $10000.00 to $10000.00
9:24:10 AM Info Updated Savings Account at row 344 from $16923.00 to $39124.80
9:24:10 AM Info Updated Savings Account at row 358 from $16923.00 to $39124.80
9:24:10 AM Info Updated Savings Account at row 362 from $16922.73 to $39124.53
9:24:10 AM Info Updated Savings Account at row 377 from $16922.73 to $39124.53
9:24:10 AM Info Updated Savings Account at row 396 from $16922.73 to $39124.53
9:24:10 AM Info Updated Savings Account at row 408 from $18006.73 to $40208.18
9:24:10 AM Info Updated Savings Account at row 421 from $18006.73 to $40208.18
9:24:10 AM Info Updated Savings Account at row 433 from $18006.43 to $40207.88
9:24:10 AM Info Updated Savings Account at row 451 from $18006.43 to $40207.88
9:24:10 AM Info Updated Savings Account at row 465 from $15506.43 to $37707.88
9:24:11 AM Info Removed Mid Term Savings Account at row: 464
9:24:11 AM Info Removed Mid Term Savings Account at row: 450
9:24:12 AM Info Removed Mid Term Savings Account at row: 432
9:24:14 AM Info Removed Mid Term Savings Account at row: 420
9:24:14 AM Info Removed Mid Term Savings Account at row: 406
9:24:15 AM Info Removed Mid Term Savings Account at row: 395
9:24:16 AM Info Removed Mid Term Savings Account at row: 376
9:24:17 AM Info Removed Mid Term Savings Account at row: 361
9:24:18 AM Info Removed Mid Term Savings Account at row: 357
9:24:18 AM Info Removed Mid Term Savings Account at row: 353
9:24:19 AM Info Removed Mid Term Savings Account at row: 329
9:24:21 AM Notice Execution completed
Setting Up the Script in Google Sheets
To set up and use the consolidateAccounts script in a Google Sheets document, follow these steps:
- Open Your Google Sheet:
- Navigate to the Google Sheets document where you want to run the script.
- Access the Script Editor:
- Click on
Extensionsin the menu bar. - Select
Apps Scriptfrom the dropdown menu. This opens the Google Apps Script editor in a new tab.
- Paste the Script:
- In the Apps Script editor, delete any existing code, or create a new .gs file.
- Copy and paste the
consolidateAccountsscript into the editor.
- Using the Script:
- To execute the script, you’ll need to specify the
fromAccountandtoAccountnames directly in the script’sconsolidateAccountsfunction call at the bottom. - These account names should match the account names as they appear in your
Balance History.
- Save and Run:
- Click the disk icon or
File > Saveto save the script. - Run the script by clicking the play button or selecting the
Runbutton on the toolbar. - The first time you run the script, you will need to grant the necessary permissions for the script to interact with your Google Sheets.
Example Images
Here are the finished results before and after running the script. This should help remove any unneeded accounts while maintaining the same net worth values.
Before
After
Gotchas
- Please keep in mind, that the last values of the fromAccount need to correctly be $0. A lot of times you will close the account without the account balance being $0, and it will use that as the most recent value and propagate throughout your Net Worth. This script does not do that for you, and please do that on your own before manually running the script. In the past, I have done this by manually adding a row on the
Balance Historywith $0 on the desired date.
Please let me know if this is helpful for any of you, or if you have any improvement suggestions.