6
\$\begingroup\$

It takes around 5-8 seconds for me to retrieve a previously-closed stock price and a dividend rate from US Yahoo! Finance. If I wanted to retrieve 10+ stock prices, it would take me more than a minute with the below VBA code.

Is there any other way to speed up or improve the code?

I input multiple stock code on column B and retrieve data on column C & D:

  1. Column B = stock code (e.g. HPQ, IBM, AAPL)
  2. Column C = previous closed price
  3. Column D = dividend rate

Sub Button1_Click()

Dim ie As Object
Dim r As Integer
Dim prevClose As String
Dim div As String
Dim Doc As HTMLDocument

With Application
 .DisplayAlerts = False
 .ScreenUpdating = False
 .Calculation = xlCalculationManual 
 .EnableEvents = False 
 .StatusBar = "Retrieving data from internet explorer"
End With


Dim time1 As Double, time2 As Double
time1 = Timer

Set ie = CreateObject("InternetExplorer.Application")


For r = 2 To Range("B65535").End(xlUp).Row


With ie
  .Visible = 0

  .navigate "http://finance.yahoo.com/q?s=" & Cells(r, "B").Value

   While .Busy Or .readyState <> 4 'add do or not
   DoEvents
   Wend

End With



 Set Doc = ie.document

 prevClose = Trim(Doc.getElementById("table1").getElementsByTagName("td")(0).innerText)
 Cells(r, "C").Value = prevClose


 div = Trim(Doc.getElementById("table2").getElementsByTagName("tr")(7).getElementsByTagName("td")(0).innerText)
 Cells(r, "D").Value = div



Next r

ie.Quit: Set ie = Nothing

With Application
 .DisplayAlerts = True
 .ScreenUpdating = True
 .Calculation = xlCalculationAutomatic 
 .EnableEvents = True 

End With

time2 = Timer
MsgBox Format(time2 - time1, "0.00 \s\ec")

End Sub
\$\endgroup\$

2 Answers 2

5
\$\begingroup\$

To know whether or not it's realistic to pull 10K results with VBA, you need to first split your monolith into smaller chunks, and see where it is exactly that the time is being spent.

Keep in mind that VBA code can only run on a single thread, so all you can do, is make sure your code is optimal for a single request, and then making 10K requests is just going to have to be 10K times that.

Idea: COM-Visible BackgroundWorker

If you have access to Visual Studio (.NET), you could make a COM-visible class library that exposes a BackgroundWorker implementation that fires up COM-visible events - and then you could reference that library, and create a class to hold the state and results:

Private WithEvents worker As MyCOMVisibleLibrary.MyBackgroundWorker

Private Type TStockPriceResult
    StockCode As String
    PreviousClosedPrice As Double
    DividendRate As Double
    IsCompleted As Boolean
End Type
Private this As TStockPriceResult

Public Property Get StockCode() As String
    StockCode = this.StockCode
End Property

Public Property Let StockCode(ByVal value As String)
    this.StockCode = value
End Property

Public Property Get PreviousClosedPrice() As Double
    PreviousClosedPrice = this.PreviousClosedPrice
End Property

Public Property Get DividendRate() As Double
    DividendRate = this.DividendRate
End Property

Public Property Get IsCompleted() As Boolean
    IsCompleted = this.IsCompleted
End Property

Private Sub worker_DoWork()
    ' implement background-thread code here
End Sub

Private Sub worker_RunWorkerCompleted(ByVal HasError As Boolean, ByVal ErrorMessage As String, ByVal IsCancelled As Boolean)
    this.IsCompleted = true
End Sub

Public Sub DoWork()
    worker.RunWorkerAsync
End Sub

In the DoWork handler, you'd set the private field values, and use the RunWorkerCompleted handler to flag the instance as "completed", meaning the client code can now safely read the encapsulated values using the exposed property getters.

This means the calling code simply needs to create an instance of that class for each StockCode that needs to be pulled, put them all in a collection and then call the DoWork method on each instance - then you can loop/wait until all instances have IsCompleted = True, and write the results to Excel - they should all run asynchronously, and gather their results from a separate background thread that's running in .NET managed code. So 10K requests made that way would probably complete much faster than the same 10K requests made from 100% VBA code.


The actual review

That's all nice, but there are a number of things to say about the code you have here:

  • You've coded all the logic inside a button's Click handler. Extract procedures, move that code elsewhere: there shouldn't be much code in a Click handler.
    • You need a method to enable/disable "wait mode", where Excel is not responsive and waiting for the data.
    • You need a method to make the web request
    • You need another method to inspect the response
    • You need another method to write the results to Excel
  • Indentation is lacking - use the Tab key to indent code blocks. A single space isn't indentation. All code blocks should have an indentation level - be it the Sub, With, If, For, ... all clode blocks want to be indented.
\$\endgroup\$
2
  • \$\begingroup\$ Hi mat,these are really great tips especially when you explained that VBA only run on single-thread and how my i shouldn't put everything in just a click handler. I am just wondering if i could write the code with Visual Studio(.net) at home and run it on excel(vba) in my office. There is no Visual Studio(.net) at my work place. All we have are excel and IE. I can't get permission to install extra programs in my work place. P.S. The data i would like to scrap are on intranet. \$\endgroup\$
    – pexpex223
    Jan 8, 2015 at 14:11
  • \$\begingroup\$ You should be able to copy your homebrewn .dll to your office computer and reference it from VBA. Better test it at home first, COM interop can be... picky ;-) \$\endgroup\$ Jan 8, 2015 at 14:44
5
\$\begingroup\$

Now, I don't know much about webscraping, but I don't think there's much you can do to make this method of going about it any faster. You're restricted by how fast Internet Explorer can retrieve the website. There's also the fact that VBA is a single threaded language. So, multi-threading is out of the question.... or is it?

I would tackle this problem a little differently. I would create a swarm of VBScript scripts to retrieve the data you're after. This works by using VBA to writing many VBScript files to a drive location and using a shell to execute them. Each VBScript is responsible for writing the data it finds to the appropriate cell in Excel.

You can find a reasonable example of this (including a workbook with the code) here courtesy of Daniel Ferry.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.