Python in Excel: PY Calculation, Globals & Co-Volatility

Python Calculation

Python in Excel calculates all the PY cells in a workbook sheet by sheet, and row by row, starting at the top left PY cell.

If you break this calculation sequence rule, by for instance moving cell E5 to A6, you get a circular reference or Python error:

By contrast Excel does not really care about the positions of inter-dependent cells: cell C12 has a backwards dependency on C10. To achieve this Excel uses dependency trees that show how the cells depend on one another.

And you can freely move Excel cells around: move E12 to A12 and Excel happily copes.

The Python Global Space

When you define and use variables in Python they are stored and accessed in a Global space. This is the real reason for the left-to-right top-to-bottom PY calculation sequence rule.

Cell C4 defines a Python variable called DF and sets it to 1, then the PY cell in E4 adds 5 to the variable and the PY cell in C6 adds 10 to the variable.
There are no dependencies on Excel cells. (Note the dependency arrows are black).

Now if I move the PY cell in E4 to E6 Python recalculates, and the values change because the calculation sequence has changed to C4->C6->E6.

And if I move E6 to A1 I get a Python error because the PY formula references DF before it has been defined,

If I do the equivalent in Excel then moving cells does not change the values because they are calculated in dependency sequence regardless of position:

Python Co-Volatility

In this example I have values in Cells A3:A10 and simple Python references to each of these cells in C3:C10. The Python cells will calculate top-to bottom, row by row.

Bu when I change cell A10 to 99 then ALL the PY cells will recalculate in sequence even though only cell C10 is dependent on A10 (the image shows that PY cells C3 and C4 have calculated but C5:C10 are pending a Python recalculation)

Whenever a PY cell in a workbook recalculates then ALL the PY Cells recalculate.

I call this “Co-Volatility”.

Conclusion

Whilst Python Globals and Co-volatility make it easy to split a Python script between cells there is a resulting heavy performance penalty when there are several PY cells. This can be somewhat mitigated by using the new “Partial” calculation method which makes Excel calculations automatic but Python calculations only done on request.

But it would be nice to have an option to bypass the co-volatility when the Python Global space is not being used.