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

964.61	+5.39 (+0.56%)
Previous Close	959.22		Market Cap	658.15B
Open	963.55		Beta	0.92
Bid	964.26 x 200		PE Ratio (TTM)	32.6
Ask	964.41 x 100		EPS (TTM)	29.59
Day's Range	960.35 - 965.90		Earnings Date	‎Jul‎ ‎26‎, ‎2017 - ‎Jul‎ ‎31‎, ‎2017
52 Week Range	672.66 - 965.90		Dividend & Yield	N/A (N/A)
Volume	966,086		Ex-Dividend Date	N/A
Avg. Volume	1,495,863		1y Target Est	1,054.17

<tbody>
</tbody>

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.