VBA获得纳斯达克分析师价格目标(需要实际的$出现在我的代码中)当前输出工作，但显示$0

Question

Sub Get_Web_Data2(ByVal Target As Range)

    On Error Resume Next
    Dim request As Object
    Dim response As String
    Dim html As New HTMLDocument
    Dim website As String
    Dim price As Variant
    
    
    ' Website to go to
    website = "https://www.nasdaq.com/market-activity/stocks/" & Target.Value & "/analyst-research"
    
    ' Create the object that will make the webpage request.
    
    Set request = CreateObject("MSXML2.XMLHTTP")
    
    
    ' Where to go and how to go there - probably don't need to change this.
    request.Open "GET", website, False
    
    ' Get fresh data.
    request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
    
    ' Send the request for the webpage.
    request.send
    
    ' Get the webpage response data into a variable.
    response = StrConv(request.responseBody, vbUnicode)
    
    ' Put the webpage into an html object to make data references easier.
    html.body.innerHTML = response
    
    ' Get the price from the specified element on the page.
    price = html.getElementsByClassName("analyst-target-price__description").Item(0).innerText
    
    ' Output the price into a message box.
    
      If Target.Column = 4 Then
            Range("P" & Target.Row).Value = price
           
     End If
End Sub

Answer 1

数据是从API调用返回的，因此在调用当前URI时不会出现数据。

更新以调用API。它返回少量的json。使用json解析器似乎是过火了，所以我会使用更快的字符串操作。

值似乎是美元，所以将P列格式化为符号为$的货币。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim request As Object
    Dim response As String
    Dim website As String

    website = "https://api.nasdaq.com/api/analyst/" & LCase$(Target.Value) & "/targetprice"
    
    Set request = CreateObject("MSXML2.XMLHTTP")
    
    With request
        .Open "GET", website, False
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .send
        response = .responseText
    End With
    
    Dim price As String
    
    price = Left$(Right$(response, Len(response) - (InStr(response, "priceTarget"":") + Len("priceTarget"":") - 1)), InStr(Right$(response, Len(response) - (InStr(response, "priceTarget"":") + Len("priceTarget"":") - 1)), ",") - 1)

    Application.EnableEvents = False
    
    If Target.column = 4 Then
        Range("P" & Target.row).Value = price
    End If
    
    Application.EnableEvents = True

End Sub