CAT 2024 Analysis

Competitions

CAT Prep

Upskill

Placements

MBA Co'26

RTI Response

Rankings

Score Vs. %ile

Salaries

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

Oct 1, 2014 | 5 minutes |

Join InsideIIM GOLD

Webinars & Workshops

Compare B-Schools

Free CAT Course

Take Free Mock Tests

Upskill With AltUni

CAT Study Planner

1 Day to CAT 2024 (All the best)

Participants: 557

Final 2 Days to CAT 2024 Test-44

Participants: 551

Final 3 Days to CAT 2024 Test-43

Participants: 401

Final 4 Days to CAT 2024 Test-42

Participants: 409

Final 5 Days to CAT 2024 Test-41

Participants: 411

Final 6 Days to CAT 2024 Test-40

Participants: 385

Final 7 Days to CAT 2024 Test-39

Participants: 370

Final 8 Days to CAT 2024 Test-38

Participants: 337

Final 9 Days to CAT 2024 Test-37

Participants: 343

Final 10 Days to CAT 2024 Test-36

Participants: 305

Final 11 Days to CAT 2024 Test-35

Participants: 581

Final 12 Days to CAT 2024 Test-34

Participants: 342

Final 13 Days to CAT 2024 Test-33

Participants: 305

Final 14 Days to CAT 2024 Test-32

Participants: 288

Final 15 Days to CAT 2024 Test-31

Participants: 392

Final 16 Days to CAT 2024 Test-30

Participants: 311

Final 17 Days to CAT 2024 Test-29

Participants: 316

Final 18 Days to CAT 2024 Test-28

Participants: 354

Final 19 Days to CAT 2024 Test-26

Participants: 342

Final 20 Days to CAT 2024 Test-26

Participants: 312

Final 21 Days to CAT 2024 Test-25

Participants: 261

Final 22 Days to CAT 2024 Test-24

Participants: 275

Final 23 Days to CAT 2024 Test-23

Participants: 186

Final 24 Days to CAT 2024 Test-22

Participants: 232

Final 25 Days to CAT 2024 Test-21

Participants: 234

Final 26 Days to CAT 2024 Test-20

Participants: 292

Final 27 Days to CAT 2024 Test-19

Participants: 242

Final 28 Days to CAT 2024 Test-18

Participants: 242

Final 29 Days to CAT 2024 Test-17

Participants: 262

Final 30 Days to CAT 2024 Test-16

Participants: 318

The real reason behind investment banking's insanely long hours is actually a dirty secret. Outwardly, most see it as a sort of machismo to be exuded by the banking community so that they can continue to enjoy their pre-eminent status over the rest of humanity. It is also seen as a coming-of-age ritual for bright young twenty-somethings wanting to make a successful career in banking. According to me, the real reason behind the long hours for equity research analysts, investment bankers and the whole lot, is plain stupidity. Given that a major chunk of work (for analysts and junior bankers) revolves around collecting data, analyzing and reporting on it, it is quite astonishing that they haven't built any tools to simplify these processes. Indeed, it is ironic that some of India's and the world best and brightest brains have somehow been unable to conjure up a simpler way to collect, analyze and report on data. Perhaps they are afraid that doing this would leave them with little work to do after all the data is analyzed. Tools like this one below can actually simplify workflows and shorten the days for analysts. 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