[SOLVED] Excel VBA : How to scrap a single value from yahoo finance webpage

Excel VBA : How to scrap a single value from yahoo finance webpage

I am trying to scrap the live BTC value on the yahoo finance webpage and store it in a cell.

The html structure is :

<fin-streamer class="livePrice yf-1tejb6" data-symbol="BTC-USD" data-testid="qsp-price" data-field="regularMarketPrice" data-trend="none" data-pricehint="2" data-value="69313.64" active="">
     <span>69,313.64</span>
</fin-streamer>

The value I want to store in a cell on a spreadsheet is 69,313.64.

Here is my code :

Private Sub Enter_Click()

Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim Website As String
Dim value As Variant

Website = "https://ca.finance.yahoo.com/quote/BTC-USD/"
Set request = CreateObject("MSXML2.XMLHTTP")

request.Open "GET", Website, False
request.send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response

value = html.getElementsByClassName("livePrice yf-1tejb6").Item(0).innerText
Range("AE1").value = value

End Sub

The line

value = html.getElementsByClassName("livePrice yf-1tejb6").Item(0).innerText

returns a "Runtime Error 91 : Object Variable or With block varaible not set". How shoud I fix the code and get the value? Thank you!

Solution

You can try the V8 Chart API from Yahoo as below;

Sub GetBTC()
    Dim URL As String, valBTC As Variant
    
    URL = "https://query1.finance.yahoo.com/v8/finance/chart/BTC-USD"
    
    With CreateObject("MSXML2.ServerXMLHTTP")
        .Open "GET", URL, False
        .send
        valBTC = Split(Split(.responseText, """regularMarketPrice"":")(1), ",")(0)
    End With
        
    MsgBox valBTC
End Sub