Matrix Functions in Excel

Basic matrix and vector functions for Excel users and programmers

Matrix Functions in Excel

Postby tunc » 24 Sep 2014, 19:51

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.

Image

Downloads for beta release R102


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.

Image

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.

Image

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:

Image

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

Image

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 ©


Image

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

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.
Image

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

Tunc
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland

How to extend the Matrix Functions of the excel add-in

Postby tunc » 24 Sep 2014, 20:22

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
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland

Matrix: Converting ILNumerics to Finaquant, and vice versa

Postby tunc » 24 Sep 2014, 20:28

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 ©
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland


Return to Matrix and Vector Functions with VBA/Excel

Who is online

Users browsing this forum: No registered users and 1 guest

cron