Following the previous ExcelPython post I have modified the VBA and Python code for improved performance and to simplify the Python coding. The main change is that where Numpy Arrays are required in the Python code, these are now created in the VBA, as described here (see post number 9 from Eric Reynolds) and here (a more detailed and wide ranging tutorial).
The new spreadsheet and Python Code may be downloaded from MatrixXLpy.zip including full open source code.
I have also updated the performance benchmarks as shown below:
ExcelPython Matrix Solver Functions
I have now separated out the data transfer times from the actual solution time. It can be seen that when operating on a full dense matrix the xl_Solve function is very much faster than any of the sparse functions, but on the 1000×1000 sparse matrix all of the functions performed in much the same time. With the large (10945×10945) sparse matrix (which is too large for the dense functions to handle) the xl_SpSolveCOO function (using the Scipy Library) was significantly faster than the PySparse based xl_SpSolveLU, but the iterative xl_SpSolveit function (also using the PySparse Library) was 5 times faster again. The PySparse based functions operate on the non-zero elements of the lower triangle of symmetrical matrices, allowing substantially larger problems to be solved than could be handled by the xl_SpSolveCOO function.
Coding for the xl_SpSolveCOO function is shown below:
Function xl_SpSolveCOO(SMatrix As Range, SVect As Range) As Variant
Dim Rtn As Variant
Rtn = xl_Run2A("xl_spsolvecooL", SMatrix, SVect, modname2, 1)
If TypeName(Rtn) = "String" Then
xl_SpSolveCOO = Rtn
Else
xl_SpSolveCOO = WorksheetFunction.Transpose(Rtn)
End If
End Function
The Python code is called by the xl_Run2A function:
Function xl_Run2A(FuncName As String, VA_1 As Range, VA_2 As Range, Optional Modname As String = modname1, Optional RtnDims As Long = 2) As Variant
Dim methods As Variant, result As Variant, VA_1py As Variant, VA_2py As Variant, NumpyArray As Variant, Result_List As Variant
On Error GoTo rtnerr
Set NumpyArray = PyGet(PyModule("numpy"), "array")
Set VA_1py = PyCall(NumpyArray, , PyTuple(VA_1.Value2))
Set VA_2py = PyCall(NumpyArray, , PyTuple(VA_2.Value2))
Set methods = PyModule(Modname, AddPath:=Path1)
Set result = PyCall(methods, FuncName, PyTuple(VA_1py, VA_2py))
Set Result_List = PyCall(result, "tolist")
xl_Run2A = PyVar(Result_List, RtnDims)
Exit Function
rtnerr:
xl_Run2A = Err.Description
End Function
The associated Python code is:
import numpy as np
import scipy.sparse as ssp
import scipy.sparse.linalg as sspla
from pysparse import spmatrix, superlu, precon, itsolvers
import linalgfuncs as laf
import time
def xl_spsolvecooL(m, x):
"""
Solve a matrix equation Mx = y in sparse COO format, input from array.
m: three column array of: row indeces, column indeces, values.
x: column x vector
"""
i, j, v, x = list2ijvx(m, x)
n = x.size
A = ssp.coo_matrix((v,(i,j)),shape=(n,n))
return xl_spsolvecoo(A, x)
def list2ijvx(m, x):
"""
Convert 3 column array, m, to 3 vectors, and single column array, x, to vector
"""
v = m[:,2]
i = m[:,0]
j = m[:,1]
x = np.array(x)
x = x[:,0]
return i, j, v, x
def xl_spsolvecoo(A, x):
"""
Solve a matrix equation Ax = y in sparse COO format.
A: Sparse array in COO format.
x: column x vector
"""
A = A.tocsr()
A = sspla.spsolve(A, x)
return A
Newton Excel Bach, not (just) an Excel Blog 