Skip to main content

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


    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


    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("" & 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.

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.


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(colNumbersearchTermtargetSheet ) {
  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 =,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(dateColumndateValuetargetSheet);

  let hInvestmentCol = 3;  
  let totalAssetCol = 8;
  let mortgageCol = 9;
  let totalLibCol = 12;
  let netAssetsCol = 13;




Popular posts from this blog

New Site

Testing the new site! The focus will be on Project Portfolio Management and Software Development. Hope everyone's having a great day!

Checked Task Pro Instruction (BlackBerry 10)

  Checked Instruction I just started working on this page, please send me comments and suggestions that could improve this site. Please note that if you use the Android app, the instruction is similar to the BlackBerry app below. I will create a separate instruction page for Android app soon. Contents 1  Initial Set-up after Installation 2  SMART task system that combine the best of GTD (Getting Things Done) and MYN  3  Smart Add - natural language date parser 4  ***NEW*** Shortcuts 5  Back-up and Restore - How to Transfer data from one device to another 6  BES - Work Space - Add a public app from the BlackBerry World storefront 7  Settings options that will definitely increase your productivity 8  Automatically add email as task 9  Tips on improving performance 10  Add a category 11  Delete a category 12  Keep categories permanently 13  Add sub-task 14  Automatic Synchronize from remember 15  Sort options 16  How to add recurrent task and edit advance calendar detail 17 a

React Native + Javascript + Date Time + JSON.Stringify() + JSON.parse()

This page will capture all issues related to Date and React Native mobile app development. Will add more as I go through the development cycle.  I will play around with Javascript for a bit before switching to TypeScript.  Some of the information below might not be following the best practice as I just started the development process. Will update as I learn more about React Native + Javascript. Storing Date object in AsyncStorage You can store Date object directly into AsyncStorage. Generally in other programming language, you can store the Date object directly in a data storage. However, with React Native, you will get the the Debug Console warning on Expo Go if you do so. I recommend using JSON.stringify() command below to store the date object. Please follow the instruction below.  On the side note, you can also store millisecond and use the date toString option but it is not recommended. I would recommend using stringify command as it will store using the toISOString() which is the