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