I am using pycel to create a graph of an excel calculation. Essentially I have a cell that performs some calculation:
E.g., =AVERAGE(I2:X2)
Which in turn is a function of other cells in other worksheets
E.g., I2 = sheet2!A1/sheet3!B6
And these cells can be functions of other cells etc. pycel builds a graph of this process using networkx.
My goal is to run some simulations. I create sample values for some of the cells, pass them through my graph and check what the target cell value is (=AVERAGE(I2:X2) in this case).
My actual graph has around 6000 nodes and 8000 edges. When I pass in ~10 samples, the computations take around 5 minutes. But when I try over 500 samples, the runtime becomes horrible (over 4-5 hours atleast, I have never let it finish).
I have some dotproduct calculations in my actual excel and I believe these might be causing some redundant calculations because I am seeing output such as:
Range Correlation Matrix!B2:I2 evaluated to '((36.305348, 19.746452, 89.413364, 87.434721, 19.308097, 90.679385, 3.561042, 15.810838),)'
Range Correlation Matrix!B2:I2 evaluated to '((36.305348, 19.746452, 89.413364, 87.434721, 19.308097, 90.679385, 3.561042, 15.810838),)'
Range Correlation Matrix!B2:I2 evaluated to '((36.305348, 19.746452, 89.413364, 87.434721, 19.308097, 90.679385, 3.561042, 15.810838),)'
Range Correlation Matrix!B2:I2 evaluated to '((36.305348, 19.746452, 89.413364, 87.434721, 19.308097, 90.679385, 3.561042, 15.810838),)'
Several times.
So is there a way to address the runtime issues? Since each pass through the graph is independent, I think this should be highly parallelizable but I am unsure of how to go about this.
Previously I was using xlwings to insert data and track the target cell value and this worked. It took around 4 hours to run 10000 samples but I thought that by moving the calculations outside of excel, I should see some improvements but the opposite seems to be true.
You need to sign in to view this answers
