Unnecessary udf recalculations in another book using what-if analysis cause performance issues

[EliPassov]

Hi I've used xlwings 0.11.3 to replace some heavy excel calculations with udf functions which call python code in a sheet which is auto-recalculating every two seconds. The calculation mode is set to automatic and RTD functions which constantly update financial data trigger the recalculation. This works quite well, and I'm very grateful for all your work on this product.

However I've encountered major performance issues when using excel's "what-if" functionality (Data Table & Goal Seek) as it seems to trigger multiple unnecessary recalculation of udf functions. This even happens when a Goal Seek is triggered in another Workbook which doesn't reference anything in the workbook where the xlwings udfs are used. I can see clearly from my logs the udf functions are called multiple times, despite not being connected in any way to the goal seek calculations. Note that when I close the workbook with the udf functions, the Goal Seek is running much faster. I've recreated the same result in manual calculation mode where the udf function are not running until I trigger the Goal seek, which in turn runs them multiple times for no reason.

I've tired to add to the xlwings.func decorator xlwings.func(volatile=false), this seems to result in not calling the python functions, but I can still see the calls to the VBA udf functions for each scenario the Goal Seek tries to calculate (again, in a different unrelated workbook).

Is there anything I can do to overcome or avoid the recalculation of the udf functions?

I can't send the workbooks I am working on, but I can produce a toy example if that helps.

Thanks

[wkschwartz]

You could try decorating your UDFs with functools.lru_cache from the Python stdlib. Put the lru_cache decorator below all the xlwings decorators.

This wouldn't prevent calling into Python too frequently but it would make multiple calls to the same function with the same arguments be very fast.

[EliPassov]

Thanks for the response, I'm am already using lru_cache for any calls which read static data. However I have plenty of udf calls which should run each time the relevant sheet updates, so this solution is not practical for my case. I also though of somehow disabling the calls within the udf VBA, however this is not very practical as I'm guessing the udf module is replaced by the add-in each time I re-load the python functions.

The work-around I've implemented is a wrapper of goal seek call (from VBA) which temporary disables the calculation of all the sheets where I use xlwings udf functions, while performing the goal seek.

However this solution is quite bad, as any workbook which calls goal seek needs to have this wrapper and maintain and constantly update a list of all sheets which contain xlwings udf calls. Furthermore it is limited to goal seek calls from code, and doesn't solve the issue for using it within excel itself).

[jmelo11]

Hi @wkschwartz , how did you implement the decorator? I'm getting an error when i use @functools.lru_cache() under @xw.func.

[fzumstein]

Use the lru_cache decorator first:

import xlwings as xw
from functools import lru_cache
import time

@lru_cache()
@xw.func
def hello(name):
    time.sleep(5)
    return "hello {0}".format(name)