NSE Live Equity Future Prices in Excel

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

Advertisements

About Paripurnachari

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

11 Responses to NSE Live Equity Future Prices in Excel

  1. Keerthan says:

    Sir, Once again thank you for this wonderful page. Here I am unable to express my gratitude through words, but I sincerely thank you for sharing this outstanding application to all. I will try to spread the word regarding this excel application and this blog. To add to that, you have read my mind and already incorporated a “Refresh all” toggle in the file. This new toggle is what was lacking. Thank you, Thank you and Thank you!!!
    Sir, I have started trading since 1.5 months now. I am very new to the stock markets and I am still young(mid 20’s). Since I have started trading, I have been using two methods and I have been doing a lot of research in terms of live and paper trades. One of the methods has given me consistent profits for like 10 days now( I have been doing well so far). The other method I have been paper trading and would be going live from 8/6/15. I have developed excel sheets for both and have tried to automate to the best of my ability. But I feel a lot can be changed and it can be developed into a full fledged automated system using vba codes and macros. I am a non IT Engineer and I am doing my best and trying to learn VB Macro so that someday I would be able to completely automate this system and develop new systems for beginners like me. I would like to share the details with you if you don’t mind and I hope you could help me in developing/automating this system. Please let me know if you are interested.
    Thank you once again!!

  2. Keerthan says:

    Sir, is it possible to auto update this file. Say we have around 15- 20 equity futures in this file, and instead of using the “refresh all “toggle button is it possible to auto update this file say for every 1 min or so…
    Also is it possible to get the following table into excel? http://www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm
    I tried using web query but i get the “web query did not return message..” Is there a way around this?
    Thanks again!!

  3. GeePee says:

    This is absolutely brilliant, I have been looking for this feature for quite a long time finally, Finally received it here. Thanks a lot Paripurnachari !!

  4. Gourishankar says:

    Hi sir, I have been using these Excel files for 3 months now. They are stopped working from last 3 days. I’m a noob in excel. I don’t know what went wrong. Nse India is not providing data as of now. Can you please fix the issue? It would help me a lot. Thank you!

  5. Hi Sir, Good Evening… Thanks for the updated file. I have been using these Excel files from past 6 months. i would like to ask for few more updates…those are 1. please update futures data file also. 2. please make a new file for NSE options data like index options as well as stock options. 3. please make a new file for downloading historical data i.e i want to plot a chart for 1 index and 1 or more stocks on the same chart. 4. please make a new file for downloading intraday data for stocks and indexes. Thanks in advance.

  6. Vimalesh says:

    Hi sir,

    I have seen your VBA files it was brilliantly designed..

    I have one quick query is it possible to download historical contract wise stock prices using excel VBA.

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