Sulprobil
Search…
PF_Allocate
If you need to generate random portfolios with a given total sum and lower boundaries and upper boundaries for each asset, you can take the function PF_Allocate() shown below.
Please read my Disclaimer.
1
Function PF_Allocate(db As Double, _
2
vlb As Variant, _
3
vub As Variant) As Double()
4
'Generate a portfolio of assets x1..xN
5
'x1..xN being random numbers (double) with:
6
'x1+x2+..xN = db 'budget
7
'xi >= vlb(i) 'lower bound vector
8
'xi <= vub(i) 'upper bound vector
9
'Reverse(moc.liborplus.www) V0.11
10
Dim i As Variant, n As Long
11
Dim dcumx As Double
12
Dim dcumlb As Double
13
Dim dcumub As Double
14
Dim dxlb As Double
15
Dim dxub As Double
16
17
'Application.Volatile
18
dcumlb = Application.WorksheetFunction.Sum(vlb)
19
dcumub = Application.WorksheetFunction.Sum(vub)
20
If dcumlb > db Or dcumub < db Then
21
PF_Allocate = CVErr(xlErrValue)
22
Exit Function
23
End If
24
n = vlb.Count
25
ReDim dR(1 To n) As Double
26
dcumx = 0#
27
'For i = 1 To n 'Old biased solution
28
For Each i In VBUniqRandInt(n, n)
29
'http://www.sulprobil.com/html/uniqrandint.html
30
If vlb(i) > vub(i) Then
31
PF_Allocate = CVErr(xlErrValue)
32
Exit Function
33
End If
34
dcumlb = dcumlb - vlb(i)
35
dcumub = dcumub - vub(i)
36
dxlb = db - dcumx - dcumub
37
If dxlb < vlb(i) Then dxlb = vlb(i) 'dxlb = Min(..)
38
dxub = db - dcumx - dcumlb
39
If dxub > vub(i) Then dxub = vub(i) 'dxub = Max(..)
40
dR(i) = dxlb + Rnd() * (dxub - dxlb)
41
dcumx = dcumx + dR(i)
42
Next i
43
PF_Allocate = dR
44
End Function
Copied!
PF_Allocate.xlsm
23KB
Binary
PF_Allocate.xlsm
Last modified 1yr ago
Copy link