NSE Live Indices into Excel

One of our blog reader, Bimal has asked for how to get live NSE Indices (CNX Nifty, India VIX) into Excel. As posted in the previous blog posts, to get any live data we need to perform two steps. One step is get the live data from internet through URL and second step is to process the URL response to get the desired data.

In line with our thought of initial post on JSON processing in Excel VBA, we will choose JSON URL for getting this live information. The URL we will be using is NSE Indices JSON URL. If we observe the response of the URL, it provides information of 35 NSE indices with current market price (lastPrice), absolute change (change), and percentage change (pChange). So with this single URL we will try to fetch all the index related data in one query to web. The VBA part of getting the response is similar to what we have considered in first blog post on this subject. Also, processing the web response is also same that is explained in that post.

We have not faced any problem in the initial key value pair JSON responses for equity prices, but when the NSE indices are fetched in JSON we have two key values of “CNX Nifty”. The duplicate is not because of duplicate keys in JSON which is not permitted but because of the stastic lookup Excel find function we are using. To overcome this problem, we can substitute the second key with known key and queried accordingly for its value.

After little conditional formatting and data refresh button, the Excel Sheet will look like

NSE Indices in Excel

The workbook developed through this post for getting NSE Live Indices in Excel can be downloaded from NSE Live Indices in Excel.

Advertisements

About Paripurnachari

Finance Professional, Coder, Engineer
This entry was posted in Technology and tagged , , , , , , . Bookmark the permalink.

6 Responses to NSE Live Indices into Excel

  1. Bimal says:

    Thank you for posting the code for NSE Indices. Much appreciated. Like for stocks, I was looking for information in the form Open, High, Low & Close for CNX Nifty. This is available at link “http://www.nseindia.com/live_market/dynaContent/live_watch/live_index_watch.htm” and also at “http://www.moneycontrol.com/nifty/nse/nifty-live”. However, I am not able to get json url for these pages. Look forward to your help.

  2. vppc says:

    When we started with JSON is to process the specific stock selected by the user as in the initial posts. If we have constant parameters to query the web, then normal way of getting the data into excel should be suffice (may refer to https://vppc.wordpress.com/2014/12/03/excel-data-from-web/)
    Here, from the MoneyControl link mentioned, we can fetch the desired data and keep refreshing (automatic time setting option is also possible, if we play around Excel data connections).

  3. Uttam says:

    Thank you for post. Is anyway possible to get the data from following site?
    http://www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm?cat=N
    I am looking for Yearly performace of the stocks in 2nd last column. Can you please provide me information?

  4. Uttam says:

    Thank you for your help. One updation in NSE Live Indices into Excel. The last column values need to corrected. The cell should be divided by one hundred or the %change to be removed.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s