VBA: Intro to Userforms

Tips and Pointers:

1. Creating a new form: On the Insert Menu, Click Userform.
2. You can change name or label of userform in its Properties section. 
3. A form that choose a matrix or data from excel sheet: RefEdit in Toolbox. If there is no RefEdit, Right click on ToolBox--> Additional Controls --> Check refedit.ctrl --> Ok.
4. Double click on the object itself to view Code. OR On the Project Box, Forms folder, Right click on the form and select View Code.
5. To get the range in refedit: NewName = Range(RefeditName.Text)
6. To run the userform:  FormName.show
7. To end the userform: Unload Me

Additional New Codes:

1. Sheets.Add => adding new Sheets
2. Range("A5").Activate => activating the range

Example Process:

Given two matrices of unknown dimensions, we want to multiply them. 

1. Using the Button "Multiply!" in Excel, it opens up the Userform for Matrix Multiplication.

2. Select the two Matrix to multiply.

3. Click Ok, and then the answers will appear in a new sheet.

4. If wrong dimensions, a msgBox will appear.

5.Clicking the Cancel Button will end the Userform.

Code: userform mmult.xlsm

Sub Macro1()

    'For Button Multiply!

    MatrixMult.Show

End Sub

Private Sub CancelBut1_Click()

    Unload Me

End Sub

Private Sub OkBut1_Click()

    MatrixA = Range(mA.Text)

    MatrixB = Range(mB.Text)

    

    'Checker

    If UBound(MatrixA) = UBound(MatrixB) And UBound(MatrixA, 2) = UBound(MatrixB, 2) Then

        'Start of MMult

        r = UBound(MatrixA)

        c = UBound(MatrixA, 2)

    

        Sheets.Add

        ActiveCell.Range("A1").Select

        ActiveCell.Range(Cells(1, 1), Cells(r, c)) = Application.WorksheetFunction.MMult(MatrixA, MatrixB)

    Else

        MsgBox "Wrong dimensions of matrices"

    End If

    

End Sub

Averages, Covariance and Correlation

Recall:

These are some of the important group statistics for portfolio optimization.

1. Averages - a $1$ x $n$ matrix whose inputs are the average of each stocks.
2. Covariance Matrix - the vector product of the mean returns transpose and the mean return itself
3. Correlation Matrix  - $ {cov_{ij}} / {\sqrt{cov_{ii}cov_{jj}}} $

Using functions in VBA

Sub cov()
    Dim str As String
    str = Range("D3")
    
    'calling returns
    ret = Range(str)

    'counting columns
    c = UBound(ret, 2)
    
    'Writing Output
    
    Range("B5") = "Averages"
        'writing answer to averages
        Range("B6").Select
        ActiveCell.Range(Cells(1, 1), Cells(1, c)) = AveM(ret) 'it calls for the function Ave and paste it to a

    Range("B8") = "Covariance Matrix"
        'writing answer to covariance
        Range("B9").Select
        ActiveCell.Range(Cells(1, 1), Cells(c, c)) = CovarianceM(ret)
    
    Range("B" & 10 + c) = "Correlation Matrix"
        'writing answer to correlation
        Range("B" & 11 + c).Select
        cv = CovarianceM(ret)
        ActiveCell.Range(Cells(1, 1), Cells(c, c)) = CorrelationM(cv)
End Sub

Function AveM(ret)
    'summing up all values in each stock and then dividing it by the total number of returns
    
    'counting rows and columns
    r = UBound(ret)
    c = UBound(ret, 2)
        
    'creates 1xc array
    a = Range(Cells(1, 1), Cells(1, c))
    
    'summing up all values in row, then divide it by the row
    For j = 1 To c
        s = 0
        For i = 1 To r
            s = ret(i, j) + s
        Next i
        a(1, j) = s / r
    Next j
        'returns a as the average
        AveM = a
End Function

Function CovarianceM(ret)
    'covariance matrix  = (mean returns transpose x mean returns)/(n-1)
    
    'count rows and columns
    r = UBound(ret)
    c = UBound(ret, 2)
    
    'create dummy range for mean returns
    mret = Range(Cells(1, 1), Cells(r, c))
    
    'create dummy range for average and run the function Ave
    a = Range(Cells(1, 1), Cells(1, c))
    a = AveM(ret)
    
    'solving for mean returns
    For i = 1 To r
        For j = 1 To c
            mret(i, j) = ret(i, j) - a(1, j)
        Next j
    Next i
    
    'solving for transpose and the covariance matrix
    trans = Application.WorksheetFunction.Transpose(mret)
    covm = Application.WorksheetFunction.MMult(trans, mret)
    For i = 1 To c
        For j = 1 To c
            covm(i, j) = covm(i, j) / (r - 1)
        Next j
    Next i
    
    'returning the covariance matrix
    CovarianceM = covm
    
End Function

Function CorrelationM(covm)
    'Correlation Matrix cor_ij = cov_ij / [sqrt(cov_ii)*sqrt(cov_jj)]
    
    'count rows only since covm is an nxn matrix
    r = UBound(covm)
    
    'creating dummy range for cor_ij
    cor = Range(Cells(1, 1), Cells(r, r))
    
    'solving for correlation matrix
    For i = 1 To r
        For j = 1 To r
            cor(i, j) = covm(i, j) / (covm(i, i) * covm(j, j)) ^ (1 / 2)
        Next j
    Next i
    
    CorrelationM = cor
End Function

VBA: Matrix Lecture

Sub matrixP()

'putting the to matrix in an array
a = Range("b3:d5")
b = Range("g3:i5")

'applying matrix multiplication
m = Application.WorksheetFunction.MMult(a, b)
Range("b8:d10") = m

'counting rows and columns
r = Range("b3:d5").Rows.Count
c = Range("b3:d5").Columns.Count

'scalar multiplication
'has to define the array first

cvm = Range("B12:d14")
For i = 1 To r
    For j = 1 To c
        cvm(i, j) = m(i, j) / r
    Next j
Next i
Range("B12:d14") = cvm

________________________________________________________

End Sub

Sub Lecture()
'getting the average

'define returns
a = Range("b3:d5")

'define mean adjusted return
MARM = Range("b9:d11")


For j = 1 To 3
    'average
    m = 0
    For i = 1 To 3
        m = m + a(i, j)
    Next i
    ave = m / 3
    Range("a7").Offset(0, j - 1).Value = ave

    'mean returns
    For kount = 1 To 3
        MARM(kount, j) = a(kount, j) - ave
    Next kount
Next j

Range("B9:D11") = MARM


End Sub
________________________________________________________
Sub Macro3()

'diagonals 1-10
For i = 1 To 10
    Range("a1").Offset(i - 1, i - 1).Value = i
Next i


End Sub
________________________________________________________

Sub Macro4()

'row 10x1, 1-10
For i = 1 To 10
    Range("a" & i) = i
Next i

End Sub

VBA: Covariance Matrix 3

applying what we learned in class to the covariance matrix

This code contains:  averages, mean returns, covariance matrix and changing price to returns.

Sub Cov3()

    Dim ws As Worksheet
    Set ws = Sheets.Add
       
    ws.Range("A1") = "Covariance Matrix"

    ws.Range("A3") = "Prices:"
   
    'prices
    p = Worksheets("Sheet1").Range("B2:K100")
   
    'counting rows and columns
    r = Worksheets("Sheet1").Range("B2:K100").Rows.Count
    c = Worksheets("Sheet1").Range("B2:K100").Columns.Count
   
    'pasting price to ws
    ws.Range("A5").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(r, c))
    ans.Value = p
   
    'Returns
    ws.Range("M3") = "Returns"
       
    'rows of return
    r = r - 1
   
    ret = Range("M6:V103")
    For i = 1 To r
        For j = 1 To c
            ret(i, j) = p(i + 1, j) / p(i, j) - 1
        Next j
    Next i
    Range("M6:V103") = ret
   
    'getting the averages and mean returns
    ws.Range("X3") = "Average"
    ws.Range("AI3") = "Mean Returns"
   
    mret = ws.Range("AI6:AR103")
   
    For j = 1 To c
        'average
        s = 0
        For i = 1 To r
            s = s + ret(i, j)
        Next i
        ave = s / r
        Range("X6").Offset(0, j - 1).Value = ave

        'mean returns
        For kount = 1 To r
        mret(kount, j) = ret(kount, j) - ave
        Next kount
    Next j
   
    ws.Range("AI6:AR103") = mret
       
    'getting the covariance matrix
    ws.Range("AT3") = "Covariance"
    trans = Application.WorksheetFunction.Transpose(mret)
    covm = Application.WorksheetFunction.MMult(trans, mret)
    For i = 1 To c
        For j = 1 To c
            covm(i, j) = covm(i, j) / (r - 1)
        Next j
    Next i
   
    Range("AT6").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(c, c))
    ans.Value = covm
   
End Sub

VBA: Covariance Matrix 2

Here is the second attempt in programming the covariance matrix. I still have problems with getting the returns and dynamic averages.


Sub COV2M()

    Dim ws As Worksheet
    Set ws = Sheets.Add
        
    ws.Range("A1") = "Covariance Matrix"

    ws.Range("A3") = "Prices:"
    
    'prices
    p = Worksheets("Sheet1").Range("B2:K100")
    
    'counting rows and columns
    r = Worksheets("Sheet1").Range("B2:K100").Rows.Count
    c = Worksheets("Sheet1").Range("B2:K100").Columns.Count
    
    'pasting price to ws
    ws.Range("A5").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(r, c))
    ans.Value = p
    
    'Returns
    ws.Range("M3") = "Returns"
        
    'rows of return
    r = r - 1
    
    ret = Range("M6:V103")
    For i = 1 To r
        For j = 1 To c
            ret(i, j) = p(i + 1, j) / p(i, j) - 1
        Next j
    Next i
    Range("M6:V103") = ret
    
    'getting the averages
    ws.Range("X3") = "Average"
    ave = ws.Range("X6:AG6")
    For j = 1 To c
        s = 0
        For i = 1 To r
            s = ret(i, j) + s
        Next i
        ave(1, j) = s / r
    Next j
    ws.Range("X6:AG6") = ave
    
    'getting the mean Returns
    ws.Range("AI3") = "Mean Returns"
    mret = ws.Range("AI6:AR103")
    For i = 1 To r
        For j = 1 To c
            mret(i, j) = ret(i, j) - ave(1, j)
        Next j
    Next i
    ws.Range("AI6:AR103") = mret
    
    'getting the covariance matrix
    ws.Range("AT3") = "Covariance"
    trans = Application.WorksheetFunction.Transpose(mret)
    covm = Application.WorksheetFunction.MMult(trans, mret)
    For i = 1 To c
        For j = 1 To c
            covm(i, j) = covm(i, j) / (r - 1)
        Next j
    Next i
    
    Range("AT6").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(c, c))
    ans.Value = covm
    
End Sub

VBA: Matrix Operations

Since I still don't know how to let the user specify the "range" in a matrix, but I was eager to do something productive, here's what I came up with:

Given to Matrices, find:

1. Sum
2. Scalar Multiplication
3. Matrix Multiplication
4. Inverse
5. Transpose

I'm having difficulty with Inverse as I am having a runtime error '1004': Unable to get the MInverse property of the WorksheetFunction class. somehow, the help button doesn't work. I do know the code is correct. I just have to get why I am having such errors.


Sub MatrixPractice()
    
    matrixA = Range("A1:J10")
    matrixB = Range("L1:U10")

    
    'Count Rows and Columns
    rA = Range("A1:J10").Rows.Count
    rB = Range("L1:U10").Rows.Count
    
    cA = Range("A1:J10").Columns.Count
    cB = Range("L1:U10").Columns.Count
    
    'checker for same m x n
    If Not rA = rB And cA = cB Then
        MsgBox "can't do operations with the two matrices"
        Exit Sub
    Else
        r = rA
        c = cA
    End If
    
    'Sum
    sum = Range("B14:K23")
    Range("A13") = "Sum"
    For i = 1 To r
        For j = 1 To c
            sum(i, j) = matrixA(i, j) + matrixB(i, j)
        Next j
    Next i
    Range("B14:K23") = sum
    
    'Multiplication
    scalarM = Range("B26:K35")
    Range("A25") = "Scalar Multiplication"
    For i = 1 To r
        For j = 1 To c
            scalarM(i, j) = matrixA(i, j) * matrixB(i, j)
        Next j
    Next i
    Range("B26:K35") = scalarM
    
    'Mmult
    MMult = Application.WorksheetFunction.MMult(matrixA, matrixB)
    Range("A37") = "Matrix Multiplication"
    Range("B38:K47") = MMult
    
    'Inverse have to work on it
    'inv = Application.WorksheetFunction.MInverse(matrixB)
    'Range("A49") = "Matrix B Inverse"
    'Range("B50:K59") = inv

    'Transpose
    tran = Application.WorksheetFunction.Transpose(matrixB)
    Range("A61") = "Matrix B Transpose"
    Range("B62:K71") = tran

End Sub

VB: Covariance Matrix

This is getting the Covariance Matrix using the Record Macro. It is to analyze how it is being done in VBA.

Sub CovMatrix()
'
' CovMatrix Macro
' Fixed, Relative References is not used
'
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Covariance Matrix given Prices"
    
    'Prices
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "1. Get the Prices"
    Range("B6").Select
    Sheets("Sheet1").Select
    Range("K100").Select
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("B2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("VBA").Select
    ActiveSheet.Paste
    Range("N4").Select
    Application.CutCopyMode = False
    
    'Returns
    ActiveCell.FormulaR1C1 = "2. Change it into Returns"
    Range("N7").Select
    ActiveCell.FormulaR1C1 = "=RC[-12]/R[-1]C[-12]-1"
    Range("N7").Select
    Selection.Copy
    Range("N8:W104").Select
    ActiveSheet.Paste
    Range("O7:W7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    'Averages
    Range("Y4").Select
    ActiveCell.FormulaR1C1 = "3.Get the Averages"
    Range("Y7").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-11]:R[97]C[-11])"
    Range("Y7").Select
    Selection.Copy
    Range("Z7:AH7").Select
    ActiveSheet.Paste
    Range("Y7:AH7").Select
    Application.CutCopyMode = False
    
    'Mean adjusted Return
    Range("AJ4").Select
    ActiveCell.FormulaR1C1 = "4.Mean Adjusted Returns"
    Range("AJ7").Select
    ActiveCell.FormulaR1C1 = "=RC[-22]-R7C[-11]"
    Range("AJ7").Select
    Selection.Copy
    Range("AJ8:AJ104").Select
    ActiveSheet.Paste
    Range("AK7:AS104").Select
    ActiveSheet.Paste
    Range("AU5").Select
    Application.CutCopyMode = False
    
    'covariance Matrix
    Range("AU4").Select
    ActiveCell.FormulaR1C1 = "5. Get the Covariance matrix"
    Range("AU7:BD16").Select
    Selection.FormulaArray = _
        "=MMULT(TRANSPOSE(RC[-11]:R[97]C[-2]),RC[-11]:R[97]C[-2])/(COUNT(RC[-11]:R[97]C[-11])-1)"
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
End Sub

Thinking of a dynamic way to program this Covariance. Somehow copy and paste seems to be the easier way. Though when reading the code by itself and applying it using relative references, it doesn't make sense. So I have to go back to arrays and matrices.

LP: Simplex Method

Maximization

How lucky my sister was for waking me up at the middle at the night to teach her the simplex method, in the matrix way for her exam in a few hours. How luckier I am, it is one of the class topics we were about to discuss allowing me to recall on some things. 

Here is a simple maximization problem from her book. All we needed to do after the first table is to make the $2$ x $2$ matrix on the upper left an identity matrix and the last $1$ x $2$ matrix on the lower left, zeroes. Remember that these are equations so the "elimination via matrix" is applied.

Minimization

For minimization problems, a little tweaking is needed. Example, if you are given
 \[ \text{min } z = 0.15x_1+0.12x_2\]
with constraints
\[ \begin{split} 60x_1 +60x_2 & \geq 300 \\ 12x_1 + 6x_2 & \geq 36 \\ 10x_1 + 30x_2 & \geq 90 \end{split} \]
then we have the matrix
\[ \begin{bmatrix} 60 & 60 & \vdots & 300 \\ 12 & 6 & \vdots & 36 \\ 10 & 30 & \vdots & 90 \\ \cdots &\cdots & \vdots & \cdots \\ 0.15 & 0.12 & \vdots & 0\\ \end{bmatrix} \]
The transpose of this matrix will give you a maximization problem and the rest of the process is the same as earlier.  

Application - Simplex method Application varies from business to business and are commonly used.

Links for more info is provided below: 
http://pages.intnet.mu/cueboy/education/notes/algebra/simplex.htm
http://college.cengage.com/mathematics/larson/elementary_linear/5e/students/ch08-10/chap_9_3.pdf
http://college.cengage.com/mathematics/larson/elementary_linear/5e/students/ch08-10/chap_9_4.pdf

Matrices in OLS II

The Technicals

In the book Quantitative Methods in Finance by Watsham and Parramore and in class, it tells us how in empirical data, the series of $x$'s or the matrix with $x$ variables are usually not square, thus not invertible. We just have to work on what was given which are  $\hat{x}$ and  $\hat{y}$.
That's why we have to transform \[ \hat{b} = \hat{x}^{-1}\hat{y} \] into
\[ \hat{b} = (\hat{x}^T \hat{x})^{-1} \hat{x}^T \hat{y}\]
To transform it, identity is used and so is $\hat{x}^T$ as can be seen below.  This is to satisfy my curiosity about the identity.
\[ \begin{array}{rl} b = & x^{-1}y & \\
= & x^{-1}I_n y \\
= & x^{-1}(x x^{-1})^T y \\
= & x^{-1}(x^{-1})^T x^T y \\
= & (x^T x)^{-1} (x^T y)
 \end{array} \]
I looked for other ways to simplify the equation but so far, this is the easiest with the given data.

Application Found

• Regression Analysis (of course) - The measurement of change in one variable (y)  that is the result of changes in other variables (x) . Regression analysis is used frequently in identifying the variables that affect a certain stock's price.
• Hedging or Hedge Ratio (211-212 book)

OLS Macro I

Code lies on matrixOLS.xlsm

Sub olsmaker()
' this solves for regression calling on fixed number of dataset
' Also matrix form is defined by the user.

    'calling for x and y
    x = Range("A105:B114").Value
    y = Range("C105:C114").Value
    
    'solving for b=(xT x)^-1 (xT y)
    xT = Application.WorksheetFunction.Transpose(x)
    xTx = Application.WorksheetFunction.MMult(xT, x)
    xTxinv = Application.WorksheetFunction.MInverse(xTx)
    xTxinvxT = Application.WorksheetFunction.MMult(xTxinv, xT)
    sol = Application.WorksheetFunction.MMult(xTxinvxT, y)

    'returning it back to the sheet
    Range("F106") = "b="
    Range("F107:F108").Value = sol

End Sub

Notes:
1. How do I make it dynamic? Update this program.
2. Review on Matrix and OLS. Work on how did this happened. And is there a way to shorten the form?
 \[ \hat{b} = \hat{x}^{-1}\hat{y} \to \hat{b} = (\hat{x}^T \hat{x})^{-1} \hat{x}^T \hat{y} \]
3. Very interested in programming numerical methods. Add it to the List to program.
4. Read the book: VB for Dummies.

Matrices in OLS

Matrix Multiplication

If $A$ and $B$ is $3$ x $3$ matrix, then $AB$ is also a $3$ x $3$ matrix. However $AB \neq BA$ because matrix multiplication is not commutative.

As an illustration: matrixOLS.xlsm

\[ A =  \begin{bmatrix}
1 & 2 &3 \\
4 & 5 & 6 \\
7 & 8 & 9
\end{bmatrix}
\;\;\;\;\;
B =  \begin{bmatrix}
11 & 12 &13 \\
14 & 15 & 16 \\
17 & 18 & 19
\end{bmatrix}  \]
Then,
\[ \begin{split} AB &= { \begin{bmatrix}
1*11+2*14+3*17 & 1*12+2*15+3*18 & 1*13+2*16+3*19 \\
4*11+5*14+6*17 & 4*12+5*15+6*18 & 4*13+5*16+6*19 \\
7*11+8*14+9*17 & 7*12+8*15+9*15 & 7*13+8*16+9*19
\end{bmatrix} }\\
&=
\begin{bmatrix}
90 & 96 &102 \\
216 & 231 & 246 \\
342 & 366 & 390
\end{bmatrix}
\end{split} \]
and
\[ \begin{split} BA &= { \begin{bmatrix}
11*1+12*4+13*7 & 11*2+12*5+13*8 & 11*3+12*6+13*9 \\
14*1+15*4+16*7 & 14*2+15*5+16*8 & 14*3+15*6+16*9 \\
17*1+18*4+19*7 & 17*2+18*5+19*8 & 17*3+18*6+19*9
\end{bmatrix} }\\
&=
\begin{bmatrix}
150 & 186 &222 \\
186 & 231 & 276 \\
222 & 276 & 330
\end{bmatrix}
\end{split} \]

In Excel, matrix multiplication is easier.
1. Select a $n$ x $n$ where you will put your answer.
2. Use the function =MMULT(1st array, 2nd array) and
3. Type ctrl+shift+enter.

Matrices in OLS: 

 Sample1: on matrixOLS.xlsm

Given: \[ \begin{split} 12&=7a-5b \\
2&=7+9b\end{split}\]
Find: $b$

There are many ways to solve for b. Though the solution we made involves the theoretical concepts on matrices.


Given:
\[ \begin{split} y_1&=a+bx_1 \\
y_2&=a+bx_2\end{split}\]
then in matrix form:
\[ \hat{b} = \begin{bmatrix} a\\b \end{bmatrix} \;\;\;\;\;\;
\hat{y} = \begin{bmatrix} y_1\\y_2 \end{bmatrix} \;\;\;\;\;
\hat{x} = \begin{bmatrix} 1 & x_1\\ 1 & x_2 \end{bmatrix}
\]
and
\[ \hat{y} = \hat{x}\hat{b} \to \hat{b} = \hat{x}^{-1}\hat{y} \]

These are the steps applied to the example:

1. The linear equation was converted into its matrix form.
2. We got $\hat{x}^{-1}$ by using Excel's function =MINVERSE(array)
3. The formula $\hat{b} = \hat{x}^{-1}\hat{y}$ is then applied using the Excel's function =MMULT(array).

Sample2:  matrixOLS.xlsm

Given the following dataset, find $b$.

Solution 1: Matrix

Sample 2 involves a series of $x$'s and $y$'s. If that is the case, solving for $b$ would involve some transformation. 

\[ \hat{b} = \hat{x}^{-1}\hat{y} \to \hat{b} = (\hat{x}^T \hat{x})^{-1} \hat{x}^T \hat{y} \]

The image below shows the solution for Sample2.

Solution 2: Graphing

1. Select the data to regress. $x$ must be on the first column followed by the $y$.
2. Click on "Insert" Ribbon -> "Charts" -> "Scatter" -> "Scatter with only Markers".
3. Right click on one of the data points of the Graph.
4. Select "Add Trendline" or "Format Trendline"
5. Check on the "Display Equation on Graph." The slope is $b$ and the y-intercept is $a$.

Bivariate Case (OLS)

Given
\[ x = x_1,x_2,\cdots, x_n   \,\,\ \text{and}  \,\,\,  y = y_1,y_2,\cdots, y_n\]

We define the optimal minimum or the optimized mininum as:
\[ \text{Opt Min F} = \sum{[y_\text{actual}-f(x)]^2}\]
where
\[ \text{line  } y = f(x)= a +bx\]
Substituting the line $f(x)$ to the optimized minimum $F$, we have
\[ F = \sum{[y-a -bx)]^2}\]
Taking the first derivative with respect to $a$ and $b$,
\[ \begin{split} \frac{\partial F}{\partial a} &= 2 \sum{(y-a-bx)(-1)} = 0\\
\frac{\partial F}{\partial b} &= 2 \sum{(y-a-bx)(-x)} = 0 \end{split}  \]
We can now derive the normal equation for the OLS as:

\[ \begin{split} \sum{y} &= an + b \sum{x}\\
\sum{xy} &= a \sum{x} + b \sum{x^2} \end{split} \]

Solve for $a$ and $b$ to get critical points and find out if they really produced the optimum.