SAFE TOOLBOXES® allows you to do linear algebra calculations without much effort. It comes with a great variety functions (see list below) and provides some facilities to handle matrices and vectors.
# | Function Name | Function Definition |
---|---|---|
1 |
sMx |
Transforms a range into a matrix. |
2 |
sMxBindCols |
Inserts the columns in the matrix. |
3 |
sMxBindRows |
Inserts the rows in the matrix. |
4 |
sMxcMult |
Returns the multiplication of a matrix by a scalar. |
5 |
sMxColApply |
Apply the expression in each matrix column. |
6 |
sMxCorrelation |
Returns the correlation matrix of inputted series. |
7 |
sMxCorrelationPValues |
Returns the p-values of the inputted series correlation matrix. |
8 |
sMxCorrelationToPositiveDefinite |
Returns an approximation of the correlation matrix that is positive definite. |
9 |
sMxCovariance |
Returns the covariance matrix of inputted series. |
10 |
sMxcSum |
Sum a scalar to all matrix elements. |
11 |
sMxDecompositionCholesky |
Returns the Cholesky decomposition. |
12 |
sMxDecompositionEigen |
Returns the Eigen decomposition. |
13 |
sMxDecompositionLU |
Returns the LU decomposition. |
14 |
sMxDecompositionQR |
Returns the QR decomposition. |
15 |
sMxDecompositionSingularValue |
Returns the singular value decomposition. |
16 |
sMxDeleteCol |
Returns the matrix without the specified column. |
17 |
sMxDeleteRow |
Returns the matrix without the specified row. |
18 |
sMxDiagonalize |
Put all vector elements in the main diagonal of a matrix. |
19 |
sMxElemDiv |
Returns the division of each element of Matrix A by the corresponding element of Matrix B. |
20 |
sMxElementApply |
Apply the expression in each matrix element. |
21 |
sMxElemExp |
Applies the exp function to each matrix element. |
22 |
sMxElemInv |
Returns the inverse of each matrix element. |
23 |
sMxElemLog |
Returns the natural logarithm of each matrix element. |
24 |
sMxElemLog10 |
Returns the base-10 logarithm of each matrix element. |
25 |
sMxElemMult |
Returns the element-wise multiplication of matrices. |
26 |
sMxElemPower |
Returns the corresponding power of each matrix element. |
27 |
sMxExponentialGrowthRate |
Returns the exponential growth rate within each inputted series. |
28 |
sMxGetChange |
Returns the change within each inputted series. |
29 |
sMxGetCol |
Returns the selected column of the matrix. |
30 |
sMxGetCols |
Returns the selected columns of the matrix. |
31 |
sMxGetDiagonal |
Get the matrix main diagonal and put it on a vector. |
32 |
sMxGetElement |
Returns the element at the given position in the matrix. |
33 |
sMxGetRow |
Returns the selected row of the matrix. |
34 |
sMxGetRows |
Returns the selected rows of the matrix. |
35 |
sMxGetSubMatrix |
Returns a partition of the matrix. |
36 |
sMxHash |
Returns a unique number that identifies the matrix. |
37 |
sMxIdentity |
Returns with ones on the main diagonal and zeros elsewhere. |
38 |
sMxInv |
Returns the inverse of the square matrix. |
39 |
sMxIsCol |
Tells whether the inputted matrix is a column vector. |
40 |
sMxIsEqual |
Tells if the two matrices are equal. |
41 |
sMxIsPositiveDefinite |
Tells if the matrix is positive definite. |
42 |
sMxIsRow |
Tells whether the inputted matrix is a row vector. |
43 |
sMxIsSingular |
Tells whether the inputted matrix is singular. |
44 |
sMxIsSymmetric |
Tells if the selected matrix is positive definite. |
45 |
sMxJoinOuter |
Returns the SQL outer join of the two matrices, using the first column as the reference. |
46 |
sMxKurtosis |
Returns the kurtosis of each inputted series. |
47 |
sMxKurtosisPopulation |
Returns the population kurtosis of each inputted series. |
48 |
sMxMaximum |
Returns the maximum of each inputted series. |
49 |
sMxMean |
Returns the mean of the inputted series. |
50 |
sMxMinimum |
Returns the minimum of each inputted series. |
51 |
sMxMult |
Returns the multiplication of matrices. |
52 |
sMxNegate |
Returns the multiplication of a matrix by -1. |
53 |
sMxOnes |
Returns a matrix with ones in all elements. |
54 |
sMxOrderBy |
Returns the matrix ordered by the selected row or column index. |
55 |
sMxPercentualChange |
Returns the percentage change within each inputted series. |
56 |
sMxPrincipalComponentAnalysis |
Performs the Principal Component analysis. |
57 |
sMxPseudoInverse |
Returns the matrix pseudo-inverse. |
58 |
sMxRepeat |
Returns a vector with the inputted value repeated a number of times. |
59 |
sMxReplaceCol |
Replaces the specified column in the matrix by a new one. |
60 |
sMxReplaceElement |
Replaces the element at the given position in the matrix. |
61 |
sMxReplaceRow |
Replaces the specified row in the matrix by a new one. |
62 |
sMxReplaceSubMatrix |
Replaces the specified partition in the matrix by a new one. |
63 |
sMxResize |
Forces matrix to have the specified size. |
64 |
sMxReturnsCompound |
Returns the compounded returns of each inputted series. |
65 |
sMxReturnsSimple |
Returns the simple returns of each inputted series. |
66 |
sMxRowApply |
Apply the expression in each matrix row. |
67 |
sMxSize |
Returns the dimension of the given matrix |
68 |
sMxSkewness |
Returns the skewness of each inputted series. |
69 |
sMxSkewnessPopulation |
Returns the population skewness of each inputted series. |
70 |
sMxSolve |
Returns the solution Matrix X of AX = B. |
71 |
sMxStackUp |
Transforms a matrix into a vector. |
72 |
sMxStandardDeviation |
Returns the standard deviation of each inputted series. |
73 |
sMxStandardDeviationPopulation |
Returns the population standard deviation of each inputted series. |
74 |
sMxSum |
Returns the sum of two or more matrices. |
75 |
sMxSumElements |
Sum all matrix elements. |
76 |
sMxT |
Transpose a matrix. |
77 |
sMxToString |
Transforms the inputted matrix in one single string. |
78 |
sMxVariance |
Returns the variance of each inputted series. |
79 |
sMxVariancePopulation |
Returns the population variance of each inputted series. |
80 |
sMxVectorDiv |
Returns the element-wise division of a matrix by a vector. |
81 |
sMxVectorMult |
Returns the element-wise multiplication of a matrix by a vector. |
82 |
sMxZeros |
Returns a matrix with zeros in all elements. |
83 |
sVecBind |
Merges the vector 1 and 2. |
84 |
sVecOrder |
Returns the indices of the vector in sorted order. |
85 |
sVecSeparate |
Transforms a vector into a matrix. |
86 |
sVecSequence |
Returns a sequence of numbers. |
87 |
sVecSort |
Sort the vector in a specified order. |
The usage of a linear algebra formula is straightforward and it's performed like any other Excel function. The only difference is that at the end of calculation you should press the MVF button on the ribbon tab (an option also available under the right click menu). The example below, for instance, illustrate the result of an Eigen decomposition of a matrix.
A |
B |
C |
D |
E |
F |
G |
H |
I |
|
1 |
Eigen decomposition of A = L*V |
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
3 |
Matrix A: |
|
|
Decomposition: |
|
|
|
|
|
4 |
2 |
3 |
|
L |
4 |
V |
0.83205 |
-0.70711 |
{=sMxDecompositionEigen(Sheet1!$A$4:$B$5)} |
5 |
2 |
1 |
|
L |
-1 |
V |
0.5547 |
0.707107 |
|
6 |
|
|
|
|
|
|
|
|
|
Most of the linear algebra functionalities can also be performed through the “Data manipulation” group menu available in the General Toolbox tab. To perform the same decomposition, please follow these steps:
After confirming, the formula “=sMxDecompositionEigen(Sheet1!$A$4:$B$5)” will be automatically added to the range D4:H5. A more detailed exposition of the data manipulation tool can be found here.