Get real-time data (stock quote) from Website in Excel using VBA Macro

This macro does not use "Internet Explorer" like this previous example.

The website that this macro will web-scrape is Rediff Money. Its a very popular website for stock traders in India. However, the macro that I am sharing here, can be used to web-scrape other silimar sites like "yahoo" finance. You just have to make slight changes to code. That's it.

Contact me now if you need any help to create a portfolio in Excel.

Open Excel and save the file in ".xlsx" format. This is a macro enabled Excel format.

Now, open the VBA editor and choose Module from project explorer.

Before, writing the code, you need to add a reference.

Follow these steps.

1) In the VBA editor, find "Tools" tab from the top menu and click References.

2) In the "References" dialog box, find Microsoft HTML Object Library from the list and click the "checkbox" to select it.

Copy the code in the Module.

Private Sub getStockPrice()
    Dim sSiteName
    sSiteName = "https://money.rediff.com/companies/Ntpc-Ltd/15130025"

    ' Make a request for data.
    Dim objXmlHttpRequest As Object
    Set objXmlHttpRequest = CreateObject("MSXML2.ServerXMLHTTP")
    
    With objXmlHttpRequest
        .Open "GET", sSiteName, False
        
        .setRequestHeader "pragma", "no-cache"  ' clear website cache.
        .setRequestHeader "cache-control", "no-cache,max-age=0"
        .setRequestHeader "Content-Type", "text/xml"
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        
        .Send      ' send a request to the webpage.
    End With
    
    ' Get reponse in the form of data, from the webpage.
    Dim xmlResponse
    xmlResponse = vbNullString

    ' Store the retured data (HTML markup) into a varible.
    xmlResponse = StrConv(objXmlHttpRequest.responseBody, vbUnicode)

    Dim oHDoc As New HTMLDocument     ' create a document object. Remember, we have added a reference for this.
    oHDoc.Body.innerHTML = xmlResponse
    
    ' Show data in the Debug window.
    Debug.Print (oHDoc.getElementById("tickertime_bse").innerHTML) ' ticker time. the time and date when the data is updated.
    Debug.Print (oHDoc.getElementById("ltpid").innerHTML)   ' the current price.
    Debug.Print (oHDoc.getElementById("change").innerHTML)  ' the change in percentage.
End Sub

This is not a complete solution. Its just a technique that I have shared to explain how web-scraping is done in Excel using VBA Macro. Writing your own code gives you complete control on kind of data, you want in your Excel sheet.

In the beginning I have assigned Rediff Money Url, with script (or company name) along with a "unique id".

"https://money.rediff.com/companies/Ntpc-Ltd/15130025" ' the highlighted part is the script and company id.

Next, I have created an object of MSXML2.ServerXMLHTTP. With this object, I get access to the properties and methods that will help establish an HTTP connection with a website.

➡️ Read the documentation for MSXML2.ServerXMLHTTP

After making a successful request, XML data from the website is served and it is stored in a response variable, which is then assigned to "body" element of "HTMLDocument".

oHDoc.Body.innerHTML = xmlResponse

Now, to get the "stock price", "% change" etc. from the Body element of the document, you need to know the "id" (and some case the class name) of the HTML elements that holds the values. So, the "current stock price" in the website is displayed in <span> element with an ltpid. Similarly, the "change" is displayed using a SPAN element with the "change" id.

The data or content from an HTML element is extracted using the ".getElementById() method".

Contact me now if you need any help to create a portfolio in Excel.

← PreviousNext →