How to Build a Professional Portfolio Tracker with Google Sheets
A step-by-step guide with a template available at the end of the article
A few months ago, I decided to take on day-trading. I started in November 2020. Back then the market kept going up forayed by rock-bottom interest rates and by the flux of subsidies and financial support from the FED.
Armed with my phone, I scrolled the Android app store to find a suitable trading app. I live in Toronto, Canada. Here, there is not Robinhood, eToro or Questrade. The best Canadian trading app is Wealthsimple Trade hands-down.
However, even with a solid app as Wealthsimple Trade, it became increasingly difficult to keep track of my investments as I started to invest in more and more stocks.
After a few failed attempts with other portfolio tracking apps, I stumbled upon an article by Harish V that introduces GOOGLEFINANCE and walks the reader through building elementary Stocks Tracker on Google Sheets.
This contribution aims to expand on that article by providing a step by step guide on how to integrate additional features (sparklines), dedicated examples on how to add non-American securities, and finally by sharing with the reading a plug-and-play Portfolio Tracker template.
What is GOOGLEFINANCE?
Let’s start with the basics. Google Sheet has a built-in function that allows you to track real-time movements and key metrics of a wide range of securities, including stocks, ETFs and mutual funds. All that is needed is the ticker of the security.
Step 1 — Build the fundamentals
Start by creating a column with the tickers of the stocks you want to track on a Google Sheet. GOOGLEFINANCE will use the ticker to automatically query all the metric you want in your portfolio tracker.
The basic structure of the formula is:
=GOOGLEFINANCE([Ticker],[Attribute])
For example, the below formula will fetch GOOGL’s current price:
=GOOGLEFINANCE(“GOOGL”, “price”)
“priceopen”, “price”, “pricechangepct”, “high52”, “low52” are all useful attributes that should make it into all portfolio trackers.
If you want to track non-American securities then you also need to specify the index they are listed on.
=GOOGLEFINANCE([Index]:[Ticker],[Attribute])
For instance, the formula below will provide Air Canada 52w high on the Toronto Stock Exchange (TSE).
=GOOGLEFINANCE(“TSE: AC”, “high52”)
For all the list of attributes and features, refer to GOOGLEFINANCE’s editor document.
Step 2 — Add Sparklines
You can add sparklines to give your portfolio tracker a professional touch!
Sparkline formula follows the following structure:
=SPARKLINE(GOOGLEFINANCE([Ticker],[Attribute],[Start Period],[End Period])
The formula below will render GOOGL’s price movement in the last 365 days.
=SPARKLINE(GOOGLEFINANCE(“GOOGL”,“price”,Today()-365,Today()))
Step 3 — Import All Other Information
You will notice that GOOGLEFINANCE does not provide the whole suite of financial valuation metrics (i.e. P/B, EV, S/EV) or even dividend data.
Do not despair! These pieces of information can be imported from other financial data purveyors through the formula IMPORTXML. Good resources are Yahoo Finance, Morningstar or Marketwatch.
The one I rely on the most is Marketwatch. Although I noticed each one has its strengths.
You can find the most common financial metrics of securities in the Profile tab on MarketWatch.
IMPORTXLM([url],[xpath_query]) requires the [url] of the webpage to parse and the [xpath_query] to run on the structured data.
Let’s start from the [url]. Have a look at the [url] below:
https://www.marketwatch.com/investing/stock/ac/company-profile?countrycode=ca&mod=mw_quote_tab
Notice how the ticker symbol of the company and the country code (the country where the company is listed) are embedded in the URL. Hence, by customizing these two pieces of information we can identify the [url] for each stock.
The following formula will do the trick:
=CONCATENATE(“https://www.marketwatch.com/investing/stock/”,[ticker],”/company-profile?countrycode=”,[country code],”&mod=mw_quote_tab”)
Now, let’s move to the [xpath_query]. This can be easily obtained by right-clicking on the desired metric - in our case Net Income. Your browser will open the HMTL structure of the webpage highlighting the element you selected.
Click on Copy and click again on Copy XPath. What you copied is the [xpath_query].
Now you are all set! Plug the [xpath_query] in IMPORTXML formula and Google Sheet will automatically import the selected data.
=IMPORTXML(CONCATENATE(“https://www.marketwatch.com/investing/stock/","AC","/company-profile?countrycode=","CA","&mod=mw_quote_tab"),"/html/body/div[3]/div[5]/div[1]/div[1]/div/ul/li[5]/span")
Warning: many IMPORT queries do slow down your sheet and take a long time to lead. Limit the IMPORT queries to the strictly necessary.
Step 4 — Play around with it
Now that we have covered the basics, there is nothing left but to play around with it.
In case you have not built your portfolio tracker yet, feel free to tweak the template (link below) and save it on your computer.
https://docs.google.com/spreadsheets/d/1BGfU3npvtLkzVqTra9zh148n4OHrgbkD_ZXG4VmbFao/edit?usp=sharing
Thank you for reading and most importantly…happy investing!
