I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

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.

Helpful links are:
Anton Theunissen: BUS 864 Computing Notes  - Here I got a simple VBA code from (see below my slightly changed version)
Team Latte: All about the Cholesky Matrix      - Nice explanation and why it's "preferrable" to eigenvalues (I do not necessarily agree)
Michael H. Press: Numerical Recipes               - Use algorithms tred2 and tqli if you need to treat the matter more seriously

20091110_PB_01_Cholesky

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 my friend and former colleague Glen R.) Bernd Plumhoff 22-Sep-2019
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
        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.
'Reverse("moc.LiborPlus.www") V0.2 PB 06-Nov-2009
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) = .NormSInv(Rnd())
    Next j
Next i
vR = .MMult(vR, Db)
RandCorr = vR
End With
End Function

If you are interested in downloading a 1.587 MB Excel 2013 sample file go to my Download page, please.

Sulprobil   Get it done   Contact   Disclaimer   Impressum   Download