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 priceColumn D = dividend rateSub Button1_Click()Dim ie As ObjectDim r As IntegerDim prevClose As StringDim div As StringDim Doc As HTMLDocumentWith Application .DisplayAlerts = False .ScreenUpdating = False .Calculation = xlCalculationManual  .EnableEvents = False  .StatusBar = Retrieving data from internet explorerEnd WithDim time1 As Double, time2 As Doubletime1 = TimerSet ie = CreateObject(InternetExplorer.Application)For r = 2 To Range(B65535).End(xlUp).RowWith ie  .Visible = 0  .navigate http://finance.yahoo.com/q?s= & Cells(r, B).Value   While .Busy Or .readyState <> 4 'add do or not   DoEvents   WendEnd 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 = divNext rie.Quit: Set ie = NothingWith Application .DisplayAlerts = True .ScreenUpdating = True .Calculation = xlCalculationAutomatic  .EnableEvents = True End Withtime2 = TimerMsgBox Format(time2 - time1, 0.00 \s\ec)End Sub
Retrieving stock prices
vba;excel;finance;web scraping;internet explorer
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 BackgroundWorkerIf 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.MyBackgroundWorkerPrivate Type TStockPriceResult    StockCode As String    PreviousClosedPrice As Double    DividendRate As Double    IsCompleted As BooleanEnd TypePrivate this As TStockPriceResultPublic Property Get StockCode() As String    StockCode = this.StockCodeEnd PropertyPublic Property Let StockCode(ByVal value As String)    this.StockCode = valueEnd PropertyPublic Property Get PreviousClosedPrice() As Double    PreviousClosedPrice = this.PreviousClosedPriceEnd PropertyPublic Property Get DividendRate() As Double    DividendRate = this.DividendRateEnd PropertyPublic Property Get IsCompleted() As Boolean    IsCompleted = this.IsCompletedEnd PropertyPrivate Sub worker_DoWork()    ' implement background-thread code hereEnd SubPrivate Sub worker_RunWorkerCompleted(ByVal HasError As Boolean, ByVal ErrorMessage As String, ByVal IsCancelled As Boolean)    this.IsCompleted = trueEnd SubPublic Sub DoWork()    worker.RunWorkerAsyncEnd SubIn 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 reviewThat'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 requestYou need another method to inspect the responseYou need another method to write the results to ExcelIndentation 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.