How to Track Your Stock Portfolio in Google Sheets?

How to track your stock portfolio in Google Sheets? 

There are a number of free financial websites and mobile apps where you can track your portfolio to calculate your gain/losses etc. For example- money control, ET markets, market mojo etc.

However, if you a small investor in India and aren’t involved in frequent trades, intraday, or futures and options, then all you need is a simple sheet to track your stocks and the dividends received.

In addition, these sheets can also provide more privacy and security to your portfolio compared to mobile apps.

The good news is that it’s really simple to create and track your stock portfolio in google sheets. And in this post, I’ll teach you precisely how you can use google finance functions to create such sheets step by step.

Pros and cons of using google sheets to track stock portfolio:

Pros of creating a portfolio in google sheets:

• Live updation of the current share price of stocks.
• Customized portfolio.
• Easy tracking of dividends received.
• Simple tracking of net changes in the portfolio.

Cons of creating a portfolio in google sheets:

• Dividends are to be entered regularly (whenever you receive it).
• In case of stock splits or bonuses, you need to make the changes manually.

How to track your stock portfolio in Google Sheets?

If you do not know, Google offers ‘GOOGLE FINANCE’ which keeps most of the important stock data (like current price, PE, market cap etc) from the stock exchanges (BSE and NSE).  You can easily pull all the stock data in google sheets to track your portfolio.

To use this free facility, all you need is a google account (which I guess most of you will already have).

We are going to use few google finance functions to get the stock data like its current price, 52-week high price, 52-week low price, market capitalization etc.

Also read: How to Monitor Your Stock Portfolio?

Image: Dummy portfolio using google sheets (REAL-TIME UPDATION)

Video Tutorial: How to create and track your stock portfolio in google sheets

Here is a video tutorial which teaches you how to get the data on google sheets using google finance, create your portfolio and then track it.

Please note that you may find this tutorial a little complicated. However, I tried my best to explain the details in a simple and easy manner.

In addition, here is the stock portfolio template that we created in the tutorial video: Portfolio Tracker

Google finance help sheet: https://support.google.com/docs/answer/3093281?hl=en

How to find the symbol of a company?

As explained in the above video, you can find the symbol of a company by a simple google search. Just search, “Stock name + Share price” on google and you will get the following result. The symbol is highlighted in the below picture.

Ex: Ashok Leylands: ASHOKLEY

In case, you are not able to find the symbol using the simple google search (or it is not working), you can go to google finance and search for the company in its search bar. You will get a numerical symbol, which you can use instead.

Here is the link to google finance: https://finance.google.com/finance

For example, to find the current share price of TATA motors using the numerial symbol, you can use:
=GOOGLEFINANCE(“BOM:500570”,“price”) on google sheets.

Quick note:

There are few companies where company symbols are not unique and a different company with the same symbol might be listed on another stock exchange.

For example, Bharat Electronics limited has a stock symbol of ‘BEL’.

In this case, another company- Belmond ltd has the same symbol “BEL” and is listed on New York stock exchange (NYSE).

In these cases, you have to add the stock exchange symbol along with the company symbol in the google sheet, before you run the query.

For example, if you want to find the current price of Bharat electronics limited on google sheets, then use this function:

=GOOGLEFINANCE(“NSE:BEL”,”price“)

In addition, you can use this script “NSE:company symbol” anytime, even if the company has a unique symbol.

Summary:

Here is the list of the important google finance functions that we used in the above video to track your stock portfolio in google sheets.

Data	Google finance function
Company Name	=GOOGLEFINANCE(stock symbol, “name”)
Current stock price	=GOOGLEFINANCE(stock symbol, “price”)
Price history	=INDEX(GOOGLEFINANCE(“stock symbol”,”price”,date(YYYY,MM,DD))2,2)
52-week high price	=GOOGLEFINANCE(stock symbol, “high52”)
52-week low price	=GOOGLEFINANCE(stock symbol, “low52”)
Market capitalization	=GOOGLEFINANCE(stock symbol, “marketcap”)
PE ratio	=GOOGLEFINANCE(stock symbol, “pe”)
EPS	=GOOGLEFINANCE(stock symbol, “eps”)

That’s all. This is how you can create and track your stock portfolio in Google Sheets.

I hope this post is useful to you and you can also create a similar portfolio for your stocks in google sheets.

Please comment below if you have any questions. I’ll be happy to help.

New to stocks? Here’s is an amazing online course for newbie investors: INVESTING IN STOCKS: THE COMPLETE COURSE FOR BEGINNERS. Enroll now and start your share market journey.

Tags: how to track your portfolio using google sheets, track portfolio using google sheets, google sheet portfolio, google finance functions on google sheets, google finance functions, google finance stock tracker, google sheets stock template