[SOLVED] Scraping Morningstar web page

Scraping Morningstar web page

I want to scape the financials on this link.

I want to copy and paste all the data. It doesn't matter if it contains other information.

Link: http://financials.morningstar.com/cash-flow/cf.html?t=SPN&region=usa&culture=en-US

Sub MS()

    Sheets("Morningstar").ClearContents
        my_Page = "http://financials.morningstar.com/cash-flow/cf.html t=SPN&region=usa&culture=en-US"
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate my_Page
        Do Until .ReadyState = 4: DoEvents: Loop
    End With

    Application.EnableEvents = False
    IE.ExecWB 17, 0
    Do Until IE.ReadyState = 4: DoEvents: Loop
    IE.ExecWB 12, 2
    ActiveSheet.PasteSpecial Format:="HTML", link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
    Range("A1").Select
        
    IE.Quit
    Application.EnableEvents = True

End Sub

Solution

One way is to use the MSHTML library (you will need to enable this in references).

Load the html document into a object. IE.document Loop through the elements to extract the data you want.

Here are some good examples of parsing the information using VBA. You can use these techniques to get your data: Parse HTML content in VBA Pulling a table out of a mess of HTML w/ VBA & Excel http://www.ozgrid.com/forum/showthread.php?t=184695

Good luck. Remember to review regularly as site changes can break scraping code.