CODEWITHSUNDEEP
webweb-scrapingyahoo-finance
Anthony
Anthony

Reputation: 3

VBA Adjustment for adaptablity

I have this code I would like to rewrite to get the Bid price from yahoo. The code currently gets the Last price, however I would like to get the Bid price and if the Bid price is zero, then get the last price. I tried exhaustively to rewrite it myself but was not successful. Can someone assist in my quest to rewrite this code.

Thank you kindly 

Sub GetRate()
    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/AAP181221C00170000?p=AAP181221C00170000"

    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

End Sub

Upvotes: 0

Views: 45

Answers (1)

SIM
SIM

Reputation: 22440

Try the following. It should fetch you the bid price if it is greater than 0 otherwise it will grab you the last price:

Sub GetRate()
    Const Url$ = "https://finance.yahoo.com/quote/AAP181221C00170000?p=AAP181221C00170000"
    Dim S$, elem As Object, post As Object

    With New XMLHTTP60
        .Open "GET", Url, False
        .send
        S = .responseText
    End With

    With New HTMLDocument
        .body.innerHTML = S

        Set elem = .querySelector("td[data-test='BID-value'] > span")
        If elem.innerText = 0 Then
            Set post = .querySelector("#quote-market-notice").ParentNode.FirstChild
            MsgBox post.innerText
        Else: MsgBox elem.innerText
        End If
    End With
End Sub

Exactly the way you have tried above other than .querySelector():

Sub GetRate()
    Const Url$ = "https://finance.yahoo.com/quote/AAP181221C00170000?p=AAP181221C00170000"
    Dim Http As New XMLHTTP60, Htmldoc As New HTMLDocument
    Dim elem As Object, post As Object

    With Http
        .Open "GET", Url, False
        .send
        Htmldoc.body.innerHTML = .responseText
    End With

    Set elem = Htmldoc.querySelector("td[data-test='BID-value'] > span")
    If elem.innerText = 0 Then
        Set post = Htmldoc.querySelector("#quote-market-notice").ParentNode.FirstChild
        MsgBox post.innerText
    Else: MsgBox elem.innerText
    End If
End Sub

Reference to add to the library:

Microsoft xml,v6.0
Microsoft Html Object Library

If you wanna learn how .querySelector() works, check out this link.

Upvotes: 0

Related Questions