VBA: Autoregression and OLS in functions

The code calls for the range of the original series in one cell and the number of lags to the other cell.  It is assumed that the range of the original series is correct.(Meaning there is no checker for the input of the orig series, but for the lag, there is..) Then we wanted to do autoregression only with this given data.

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