Excel VBA爬取Yahoo Finance比特币实时价格遇运行时错误91

错误原因 #

出现Runtime Error 91是因为代码尝试访问未初始化的对象，核心原因有两点：

1. Yahoo Finance页面依赖JavaScript动态渲染内容，MSXML2.XMLHTTP获取的是初始静态HTML，此时目标<fin-streamer>元素还未加载生成。
2. 代码中使用的yf-1tejb6是动态生成的类名，每次页面加载都会变化，用它定位元素完全不可靠。

解决方案 #

以下提供三种可行方案，按稳定性优先排序：

方案1：调用Yahoo Finance API（最稳定） #

直接通过官方API获取JSON格式的价格数据，不受页面结构变化影响：

Private Sub GetBTCPriceViaAPI()
    Dim request As Object
    Dim response As String
    Dim json As Object
    Dim price As Double
    
    Set request = CreateObject("MSXML2.XMLHTTP")
    ' 调用Yahoo Finance的Chart API获取BTC-USD数据
    request.Open "GET", "https://query1.finance.yahoo.com/v8/finance/chart/BTC-USD", False
    request.send
    
    If request.Status = 200 Then
        response = request.responseText
        ' 解析JSON需先导入VBA-JSON库（可在VBA编辑器中添加引用）
        Set json = JsonConverter.ParseJson(response)
        ' 提取实时价格
        price = json("chart")("result")(1)("meta")("regularMarketPrice")
        Range("AE1").Value = Format(price, "#,##0.00")
    Else
        Range("AE1").Value = "请求失败"
    End If
    
    Set request = Nothing
    Set json = Nothing
End Sub

方案2：用浏览器控件加载动态页面 #

利用IE/Edge控件等待页面完全渲染后，通过稳定的data-testid属性定位元素：

Private Sub Enter_Click()
    Dim ie As Object
    Dim targetElement As Object
    Dim price As String
    
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = False ' 设为True可查看页面加载过程
    ie.Navigate "https://ca.finance.yahoo.com/quote/BTC-USD/"
    
    ' 等待页面加载完成
    Do While ie.Busy Or ie.ReadyState <> 4
        DoEvents
    Loop
    
    ' 通过data-testid定位目标元素，比类名更可靠
    Set targetElement = ie.Document.querySelector("fin-streamer[data-testid='qsp-price']")
    
    If Not targetElement Is Nothing Then
        price = targetElement.innerText
        Range("AE1").Value = price
    Else
        Range("AE1").Value = "未找到价格"
    End If
    
    ie.Quit
    Set ie = Nothing
End Sub

方案3：优化原XMLHTTP代码（仅应急使用） #

如果页面静态HTML中仍包含目标数据，可通过标签名+属性筛选的方式定位元素，避开动态类名：

Private Sub Enter_Click()
    Dim request As Object
    Dim response As String
    Dim html As New HTMLDocument
    Dim streamerElements As Object
    Dim i As Integer
    
    Set request = CreateObject("MSXML2.XMLHTTP")
    request.Open "GET", "https://ca.finance.yahoo.com/quote/BTC-USD/", False
    request.send
    response = StrConv(request.responseBody, vbUnicode)
    html.body.innerHTML = response
    
    ' 遍历所有fin-streamer元素，通过data-symbol和data-testid筛选目标
    Set streamerElements = html.getElementsByTagName("fin-streamer")
    For i = 0 To streamerElements.Length - 1
        If streamerElements(i).getAttribute("data-symbol") = "BTC-USD" And _
           streamerElements(i).getAttribute("data-testid") = "qsp-price" Then
            Range("AE1").Value = streamerElements(i).innerText
            Exit For
        End If
    Next i
    
    ' 未找到元素时提示
    If i >= streamerElements.Length Then
        Range("AE1").Value = "未找到价格"
    End If
    
    Set request = Nothing
    Set html = Nothing
    Set streamerElements = Nothing
End Sub

内容的提问来源于stack exchange，提问作者Coded-Blood Junior