NSE Equity Stock Watch in Excel

In this blog post, we will try to replicate the NSE Equity Stock Watch in MS Excel.

Following is the snapshot of original website –

NSE Equity Watch in Excel Header

NSE Equity Watch

Here, we will try to get the NSE live data into Excel without VBA code. One way to get data from the website without VBA code is to use the Excel Data Connections for getting the external data from the web, as explained in this blog post. The connections properties are adjusted not to disturb the format and importantly auto updating for a certain internal. The shared workbook is set to refresh for every minute. One drawback with this setting is, it keeps fetching the data even after the market hours. The URL to be used for the connection is the JSON URL used by the NSE Website to fetch the data.

URL = http://www.nseindia.com/live_market/dynaContent/live_watch/stock_watch/niftyStockWatch.json

Once we have the NSE live data in the Excel, we have to perform similar Excel string operations that as described in previous posts.

One aspect that is missing in Excel compared to the live website is Sparkline graphs that are displayed in the website. We can achieve this easily in Excel in two ways. One is to get the images straight from the website and insert into Excel. And other way is get the last 365 days data of the script and plot the data in Excel with Sparklines. The shared workbook do not implemented this feature. The other missing feature is the NSE highlighting the scripts that are touched 52week lows / highs, which can be easily achieved in Excel through conditional formatting.

The workbook developed in this post can be downloaded here NSE Equity Watch in Excel . To make the data connections work in Excel, please allow enable the data connections warning (clicking “Enable Content” in the yellow popup bar displayed at the top).

Advertisements

About Paripurnachari

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

11 Responses to NSE Equity Stock Watch in Excel

  1. Keerthan says:

    Bro, simply awesome man. Once again wonderful job. I have been very busy in the last couple of days and hence was unable to get in touch with you. The system I told you about needs more testing and hence I will get back to you regarding that in a few months time. But yesterday I had this idea for a new tool that could actually help a lot of people. I will discuss with you on this matter very soon. Thanks for your efforts man. Very much appreciated.
    One concern is it possible for the live equity futures prices excel file to have a one minute auto update feature like this file you have created instead of manually updating it.

  2. GeePee says:

    Great !! If I want data for Nifty midcap 50 watchlist, what is the nseindia.com json url?

  3. Samar says:

    Hey thanks a lot for the great post….I’m however not able to get the quotes from NSE anymore! Any idea about what might have happened?

    https://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/ajaxGetQuoteJSON.jsp?symbol=TCS

    It says access denied!

  4. Balaji says:

    Hi,
    I can see that you have used http://www.nseindia.com/live_market/dynaContent/live_watch/stock_watch/niftyStockWatch.json
    I have also found links for “http://nseindia.com/live_market/dynaContent/live_watch/stock_watch/juniorNiftyStockWatch.json” and “http://nseindia.com/live_market/dynaContent/live_watch/stock_watch/niftyMidcap50StockWatch.json”. But how
    to get the data for BANK Nifty? Do you have a similar JSON link for Bank Nifty?

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