Spreadsheet Speed Up Ideas?

[Data123]

I have a spreadsheet that has 850 rows and about 7 columns. I get lots of #BUSY! statements when using =STOCKHISTORY, and it's being updated. It normally takes about 45 min to 1 hour to show the values. I have turned off auto calculations and that does help a great deal, but I still need to update it when I make changes to the spreadsheet or when I would like to see price changes once per day.

My question is how else can I speed up this process without dividing the spreadsheet up by making multiple sheets or by changing the spreadsheet into something completely different?

Can I create a default setting for the spreadsheet that it will limit the rows shown to 100, but I would like to also specify a specific column values to be shown in descending or ascending order? Then the next time I update the spreadsheet it will auto remember this setup for the specific column order and expand it to the rest of the spreadsheet.

If so, the 850 rows never have to be shown until I sort the other columns, then they could show up as part of the 100 rows.

 

[Logit]

This part goes at start of your macro :

With Application
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

This part goes at the end of your macro :

    With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With

 

[Data123]

Hi Logit, and thanks for the response. May I ask what will the VBA code do for the spreadsheet and can you tell me how to add it please?

This part goes at start of your macro :

With Application
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

This part goes at the end of your macro :

    With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With

 

[Logit]

The code statements I provided work in VBA code. I see you are probably using Excel Formulas to achieve your goals ? If so, I am unaware of any
formulas that would make things run faster for you. There may be some, its just that I am unaware of any if they exist.

If you decide to achieve your goals using a VBA macro, the code I provided will assist in speeding things up.

 

[CephasOz]

Though there is one thing that you might want to consider. Volatile functions will cause many more calculations than non-volatile functions, so they should be avoided, if possible. These type of functions include: RAND(), RANDARRAY(), RANDBETWEEN(), NOW(), TODAY(), OFFSET(), INDIRECT(), CELL(), and INFO(). Conditional formatting will also slow things down. Google "excel volatile functions" and you'll see a lot of info about this problem.

 

[Alex Blakenburg]

Since your issue seems to be specific to the StockHistory function see if this gives you any ideas.
My Table name is tblStocks. You will need to convert the range to a table and use that name for the below to work.

What I envisage is that:
• tblStocks has your full list of 850 stocks in it
• Number of Stocks to show is where you tell it you just want 100
• I have today as the date but you can put any Date you like

WARNING: When I put the Today() directly into the formula, the formula did not always update.
I tend to prefer putting Today in a cell in general but in this case when I referred to a cell rather than Today() directly it did reliably update.
I think it also showed #BUSY less but I was only using a very small number of stocks and will be interested to know if you notice any difference.

20240227 StockFunction Update Trigger Data123.xlsx
	A	B
1	Date:	27/02/2024
2
3	Number to show of stocks to show	4
4
5
6	Stocks
7	MSFT
8	GOOG
9	AMZN
10	RCL
11	AAL
12
13
14
15		Today in Cell above
16		Automatically updates
17
18
19	Output	Close Date
20	MSFT	$ 407.54
21	GOOG	$ 138.75
22	AMZN	$ 174.73
23	RCL	$ 119.57
24
25
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TODAY()
A20:A23	A20	=TAKE(tblStocks[Stocks],$B$3)
B20:B23	B20	=STOCKHISTORY(A20#,$B$1-7,$B$1,2,0,1,3)
Dynamic array formulas.