Creating a rolling window Variance Covariance Matrix

Hello

I am having problems creating loops for varcovar matrix as I want it to move through a daily rolling window given: (1:1001) , (2:1002) and so on.

I want to do the same for the linest function as well in order for it to use data in a rolling window

This is in order to create function that uses such rolling windows to forecast day-ahead Variance which is brought by multiplying the vector output from linest by the VarCovar matrix. Could someone please help me build such code?

I have coded the var covar matrix on VBA and the code is below:


Function VarCovar(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
numcols = rng.Columns.Count
numrows = rng.Rows.Count
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)

For i = 1 To numcols
For j = 1 To numcols
matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j)) _
* numrows / (numrows - 1)
Next j
Next i
VarCovar = matrix
End Function