Account Consolidation Script - Combine Two Accounts' Balances

Google Sheets Show & Tell
, ,
1

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:

  1. Sheet Selection:
  • The script operates on a specific sheet named “Balance History” within the Tiller document.
  1. Account Processing:
  • The script locates all instances of the fromAccount and organizes them by the week for efficient access.
  • For each entry of the toAccount, the script finds the closest preceding fromAccount entry that either falls within the same week or is the closest by date.
  • It then updates the balance of the toAccount by adding the balance from the identified fromAccount.
  1. Data Update and Removal:
  • The script updates the balance directly in the Balance History for each processed toAccount entry.
  • After updating, it removes all instances of the fromAccount from the sheet.
  1. 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:

  1. Open Your Google Sheet:
  • Navigate to the Google Sheets document where you want to run the script.
  1. Access the Script Editor:
  • Click on Extensions in the menu bar.
  • Select Apps Script from the dropdown menu. This opens the Google Apps Script editor in a new tab.
  1. Paste the Script:
  • In the Apps Script editor, delete any existing code, or create a new .gs file.
  • Copy and paste the consolidateAccounts script into the editor.
  1. Using the Script:
  • To execute the script, you’ll need to specify the fromAccount and toAccount names directly in the script’s consolidateAccounts function call at the bottom.
  • These account names should match the account names as they appear in your Balance History.
  1. Save and Run:
  • Click the disk icon or File > Save to save the script.
  • Run the script by clicking the play button or selecting the Run button 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

Before
Before1023×92 13.4 KB

After
After
After1026×65 9.36 KB

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 History with $0 on the desired date.

Please let me know if this is helpful for any of you, or if you have any improvement suggestions.

2

Super cool, @tyler.britton11! Thanks for sharing.