VBA实现数组协方差矩阵函数报错#VALUE问题求助

Let's break down the issues in your code and fix them to get the correct covariance matrix for your stock returns:

Key Issues in the Original Code #

• Incorrect Return Array Assignment: You were overwriting the entire ReturnArr array with a single value instead of assigning to individual elements.
• Misconfigured Temporary Arrays: Your TempArr and TempArr2 had wrong dimensions, and you were looping over the wrong range when populating them. Plus, you were recalculating covariance on every row instead of using the full return series.
• Redundant Object: You declared a WF object but didn't use it (we'll clean that up too).

Corrected Code #

Function VarCovArr(RNG As Range) As Variant
    Dim Arr() As Variant
    Dim OutputArr() As Double
    Dim ReturnArr() As Double
    Dim TempArr1() As Double
    Dim TempArr2() As Double
    Dim i As Long, j As Long, k As Long
    
    ' Convert input range to a 2D array
    Arr = RNG.Value
    
    ' Initialize return array (one less row than input, same number of columns)
    ReDim ReturnArr(1 To UBound(Arr, 1) - 1, 1 To UBound(Arr, 2))
    
    ' Calculate arithmetic returns: (Current Price / Previous Price) - 1
    For i = 1 To UBound(ReturnArr, 1)
        For j = 1 To UBound(ReturnArr, 2)
            ReturnArr(i, j) = Arr(i + 1, j) / Arr(i, j) - 1
        Next j
    Next i
    
    ' Initialize covariance matrix (n x n where n is number of stocks)
    ReDim OutputArr(1 To UBound(Arr, 2), 1 To UBound(Arr, 2))
    
    ' Calculate covariance between each pair of stocks
    For i = 1 To UBound(OutputArr, 1)
        For j = 1 To UBound(OutputArr, 2)
            ' Populate temporary arrays with return series for stock i and stock j
            ReDim TempArr1(1 To UBound(ReturnArr, 1))
            ReDim TempArr2(1 To UBound(ReturnArr, 1))
            
            For k = 1 To UBound(ReturnArr, 1)
                TempArr1(k) = ReturnArr(k, i)
                TempArr2(k) = ReturnArr(k, j)
            Next k
            
            ' Use sample covariance (Covariance_S) to fill the matrix
            OutputArr(i, j) = Application.WorksheetFunction.Covariance_S(TempArr1, TempArr2)
        Next j
    Next i
    
    ' Return the covariance matrix
    VarCovArr = OutputArr
End Function

How to Use #

1. Select a range of cells that matches the size of your covariance matrix (if you have 5 stocks, select a 5x5 range).
2. Enter =VarCovArr(YourPriceRange) where YourPriceRange is the range containing your 5 stocks' daily prices.
3. Press Ctrl+Shift+Enter to enter it as an array formula (this is crucial for returning the full matrix).

Notes #

• The function uses Covariance_S (sample covariance) which divides by n-1. If you need population covariance, replace it with Covariance_P.
• Ensure your input price range has no blank cells or non-numeric values—these will cause errors.

内容的提问来源于stack exchange，提问作者Tosh