Optimization Question. Query wont work for more than 5 tickers. It averages between 5-6 minutes each time i run it with 5 tickers

Question

i have created this excel sheet to help me out with stock analysis. the main macro within the sheet scrubs yahoo finance and market watch for specific information, throws it into different sheets, copies and pastes it into a summary sheet and then moves on to the next ticker. The macro only works up until 5 tickers. I dont know if it is because my code is not optimized as much as it should be or if there is something else that i can do to allow me to run more than 5 tickers at a time. Any advice would help :)

thank you and i appreciate your time

here is my code for the macro

Sub fundamentals()

'This checks the tickers that we enter at the top of the sheet

Call TurnOffStuff

For i = 2 To Sheets(1).Cells(1, 1).End(xlToRight).column

'This will assign one of the tickers to the variable to be inserted into the url
ticker = Sheets(1).Cells(1, i)

qurl = "https://finance.yahoo.com/quote/" & ticker & "/?p=" & ticker & ""


'This gets the data from yahoofinance's summary page and puts it in sheet 5
Sheets(5).Select
Sheets(5).Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets(5).range("A2"))
    .BackgroundQuery = True
    .Refresh BackgroundQuery:=False

End With

'This takes the data that we got off of the url above and copy & paste it to
'main sheet we want our data to be in

Sheets(5).range("B2:B18").Copy
Sheets(1).Select
Cells(2, i).Select
ActiveSheet.Paste
CutCopyMode = False


qurl = "https://finance.yahoo.com/quote/" & ticker & "/key-statistics?p=" & ticker & ""


'This gets the data from yahoofinance's key statistics page and puts it in sheet 2
Sheets(2).Select
Sheets(2).Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets(2).range("A2"))
    .BackgroundQuery = True
    .Refresh BackgroundQuery:=False

End With

'This takes the data that we got off of the url above and copy & paste it to
'main sheet we want our data to be in

Sheets(2).range("B20:B78").Copy
Sheets(1).Select
Cells(22, i).Select
ActiveSheet.Paste
CutCopyMode = False


qurl = "https://finance.yahoo.com/quote/" & ticker & "/analysis?p=" & ticker & ""

'This gets the data from yahoofinance's analysis page and puts it in sheet 3
Sheets(3).Select
Sheets(3).Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets(3).range("A2"))
    .BackgroundQuery = True
    .Refresh BackgroundQuery:=False

End With

'This takes the data that we got off of the url above and copy & paste it to
'main sheet we want our data to be in

Sheets(3).range("B20:B60").Copy
Sheets(1).Select
Cells(94, i).Select
ActiveSheet.Paste
CutCopyMode = False

Sheets(3).range("C20:C60").Copy
Sheets(1).Select
Cells(136, i).Select
ActiveSheet.Paste
CutCopyMode = False

Sheets(3).range("D20:D60").Copy
Sheets(1).Select
Cells(178, i).Select
ActiveSheet.Paste
CutCopyMode = False

Sheets(3).range("E20:E60").Copy
Sheets(1).Select
Cells(220, i).Select
ActiveSheet.Paste
CutCopyMode = False



qurl = "https://www.marketwatch.com/investing/stock/" & ticker & "/analystestimates?mod=mw_quote_tab"

'This gets the data from market watches anaylst estimate page and puts it in sheet 4

Sheets(4).Select
Sheets(4).Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets(4).range("A2"))
    .BackgroundQuery = True
    .Refresh BackgroundQuery:=False

End With

'This takes the data that we got off of the url above and copy & paste it to
'main sheet we want our data to be in

Sheets(4).range("B17:B79").Copy
Sheets(1).Select
Cells(264, i).Select
ActiveSheet.Paste
CutCopyMode = False

Sheets(4).range("C35:C63").Copy
Sheets(1).Select
Cells(327, i).Select
ActiveSheet.Paste
CutCopyMode = False


Sheets(4).range("D35:D63").Copy
Sheets(1).Select
Cells(356, i).Select
ActiveSheet.Paste
CutCopyMode = False


Sheets(4).range("E35:E43").Copy
Sheets(1).Select
Cells(385, i).Select
ActiveSheet.Paste
CutCopyMode = False



Next i

Call TurnOnStuff

Sheets("All Ticker Analysis").Activate
ActiveSheet.Cells(1, 1).Select


End Sub



Sub TurnOffStuff()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False


End Sub

Sub TurnOnStuff()

Application.Calculation = xlCalcualtionAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Answer 1

I agree with @Pᴇʜ in avoiding use of Select in Excel. R could also be a good alternative using 'quantmod'. However, lets try and answer your question. Since I cannot see the sheet I cannot know if there is anything which could be inhibiting you from not being able to pull all tickers. One thing to try is to enter the macro and repeatedly press F8 to step through it. Watch the steps and check to see how many iterations of (i) your macro is performing. It may be stopping early. If you have nothing to the right of the columns in Sheets(1). Try using this following code:

For i = 2 to Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

This should help ensure you are getting the right most column in the sheet, incase there are any spaces. And if there is no data it is not going off towards Excel's column limit.

Also, the ranges of B are not dynamic. What if the data is longer or shorter than expected?

Try using variables to find the range size, and check the range size by using the same .End(xlToLeft/ToRight/Up/Down) functionality shown above.

I think your sheet should be fully capable of doing this, and I don't think it is because the sheet is not "optimized" haha. Excel is normally far from optimized. I think your problem lies in the macro ending early because it isn't going through the full loop. Again, step through it using F8 and see if you can't find where the logical misstep is. Also, to speed it up you could turn screen updating off. Do this using the following line of code:

Application.ScreenUpdating = False 'Use this at the very beginning of the macro 

Last thing, some of your sheets may be fried. I don't know the technical term for this, but it can occur in poorly constructed macros, where the sheet doesn't appear to change, but actually can encompass all rows or all columns by mistake and really slow down a program. Check the slider bars on each sheet and see what row and column you slide to when you move the bar all the way to the bottom. If it brings to the millions of rows or columns like ZXX, then you know the sheet is bad and sucking memory in Excel. The only solution is to create a new sheet from scratch. This likely happened to your sheets based on the For loop I corrected earlier. If you have ever run the sheet without a ticker or with just 1 ticker, you likely blew out your columns and will need to reconstruct this sheet. This should also help improve the time issues you are facing. And when I say reconstruct the sheet that does not mean you need to redo the entire file! Just create a new sheet, populate it the way you like, and then delete your old one and update the code to match the new sheet number.