Import Web Data Using VBA - issues pulling data from morningstar

0

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
Answer
Discuss

Discussion

Not everything you see on a web site actually is on that particular website. Sites like morningstar may pull data from other sites - their own or third parties'. Especially if it's their own the purpose may be to disable download.
On the other hand, the error you encounter indicates a mistake in your code's syntax.You would need to amend your question to include your code.
Variatus (rep: 4729) Dec 26, '20 at 6:46 pm
You say the macro stalls at this line 'metric = html.getElementsByClassName(tagname).Item(tagnumber).innerText

I've only glanced at that video but you need to specify a valid tagname and tagnumber from the target webpagfor that line to work, right? (Don shows you how to get those in the video I believe)
John_Ru (rep: 1267) Dec 27, '20 at 4:57 am
Oops, I withdraw my comment, asuming those cells in column 16 are populated with a valid string/ integer - and you've checked for spaces in the string or tried TRIM(tag name)
John_Ru (rep: 1267) Dec 27, '20 at 9:25 am
Add to Discussion

Answers

0

Please look for this line in your code, ThisWorkbook.Worksheets("Sheet1").Cells(20, 5) = response. It dumps the entire page into cell E5 of your workbook's "Sheet1" tab. 

In the next line you see For column = 16 To 16. By rights there should be nothing in column 16, at least, nothing related to this downoad because all that was downloaded is in cell E5.

Therefore there is a line of code missing that splits the download into columns. I presume that the TextToColumns function is used. It probably got omitted when copying code from Don's template.

Of course, since cells P2 and P3 are either blank or contain something left over from the last scrape the variables tagname and tagnumber can't be set correctly and the line metric = html.getElementsByClassName(tagname).Item(tagnumber).innerText will fail for this reason.

Discuss

Discussion

Hi Variatus

Thanks for your response.

ThisWorkbook.Worksheets("Sheet1").Cells(20, 5) is simply my dump of the response so i could see what i was getting, that's all, i was not intending to parse the response across columns.

The tagname and tagnumber are correct - I've msgboxed them to check.

I think the key issue here is the table data is not being pulled in the response - perhaps it is the way morningstar created the table and my code does not work for this situation.

Thanks as always for any help.
joepjryan Dec 28, '20 at 8:20 am
Hello Joe,
Surely, if the contents of P2:P3 is the key to understanding your problem you should stand a better chance of getting help if you were to disclose what's there. On the other hand, if you prefer to continue without help from here, the next step is to ascertain that tag and number are both included in the dumped html data in E5. finding them there will tell you one thing and not finding them another.
Variatus (rep: 4729) Dec 28, '20 at 9:00 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login