As noted in the comments here, the main bottleneck in the Python matrix solver functions presented recently was not in the data transfer from Excel, but rather in the creation of the Numpy arrays for very long lists of short lists (see this Stackoverflow thread for more details of the background). It seems there is a substantial time saving in converting the long array into 1D vectors, which can be converted into Numpy arrays very much more quickly. The VBA code below converts a 3D array of any length (up to the maximum allowed in Excel) to 3 vectors.
Function Vectorize(x As Variant, x_1 As Variant, x_2 As Variant, x_3 As Variant) As Long Dim nr As Long nr = x.Rows.Count x_1 = x.Resize(nr, 1).Value2 x_2 = x.Offset(0, 1).Resize(nr, 1).Value2 x_3 = x.Offset(0, 2).Resize(nr, 1).Value2 Vectorize = nr End Function
To transfer these vectors to Python, via ExcelPython, the PyObj function must be used:
Set x = Range("SSA") ' Excel range with 500500 rows and 3 columns n = Vectorize(x, x_1, x_2, x_3) 'Convert range values to 3 vectors ' Create ExcelPython objects for transfer to Python Set x_1 = PyObj(x_1, 1) Set x_2 = PyObj(x_2, 1) Set x_3 = PyObj(x_3, 1)
In Python the three vectors are converted to Numpy arrays:
def xl_getnpvect(x_1, x_2, x_3): timea = np.zeros(4) timea = time.clock() x_1 = np.array(x_1) timea = time.clock() x_2 = np.array(x_2) timea = time.clock() x_3 = np.array(x_3) timea = time.clock() return timea.tolist()
The table below compares the data transfer and conversion times using this method on an Excel range of 500500 rows x 3 columns, with the same operation using a 2D variant array.
The data transfer and array creation times are now a relatively small proportion of the total solution time, even for the iterative solver with a solve time of only 0.28 seconds.