Dynamically derive data from websites into excel.

[mshah23]

I wish to automate the calculation of my portfolio at the end of day for my analysis purposes.

https://nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=SBIN

The above web address needs to be dynamically modified to obtain data of stocks.
In the above address "SBIN" represents the stock name which is required to be changed as per scrip name
From the screen shot below, I wish to get data like Last Price (i.e. 279.55), Pr. Close, Open, High, Low and Close.
Is it possible?

 

[Domenic]

The following macro gets the data for the stock symbol specified in cell B2 of the active worksheet, and places the information in a newly created worksheet in the active workbook...

Option Explicit

Sub GetStockData()


    Dim IE As Object
    Dim HTMLDoc As Object
    
    Set IE = CreateObject("InternetExplorer.Application")
    Set HTMLDoc = CreateObject("htmlfile")
    
    With IE
        .Visible = False
        .navigate "https://nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=" & Range("B2").Value
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End With
    
    Set HTMLDoc = IE.document
    
    'add a new worksheet to the activeworkbook
    Worksheets.Add
    
    'add column headers to the newly created worksheet
    Cells(1, "a").Resize(, 8).Value = Array("Price", "Change", "Percentage Change", "Previous Close", "Open", "High", "Low", "Close")
    
    'last price
    Cells(2, "a").Value = HTMLDoc.getElementById("lastPrice").innerText
    
    'change
    Cells(2, "b").Value = HTMLDoc.getElementById("change").innerText
    
    'percentage change
    Cells(2, "c").Value = HTMLDoc.getElementById("pChange").innerText
    
    'previous close
    Cells(2, "d").Value = HTMLDoc.getElementById("previousClose").innerText
    
    'open
    Cells(2, "e").Value = HTMLDoc.getElementById("open").innerText
    
    'high
    Cells(2, "f").Value = HTMLDoc.getElementById("dayHigh").innerText
    
    'low
    Cells(2, "g").Value = HTMLDoc.getElementById("dayLow").innerText
    
    'close
    Cells(2, "h").Value = HTMLDoc.getElementById("closePrice").innerText
    
    Set IE = Nothing
    Set HTMLDoc = Nothing
    
End Sub

Hope this helps!

 

[mshah23]

I am getting this error called
"Compile Error. Variable not defined."

The following macro gets the data for the stock symbol specified in cell B2 of the active worksheet, and places the information in a newly created worksheet in the active workbook...

Option Explicit

Sub GetStockData()


    Dim IE As Object
    Dim HTMLDoc As Object
    
    Set IE = CreateObject("InternetExplorer.Application")
    Set HTMLDoc = CreateObject("htmlfile")
    
    With IE
        .Visible = False
        .navigate "https://nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=" & Range("B2").Value
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End With
    
    Set HTMLDoc = IE.document
    
    'add a new worksheet to the activeworkbook
    Worksheets.Add
    
    'add column headers to the newly created worksheet
    Cells(1, "a").Resize(, 8).Value = Array("Price", "Change", "Percentage Change", "Previous Close", "Open", "High", "Low", "Close")
    
    'last price
    Cells(2, "a").Value = HTMLDoc.getElementById("lastPrice").innerText
    
    'change
    Cells(2, "b").Value = HTMLDoc.getElementById("change").innerText
    
    'percentage change
    Cells(2, "c").Value = HTMLDoc.getElementById("pChange").innerText
    
    'previous close
    Cells(2, "d").Value = HTMLDoc.getElementById("previousClose").innerText
    
    'open
    Cells(2, "e").Value = HTMLDoc.getElementById("open").innerText
    
    'high
    Cells(2, "f").Value = HTMLDoc.getElementById("dayHigh").innerText
    
    'low
    Cells(2, "g").Value = HTMLDoc.getElementById("dayLow").innerText
    
    'close
    Cells(2, "h").Value = HTMLDoc.getElementById("closePrice").innerText
    
    Set IE = Nothing
    Set HTMLDoc = Nothing
    
End Sub

Hope this helps!

 

[Domenic]

Sorry, my mistake. Since we're using late binding, we need to replace the constant READYSTATE_COMPLETE with the actual value. Therefore, replace...

Do While .Busy Or .readyState <> READYSTATE_COMPLETE

with

Do While .Busy Or .readyState <> 4