.chapter11<-function(i=0){ " i Chapter 11: Matrix manipulation - ------------------------------------- 1 What is a matrix? 2 Row, column vs. matrix 3 Dimension requirement for matrix manipulation 4 hit ctrl-shift-Enter 3 keys simultaneously 5 Convert a row (row)to a column (column) 6 Transpose an n*m matrix to an m*n matrix 7 for a large data set, how to find its last row? 8 for a large data set, how to highlight it? 9 Explanation of matrix multiplication 10 Excel mmult() function 11 Using excel mmult() function for the 1st time 12 one row times a column will be one number 13 one column (n *1) times a row (1 * m ) will be a matrix (n * m) 14 one row times the transpose of itself 15 one row times the transpose of another row 16 one column times the transpose of itself 17 one column times the transpose of another column 18 If 'Developer' is not available on your menu bar 19 VBA for estimating variance-covariance matrix 20 VBA for estimating correlation matrix 21 Links Example #1:>c11 # find out the above list Example #2:>c11() # find out the above list Example #3:>c11(1) # find the 1st explanation ";.zchapter11(i)} .n11chapter<-21 .zchapter11<-function(i){ if(i==0){ print(.c11) }else{ if(i<=.n11chapter){ x<-paste('cat(.C11EXPLAIN',i,')',sep='') .runText(x) }else{ cat("Invalid number: input value should between 1 and 20. \n") } } } .c11<-.chapter11 .C11EXPLAIN1<-"What is a matrix ///////////////////////////////////// A matrix could be viewed as n rows with m columns 1,2,3,44,11 2,3,4,1,9 2,3,4,1,10 4,5,2,1,0 For a matrix, assume that we have n rows and m columns. Its dimension is n * m or n by m. ///////////////////////////////////// " .C11EXPLAIN2<-"Row, column and matrix ///////////////////////////////////// 1) A row is a special case for matrix Example 1: 1,2,3,10,5,10,11 Example 2: 2.5, 3.5, 0.1 Its dimensions are 1 * m or 1 by m. 2) A column is also a special case for matrix Example #1: 1 2 3 4 10 20 3 4 Its dimension are n * 1 or n by 1. ///////////////////////////////////// " .C11EXPLAIN3<-"Dimension requirement for matrix manipulation ///////////////////////////////////// Rule #1: addition: A + B Matrices A and B should have the same dimensions A (n*m) and B (n *m) Note: A(n*m) means that matrix has n rows and m columns Rule #2: subtraction: A-B Matrices A and B should have the same dimensions A (n*m) and B (n *m) Rule #3: Multiplication : A * B The second dimension of the first matrix should be the same as the first dimension of the second matrix A (n*k) and B (k *m) ///////////////////////////////////// " .C11EXPLAIN4<-"Ctrl-Shift-Enter ///////////////////////////////////// For matrix manipulation, we have to hit three keys of Ctrl, Shift and Enter simultaneously. Example 1: enter three lines in adjacent three cells such as A1, B1, C2. Then another three values in e.g., c4, d4, e4 1 2 3 3 4 5 choose three cells horizontally, then enter the following formula =A1:C1+C4:E4 See different results a) hit enter b) hit Ctrl-Shift-Enter ///////////////////////////////////// " .C11EXPLAIN5<-"Convert a row (column) to a column (column) ///////////////////////////////////// The Excel function used is called transpose() 1) a row to a column -------------------- Assume that we have the following row in A1 to D1. 1 5 3 4 Since there are 4 values, we high light a column with 4 cells. then enter the following equation =transpose(A1:A4) then hit Ctrl-Shift-Enter 2) Convert a column to a row ------------------------- Assume that we have the following column from A1 to A4 1 5 3 4 2.2 Since there are 5 values, we high light a row with 5 cells. then enter the following equation =transpose(A1:A4) then hit Ctrl-Shift-Enter ///////////////////////////////////// " .C11EXPLAIN6<-"Transpose an n*m matrix to an m*n matrix ///////////////////////////////////// Assume that we have the following 5 * 3 matrix in A1 to C5. 1,3,4 5,2,1 3,2,3 4,3,0 7,1,2 Since it is a 5 by 3 matrix, it will be a 3 by 5 matrix after transpose, we highlight a 3 by 5 area then enter the following equation =transpose(A1:C5) then hit Ctrl-Shift-Enter If everything is fine, after we hit three keys (Ctrl-Shift-Enter), we will see {=TRANSPOSE(A1:C5)} ///////////////////////////////////// " .C11EXPLAIN7<-"For large data set, how to find the last row? ///////////////////////////////////// Hit ctrl-End at the same time ///////////////////////////////////// " .C11EXPLAIN8<-"For a large data set, how to highlight it? ///////////////////////////////////// Move our curser to the data set, then hit Ctrl-A Note: here we assume that there is not black row or column in our data set. ///////////////////////////////////// " .C11EXPLAIN9<-"Explanation of matrix multiplication ///////////////////////////////////// We have two matrix A and B. Assume that the dimensions of A is n*m while it is m*k for matrix B. For A, each item, we could use a(i,j), i from 1 to n and j from 1 to m. a11, a12, ...., a1m a2, a22, ...., a2m A = ... .... ..... .. an1, an2 ..... ann For B, each item, we could use b(i,j) where i from 1 to m and j from 1 to k. B11, B12, ...., B1k B21, B22, ...., B2k B = ... .... ..... .. Bm1, Bm2 ..... Bmk Let's call their product as c, i.e, C= A * B For C, each item, we could use c(i,j) where i from 1 to n and j from 1 to m. C11, B12, ...., B1k C21, B22, ...., B2k C = ... .... ..... .. Cn1, Bn2 ..... Bnk The following formula is the most important !!!! In words, for the ith row and jth column in final C matrix, its value is the product of ith row of A times the jth column of B. c(i,j) = a(i,1)* b(1,j) + a(i,2) *b(2,j) + ..... + a(i,m)*b(m,j) ///////////////////////////////////// " .C11EXPLAIN10<-"Excel mmult() function ///////////////////////////////////// Example #1: =mmult(myRow,myClumn) Ctrl-Shit-Enter Example 2: =mmultmyClumn, myRow) Ctrl-Shit-Enter Example 3: =mmult(myRow,transpose(myrow)) Ctrl-Shit-Enter Example 4: =mmult(myColumn,transpose(myColumn)) Ctrl-Shit-Enter Example 5: =mmult(matrixA,matrixB) Ctrl-Shit-Enter Note for dimension requirements, see >e10(9) ///////////////////////////////////// " .C11EXPLAIN11<-" Excel mmult() function the first time ///////////////////////////////////// Example 1: type a row 1, 2, 3 type a column 1 2 3 =mmult(myRow,myClumn) Ctrl-Shit-Enter 1*1 + 2*2 + 3*4 = 14 Example 2: two column 1 2 2 1 3 5 =mmult(tranpose(Column_01),column_2) ///////////////////////////////////// " .C11EXPLAIN12<-"one row times a column will be one number ///////////////////////////////////// Assume that we have the following row. 1 2 3 4 5 and following column 0 3 2 5 6 The final result will be 1*0 + 2*3 + 3*2 + 4*5 + 5*6 ///////////////////////////////////// " .C11EXPLAIN13<-"one column (n *1) times a row (1 * m ) will be a matrix (n * m) ///////////////////////////////////// Assume that we have the following column ( 5 by 1) 1 2 3 3 5 Our row is 1 by 3 1 2 5 Our final result will be 5 by 3 ///////////////////////////////////// " .C11EXPLAIN14<-"One row times the transpose of itself ///////////////////////////////////// Assume that we have a set of values and we want to estimate the sum of their squared value. Here is a example. We have 1, 3, 5 and 10. We want to estimate 1^2 + 3^2 + 5^2 and 10^2=? we generate a row and assume that they are in A1 to D1. 1 3 5 10 =mmult(A1:D1,transpose(A1:D1)) After hit Ctrl-Shift-Enter, we will see 135 appear in the cell. The following would appear in the formula bar. {=MMULT(A1:D1,TRANSPOSE(A1:D1))} ///////////////////////////////////// " .C11EXPLAIN15<-"One row times the transpose of another row ///////////////////////////////////// If we have the same number of values for both row and column then we could use the following formula =mmult(myRow, transpose(myColumn)) ///////////////////////////////////// " .C11EXPLAIN16<-"One column times the transpose of itself ///////////////////////////////////// Assume that we have a set of values and we want to estimate the sum of their squared value. Here is a example. We have 1, 3, 5 and 10. We want to estimate 1^2 + 3^2 + 5^2 and 10^2=? we generate a column and assume that they are in A1 to A4. 1 3 5 10 =mmult(transpose(A1:A4,A1:A4)) After hit Ctrl-Shift-Enter, we will see 135 appear in the cell. The following would appear in the formula bar. {=MMULT(TRANSPOSE(A1:A4),A1:A4)} ///////////////////////////////////// " .C11EXPLAIN17<-"One column times the transpose of another column ///////////////////////////////////// If we have the same number of values for both column then we could use the following formula =mmult(transpose(column1),column2) ///////////////////////////////////// " .C11EXPLAIN18<-"This is for e10(20) VBA issue. ///////////////////////////////////// If \"Developer\" is not available on your menu bar Step 1: click 'File', Step 2: click 'Options' Step 3: click 'Customize Ribbon' Step 4: Activate 'developer' and click 'OK' ///////////////////////////////////// " .C11EXPLAIN19<-"VBA to generating a variance-covariance matrix ///////////////////////////////////// Step 1: highlight and copy the following VBA Function VarCov(rng As Range) As Variant Dim i As Integer Dim j As Integer Dim colnum As Integer Dim matrix() As Double Dim x As Double Dim nrow As Integer colnum = rng.Columns.Count ReDim matrix(colnum - 1, colnum - 1) nrow = rng.Rows.Count For i = 1 To colnum For j = 1 To colnum x = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j)) matrix(i - 1, j - 1) = x * nrow / (nrow - 1) Next j Next i VarCov = matrix End Function Step 2: click 'developer' [Note that if 'developer' is not available on your menu bar, see e10(19). ] Step 3: VBA-> Insert -> Module Step 4: right click your mouse and paste Step 5: File -> Return to Microsoft Excel usage: assume that you have returns for 4 stocks choose a 4 by 4 cells, =varCov(your_ret_matrx) Ctrl-Shift-Enter ///////////////////////////////////// " .C11EXPLAIN20<-"VBA to generating a correlation ///////////////////////////////////// Source:http://www.ozgrid.com/forum/showthread.php?t=45045 Function corrMatrix(dataRange As Object) As Variant On Error Goto 20 Dim r As Integer, n As Integer, rr As Integer, i As Integer, j As Integer, k As Integer, doit As Integer Dim x() As Variant, mc() As Double, ss() As Double, m() As Double, ob As Object r = dataRange.Rows.Count n = dataRange.Columns.Count Redim ss(1 To n, 1 To n), m(1 To n), x(1 To n), mc(1 To n, 1 To n) rr = r For i = 1 To r doit = 0 For j = 1 To n Set ob = dataRange.Cells(i, j) doit = doit + Application.Count(ob) x(j) = ob.Value Next j If doit = n Then For j = 1 To n m(j) = m(j) + x(j) For k = 1 To j ss(j, k) = ss(j, k) + x(j) * x(k) Next k Next j Else rr = rr - 1 End If Next i For j = 1 To n ss(j, j) = (ss(j, j) - m(j) * m(j) / rr) ^ 0.5 mc(j, j) = 1 For k = 1 To j - 1 ss(j, k) = ss(j, k) - m(j) * m(k) / rr mc(j, k) = ss(j, k) / (ss(j, j) * ss(k, k)) mc(k, j) = mc(j, k) Next k Next j corrMatrix = mc Exit Function 20 corrMatrix = CVErr(xlErrValue) End Function ///////////////////////////////////// " .C11EXPLAIN21<-"Links ///////////////////////////////////// Using Excel for matrix https://web.stanford.edu/~wfsharpe/mia/mat/mia_mat4.htm http://facweb.cs.depaul.edu/mobasher/classes/csc575/assignments/MatrixOperations-Excel2007.pdf https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 YouTube Matrix Multiplication and Inverse in Exce (5m22s) https://www.youtube.com/watch?v=G8w-d9U3PJM Matrix transpose and matrix multiplication by Excel (7m29s) https://www.youtube.com/watch?v=6UWG_L8TceU Matrix Math/Excel-1 (5m35s) https://www.youtube.com/watch?v=Kp3F-cJwIJ8 VBA for covariance http://canisius.edu/~yany/excel/varCov.txt VBA for correlation http://www.ozgrid.com/forum/showthread.php?t=45045 http://canisius.edu/~yany/excel/VBAcorreMatrix.txt ///////////////////////////////////// "