Roulette Simulator

I made some ealier posts about a graduate statistics project Im working on, this is a contination.

So I've written an excel simulator that uses a random number generator to mimic the roulette wheel (european roulette)
The program currently runs a martingale simulation and ends when the bettor goes broke.

So far, in my simulations (i've set n = 10000 spins), I see than as the bettor approaches more spins, the profit approaches zero.

This makes sense (i think) because if you can make it to say 1000 spins then you must have gained some significant profit because you havent busted yet. But I was under the impression that as n approaches infinity your average profit should be -.027 X # spins.
Can anyone point out my error in thinking?





This probably isnt the best place to put code, but if anyone is interested, code is below.
Cut/Paste into macro.

sub martingale()

bank = 1000
Bet = 5
red = "S4:S21"
black = "T4:T21"

' Here i just placed the black and red numbers in the rows from s4-s21 and t4-21 respectively, this can be moved or placed in an array

For i = 1 To 10000
x = Int((37 * Rnd) + 0)
Cells(i + 1, 1).Formula = x
compare = WorksheetFunction.CountIf(Range(red), "=" & Cells(i + 1, 1))
count = count + 1

If Bet > bank Then

' if bettor is about to go broke, they can only bet what they have remaining

Bet = Cells(i, 2)

If compare = 1 Then

bank = bank + Bet
Cells(i + 1, 2) = bank
Cells(i + 1, 3) = Bet
Bet = 5


Else

bank = bank - Bet
Cells(i + 1, 2) = bank
Cells(i + 1, 3) = -Bet


Dim profit As Single

Sum = WorksheetFunction.Sum(Range("C2:C10001"))
profit = Sum / count

MsgBox "Bankroll exhaused in " & (count) & " spins" & vbCrLf & "Expectation per spin " & (profit)
Exit For

End If
Else

If compare = 1 Then

bank = bank + Bet
Cells(i + 1, 2) = bank
Cells(i + 1, 3) = Bet
Bet = 5

Else

bank = bank - Bet
Cells(i + 1, 2) = bank
Cells(i + 1, 3) = -Bet
Bet = 2 * Bet
streak = streak - 1

End If
End If
Next i
End Sub

Quote: beenitty

But I was under the impression that as n approaches infinity your average profit should be -.027 X # spins.
Can anyone point out my error in thinking?

Your Expectation per spin is your bankroll lost (-1000) divided by the number of spins.
OK
You would need to know the average bet per spin also

for the house edge you are looking for (-1/37) you need net (-1000) / total $wagered
(house edge = $net/$total wagered)

summing column c will not do this as you have negative values in it. You need absolute values.

And even after N spins, there is still a standard deviation (standard error) for that magical house edge.
=1unitSD / square root of N or simply 1/square root of N

With your $1k bankroll you are in the middle of a 7and 8 step marty

Your average bet per spin will be around maybe $30 but you can easily calculate that yourself with your code.
I noticed you also have no max bet imposed.

Your code works fine and I see no problems.
Just divide the -1000 by the total $wagered for the house edge
or do it per spin divided by average $ bet per spin for the house edge
and then 1/square root of # spins for your standard error

example using your code and adding avg$ per spin and total $s wagered
spins: 1,378
avg $ bet per spin: $23.33091437
total$ wagered: $32,150

-1000/1,378 = -0.725689405/$23.33091437 = -0.031104199 (close to -1/37)
that is the same as -1000/$32,150
the standard deviation (error) of the house edge = 1/sqrt(1378) = 0.026938623

Good Luck