Matrix Functions in Excel

[tunc]

In addition to the primary feature Table Valued Functions, Matrix Functions are also included in the new beta release (R102) of our Excel add-in named Finaquant in Excel.



Downloads for beta release R102

• FinaquantInExcel_beta102.xll Excel add-in (digitally signed by Finaquant Analytics)
  
• FinaquantInExcel_beta102.zip Zip package including Visual Studio project to develop the add-in (open source)
  
• FinaquantExcelDemo_20140924.xls Excel workbook with a sheet named Parameters for Getting Started
  
• Visual User Guide Installing the add-in, table function examples, developer notes (pdf file)

Don't forget to visit article Empowering Excel with Table Functions for most recent releases and installation steps.

DEVELOPER NOTES

Three new code files are added to Visual Studio project FinaquantInExcel for matrix functions:

MatrixX.cs Class with matrix functions that can be called in Excel VBA. The methods of this class are simple wrappers that make some selected matrix functions of the underlying .NET library Finaquant Calcs available (i.e. callable) in VBA. Developers can extend this class to have more matrix functions in VBA.



Following code example shows how you can call the non-static methods of this class (matrix functions) in VBA:

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. Sub Test_MatrixX()
2. Dim M1 As Object: Set M1 = CreateObject("Finaquant_KeyMatriX")
3. Dim M2 As Object: Set M2 = CreateObject("Finaquant_KeyMatriX")
4. Dim R As Object: Set R = CreateObject("Finaquant_KeyMatriX")
5. ' read matrix from named range
6. Call M1.ReadFromExcel("Matrix2")
7. Call M2.ReadFromExcel("Matrix3")
8. ' print properties
9. Debug.Print M1.nRows
10. Debug.Print M2.nRows
11. ' print matrix
12. Debug.Print M1.ToString()
13. ' matrix multiplication
14. Set R = M1.MultiplyMatrix(M2)
15. ' write result to excel range
16. Call R.WriteToExcel("Matrix4", "Sheet2", "", "B12")
17. ' get inverse matrix
18. Set R = M1.Inverse()
19. ' write inverse matrix to excel range
20. Call R.WriteToExcel("Matrix5", "Sheet2", "", "F2")
21. ' determinant
22. Debug.Print M1.Determinant()
23. End Sub

GeSHi ©

ExcelMatrix.cs A class with application oriented non-static methods (matrix functions). It includes a list of matrix functions that you want to make directly available in Excel as command menu item or a ribbon button.



The methods of this class are also available (i.e. callable) in Excel VBA, as shown in the example below:

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. ' Test ExcelMatrix class methods
2. Sub Test_ExcelMatrix()
3. Dim XM As Object: Set XM = CreateObject("Finaquant_ExcelMatrix")
4. Dim nRows, nCols As Integer
5. Dim SheetName, RangeName, TopLeftCell As String
6. ' create matrix with random-valued elements
7. nRows = 3
8. nCols = 4
9. SheetName = "Sheet2"
10. TopLeftCell = "B3"
11. Call XM.CreateRandomMatrix(nRows, nCols, RangeName, _
12.     ThisWorkbook.FullName, SheetName, TopLeftCell)
13. End Sub

GeSHi ©

Following code example (in C#/.NET) shows the typical flow of a matrix calculation (read, calculate, write) in a method of class ExcelMatrix:

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. public void MultiplyMatrices(string InRangeName1, string InRangeName2,
2.     string OutRangeName, string WorkbookFullName = null,
3.     string TargetSheetName = "ResultMatrix", string TopLeftCell = "A1")
4. {
5.     // Typical flow of calculation:
6.  
7.     // Step 0: Get current application and active workbook
8.     Excel.Application xlApp = ExcelFunc_NO.GetExcelApplicationInstance();
9.     Excel.Workbook wbook;
10.  
11.     if (WorkbookFullName == null || WorkbookFullName == String.Empty)
12.         wbook = xlApp.ActiveWorkbook;
13.     else
14.         wbook = ExcelFunc_NO.GetWorkbookByFullName(xlApp, WorkbookFullName);
15.  
16.     // Step 1: Read excel ranges into input matrices
17.     KeyMatrix M1 = ExcelFunc_NO.ReadKeyMatrixFromExcel(InRangeName1,
18.         WorkbookFullName);
19.     KeyMatrix M2 = ExcelFunc_NO.ReadKeyMatrixFromExcel(InRangeName2,
20.         WorkbookFullName);
21.  
22.     // Step 2: Generate resultant (output) matrices
23.     KeyMatrix R = KeyMatrix.MultiplyMatrices(M1, M2);
24.  
25.     // Step 3: Write resultant matrices to excel
26.     ExcelFunc_NO.WriteKeyMatrixToExcel(wbook, R, TargetSheetName,
27.         TopLeftCell, OutRangeName);
28.     ((Excel.Worksheet)wbook.Worksheets[TargetSheetName]).Activate();
29. }

GeSHi ©

Note that each matrix is represented with the name of its range. That is, range names are used as input and output parameters. This is similar to table functions where the names of ListObjects (excel tables) are used as input and output parameters.

How to name a range in Excel: Select cells > right-mouse menu > Define Name
How to see the list of range names in a workbook: Press F3
How to delete range names: Press Ctrl-F3

See also: Deleting range names in Excel

ExcelMatrixDNA.cs The static methods of this class are attributed as either ExcelFunction or ExcelCommand.

As ExcelFunction attributed methods are available as worksheet functions in Excel:



As ExcelCommand attributed methods appear as menu option in the command menu named Table Functions.



A static method of class ExcelMatrixDNA can be called in VBA as follows:

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. ' Test ExcelMatrixDNA class methods
2. Sub Test_ExcelMatrixDNA()
3. Dim x As Double
4. Dim RangeName As String
5.  
6. ' call matrix sum
7. RangeName = "Matrix1"
8. x = Application.Run("MatrixSum", RangeName)
9.  
10. ' display result in immediate window
11. Debug.Print "Matrix sum = " & x
12. End Sub

GeSHi ©

Most methods of the class ExcelMatrixDNA do nothing other than calling the methods of the class ExcelMatrix.

Some methods that are attributed with ExcelFunction may return an array instead of a scalar value:

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. [ExcelFunction(Description = "Matrix Multiplication,
2. returns a 2-dim array (array formula)", Category = "Matrix Functions")]
3. public static double[,] MatrixMultiplication(string RangeName1,
4.     string RangeName2)

GeSHi ©

Press Control-Shift-Return to enter such functions (array formula) in a worksheet.


The formula will be automatically enveloped with curved brackets denoting that this is a function which returns an array.

In order to expand an array to see all the elements (1) select the cell with array formula with neighboring cells, (2) Press F2 to activate the array formula and (3) press Ctrl-Shift-Return to expand.


For more information about array formulas in Excel you may visit:
Introducing array formulas in Excel

Tunc

[tunc]

What can be done to extend the matrix functions in this excel add-in?

• Adopting (i.e. wrapping in class KeyMatrixX) more matrix functions from the .NET library Finaquant Calcs
  
• Developing new composite matrix functions based on the available matrix and vector functions in Finaquant Calcs
  
• Extending the palette of matrix functions with an extensive open-source .NET library like ILNumerics

[tunc]

Following code shows how to convert a finaquant matrix (KeyMatrix) to an ILNumerics matrix:

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. using ILNumerics;
2. using FinaquantCalcs;
3.  
4. public static ILArray<double> ConvertFQtoILNum(KeyMatrix M)
5. {
6.     // get row and column size
7.     int RowCount = M.nRows;
8.     int ColumnCount = M.nCols;
9.  
10.     // convert matrix type from Finaquant to IlNumerics
11.     return array<double>(KeyMatrix.MatrixToVector(M,
12.         RowColDirection.ColByCol).toArray, RowCount, ColumnCount);
13. }

GeSHi ©

Following code shows how to convert an ILNumerics to a finaquant matrix (KeyMatrix) matrix:

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. using ILNumerics;
2. using FinaquantCalcs;
3.  
4. public static KeyMatrix ConvertILNumToFQ(ILArray<double> M)
5. {
6.     // get row and column size
7.     int RowCount = M.S.ToIntArray()[0];
8.     int ColumnCount = M.S.ToIntArray()[1];
9.  
10.     // convert matrix type from IlNumerics to Finaquant
11.     return OneDimArrayToMatrix(M.ToArray(), RowCount, ColumnCount);
12. }

GeSHi ©