Run-time Error '1004'

[mikeypsooner]

I have the following code below but for some reason I am getting the Run-time Error '1004'. Can anyone please help. I am not sure what this is related. Some information I found states its related to internet connection but I am connected to the internet.

Sub test()


    Sheet2.Select
    Sheet2.Cells.Clear
    
    qurl = "https://finance.yahoo.com/quote/GOOGL?p=GOOGL"


    With Sheet2.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheet2.Range("A1"))
 
        .BackgroundQuery = True
        .Refresh BackgroundQuery:=False


    End With


End Sub

 

[Domenic]

When I tried it, I too got an error. It looks like you will have to use another method. Which data do you want to retrieve from that web page?

 

[bobsan42]

The error is due to the quantity and complexity of the data on the page.
You will need another method to specify which part of the data you need.

 

[mikeypsooner]

I guess just to pull the first table off is okay with me. What would that look like and would it work? Then I can edit how needed.

 

[Domenic]

This method uses Internet Explorer and the HTML object. First, set the following references (VBE > Tool > References)...

1) Microsoft Internet Controls

2) Microsoft HTML Object Library

Then try...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] GetStockData()

    [COLOR=darkblue]Dim[/COLOR] IE [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] SHDocVw.InternetExplorer
    [COLOR=darkblue]Dim[/COLOR] HTMLDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSHTML.HTMLDocument
    [COLOR=darkblue]Dim[/COLOR] HTMLRows [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElementCollection
    [COLOR=darkblue]Dim[/COLOR] HTMLRow [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElement
    [COLOR=darkblue]Dim[/COLOR] wksDest [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] wksDest = Sheet2
    wksDest.Cells.Clear
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    IE.navigate "https://finance.yahoo.com/quote/GOOGL?p=GOOGL"
    IE.Visible = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] IE
        [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] .Busy [COLOR=darkblue]Or[/COLOR] .readyState <> READYSTATE_COMPLETE
            DoEvents
        [COLOR=darkblue]Loop[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = IE.document
    
    [COLOR=darkblue]With[/COLOR] HTMLDoc.getElementsByClassName("D(ib) Fw(200) Mend(20px)")(0)
        wksDest.Range("A1").Value = .Children(0).innerText
        wksDest.Range("B1").Value = .Children(1).innerText
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] HTMLRows = HTMLDoc.getElementsByClassName("D(ib) W(1/2) Bxz(bb)")(0).getElementsByTagName("tr")
    
    r = 3
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLRow [COLOR=darkblue]In[/COLOR] HTMLRows
        wksDest.Cells(r, "A").Value = HTMLRow.Cells(0).innerText
        wksDest.Cells(r, "B").Value = HTMLRow.Cells(1).innerText
        r = r + 1
    [COLOR=darkblue]Next[/COLOR] HTMLRow
    
    [COLOR=darkblue]Set[/COLOR] HTMLRows = HTMLDoc.getElementsByClassName("D(ib) W(1/2) Bxz(bb)")(1).getElementsByTagName("tr")
    
    r = 3
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLRow [COLOR=darkblue]In[/COLOR] HTMLRows
        wksDest.Cells(r, "D").Value = HTMLRow.Cells(0).innerText
        wksDest.Cells(r, "E").Value = HTMLRow.Cells(1).innerText
        r = r + 1
    [COLOR=darkblue]Next[/COLOR] HTMLRow
    
    Sheet2.Activate
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
    MsgBox "Completed...", vbInformation
    
    [COLOR=darkblue]Set[/COLOR] IE = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLRows = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLRow = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wksDest = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Here's an example of the results you'll get...

A1:E10

[TABLE="width: 950"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: right"]964.61[/TD]
[TD="class: xl63, width: 64"]+5.39 (+0.56%)[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Previous Close[/TD]
[TD="class: xl63, align: right"]959.22[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Market Cap[/TD]
[TD="class: xl63"]658.15B[/TD]
[/TR]
[TR]
[TD="class: xl63"]Open[/TD]
[TD="class: xl63, align: right"]963.55[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Beta[/TD]
[TD="class: xl63, align: right"]0.92[/TD]
[/TR]
[TR]
[TD="class: xl63"]Bid[/TD]
[TD="class: xl63"]964.26 x 200[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]PE Ratio (TTM)[/TD]
[TD="class: xl63, align: right"]32.6[/TD]
[/TR]
[TR]
[TD="class: xl63"]Ask[/TD]
[TD="class: xl63"]964.41 x 100[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]EPS (TTM)[/TD]
[TD="class: xl63, align: right"]29.59[/TD]
[/TR]
[TR]
[TD="class: xl63"]Day's Range[/TD]
[TD="class: xl63"]960.35 - 965.90[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Earnings Date[/TD]
[TD="class: xl63"]‎Jul‎ ‎26‎, ‎2017 - ‎Jul‎ ‎31‎, ‎2017[/TD]
[/TR]
[TR]
[TD="class: xl63"]52 Week Range[/TD]
[TD="class: xl63"]672.66 - 965.90[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Dividend & Yield[/TD]
[TD="class: xl63"]N/A (N/A)[/TD]
[/TR]
[TR]
[TD="class: xl63"]Volume[/TD]
[TD="class: xl64, align: right"]966,086[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Ex-Dividend Date[/TD]
[TD="class: xl63"]N/A[/TD]
[/TR]
[TR]
[TD="class: xl63"]Avg. Volume[/TD]
[TD="class: xl64, align: right"]1,495,863[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]1y Target Est[/TD]
[TD="class: xl65, align: right"]1,054.17[/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps!

 

[mikeypsooner]

Domenic this is great and very useful for me.

Building off the work you compiled how would one pull the data from the tables on the webpage https://finance.yahoo.com/quote/VTI/holdings?p=VTI

Looking to get the Overall Portfolio Composition (%), Sector Weightings (%), Equity Holdings, Bond Ratings, Top 10 Holdings (14.91% of Total Assets), and Fund Overview in tables similar to the one you provided. I am not sure how you located table names or the getElementsByClassName variables from the page. Not to familiar with that.

 

[Domenic]

For your new question, I suggest you start a new thread...

 

[mikeypsooner]

Will do thanks Domenic. Again appreciate the help.