Speed of multiple URL web scraping

[johnnyL]

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.

'
'-----------------------------------------------------
'   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
'________________________________________________________________________________________________________________________________________________________

 

[johnnyL]

This is where I am at now. \/ \/ \/

 

[Dan_W]

Is that saying 1hr and 13mins?!

 

[johnnyL]

Dan_W Yes, unfortunately.

The half full/half empty side of looking at things is that prior to all of the help I have received here, as well as my googling, it used to take twice as long to complete.

 

[johnnyL]

⚠ Breakneck speed Detected ahead!

I did manage to shave about 10 minutes off of Phase 3 though.

 

[johnnyL]

Less than 1 hour.

 

[Dan_W]

It still seems abnormally long. Can you please remind me what it is you're doing during Phase 3? Isn't it that you're checking the data for several hundred stocks, each of which has a separate URL that you have to load through IE? Can you break down the Phase 3 time into smaller constituent parts to try and work out if there are any bottlenecks you can optimise?

 

[johnnyL]

Hey Dan_W!

I have tried to mess with my code to try & improve the speed, but when I mess with it, the results seem to worsen.

I have included some code that involves the "Phase 3" in hopes that you, or anyone else for that matter, can offer some assistance.

Thanks to all!

Private Sub Phase_3()
'
'   Phase_3     Goal: Load each one of the 250 stocks, that were scraped from the three (Phase 1) BarChart URLs, into 250
'                       different BarChart URLs to scrape analyst ratings and stuff
'
    PhaseStartTime = Now                                        ' Initiate Timer to time how long this phase takes to complete
'
    RowCounter = 5                                              ' Set the 5th row of Excel as the row to save more scraped values into
    PageLoadAttempt = 0                                         ' Clear our PageLoadAttempt counter
    TotalStocksToLoad = 250                                     ' Load this amount of stock symbols from the stock symbols that were previously scraped
'
'   Update status bar to inform the user of what is occurring
    Application.StatusBar = "Loading BarChart Analyst Ratings … "
'
    For StockCount = 1 To TotalStocksToLoad                     ' we will load this amount of stock symbols from the stocks that were loaded
        Call GetBarChartAnalystRatings                          '   Load stock symbol into URL and scrape some Data
    Next                                                        ' Load next stock symbol until none left to load
'
    PhaseEndTime = Now                                          ' Stop the timer that is timing this phase completion time
'
    Phase_3_Timer = Format(PhaseEndTime - PhaseStartTime, "hh:mm:ss")   ' Set Phase_3_Timer = to the time it took to complete Phase 3
'
    Range("D2").Value = Phase_3_Timer                           ' Save the Phase Timer value into $D2
'
'
    Range("E2").Value = Format(PhaseEndTime - ProgramStartTime, "hh:mm:ss") ' Save the Total time to run the program into $E2
'
'   Display a Completion box that displays the completion times and total time for all phases
    MsgBox "Phase RunTimes = " & vbCrLf & vbTab & "Time to complete Phase 1 - Scrape stocks to be used = " & Phase_1_Timer & _
                                vbCrLf & vbTab & "Time to complete Phase 2 - Scrape Yahoo 1 Year Estimates = " & Phase_2_Timer & _
                                vbCrLf & vbTab & "Time to complete Phase 3 - GetBarChartAnalystRatings = " & Phase_3_Timer & _
                                vbCrLf & vbCrLf & vbTab & "Total Program RunTime hh:mm:ss = " & Format(PhaseEndTime - ProgramStartTime, "hh:mm:ss")
'
'-----------------------------
'
'   Update status bar to inform the user of what is occurring
    Application.StatusBar = "Done !!!"
'
'-----------------------------
'
End Sub
'
'
'
'
Private Sub GetBarChartAnalystRatings()
'
GetBarChartAnalystRatingsStart:
'
    Application.Calculation = xlCalculationManual                   ' Test1 to see if we can speed things up
    Application.ScreenUpdating = False                              ' Test2 to see if we can speed things up

'
    currentstocksymbol = Trim(Range("B" & RowCounter).Value)        ' Set CurrentStockSymbol = $B?
'
    If currentstocksymbol = vbNullString Then                       ' If no stock symbol found @ $B?  then ...
        PageLoadAttempt = 0                                         '   Clear our PageLoadAttempt counter
        StockCount = TotalStocksToLoad                              '   Indicate that we have loaded all of the stock symbols
'
        IE.Quit                                                     '   Close Internet Explorer Window
        Set IE = Nothing                                            '   Clear Internet Explorer memory
'
        Application.Calculation = xlCalculationAutomatic            '   End Test1 of SubSpeed up
        Application.ScreenUpdating = True                           '   End Test2 of SubSpeed up
'
        Exit Sub                                                    '   Exit this Sub
    End If                                                          ' End If
'
    PageLoadAttempt = PageLoadAttempt + 1                           ' Add 1 to our PageLoadAttempt counter
'
'   Set up the URL to load
    StockMainPortionURL = "https://www.barchart.com/stocks/quotes/"
    TotalURL = StockMainPortionURL & currentstocksymbol & "/analyst-ratings"
'
    DelaySeconds = 0                                                ' Clear DelaySeconds counter to zero
'
    On Error GoTo ReloadGetBarChartAnalystRatingsSubRoutine         ' If Error occurs then goto ReloadGetBarChartAnalystRatingsSubRoutine
'
'   Browser address that we will be scraping values from
    IE.navigate TotalURL
'
'   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 <= 29              '   Loop While Internet Explorer Not Fully Loaded and <= 29 seconds delayed
        Application.Wait (Now + TimeValue("00:00:01"))              '   Delay 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 <= 29                         ' Loop While Internet Explorer Not Fully Loaded and <= 29 seconds delayed
        Application.Wait (Now + TimeValue("00:00:01"))              '   Delay 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 > 29 Then                                       ' If we have delayed for 30 seconds to allow the page to load then ...
        IE.Quit                                                     '   Close Internet Explorer Window
'
        If PageLoadAttempt <= 4 Then Call GetBarChartAnalystRatings '   If we have'nt tried 5 reloads of page then reload this subroutine
    End If                                                          ' End If
'
    If PageLoadAttempt > 4 Then                                     ' If we have tried 5 reloads of the URL page then Display a message box &...
        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"
'
        Stop                                                        '   Halt this Excel program!
'
        PageLoadAttempt = 0                                         '   Clear our PageLoadAttempt counter
    End If                                                          ' End If
'
    On Error GoTo 0                                                 ' Set Excel Error handling back to Default
'
    Set Doc = IE.document
'
'   Make the Browser window, that we will be scraping values from, visible
''    IE.Visible = True                                             ' Default ... Make Internet Explorer Windows Visible
    IE.Visible = False                                              ' Make Internet Explorer Windows InVisible
'
'   Update status bar to inform the user of what is occurring
    Application.StatusBar = "Gathering Data from website … " & TotalURL & "    Stock # " & (RowCounter - 4)
'
    On Error GoTo NoAnalystRatings
'
    Range("O" & RowCounter).Value = num(Doc.getElementsByClassName("right-border-separator")(1).innerText)  ' Avg Analyst 1 yr price
    Range("P" & RowCounter).Value = Doc.getElementsByClassName("block__colored-header")(3).innerText        ' Analyst stock strength
    Range("Q" & RowCounter).Value = Doc.getElementsByClassName("block__average_value")(3).innerText         ' Analyst rating 1 - 5
    Range("R" & RowCounter).Value = Doc.getElementsByClassName("bold")(3).innerText                         ' # of analysts
'
    RowCounter = RowCounter + 1                                     ' Advance to next row in Excel sheet
    PageLoadAttempt = 0                                             ' Clear our PageLoadAttempt counter
'
    IE.Quit                                                         ' Close Internet Explorer Window
    Set IE = Nothing                                                ' Clear Internet Explorer memory
'
    Application.Calculation = xlCalculationAutomatic                ' End Test1 of SubSpeed up
    Application.ScreenUpdating = True                               ' End Test2 of SubSpeed up
'
    Exit Sub                                                        ' Exit this Sub
'
NoAnalystRatings:
'
    Range("P" & RowCounter).Value = "No Estimates"                  ' Indicate that no analysts have made any estimates
'
    RowCounter = RowCounter + 1                                     ' Advance to next row in Excel sheet
    PageLoadAttempt = 0                                             ' Clear our PageLoadAttempt counter
'
    IE.Quit                                                         ' Close Internet Explorer Window
    Set IE = Nothing                                                ' Clear Internet Explorer memory
'
    Application.Calculation = xlCalculationAutomatic                ' End Test1 of SubSpeed up
    Application.ScreenUpdating = True                               ' End Test2 of SubSpeed up
'
    Exit Sub                                                        ' Exit this Sub
'
ReloadGetBarChartAnalystRatingsSubRoutine:
'
    On Error GoTo 0                                                 ' Set Excel Error handling back to Default
    On Error GoTo -1                                                ' Clear Excel Error flags
''    Call GetBarChartAnalystRatings                                ' Reload this Sub
    GoTo GetBarChartAnalystRatingsStart                             ' Reload this Sub
'
End Sub

 

[Dan_W]

Where is the bottleneck? What's slowing it down? I just quickly skimmed the code, and this jumped out at me:

Do While IE.Busy And DelaySeconds <= 29                         ' Loop While Internet Explorer Not Fully Loaded and <= 29 seconds delayed
        Application.Wait (Now + TimeValue("00:00:01"))              '   Delay 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

Are DelaySeconds and PageLoadAttempt (which is elsewhere in the code) really necessary? I'm guessing that PageLoadAttempt probably isnt' because if it were coming up alot you'd have to be sitting there monitoring the computer in order to get rid of the msgbox, no? You could store the fact that youcouldn't load a certain page in a log somewhere, but I don't know that it's strictly necessary to present the fact to the user, thereby pausing a long automated process. Does it usually time out? If not, I think just get rid of the DelaySeconds too.

Application.StatusBar = "Loading website … " & TotalURL & " Stock # " & (RowCounter - 4)

This line should not be in the loop. The variables referenced do not change anywhere within the loop, so you should just put it before the Do While statement.

Application.Wait (Now + TimeValue("00:00:01"))

I suspect that this is slowing things down unnecessarily - I would delete it.

In conclusion, you may want to consider replacing that code block with:

Application.StatusBar = "Loading website … " & TotalURL & "    Stock # " & (RowCounter - 4)
Do While IE.Busy Or IE.ReadyState <> 4
    DoEvents
Loop

Let me know what your analysis reveals about what seems to be slowing the whole thing down.

 

[lrobbo314]

Just another thing to consider. You are writing values to the sheet over and over. If you store the results in memory, e.g., array, dictionary, etc., and write the results in one go at the end, you should see time improvements.

 

[johnnyL]

Hey Dan_W!

Application.StatusBar = "Loading website … " & TotalURL & " Stock # " & (RowCounter - 4)
This line should not be in the loop. The variables referenced do not change anywhere within the loop, so you should just put it before the Do While statement.

Agreed, that should be relocated.

In conclusion, you may want to consider replacing that code block with:

[VBA Code]
Application.StatusBar = "Loading website … " & TotalURL & " Stock # " & (RowCounter - 4)
Do While IE.Busy Or IE.ReadyState <> 4
DoEvents
Loop
[/VBA Code]

That seems like a recipe for a CPU cycle waste, aka "Battery Burner".