In this blog post, we will try to replicate the NSE Equity Stock Watch in MS Excel.
Following is the snapshot of original website –
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).