VBA: OLS and SSE

This is a part of our class exercise. Also can be seen in the OLS and SSE excel file. The problem involved:

•  Getting the results of the regression and find the regression estimate per row and compare with the actual, 
• Computing for the error and the squared error
• Displaying the results of regression and the sum of errors in a new worksheet
• Creating a form for that,
• And running the regression program dynamically

I reused and revised the code in the post Autoregression in functions, in particular the  Function OLS() to regress. In addition, I made up a form getting the data dynamically. Although test for errors were not included.

Functions and Subs

• xM(x1) - Function that creates the x matrix
• CommandButton1_Click() -serves as the test button or the Ok button. or also known as my input - output button because it determines what is your input and creates the output.
• CommandButton2_Click() - the cancel button
• OLS(y, x) - the revised OLS function. Its output are the betas
• SSE(y, x) - solves for the SSE of the regressed y,x

Learning

I spent almost half of the class figuring out why the error mmult property cant run keeps popping up. and after a few hours I found out where I got it wrong. The logic of the code is correct but the parameters of the inputs are wrong. So lesson of the day is: Kung tama ang logic, at mali naman sa pinag gamitan. Mali pa rin yun. It works in real life.

The Form

Simple right :)

The Code

Private Sub CommandButton1_Click()
    y = Range(RefEdit1.Text)
    x1 = Range(RefEdit2.Text)
    
    'creating x matrix
    x = xM(x1)
    
    'regress
    param = OLS(y, x)
    
    'output Sheet
    Sheets.Add
    Range("A1") = "Solution Matrix"
    Range("A" & 2) = "Constant"
    For i = 2 To UBound(param)
        Range("A" & 1 + i) = "x" & i - 1
    Next i
    Range("B2").Select
    ActiveCell.Range(Cells(1, 1), Cells(UBound(param), 1)) = param
    Range("D1") = "Sum of Squared Errors"
    Range("D2") = SSE(y, x)
    
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Function xM(x1)
    'adding ones to for coefficients in x matrix
    x = Range(Cells(1, 1), Cells(UBound(x1), UBound(x1, 2) + 1))
    For j = 1 To UBound(x, 2)
        For i = 1 To UBound(x)
            If j = 1 Then
                x(i, j) = 1
            Else
                x(i, j) = x1(i, j - 1)
            End If
        Next i
    Next j
    
    xM = x
End Function
    
Private Function OLS(y, x)
 'regression, getting betas and coefficient
 'given the matrix y and x
    
    xtrans = Application.WorksheetFunction.Transpose(x)
    xtx = Application.WorksheetFunction.MMult(xtrans, x)
    xtxinv = Application.WorksheetFunction.MInverse(xtx)
    xtxinvxt = Application.WorksheetFunction.MMult(xtxinv, xtrans)
    bsol = Application.WorksheetFunction.MMult(xtxinvxt, y)
    OLS = bsol
    
End Function

Private Function SSE(y, x)
    'sum of squared errors
    
    param = OLS(y, x)
    
    model = Application.WorksheetFunction.MMult(x, param)
    
    SumErrors = 0
    For i = 1 To UBound(x)
        SumErrors = SumErrors + (y(i, 1) - model(i, 1)) ^ 2
    Next i
    
    SSE = SumErrors
End Function