Get Number from website

[Av8tordude]

On Yahoo finance, the line in the HTML code below is the number (13.00) and I would to display a msgbox with the results . I manage to put together some code watching youtube videos, however i can not get the number to display in a msgbox. Then msgbox should say 13.00. Can you assist.

Many thanks

Range("A1") = DIS181123C00102000

Private Sub Worksheet_Change(ByVal Target As Range)
Dim IE As New InternetExplorer


IE.Visible = True
IE.navigate "https://finance.yahoo.com/quote/" & Range("A1") & "?p=" & Range("A1")
Do
    DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim sDD As String
sDD = Doc.getElementsByClassName("D(ib)")(1).innerText


MsgBox sDD 


End Sub

 

[Av8tordude]

here is the HTML element information....

span class="Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)" data-reactid="35">13.00

 

[strooman]

Try this:

Sub GetRate()
'*******************************************************************************
'Set references to:
'Microsoft Internet Controls
'Microsoft HTML Object Library
'Microsoft XML, v6.0
'Hit Alt+F11 to go to the Visual Basic Editor and then hit:
'Tools | References
'*******************************************************************************
    Dim XMLPage As New MSXML2.XMLHTTP60
    Dim htmlDoc As New MSHTML.HTMLDocument
    Dim URL As String
    Dim HTMLspans As MSHTML.IHTMLElementCollection
    Dim HTMLspan As MSHTML.IHTMLElement
    
    URL = "https://finance.yahoo.com/quote/" & Range("A1") & "?p=" & Range("A1")
    XMLPage.Open "GET", URL, False
    XMLPage.send
    
    htmlDoc.body.innerHTML = XMLPage.responseText
    
    Set HTMLspans = htmlDoc.getElementsByTagName("span")
    
    For Each HTMLspan In HTMLspans
        If HTMLspan.className = "Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)" Then
            MsgBox HTMLspan.innerText
        End If
    Next HTMLspan
    
End Sub

 

[Av8tordude]

Thank you Stooman for the help. I tried to implement your code to loop through rows 3-12, but I'm not getting any results. Can you offer any assistance.? Here's my attempt. Also, how can I get the code to run faster considering the looping?

For i = 3 To 12    
    URL = "https://finance.yahoo.com/quote/" & Range("A" & i) & Format(Range("H" & i), "yymmdd" & i) & Left(Range("E" & i), 1) & Format(Range("G" & i) * 1000, "00000000") & "?p=" & _
        Range("A" & i) & Format(Range("H" & i), "yymmdd") & Left(Range("E" & i), 1) & Format(Range("G" & i) * 1000, "00000000")
    
    XMLPage.Open "GET", URL, False
    XMLPage.send
    
    htmlDoc.body.innerHTML = XMLPage.responseText
    
    Set HTMLspans = htmlDoc.getElementsByTagName("span")
    
    For Each HTMLspan In HTMLspans
        If HTMLspan.className = "Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)" Then
            
            'Range("M3") = CDbl(HTMLspan.innerText) ', "$#,0.00")
            
            Debug.Print Format(HTMLspan.innerText, "$#,0.00")


        End If
    Next HTMLspan
Next i

 

[Av8tordude]

disregard. I manage to accomplish it. Thank you very much for your help.

Sub GetRate()'*******************************************************************************
'Set references to:
'Microsoft Internet Controls
'Microsoft HTML Object Library
'Microsoft XML, v6.0
'Hit Alt+F11 to go to the Visual Basic Editor and then hit:
'Tools | References
'*******************************************************************************
    Dim XMLPage As New MSXML2.XMLHTTP60
    Dim htmlDoc As New MSHTML.HTMLDocument
    Dim URL As String
    Dim HTMLspans As MSHTML.IHTMLElementCollection
    Dim HTMLspan As MSHTML.IHTMLElement
    Dim i As Long
    
    
    For i = 3 To 12


    URL = "https://finance.yahoo.com/quote/" & Range("A" & i) & Format(Range("H" & i), "yymmdd") & Left(Range("E" & i), 1) & Format(Range("G" & i) * 1000, "00000000") & "?p=" & Range("A" & i) & Format(Range("H" & i), "yymmdd") & Left(Range("E" & i), 1) & Format(Range("G" & i) * 1000, "00000000")
    XMLPage.Open "GET", URL, False
    XMLPage.send
    
    htmlDoc.body.innerHTML = XMLPage.responseText
    
    Set HTMLspans = htmlDoc.getElementsByTagName("span")
    
    For Each HTMLspan In HTMLspans
        If HTMLspan.className = "Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)" Then
            
            Debug.Print HTMLspan.innerText
            
        End If
    Next HTMLspan
    
    Next i
    
End Sub

 

[Av8tordude]

I'm having trouble rewriting this code to get the "Bid" Price. The price I want is 2.80 Can anyone assist. It would be greatly appreciated

td class="Ta(end) Fw(b) Lh(14px)" data-test="BID-value">
span class="Trsdu(0.3s) ">2.80</span></td>

 

[Av8tordude]

I never got an answer to my question....Can anyone assist. It would be greatly appropriated. Thanks

 

[strooman]

You need to give more information. For example, what are the values in columns A, G, H, E etc. An example with sample data would be convenient. What is your goal? What values are you interested in?

 

[Av8tordude]

The URL in the code are value that will be put together to look like the URL below...

'URL = 'https://finance.yahoo.com/quote/AAP181221C00170000?p=AAP181221C00170000'

The goal is to get the Bid Price, if the bid price is zero, then the original code value applies

 

[strooman]

I'm not sure if the webpage you visit to get your values, is a static page or dynamic. I mean, I think it's created on the fly. This means that the macro code will not give you always the desired values you're looking for but here is a try. I put some comment lines in it for further explanation. This presumes that Range("A1") = AAP181221C00170000

Sub GetBidPrice()
'****************************************************************************
'Set references to:
'Microsoft Internet Controls
'Microsoft HTML Object Library
'Microsoft XML, v6.0
'Hit Alt+F11 to go to the Visual Basic Editor and then hit:
'Tools | References
'****************************************************************************
    Dim XMLPage As New MSXML2.XMLHTTP60
    Dim htmlDoc As New MSHTML.HTMLDocument
    Dim URL As String
    Dim HTMLtables As MSHTML.IHTMLElementCollection
    Dim HTMLtable As MSHTML.IHTMLElement
    Dim HTMLtds As MSHTML.IHTMLElementCollection
    Dim HTMLtd As MSHTML.IHTMLElement
    
    'Create URL
    URL = "https://finance.yahoo.com/quote/" & Range("A1") & "?p=" & Range("A1")
    XMLPage.Open "GET", URL, False
    XMLPage.send
    
    'Get the source (code) of the webpage
    htmlDoc.body.innerHTML = XMLPage.responseText
    
    'Set reference to all tables in htmlDoc.body.innerHTML
    Set HTMLtables = htmlDoc.getElementsByTagName("table")
    
    'Loop through all table-tags . . .
    For Each HTMLtable In HTMLtables
        
        '. . . and search for classname "W(100%)"
        If HTMLtable.className = "W(100%)" Then
            
            'When found, set reference to all td elements in table
            Set HTMLtds = htmlDoc.getElementsByTagName("td")
            
            'Loop through all td elements . . .
            For Each HTMLtd In HTMLtds
                
                '. . . and search for the attribute "data-test" with value "BID-value"
                If HTMLtd.getAttribute("data-test") = "BID-value" Then
                    
                    'When found, print value to immediate window
                    Debug.Print HTMLtd.innerText
                End If
            Next HTMLtd
        End If
    Next HTMLtable
End Sub