If you are unable to access your trading platform possibly due to your office network settings or for any other reason, you can track real-time stock information on Google Sheets with the help of a function/formula.
You can create your stock portfolio in Google Sheets with the help of Google Sheets GoogleFinance function. Let’s see how to get BSE, NSE real-time stock prices in Google Doc Spreadsheet throughout the trading secession.
By following this tutorial you can learn to fetch real-time security information from ‘possibly’ any stock markets in the world.
Note: For stock exchange codes, the delay time, if any, of available stock quotes from different markets, and associated disclaimers please see this official Google Finance Disclaimer page.
If you are a trader or have used any trading platform in the past, you may find the GoogleFinance function very easy to use. This’s because you may find the arguments in this function familiar to you.
I’ve set up a sample spreadsheet for you to learn how to use GoogleFinance function to bring live as well as historical security Information on to your spreadsheet. You can see that link at the end of this post.
Similar: How to get real-time currency exchange rates in Google Sheets
How to Fetch BSE, NSE Real-Time Stock Prices in Google Sheets
Syntax:
GOOGLEFINANCE(TICKER, [ATTRIBUTE], [START_DATE], [END_DATE|NUM_DAYS], [INTERVAL])
In this syntax, the arguments within open and clause square brackets are optional and related to historical data. So I’m skipping that here as it’s not essential for real-time data.
Let’s begin with a few examples of the use of GoogleFinace to fetch real-time stock data in Google Sheets.
As you may know, the ticker symbol is essential to track related security info. Then how to find ticker symbols to use in GoogleFinance formula?
You can find the ticker symbol for the security which you want to consider in this formula form finance.google.com. You may just want to type the few starting letters of the security on the search field there to find the stock ticker.
In the above example, you can see that the security “HDFC Bank Limited” is listed in four stock markets. You can see the ticker symbol/security codes on the left.
Examples to NSE, BSE Real-Time Stock Prices
Below you can find few formulas as examples of the use of GoogleFinance function.
NSE Real Time Stock Price on Google Sheets
=GOOGLEFINANCE("NSE:HDFCBANK","PRICE")
BSE Real Time Stock Price on Google Doc Spreadsheet
=GOOGLEFINANCE("BOM:500180","PRICE")
In the above GoogleFinance formula, I’ve used the “price” argument to get the BSE, NSE Real-Time Stock Prices in Google Doc Spreadsheet.
The above two formulas fetch the real-time stock prices of the security “HDFC Bank Limited” from NSE (National Stock Exchange) and BSE (Bombay Stock Exchange) respectively.
There are more arguments that you can use to fetch real-time stock data like trade time, currency, high, low, volume, etc.
At the beginning of this post, I’ve promised about a spreadsheet containing the live examples. Follow this link and make a copy of the file from the File menu.
If you are lazy like me to open the link, below you can find all the arguments and the usage.
Here I am going to use the ticker in cell B4 as a reference. Further, I’ve used Google Sheets IFERROR function because of real-time security information for some of the arguments like ‘datadelay’, ‘volumeavg’ and ‘change’ may not available all the time.
Again I suggest you open my sheet to see the live examples.
Note: If you plan to copy the formulas below, first, enter the ticker in B4.
Available Arguments in GoogleFinace to Fetch Real-Time Stock Data in Google Sheets
Below I’m providing you the available all arguments that you can use to find BSE, NSE real-time stock prices in Google Doc Spreadsheet.
price – to find the real-time market price of the stock.
=IFERROR(GOOGLEFINANCE($B$4,"PRICE"))
priceopen – to find the current day’s opening stock price.
=IFERROR(GOOGLEFINANCE($B$4,"PRICEOPEN"))
high – to fetch current day’s highest price of the stock.
=IFERROR(GOOGLEFINANCE($B$4,"HIGH"))
low – to fetch current day’s lowest price of the stock.
=IFERROR(GOOGLEFINANCE($B$4,"LOW"))
volume – find the number of shares traded of the selected stock for the current day.
=IFERROR(GOOGLEFINANCE($B$4,"VOLUME"))
marketcap – the market cap of the selected stock.
=IFERROR(GOOGLEFINANCE($B$4,"MARKETCAP"))
tradetime – to find the time of the stock last traded.
=IFERROR(GOOGLEFINANCE($B$4,"TRADETIME"))
datadelay – this you can use to get the info on how far delayed the real-time data is.
=IFERROR(GOOGLEFINANCE($B$4,"DATADELAY"))
volumeavg – to find the daily trading average volume for this stock.
=IFERROR(GOOGLEFINANCE($B$4,"VOLUMEAVG"))
pe – to find the price to earnings ratio for the selected stock.
=IFERROR(GOOGLEFINANCE($B$4,"PE"))
eps – to find the earnings/share.
=IFERROR(GOOGLEFINANCE($B$4,"EPS"))
high52 – the 52-week high price of the stock.
=IFERROR(GOOGLEFINANCE($B$4,"HIGH52"))
low52 – the 52-week low price of the stock.
=IFERROR(GOOGLEFINANCE($B$4,"LOW52"))
change – this’s another useful info. You can use this argument to find the change in the price of the selected stock since the close of yesterday’s market.
=IFERROR(GOOGLEFINANCE($B$4,"CHANGE"))
beta – use to find the beta value of the stock.
=IFERROR(GOOGLEFINANCE($B$4,"BETA"))
changepct – use to find the percentage change in the price of the selected stock since the close of yesterday’s market.
=IFERROR(GOOGLEFINANCE($B$4,"CHANGEPCT"))
closeyest – to find the closing price of the previous day.
=IFERROR(GOOGLEFINANCE($B$4,"CLOSEYEST"))
shares – to track the number of outstanding shares of this stock.
=IFERROR(GOOGLEFINANCE($B$4,"SHARES"))
currency – the currency of the stock is being traded.
=IFERROR(GOOGLEFINANCE($B$4,"CURRENCY"))
Conclusion
Using the above formula and arguments you can easily get BSE, NSE Real-Time Stock Prices in Google Doc Spreadsheet. But don’t limit there!
You can use GoogleFinance function to track all your investments in any stock market in the world through live price updates.
Further, there are more arguments for the mutual fund as well as historical data. I’ll explain it in another post. That’s all. Enjoy.
Hi Prashanth,
Firstly superb work.
Just wanted to know that is there any way I can add a filter in this which shows that the share is within the 5, 10, 20 % range of its 52 week high.
And also, is it possible that I can know the Upper limit and lower limit band in terms of percentage i.e., 5, 10, 15, 20.
Thanks
Hi, Rk,
To find the percentage off the 52-Week High (ticker “BOM:500180”), you can try this formula.
=to_percent(1-(IFERROR(GOOGLEFINANCE("BOM:500180","PRICE"))/
IFERROR(GOOGLEFINANCE("BOM:500180","HIGH52"))))
Thanks, Prashant, and in case if we want to track the upper limit band and lower limit regularly, is there any way we can?
Hi,
Good day to you.
How can I get the commodities prices in google sheet?
Thanks
Hi, SPH,
As far as I know, right now, there is no built-in function that supports it. You may require an apps script or add-on.
Hi Prashanth, Is there a way to get the FNO details of NIFTY Stocks? Thanks
Hi, Praveen,
As far as I know, there is no such option present.
Do we have any syntax which can track the delivery percentage of the stock for that particular trading day?
Hello Prasanth,
I get the #N/A error when I call for PE, and Market Cap, for Adani power, FSL & Jetairways. Does it mean anything?
I tried the Iferror method too, could you please clarify?
Thanks in advance.
Hi, Natarajan,
In my test, the following returns the value (market capitalization of the stock).
Formula # 1:
=GOOGLEFINANCE("AdaniPower","marketcap")
Formula # 2:
=GOOGLEFINANCE("BOM:533096","marketcap")
But for “PE” (the price/earnings ratio), it returns #N/A.
We can use IFERROR or IFNA to remove the #N/A as below.
=ifna(GOOGLEFINANCE("AdaniPower","pe"))
Other than this, there is no other purpose of using these functions with GOOGLEFINANCE.
Regarding the errors, it may often happen with the GOOGLEFINANCE function. It might work the next time.
The symbol is NIFTY_50 but it has stopped working in my sheet for the last two days. In fact, everywhere it has stopped working. What can be the issue?
Thanks
Hi, SPH,
It’s widely reported. Let’s hope it will be rectified soon.
Hello there,
Can anyone please guide how do we get the Nifty50 and NiftyBank price syntax for the Google finance sheet? It was working earlier with the syntax
=GOOGLEFINANCE("NSE:" &A1,"PRICE")
but now it has stopped working. Kindly help.It is a simple strategy. Buy when the close is above 50 DMA but on 5 minutes candle. How to do this? EOD is possible and I know how to do that.
Thanks
How can I get the 50 ma value but on 5 minutes candle? Is this even possible?
Thanks
Hi, SPH,
Sorry! I’m not familiar with the 5-minute candles trading strategy.