Monte carlo simulation Excel
Hello everyone,
I am a beginner in VBA.
I need to create a Monte Carlo simulation on VBA for the Martingale roullete strategy . I have the follow code that, but I want a function that give me the returns (profits in cash) instead of 1 and 2 like appear in excel file. How I do that?
Option Explicit
Function Random(Lower As Integer, Upper As Integer) As Integer
Randomize
Random = Int((Upper - Lower + 1) * Rnd + Lower) ' to produce integer in a given range
End Function
Sub MonteCarlo()
Dim IniMoney As Single, MaxPlays As Integer, StopProfit As Single
Dim ColumnNum As Integer, CurrentProfit As Single, Y As Integer, X As Integer
Dim Result As Integer, Stake As Integer, CumTot As Integer
For X = 7 To 16 'X is the column value
Cells(17, X).Select
ColumnNum = ActiveCell.Column
IniMoney = Sheet1.Cells(18, ColumnNum).Value
MaxPlays = Sheet1.Cells(19, ColumnNum).Value
StopProfit = Sheet1.Cells(20, ColumnNum).Value
Stake = 1
CumTot = IniMoney
For Y = 21 To 21 + MaxPlays ' Y is the row value
Result = Random(1, 2)
Sheet1.Cells(Y, ColumnNum).Value = Result
Select Case Result
Case 1 'WIN
CumTot = CumTot + Stake
Stake = 1
If CumTot = StopProfit Then Exit For
Case 2 'LOSE
CumTot = CumTot - Stake
Stake = Stake * 2
End Select
Next Y
Sheet1.Cells(13, ColumnNum).Value = CumTot
Next X
End Sub
The Excel sheet:
% wins 51% 49% 50% 54% 51% 51% 50% 50% 52% 53%
Average 1,480392 1,509804 1,490196 1,45098 1,490099 1,485149 1,5 1,5 1,480198 1,465347
Stdev 0,502272 0,502272 0,502469 0,500495 0,501193 0,501093 0,500499 0,500499 0,500944 0,500199
Ini Money 100 100 200 200 200 200 500 500 500 500
Max Plays 100 100 100 100 200 200 500 500 200 200
Stop Profit 25 50 25 50 25 50 25 50 50 100
1 2 1 2 2 1 2 2 2 2 2
2 2 1 1 1 2 2 2 1 1 1
3 1 1 2 1 1 1 2 2 2 2
4 2 1 2 2 2 1 2 1 2 1
5 2 2 2 1 1 1 1 2 1 2
6 2 1 1 2 2 1 2 2 2 2
7 1 1 1 2 2 1 1 1 2 2
8 2 2 2 1 1 1 1 2 2 2
9 1 2 1 1 1 2 2 2 2 2
10 2 1 2 1 1 1 1 1 2 1
11 2 2 1 1 2 1 2 2 1 1
12 1 1 1 1 2 1 2 1 1 2
13 1 1 2 1 2 1 2 2 2 2
14 2 1 1 2 2 1 2 2 1 1
15 2 1 2 1 1 1 1 2 2 1
16 2 2 1 2 2 2 2 1 1 1
17 1 1 1 2 1 1 1 2 2 2
18 2 2 2 2 2 2 1 1 2 1
19 1 1 2 1 1 2 1 1 1 2
20 2 1 2 1 1 1 1 1 1 1
21 2 1 1 2 2 2 1 1 2 2
22 2 2 1 1 1 1 1 1 2 2
23 1 2 2 1 1 1 2 1 1 2
24 2 1 1 2 2 1 2 1 1 1
25 1 2 1 2 1 2 2 1 2 1
26 2 1 1 1 2 1 1 2 2 1
27 1 1 2 1 1 1 2 2 1 1
28 1 1 2 1 2 2 1 1 2 2
29 1 2 1 1 2 1 2 1 1 2
30 1 2 1 1 2 1 2 2 1 2
31 1 1 2 2 2 2 1 1 2 2
32 2 2 1 2 1 2 2 1 2 2
33 1 2 1 2 2 2 2 1 1 2
34 1 2 2 1 1 2 1 2 1 1
35 1 2 1 2 2 1 2 2 2 1
36 1 1 1 2 1 1 2 2 1 2
37 2 1 2 2 1 1 2 2 2 2
38 2 1 1 2 2 1 2 2 1 2
39 2 1 1 2 2 1 2 1 2 2
40 1 1 1 2 2 2 1 2 2 2
41 2 2 1 2 1 2 1 2 2 1
42 2 2 1 1 1 2 1 1 2 1
43 1 2 2 1 1 2 2 2 2 2
44 2 2 1 2 1 1 1 1 1 2
45 1 2 2 1 1 1 2 1 1 1
46 1 2 2 1 2 1 1 2 1 1
47 2 2 1 2 2 1 1 2 1 1
48 1 2 2 1 1 1 1 2 2 2
49 1 2 1 1 2 1 2 2 2 2
50 1 2 2 1 2 2 1 2 1 1
51 2 1 1 1 1 2 1 1 2 1
52 1 1 1 2 2 1 1 1 2 1
53 2 1 1 1 1 2 2 1 1 1
54 1 2 2 2 1 1 1 2 1 2
55 2 2 2 1 1 2 1 2 1 2
56 2 2 1 2 2 2 2 2 1 2
57 1 2 1 1 2 2 1 1 1 1
58 2 1 1 1 2 2 1 1 2 2
59 2 1 2 2 2 2 1 1 2 2
60 2 2 1 1 2 1 2 2 2 2
61 1 2 1 2 1 2 1 1 2 2
62 1 1 1 1 2 2 1 2 1 2
63 1 1 2 1 2 1 2 2 1 2
64 1 2 2 2 2 1 2 1 1 1
65 1 2 2 1 2 1 1 2 2 2
66 2 1 2 1 2 1 1 2 2 2
67 1 1 2 1 2 2 1 1 2 1
68 1 1 1 2 1 1 2 2 1 1
69 1 2 2 2 2 1 1 2 1 1
70 2 1 1 2 2 2 2 2 1 1
71 2 2 2 1 2 1 1 1 1 1
72 1 1 1 2 2 1 1 1 1 1
73 1 2 2 1 1 1 2 2 2 1
74 2 2 2 1 2 2 1 1 1 1
75 2 1 1 1 1 2 1 2 1 1
76 2 2 1 2 1 2 1 1 2 2
77 1 1 2 1 1 1 2 2 2 1
78 2 2 1 1 1 2 1 2 2 2
79 1 2 2 2 1 1 2 1 2 1
80 2 1 1 1 1 2 1 2 1 1
81 2 1 2 1 2 2 2 1 2 2
82 1 1 2 1 2 1 1 2 2 1
83 1 1 2 2 1 2 1 2 1 1
84 1 1 2 2 1 2 2 1 1 1
85 1 1 1 2 2 2 2 2 2 1
86 1 2 1 2 2 1 2 2 2 2
87 1 2 2 2 1 1 1 2 1 1
88 2 2 2 2 2 2 1 2 1 1
89 2 2 2 2 1 2 1 2 1 1
90 2 1 2 2 1 2 2 1 2 1
91 1 2 2 1 2 2 1 1 1 1
92 2 1 1 1 1 2 1 2 2 2
93 2 2 2 1 1 2 2 2 1 1
94 1 2 2 2 2 1 1 2 1 2
95 2 1 1 1 1 1 2 2 2 1
96 2 2 1 1 2 1 2 2 1 1
97 1 2 2 2 1 1 2 1 2 2
98 1 2 1 2 2 2 2 2 1 2
99 1 2 2 1 1 1 1 1 2 2
100 2 1 2 1 1 2 1 1 1 1
101 1 2 1 1 2 1 1 2 2 1
102 1 1 1 2 1 2
103 1 2 1 2 2 1
104 1 2 2 2 1 1
105 2 1 2 2 1 2
106 2 1 2 2 2 1
107 2 1 1 1 2 2
108 1 2 2 2 2 2
109 2 2 1 2 1 1
110 1 2 2 1 1 1
111 2 2 2 1 1 2
112 1 2 2 1 2 1
113 1 1 1 1 1 1
114 1 1 2 1 1 1
115 2 1 2 2 1 1
116 1 2 2 2 1 1
117 1 1 1 2 2 1
118 2 1 2 1 2 2
119 2 1 2 1 1 2
120 2 1 1 1 2 2
121 2 2 2 2 1 2
122 1 2 2 2 2 2
123 1 1 1 2 2 2
124 1 2 2 1 2 2
125 2 1 2 2 2 2
126 1 1 2 1 2 2
127 2 2 1 1 1 1
128 1 2 1 2 1 1
129 1 1 1 2 2 1
130 1 2 2 2 1 2
131 1 1 2 2 2 1
132 2 2 1 1 1 1
133 1 2 2 1 1 2
134 2 1 1 2 1 1
135 1 1 2 1 2 1
136 1 1 2 2 1 1
137 2 1 1 1 2 1
138 1 1 2 2 1 2
139 1 2 2 1 1 1
140 2 2 2 2 2 2
141 2 1 1 1 2 1
142 1 2 2 1 1 2
143 2 2 2 2 2 1
144 2 1 2 1 1 1
145 1 2 2 2 2 2
146 1 2 1 1 1 1
147 2 1 2 2 1 2
148 1 1 1 2 1 1
149 1 1 2 1 2 1
150 1 2 2 1 2 2
151 2 2 2 1 1 1
152 1 2 2 1 1 1
153 2 1 2 2 1 1
154 2 2 1 1 2 1
155 1 2 1 1 2 1
156 1 1 2 2 2 2
157 1 2 1 2 1 1
158 2 2 1 2 1 2
159 1 2 1 1 2 1
160 1 1 2 1 1 1
161 2 1 1 1 1 1
162 2 1 1 2 2 2
163 1 2 2 1 1 2
164 1 1 2
|
