Retrieving stock prices

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:

• Column B = stock code (e.g. HPQ, IBM, AAPL)

• Column C = previous closed price

• 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

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.

StackExchange Code Review Q#74648, answer score: 5