Very Slow Calculation Time for just One Cell in 2 Year Project?

Question

Level 1

51 points

Very Slow Calculation Time for just One Cell in 2 Year Project?

I'm hoping someone can figure out what is going on otherwise 2 yrs of hard work is about to go down the drain. I’ve spent the last wk deleting and recreating tables, charts, one at a time and referencing tables to see their impact on the excessively slow calculation time of one cell, all to no avail.

I have only just noticed a sudden increase in calc time to 15+ seconds in (Trade Position 1, row 4), aka Leg 1, in the “Option Premium” green cell Y4. Calc times for Y4 in earlier version = 3 - 5 secs.

The speed issue only happens for that first row 4, Leg 1 (one of ten Legs) — and only when the asset price in E4 is changed and only for this sole one (of eight inputs) in row 4, Leg 1, no other blue celled inputs for either row 2, Leg 1 or and other the other 9 Legs are slow -- all 1-2 secs.

In tab, "10x Option P&L Strategy v138" there is a top left blue bordered table called “Initial Strategy — Tied to 10 x BSM Engines.”

Inputs go in the blue cells cols E - M. The only input for all 10 Legs that is producing a slow output in the new “Option Pr” calculation in column Y — is for Y4, Leg 1. Col Y outputs are calculated in a tab called 10 x BSM Engines that has 20 tables to calculate option premiums for any given change in the input factor like asset price or Rate changes.

Changes to the Option premium are only slow for this cell: E4. All other changes in the asset prices for all the other 9 Legs made in cells E6, E8, to E22 ( Leg 10) take 1 second to get a result in Y6, Y8, et c to Y22 (Leg 10).

Changing the underlying asset price in column E for rows 6, 8, 10 to row 22 (Legs 2, Leg 3, Leg 4 to Leg 10) or changes to the other columns in blue like date changes, implied volatility or Interest rates moves gives a 1 second update in the green “Option Pr” cells Y4 to Y22.

I’m on v138 and around the v100 there were a lot of times where I was seeing an autosave warning that the document could not be saved. Maybe I hit save whilst the calculator was doing some number crunching or I’d done some major editing, I am not sure but there was a point where between v100 and v101 the Leg 1, Y4 calculation time went from about 3 to 5 seconds to sometimes 15 - 20 seconds.

Changing any factor input in the blue cells for any of the 10 positions will result in a result in the Y column (except Y4) in about a second, maybe two…except when cell E4’s asset price is changed and Y4 starts its slow process of updating the “Option Pr” in cell Y4… Only that cell E4 being altered will cause a lag of sometimes up to 15+ seconds to produce an answer in Y4.

There is nothing special about the asset price E4 because I have deleted all dependencies on the E4 asset price cell. It has been used to provide the reference for building the x-axis asset price range for 12 charts. I disconnected it from that task when the speed issue happened (but it didn’t help or make any difference to Y4 times) because prior to disconnecting it, E4 would be altered and then all the 12 tables (in column A) that feed the charts would update that new asset price and build x-axis price series off it by adding 50 points above and below the mid point value in column A. This would then recalculate 90 columns of date 200 rows deep for 12 charts. Despite the vast number crunching it made no difference to the speed of the output for the option premium in the Y column.

E4 is now no different to E6, E8, E10, to E22.

What is wrong with cell E4’s Asset Price such that when that E4 asset price cell is changed (eg from Dow Jones 40,000 to 45,000) and sent to BSM engine #1 — which is one of the 20 BSM engine tables to calculate option premiums, in the other tab called “20 x BSM engines” — that the result in cell Y4 takes 12, 15, 18 seconds?

This is the only blue cell in the blue bordered Initial Table that causes excessive delays in Y4 output: E4.

All other inputs in Leg 1 like Interest Rate changes or date range changes result in a quick 1 second Y4 update of the new Option Premium in Leg 1 (which reflect the changes made in the blue input cells of this table).

Tests I have carried out and images of the code in the reply to this.

[Edited by Moderator]

MacBook Pro 16″, macOS 14.7

Posted on Jul 28, 2025 5:41 AM

Reply

Answer 1

Top-ranking reply

Bardonicloud Author

Level 1

51 points

Aug 9, 2025 5:58 AM in response to Bardonicloud

Test 1(a):

Is BSM engine #1 at fault and “buggy” and slow?

I deleted BSM engine #1, pasted BSM engine #2 in its place and updated the references in that new table. BSM Engine 1 is now fed from the blue bordered “Initial“ table in my other tab, from Leg 1, row 4 (and not fed from the Leg 2 row 6). But still, when E4 is changed, Y4 is very slow to produce a result.

Test 2(b):

Will, the random choice of using BSM engine #3 produce a good 1 second result in Y4 in the “Initial” blue bordered table, if I use BSM engine #3 to produce a result in Y4, Leg 1?

Please see image Leg 1.

Result changing E4 from 43000 to 40000 = 13 seconds.

Note, there is nothing wrong with BSM engine #3 — because when it’s used in Leg 3, row 8 for Y8 output — takes 1 second to update Y8’s result.

Test 2(c):

Will using BSM engine #1 in row 8, Leg 3 produce a good 1 second result in Y8 in the “Initial” blue bordered table, if I use BSM engine #1 to produce a result in Y8, Leg 3?

Please see Leg 3 image.

Leg 3’s Y8 cell: Altered code to now use BSM engine #1 for row 8, Leg 3.

Result changing E8 from 33333 to 44444 = Y8 update in a quick 1.5 seconds.

So BSM engine #1 works fine when used in row 8, Leg 3’s calculation for option premium in Y8.

I then altered the code in Leg 1’s row 2, Y4 cell so that it’s result comes from BSM engine #3 and updated the asset price cell in the engine #3 to accept a change made in Leg 1, row 2’s cell E4.

When I altered the asset price in E8 to see how fast the result would update in Y4 it took 11 seconds using BSM engine #3 outputted on row 4, Leg 1. Why?

Conclusion:

BSM engine #1 is fast when used in row 8, Leg 3.

BSM engine #3 is fast when used in it’s original row 8, Leg 3 position but not when used in row 2, Leg 1 for fast option premium calculations in Y4.

If it is not the BSM engine #1 or #3 that is laggy and slow, the issue appears to be coming from “doomed” Row 4….

Is row 4, Leg 1 corrupt? Is the issue within or on Row 4, Leg 1?

Test 2(a):

Is the cell E4 corrupt?

I deleted it and rebuilt it but it made no difference.

Test 2(b):

Is the Initial blue bordered table and blue input cell E4 or others in Leg 1 on row 4 buggy?

I deleted Leg 1’s entire row 4, in table called “Initial — Option 1 — P / L @Expiry versus @Now”

I pasted in its place, Leg 2, the entire row 6 and updated the references so that the new Leg 1 is referencing BSM engine #1 (and not engine #2 as it would have been if I hadn’t updated the refs).

Result: Output Y4 is still taking 11+ seconds to update its Y4 result. No difference.. still slow poor speed.

I also note that moving all the separate individual 20 x BSM engines to a new tab actually slightly slowed performance output result times in the blue bordered Initial Table and green celled column Y output times….

When I deleted all the 6 x blue/green chart controllers below the charts in the main tab “10x Option P&L Strategy v138,” the row 2, Y4 leg 1 position result was again back to being about 1-2 seconds like all the other Legs… The chart controllers don’t do anything special with E4 anymore that they do in their reliance on E6, E8 etc…

I also tried to lower the burden on this main tab by moving the 12 very large tables that feed the charts to a new tab but it made no difference to Y4’s speed and Y6, Y8 etc aren't slow.

As mentioned E4 is no more “special” than E6, E8, E10 etc”:

Cell E4 and the asset price is not being used by lots of other tables or charts anymore in a more dependant way than any of the other E6. E8 etc asset price cells. There is nothing special about E4 compare to and other E column asset price field.

Images of calculator overview, on chart controller in the main tab, one BSM Engine in a separate tab below.

Completely and utterly baffled.

Thanks for any input to try and salvage this very long project.

[Edited by Moderator]

Reply

Answer 2

Bardonicloud Author

Level 1

51 points

Jul 29, 2025 5:16 AM in response to Badunit

Thanks for getting this far!

Yes, probably way too much info, but I couldn't think of anyway to describe why one cell being changed reduced the speed of a calculation for that row, when there is nothing unique about that cell compared to any of the other cells in that E column that produce fast results in column Y.

In the end I deleted the row again (once I realised the issues weren't the BSM engine calculators doing complex math). I copied another row/leg and pasted in to the first Legs row and changed the references and it works super fast like the other 9 legs/positions now.

I still don't understand why that happened though, took a week to sort out...

Also not helped by endless Autosave warnings that it didn't save the latest changes and sometimes even launching the duplicate would crash the app. Not been an easy week!

Reply

Answer 3

Badunit

Level 7

30,336 points

Jul 28, 2025 3:36 PM in response to Bardonicloud

Your spreadsheet has become so complex it is difficut for anyone here to understand all that is going on, much less debug it. I'm sure all you wrote describing the problem makes sense to you but it is a lot to take in. I get the general gist that that one cell should act the same as the other cells in the column but, for whatever reason, is taking 10 times longer to calculate. The rest just makes my eyes glaze over and, to be honest, I did not read all of it. Is that one cell the input for something you didn't mean it to be or forgot about? Maybe it is in a huge chart that has to be redrawn while the others are smaller or not part of a chart at all? Large charts can slow things down. Does one of its inputs affect the speed of calculation; if you change the input (such as changing the value in D to something else) does it make it faster? What happens to the speed if you put a dummy function (something simple like multiplying two numbers) or just type number in the cell instead of a formula?

I'm not asking for answers to any of those questions, just asking them to help you debug the problem. It seems odd that only one cell would be slow. It does not sound like a bug or corruption. Still could be but there are other possibilities.

Reply