'Selecting specitif data in a matrix VBA
I need to calculate the average correlation among stocks whose correlations are equal to and between 15% (LB) and 85% (UB).
I wrote this function, but the "For Each (Cell...)" doesn't work and I don't find another way to do it. Any suggestion?
Function AvgRhoBounded(RET, LB, UB)
n = RET.Columns.Count 'get number of assets
total_rho = 0
n_rho = 0
For i = 1 To n
For j = i + 1 To n
For Each (Cell>=LB and Cell <=UB) in RET
rho_ij = Application.WorksheetFunction.Correl(RET.Columns(i), RET.Columns(j))
total_rho = total_rho + rho_ij
n_rho = n_rho + 1
Next j
Next i
AvgRho = total_rho / n_rho 'returns average correlation
End Function
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
