How to improve the speed of xlwings UDFs in Excel?

user_8067256 0
Viewed 2707

In Python 3, I'm using UDFs in Excel via xlwings to compute a formula. The formula is computed over 4000 times and it takes about 25 seconds to refresh the worksheet. The formula below is used as an example. The formula is called in Excel in each Excel cell using the formula with a reference to the cells, =test_1(B20,C20,D20). The VBA optimized connection setting is set to true, OPTIMIZED_CONNECTION = True.

@xw.func  
def test_1(x, y, z):  
    a = x**2 + y**2 + z**2  
return a

Calculating the same formula in VBA or in Excel is almost instantaneous. So my question is why is it so slow and is there a way to improve the speed ?

*New Information

Using array formulas is much faster than calling an UDF multiple times. The formula below does the same thing as the original formula but takes a range as input and returns a range as well. 

@xw.func
@xw.ret(expand='table')
def test_array(x, y, z):
    a = np.array(x)**2 + np.array(y)**2 + np.array(z)**2
    return np.transpose(a[np.newaxis])

This is a good workaround when it's possible to use it. However, in cases where it can't be done, the problem still remains.

xlwings excel python performance
3 Answers

Using array formulas is the best way to increase performance. And it is best to leverage pandas where appropriate to get a good speedup.

import xlwings as xw
from pandas import DataFrame
import numpy as np

@xw.func
@xw.arg('T_min', np.array, doc='Daily minimum temperature')
@xw.arg('T_max', np.array, doc='Daily maximum temperature')
@xw.ret(index=False, header=False, expand='down')
def SimpleDegreeDay(T_min, T_max):
    """Function to assemble a dataframe for calculating Degree Day using dynamic arrays.

    :param T_min: Daily minimum temperature
    :param T_max: Daily maximum temperature
    """

    degreeDay = DataFrame({'T_min': T_min, 'T_max': T_max})

    return ((degreeDay['T_min'] + degreeDay['T_max']) / 2) * 10

PyXll may also be useful in achieving speedups.

FlyingKoala does something similar to PyXll and is open source. https://github.com/bradbase/flyingkoala

user_112298730

You can use xlOil (disclaimer: I wrote it) to do this. It is tightly bound using C-APIs so there is very little overhead going between Excel and Python. The docs for the Python plugin are here: https://xloil.readthedocs.io/en/latest/xlOil_Python/index.html but the abridged summary is to run:

pip install xloil
xloil install

Then in a py file add your function:

import xloil
@xloil.func
def test_1(x, y, z):  
    a = x**2 + y**2 + z**2  
    return a

You can either name your py file as YourWorkbookName.py then load the associated workbook or direct xlOil to load your module via its .ini settings file.

I tried running test_1 on 4000 rows and the result appeared almost instantaneously. It is even quicker when called as an array function - you don't need to modify the function in any way to do this, just call =test_1(A1:A4124,B1:B4124,C1:C4124) and the arguments are passed as numpy arrays.

The (minor) price you pay for this speed-up is that your Excel and Python must be of the same bitness, so you may need another python installation.

user_132736470

If you want performance, consider using something other than xlwings. Here's a link to a comparason between the speed of xlwings and PyXLL (an add-in that embeds Python into Excel, see https://www.pyxll.com) https://support.pyxll.com/hc/en-gb/articles/360042910613-What-is-the-difference-between-PyXLL-and-xlwings-#h_4d5038b2-2bd8-4911-95a0-05683c1fd532

You should find that PyXLL is orders of magnitude faster than xlwings due to the way it works. It runs Python in the Excel process itself, so there's much less overhead than calling between Python and Excel compared with xlwings. It is also optimized for numpy, so it should work well in your use case.

Note however that it is not free software.

user_37064780
Related