RS
• Posts: 8626
Joined: Feb 11, 2014
February 2nd, 2015 at 5:12:25 AM permalink
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.
rdw4potus
• Posts: 7237
Joined: Mar 11, 2010
February 2nd, 2015 at 5:32:13 AM permalink
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.
"So as the clock ticked and the day passed, opportunity met preparation, and luck happened." - Maurice Clarett
ThatDonGuy
• Posts: 6471
Joined: Jun 22, 2011
February 2nd, 2015 at 6:48:19 AM permalink
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).
kubikulann
• Posts: 905
Joined: Jun 28, 2011
February 2nd, 2015 at 11:13:49 AM permalink
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.
Reperiet qui quaesiverit
ChesterDog
• Posts: 1612
Joined: Jul 26, 2010
February 2nd, 2015 at 11:28:53 AM permalink
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).
Dalex64
• Posts: 1067
Joined: Feb 10, 2013
February 2nd, 2015 at 2:18:22 PM permalink
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.
AxelWolf
• Posts: 22296
Joined: Oct 10, 2012
February 2nd, 2015 at 3:01:00 PM permalink
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.
♪♪Now you swear and kick and beg us That you're not a gamblin' man Then you find you're back in Vegas With a handle in your hand♪♪ Your black cards can make you money So you hide them when you're able In the land of casinos and money You must put them on the table♪♪ You go back Jack do it again roulette wheels turinin' 'round and 'round♪♪ You go back Jack do it again♪♪