Speed of multiple URL web scraping

J

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
4,546
Office Version
  1. 2007
Platform
  1. Windows
Need some help with this code.

1) The current code leaves a bunch of IE windows open.
2) The current code leads to a Run-time error '-2147437259 (80004005)':
3) It takes forever to run, Hopefully someone can assist me in converting it to use MSXML2.XMLHTTP60 for example, I heard that works faster.


Code: 
'
'-----------------------------------------------------
'   Run-time error '-2147437259 (80004005)':    ' This Error Occurs, eventually, in the 'Yahoo_One_Year_Estimates_Scrape_Error' section \/ \/ \/
'                                                   Also many internet explorer windows are left open that should have been closed
'
'   Automation Error
'   Unspecified Error
'-----------------------------------------------------
'
'
'   Global Variables That will be used
'
    Public Doc                                      As HTMLDocument
'
    Public StockMainPageURL                         As String       ' This will be the main portion of the URL that we send to Internet Explorer
    Public TotalURL                                 As String       ' This will be the complete URL that we send to Internet Explorer
'
    Public CellCounter                              As Integer      ' This will be used to adjust left to right on web site cells
    Public RowCounter                               As Integer      ' This adjusts the offset from the top of the spreadsheet to the start of the columns
    Public StockCount                               As Integer      ' This counts the actual stocks being analyzed currently
    Public TotalStocksToLoad                        As Integer      ' This counts the stocks that should be analyzed right now
'
    Public PageLoadAttempt                          As Long         ' This counts the number of times we have tried to load a page
'
'-------------------------------------------------------------------------------------------------------------------------------
'
Private Sub RefreshEntireDocument_Click()
'
'   This will Clear certain cell values in the spreadsheet when the $B$1 'Refresh' cell is clicked
'
    Range("$B$5:$K$254").Select                                 ' Select the range of $B$5 thru $J$254
    Selection.ClearContents                                     ' Delete the contents of this range
'
'
' -------------------------------------------------------------------------------------------------------------------------
'
'   Scrape stocks to consider looking into further from 1st URL page
'
    RowCounter = 5                                              ' Start loading stock values recieved into the 5th row of Excel
    MaxYahooDelay = 0                                           ' Initialize MaxYahooDelay = 0
'
    CellCounter = 0                                             ' Left to right cell counter
    PageLoadAttempt = 0                                         ' Initialize PageLoadAttempt = 0
    TotalStocksToLoad = 100                                     ' we will Scrape this amount of stocks from the 1st loaded page of stocks
'
    Call Scrape_BarChart_Stock_Page_1                           ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
'
'   Scrape stocks to consider looking into further from 2nd URL page
'
    CellCounter = 0                                             ' Left to right cell counter
    PageLoadAttempt = 0                                         ' Initialize PageLoadAttempt = 0
    TotalStocksToLoad = 100                                     ' we will Scrape this amount of stocks from the 2nd loaded page of stocks
'
    Call Scrape_BarChart_Stock_Page_2                           ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
'
'   Scrape stocks to consider looking into further from 3rd URL page
'
    CellCounter = 0                                             ' Left to right cell counter
    PageLoadAttempt = 0                                         ' Initialize PageLoadAttempt = 0
    TotalStocksToLoad = 50                                      ' we will Scrape this amount of stocks from the 3rd loaded page of stocks
'
    Call Scrape_BarChart_Stock_Page_3                           ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
' -------------------------------------------------------------------------------------------------------------------------
'
'   Scrape values from Yahoo to Update the one year estimates from previous pages of stocks scraped
'
    RowCounter = 5                                              ' Start loading stock values recieved into the 5th row of Excel
    PageLoadAttempt = 0                                         ' Initialize PageLoadAttempt = 0
    TotalYahooDelay = 0                                         ' Initialize TotalYahooDelay = 0
    TotalYahooPageAttempts = 0                                  ' Initialize TotalYahooPageAttempts = 0
    TotalStocksToLoad = 250                                     ' we will Scrape this amount of stocks from the 3rd loaded page of stocks

    Call Scrape_Yahoo_One_Year_Estimates                        ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
'
'   Display some final results in the status bar
    Application.StatusBar = "Spreadsheet Refreshing Complete :)" ' & "    Avg Yahoo Delay = " & AvgYahooDelay & "     Avg Yahoo Page Attempts = " & AvgYahooPageAttempts
'
End Sub
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'
Private Sub Scrape_Yahoo_One_Year_Estimates()                       ' *** Good up to here ***
'
'
    For StockCount = 1 To TotalStocksToLoad                         ' Grab One Year stock price estimate
'
'
ReloadScrape_Yahoo_One_Year_Estimates:
'
'       Load all of the Update one year estimates
        DelaySeconds = 0                                            '   Initialize DelaySeconds to zero
        PageLoadAttempt = PageLoadAttempt + 1                       '   Add 1 to our PageLoadAttempt counter
''''        TotalYahooPageAttempts = TotalYahooPageAttempts + 1         '   This will be the total yahoo Page Attempts
'
        StockMainPageURL = "finance.yahoo.com/quote/"               '   This will be the main portion of the URL that we send to Internet Explorer
        CurrentStockSymbol = Trim(Range("B" & RowCounter).Value)    '   This is the stock symbol that we will be addressing
'
'       Setup and Load the Internet Explorer Page ...
''''        Dim IE As New SHDocVw.InternetExplorer  ' This works
        Dim IE As New InternetExplorer
''      Dim IE As MSXML2.XMLHTTP60
''      Set IE = New MSXML2.XMLHTTP60
'
        TotalURL = "https://" & StockMainPageURL & CurrentStockSymbol   ' This will be the complete URL that we send to Internet Explorer
'
        If CurrentStockSymbol = 0 Or CurrentStockSymbol = "" Or IsEmpty(CurrentStockSymbol) = True Then ' If no stock symbol found @ $B?  then ...
            PageLoadAttempt = 0                                                                         '   Reset PageLoadAttempt = 0
            StockCount = TotalStocksToLoad                                                              '   Indicate no more stocks to load
'
            IE.Quit                                                                                     '   Close Internet Explorer Window
            Set IE = Nothing                                                                            '   Clear Internet Explorer Memory
'
            Exit Sub                                                                                    '   Exit this sub
        Else
'
            On Error GoTo Yahoo_One_Year_Estimates_Scrape_Error                                         '   If Error occurs then goto Yahoo_One_Year_Estimates_Scrape_Error
'
            Set IE = New InternetExplorer                                                               '   Open Internet Explorer Browser
'
'           Browser address that we will be scraping values from
            IE.navigate TotalURL                                                                        '   Load the Internet Explorer URL
'
'           Make the Browser window, that we will be scraping values from, visible
            IE.Visible = True                                           '   Make Internet Explorer Windows Visible
'
'           Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
            Do While IE.readyState <> 4 And DelaySeconds <= 19                                          '   Loop while IE is still loading and <= 19 seconds delayed
''              Application.Wait DateAdd("s", 1, Now)
                Application.Wait (Now + TimeValue("00:00:01"))                                          '   Delay for 1 second
                DoEvents                                                                                '   Enable Mouse Clicks
'
'               Update status bar to inform the user of what is occurring
                Application.StatusBar = "Loading website … " & TotalURL & "    Stock # " & (RowCounter - 4) ''''& _
''''                                "   Delay Seconds =  " & DelaySeconds & "    Page Load Attempts = " & PageLoadAttempt & _
''''                                "   Avg Yahoo Delay = " & AvgYahooDelay & "     AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
                DelaySeconds = DelaySeconds + 1                         '   Add 1 to our DelaySeconds Counter
'
''''                If DelaySeconds > MaxYahooDelay Then MaxYahooDelay = DelaySeconds   '   Save the MaxYahooDelay
''                  TotalYahooDelay = TotalYahooDelay + 1
'
            Loop                                                        ' Loop back
'
'           Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
            Do While IE.Busy And DelaySeconds <= 19 ' Or IE.readyState <> 4 And DelaySeconds <= 19  ' Loop while IE is still loading and <= 19 seconds delayed
''              Application.Wait DateAdd("s", 1, Now)
                Application.Wait (Now + TimeValue("00:00:01"))          '   Delay for 1 second
                DoEvents                                                '   Enable Mouse Clicks
'
'               Update status bar to inform the user of what is occurring
                Application.StatusBar = "Loading website … " & TotalURL & "    Stock # " & (RowCounter - 4) ''''& _
''''                                "   Delay Seconds =  " & DelaySeconds & "    Page Load Attempts = " & PageLoadAttempt & _
''''                                "   Avg Yahoo Delay = " & AvgYahooDelay & "     AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
                DelaySeconds = DelaySeconds + 1                         '   Add 1 to our DelaySeconds Counter
'
''''                If DelaySeconds > MaxYahooDelay Then MaxYahooDelay = DelaySeconds   '   Save the MaxYahooDelay
            Loop                                                        ' Loop back
'
'
            If DelaySeconds > 19 Then                                   ' If we have delayed for > 19 seconds to allow the page to load then ...
                IE.Quit                                                 '   Close Internet Explorer Window
'
                If PageLoadAttempt <= 4 Then GoTo ReloadScrape_Yahoo_One_Year_Estimates '   If we have'nt tried 4 reloads of this page then reload page again
            End If                                                      ' End If
'
            If PageLoadAttempt > 4 Then                                 ' If we have tried 4 reloads of the URL page then Display a message box & Exit program
                MsgBox "We've reloaded the same web page  " & PageLoadAttempt & " times without success so we're going to pause the program" & _
                " so you can investigate.", , "Multiple errors detected"
'
                PageLoadAttempt = 0                                     '   Reset PageLoadAttempt = 0
'
                Stop                                                    '   Stop this Excel program!
            End If
'
            Set Doc = IE.document
'
        End If
'
'
''''        TotalYahooDelay = TotalYahooDelay + DelaySeconds
''''        AvgYahooDelay = TotalYahooDelay / (RowCounter - 4)
''''        AvgYahooPageAttempts = TotalYahooPageAttempts / (RowCounter - 4)
'
'       Update status bar to inform the user of what is occurring
        Application.StatusBar = "Gathering Data from website … " & TotalURL & "    Stock # " & (RowCounter - 4) ''''& _
''''                                "   Delay Seconds =  " & DelaySeconds & "    Page Load Attempts = " & PageLoadAttempt & _
''''                                "   Avg Yahoo Delay = " & AvgYahooDelay & "    AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
        Range("J" & RowCounter).Value = Doc.getElementsByTagName("td")(11).innerText        '   Scrape the Yahoo 52 Week Price Range
        Range("K" & RowCounter).Value = Doc.getElementsByTagName("td")(31).innerText        '   Scrape the Yahoo One Year Price Estimate
'
        On Error GoTo 0                                                                     '   Clear Errors & Set Excel Error handling to Default
'
        RowCounter = RowCounter + 1                                                         '   Advance to next row in Excel sheet
'
        IE.Quit                                                                             '   Close Internet Explorer Window
        Set IE = Nothing                                                                    '   Clear Internet Explorer Memory
'
        PageLoadAttempt = 0                                                                 '   Reset PageLoadAttempt = 0
'
    Next                                                                                    '   Load next stock until all are loaded
'
    Exit Sub                                                                                ' Exit this Sub
'
Yahoo_One_Year_Estimates_Scrape_Error:
'
'   Tried this solution from google \/ \/ to solve errors, No luck :(                       ' Shut down all Internet Explorer windows
''    Dim wsh As Object
''    Dim windowStyle As Integer: windowStyle = 1
''    Dim waitOnReturn As Boolean: waitOnReturn = True
'
''    Set wsh = VBA.CreateObject("Wscript.Shell")
''    wsh.Run "taskkill /F /IM iexplore.exe", windowStyle, waitOnReturn
'
'
'
''    IE.Quit                                                                             '   Close Internet Explorer Window
    Set IE = Nothing                                                                    '   Clear Internet Explorer Memory
'
'   This works some what
    Set IE = New InternetExplorer                                                           ' Open Internet Explorer Browser
'
'
    Resume Next                                                                             ' Go back to the next line after the previous error occurred
'
End Sub
'________________________________________________________________________________________________________________________________________________________
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Hi

I think that there are some easy fixes here - there are some changes you can make to speed things up, but it's actually really tricky to read your code because, in part, you've left old code in there as comments.

In term of your points (1)-(3):

(1) The current code leaves a bunch of IE windows open. That's because the code inexplicably seems to keep creating new instances of Internet Explorer. This line:
VBA Code: 
Dim IE As New InternetExplorer
creates a new instance of IE, and then several lines later, the code makes yet another instance of it:
VBA Code: 
Set IE = New InternetExplorer         '   Open Internet Explorer Browser

Given that the code instantiating Internet Explorer sits inside a loop that runs 250 times, I'm not even a little bit surprised that this is taking a long time to run. There is no reason you can't just instantiate IE once and then get it to navigate to each URL each time.

2) The current code leads to a Run-time error '-2147437259 (80004005)':
In your error handling section, you've coded it so that it destroys the existing instance of IE, creates yet another, and then tries to return to the point where the code broke. That's not going to work, and is likely the source of your run-time errors. From an error-handling perspective, it's doesn't help you work out what the actual error might be or how to solve it.

3) It takes forever to run, Hopefully someone can assist me in converting it to use MSXML2.XMLHTTP60 for example, I heard that works faster.
Yes - see below. But is this the best way of getting the data you're after? Is there no API that would be a better source of information?

The code below won't do all that yours does - this is partly because a lot of your code is now redundant given that it doesn't use IE anymore, also because I couldn't entirely work what it was trying to do in the first place, and partly because it references code that you haven't provided. I would point out that this will run 250 times given that this is what you set the TotalStocksToLoad variable as - if you're not pulling data for 250 stocks, then I would suggest revising this.

Please do let me know if broadly does what you want it to do.

VBA Code: 
Private Sub RefreshEntireDocument_Click()
Dim Doc                                      As HTMLDocument
Dim StockMainPageURL             As String       ' This will be the main portion of the URL that we send to Internet Explorer
Dim TotalURL                              As String       ' This will be the complete URL that we send to Internet Explorer
Dim RowCounter                        As Integer      ' This adjusts the offset from the top of the spreadsheet to the start of the columns
Dim StockCount                         As Integer      ' This counts the actual stocks being analyzed currently
Dim strHTML                              As String

Set Doc = New HTMLDocument
StockMainPageURL = "https://finance.yahoo.com/quote/"               '   This will be the main portion of the URL
RowCounter = 5
TotalStocksToLoad = 250
For StockCount = 1 To TotalStocksToLoad                                          ' Grab One Year stock price estimate
    CurrentStockSymbol = Trim(Range("B" & RowCounter).value)      '   This is the stock symbol that we will be addressing
    TotalURL = StockMainPageURL & CurrentStockSymbol                 ' This will be the complete URL
    strHTML = GetHTML(TotalURL)
   
    Doc.body.outerHTML = strHTML
   
    Range("J" & RowCounter).value = Doc.getElementsByTagName("td")(11).innerText
    Range("K" & RowCounter).value = Doc.getElementsByTagName("td")(31).innerText
   
    RowCounter = RowCounter + 1
    strHTML = ""
Next

Set Doc = Nothing

End Sub

Function GetHTML(strURL As String) As String
    Dim objHTTP As Object, strTemp As String
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    objHTTP.Open "GET", strURL, False
    objHTTP.send
    If objHTTP.Status = 200 Then
        strTemp = objHTTP.responseText
    Else
        'There has been an error
    End If
    GetHTML = strTemp
End Function
 
Upvote 0
Thank You so much Dan W for responding and my apologies for not responding back to you way sooner than this, situations arised, my appologies. I have supplied my current code at the bottom of this post. I took your advice, as best as I understand it, but I may have messed that up. I deleted the code that was originally commented out, I am sorry if that was confusing, I left it in originally to show what I have previously attempted to make this work. I also included the 'code not provided' , I didn't include that code originally because I had no issues with it's performance, but I have added it back for clarity.

Dan_W said:
Hi

I think that there are some easy fixes here - there are some changes you can make to speed things up, but it's actually really tricky to read your code because, in part, you've left old code in there as comments.

In term of your points (1)-(3):

(1) The current code leaves a bunch of IE windows open. That's because the code inexplicably seems to keep creating new instances of Internet Explorer. This line:
VBA Code: 
Dim IE As New InternetExplorer
creates a new instance of IE, and then several lines later, the code makes yet another instance of it:
VBA Code: 
Set IE = New InternetExplorer         '   Open Internet Explorer Browser

Given that the code instantiating Internet Explorer sits inside a loop that runs 250 times, I'm not even a little bit surprised that this is taking a long time to run. There is no reason you can't just instantiate IE once and then get it to navigate to each URL each time.
Click to expand...

I have taken this advice under advisement and eliminated all of the 'Dim IE As New InternetExplorer" lines and replaced those with one 'Public IE As New InternetExplorer' at the top of the script. I hope that is what you were referring to.

However, my current code still leaves Explorer windows open if the 20 second delay interval is reached and then the Explorer window subroutine is reloaded.

Dan_W said:
2) The current code leads to a Run-time error '-2147437259 (80004005)':
In your error handling section, you've coded it so that it destroys the existing instance of IE, creates yet another, and then tries to return to the point where the code broke. That's not going to work, and is likely the source of your run-time errors. From an error-handling perspective, it's doesn't help you work out what the actual error might be or how to solve it.
Click to expand...

I have applied an alternate solution to that previous approach.


Dan_W said:
3) It takes forever to run, Hopefully someone can assist me in converting it to use MSXML2.XMLHTTP60 for example, I heard that works faster.
Yes - see below. But is this the best way of getting the data you're after? Is there no API that would be a better source of information?

The code below won't do all that yours does - this is partly because a lot of your code is now redundant given that it doesn't use IE anymore, also because I couldn't entirely work what it was trying to do in the first place, and partly because it references code that you haven't provided. I would point out that this will run 250 times given that this is what you set the TotalStocksToLoad variable as - if you're not pulling data for 250 stocks, then I would suggest revising this.

Please do let me know if broadly does what you want it to do.

VBA Code: 
Private Sub RefreshEntireDocument_Click()
Dim Doc                                      As HTMLDocument
Dim StockMainPageURL             As String       ' This will be the main portion of the URL that we send to Internet Explorer
Dim TotalURL                              As String       ' This will be the complete URL that we send to Internet Explorer
Dim RowCounter                        As Integer      ' This adjusts the offset from the top of the spreadsheet to the start of the columns
Dim StockCount                         As Integer      ' This counts the actual stocks being analyzed currently
Dim strHTML                              As String

Set Doc = New HTMLDocument
StockMainPageURL = "https://finance.yahoo.com/quote/"               '   This will be the main portion of the URL
RowCounter = 5
TotalStocksToLoad = 250
For StockCount = 1 To TotalStocksToLoad                                          ' Grab One Year stock price estimate
    CurrentStockSymbol = Trim(Range("B" & RowCounter).value)      '   This is the stock symbol that we will be addressing
    TotalURL = StockMainPageURL & CurrentStockSymbol                 ' This will be the complete URL
    strHTML = GetHTML(TotalURL)
  
    Doc.body.outerHTML = strHTML
  
    Range("J" & RowCounter).value = Doc.getElementsByTagName("td")(11).innerText
    Range("K" & RowCounter).value = Doc.getElementsByTagName("td")(31).innerText
  
    RowCounter = RowCounter + 1
    strHTML = ""
Next

Set Doc = Nothing

End Sub

Function GetHTML(strURL As String) As String
    Dim objHTTP As Object, strTemp As String
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    objHTTP.Open "GET", strURL, False
    objHTTP.send
    If objHTTP.Status = 200 Then
        strTemp = objHTTP.responseText
    Else
        'There has been an error
    End If
    GetHTML = strTemp
End Function
Click to expand...

I haven't tried this code yet, I didn't want to mix my code that currently errors with your code that may error if I don't do it right. Crawl before walk. :)

Anywho this is the code that I have currently ...

VBA Code: 
'
'   Goals:
'       1) Scrape 250 stock names/values from 3 different web pages ... Main web page is the same for all 3, but the end of the url
'                                                               changes for each one
'
'           /\ /\ /\ /\ No problems with that thus far /\ /\ /\ /\
'
'       2) Load each of the 250 stocks previously scraped into a separate URL & Scrape some more values
'
'       3)  If the URL hasn't loaded in 20 seconds, then trigger error that closes that webpage & go back to the beginning of subroutine
'               to reload the URL & try again
'
'       4)      If the URL has been reloaded 4 times with no success, then stop the program
'
'-----------------------------------------------------
'
'   Problems encountered thus far:
'
'       1) Explorer fully unloaded windows that surpass the 20 second delay opens a new explorer window but does not close the
'           previous fully unloaded Explorer window
'
'-----------------------------------------------------
'
'
'   Global Variables That will be used
'
    Public Doc                                      As HTMLDocument
    Public IE                                       As New InternetExplorer
'
    Public StockMainPageURL                         As String       ' This will be the main portion of the URL that we send to Internet
'                                                                       Explorer
    Public TotalURL                                 As String       ' This will be the complete URL that we send to Internet Explorer
'
    Public CellCounter                              As Integer      ' This will be used to adjust left to right on web site cells
    Public RowCounter                               As Integer      ' This adjusts the offset from the top of the spreadsheet to the
'                                                                       start of the columns
    Public StockCount                               As Integer      ' This counts the actual stocks being analyzed currently
    Public TotalStocksToLoad                        As Integer      ' This counts the stocks that should be analyzed right now
'
    Public PageLoadAttempt                          As Long         ' This counts the number of times we have tried to load a page
'
'-------------------------------------------------------------------------------------------------------------------------------
'
Private Sub RefreshEntireDocument_Click()
'
'   This will Clear certain cell values in the spreadsheet when the $B$1 'Refresh' cell is clicked
'
    Range("$B$5:$K$254").Select                                 ' Select the range of $B$5 thru $K$254
    Selection.ClearContents                                     ' Delete the contents of this range
'
    Set IE = New InternetExplorer                               ' Open Internet Explorer Browser
'
' -------------------------------------------------------------------------------------------------------------------------
'
'   Scrape stocks to consider looking into further from 1st URL page
'
    RowCounter = 5                                              ' Start loading stock values recieved into the 5th row of Excel
    MaxYahooDelay = 0                                           ' Initialize MaxYahooDelay = 0
'
    CellCounter = 0                                             ' Left to right cell counter
    PageLoadAttempt = 0                                         ' Initialize PageLoadAttempt = 0
    TotalStocksToLoad = 100                                     ' we will Scrape this amount of stocks from the 1st loaded page of stocks
'
    Call Scrape_BarChart_Stock_Page_1                           ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
'
'   Scrape stocks to consider looking into further from 2nd URL page
'
    CellCounter = 0                                             ' Left to right cell counter
    PageLoadAttempt = 0                                         ' Initialize PageLoadAttempt = 0
    TotalStocksToLoad = 100                                     ' we will Scrape this amount of stocks from the 2nd loaded page of stocks
'
    Call Scrape_BarChart_Stock_Page_2                           ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
'
'   Scrape stocks to consider looking into further from 3rd URL page
'
    CellCounter = 0                                             ' Left to right cell counter
    PageLoadAttempt = 0                                         ' Initialize PageLoadAttempt = 0
    TotalStocksToLoad = 50                                      ' we will Scrape this amount of stocks from the 3rd loaded page of stocks
'
    Call Scrape_BarChart_Stock_Page_3                           ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
' -------------------------------------------------------------------------------------------------------------------------
'
'   Scrape values from Yahoo to Update the one year estimates from previous pages of stocks scraped
'
    RowCounter = 5                                              ' Start loading stock values recieved into the 5th row of Excel
    PageLoadAttempt = 0                                         ' Initialize PageLoadAttempt = 0
    TotalYahooDelay = 0                                         ' Initialize TotalYahooDelay = 0
    TotalYahooPageAttempts = 0                                  ' Initialize TotalYahooPageAttempts = 0
    TotalStocksToLoad = 250                                     ' we will Scrape this amount of stocks from the 3rd loaded page of stocks
'
    Call Scrape_Yahoo_One_Year_Estimates                        ' Scrape the amount of TotalStocksToLoad into excel
'
' -------------------------------------------------------------------------------------------------------------------------
'
'   Display some final results in the status bar
    Application.StatusBar = "Spreadsheet Refreshing Complete :)"
'
End Sub
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'
Private Sub Scrape_Yahoo_One_Year_Estimates()                       ' *** Good up to here ***
'
'
    For StockCount = 1 To TotalStocksToLoad                         ' Grab One Year stock price estimate
'
'
ReloadScrape_Yahoo_One_Year_Estimates:
'
'       Load all of the Update one year estimates
        DelaySeconds = 0                                            '   Initialize DelaySeconds to zero
        PageLoadAttempt = PageLoadAttempt + 1                       '   Add 1 to our PageLoadAttempt counter
'
        StockMainPageURL = "finance.yahoo.com/quote/"               '   This will be the main portion of the URL that we send to Internet Explorer
        CurrentStockSymbol = Trim(Range("B" & RowCounter).Value)    '   This is the stock symbol that we will be addressing
'
'       Setup and Load the Internet Explorer Page ...
        TotalURL = "https://" & StockMainPageURL & CurrentStockSymbol   ' This will be the complete URL that we send to Internet Explorer
'
        If CurrentStockSymbol = 0 Or CurrentStockSymbol = "" Or IsEmpty(CurrentStockSymbol) = True Then ' If no stock symbol found @ $B?  then ...
            PageLoadAttempt = 0                                                                         '   Reset PageLoadAttempt = 0
            StockCount = TotalStocksToLoad                                                              '   Indicate no more stocks to load
'
            IE.Quit                                                                                     '   Close Internet Explorer Window
            Set IE = Nothing                                                                            '   Clear Internet Explorer Memory
'
            Exit Sub                                                                                    '   Exit this sub
        Else
'
            On Error GoTo Yahoo_One_Year_Estimates_Scrape_Error                                         '   If Error occurs then goto Yahoo_One_Year_Estimates_Scrape_Error
'
'           Browser address that we will be scraping values from
            IE.navigate TotalURL                                                                        '   Load the Internet Explorer URL
'
'           Make the Browser window, that we will be scraping values from, visible
            IE.Visible = True                                           '   Make Internet Explorer Windows Visible
'
'           Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
            Do While IE.readyState <> 4 And DelaySeconds <= 19                                          '   Loop while IE is still loading and <= 19 seconds delayed
                Application.Wait (Now + TimeValue("00:00:01"))                                          '   Delay for 1 second
                DoEvents                                                                                '   Enable Mouse Clicks
'
'               Update status bar to inform the user of what is occurring
                Application.StatusBar = "Loading website … " & TotalURL & "    Stock # " & (RowCounter - 4)
'
                DelaySeconds = DelaySeconds + 1                         '   Add 1 to our DelaySeconds Counter
'
            Loop                                                        ' Loop back
'
'           Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
            Do While IE.Busy And DelaySeconds <= 19
                Application.Wait (Now + TimeValue("00:00:01"))          '   Delay for 1 second
                DoEvents                                                '   Enable Mouse Clicks
'
'               Update status bar to inform the user of what is occurring
                Application.StatusBar = "Loading website … " & TotalURL & "    Stock # " & (RowCounter - 4)
'
                DelaySeconds = DelaySeconds + 1                         '   Add 1 to our DelaySeconds Counter
'
            Loop                                                        ' Loop back
'
'
            If DelaySeconds > 19 Then                                   ' If we have delayed for > 19 seconds to allow the page to load then ...
                IE.Quit                                                 '   Close Internet Explorer Window
'
                Set IE = New InternetExplorer                           '   Open Internet Explorer Browser
'
                If PageLoadAttempt <= 4 Then GoTo ReloadScrape_Yahoo_One_Year_Estimates '   If we have'nt tried 4 reloads of this page then reload page again
            End If                                                      ' End If
'
            If PageLoadAttempt > 4 Then                                 ' If we have tried 4 reloads of the URL page then Display a message box & Exit program
                MsgBox "We've reloaded the same web page  " & PageLoadAttempt & " times without success so we're going to pause the program" & _
                " so you can investigate.", , "Multiple errors detected"
'
                PageLoadAttempt = 0                                     '   Reset PageLoadAttempt = 0
'
                Stop                                                    '   Stop this Excel program!
            End If
'
            Set Doc = IE.document
'
        End If
'
'       Update status bar to inform the user of what is occurring
        Application.StatusBar = "Gathering Data from website … " & TotalURL & "    Stock # " & (RowCounter - 4)
'
        Range("J" & RowCounter).Value = Doc.getElementsByTagName("td")(11).innerText        '   Scrape the Yahoo 52 Week Price Range
        Range("K" & RowCounter).Value = Doc.getElementsByTagName("td")(31).innerText        '   Scrape the Yahoo One Year Price Estimate
'
        On Error GoTo 0                                                                     '   Set Excel Error handling to Default
'
        RowCounter = RowCounter + 1                                                         '   Advance to next row in Excel sheet
'
        IE.Quit                                                                             '   Close Internet Explorer Window
        Set IE = Nothing                                                                    '   Clear Internet Explorer Memory
'
        PageLoadAttempt = 0                                                                 '   Reset PageLoadAttempt = 0
'
    Next                                                                                    '   Load next stock until all are loaded
'
    Exit Sub                                                                                ' Exit this Sub
'
Yahoo_One_Year_Estimates_Scrape_Error:
'
''    IE.Quit                                                                             '   Close Internet Explorer Window
    Set IE = Nothing                                                                    '   Clear Internet Explorer Memory
'
    On Error GoTo 0                                                                     '   Set Excel Error handling to Default
    On Error GoTo -1                                                                    '   Clear Excel Error flags
'
    GoTo ReloadScrape_Yahoo_One_Year_Estimates
'
End Sub
'________________________________________________________________________________________________________________________________________________________
'
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'

Private Sub Scrape_BarChart_Stock_Page_1()
'
ReloadBarChartStockPage1:
'
'   Scrape stocks to consider looking into further from Barchart
'
    DelaySeconds = 0                                                        ' Initialize DelaySeconds to zero
    PageLoadAttempt = PageLoadAttempt + 1                                   ' Add 1 to our PageLoadAttempt counter
'
'   URL of 1St page of stocks that we will scrape from
    StockMainPageURL = "www.barchart.com/stocks/performance/percent-change/declines?timeFrame=3m&viewName=main"
'
    TotalURL = "https://" & StockMainPageURL
'
'   Update status bar to inform the user of what is occurring
    Application.StatusBar = "Loading website … " & TotalURL
'
'   Browser address that we will be scraping values from
    IE.navigate TotalURL                                                    ' Load the Internet Explorer URL
'
'   Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
    Do While IE.readyState <> 4 And DelaySeconds <= 19                      ' Loop while IE is still loading and <= 19 seconds delayed
        Application.Wait (Now + TimeValue("00:00:01"))                      '   Delay for 1 second
        DoEvents                                                            '   Enable Mouse Clicks
'
'       Update status bar to inform the user of what is occurring
        Application.StatusBar = "Loading website … " & TotalURL & "    Delay Seconds =  " & DelaySeconds & _
                                "    Page Load Attempts = " & PageLoadAttempt & "    Stock # " & (RowCounter - 4) & _
                                "    Avg Yahoo Delay = " & AvgYahooDelay & "     AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
        DelaySeconds = DelaySeconds + 1                                     '   Add 1 to our DelaySeconds Counter
'
        If DelaySeconds > MaxYahooDelay Then MaxYahooDelay = DelaySeconds   '   Save the MaxYahooDelay
'
    Loop                                                                    ' Loop back
'
'   Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
    Do While IE.Busy And DelaySeconds <= 19
        Application.Wait (Now + TimeValue("00:00:01"))                      '   Delay for 1 second
        DoEvents                                                            '   Enable Mouse Clicks
'
'       Update status bar to inform the user of what is occurring
        Application.StatusBar = "Loading website … " & TotalURL & "    Delay Seconds =  " & DelaySeconds & _
                                "    Page Load Attempts = " & PageLoadAttempt & "    Stock # " & (RowCounter - 4) & _
                                "    Avg Yahoo Delay = " & AvgYahooDelay & "     AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
        DelaySeconds = DelaySeconds + 1                                     '   Add 1 to our DelaySeconds Counter
'
        If DelaySeconds > MaxYahooDelay Then MaxYahooDelay = DelaySeconds   '   Save the MaxYahooDelay
    Loop                                                                    ' Loop back
'
'
    If DelaySeconds > 19 Then                                               ' If we have delayed for > 19 seconds to allow the page to load then ...
        IE.Quit                                                             '   Close Internet Explorer Window
'
        If PageLoadAttempt <= 4 Then GoTo ReloadBarChartStockPage1          '   If we have'nt tried 4 reloads of this page then reload page again
    End If                                                                  ' End If
'
    If PageLoadAttempt > 4 Then                                             ' If we have tried 4 reloads of the URL page then Display a message box & Exit program
        MsgBox "We've reloaded the same web page  " & PageLoadAttempt & " times without success so we're going to pause the program" & _
            " so you can investigate.", , "Multiple errors detected"
'
        PageLoadAttempt = 0                                                 '   Reset PageLoadAttempt = 0
'
        Stop                                                                '   Stop this Excel program!
    End If                                                                  ' End If
'
    Set Doc = IE.document
'
'   Make the Browser window, that we will be scraping values from, visible
    IE.Visible = True                                                       ' Make Internet Explorer Windows Visible
'
'
    For StockCount = 1 To TotalStocksToLoad                                 ' Grab One Year stock price estimate
'
'       Update status bar to inform the user of what is occurring
        Application.StatusBar = "Gathering Data from website … " & TotalURL & "    Stock # " & (RowCounter - 4)
'
'
        Range("A" & RowCounter).Value = (RowCounter - 4)                                            '   Stock number Counter
        Range("B" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter).innerText       '   Stock Symbol
        Range("C" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 1).innerText   '   Stock Name
        Range("D" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 2).innerText   '   3 Month % Change
        Range("E" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 3).innerText   '   Last Price
        Range("F" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 4).innerText   '   Change
        Range("G" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 5).innerText   '   % Change
        Range("H" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 7).innerText   '   3 Month High
        Range("I" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 8).innerText   '   3 Month Low
'
        CellCounter = CellCounter + 12                                                              '   Advance to next row on URL page
        RowCounter = RowCounter + 1                                                                 '   Advance to next row in Excel sheet
    
    Next
'
    IE.Quit                                                                                         ' Close Internet Explorer Window
    Set IE = Nothing                                                                                ' Clear Internet Explorer Memory
'
End Sub
'
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
Private Sub Scrape_BarChart_Stock_Page_2()
'
ReloadBarChartStockPage2:
'
'   Scrape stocks to consider looking into further from Barchart
'
    DelaySeconds = 0                                            ' Initialize DelaySeconds to zero
    PageLoadAttempt = PageLoadAttempt + 1                       ' Add 1 to our PageLoadAttempt counter
'
'   URL of 2nd page of stocks that we will scrape from
    StockMainPageURL = "www.barchart.com/stocks/performance/percent-change/declines?timeFrame=3m&viewName=main&page=2"
'
    TotalURL = "https://" & StockMainPageURL
'
'   Update status bar to inform the user of what is occurring
    Application.StatusBar = "Loading website … " & TotalURL
'
'   Browser address that we will be scraping values from
    IE.navigate TotalURL                                        ' Load the Internet Explorer URL
'
'   Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
    Do While IE.readyState <> 4 And DelaySeconds <= 19          ' Loop while IE is still loading and <= 19 seconds delayed
        Application.Wait (Now + TimeValue("00:00:01"))          '   Delay for 1 second
        DoEvents                                                '   Enable Mouse Clicks
'
'       Update status bar to inform the user of what is occurring
        Application.StatusBar = "Loading website … " & TotalURL & "      Delay Seconds =  " & DelaySeconds & _
                                " Page Load Attempts = " & PageLoadAttempt & "    Stock # " & (RowCounter - 4) & _
                                "    Avg Yahoo Delay = " & AvgYahooDelay & "     AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
        DelaySeconds = DelaySeconds + 1                         '   Add 1 to our DelaySeconds Counter
'
        If DelaySeconds > MaxYahooDelay Then MaxYahooDelay = DelaySeconds   '   Save the MaxYahooDelay
'
    Loop                                                        ' Loop back
'
'   Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
    Do While IE.Busy And DelaySeconds <= 19
        Application.Wait (Now + TimeValue("00:00:01"))          '   Delay for 1 second
        DoEvents                                                '   Enable Mouse Clicks
'
'       Update status bar to inform the user of what is occurring
        Application.StatusBar = "Loading website … " & TotalURL & "      Delay Seconds =  " & DelaySeconds & _
                                " Page Load Attempts = " & PageLoadAttempt & "    Stock # " & (RowCounter - 4) & _
                                "    Avg Yahoo Delay = " & AvgYahooDelay & "     AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
        DelaySeconds = DelaySeconds + 1                         '   Add 1 to our DelaySeconds Counter
'
        If DelaySeconds > MaxYahooDelay Then MaxYahooDelay = DelaySeconds   '   Save the MaxYahooDelay
    Loop                                                        ' Loop back
'
'
    If DelaySeconds > 19 Then                                   ' If we have delayed for > 19 seconds to allow the page to load then ...
        IE.Quit                                                 '   Close Internet Explorer Window
'
        If PageLoadAttempt <= 4 Then GoTo ReloadBarChartStockPage2 '   If we have'nt tried 4 reloads of this page then reload page again
    End If                                                      ' End If
'
    If PageLoadAttempt > 4 Then                                 ' If we have tried 4 reloads of the URL page then Display a message box & Exit program
        MsgBox "We've reloaded the same web page  " & PageLoadAttempt & " times without success so we're going to pause the program" & _
            " so you can investigate.", , "Multiple errors detected"
'
        PageLoadAttempt = 0                                     '   Reset PageLoadAttempt = 0
'
        Stop
    End If
'
    Set Doc = IE.document
'
'   Make the Browser window, that we will be scraping values from, visible
    IE.Visible = True
'
'
'
'
    For StockCount = 1 To TotalStocksToLoad       ' Grab One Year stock price estimate
'
'       Update status bar to inform the user of what is occurring
        Application.StatusBar = "Gathering Data from website … " & TotalURL & "    Stock # " & (RowCounter - 4)
'
'
        Range("A" & RowCounter).Value = (RowCounter - 4)                                                  ' Stock number Counter
        Range("B" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter).innerText       ' Stock Symbol ... + 4
        Range("C" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 1).innerText   ' Stock Name
        Range("D" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 2).innerText   ' 3 Month % Change
        Range("E" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 3).innerText   ' Last Price
        Range("F" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 4).innerText   ' Change
        Range("G" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 5).innerText   ' % Change
        Range("H" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 7).innerText   ' 3 Month High
        Range("I" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 8).innerText   ' 3 Month Low
'
        CellCounter = CellCounter + 12
        RowCounter = RowCounter + 1
    
    Next
'
    IE.Quit
    Set IE = Nothing
'
End Sub
'
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------
Private Sub Scrape_BarChart_Stock_Page_3()
'
ReloadBarChartStockPage3:
'
'   Scrape stocks to consider looking into further from Barchart
'
    DelaySeconds = 0                                            ' Initialize DelaySeconds to zero
    PageLoadAttempt = PageLoadAttempt + 1                       ' Add 1 to our PageLoadAttempt counter
'
'   URL of 3rd page of stocks that we will scrape from
    StockMainPageURL = "www.barchart.com/stocks/performance/percent-change/declines?timeFrame=3m&viewName=main&page=3"
'
    TotalURL = "https://" & StockMainPageURL
'
'   Update status bar to inform the user of what is occurring
    Application.StatusBar = "Loading website … " & TotalURL
'
'   Browser address that we will be scraping values from
    IE.navigate TotalURL                                        ' Load the Internet Explorer URL
'
'   Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
    Do While IE.readyState <> 4 And DelaySeconds <= 19          ' Loop while IE is still loading and <= 19 seconds delayed
        Application.Wait (Now + TimeValue("00:00:01"))          '   Delay for 1 second
        DoEvents                                                '   Enable Mouse Clicks
'
'       Update status bar to inform the user of what is occurring
        Application.StatusBar = "Loading website … " & TotalURL & "      Delay Seconds =  " & DelaySeconds & _
                                " Page Load Attempts = " & PageLoadAttempt & "    Stock # " & (RowCounter - 4) & _
                                "    Avg Yahoo Delay = " & AvgYahooDelay & "     AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
        DelaySeconds = DelaySeconds + 1                         '   Add 1 to our DelaySeconds Counter
'
        If DelaySeconds > MaxYahooDelay Then MaxYahooDelay = DelaySeconds   '   Save the MaxYahooDelay
'
    Loop                                                        ' Loop back
'
'   Allow mouse clicks and such while browser window is loading ... Loop until browser window is fuilly loaded, ie. READYSTATE_COMPLETE
    Do While IE.Busy And DelaySeconds <= 19
        Application.Wait (Now + TimeValue("00:00:01"))          '   Delay for 1 second
        DoEvents                                                '   Enable Mouse Clicks
'
'       Update status bar to inform the user of what is occurring
        Application.StatusBar = "Loading website … " & TotalURL & "      Delay Seconds =  " & DelaySeconds & _
                                " Page Load Attempts = " & PageLoadAttempt & "    Stock # " & (RowCounter - 4) & _
                                "    Avg Yahoo Delay = " & AvgYahooDelay & "     AvgYahooPageAttempts = " & AvgYahooPageAttempts
'
        DelaySeconds = DelaySeconds + 1                         '   Add 1 to our DelaySeconds Counter
'
        If DelaySeconds > MaxYahooDelay Then MaxYahooDelay = DelaySeconds   '   Save the MaxYahooDelay
    Loop                                                        ' Loop back
'
'
    If DelaySeconds > 19 Then                                   ' If we have delayed for > 19 seconds to allow the page to load then ...
        IE.Quit                                                 '   Close Internet Explorer Window
'
        If PageLoadAttempt <= 4 Then GoTo ReloadBarChartStockPage3 '   If we have'nt tried 4 reloads of this page then reload page again
    End If                                                      ' End If
'
    If PageLoadAttempt > 4 Then                                 ' If we have tried 4 reloads of the URL page then Display a message box & Exit program
        MsgBox "We've reloaded the same web page  " & PageLoadAttempt & " times without success so we're going to pause the program" & _
            " so you can investigate.", , "Multiple errors detected"
'
        PageLoadAttempt = 0                                     '   Reset PageLoadAttempt = 0
'
        Stop
    End If
'
    Set Doc = IE.document
'
'   Make the Browser window, that we will be scraping values from, visible
    IE.Visible = True
'
'
'
'
    For StockCount = 1 To TotalStocksToLoad       ' Grab One Year stock price estimate
'
'       Update status bar to inform the user of what is occurring
        Application.StatusBar = "Gathering Data from website … " & TotalURL & "    Stock # " & (RowCounter - 4)
'
'
        Range("A" & RowCounter).Value = (RowCounter - 4)                                                  ' Stock number Counter
        Range("B" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter).innerText       ' Stock Symbol ... + 4
        Range("C" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 1).innerText   ' Stock Name
        Range("D" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 2).innerText   ' 3 Month % Change
        Range("E" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 3).innerText   ' Last Price
        Range("F" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 4).innerText   ' Change
        Range("G" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 5).innerText   ' % Change
        Range("H" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 7).innerText   ' 3 Month High
        Range("I" & RowCounter).Value = Doc.getElementsByTagName("td")(CellCounter + 8).innerText   ' 3 Month Low
'
        CellCounter = CellCounter + 12
        RowCounter = RowCounter + 1
    
    Next
'
    IE.Quit
    Set IE = Nothing
'
End Sub
'

This code can be condensed quite a bit, but I want to get it working before I do that.

Thank you!
 
Upvote 0
Hi - I will take a look at this over the weekend. I have a few other tasks I need to complete before it, but thank you for posting the code. The code I rewrote for you can run independently, so maybe it would be worth putting it in its own macro-enabled workbook so thatyou can test it and understand how it works.
 
Upvote 0
Hello again Dan_W

I tried your code wrote for me, much appreciated :) , but I encountered a Run-time error '600' Application-Defined or Object-Defined error
@ Doc.body.outerHTML = strHTML
 
Upvote 0
Hi

I tried my code again, and it seems to work. I get the following results:
Book2
BCDEFGHIJK
5AAPL51.06 - 131.00110.81
6GOOG1,013.54 - 1,659.221,699.84
7TSLA43.67 - 500.14273.05
8AMZN1,626.03 - 3,495.003,636.42
Test


Did you set a reference to Microsoft HTML Object Library?
 
Upvote 0
Hey Dan_W

Yes, that is already checked. I also tried the code on a laptop as opposed to my usual desktop computer, same result.

It's got me pulling my hair out. :(
 

Attachments

  • Capture.PNG
    Capture.PNG
    26.7 KB · Views: 24
Upvote 0
And the code you're running is exactly as I wrote it? In a completely new workbook?
 
Upvote 0
Hey Dan_W

Yes. Could you possibly upload the workbook that you have affirmed as working so I could test that?
 
Upvote 0
Not sure if it will be any faster than VBA, but you can do this with PQ as well. I added a custom column to my list of stock symbols. The custom column is driven by a custom function...

Power Query: 
(sym as text) as table =>

let
    Source = Web.Page(Web.Contents("https://finance.yahoo.com/quote/" & sym)),
    Data1 = Source{1}[Data],
    RC0 = Table.RemoveColumns(Data1,{"Column1"}),
    LastN = Table.LastN(RC0, 1),
    Data0 = Source{0}[Data],
    CT = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}}),
    RC1 = Table.RemoveColumns(CT,{"Column1"}),
    KeepN = Table.Range(RC1,5,1),
    CC = Table.AddColumn(LastN, "Custom", each KeepN),
    EC = Table.ExpandTableColumn(CC, "Custom", {"Column2"}, {"Column2.1"})
in
    EC

And here were the results.

Book1
ABCDE
1SymbolSymbolColumn2Column2.1
2AAPLAAPL110.8151.06 - 134.80
3GOOGGOOG1,699.841,013.54 - 1,665.73
4TSLATSLA273.0543.67 - 502.49
5AMZNAMZN3,636.421,626.03 - 3,513.87
Sheet11
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Scrape data from password protected site without using Internet Explorer
Replies
6
Views
5K
Dan_W
D
I
  • Question
How can I make this web scrape VBA code perform quicker?
Replies
4
Views
1K
Indominus
I
I
  • Question
Tweaked Internet Explorer web scrape code will not work using Get request
2
Replies
12
Views
2K
Indominus
I
A
  • Question
Web Scraping using VBA
Replies
4
Views
9K
atr140
A
S
  • Question
Scraping a Site with VBA
Replies
1
Views
2K
spneufel
S

Forum statistics

Threads
1,214,652
Messages
6,120,747
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top