How to extract yahoo-finance analyst price targets by VBA?

I am trying to extract yahoo-finance analyst price targets by VBA(eg: no. of analyst, high, low, average, current)

but I can't extract any of these by .getelementsbyclassname/.getelementbyID.

Here is my code:

Sub Analysis_import()

Dim website As String

Dim request As Object

Dim response As String

Dim html As New HTMLDocument

Dim price As Variant

website = "https://finance.yahoo.com/quote/AAPL/analysis?p=AAPL"

Set request = CreateObject("MSXML2.XMLHTTP")

request.Open "get", website, False

request.setRequestHeader "If-Modified-since", "Sat, 1 Jan 2000 00:00:00 GMT"

request.send

response = StrConv(request.responseBody, vbUnicode)

html.body.innerHTML = response

price = html.getElementsByClassName("Fz(m) D(ib) Td(inh)").innerText

Debug.Print price

End Sub

What is the problem? Many thanks!

The fields you wish to grab from that site generate dynamically, hence you can't fetch them using HTMLDocument parser. If you want to locate the fields using tag, id ,class e.t.c, your options would be IE or Selenium.

However, the good news is the required fields are available in some script tag within raw json content. So, you can process them using vba json converter or regex even when you stick with xmlhttp requests. The following script is based on regex.

Sub GrabAnalysisInfo()
    Const Url As String = "https://finance.yahoo.com/quote/AAPL/analysis?p=AAPL"
    Dim sResp$, sHigh$, currentPrice$
    Dim analystNum$, sLow$, tMeanprice$

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", Url, False
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36"
        .send
        sResp = .responseText
    End With

    With CreateObject("VBScript.RegExp")
        .Pattern = "numberOfAnalystOpinions[\s\S]+?raw"":(.*?),"
        If .Execute(sResp).count > 0 Then
            analystNum = .Execute(sResp)(0).SubMatches(0)
        End If

        .Pattern = "targetMeanPrice[\s\S]+?raw"":(.*?),"
        If .Execute(sResp).count > 0 Then
            tMeanprice = .Execute(sResp)(0).SubMatches(0)
        End If

        .Pattern = "targetHighPrice[\s\S]+?raw"":(.*?),"
        If .Execute(sResp).count > 0 Then
            sHigh = .Execute(sResp)(0).SubMatches(0)
        End If

        .Pattern = "targetLowPrice[\s\S]+?raw"":(.*?),"
        If .Execute(sResp).count > 0 Then
            sLow = .Execute(sResp)(0).SubMatches(0)
        End If

        .Pattern = "currentPrice[\s\S]+?raw"":(.*?),"
        If .Execute(sResp).count > 0 Then
            currentPrice = .Execute(sResp)(0).SubMatches(0)
        End If
    End With

    Debug.Print analystNum, tMeanprice, sHigh, sLow, currentPrice
End Sub