Learning Center

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:

  1. Go to the General Toolbox tab;
  2. Under the “Data manipulation” group, select the option “Eigen decomposition” in the “Common operations” combo box;
  3. Set the input range to A4:B5 and the cell D4 to be the first cell of the output.

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.

© 2016-2018 Safe Quantitative Technologies, ltd. All rights reserved.