First I would like to thank one of our blog readers, Keerthan for his kind words on the blog posts. This blog post is based on request from Keerthan for getting the OHLC prices of NSE Live Futures into Excel.
As in continuation with the previous posts on this subject of getting live data into Excel, we have to find from which URL we have to get the JSON response to process for getting the desired results.
The URL we are using here is NSE Live Quote for Futures. This URL request takes 3 main parameters related to our data. They are “instrument”: what kind of instrument we are looking, “underlying”: what is the underlying stock / equity share, and “expiry”: what is the expiry date looking for this future quote. Here our pair parameter for request key instrument is “FUTST” as we are looking for stock futures data. The underlying key will have mapping pair of the underlying equity asset for which we are interested. Given that the expiry dates are fixed as last Thursday of each month, we can automate it.
The trick here is get the date of last Thursday of each month in the desired text format of the request parameter. For example, for the month of June 2015, 25th is last Thursday date and the required format for the URL shall read as “25JUN2015”
The last Thursday of each can be obtained from the following formula
=EOMONTH(TODAY(),0)-(WEEKDAY(EOMONTH(TODAY(),0),14)-1) The trick is in Weekday function and its parameter 14 to start Thursday as 1 so that we can subtract the difference in days from end of the month
Once we have the last Thursday date, we have to use the following formulas to get the desired text format
=TEXT(DAY(D1),"dd")&UPPER(TEXT(D1,"MMM"))&YEAR(D1) here D1 refers to the cell where last Thursday of month is calculated with the above stated formula The trick is use the Text function and get the desired results. We shall use "MMM" to get the month in 3 letter format.
As explained in the previous post, we can get the desired data by processing the JSON response.
The Excel Workbook for this exercise can be downloaded from this link NSE Futures Live Prices in Excel.
Update: Workbook with auto refresh for every minute (still needs to click on “Refresh All” button once) can be downloaded from this link NSE Futures Live Prices in Excel with Autoupdate </deprecated>
Update 2: Please download the updated tool with changes from latest post NSE Live Stock (Share Price) Data into Excel – Part 3