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.

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.

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).

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.

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

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

• 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.

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

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.

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

• By using the TODAY function, you can set the closing date in cell H6.

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.

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

• To extract the stock values for RELIANCE, use the following formula in cell C4.

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.

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

• Drag down the Fill Handle tool.

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

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.

• Use the following formula in cell C4.

Here, the last argument 2 is for Open property.

• Drag down the Fill Handle tool.

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.

Here, the last argument 3 is for High property.

• Drag down the Fill Handle tool.

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

• Type the following formula in cell C4.

Here, the last argument 4 is for Low property.

• Drag down the Fill Handle tool.

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

• Eventually, apply the following formula in cell C4.

Here, the last argument 5 is for Volume property.

• Drag down the Fill Handle tool.

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

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.

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

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.

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

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

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

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.

Steps:

• Use the following formula in cell B3.

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.

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

• Similarly, use the following 2 formulas for gathering the weekly stock values for the TCS and HDFCBANK companies.

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

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

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.

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

• How to Add Stock Data Type in Excel (2 Effective Methods)
• Get Stock Quote with Excel Add-in (With Easy Steps)
• How to Get Stock Prices in Excel (3 Easy Methods)
• Stock return analysis using histograms & 4 skewness of histograms
• How to Get Live Stock Prices in Excel (4 Easy Ways)
• How to Get Stock Quotes in Excel (2 Easy Ways)
• GST State Code List in Excel

<< Go Back to Excel Sample Data | Excel Data for Analysis | Learn Excel