Speed of multiple URL web scraping

johnnyL

Board Regular
Joined
Nov 7, 2011
Messages
94
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
'________________________________________________________________________________________________________________________________________________________
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
258
Office Version
  1. 365
Platform
  1. Windows
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
 

johnnyL

Board Regular
Joined
Nov 7, 2011
Messages
94
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.

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.

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.

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.

I have applied an alternate solution to that previous approach.


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

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!
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
258
Office Version
  1. 365
Platform
  1. Windows
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.
 

johnnyL

Board Regular
Joined
Nov 7, 2011
Messages
94

ADVERTISEMENT

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
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
258
Office Version
  1. 365
Platform
  1. Windows
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?
 

johnnyL

Board Regular
Joined
Nov 7, 2011
Messages
94

ADVERTISEMENT

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: 9

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
258
Office Version
  1. 365
Platform
  1. Windows
And the code you're running is exactly as I wrote it? In a completely new workbook?
 

johnnyL

Board Regular
Joined
Nov 7, 2011
Messages
94
Hey Dan_W

Yes. Could you possibly upload the workbook that you have affirmed as working so I could test that?
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,951
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,774
Messages
5,574,157
Members
412,574
Latest member
shadowfighter666
Top