1

I have many equations with many unknowns (my data is in Excel) and currently I am using matrix method to solve them. I use inbuilt MMULT (matrix multiply) and MINVERSE (matrix inverse) in following form :- Result = MMULT (MINVERSE(matrix1),matrix2) Here lies the problem, my matrices are of the order of 2000 x 2000 or more, and Excel takes lot of time in doing inverse (matrix multiplication is very fast).

What is the best way for me to speed up the process? I don't mind exporting data to any external 3rd party program (compatible with Excel) and then importing the inversed matrix back to Excel.

I don't know much C / C++, but I feel if we compile the Matrix Inverse Function into a DLL and then use the same from excel VBA, maybe the speed will improve. Please help.

Please see following link : Why is MATLAB so fast in matrix multiplication?

It is found that MATLAB has highest speed for matrix calculations. Can we use any such library in Excel / VBA ?

I have found certain libraries such as LAPACK, ARMADILO etc which are in C / C++ / C# or .NET. How can I use compiled versions of these libraries in my Excel VBA?

Community
  • 1
  • 1
maverick
  • 83
  • 3
  • 10
  • The inverse function is probably *already* implemented in C/C++, along with the rest of the Excel application. Might be worth reviewing this link to see if there are things you can apply to your workbook: https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx – Tim Williams Mar 07 '16 at 20:19
  • Matrix inversion is slow (order of N^3 operations), so linear algebra libraries will decompose matrix1 into the product of lower and upper triangular matrices: matrix1=L×U. This method can solve y = Mx in n^2 operations (IIRC). Also, if matrix1 has one of a number of special structures (triangular, symmetric, banded, etc.) there are library routines that will solve these much faster than n^3. Finally, if the same matrix1 occurs repeatedly, you *could* save and reuse the inverse. – xidgel Mar 07 '16 at 21:33
  • A bit roundabout -- but Python might provide a glue connecting VBA and the highly optimized scipy/numpy libraries. See this: https://newtonexcelbach.wordpress.com/2014/05/22/linking-to-python-with-excel-python-and-python-matrix-functions/ – John Coleman Mar 08 '16 at 15:33

1 Answers1

3

I am the author of the blog linked by John Coleman. There are quite a few options available, depending on your familiarity with compiling and linking to different languages. I'd say the main options are (in order of ease of implementation):

  1. Use the Python Numpy and Scipy libraries with ExcelPython or xlwings (which now includes ExcelPython). The most recent post on the subject at my blog (including free downloads) is: https://newtonexcelbach.wordpress.com/2016/01/04/xlscipy-python-scipy-for-excel-update-with-new-functions/

Advantages: Free Easy to implement (especially using Anaconda Python, which now includes xlwings).
No compilation required.

The Scipy code is fast, and includes packages for sparse matrices, which where applicable makes a huge difference to solving time, and the maximum size of matrix that can be handled.

Free and open-source spreadsheet implementation available on my blog.

Disadvantages: The overhead in transferring large data sets from VBA to Python can be significant, in fact for small to medium sized matrices it is often greater than the solution time.

  1. Use the Python version of the Alglib library. Search my blog for Alglib for recent examples using Python, and older examples using C++ and C#.

Advantages and disadvantages: As for Numpy and Scipy, except there is a commercial version available as well, which may offer performance advantages (I haven't tried it).

  1. Use Numpy/Scipy with Pyxll. https://newtonexcelbach.wordpress.com/2013/09/10/python-matrix-functions-using-pyxll/ for sample spreadsheet.

Advantages: The data transfer overhead should be much reduced. More mature and documentation is better than the current xlwings.

Disadvantages: Commercial package (but free for evaluation and non-commercial use) Not open source.

  1. Use open source C++ or Fortran packages with Visual Studio or other compiler, to produce xll based functions.

Advantages: Potentially the best all-round performance.

Disadvantages: More coding required. Implementation more difficult, especially if you want to distribute to others. 32 bit/ 64 bit issues likely to be much harder to resolve.

As mentioned in the comments, matrix inversion is much slower than other matrix solution methods, but even allowing for that, the built in Excel Matrix functions are very slow compared with alternative compiled routines, and the advantages of installing one of the alternatives listed above is well worth the effort.

Doug Jenkins
  • 94
  • 6