Can this Simulator be done by Excel?

[scalpmaster]

BreakThrough In Coin Toss System
<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1><!-- / icon and title --><!-- message -->Hi all,

appreciate if anyone can create this excel spreadsheet simulation:

User input:

T= Total number of tosses, e.g. T=1000

W=Winning probability% of each toss e.g. 0.5 (= fair)

N = number of latest tosses without the occurrence of 2 consecutive Heads or Tails e.g, N=7

e.g, For bets on Heads (vice-versa for Tails),

If there is No occurrence of 2 Consecutive Heads within the last N=7 tosses, then on the next appearance of a Head (could be one or more tosses after N), place first bet 1 on the next toss on Heads.

If Heads appears on next toss, +1 is added to Balance Total(cycle ends), otherwise, 1 is subtracted.
If Tails subsequently continues to appear, do not place next bet until the next Head appears.

When that Heads appears, place bet 2 on the next toss on Heads.
If Heads appears on next toss, +2 is added to Balance Total(cycle ends), otherwise, 2 is subtracted.
If Tails subsequently continues to appear, do not place next bet until the next Head appears.

When that Heads appears, place bet 4 on the next toss on Heads.
If Heads appears on next toss, +4 is added to Balance Total, otherwise, 4 is subtracted.
CYCLE ENDs here regardless it is a win or loss.(Limited martingale)

Wait for the next Non-occurrence of 2 Consecutive Heads or Tails within the latest N=7draws to start the new cycle.

Plot Balance Total chart over T number of tosses.

Thanks,
Scalpmaster
<!-- / message -->

 

[scalpmaster]

In using trading systems, we may manage risk by NOT compounding the returns in One particular system non-stop. Say we have an aggressive automated system with monthly returns of >50% ... we will take out 50% of net profit for the month when bal. total falls below MA and put it in cash, other trading systems or investing strategies... In case the system implodes, we want to increase our chance of capital preservation...

 

[xenou]

The MA value calculation itself based on average bal of last 20 Toss, not 1-19 games.
The moving average Stop Run on balancetotal is another form of risk management for trading systems...Adding independent runs' net P/L with MA StopRun each time can have a different effect than running it non-stop infinitely since the objective is to protect capital and gains first, take a break,re-evaluate then go again...the best way to know is to test it out and compare.
Yes, there should be an initial stop run level (say 15% of $1000) in case bal. line drops below MA at the begining...

I'll post my simulation but it would be interesting if you could put this in layman's terms.

As far as moving average on tosses, it seems to me they fluctuate up and down so you'd quickly cross your MA line, and probably never get to the "bet". This is a little confusing now that a toin coss game has turned into a risk management model for trading. I was working on post #1 the past few days - you've taken this into a lot of new territory.

Below are some simulations of MA based on tosses - see how they go up and down rather erratically at first, but after about 30 trials the average stabilizes with changes of +.01 or -.01 -- is that what you expect? I'm not sure I see how this could fit into this simulation as the early "tosses" will quickly misfire on the erratic averages, and with more "tosses" you just don't get much change on the moving averages - just a point up or down. You have to explain this to me like I don't know anything about risk management models (actually, that's the case here).

<pre>0.50 n = 02 0.00 n = 02 0.50 n = 02
0.33 n = 03 0.00 n = 03 0.67 n = 03
0.25 n = 04 0.25 n = 04 0.75 n = 04
0.20 n = 05 0.40 n = 05 0.80 n = 05
0.17 n = 06 0.33 n = 06 0.83 n = 06
0.29 n = 07 0.29 n = 07 0.71 n = 07
0.25 n = 08 0.25 n = 08 0.62 n = 08
0.33 n = 09 0.22 n = 09 0.56 n = 09
0.40 n = 10 0.30 n = 10 0.50 n = 10
0.45 n = 11 0.27 n = 11 0.45 n = 11
0.42 n = 12 0.25 n = 12 0.50 n = 12
0.46 n = 13 0.31 n = 13 0.46 n = 13
0.43 n = 14 0.29 n = 14 0.43 n = 14
0.40 n = 15 0.27 n = 15 0.47 n = 15
0.44 n = 16 0.31 n = 16 0.50 n = 16
0.41 n = 17 0.29 n = 17 0.47 n = 17
0.44 n = 18 0.33 n = 18 0.50 n = 18
0.42 n = 19 0.32 n = 19 0.53 n = 19
0.45 n = 20 0.35 n = 20 0.50 n = 20
0.43 n = 21 0.33 n = 21 0.52 n = 21
0.41 n = 22 0.36 n = 22 0.50 n = 22
0.43 n = 23 0.35 n = 23 0.52 n = 23
0.42 n = 24 0.38 n = 24 0.54 n = 24
0.44 n = 25 0.40 n = 25 0.52 n = 25
0.46 n = 26 0.42 n = 26 0.50 n = 26
0.44 n = 27 0.44 n = 27 0.48 n = 27
0.43 n = 28 0.46 n = 28 0.46 n = 28
0.41 n = 29 0.45 n = 29 0.45 n = 29
0.43 n = 30 0.43 n = 30 0.43 n = 30
0.45 n = 31 0.42 n = 31 0.42 n = 31
0.44 n = 32 0.44 n = 32 0.41 n = 32
0.45 n = 33 0.45 n = 33 0.39 n = 33
0.47 n = 34 0.47 n = 34 0.41 n = 34
0.46 n = 35 0.49 n = 35 0.43 n = 35
0.44 n = 36 0.50 n = 36 0.42 n = 36
0.46 n = 37 0.51 n = 37 0.41 n = 37
0.45 n = 38 0.50 n = 38 0.39 n = 38
0.46 n = 39 0.51 n = 39 0.41 n = 39
0.48 n = 40 0.50 n = 40 0.42 n = 40
0.49 n = 41 0.51 n = 41 0.41 n = 41
0.50 n = 42 0.50 n = 42 0.40 n = 42
0.49 n = 43 0.51 n = 43 0.40 n = 43
0.48 n = 44 0.52 n = 44 0.41 n = 44
0.49 n = 45 0.53 n = 45 0.42 n = 45
0.48 n = 46 0.52 n = 46 0.41 n = 46
0.47 n = 47 0.51 n = 47 0.40 n = 47
0.48 n = 48 0.52 n = 48 0.42 n = 48
0.47 n = 49 0.51 n = 49 0.41 n = 49
0.46 n = 50 0.52 n = 50 0.42 n = 50

</pre>

 

[xenou]

Actually, I'm probably missing the "moving part". But if you look at n = 20 in the above three trials, you can see that with the next toss (n = 21) you either bring the moving average up or down so you would probably be able to "end the game there" as you are saying.

I'm not sure it would change the results of the simulation much - it's probably fifty-fifty still over the long run as you'll have as many cases that "end" the game as "continue" the game, and over time you'll have many values for the p/l at that point - basically, the same distribution, just at different ending point for a individual game.

 

[xenou]

I got a little lazy towards the end - the code below keeps output in an array but I just pushed it all to a worksheet (which surely slows it down). This was set up as if someone played each night for a year - so the final result is their balance at the end of the year (365 games). This was repeated 10000 times. (Note to VB enthusiasts: the flowchart in post #13 will help to clarify the structure of the simulation as a vb program).

The results were about as expected but actually do seem to lean just slightly towards the positive (win) side ... unless I've misread the results.
Below is a graph of the results, the code, and the frequency in bins of 10.

It is my belief that this is basically just a simple exercise conducting a binary probability experiment. It is an answer to post #1, and nothing more. Although (to speak to the topic of creating trading scenarios) I'm aware that monte carlo methods are used in risk simulation of different kinds, I'm hard pressed to see this as anything but a mere step in that direction - it would be interesting to follow the trail further along but alas this is hardly the place for such a complex task).

<img alt="histogram" src="http://northernocean.net/etc/mrexcel/20101024_hg.png" />

[COLOR="Navy"]Option Explicit[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Balance [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Bet [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Byte[/COLOR]
[COLOR="Navy"]Dim[/COLOR] L7(6) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Byte[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Results() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] dummy [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

[COLOR="Navy"]Sub[/COLOR] RunSim_Repeat()
[COLOR="SeaGreen"]'//Play the game for 10,000 years, starting over with $1000 at the start of each year[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 10000
        [COLOR="Navy"]Call[/COLOR] RunSim
        DoEvents
    [COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] RunSim()
[COLOR="SeaGreen"]'//This was set up as a "play one game each night for a year" simulation[/COLOR]
[COLOR="SeaGreen"]'//The result is the players bank balance at the end of the year[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] Long, j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Simulation_Tally
[COLOR="Navy"]Dim[/COLOR] Number_Trials [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    
    [COLOR="SeaGreen"]'//Reset all module variables[/COLOR]
    Bet = 0
    Balance = 0
    [COLOR="Navy"]Erase[/COLOR] L7
    [COLOR="Navy"]Erase[/COLOR] Results
    n = 0

    Balance = 1000
    Sheet1.Cells(1, 1).EntireColumn.Clear
    Number_Trials = 365
    [COLOR="Navy"]ReDim[/COLOR] Results(1 [COLOR="Navy"]To[/COLOR] Number_Trials, 1 [COLOR="Navy"]To[/COLOR] 2)
    [COLOR="Navy"]ReDim[/COLOR] Simulation_Tally(1 [COLOR="Navy"]To[/COLOR] Number_Trials)
    [COLOR="Navy"]For[/COLOR] j = 1 [COLOR="Navy"]To[/COLOR] 1
        [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 365
            n = n + 1
            [COLOR="Navy"]Call[/COLOR] Simulation
            Simulation_Tally(n) = Balance
        [COLOR="Navy"]Next[/COLOR] i
    [COLOR="Navy"]Next[/COLOR] j
    Sheet1.Cells(1, 1).Resize(Number_Trials, 1).Value = WorksheetFunction.Transpose(Simulation_Tally)
    Sheet1.Cells(Rows.Count, 10).End(xlUp).Offset(1, 0).Value = Sheet1.Cells(365, 1)
    Sheet1.Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).Value = Sheet1.Cells(2, 4) [COLOR="SeaGreen"]'I had some avg, min, max sorts of formulas in Column D[/COLOR]
    Sheet1.Cells(Rows.Count, 12).End(xlUp).Offset(1, 0).Value = Sheet1.Cells(3, 4)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Function[/COLOR] Simulation()
[COLOR="SeaGreen"]'//Start Simulation[/COLOR]
[COLOR="Navy"]Dim[/COLOR] bytCount [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] Long, y [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Balance_Start [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Balance_End [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Static[/COLOR] blnRandomized [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]

    Balance_Start = Balance
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] blnRandomized [COLOR="Navy"]Then[/COLOR]
        Randomize
        blnRandomized = True
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
    bytCount = 0
    [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] bytCount < 5
        L7(bytCount) = Flip
        bytCount = bytCount + 1
    [COLOR="Navy"]Loop[/COLOR]
    
    [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] Two_Sequential_Heads()
        Pop_Flip
    [COLOR="Navy"]Loop[/COLOR]
    Bet = 1
    [COLOR="Navy"]Call[/COLOR] Martingale(Bet)
    Balance_End = Balance
        
    [COLOR="Navy"]Call[/COLOR] Report_Output(Balance_Start, Balance_End)
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

[COLOR="Navy"]Sub[/COLOR] Martingale(Bet [COLOR="Navy"]As[/COLOR] Byte)

    dummy = 1
    [COLOR="SeaGreen"]'Debug.Print Balance[/COLOR]
    [COLOR="Navy"]If[/COLOR] Flip() [COLOR="Navy"]Then[/COLOR]
        Balance = Balance + Bet
    [COLOR="Navy"]Else[/COLOR]
        Balance = Balance - Bet
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="SeaGreen"]'Debug.Print Balance[/COLOR]
    
    [COLOR="Navy"]If[/COLOR] Bet = 4 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
    [COLOR="Navy"]Else[/COLOR]
        Bet = Bet * 2
        [COLOR="Navy"]Do[/COLOR]
            [COLOR="Navy"]If[/COLOR] Flip() [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Do[/COLOR]
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]Loop[/COLOR]
        [COLOR="Navy"]Call[/COLOR] Martingale(Bet)
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Function[/COLOR] Two_Sequential_Heads() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Byte[/COLOR]
    Two_Sequential_Heads = False
    [COLOR="SeaGreen"]'Debug.Print "---"[/COLOR]
    [COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] 5
        [COLOR="SeaGreen"]'Debug.Print L7(i)[/COLOR]
        [COLOR="Navy"]If[/COLOR] L7(i) [COLOR="Navy"]And[/COLOR] L7(i + 1) [COLOR="Navy"]Then[/COLOR]
            Two_Sequential_Heads = True
            [COLOR="SeaGreen"]'Exit Function[/COLOR]
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Next[/COLOR] i
    [COLOR="SeaGreen"]'Debug.Print L7(6)[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

[COLOR="Navy"]Sub[/COLOR] Pop_Flip()
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Byte[/COLOR]
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 6
        L7(i - 1) = L7(i)
    [COLOR="Navy"]Next[/COLOR] i
    L7(6) = Flip
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Function[/COLOR] Flip() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Byte[/COLOR]
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Double[/COLOR]
    x = Rnd()
    [COLOR="Navy"]If[/COLOR] x < 0.5 [COLOR="Navy"]Then[/COLOR]
        Flip = 0
    [COLOR="Navy"]Else[/COLOR]
        Flip = 1
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

[COLOR="Navy"]Sub[/COLOR] Report_Output(BegBal [COLOR="Navy"]As[/COLOR] Long, EndBal [COLOR="Navy"]As[/COLOR] Long)
    dummy = 1
    Results(n, 1) = EndBal
    Results(n, 2) = EndBal - BegBal
    [COLOR="SeaGreen"]'Debug.Print Results(i, 1) & " | " & Results(i, 2)[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Bin Frequency
650 0
660 0
670 0
680 0
690 0
700 1
710 0
720 3
730 7
740 4
750 4
760 7
770 12
780 14
790 25
800 42
810 40
820 45
830 49
840 74
850 83
860 117
870 151
880 176
890 200
900 195
910 249
920 298
930 298
940 359
950 362
960 408
970 424
980 456
990 467
1000 446
1010 470
1020 416
1030 400
1040 429
1050 386
1060 337
1070 387
1080 339
1090 284
1100 238
1110 208
1120 213
1130 161
1140 142
1150 108
1160 91
1170 86
1180 70
1190 65
1200 45
1210 36
1220 11
1230 15
1240 20
1250 5
1260 7
1270 8
1280 4
1290 2
1300 1
More 0

 

[scalpmaster]

Ok, case closed. Thanks for your nice analysis and time spent. The code above is new to me.Can you upload the spreadsheet so that everyone can have "fun" with it?

 

[xenou]

I'm afraid you will be sorely disappointed. The "spreadsheet" is little more than a list of number, output from the program.

<a href="http://northernocean.net/etc/mrexcel/20101025_lm.zip">sample workbook</a>