Calculating EV using Monte Carlo Simulation?

So I made an Excel spreadsheet for a game I believe I have an advantage on. I did 10,000 lines of "hands" (one hand per line) in Excel. I kept a running net win/loss column as well. Most of the time I refresh Excel, it shows the game to be positive. Rarely it shows to be negative. But even so, it generally shows a range between $5 to $30 (per 100 hands) in EV.

I want to know exactly how much this game is worth -- or at least be almost certain of what the edge in the game is (EV per 100 hands).


Intuitively, I knew there'd be a bit of variance in this play. I've done Excel spreadsheet monte carlo simulation things before where every time I refreshed it, it'd consistently show a range of $50-55 per 100 rounds (or w/e the figure was). But in that case, I knew it'd be worth somewhere in that range since it was almost always within that range.


How narrow does the range have to be so that I'm fairly sure my edge is within that range? Should I refresh it (10K hands) like 100 times, record the EV/100-rounds, then find the average? Or is 20 sims enough? Or is 100 not enough and I'd have to do this for 500 or 1000 sims?



I'd also like to be able to figure out what the variance is in the play -- do I just record each EV in each sim I run (well, refresh), then do the hole "square root of the sum of the square" [or w/e the formula is]?


Unfortunately I can't disclose what the actual game is, how it's played or where the edge comes from. =\



Any halp appreciated.

Can you write a script to capture the output of each refresh? If so, do that and then look at the variance and standard deviation of that data.

If you can't write a script to do it, I think Oracle give a time-limited free trial of Crystal Ball when you pretend to want to buy it. It could do that recording and analysis for you, I think.

The only way to be "absolutely" sure is to see if you can work it out with math.

Personally, I don't use spreadsheets for Monte Carlo simulations, but programming. Of course, Excel comes with a built-in version of Visual Basic, but my "weapon of choice" is Visual C# Express (which is available as a free download from Microsoft, as is Visual Basic Express).

Quote: ThatDonGuy

The only way to be "absolutely" sure is to see if you can work it out with math.

Personally, I don't use spreadsheets for Monte Carlo simulations, but programming. Of course, Excel comes with a built-in version of Visual Basic, but my "weapon of choice" is Visual C# Express (which is available as a free download from Microsoft, as is Visual Basic Express).

There has been some discussion and warning about using Excel for MonteCarlo simulations. The problem is, apparently, the unsufficiently "random" features of the randomization procedure. IIRC, this is a problem when you need a large amount of "random draws". Use Excel for small size simulations only; not for MonteCarlo.

Quote: RS

...I'd also like to be able to figure out what the variance is in the play ...

Suppose the results of your 10,000 hands are in the cells A1:A10000. Then the Excel formula for the variance is =var(A1:A10000).

Excel can fetch from random.org, using the http api. Probably be pretty slow, though.

I'm not sure if this is the reason behind the warning against using excel's RNG, but some of those pseudo-random number generators will generate a comparatively short sequence and then start repeating the sequence.

Quote: RS

So I made an Excel spreadsheet for a game I believe I have an advantage on. I did 10,000 lines of "hands" (one hand per line) in Excel. I kept a running net win/loss column as well. Most of the time I refresh Excel, it shows the game to be positive. Rarely it shows to be negative. But even so, it generally shows a range between $5 to $30 (per 100 hands) in EV.

I want to know exactly how much this game is worth -- or at least be almost certain of what the edge in the game is (EV per 100 hands).


Intuitively, I knew there'd be a bit of variance in this play. I've done Excel spreadsheet monte carlo simulation things before where every time I refreshed it, it'd consistently show a range of $50-55 per 100 rounds (or w/e the figure was). But in that case, I knew it'd be worth somewhere in that range since it was almost always within that range.


How narrow does the range have to be so that I'm fairly sure my edge is within that range? Should I refresh it (10K hands) like 100 times, record the EV/100-rounds, then find the average? Or is 20 sims enough? Or is 100 not enough and I'd have to do this for 500 or 1000 sims?



I'd also like to be able to figure out what the variance is in the play -- do I just record each EV in each sim I run (well, refresh), then do the hole "square root of the sum of the square" [or w/e the formula is]?


Unfortunately I can't disclose what the actual game is, how it's played or where the edge comes from. =\



Any halp appreciated.

Perhaps talk with Axiom(Ill get you his number) he might be able to help and you can trust him with more sensitive information.

No need to disclose all aspects but more then what you would here.