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

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

  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(newRowCountdateColumn),SpreadsheetApp.CopyPasteType.PASTE_VALUESfalse);
}


Comments

Popular posts from this blog

Checked! Task Pro for Android

SMART task system that combine the best of task management methodologies Natively Built for Android! The best Task, Note, and Calendar Master from BlackBerry is now available on Android! Please scroll to near bottom to see information on the Smart Task system - most efficient approach to manage your tasks and projects. Android Store link ------------------------------------------------- Instructions: Checked! Task Pro Instruction ------------------------------------------------- Current features: Use Speech to text icon to automatically add new task and calendar entry: e.g. Meet John tomorrow (use keyword similar to Smart Add feature below). Smart Add featuring category and natural language parsing for extremely fast event creation: Dr appointment on Dec 1, 2016 at 5pm Pick up airport tomorrow Meet John next Monday at 5PM Email Shawn tomorrow @Tax Finish tax in 5 days // Tax is a category Superb fluid user interface. Ultimate task and project management application SMART task system -

Bartender Cocktails and Drink 30,000+ for BlackBerry 10

Calling all cocktails & drinks lovers! This is the most wanted intelligent Cocktails & Drinks recipe application. It contains 30,000+ easy-to-follow cocktails and drinks with photo capability. No internet/Wifi connection required! You can also search any drink recipes easily, or even add your own invented cocktails and drinks! 

Dummy Text, Filter, and Random Text: Lorem Ipsum

I use dummy text, filter, and random text in document template, blogging, software development, and testing regularly. There are quite a few tips and tricks on generating this type of random texts.  You can use some of the methods on the list below to generate the text or simply copy the text from this post. Dummy Text, Filter, and Random Text for Software Development/Testing Dummy text is generally used in software development for testing purpose. The tester or developer fill a space where the content will eventually sit. The standard dummy text used is usually Lorem Ipsum. Dummy Text, Filter, and Random Text Generators Lorem Ipsum Website Generators Google: link Microsoft Word Lorem Ipsum function Type =lorem() Press Enter You can control the number of paragraph and sentences generated by entering more detail: =lorem(paragraph, sentence) For example: =lorem(8, 3) => This will create 8 paragraphs. Each paragraph will have 3 sentences. Tips & Tricks: if you don't have Micros