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
The workbook developed through this post for getting NSE Live Indices in Excel can be downloaded from NSE Live Indices in Excel.