How to Get Historical Data of NSE Stocks in Excel (2 Effective Ways)

If you are trying to collect historical data of NSE stocks in Excel, then you will find this article useful. NSE is the abbreviation for National Stock Exchange of India Ltd. So, let’s start with the main article to know the steps of doing this job.

Download Workbook


Available Stock Exchanges in Excel

In the following figures, we have tried to manifest the list of Stock exchanges which you can use in Excel to get the details of the historical data of these stocks. Notice that in this list we have highlighted the National Stock Exchange of India which means we can get the historical data of the enlisted companies of this stock exchange easily in Excel.

historical data of NSE stocks in Excel

Available stock exchange
historical data of NSE stocks in Excel

Available stock exchange

Available stock exchange

historical data of NSE stocks in Excel


2 Ways to Get Historical Data of NSE Stocks in Excel

Here, we have a list of some of the symbols of the companies enlisted in the National Stock Exchange of India. To extract the historical stock data of these companies we will be using the STOCKHISTORY function and keep in mind that only Microsoft Excel 365 users can access this function. Besides this, we will show the use of the GOOGLEFINANCE function for the spreadsheet if you don’t have Microsoft Excel 365 version.

historical data of NSE stocks in Excel


Method-1: Using STOCKHISTORY Function to Gather Historical Data of NSE Stocks in Excel

Here, we will determine the stock data for the following companies of NSE on a monthly basis from 1/1/2022 to 5/26/2022 (today’s date and format is m/dd/yyyy).

STOCKHISTORY FUNCTION


Step-01: Getting Full Names of Companies from Symbols

  • For showing the details of the symbols of the companies, an extra column, Full Form, has been added to accommodate the full names of the companies.

historical data of NSE stocks in Excel

  • Press CTRL + C to Copy the symbols of the companies.

FULL NAMES

  • Then, the copied symbols will be pasted into the Full Form column by pressing CTRL + V

historical data of NSE stocks in Excel

  • To convert the symbols to the full name of the companies, select the Full Form range and then go to the Data Tab >> Data Types Group >> Stocks Option.

FULL NAMES

After that, you will get the Full form of the companies.

FULL NAMES


Step-02: Creating Basic Outline to Gather Historical Data of NSE Stocks in Excel

Here, we will use the symbols of the companies due to brevity and then add 5 extra columns to accumulate the stock data for these companies for 5 months.
Besides these, we have kept 2 cells for inserting the date limits; Starting Date and Closing Date.

historical data of NSE stocks in Excel

  • Write down the starting date from which you want the historical data (here, we used 1/1/2022).

BASIC OUTLINE

=TODAY()

BASIC OUTLINE


Step-03: Using Formula to Retrieve Stock Data

  • Here, we want the dates of each month as the header of the stock values, so used the following formula in cell C2.
=INDEX(TRANSPOSE(STOCKHISTORY(B4,H4,H6,2,0,0,1)),1)
  • STOCKHISTORY(B4, H4, H6, 2, 0, 0,1) → B4 is the symbol of the company, H4 is the starting date, H6 is the closing date, 2 is the interval as Monthly (0→Daily, 1→Weekly, 2→Monthly), 0 is for no headers, the second 0 is for the property as Date, 1 is the second property for Close. It will return the dates along with the stock values.
    Output → {44562,2386.6; 44593,2359.55; 44621,2634.75; 44652,27990.25; 44682,2612}
  • TRANSPOSE(STOCKHISTORY(B4, H4, H6, 2, 0, 0,1)) becomes
    TRANSPOSE({44562,2386.6; 44593,2359.55; 44621,2634.75; 44652,27990.25; 44682,2612}) → The TRANSPOSE function transposes the date values because we want to gather the dates horizontally here for each company.
    Output → {44562, 44593, 44621, 44652, 44682; 2386.6,2359.55,2634.75,27990.25,2612}
  • INDEX(TRANSPOSE(STOCKHISTORY(B4,H4,H6,2,0,0,1)),1) becomes
    INDEX({44562, 44593, 44621, 44652, 44682; 2386.6,2359.55,2634.75,27990.25,2612},1) → returns the first array with dates among the two arrays for the dates and stock values.
    Output → 1/1/2022

historical data of NSE stocks in Excel

After pressing ENTER, you will get the following 5 dates for each month.

USING FORMULAS

  • To extract the stock values for RELIANCE, use the following formula in cell C4.
=TRANSPOSE(STOCKHISTORY(B4,$H$4,$H$6,2,0,1))

STOCKHISTORY will return the monthly closing stock values for the RELIANCE company, and then TRANSPOSE will reverse the column values into rows to align horizontally with the name of the company.

USING FORMULAS

  • Press ENTER.
    Then, you will get the closing stock values from January to May for the RELIANCE company.

historical data of NSE stocks in Excel

  • Drag down the Fill Handle tool.

USING FORMULAS

In this way, you will get the stock values for the rest of the companies, and the currency symbols here are automatically updated.

USING FORMULAS


Step-04: Exploring Different Properties of STOCKHISTORY Function

In the previous step, we used properties 0 and 1 to gain the dates and the closing stock values for the companies. Here, we will use 2 for Opening value, 3 for High value, 4 for Low value, and 5 for the Volume of the amount traded during this period.

historical data of NSE stocks in Excel

  • Use the following formula in cell C4.
=TRANSPOSE(STOCKHISTORY(B4,$H$4,$H$6,2,0,2))

Here, the last argument 2 is for Open property.

  • Drag down the Fill Handle tool.

EXPLORING DIFFERENT PROPERTIES

In this way, you will gather the opening stock values each month for all of the companies.

  • Now, apply the following formula in cell C4.
=TRANSPOSE(STOCKHISTORY(B4,$H$4,$H$6,2,0,3))

Here, the last argument 3 is for High property.

  • Drag down the Fill Handle tool.

EXPLORING DIFFERENT PROPERTIES

Afterward, we will have high stock values each month for all of the companies.

historical data of NSE stocks in Excel

  • Type the following formula in cell C4.
=TRANSPOSE(STOCKHISTORY(B4,$H$4,$H$6,2,0,4))

Here, the last argument 4 is for Low property.

  • Drag down the Fill Handle tool.

EXPLORING DIFFERENT PROPERTIES

Finally, you will get the companies’ minimum stock values for January to February.

EXPLORING DIFFERENT PROPERTIES

  • Eventually, apply the following formula in cell C4.
=TRANSPOSE(STOCKHISTORY(B4,$H$4,$H$6,2,0,5))

Here, the last argument 5 is for Volume property.

  • Drag down the Fill Handle tool.

historical data of NSE stocks in Excel

In this way, you will get the volume of the stocks traded each month.

EXPLORING DIFFERENT PROPERTIES


Step-05: Creating Sparklines for Stocks

Here, we will add the sparklines of the closing stock values for each company to visualize the characteristics of the change of the stocks over months. To add those lines, we have inserted a new column Line Nature.

historical data of NSE stocks in Excel

  • Go to the Insert Tab >> Sparklines Group >> Line Option.

SPARKLINE

Afterward, the Create Sparklines dialog box will open up.

  • Select the data range of stocks for the RELIANCE company in the Data Range box and $H$4 as the Location Range.
  • Press OK.

SPARKLINE

In this way, you will get the trendline of the stock values nature of the RELIANCE  company.

SPARKLINE

Similarly, create the sparklines for other companies to compare the nature of the stock values of these companies.

historical data of NSE stocks in Excel

Read More: How to Import Stock Prices into Excel from Yahoo Finance


Similar Readings


Method-2: Using GOOGLEFINANCE Function to Gather Historical Data of NSE Stocks in Google Sheet

If you don’t have Microsoft Excel 365 version, then you can collect the historical data of the stocks by using the GOOGLEFINANCE function in Google Sheet.
We will demonstrate the process of doing this job for the following 3 companies, we have made their names as headers and each name has 2 columns to gather the dates with the values.

GOOGLEFINANCE Function

Steps:

  • Use the following formula in cell B3.
=GOOGLEFINANCE(B3,"Close",DATE(2022,1,1),DATE(2022,2,28),"Weekly")

Here, B3 is the company’s symbol, “Close” is similar to the property of the above method, DATE(2022,1,1) will return the starting date by using the DATE function, DATE(2022,2,28) will give the end date, and “Weekly” is the interval.
The other properties can be used here; “Open”, “High”, “Low”, “All” etc. And we can only get the values for the Daily or Weekly interval.

historical data of NSE stocks in Excel

  • Press ENTER.
    Then, you will get the weekly closing stock values with corresponding dates and headers as well for the RELIANCE company.

GOOGLEFINANCE Function

  • Similarly, use the following 2 formulas for gathering the weekly stock values for the TCS and HDFCBANK companies.
=GOOGLEFINANCE(D3,"Close",DATE(2022,1,1),DATE(2022,2,28),"Weekly")

GOOGLEFINANCE Function

=GOOGLEFINANCE(F3,"Close",DATE(2022,1,1),DATE(2022,2,28),"Weekly")

GOOGLEFINANCE Function

  • To save this Google sheet as an Excel file, go to the File Tab >> Download Dropdown >> Microsoft Excel (.xlsx) Option.

GOOGLEFINANCE FUNCTION

Then, you will get the following sheet in an Excel workbook.

GOOGLEFINANCE FUNCTION

Read More: How to Import Stock Prices into Excel from Google Finance (3 Methods)


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

PRACTICE


Conclusion

In this article, we tried to cover the ways to get historical data of NSE stocks in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo