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

Advertisements

About Paripurnachari

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

46 Responses to NSE Live Stock (Share Price) Data into Excel

  1. srinath says:

    How to get real time BSE share price

  2. Pingback: NSE Live Stock (Share Price) Data into Excel – Part 2 | Thinking Through Excel A1

  3. Pingback: NSE Live Stock (Share Price) Data into Excel – Part 2 « ExploreAbout.com

  4. Rajat says:

    Hi,

    I am not very well versed with VBA code. Can you please provide the modified code for BSE. Need it for a project. Really appreciate your help. Thanks in advance

  5. Pingback: BSE Live Stock (Share Price) Data into Excel | Thinking Through Excel A1

  6. Bimal says:

    Hi

    This is absolutely brilliant work. Thank you for sharing.

    I am trying to get information for indexes. I tried using symbols “Nifty”, “CNXNifty”, “CNX Nifty”, “IndiaVix” but could not get the information.

    Can you please help

  7. Pingback: NSE Live Indices into Excel | Thinking Through Excel A1

  8. Keerthan says:

    Hi there, have been trying to get live data into excel for some time now and today I find it here. If I could ask you for a favor, could you generate a similar file to pull out Futures OHLC data? Even if you do not want to do it, I am still grateful to you for this wonderful post.
    PS- I use excel for many things but I have had no exposure to VBA/Macro coding. That is why I am asking you for this favor. I would love to learn macro/VBA coding in Excel from scratch. Could you point me out to some resources please…

  9. Pingback: NSE Live Equity Future Prices in Excel | Thinking Through Excel A1

  10. Amit Bajaj says:

    Thanks a ton for developing nse and bse live stock price in excel. Very handy, and open the arena for precise entry and exits, holding etc

  11. Darshan N S says:

    Hi,
    Wanted to know how to download data with Special characters in the name, Eg M&M, M&Mfin. thanks.

  12. Code in symbol is not working i.e. M&M Mahindra & Mahindra

  13. Munavalli says:

    Thanks a lot for this post. I had been searching for a solution like this for quite sometime.

  14. Naman Shah says:

    Hi,
    I downloaded your excel workbook and used the code I needed.
    While it works for other scripts, it is not working with scripts like M&M.
    It seems the special char involved is causing the break.
    Any way to solve this?
    Thanks a lot for your effort,

  15. Yogesh says:

    Excellent Work!

  16. Shiva says:

    Hey Paripurnachari,

    I have been using your Excel template for quite some time now and it was brilliant. However it stopped working since couple of days. Looks like the way NSE website publishes data has changed the format. Can you please look into it ?

  17. Aditya Roongta says:

    Hi vppc,

    I started using your nse and bse live stock prices excel. Thanks for making these sheets.
    Recently, the nse live stock prices macro stopped working. I am guessing it could be due to a change in the url of nse. Could you please find time to check it at your end. I am unable to figure out. Thanks!

    Cheers!

  18. Amit Shukla says:

    The macro is not working… It has stop providing live price of Future segment scrip.. is there any alternate or any correction in the VBA..?

  19. Kamal says:

    I was trying to get nse data but the link which is given by you for infosis is giving access denied

  20. Kamal says:

    Earlier it was working but after one day it starts giving access denied. I am out of India. It may be the reason

  21. Ruhul Amin says:

    Access Denied from 11th May 2017, Please check provide a Solution.

  22. ZPDEX says:

    NSE blocked that link, kindly make something with google data

  23. SAMEER GUPTA says:

    Nse site have changed the url from http to https such that data is not coming in the excel file, Kindly find the solution for the same.

  24. Moorthi says:

    Hi Paripurnachari, I was looking for this solution for quite some time and did lot of searching. Thanks a ton for this beautiful post…

  25. Asad Ahmad says:

    Hi
    Thanks your effort to import live data into excle. I have download your updated excel sheets ver 4. But this is not working for ABGSHIP, ADSL, AFL, AGARIND, AGROPHOS, AIRAN, AKASH, ALPA and many more. Please suggest me if any possibilities to solve the problem.
    Thanks

    Dr. Asad Ahmad
    Maya Institute of Technology & Management
    Dehradun Mob: 9760070017

  26. Waheed Syed says:

    The NSE url to fetch data no longer works. “Access Denied”. Let me know if u know of any alternative for this

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