The AR Function only calls for the original prices and the number of lags we wanted. Inside that function, the y and the x values are created. And then we throw those values to the OLS function where the regression is made.
The function OLS y and x values as inputs. Since y is now a matrix, we are left with creating the x matrix in such a way that the first columns are 1's. The output would be the betas and coefficient of the model.
It was done as such, so that the OLS(y,x) can function independently. AR(orig,lag) however relies to the OLS function as to how I understood the concept.
The code is written below. It can also be seen in AR functions.xlsm
Sub test()
'calling for the range and lag values
Dim ran As String
ran = Range("J3")
lag = Range("J4")
If lag < 1 Or lag Like "[A-Z,a-z]" Then MsgBox "Wrong Lag"
orig = Range(ran)
'output in the excel
Range("H6") = "b="
Range("I6").Select
ActiveCell.Range(Cells(1, 1), Cells(lag + 1, 1)) = AR(orig, lag)
End Sub
Function AR(orig, lag)
'given original series and number of lag, we apply AR
'creating y values
r = UBound(orig) - lag
y = Range(Cells(1, 1), Cells(r, 1))
For i = 1 To r
y(i, 1) = orig(i + lag, 1)
Next i
'creating x values
x1 = Range(Cells(1, 1), Cells(r, lag))
For j = 1 To lag
For i = 1 To r
x1(i, j) = orig(i + lag - j, 1)
Next i
Next j
AR = OLS(y, x1)
End Function
Function OLS(y, x1)
'regression, getting betas and coefficient
'given values y and x (not the matrix x)
'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
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
