Tracking stock prices listed on NSE and BSE using Google Sheets

Let's design a live portfolio on google sheets

Photo by Gilly on Unsplash

In this blog, I will be covering how to make your own portfolio tracker using google sheets for investments done on NSE and BSE. I will also share some tips and tricks on the same. We will use the google finance tool in google spreadsheet.

The documentation of google finance is available here, but it does not show how one can track Indian stocks especially on BSE, consider this as an extension.

Let’s get started and pull some data.

Nomenclature

It is very important that you name your stocks by appending the exchange name as given below. I will take the example of Reliance Industries Limited.

Step 1. Get the symbol Information

Open the page of the company in your preferred exchange. Adding the screenshots below.

Source: https://www1.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=RELIANCE

Note down the symbol name if it’s NSE.

Source: https://www.bseindia.com/stock-share-price/reliance-industries-ltd/reliance/500325/

Note down the number as highlighted in the screenshot.

So for Reliance Industries Limited the symbol on NSE is RELIANCE and on BSE is 500325.

Step 2 - Let’s track the current price

Great now open google sheets and type the following command.

Carefully observe what I have appended in the symbol, the prefix for the National stock exchange is NSE and for the Bombay stock exchange is BOM (Not BSE)

The formula used is

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

BSE: =GOOGLEFINANCE(“BOM:500325”, “price”)

Let's get some more data

The syntax general syntax for the query is

=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

Ticker nomenclature for Indian exchanges is discussed above. The reason why I am emphasizing so much on the ticker is that there are some companies that are listed both on the Indian exchange and US exchange and google will give preference to US exchange. Also when we have to track prices based on what exchange it's a best practice to append names properly.

Attributes for real-time data

The following attributes are for real-time data

• "price" - Real-time price quote, delayed by up to 20 minutes.
• "priceopen" - The price as of market open.
• "high" - The current day's high price.
• "low" - The current day's low price.
• "volume" - The current day's trading volume.
• "marketcap" - The market capitalization of the stock.
• "tradetime" - The time of the last trade.
• "datadelay" - How far delayed the real-time data is.
• "volumeavg" - The average daily trading volume.
• "pe" - The price/earnings ratio.
• "eps" - The earnings per share.
• "high52" - The 52-week high price.
• "low52" - The 52-week low price.
• "change" - The price change since the previous trading day's close.
• "beta" - The beta value. (Works very well with NSE but not with BSE)
• "changepct" - The percentage change in price since the previous trading day's close.
• "closeyest" - The previous day's closing price.
• "shares" - The number of outstanding shares.
• "currency" - The currency in which the security is priced. Currencies don't have trading windows, so open, low, high, and volume won't return for this argument.

Attributes for historical data

• "open" - The opening price for the specified date(s).
• "close" - The closing price for the specified date(s).
• "high" - The high price for the specified date(s).
• "low" - The low price for the specified date(s).
• "volume" - The volume for the specified date(s).
• "all" - All of the above.

Example query for tracking last 5 days data for Reliance

=GOOGLEFINANCE(“NSE:RELIANCE”, “all”, TODAY()-5,TODAY())

Additional Syntax information

• start_date - [ OPTIONAL ] - The start date when fetching historical data.
• If start_date is specified but end_date|num_days is not, only the single day's data is returned.
• end_date|num_days - [ OPTIONAL ] - The end date when fetching historical data, or the number of days from start_date for which to return data.
• interval - [ OPTIONAL ] - The frequency of returned data; either "DAILY" or "WEEKLY".
• interval can alternatively be specified as 1 or 7. Other numeric values are disallowed.

Now let us build our custom portfolio

Now using the concepts above and some excel calculations we can make our own portfolio. The formulas used are given in yellow cells.

Also added some pie charts to show your percent allocation in respective stocks.

I hope you liked the blog and also find it helpful. Do share with me your trackers and how you used this to track your investments.

You can reach out to me on Linkedin or E-Mail