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
Kritesh (Tweet here) is the Founder & CEO of Trade Brains & FinGrad. He is an NSE Certified Equity Fundamental Analyst with +7 Years of Experience in Share Market Investing. Kritesh frequently writes about Share Market Investing and IPOs and publishes his personal insights on the market.
Start Your Stock Market Journey Today!
Want to learn Stock Market trading and Investing? Make sure to check out exclusive Stock Market courses by FinGrad, the learning initiative by Trade Brains. You can enroll in FREE courses and webinars available on FinGrad today and get ahead in your trading career. Join now!!
WHEN TRYING TO PUT Bharat Electronics Ltd (BEL) ON SYMBOL IT REFLECT Belmond Ltd WHAT TO DO PLEASE SUGGEST.
Use “NSE:BEL” as the symbol (here NSE is national stock exchange). There are few companies symbols are not unique and a company with the same symbol might be listed on other stock exchange. In this case, Belmond ltd has the same symbol “BEL” and is listed on New York stock exchange (NYSE). That’s why, in those cases, you have to add the stock exchange symbol along with the company symbol.I hope it helps.
As per my observation Google finance take time to update LTP in Google Sheet, it takes more than 5-10 min, please guide.
Hi,
Thanks for informative article. I am looking for a method to calculate the daily volatility of a stock that I get in the csv here https://www.nseindia.com/archives/nsccl/volt/CMVOLT_07092018.CSV
How do I get the yield of a stock?
hi kritesh, thanks much for the great job. do you have any idea for how i can track 50 and 200 day exponential moving averages for US listed stocks?
Thanks a lot 🙂
Thank you so much for your support I will implement this on my website.
Really Nice blog.
I just want to know how moving average we can calculate.?
i try to calculate using “=AVERAGE(INDEX( GoogleFinance( “RELIANCE”, “all” , WORKDAY( TODAY(), -200 ) , TODAY() ) , , 5))” formula, but i think its not correct one.
Please guide me if you have any idea.
Thanks.
I have one more question. I try to fetch price for Larsen & Toubro Ltd. but its not working.
i try all ways like below, but its not working
=GOOGLEFINANCE(LT, “price”)
=GOOGLEFINANCE(NSE:LT, “price”)
=GOOGLEFINANCE(BSE:LT, “price”)
Let me know if you can help me.
@Rakesh Patel,
Try this,
=GOOGLEFINANCE(“NSE:LT”, “price”)
I WANT TO KNOW HOW TO TRACK MUTUAL FUNDS IN GOOGLE SHEETS
how can i track industry PE in google sheets
How do you record a sale of one of the stocks in the portfolio?
Missing a comma before the first 2
=INDEX(GOOGLEFINANCE(“stock symbol”,”price”,date(YYYY,MM,DD))2,2)
should be
=INDEX(GOOGLEFINANCE(“stock symbol”,”price”,date(YYYY,MM,DD)),2,2)
I want weekly total volume traded in google sheet for some stocks. How to get it? Please help.
Wish to speak to you, on some key area’s