Need help getting table from HTML

user_4271447 0
Viewed 179

I had been successfully pulling mutual fund performance data from Marketwatch.com using the following code:

Dim A As Long
Dim B As Long
Dim C As Long
Dim Z As Long


For Z = 1 To 35

    Range("A1").Select
    ActiveCell.Offset((37 + (Z * 10)), 0).Select
    If ActiveCell.Value = "" Then
    Exit For
    Else
    End If
Dim oHTML       As Object
Dim oTable      As Object
Dim x           As Long
Dim Y           As Long
Dim vData       As Variant

Set oHTML = CreateObject("HTMLFile")

With CreateObject("WinHTTP.WinHTTPRequest.5.1")
.Open "GET", "http://www.marketwatch.com/investing/fund/vfinx", False
.send
oHTML.body.innerhtml = .responsetext
End With

For Each oTable In oHTML.Getelementsbytagname("table")
If oTable.classname = "fundstable" Then
    ReDim vData(1 To oTable.Rows.Length, 1 To oTable.Rows(1).Cells.Length)

        For x = 1 To UBound(vData)
        For Y = 1 To UBound(vData, 2)
            vData(x, Y) = oTable.Rows(x - 1).Cells(Y - 1).innertext
        Next Y
        Next x

    With ActiveCell.Offset(1, 0)
    .Resize(UBound(vData), UBound(vData, 2)).Value = vData
    End With
Exit For
End If
Next oTable

Next Z

Unfortunately, Marketwatch has added a Captcha to stop bots (i.e. me) from scraping their data. I don't know of anyway around this, so I figured I'd try another site.

I looked at Morningstar: http://performance.morningstar.com/fund/performance-return.action?t=VFINX&region=usa&culture=en_US

It appears that the table I want on that page would be: "table.r_table3 width955px print97" or just "r_table3 width955px print97", but neither one seems to work for me.

Any ideas?

Thanks!

web-scraping vba excel html
1 Answers

The data is loaded by javascript and won't be available via XMLHTTP request as scripts won't have run to load content.

You can use that second link, for example, with IE and introduce a wait to ensure info is loaded. I show getting the table with that class name at index 1. You can change the index here:

ele.item(1).outerHTML

So, for the next table use clipboard.SetText ele.item(2).outerHTML .

You can also loop the .Length of ele to get each table but ensure you write out to a different cell when you paste:

Dim i As Long
For i = 0 To ele.Length-1
    clipboard.SetText  ele.item(i).outerHTML
   'Etc   
Next

VBA:

Option Explicit

Public Sub GetInfo()
    Dim IE As New InternetExplorer, clipboard As Object
    Dim ele As Object, ws As Worksheet, t As Date, tableCount As Long
    Const MAX_WAIT_SEC As Long = 5
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    With IE
        .Visible = True
        .navigate "http://performance.morningstar.com/fund/performance-return.action?t=VFINX&region=usa&culture=en_US"

        While .Busy Or .readyState < 4: DoEvents: Wend

        With .document
            t = Timer
            Do
                DoEvents
                On Error Resume Next
                Set ele = .querySelectorAll(".r_table3.print97")
                tableCount = ele.Length
                On Error GoTo 0
                If Timer - t > MAX_WAIT_SEC Then Exit Do
            Loop While tableCount < 3

            If Not ele Is Nothing Then
                clipboard.SetText ele.item(1).outerHTML
                clipboard.PutInClipboard
                ws.Cells(1, 1).PasteSpecial
            End If
        End With
        .Quit
    End With
End Sub

user_62412350
Related