Automating the download of share price data from NSE
Share prices on NSE are available from this page. The challenge is to collect share price data for a given index over a period of time. We can do it using two methods.
Method One - Filling the form on the web page, and then submitting the data, and then downloading the CSV.
This is a standard and logical technique. It comprises the following steps.
1. Open NSE India on Internet Explorer and navigate to this page. (Why Internet Explorer? That's because VBA has readymade methods for manipulating elements of a web page that are opened in Internet Explorer. You can automatically fill forms and hit the submit button of web pages, provided they are opened using IE).
2. Fill up the relevant dates in the form fields (Start Date and End Date) and hit the submit (Get Data) button.
3. Once the Page is loaded, hit the Download file in csv format link using VBA, and you will have downloaded the data on your machine.
I will expand upon this method in the next article.
Method Two - Hacking the process and using a Web Query from Excel
Excel has a built-in feature for importing data from the web directly. This is the Web Query feature, to be found under the Data Tab, Import data from Web option. This feature allows you to directly import data from a web page if you specify the url correctly. It has a few options, including letting you select specific tables from the page, letting you choose the format in which you want to import the data - No Format means that the data will be downloaded without the formatting, Rich Text Formating means the fonts from the web page will be preserved on the excel, and full HTML formatting means the web page will be almost exactly reproduced as it is on the excel sheet. I would recommend choosing 'Full HTML formatting'. However, as of now we can't use this method because we don't know what url to target.
The problem broken down - Which URL should I target to get index data between a specific date range from NSE ?
Assuming that you want to download index data for the CNX Nifty between 1st January 2014 and 1st October 2014, and you are going to use a web query to download the final data from the url, the problem now reduces to guessing the correct url on NSE for a particular index's data (in this case NIFTY) for a particular date range (start date: 1st January 2014 to 1st October 2014).
Hacking the URL from Javascript for getting the dates right
1. Have a look at this page and click on view source. You will see that the function "Load Indices Data " is loading the results from the database on the webpage (<input type="image" onclick="loadindicesdata()" class="getdata-button" src="/common/images/btn-get-data.gif" id="get"/>) .
2. Search for this function in the remaining javascript files. You will find it defined in this file (http://www.nseindia.com/products/resources/js/pepb.js)
3. The loadIndicesData() function prefixes a url at the beginning that goes something like (http://www.nseindia.com/products/dynaContent/equities/indices/historicalindices.jsp?) and then appends three parameters, and produces a result based on your inputs for those three parameters. A little bit of trial and error will let you figure out the correct way of passing these parameters.
(a) indexType -> This is the name of the Index (CNX NIFTY, CNX 100, CNX 200 etc etc)
(b) fromDate -> This is the start date. The format is mm-dd-yyyy
(c) toDate -> This is the end date. The format is again mm-dd-yyyy
The final url, for example, if you are searching for CNX Nifty data between 1st January 2014 and 1st October 2014, will be (http://www.nseindia.com/products/dynaContent/equities/indices/historicalindices.jsp?indexType=CNX%20100&fromDate=01-01-2014&toDate=01-10-2014)
Use this url in your web query, and download the data from the web page to the excel sheet. Make life easier.
The sample file can be accessed by clicking on the link below.
InsideIIM NSE Data Download Demo
I will leave it to you to make the dates and index names configurable.
Read this article by Anup Kumar Agarwal on Excel Tips and Tricks
Read this article on how to send an email automatically through excel
Comments
Pradeep P.B
Equity Research Analyst at STAR (Sys Two Analytics & Research (India) Private Limited)
Hi Shyam, Very useful post. My earlier simple code failed and i was searching for long time. Thanks. I tried replicating the same process for Stocks in this link: http://www.nseindia.com/products/content/equities/equities/eq_security.htm However i was not able to track down the exact function for onclick. There were 2 functions which were getHistoricalData & another function "activate". Trying to read the Java script (i dont know much coding or js) did not yield what are the fields appended for generation of the csv. Can you help me giving the URL? (or probably you can write part 2 for larger benefit). Thanks. Pradeep (planetpb at gmail)
13 Apr 2015, 06.19 PM
+Read Replies (5)
Kainaz Sherdiwala
hi pradeep i m facing with the same problem...i also want the code for http://www.nseindia.com/products/content/equities/equities/eq_security.htm...have u tried the above code for this link n is it working?...plz guide me...thnx
2 Nov 2015, 08.55 AM |
Shyam Sunder Ramakrishnan
Hi Pradeep and Kainaz. I hacked around a bit on the NSE website. What you can do is go to the above page, fill the form, and click on "Get Data" and at the time, keep the network panel open (Network panel is part of Chrome dev tools ---> to go there - a convenient way can be --> Right click on any element on web page and click on "Inspect element". You can see a bunch of options....Elements, Network, Sources, Timeline etc... Looking at Network will tell you what is the function called). Anyway - I played around for Tata Coffee - 1 month results and got this http://www.nseindia.com/products/dynaContent/equities/equities/histscrip.jsp?symbolCode=1075&symbol=TATACOFFEE&symbol=tatacoffee&segmentLink=3&symbolCount=1&series=ALL&dateRange=1month&fromDate=&toDate=&dataType=PRICEVOLUMEDELIVERABLE you should be able to easily reverse-engineer from the above URL. This was for 1 month data range (last one month)
27 Nov 2015, 01.04 PM |
Jayaram Es
Sir, What is symbol code 1075 used in above link. & where to get these codes for other stocks.
9 Jan 2016, 01.01 PM |
uttara om
where can we get the list of symbolCode for nse
28 May 2017, 03.52 AM |
Karthickraja Ponnuswamy
I am also looking for it.. still didn get it
2 Jun 2017, 05.20 PM |
null null
Hi Thanks for info. Can you help me with following NSE URL it contails JSON data. http://www.nseindia.com/products/content/equities/equities/oi_spurts.htm I am trying to put this in excel couldn't do in proper format. If you can help me to get the XLSM will be great help.
10 Jun 2015, 11.26 AM
+Read Replies (1)
Shyam Sunder Ramakrishnan
Hi . you can try "get external Data" and then "From web" as an option, and then put the above URL - and copy all the data. This will let you import webpage data to your excel sheet (see this - for an older version of excel http://www.howtogeek.com/80142/copy-website-tables-into-excel-2007-spreadsheets-2/). You can play around with formatting options. To automate this, you can write a VBA script/Macro. If you are unfamiliar with writing macros, then use the "Record Macro" and perform the above actions - (get external data....etc) . It will then create the MAcro - you can see the Macro and play around with the options. There is an option called "webTables" which lets you specify which table on a webpage you want to copy https://msdn.microsoft.com/en-us/library/office/ff839600.aspx
27 Nov 2015, 12.50 PM |
dharmendra shah
computer programmer at Sati engg college
Excellent.. Thanks. Have a good day.
6 Sep 2015, 12.49 PM
Shyam Sunder Ramakrishnan
Hi. I will get back to you on this one. Regards Shyam
2 Nov 2015, 09.18 AM
Shyam Sunder Ramakrishnan
Hi Pradeep, Kainaz and Eesha. I have responded to the two queries posed you. If you are facing any problems, you can specify them in this thread. Shyam
27 Nov 2015, 01.16 PM
Pb Pradeep
Hi Shyam, Thanks for the reply. Was able to crack the same and could download all the JSON tables. Interestingly, I was able to download every data but was not able to get marketcap data. Is there a place to get marketcap data from NSE? say for eg for a stock: ACC "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=ACC" we get Free Float Market Cap. I was not able to download that too. Do let me know in any place you were able to locate Market cap data in NSE & possibility to download Free float M.Cap data from the link. Regards Pradeep
30 Dec 2015, 12.26 PM
+Read Replies (1)
Shyam Sunder Ramakrishnan
Hi Pradeep. I couldn't find the market cap data on any of the websites (NSE, Google Finance, Yahoo Charts, Moneycontrol). It's easy to infer it from the price - I suppose that's the reason why nobody offers it. Are you looking for overall market cap or free float market cap ? If it is the former, it can be directly inferred from the price (some of these sources auto adjust for stock splits etc). If it is the latter, again it can be inferred by subtracting the promoter's shareholding.
1 Jan 2016, 07.35 PM |
Dweep Vaidya
Hello
Great discussion. I am trying to get data from following url to excel (via web query) but i get error - unspecified error in jquery and i cant see any data in window. so can't import. pls help. thanks http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=NIFTY&instrument=OPTIDX&strike=8000.00&type=CE&expiry=28JAN2016
1 Jan 2016, 08.54 AM
Dipak Senapati
Hi, I want to get a daily report of the stocks which were 52 week high (or low) which is published in moneycontrol or nse/bse. How can I do it on an automated basis in excel sheet (instead of everytime copy pasting) ?
14 Jun 2016, 04.59 PM
Shyam Sunder Ramakrishnan
Hi Dipak. Is there a URL where you can find this data? Is it okay if the automatic download requires a button click (i.e. process triggered manually) or do you want the trigger to be automated as well (i.e. everytime when you open the book, or at a certain time in the day like 4:00 pm).
14 Jun 2016, 05.25 PM
Dipak Senapati
Hi Shyam, The URL is as below: http://www.moneycontrol.com/stocks/marketstats/nsehigh/index.html Will be ok in either way - by a button click or while opening the document
16 Jun 2016, 07.04 PM
Shyam Sunder RamaKrishnan
Hi Dipak. It would be simple to do this - you need to write a macro that goes to above web page, and downloads data. Just try recording a Macro, and then doing this - 1. Go to the Data tab and select External Sources - Web page as the option. It allows you to select - either the whole page to be downloaded to excel, OR, any specific table/tables on the web page that will be downloaded. It also allows you to specify different formats - (full HTML - which means - all the background colours and everything will be downloaded, RTF - which means - the text colours/sizes etc will be preserved, and Plain Text). After you finish recording the macro, do try running it again, stepping into the code and modifying some values to find out how things change etc. Regards Shyam
17 Jun 2016, 02.42 PM
vijay v.j.r
hi Shyam can you help me to get this /products/dynaContent/equities/indices/historical_pepb.jsp dividend yield in csv i have try to edit your excel but i no use can you help me get it. thanks
24 Jul 2016, 05.34 PM
prasad kotte
Hi shyam i have used the excel u gave to download nse data. Can u please the excel macro for stock historical data also.it will be of grea help. I dont know java and all. Thanks Babu
7 May 2017, 07.12 AM
Amit Kachalia
Hello Shyam. The original thread of getting indices data has been changed by NSE. That is, its source is changed. Please provide me with the update source link.
12 May 2017, 05.15 AM