Cholesky
You can generate correlated random variables easily with a Cholesky (pronounce “koleski”) decomposition. I present a simple example here. For the better Iman Conover approach look here, please.
A helpful links is: Team Latte: All about the Cholesky Matrix - Nice explanation and why it's "preferrable" to eigenvalues (I do not necessarily agree) 1
Function Cholesky(r As Range) As Variant
2
'I suggest to use the Cholesky decomposition just for purposes of demonstration.
3
'Better options are (in this order): tred2, tqli, eigsrt from Numerical Recipes.
4
'SVD also works but is computationally more expensive by far since it does not
5
'make use of symmetry.
6
'(Thanks to my friend and former colleague Glen R.) Bernd Plumhoff 22-Sep-2019
7
Dim vA As Variant
8
Dim d As Double
9
Dim i As Long, j As Long, k As Long, n As Long
10
vA = r
11
n = r.Rows.Count
12
If n <> r.Columns.Count Then
13
Cholesky = CVErr(xlErrRef)
14
Exit Function
15
End If
16
17
ReDim vR(1 To n, 1 To n) As Variant
18
For j = 1 To n
19
d = 0#
20
For k = 1 To j - 1
21
d = d + vR(j, k) * vR(j, k)
22
Next k
23
vR(j, j) = vA(j, j) - d
24
If vR(j, j) > 0# Then
25
vR(j, j) = Sqr(vR(j, j))
26
For i = j + 1 To n
27
d = 0#
28
For k = 1 To j - 1
29
d = d + vR(i, k) * vR(j, k)
30
Next k
31
vR(i, j) = (vA(i, j) - d) / vR(j, j)
32
Next i
33
Else
34
'Cannot continue with usual Cholesky
35
'Fill this column with zeros
36
For i = j To n
37
vR(i, j) = 0#
38
Next i
39
End If
40
Next j
41
Cholesky = vR
42
End Function
Copied!
1
Function RandCorr(n As Long, vVarCovar As Variant) As Variant
2
'Returns Ubound(vVarCovar,1) correlated random number vectors of length n.
3
'vVarCovar is a square matrix containing the variance/covariance matrix.
4
'Please notice that you will only get a "proxy" correlation, not an exact one.
5
'Reverse("moc.LiborPlus.www") V0.2 PB 06-Nov-2009
6
Dim vA As Variant
7
Dim d As Double
8
Dim i As Long, j As Long, k As Long, m As Long
9
10
With Application.WorksheetFunction
11
vA = .Transpose(.Transpose(vVarCovar))
12
m = UBound(vA, 1)
13
If m <> UBound(vA, 2) Then
14
RandCorr = CVErr(xlErrRef)
15
Exit Function
16
End If
17
18
ReDim Db(1 To m, 1 To m) As Double
19
For j = 1 To m
20
d = 0#
21
For k = 1 To j - 1
22
d = d + Db(j, k) * Db(j, k)
23
Next k
24
Db(j, j) = vA(j, j) - d
25
If Db(j, j) <= 0 Then
26
RandCorr = CVErr(xlErrNum)
27
Exit Function
28
End If
29
Db(j, j) = Sqr(Db(j, j))
30
31
For i = j + 1 To m
32
d = 0#
33
For k = 1 To j - 1
34
d = d + Db(i, k) * Db(j, k)
35
Next k
36
Db(i, j) = (vA(i, j) - d) / Db(j, j)
37
Next i
38
Next j
39
40
ReDim vR(1 To n, 1 To m) As Variant
41
For i = 1 To n
42
For j = 1 To m
43
vR(i, j) = .NormSInv(Rnd())
44
Next j
45
Next i
46
vR = .MMult(vR, Db)
47
RandCorr = vR
48
End With
49
End Function
Copied!
Cholesky.xlsm
3MB
Binary