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
This is your current financial picture view. An example structure is below.
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("https://ycharts.com/mutual_funds/M:" & C13 & "." & D13, "/html/body/main/div/div/div/div/div/div/span")
Create as many investment tabs as necessary to track your family investment.