Hi
I've been using your macro <https://www.youtube.com/watch?v=IOzHacoP-u4> to pull data from yahoo.finance. It works brilliantly, thanks.
I've now modified the macro (pasted below) to point to morningstar (this url: https://www.morningstar.com/stocks/xnys/tgt/valuation). What I am trying to do is bring in the Price/Sales ratios from 2010 through 2020, but am having trouble pulling all the html so that I can select the specific data point.
The P/S ratio for 2010 is 0.66. When i inspect this element it has the tagname "ng-binding ng-scope" and the tagnumber 14.
When i run the macro it pulls the html, but not the full html. The message length of the pulled html is 76473 characters, but the full html seems to be much larger. I think this is why the macro cannot find the element within the html.
Any pointers for fixing this issue?
Thanks
Joe
p.s. the macro stalls at this line
'metric = html.getElementsByClassName(tagname).Item(tagnumber).innerText
and i get the error
Run Time Error 91: Object variable or with bock variable not set
MACRO
____________
Code_Goes_Here
Sub Get_Morningstar_Data()
Dim request As Object
Dim country As String
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim metric As String
Dim ticker As String
Dim row As Integer
Dim column As Long
Dim tagname As String
Dim tagnumber As Integer
Dim pricechange As Integer
Dim pe As Integer
Dim eps As Integer
Dim divyld As Integer
Dim fiftytwohigh As Integer
Dim fiftytwolow As Integer
Dim evebitda As Integer
Dim currentratio As Integer
Dim debtequity As Integer
For row = 4 To 4
ticker = ThisWorkbook.Worksheets("Sheet1").Range("b" & row).Text
country = ThisWorkbook.Worksheets("Sheet1").Range("d" & row).Text
If ticker = "" Then GoTo the_end:
If country = "" Then GoTo the_end:
' Website to go to.
If country = "can" Then
website = "https://www.morningstar.com/stocks/xtse/" & ticker & "/valuation"
Else
website = "https://www.morningstar.com/stocks/xnys/" & ticker & "/valuation"
End If
'MsgBox website
' 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)
'MsgBox response
'MsgBox Len(response)
' Put the webpage into an html object to make data references easier.
html.body.innerHTML = response
ThisWorkbook.Worksheets("Sheet1").Cells(20, 5) = response
For column = 16 To 16
tagname = ThisWorkbook.Worksheets("Sheet1").Cells(2, column).Text
tagnumber = ThisWorkbook.Worksheets("Sheet1").Cells(3, column).Value
'MsgBox tagname
'MsgBox tagnumber
' Get the price from the specified element on the page.
metric = html.getElementsByClassName(tagname).Item(tagnumber).innerText
' Output the price into a message box.
MsgBox metric
ThisWorkbook.Worksheets("Sheet1").Cells(row, column) = metric
metric = ""
Next column
Next row
the_end:
MsgBox "macro_finished"
Set request = Nothing
End Sub