Python polynomial functions have several features that require manipulation of data passed from Excel:
- In Python polynomial coefficients are listed in order of increasing powers of x (a + bx + cx^2 …), but they are more usually listed with the highest power first, including in my VBA polynomial functions.
- Polynomial functions may have complex numbers as input, and often have complex output, but Excel complex numbers are limited in functionality and are displayed as text strings, which makes use of the values on the spreadsheet difficult. A more convenient format is to pass complex numbers to and from Python as pairs of floats, or 2D arrays of floats.
I have written 3 short Python functions to deal with passing arrays of complex numbers from and to Excel, using pyxll, or within Python code to convert arrays of pairs of floats to Python complex format.
The py_ReverseA function reverses a 1D or 2D array, and with a 2D array the axis to be reversed may be specified, or by default the longer axis is used:
@xl_func @xl_arg('array1', 'numpy_array') @xl_arg('axis', 'int') @xl_return('numpy_array') def py_ReverseA(array1, axis = None): ndims = len(array1.shape) if ndims == 1: return array1[::-1] else: if axis == None: axis = 0 if array1.shape > array1.shape: axis = 1 if axis == 0: return array1[::-1,:] else: return array1[:, ::-1]
py_FloatA2Complex converts an Excel range of values to a Python complex array. If the Excel range is a single column the values will be converted to complex numbers with an imaginary value of zero. Ranges with 2 columns or 2 rows will have pairs of values converted to Python complex values:
@xl_func @xl_arg('array1', 'numpy_array', ndim = 2) @xl_arg('axis', 'int') @xl_return('var') def py_FloatA2Complex(array1, axis = None): rows, cols = array1.shape if axis == None: if rows > 2 or rows >= cols: axis = 0 else: axis = 1 if axis == 0: complexa = np.zeros(rows, dtype=np.complex128) if cols > 1: complexa[:] = array1[:, 0] +1j*array1[:, 1] else: complexa[:] = array1[:, 0] +1j*np.zeros(rows) else: complexa = np.zeros(cols, dtype=np.complex128) if rows > 1: complexa[:] = array1[0, :] +1j*array1[1, :] else: complexa[:] = array1[0, :] +1j*np.zeros(cols) return complexa
Note that this function returns an array of complex numbers, which are primarily intended for use by other Python functions. If it is called from Excel it will return a cache object, displaying as “ndarray@18”. This can be returned back to Python, or another Python function (such as py_Complex2FloatA below) can be used to extract the data in a format that can be displayed by Excel.
Finally, py_Complex2FloatA converts a Python array of complex numbers to a 2 column or 2 row range of Excel doubles:
@xl_func @xl_arg('array1', 'var') @xl_arg('axis', 'int') @xl_return('var') def py_Complex2FloatA(array1, axis = 0): rtn = np.array([array1.real, array1.imag]) if axis == 0: rtn = np.transpose(rtn) return rtn
The screenshot below shows examples of each of these functions: