Matrix math -- Application development 3

[ePOWEReng]

Hello,

I am considering taking some calculations that presently performed using MathCAD and creating an application to perform those calculations instead. The calculations involve creating a matrix of complex numbers and performing some matrix math operations to arrive at the solution. The complex matrices will be up to 200x200 in size and matrix inversion will be required. My question is what programming language and development platform would make be the easiest to implement a program like this in? Are there libraries out there for performing complex matrix inversion? I was considering using the microsoft visual platform...

Thanks for your input and suggestions.

 

[electricpete]

Thanks Doug. That is some useful info for anyone getting started with Alglib in vba.

I will use some of your ideas for interface function. I like the idea of allowing spreadsheet range as input whose .value property returns a variant matrix.

One thing about Alglib... there are a variety of routines that perform similar functions with similar names. To avoid adding to the confusion, I think I would just name the interface functioms something like aif_xxx where xxx is the exact alglib routine number. (AIF for Alglib Interface Function).


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Thanks Doug. That is some useful info for anyone getting started with Alglib in vba.

I will use some of your ideas for interface function. I like the idea of allowing spreadsheet range as input whose .value property returns a variant matrix.

One thing about Alglib... there are a variety of routines that perform similar functions with similar names. To avoid increasing the complexity of an already-confusing naming scheme, I think I would just name the interface functioms something like aif_xxx where xxx is the exact alglib routine number. (AIF for Alglib Interface Function).

For matrices in vba, there are a few routines that have to be built.
multiplication, inversion, and determinant, unless you use the worksheet functions which have some limitations.

multiplication of a matrix by a scalar.

addition of matrices

negation of matrices.

retrieving portion of a matrix (like row 2-4, column 3-5) or replacing portion fo a matrix.

There is also the problem of assignment that I mentioned. You can get around that one using the variant matrix type. There are pro's and con's there. One downside is that will slow calcualtions way down. Also you will be limited in what you can do for error checking. In contrast if you use an assignment routine, you can error check the size of the matrices.

There are no insurmountable obstacles for doing complicated matrix manipulations in vba similar to Matlab. Just a lot of bookkeeping details.


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Sorry, I didn't mean to include inversion and determinant as routines the user would build. I think we can get them from Alglib.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IDS]

electricpete - thanks for the feedback. I agree with the comment about the procedure names being potentially confusing if done on an ad-hoc basis (as I have been up to now). The only problem I have with just sticking an AIF_ on the front is that I don't like long function names (especially for UDFs), but I agree something systematic needs to be done.

With regard to assiging spreadsheet ranges to variant arrays - a few points:

It's better to use the .value2 property than .value because that has less memory overhead and is significantly quicker. I think you lose the currency formatting information (which I never use anyway).

It's true that operating on double arrays is much quicker than working with variant arrays, but if you are transferring data to the Alglib routines you need to convert them to a base 0 array of doubles anyway, so you will be doing all the processing on doubles, not variants.

I wasn't sure what you meant about the error checking. You can check the size of a variant array just like any other array.

I've been playing with matrix multiplication routines. I was a bit surprised to find that using worksheetfunction.mmult was much faster than any VBA routine I could come up with. In XL2007 the maximum matrix size is much bigger than in earlier versions. I don't know what the maximum size is, but it's more than 500x500.

The AlgLib routines have matrix multiplication buried away in the ABLAS module (RMatrixGEMM and CMatrixGEMM). They have a whole long list of inputs (allowing scalar matrices, and working with sub matrices) but it is quite easy to write a front end just requiring the two matrices to be multiplied as input. When I get time I will compile the c++ version and see how it compares with the worksheetfunction.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

Good comments.

Regarding error checking – if you plug the wrong type of data into a variant, there will be no error. That’s probably not a big problem, just depends on the situation and your preferences.

I did find that we can do block assignment (using =) for arrays of doubles if we are careful about the syntax as below.

I did a timing test similar to what you described for matrix multiplication.

Results were:
mmult takes 0.34 sec
vba routine to multiply doubles time takes 1.42 sec
vba routine to multiply variants time takes 3.08

So there is definitely a penalty for using variants if you will be doing a lot of calculations with them (not relevant if you are just passing straight to Alglib... depends what you’re doing). I agree it is interesting that excel’s mmult is so much faster.... I assume it is somehow coded at a lower level that doesn’t have so much book-keeping on the indeces.

Here is the code that I used to run the timing test:

Option Explicit
Option Base 1

Sub timetest()  '   TIMING TEST:
' OBJECTIVE: Compute C = A*B 50 times using 3 different methods

' Miscellaneous Variables:
Dim counter As Integer ' iteration counter
Const iters = 50 ' iteration limit
Const matsize = 65 ' matrix size
Dim tstart As Double, telapsed As Double  'timing variables


' define/initialize variant matrices A, B, C
Dim av As Variant
Dim bv As Variant
Dim cv As Variant
av = eye(matsize)
bv = eye(matsize)

' define/initialize double matrices A, B, C
Dim ad() As Double
Dim bd() As Double
Dim cd() As Double
ad = eye(matsize)
bd = eye(matsize)

' Time test using excel Mmult:
tstart = Timer
For counter = 1 To iters
cv = Application.WorksheetFunction.MMult(ad, bd) ' didn't work with cd =...
Next counter
telapsed = Timer - tstart
Debug.Print "excel mmult time is " + CStr(telapsed)

' Time test using vba function with doubles
tstart = Timer
For counter = 1 To iters
cd = ma_multdoub(ad, bd)
Next counter
telapsed = Timer - tstart
Debug.Print "multiply doubles time is " + CStr(telapsed)

' Time test using vba function with variants
tstart = Timer
For counter = 1 To iters
cv = ma_multvar(av, bv)
Next counter
telapsed = Timer - tstart
Debug.Print "multiply variants time is " + CStr(telapsed)

End Sub


Function ma_multvar(a, b) ' vba function to multiply using variants

'Error check on dimensions:
If UBound(a, 2) <> UBound(b, 1) Then MsgBox ("error in input dimensions ma_multvar")

ReDim output(1 To UBound(a, 1), 1 To UBound(b, 2)) As Double
Dim row As Integer, col As Integer, counter As Integer

For row = 1 To UBound(a, 1)
  For col = 1 To UBound(b, 2)
    For counter = 1 To UBound(a, 2)
      output(row, col) = output(row, col) + a(row, counter) * b(counter, col)
    Next counter
  Next col
Next row

ma_multvar = output
End Function

Function ma_multdoub(a() As Double, b() As Double) As Double()  ' Multiply doubles
' vba function to multiply using variants

' Error check on dimensions:
If UBound(a, 2) <> UBound(b, 1) Then MsgBox ("error in input dimensions ma_multvar")

ReDim output(1 To UBound(a, 1), 1 To UBound(b, 2)) As Double
Dim row As Integer, col As Integer, counter As Integer

For row = 1 To UBound(a, 1)
  For col = 1 To UBound(b, 2)
    For counter = 1 To UBound(a, 2)
      output(row, col) = output(row, col) + a(row, counter) * b(counter, col)
    Next counter
  Next col
Next row

ma_multdoub = output
End Function

Function eye(n) As Double() ' Return identify matrix "I"

ReDim tempeye(1 To n, 1 To n) As Double
Dim row As Integer, col As Integer

For row = 1 To n
  For col = 1 To n
    If row = col Then
        tempeye(row, col) = 1
    Else
        tempeye(row, col) = 0
    End If
  Next col
Next row

eye = tempeye
End Function

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IDS]

electricpete - a couple of points on your code:

The point of using a variant array is in getting data from the spreadsheet into VBA using:

VBAarray = Range("rangename").value2

If VBAarray is declared as a double this won't work.
If the array is going to be used in something like matrix multiplication or inversion, then it is well worthwhile to copy it into a double array with two nested for-next loops. In the case of the AlgLib routines this also allows you to change to base zero, and assign the values to one of the Alglib data types where appropriate.

A minor point on your counters, it's actually more efficient to declare them as longs rather than integers, even if they will always be within the integer range, because VBA converts integers into longs anyway. Or so I'm told, I've never actually checked it.


I have now compiled the AlgLib C++ matrix multiplication routine, and got the following results multiplying two 250 x 250 matrices once:

C++ function: 0.06 sec
worksheetfunction.Mmult with variant array: 0.51 sec
worksheetfunction.Mmult with double array: 0.51 sec
worksheetfunction.Mmult with range object: 0.48 sec
VBA function with double array: 1.6 sec
VBA function with variant array: 4.5 sec

I've found the same with matrix inversion etc, the C++ routines are of the order of 10 times faster than the equivalent Excel built in function, and about 30 times faster than a VBA function operating on doubles.

So the question is not why the built in functions are fast, it is why are they so slow?

BTW, the compiled functions are also available to use on the worksheet, and still have much the same speed advantage.





Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

Interesting about the long vs integer. I tried my code above with long and the time did indeed decrease 10% - 20%.

Can you post the code that you used to compare variant multiplication and double multiplication? I don't understand why your results would show identical performance when mine showed more than a factor of 2 difference.


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Can you post the code that you used to compare variant multiplication and double multiplication? I don't understand why your results would show identical performance when mine showed more than a factor of 2 difference.

Never mind - I see you were just testing these variable types with mmult.


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IDS]

Yes, no question that variants are way slower than doubles for calculation intensive applications.

The only question is whether it is worth the time and effort of copying the variant array (as delivered from the spreadsheet) into a double array. It seems that the answer for any but the smallest matrix operation is yes.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

One other small advantage of double ( ) over variant is it will improve the self-documenting nature of the code – it’s easier to figure out what a variable represents when you know it’s type. This applies both when reviewing he code and when typing in a call for a user defined function ( the interactive display editor prompts you with names and data types for each argument).

I plan on building a small library of routines for real matrices: matrix multiplication, scalar multiplication, addition, negation, transpose, extract submatrix, insert submatrix, determinant, inverse, input/output etc. (some are just calls to other routines such as AlgLib). Ideally they are standardized in terms of the forrm that they accept and return real matrices, and I’ll use double() for that purpose. The function names will all begin with prefix ma_.. for matrix. Then I’ll build an analogous library of routines for complex matrices with same names, but prefix cm_ for complex matrix. Changing the type declarations in modules, function argument declarations, and function return declaration would just involve changing double to complex (in contrast if we used variant, there is a fundamentally different structure that is not as easily adaptable)

Sorry – didn’t mean to beat a dead horse on choice of type – I am sure I’m not telling you anything new about programming in general... just some thoughts about my preferences and where I think I’m heading.

Separate subject – it is revealing to see that C++ compiled routines work so much faster. Unfortunately I haven’t worked with that at all so I don’t think I can easily take advantage of it.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.