Excel Tips and Tricks - How to Automatically Download Index Data from NSE - Part One


2

Editor's Pick

Here are the best stories on InsideIIM handpicked for you

Why MBA - Stories From Professionals Working At Google, Flipkart, Microsoft & More

Have you wondered why people choose to do an MBA? Perhaps for better work prospects and return on investment? Perhaps in order to gain an edge over others? Every year,

B-School Is Not A Placement Agency - Dr. Himanshu Rai, Director - IIM Indore, IIM Ahmedabad Alum

In this video, Dr. Himanshu Rai, Director at IIM Indore, Ex-Faculty at XLRI, IIM L, SDA Bocconi, Ex-Tata Steel, IIM A Alumni, talks to us about diversity and equality at

How A Non-Engineer Cracked CAT In Two Months And Made It To IIM Indore

Is it possible to crack an exam like CAT in a matter of just two months? Yes, and we bring to you an interview with someone who managed to not

How To Get Into Goldman Sachs For Investment Banking - A Fresher's Journey - Awiral Gupta, IIM I

Investment Banking, at some of India's top B-school campuses, is the most sought-after domains, owing to the steep learning curve and high packages offered by marquee firms. In this highly

'Don't Overhype CAT' - A CAT Topper Shares Tips To Score 99 Percentile In CAT 2019

The CAT exam is a tough nut to crack, and it takes a well-organised strategy and a bucket load of determination to crack the exam. So what kind of strategy

Pradeep P.B

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 |

+Read Replies (2)

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 |

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 |


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 |

+Read Replies (3)

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 |

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 |

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 |


dharmendra shah

Excellent.. Thanks. Have a good day.

6 Sep, 2015 |


Shyam Sunder Ramakrishnan

Hi. I will get back to you on this one. Regards Shyam

2 Nov, 2015 |


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 |


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 |

+Read Replies (6)

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 |

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 |

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 |

Jayaram Es

Sir, What is symbol code 1075 used in above link. & where to get these codes for other stocks.

9 Jan, 2016 |

uttara om

where can we get the list of symbolCode for nse

28 May, 2017 |

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 |


Dweep Vaidya

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 |


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 |


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 |


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 |


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 |


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 |


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 |


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 |