Scrapping data from web when it's not in the HTML

[chufo_q09]

Hey y'all!

So, regarding an "old" thread I saw in this forum, one of the users requested if it was possible to pull data from the tables of the webpage below. The itchy point is that the HTML code of the website doesn't contain the info of the tables, which makes us think of a different solution.


So, my expert friends, what are your proposals? I have no clue right now and it is a really interesting and useful topic for those who want to deep in web scrapping!

Thaaank you, link below

http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=GNTX&region=USA&culture=en-CA&ops=clear

 

[mrshl9898]

Which data isn't in a table? I've only scraped a dozen or so pages, but this looks to be a bunch of tables to me.

Plan B would be to copy everything across and get the information through Formulas.

 

[mrshl9898]

Sub scrapeDATA()

Dim rownum As Long
Dim strUniName As String
Dim appIE As Object
Dim Elements As IHTMLElementCollection
Dim Element As IHTMLElement
Dim HTMLdoc As MSHTml.HTMLDocument
Dim objShellWindows As New SHDocVw.ShellWindows


Application.ScreenUpdating = False
Sheets("ChargeData").Cells.ClearContents


Set appIE = CreateObject("internetexplorer.application")


        With appIE
            .Visible = True
            .Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=GNTX&region=USA&culture=en-CA&ops=clear"
        
            While appIE.Busy Or appIE.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
        End With
        
        
        Set HTMLdoc = appIE.Document
        ProcessHTMLPage HTMLdoc
        appIE.Quit
                
                
Application.ScreenUpdating = True


End Sub








Sub ProcessHTMLPage(HTMLPage As MSHTml.HTMLDocument)


    Dim HTMLTable As MSHTml.IHTMLElement
    Dim HTMLTables As MSHTml.IHTMLElementCollection
    Dim HTMLInput As MSHTml.IHTMLElement


    Set HTMLTables = HTMLPage.getElementsByTagName("table")
    
    For Each HTMLTable In HTMLTables
    
            For Each HTMLRow In HTMLTable.getElementsByTagName("td")
            
                Debug.Print HTMLTable.innerText
            
            Next HTMLRow
    
    Next HTMLTable
   
End Sub

 

[mrhstn]

In Excel - Data >> From Web >> Enter URL >> Select the table(s) you want >> Load.

 

[mrshl9898]

Looping through rows above, copying all to "ChargeData" Sheet below:

Sub scrapeDATA()

Dim rownum As Long
Dim strUniName As String
Dim appIE As Object
Dim Elements As IHTMLElementCollection
Dim Element As IHTMLElement
Dim HTMLdoc As MSHTml.HTMLDocument
Dim objShellWindows As New SHDocVw.ShellWindows


Application.ScreenUpdating = False
Sheets("ChargeData").Cells.ClearContents


Set appIE = CreateObject("internetexplorer.application")


        With appIE
            .Visible = True
            .Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=GNTX&region=USA&culture=en-CA&ops=clear"
        
            While appIE.Busy Or appIE.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
    
            x = .Document.body.innerText
            x = Replace(x, Chr(10), Chr(13))
            x = Split(x, Chr(13))
            Sheets("ChargeData").Range("A1").Resize(UBound(x)) = Application.Transpose(x)
        End With
        
        appIE.Quit
                
                
Application.ScreenUpdating = True


End Sub

 

[chufo_q09]

yeah!! i get your solution! amazing! but in that case, recovering the data by formulas looks really difficult, isn't it?

isn't it possible to distribute numbers and values spread by columns apart from rows?

thank youuu!