File with STOCKHISTORY function becomes unresponsive

[doktorj]

Hello, I have several Excel files using the beta Stockhistory function. One file has become unresponsive every time I open it. These files download daily price data for multiple stocks for the past 42 days. To first assign dates for the data I need, I first use:

=TRANSPOSE(STOCKHISTORY($B$32,TODAY()-42,TODAY(),0,1)), where the $B$32 references a stock.

Then I use "=STOCKHISTORY($B32:$B42,EU$3,$FV$3,0,0,1)" where EU$3 and $FV$3 references the start and end dates delivered by the earlier "Transpose(Stock ..)" function.

But it looks like the first function results in a SPILL error, probably because of the "42" setting. At bottom right I see "Calculating (Spill Resize Pass 1): 0%"

I think this is then causing a problem for the 2nd function, and the file becomes unresponsive. To try and fix the problem, I'd like to adjust the first function to 40 to prevent Spill. But I can't because the data download automatically starts.

Does anyone have a solution to my problem? It's a large file, so I'd like to avoid rebuilding the file from scratch. Is there a way to stop data download from starting automatically when the file is open? Would appreciate any suggestions to my problem. I don't have problems with the other files using these same functions. Thanks.

[#2]

@doktorj 

Interesting case. Could you please share sample file (not your work file, but simple one which illustrate an issue) not to build the models for the replication.

[#3]

@Sergei BaklanThanks for reply Sergei. I'm unable to replicate the problem in a sample file, as the other files with the same functions seem to be working. Again, I'm assuming the problem stems from a SPILL of the array into adjacent cells, so what I've done with the files that are working is to enter additional empty columns, to "catch" the spill. As there's nothing sensitive in the file (just stock codes), I'm attaching it here for you to look at ...