NSE Live Stock (Share Price) Data into Excel – Part 3

After a long time, I am updating the blog. Regret for not able to replying to comments (many are still pending for approvals for publish, apologies).

NSE website has changed the access of public JSON URL that we have been using to get the quotes from NSE website into the tool. So this post updates the NSE Share Price tool with the following –

  • Update the NSE Website URL to be used for data fetching
  • Encoding the ticker symbols with “&” (like M&M, COX&KINGS, L&TFH …etc.)

The updated URL is directly linked to the website URL of NSE Quote page instead of the JSON URL used for fetching previously. Though the NSE might have changed the access of direct JSON URL for their web security / access privilege purposes, data-wise access should be open for the public information.

The updated product file can be downloaded from the link – NSE Live Stock Prices in Excel Ver 3 – Portfolio Mangement Through Excel <not working for all the users>

The updated futures file can be downloaded from the link – NSE Futures Live Prices in Excel with Autoupdate ver2 <not working for all the users>

Update :  Some of the users are not getting the relevant data as the direct access of the URL is blocked. So again we have to update to a new URL and also improved the speed of the excel. Just click on the calculate all after you insert the script id.

The updated product file can be downloaded from the link – NSE Live Stock Prices in Excel Ver 4 – Portfolio Mangement Through Excel <updated on 28th May 2017>

Also, users can download the tool of BSE data from BSE Live Stock Prices in Excel Ver 1 – Portfolio Mangement Through Excel

Posted in Technology | 32 Comments

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).

Posted in Technology | Tagged , , , , , , , , , | 11 Comments

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

Posted in Technology | Tagged , , , | 13 Comments

NSE Live Indices into Excel

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

NSE Indices in Excel

The workbook developed through this post for getting NSE Live Indices in Excel can be downloaded from NSE Live Indices in Excel.

Posted in Technology | Tagged , , , , , , | 6 Comments

BSE Live Stock (Share Price) Data into Excel

How about crowd-testing? This happens already with big product companies which releases beta versions to public to test the functionality and to identify any product limitations.

Coming to our weblog, till the last post the Excel portfolio management product is getting the current market prices from NSE but some of our friends asked for getting the latest market prices from BSE. So this post initiates fetching live share prices from BSE. Logically there is no much difference between fetching data from NSE or BSE as we follow the same steps that are 1>Fetch the data from internet 2>Perform string manipulations to update the data. As explained in NSE part1 post, we will use the VBA function to get the data from internet. Only change from that code is change the URL from which we fetch the data. Here we are using URL http://www.bseindia.com/stock-share-price/SiteCache/EQHeaderData.aspx?text=500002 One difference with initial NSE implementation is instead of script id the BSE URL is using scrip code. For NSE, it may be easy to remember the scrip id but remembering scrip code is tough ask (at least for me). So we will use the search functionality implemented for picking NSE scripids for picking the BSE scrip codes.

The other difference with NSE implementation is how we perform the string manipulation on the data fetched from internet. Given NSE web data is of JSON type, we can fetch easily with key / pair combination of required information. But the BSE data is just a text response so we need to understand what pattern the response forming. On closer observation, we can see that last comma separated values in previousClose, open, dayHigh, dayLow, lastTradedPrice. Though we can fetch the desired result with only Excel formulas as explained in the post, here we are doing through VBA code to reduce the lengthy formulas. The VBA code consists of public function taking the BSE url response and the which parameter value we want. So the current market price which is last traded price is of last value in CSV of response. Accordingly the function splits the BSE web response and returns the desired result as given by the second input parameter passed to function.

The Excel workbook can be downloaded here BSE Live Stock Prices in Excel Ver 1 – Portfolio Mangement Through Excel.

Posted in Technology | Tagged , , , , , , | 2 Comments

NSE Live Stock (Share Price) Data into Excel – Part 2

In continuation with last post on fetching NSE live share prices into Excel, we will try to add few more features to this Excel workbook (NSE BSE Live Stock Prices in Excel) and develop into a portfolio management product.

Main features of the product –

  1. Live Share price from NSE (low delay compared to other public websites)
  2. NSE Ticker lookup for all the NSE listed companies (no query to exchange)
  3. Track the stock portfolio with return % and no sign in (user tracking not required)
  4. Excel power of calculating and formatting (computational power and dashboard visualization)

Scope for future features of the product –

  1. Triggering alert on specified price levels of stock (popup message / email alert)
  2. Moving averages and price charts for a selected stock (technical analysis)
  3. Fundamental analysis based on the filings (XBRL will make it easy)
  4. Research reports linking and public information for a selected stock

Product Functionality

As discussed in last post, the data source for stock information is crucial for the entire product functionality. Here, the selected source of NSE website is of low delay (~2 mins) compared to other finance / money related websites (~15min). Given the data is fetched through the NSE ticker symbol which needs to be specified in the product, the users may find difficult to get the exact NSE symbol so we have incorporated the symbol look up functionality. Instead of querying the exchange for every lookup search, we will keep the local data source. This will reduce the web traffic and improve the user experience in displaying the search results without any lag. The source data here is taken from NSE website of all the listed companies as on 30th Nov 2014. So when the user queries any part of the company name, all the related companies along with its ticker symbol are displayed so that user can select and click to add to the existing portfolio. To compute the  mark to market returns (in terms of percentage) the user needs to enter the purchase price and may include the transaction costs (brokerage & other charges) as part of their purchase price. To get the absolute returns, the user can enter the purchase quantity to get the absolute return and profit (before exit taxes) of the portfolio. Excel has powerful functionality of formatting apart from the strong computing power. The mark to market returns and each share weightage of portfolio are formatted with basic conditional formatting. Given the product is in early stages of its functionality enhancement, this blog post is not elaborate on the detailed Excel formatting of the product. The entire formatting (static / conditional) along with dashboard creations will itself a product enhancement which will be addressed in later posts.

Excel Work

Update All – One of my friend (who is not Excel expert but expert of another technology) is using the workbook and mentioned to me that calculate now (pressing F9) is not updating the latest prices. This is mainly because of VBA UDF refresh bug which do not recalculates all the UDF function on sheet calculation. One way to solve this problem is use the Ctrl + Alt + F9 key strokes to make the full calculations of the workbook or use Ctrl + Alt + Shift + F9 key combination to make the full calculation rebuild of the workbook. Instead of making the user to remember and press these keys, we can write one line macro code and link it with a button. Following is the VBA code of these functionality –

Ctrl + Alt + F9 : Application.CalculateFull Ctrl + Alt + Shift + F9 : Application.CalculateFullRebuild

Here we are using only calculate full code and link to the button named as “Calculate All” NSE Symbol Lookup – The other functionality enhancement of NSE symbol lookup is done by the Excel Auto Suggest code as explained in the previous post, link. The related graphical user interface (GUI) is created through VBA userforms. When the user enters more than 3 characters in the inputbox, the list boxes updated with all company names with search string and also corresponding NSE ticker symbols. Once the user selects the desired companies / tickers and click on the “Add Selected Tickers” on the form, all the selected companies are added to the portfolio. Following is the user action flow diagram of this functionality – NSE Ticker Symbol Lookup Basic conditional formatting is applied on portfolio weight of the each stock and on returns. The product file of Excel portfolio management can be downloaded from this link, NSE Live Stock Prices in Excel Ver 2 – Portfolio Mangement Through Excel. <not working>

Please refer to the latest post for the updated version – NSE Live Stock (Share Price) Data into Excel – Part 3

Posted in Finance, Technology | Tagged , , , , , , , , | 5 Comments

NSE Live Stock (Share Price) Data into Excel

How do we check the stock prices? When any investor starts trading, they will login to their broker website (banks / brokerage houses) and check the prices of shares they are holding. But the trading hours are subset period of normal working hours. So even if you are not active in the brokerage site for 2 minutes because of your work, the brokerage site will automatically close the session and will force you to re-login through normal password / userid authentication process. Other way is to check directly from the stock exchange (NSE or BSE or NYSE) or from the news aggregation websites ( Google finance / Yahoo finance / Moneycontrol / Marketwatch …etc.). It is easy for checking the stock price of one or two stocks in this manner. Sooner or later, the number of holding stocks or interested stocks will increase significantly so retyping / refreshing the entire webpage is required to view all of them which may not a optimal solution. So these website has given an option to create individual portfolios.

Its looks simple but how does the website identify your portfolio / how do they link to your interested stocks. Currently all the websites requires completion of registration process and generate user id through which they can identify you. This is again leading to the same user id login process but these websites do not force you to session timeouts in general. Though the web technologies are developed to create user specific ads based on the stored cookies of web history but still we need to login to let them know who am I. Here we are not going to discuss on functioning of eCommerce and their over-valuation. So the need of tracking the share prices without authentication is not solved.

One of the easiest ways is to create simple HTML file with little JavaScript code to get the desired data; And editing it regularly to update the stock portfolio; We can achieve the desired result through MS Excel which is the tool in general used for portfolio tracking by small to medium capital investors. And Excel is also easy to edit when compared with editing HTML code to update the portfolio.

Not to reinvent the wheel, searched over internet to find an Excel application of the same use and soon found available in the most valued Excel website, Chandoo at this link. As usual, the article is great and given the download link to Excel file. The given workbook is fetching the data from Yahoo finance in CSV format and process the same to display the requisite date. In the process of playing around with the workbook, I couldn’t get the stock price values of shares listed in Indian exchanges NSE & BSE. First try to add “.NS” represent NSE but didn’t work. Why its not working? Because Yahoo stopped giving the CSV formatted download of the stock information for NSE and BSE. Searched over this topic on various investment forums and could not locate simple solution for this. So it has given me a reason for writing this post here.

Objective

To get the live stock prices from NSE for the desired stock with all the related details ( highs / lows / volumes / bids / asks)

The steps involved in performing this task is to get the 1>Data from internet and 2>Data process it to display the required content. Another objective of this exercise is to keep the VBA Macro code to minimum

1>Data from internet

One way of getting the data from internet is to link internet into Excel as explained in another blog post here. The data connection properties have to be modified for each stock which I tried to change without using VBA code without any success. So we have to use VBA code to fetch the data. One way is to create Data Connection and keep altering the parameters of connection for each stock with their stock ticker / symbols. Here, we are using “ServerXMLHTTP” object to map the required URL and get the relevant data. But another question that needs to be answered is from where we have to get the relevant data. Given there are so many websites which provides this data, it is little tricky to chose which website to use. Here, I decide to go with NSE exchange website itself. The URL for Infosys stock is http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=INFY&illiquid=0

This URL gives lot of big response that will be used for formatting the HTML. Just to get the relevant data only from NSE site, we can use its Ajax URL – http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/ajaxGetQuoteJSON.jsp?symbol=INFY

To keep the option of varying the URL different stocks, we will write a public function in VBA instead of sub routine.

Public Function getNSEData(scripID As String)  
    URL = "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/ajaxGetQuoteJSON.jsp?symbol=" & scripID
    Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    xmlHttp.Open "GET", URL, False
    xmlHttp.setRequestHeader "Content-Type", "text/JSON"
    xmlHttp.send
    getNSEData = xmlHttp.responseText    
End Function

Clearly, the function input parameter is appended in URL formation so that it can get the relevant details of called stock ticker id. Also, responseText parameter on the object is used get the desired response text.

2>Processing the data

Those who have experience of JavaScript programming will say it is easy to process the given data as it clearly forms JSON. But little Googling on this subject resulted no simple solution either of without adding class libraries or character by character processing. So decided to solve the data processing in Excel itself instead of much VBA Macro code.

If we observe the JSON response of the resulted text has very detailed information on the requested stock. The information obtained from URL includes prices of opening, closing, average, last traded, days high, days low, 52week high, 52week low; volumes data of total traded volume, total traded value; and stock margins & circuit hitter details.

In simple words, JSON notation will have the data in key and value format. So we need to know the keys for different keys for getting the desired information. Like open is the key opening price, low52 is the key for 52 week low price, high52 is the key for 52 week high price …etc. After identifying the which key we need to find, we need to fetch the corresponding value by string operations on the URL response. Sample JSON looks like

 ""companyName"":""Infosys Limited"",""averagePrice"":""1,922.56"",""secDate"":""12DEC2014"",""series"":""EQ""

Following is the steps that needs to be performed for getting the desired result of the identified key –

  • Sub string of the repsonse text with Excel MID function with starting point A and number of characters as B
  • where A = locate the key with Excel FIND function  + length of the key with Excel LEN function + JSON coding character length which is 3 for “:”
  • where B = locating first quote after the key is located (X) – A
  • where X = Excel FIND function with starting parameter as A (trick to find quote is use CHAR(34) as find character)

Very detailed approach on string manipulation is explained in this post.

After formatting and little use of Excel function usage, the we shall get the following result

NSE Stock Live Excel Share Price Real Time Data

The workbook used for this exercise is shared here NSE BSE Live Stock Prices in Excel <not working, refer to updated posts>. Here the given workbook is showing only the most referred values of the NSE Live data and in similar fashion detailed bid ask prices & quantities, price bands, margin …etc can be obtained with copying the formula with required key values.

Please refer to the latest post for the updated version – NSE Live Stock (Share Price) Data into Excel – Part 3

Posted in Technology | Tagged , , , , , | 46 Comments