How to Track NSE & BSE Live Data in Google Sheets

How to Track NSE & BSE Live Data in Google Sheets
Reading Time: 4 minutes

What is NSE & BSE Live Data in Google Sheets?

The National Stock Exchange (NSE) and Bombay Stock Exchange (BSE) are two stock exchanges active in India. They deal with the stock transactions of the entire country.

Why Track Live Data in NSE & BSE?

People invest money in the stock market through NSE or BSE. Investors want to check the live prices of the stocks they have invested in or are on their waitlist. Having a Google Sheet containing live stock prices helps keep track of their investments. You have created a watchlist of stocks, and you want to track their prices. You are thinking of investing in those stocks if their prices go below a specific value. You can track NSE & BSE Live data in Google Sheets using the GOOGLEFINANCE function.

Example of how to track NSE & BSE Live Data in Google Sheets
Figure 1: Example of how to track NSE & BSE Live Data in Google Sheets

How to Track NSE & BSE Live Data in Google Sheets?

What is Google Finance Function?

To track stock prices, you have to use the Google Finance Function. The GOOGLEFINANCE function in google sheets helps you fetch real-time financial data and historical securities data from Google Finance. Google Finance is a web service by Google focused on business news and financial information. If you want to know more about Google Finance, click here.

Sample Examples of NSE & BSE Live Data in Google Sheets

GOOGLEFINANCE(“NSE:INFY”, “price”, DATE(2014,1,1), DATE(2014,12,31), “DAILY”)

GOOGLEFINANCE(“NSE:TCS”,”price”,TODAY()-30,TODAY())

GOOGLEFINANCE(A2,A3)

Syntax to Track NSE & BSE Live Data in Google Sheets

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

  • The ticker represents the ticker symbol for the stock/company to consider. Keep in mind to use both the exchange symbol and ticker symbol. For Eg, if you want to find the stock price of Infosys (NSE: INFY), use NSE(exchange symbol) and INFY(ticker symbol). If the exchange symbol is not specified, GOOGLEFINANCE will use its best judgement to choose for you.
  • The attribute specifies the type of data to fetch about ticker from Google Finance. Compulsory to mention if the date is specified. By default, the function returns the “price” parameter. If you set the parameter as “high”, it will return the day high price of that particular stock. If you want to know what all values are excepted in attribute, click here.
  • 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.

Calculate Weekly High Prices

Step 1: Open Google Sheets

  • Open Google Sheets.
  • Navigate to the cell where you want to display results.

Step 2: Use the GOOGLEFINANCE formula 

  • To calculate weekly high prices for any stock, use:

=TRANSPOSE(GOOGLEFINANCE(B2,”High”,DATE(2014,1,1), DATE(2014,1,31),7)

  • Here, the transpose function transposes the results from rows to columns.
  • The Google Finance function is used to extract stock prices.
  • The first parameter (B2) selects the ticker symbol for the stock of choice.
  • The second parameter (High) selects the highest prices for a particular day for the specified stock.
  • The third parameter, DATE(2014,1,1), is the beginning date of fetching data. Here the date used is the 1st of January, 2014.
  • The fourth parameter, DATE(2014,1,31), is the ending date of fetching data. Here the date used is the 31st of January, 2014.
  • The last parameter (7) is used to fetch weekly data, as the interval is seven days.
 Track weekly high prices of a stock
Figure 2: Track weekly high prices of a stock | Track NSE & BSE Live data in Google Sheets

Create Sparkline Graphs for better visualization

  • To calculate weekly high prices for any stock, use:

=GOOGLEFINANCE(B10,”price”,DATE(2020,1,15),DATE(2021,1,15),7)

  • Here, the transpose function transposes the results from rows to columns.
  • The Google Finance function is used to extract stock prices.
  • The first parameter (B10) selects the ticker symbol for the stock of choice.
  • The second parameter (price) is used to select prices for a particular day for the specified stock at the closing price.
  • The third parameter, DATE(2020,1,15), is the beginning date of fetching data. Here the date used is the 15th of January, 2020.
  • The fourth parameter, DATE(2021,1,15), is the ending date of fetching data. Here the date used is the 15th of January, 2021.
  • The last parameter (7) is used to fetch weekly data, as the interval is seven days.
  • Now, use the formula:

=SPARKLINE(D11:D62)

  • Now this will create a sparkline graph for the values selected.
  • The output looks like this:
Create sparkline charts
Figure 3: Create sparkline charts | Track NSE & BSE Live data in Google Sheets

Conclusion

The GOOGLEFINANCE function in Google Sheets helps import NSE & BSE Live Data in Google Sheets. You can also create sparkline graphs to visualise the trend of that particular stock better. 

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Filter Data by Color in Google Sheets: In this article, we would have a look at how to Filter Data by Color in Google Sheets.

Using GOOGLEFINANCE to Convert Currency in Google Sheets | 2 min easy guide: In this article we would see how to convert currency in Google Sheets on a real-time basis using Google Finance. 

Complete Guide on the REGEXEXTRACT Function in Google Sheets for 2022: Learn how to use the REGEXEXTRACT function in Google Sheets and its variations.