How to improve the speed of xlwings UDFs in Excel?

Question

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.

Answer 1

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