How to extract yahoo-finance analyst price targets by VBA?
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