Abstract
Mit der Cholesky (sprich: “Koleski”) Zerlegung können Sie einfach korrelierte Zufallszahlen erzeugen. Ich stelle hier ein einfaches Beispiel vor. Den besseren Iman Conover Ansatz finden Sie hier.
Appendix – Programmcode Cholesky und RandCorr
Bitte den Haftungsausschluss im Impressum beachten.
Function Cholesky(r As Range) As Variant
'I suggest to use the Cholesky decomposition just for purposes of demonstration.
'Better options are (in this order): tred2, tqli, eigsrt from Numerical Recipes.
'SVD also works but is computationally more expensive by far since it does not
'make use of symmetry. (Thanks to Glen R.)
'Source (EN): http://www.sulprobil.de/cholesky_en/
'Source (DE): http://www.berndplumhoff.de/cholesky_de/
'Bernd Plumhoff 22-Nov-2019 PB V1.0
Dim vA As Variant
Dim d As Double
Dim i As Long, j As Long, k As Long, n As Long
vA = r
n = r.Rows.Count
If n <> r.Columns.Count Then
Cholesky = CVErr(xlErrRef)
Exit Function
End If
ReDim vR(1 To n, 1 To n) As Variant
For j = 1 To n
d = 0#
For k = 1 To j - 1
d = d + vR(j, k) * vR(j, k)
Next k
vR(j, j) = vA(j, j) - d
If vR(j, j) > 0# Then
vR(j, j) = Sqr(vR(j, j))
For i = j + 1 To n
d = 0#
For k = 1 To j - 1
d = d + vR(i, k) * vR(j, k)
Next k
vR(i, j) = (vA(i, j) - d) / vR(j, j)
Next i
Else
'Cannot continue with usual Cholesky
'Fill this column with zeros. Idea: Glen R.
For i = j To n
vR(i, j) = 0#
Next i
End If
Next j
Cholesky = vR
End Function
Function RandCorr(n As Long, vVarCovar As Variant) As Variant
'Returns Ubound(vVarCovar,1) correlated random number vectors of length n.
'vVarCovar is a square matrix containing the variance/covariance matrix.
'Please notice that you will only get a "proxy" correlation, not an exact one.
'Bernd Plumhoff 06-Nov-2009 PB V0.2
Dim vA As Variant
Dim d As Double
Dim i As Long, j As Long, k As Long, m As Long
With Application.WorksheetFunction
vA = .Transpose(.Transpose(vVarCovar))
m = UBound(vA, 1)
If m <> UBound(vA, 2) Then
RandCorr = CVErr(xlErrRef)
Exit Function
End If
ReDim Db(1 To m, 1 To m) As Double
For j = 1 To m
d = 0#
For k = 1 To j - 1
d = d + Db(j, k) * Db(j, k)
Next k
Db(j, j) = vA(j, j) - d
If Db(j, j) <= 0 Then
RandCorr = CVErr(xlErrNum)
Exit Function
End If
Db(j, j) = Sqr(Db(j, j))
For i = j + 1 To m
d = 0#
For k = 1 To j - 1
d = d + Db(i, k) * Db(j, k)
Next k
Db(i, j) = (vA(i, j) - d) / Db(j, j)
Next i
Next j
ReDim vR(1 To n, 1 To m) As Variant
For i = 1 To n
For j = 1 To m
vR(i, j) = .Norm_S_Inv(Rnd())
Next j
Next i
vR = .MMult(vR, Db)
RandCorr = vR
End With
End Function
Downlaod
Bitte den Haftungsausschluss im Impressum beachten.
Cholesky.xlsm [273 KB Excel Datei, ohne jegliche Gewährleistung]