VBA UDF to calculate Covariance from Daily Stock prices

Question

Result Screenshot: ERROR:

Sample data Screenshot:

I am new to VBA and right now I am stuck with an error. I want to calculate Covariance by taking stock prices as a parameter which returns a n*n array (n= number of columns of its parameter). I cannot figure out my mistake in the following code:

Function covarmat(prices As Range) As Variant
    Dim i As Integer, j As Integer
    Dim n As Integer

    n = prices.Columns.Count

    Dim resultarray()

    ReDim resultarray(n, n)
    Dim f  'as a proxy for return variant
    Dim basicarray() 'to extract columns and calculate returns
    ReDim basicarray(prices.Rows.Count, prices.Columns.Count)                                               Tried this separately! Works perfect!

    For j = 1 To prices.Columns.Count
        For i = 1 To prices.Rows.Count
            basicarray(i, j) = prices(i + 1, j) / prices(i, j) - 1
        Next i
    Next j
    f = basicarray

    For i = 1 To n
        For j = 1 To n
            resultarray(i, j) = Application.Covariance_S(f.columns(i), f.Columns(j))
        Next j
    Next i

covarmat = resultarray
End Function

Answer 1

This will give you an array of the covarinace_s. So its an array formula. Select range equal to n*n and enter the formula (Ctrl+Shift+Enter).

Function covarmat(prices As Range) As Variant
    Dim i As Integer, j As Integer
    Dim n As Integer

    n = prices.Columns.Count

    Dim resultarray()
    ReDim resultarray(1 To n, 1 To n)

    Dim basicarray()
    ReDim basicarray(1 To prices.Rows.Count - 1, 1 To n)

    For j = 1 To n
        For i = 1 To prices.Rows.Count - 1
            basicarray(i, j) = prices(i + 1, j) / prices(i, j) - 1
        Next i
    Next j

    For i = 1 To n
        For j = 1 To n
            resultarray(i, j) = Application.Covariance_S _
            (Application.WorksheetFunction.Index(basicarray, 0, i), _
            Application.WorksheetFunction.Index(basicarray, 0, j))
        Next j
    Next i

covarmat = resultarray
End Function