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 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  Outlook.com a

New Site

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

React Native - Global StyleSheet and Dark/Light Theme design approach

We use StyleSheet in almost all components. Generally there are three different approaches to changing component layout with React Native: Direct layout change in the component (e.g. text field) Create a StyleSheet section in each page Create a Global StyleSheet and reference in each component Personally speaking, I do not recommend the first 2 options due to: maintenance and inconsistency approach in design between different components harder to transfer to a new project. with GlobalStyles, you can put on a separate windows for easy view and update as you work on different component apply the dark/light theme to the component directly through GlobalStyles without having to customize it for each page Theme - Dark and Light theme with React Native with iOS, Android, Web, etc. Warning: if you use Expo Debugging, the system will default to Light theme (at least on iOS). Remove Remote Debugging if you want the Dark Theme to show up. In your Navigagor, apply the theme: const scheme = useCol