portfolio tracker

1. Overview
2. Sheet Breakdown
   Net Worth · Monthly Tracker · Markets · Cash · Metals · Retirement · Insurance
3. Technical Logic
   Formulas · Named Ranges · The Helper Sheet
4. How to use
5. Glossary

Overview

There are a thousand different apps on the internet to track our savings and portfolios, with each offering a particular feature that others lack. Very few apps allow us to track everything we need in a systematic way. I have found manual tracking to be the only reliable method to get a true picture of my net worth. For this reason, I set out to build a Google Sheets file to do exactly that.

With this file, the user can track their Equity, Debt, Commodities and Retirement/Insurance information.

You just need to click on following link and click on “Make A Copy”.

Potfolio Tracker Template Google Sheet

It might display “Warning: Some formulas are trying to send and receive data from external parties.” in the beginning. This is to track Gold Prices from external sources. You will need to click on Allow Access

While I have added warnings throughout the post requesting not to edit certain cells in the sheets, please feel free to experiment and create your own system which works specifically for you.

Jump to the How to use section to immediately start tracking.

Sheet Breakdown

Net Worth Sheet

It contains the following tables and visualizations

Monthly Tracker Sheet

It contains one table called the “Monthly Tracker” which has the following columns

Markets Sheet

It contains one table called the “Markets” which has the following columns

Cash Sheet

It contains one table called the “Savings” which has the following columns

Metals Sheet

It contains one table called the “Metals” which has the following columns

Retirement Sheet

It contains one table called the “Retirement” which has the following columns

Insurance Sheet

It contains one table called the “Insurance Details” which has the following columns

Technical Logic

Named Ranges

Instead of manual ranges like A2:A9 or B2:B which could get complicated quickly as we scale the file based on various requirements, it gets incredibly important to use Named Ranges as they add labels to these ranges which are easier to reference. Also, once set up, these named ranges dynamically update whenever we add to or remove rows from these ranges.

For example: Using Markets_TotalValue is easier and easy to understand and reference rather than =Markets!J2:J40

Formulas

Many calculations and price detections are done using formulas. Currency conversions, Stocks, Mutual Funds and ETFs are directly supported by Google Sheets with the =googlefinance function while prices of Gold, Silver, Platinum are auto fetched via the =importxml function.

On the Net Worth Sheet, the Main View table shows total current values of many assets. And as a lot of different assets tend to be in a single table, there are formulas that can filter and output the sum of only a particular type of asset.

For example, to only find the SUM of all the IN stocks from Markets sheet, here is the formula used in the B2 cell:

=IFERROR(
  SUM(
    FILTER(
      Markets_TotalValue,
      Markets_Region="IN",
      (Markets_Type="Stock")
    )
  ),
  0
)

Here, Markets_Region & Markets_TotalValue are named ranges.

Here, IFERROR is a failsafe to return 0 if no value returned from the formula. FILTER used range and one of more conditions. SUM just sums up the all the values from the filtered range thus resulting in one Total value.

Similarly, you could also use this formula to return total of not just but multiple assets.

For example:

=IFERROR(
  SUM(
    FILTER(
      Markets_TotalValue,
      Markets_Region="US",
      (Markets_Type="Stock")
      + (Markets_Type="Equity Fund")
      + (Markets_Type="Debt Fund")
    )
  ),
  0
)

There are many other formulas throughout the sheet to calculate various things like Current Value of a Fixed Deposit etc.

To see all the formulas, refer the Glossary section.

Helper Sheet

The Helper Sheet which is hidden and protected (click on the All Sheets button to view) has a dynamic table ranging from cell A to E. This table enables the sheet to create different charts based on specific conditions.

As I wanted visualizations for Net worth trend, Asset Value Trend, Portfolio performance over time, Line charts seemed appropriate. However, I did not want to extend the vertical length of the page and thus a toggle to change charts in the same place seemed like the best idea.

There is a Line Chart Selector located below the pie charts with a dropdown toggle for Total Net Worth/Asset Trends/Performance and Start/End Date options to see these different line charts based on whatever time range the user wants.

These drop-down toggles trigger the formulas on the Helper sheet to change the table that shows up and the line chart uses this table to show the data.

This sheet should not be edited as it could break the visualizations or price fetching across the sheets.

There are two other tables on this sheet named Global Rates and Imported Rates per gm from column H.

The Global Rates table uses =GOOGLEFINANCE("CURRENCY:USDINR") to fetch the price of 1 USD in INR which is then used in the Markets sheet to convert the stock prices of holdings from the United States’ markets. This lets us use one single currency and thus discover the true value in Indian Rupee for consistency.

The Imported Rates per gm table on the other hand uses a somewhat unreliable source of price fetching. I used the IMPORTXML function to pull the daily price of gold silver and platinum from https://www.goodreturns.in/ website. It has different paths like /gold-rates/hyderabad.html, /silver-rates/hyderabad.html and /platinum-rate-in-hyderabad.html and these display the prices of said metals in a certain city on a static webpage. From there, I used the XPath of that particular amount on the page to be fetched on our helper cell.

For example, this is how the current gold price is fetched on I7 of the Helper sheet:

=importxml("https://www.goodreturns.in/gold-rates/hyderabad.html", "/html/body/div[1]/div[2]/div[1]/setion/div/div[1]/div[2]/p/span")

After this, we clean the extracted text using the following formula to normalize it to a number:

=Value((REGEXREPLACE(I7, "[₹,]", "")))

This is an unreliable way of fetching data. as it could break any time due to reasons like the website and its content being updated, it is important that we monitor the sheet for any inconsistency and if the fetching breaks, manually update the values in the helper sheet.

How to use

I like to track my portfolio once on the last day of each month. This reduced unnecessary anxiety and keeps the database clean for my taste. You will notice the same in the M/Y column of the Monthly Tracker sheet. It also allows me to see a Month-on-Month movement of my portfolio on the Line Charts.

You could essentially track Week-on-Week or even day to day movements but that would mean that your sheet would become enormous over time and may lag as well.

Tracking monthly means we have 12 rows in the Monthly Tracker sheet, while weekly means over 52 rows and 365 rows for daily tracking!

If you are content with my method, the first thing to do is to update the Insurance sheet and then in reversing order, we fill up all the remaining sheets up to the Markets sheet.

• For Insurance, we just add the description, select the type and add the sum insured and Start/End dates in the relevant fields. You may add the Policy numbers if you want to keep that handy.
• For Retirement, we add the Names, select the type and add the current balance in the relevant fields. You may add the Policy numbers if you want to keep that handy.
• For Metals, we add the Names and select the type of Metal. In “Cost @1gm”, we add the price we paid per gram for that metal type. In “Grams”, we add the total grams held. In “Making”, we add any relevant making charges. “Total Cost” calculates the total price paid for that metal. “Current rate” automatically fetches the current price of that metal. “Total Value” calculates the total current value.

  The “Current Rate” for SGBs need to be manually entered.

• For Cash, we add the Names, Orgs and select the type. In “Principal”, we add the initial invested amount (For Cash type, this would be the current balance). In “Start” and “End” fields, we enter the starting and maturity dates for FD type only. In “Interest”, we enter the percentage of interest offered for that FD. The “Current Value” calculates the total current value of that FD and just duplicates the balance(principal) amount for cash type. “Account #” can be used to store account numbers.
• For Markets, we add the Names, Tickers (pulled from Google Finance) and select the type of asset and region. In “Qty” we enter the number of Stocks or Units held. In “Cost” we enter the total invested amount for that asset. The “Avg” field auto-calculates the average price for a particular asset by dividing Cost by Quantity. “Current Price” fetches the current price of that asset. “FX Rate” shows 1 for Indian assets and the current USD to INR rate for US assets. “Total Value” calculates the Total Current value of that holding by multiplying Quantity, Current Price and FX rate. “P/L” and “P/L %” calculates the profit and loss of that holding in absolute and percentage.

After we have filled all these sheets, the Main View table on the Net Worth Sheet should have auto populated the total values for all asset and debt types. We can now also see our Total Net Worth, Pie charts showing Equity vs Debt split and Asset type split.

From here, we can copy the total values of each asset/debt types, once every month into the relevant fields of Monthly Tracker sheet. This will then enable the three interchangeable Line Charts in the main sheet.

The line charts will need a few rows in Monthly tracker to correctly show the growth/fall and trend lines.

If you find any issues with this tool or would like to discuss it further, feel free to reach out at me@harshnahar.com

Glossary of formulas

Simple formulas like “=G2 + B2” or “=B2” are not mentioned in this list.