Python matrix functions in Excel, using Pyxll

Following my previous Python post I have now successfully created a number of matrix arithmetic User Defined Functions (UDFs), using Python, SciPy and the PyXll add-in:

Function list

The spreadsheet and associated Python modules may be downloaded from Matrixpyxll.zip

See the previous post for details of downloading and installing the necessary Python and PyXll files.

Writing the python modules proved to be very simple.  Two examples are shown below, and the rest are included in the download files:


@xl_func("numpy_array x: numpy_array")
def py_inv(x):
"""
Return the inverse of a square matrix.
x: square matrix
"""
return inv(x)
@xl_func("numpy_array x: numpy_array")
def py_trans(x):
"""
Return the transpose of a matrix.
x: matrix
"""
return transpose(x)


Note that there is no need to explicitly convert the spreadsheet range to a different data type, or re-size input and output arrays.  Also the comments under the function definition are automatically copied to the Excel function wizard, providing very simple documentation.

I was also pleasantly surprised by the performance of the functions on large data sets:

• Invert a 1000×1000 dense matrix of random numbers: 0.45 sec (more than 70 x faster than the built-in MInverse() function!)
• Invert a 2000×2000 dense matrix of random numbers: 2.2 sec

Examples of the use of the functions is shown in the screen-shots below.  For details of use of array functions see: Using Array Formulas.

Matrix inversion functions

2

Eigen value and Eigen vector functions

3

py_Solve and py_SpSolvecoo