Personal Finance with Google Sheet: current and historical net worth tracking with Script

You can build a simple historical net worth / investment tracking tool with Google Sheets without providing your private financial credential to online services.

For the historical perspective, you will need to add some simple script to automatically copy the information to the Historical tab. Please note that I added light script below to help you get started.

• Level of complexity: Medium - need to add/modify script and how to use charts.
• Knowledge of spreadsheet: High
• Time: 3 hours

High level structure

Today tab

This is your current financial picture view. An example structure is below.

Date:                                2022-07-23

Assets                        

    Investment A                100,000    // Auto Calculation based on the Investment tab - direct ref. is ok

    Investment B                100,000    // Auto Calculation based on the Investment tab - direct ref. is ok

    House A                        500,000    // Manual input once in a while

Total Assets                      700,000

Liabilities/Debts

    Mortgage for House A    300,000    // Manual input once in a while

    Line of Credit A              200,000    // Manual input once in a while

Total Liabilities                  500,000

Net Worth                           200,000

Investment Tab A

Create a simple structure to track your TFSA, RRSP, or general investment. You will use either the Google Finance function or the yChart to get your stock/mutual funds price. Note, to retrieve mutual fund price in the Toronto Stock Exchange, you will need to use yCharts

Symbol     Exchange        Quantity        Price     Total

GOOGL    TSX                    500                 $100    $5,000

MS             TSX                  500                 $100   $5,000         

Total Investment                                            $10,000

To retrieve the stock price automatically, use this formula: =IF(ISBLANK(C3), 0, GOOGLEFINANCE(IF(ISBLANK(D3), C3, D3&":"&C3), "price"))

• C3 is the symbol column
• D3 is the stock exchange column

For Toronto Stock Exchange and Mutual Fund only: to retrieve the mutual fund from the Toronto stock exchange automatically, you will need to use this formula and change from TSX to TO: =IMPORTXML("https://ycharts.com/mutual_funds/M:" & C13 & "." & D13, "/html/body/main/div/div[2]/div/div/div[2]/div/span[1]")

Create as many investment tabs as necessary to track your family investment.

Historical Tab

Create a simple format data table. It could be as simple as Assets, Liabilities, and Net Assets or could be complex with all the high level investment detail. It's your choice to see how much historical data you want to capture here.

Example:

Date                Assets            Liabilities        Net Worth

2022-07-23    700,000            500,000        200,000

You can insert a few Google Charts here to show the trend for your assets, liabilities/debts, and net worth.

There's no manual update required on this tab... simply set up the structure and use the scripts below to run.

Scripts

Now, you can create a script and setup to automatically copy the data from Today tab to your historical tab. 

You can set the trigger for the script to run every time the Google Sheet opens. You can also run the script manually using the Macro function. 

The script is setup to automatically override the entry if the same date has been recorded previously.

Disclaimer: I'm not an expert on Google Sheets/Javascript and this is the first time I tried Google Sheets. Test and verify first. I'm not responsible for any issue :). 

I created the script and the entire tracking structure in a few hours and it seems to work well for my use case.

function findRow(colNumber, searchTerm, targetSheet ) {

  const sheet = targetSheet; // OR GET DESIRED SHEET WITH .getSheetByName()

  const dataRange = sheet.getDataRange();

  var values = dataRange.getValues();

  // remove all other column values - keep only 1 column

  var values = values.map(function(value,index) { return value[colNumber - 1]; });

  let index = values.flat().map(Number).indexOf(+searchTerm);

  var columnCount = 1;

  var columnIndex = index % 1;

  index = ((index - columnIndex) / columnCount);

  return index >= 0 ? index + 1 : values.length + 1;

}

function CopyValueToHistoricalSheet() {

  let spreadSheet = SpreadsheetApp.getActive();

  let sourceSheet = spreadSheet.getSheetByName('Today');

  let targetSheet = spreadSheet.getSheetByName('Historical');

  let dateColumn = 2;

  let dateValue = sourceSheet.getRange('Today!C1').getValue();

  let newRowCount = findRow(dateColumn, dateValue, targetSheet);

  let hInvestmentCol = 3;  

  let totalAssetCol = 8;

  let mortgageCol = 9;

  let totalLibCol = 12;

  let netAssetsCol = 13;

  spreadSheet.setActiveSheet(targetSheet);

  sourceSheet.getRange('Today!C1').copyTo(targetSheet.getRange(newRowCount, dateColumn),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

}