My variance simulator (excel)

**FourLengthsClear** · 01-24-12, 06:48 PM

Nice.

It might be worth sharing with the Player's Talk crowd. Some of them might swear at you but a few might 'get it'.

**Salmon Steak** · 01-24-12, 07:15 PM

cool stuff man.

**BradyMossTD** · 01-24-12, 09:42 PM

Hey man, I was the guy who originally asked for this. Someone posted something similar awhile ago but I couldn't find the link and searched fruitlessly for it.

Didn't expect anyone to take a few hours to actually make one from scratch. Thank you! Much appreciated.

edit***

Hey actually, I'm a little confused by the results. Maybe I'm wrong but they don't seem accurate.

For example I input a starting bank of $50K, bet to win 10%, odds of 1.91, win %55. 100 samples.

One of the generated results was a "worst" scenario of $26,588 at a 52% win rate. That seems very off that a 52% win rate at 1.91 odds can translate to a loss of $23,412 EVEN with percentage betting. It seems like you'd need a much lower win rate over 100 samples to realistically get that kind of loss? Am I wrong?

edit 2***

Ok, maybe I don't understand what the "samples" field stands for? If I input a value of 1 and keep the other numbers the same, I will ALWAYS get a worst win rate of 0% and a best win rate of 100%. But if there's only ONE sample, meaning the scenario is only simulated ONCE, the best and worst win rate should be identical, that is they should both be either 0% or 100%. Correct? Unless I'm confusing what the "sample" field stands for? *nevermind, confused the meaning of the sample field

edit 3***

OK, I see what's going on here. I thought that the "sample" field was the the SIMULATIONS field, which is always fixed at 10 (missed that in the OP). I would actually like to modify the SIMULATIONS field so that it's fixed at 1. Therefore, each refresh is just a way to simulate x amount of wagers. Seems simple enough to modify that, but I don't know how. Thanks again.

**FourLengthsClear** · 01-24-12, 10:02 PM

Originally posted by BradyMossTD

Hey man, I was the guy who originally asked for this. Someone posted something similar awhile ago but I couldn't find the link and searched fruitlessly for it.

Didn't expect anyone to take a few hours to actually make one from scratch. Thank you! Much appreciated.

edit***

Hey actually, I'm a little confused by the results. Maybe I'm wrong but they don't seem accurate.

For example I input a starting bank of $50K, bet to win 10%, odds of 1.91, win %55. 100 samples.

One of the generated results was a "worst" scenario of $26,588 at a 52% win rate. That seems very off that a 52% win rate at 1.91 odds can translate to a loss of $23,412 EVEN with percentage betting. It seems like you'd need a much lower win rate over 100 samples to realistically get that kind of loss? Am I wrong?

When you are overbetting it is entirely possible!

**Salmon Steak** · 01-24-12, 11:22 PM

Originally posted by FourLengthsClear

When you are overbetting it is entirely possible!

I heard people say 1-2% is a better range if you don't know your real edge.

**FourLengthsClear** · 01-24-12, 11:26 PM

Originally posted by Salmon Steak

I heard people say 1-2% is a better range if you don't know your real edge.

In this case he 'knows' his edge. He has an expected win rate of 55% and odds each time of -110. His optimum bet size is therefore to win 5% according to Kelly.

**BradyMossTD** · 01-24-12, 11:47 PM

Originally posted by FourLengthsClear

When you are overbetting it is entirely possible!

Thanks for the feedback but I didn't mean for the 10% to be a smart % for wagering at those odds. They were just numbers I was inputting to test out the program.

At 5%, with the same numbers, I got a worse case scenario of -8048 with a 52% win rate (which should be slightly less than break even). I was astounded that your losses could be so high at that win rate. Which is why I suspected that maybe there was an small error somewhere in the program.

**FourLengthsClear** · 01-25-12, 12:18 AM

Originally posted by BradyMossTD

Thanks for the feedback but I didn't mean for the 10% to be a smart % for wagering at those odds. They were just numbers I was inputting to test out the program.

At 5%, with the same numbers, I got a worse case scenario of -8048 with a 52% win rate (which should be slightly less than break even). I was astounded that your losses could be so high at that win rate. Which is why I suspected that maybe there was an small error somewhere in the program.

Variance really can be a b!tch!

I haven't checked tukkk's spreadsheet but those numbers don't seem outlandish. With such a small sample, 8 or 9 straight losses will severely deplete a bankroll and there is not much 'time' to recover. It can work the other way of course, I am sure the best case scenario figures were surprising to you too.

**tukkk** · 01-25-12, 04:46 AM

BradyMossTD

The idea is that you input the criteria and then you always have 10 simulations (read: 10 different scenarios) in the graph.
To get better results, it would be better to have 100 or 1000 simulations, but that would make the excel file from 2mb to 20mb or 100mb, so it isnt practical.
Therefore i suggest refreshing (F9) a little, so you can see in the graph what really is possible.

As you can see, sample size is really important.
The field samples stands for the number of bets. You can input a number from 1 to 10000.
So you can visualize what kind of scenarios are possible with 55% winrate over 500 bets at 1,91 odds or 58% winrate over 4000 bets at 1,94 odds.
If you input samples=1 , then you only have 1 bet and in most cases some of the 1 simulations win that one bet and some lose.

"I would actually like to modify the SIMULATIONS field so that it's fixed at 1. Therefore, each refresh is just a way to simulate x amount of wagers. Seems simple enough to modify that, but I don't know how." - That is not a good idea, because you get only 1 simulation, which doesnt tell you alot.

Heres an example :

I input starting bank = 2500, bet to win 2% of bank, average odds 1,91, win percentage 55% and samples = 500. So lets see what can happen over 500 bets with those stats. As i refresh a couple of times a distant hot run appears, so do 2 almost 50% winrate lines.
As you can see out of those 10 simulations the worst line ended at 1615 with 50,8% winrate. The hot run line ended at 9434 with 59,2% winrate. That tells us that with a realistic 55% winrate pretty much we can easily achieve 50% and think we are losers, or 60% and think we are the best. But as you input a bigger number of bets you see that if you really are a 55% capper, you wont be in losing for long.

**Professor1215** · 01-25-12, 06:57 PM

This is really cool. Still trying to understand what it all means, but thank you for helping SBRNation.

**jerry** · 01-26-12, 02:23 AM

Great tool.

**statnerds** · 01-26-12, 06:19 AM

Originally posted by tukkk

BradyMossTD

But as you input a bigger number of bets you see that if you really are a 55% capper, you wont be in losing for long.

First, thanks for putting in the time and effort for what could be a useful, or dangerous, tool for new or rec bettors.

Second, I must point out 2 things that made me cringe reading the above quote. 55% win rate is damn near world class and you will find very, very few people that can achieve said win rate over the course of 250 plays, let alone the thousands upon thousands of plays required to generate meaningful profits.

My other issue is Gambler's Ruin. If you know of it and chose to ignore it, fine. At least place the disclaimer, as I am now that as you move along and the amount of wagers placed increases consistently, the 'cold runs' will increase in size and scope. The streaks will become longer and the losses deeper.

Which leads me to my final point, one beaten to death I am sure on the pages of this forum. Kelly is flawed because you can never accurately measure your edge. It is impossible. Garbage in, garbage out.

But I truly appreciate the effort and work you put in on this and am in no way knocking you. Just another point of view is all.

good luck

**tukkk** · 01-26-12, 07:06 AM

First, thanks for putting in the time and effort for what could be a useful, or dangerous, tool for new or rec bettors.

Thanks for the feedback, the results could indeed be interpreted in different ways.
The main purpose of this simulator is to show the importance of sample size.

My other issue is Gambler's Ruin. If you know of it and chose to ignore it, fine. At least place the disclaimer, as I am now that as you move along and the amount of wagers placed increases consistently, the 'cold runs' will increase in size and scope. The streaks will become longer and the losses deeper.

The simulator uses "bet to win fixed percentage of bankroll", which is optimal, because as the bankroll gets smaller, bet amount gets smaller, same thing the other way. I didn´t ignore Gamblers ruin.

Which leads me to my final point, one beaten to death I am sure on the pages of this forum. Kelly is flawed because you can never accurately measure your edge. It is impossible. Garbage in, garbage out.

I agree that one can never accurately measure edge and full kelly will get you a heart attack, but models that are better than the market can be quite good in determining a range and varying fractional kelly is the best way to go in my opinion.
Now about this simulator and kelly, you can see i have the input for that "bet to win %". Since varying bets are technically not possible in this simulator, this is the optimal way in my opinion.

55% win rate is damn near world class and you will find very, very few people that can achieve said win rate over the course of 250 plays, let alone the thousands upon thousands of plays required to generate meaningful profits.

55% winrate is easily achievable with very low volume. However in most cases it isnt practical to have less quantity and more quality. The key is to find the optimal risk-to-reward ratio.

**jolmscheid** · 01-27-12, 06:35 PM

Originally posted by statnerds

First, thanks for putting in the time and effort for what could be a useful, or dangerous, tool for new or rec bettors.

Second, I must point out 2 things that made me cringe reading the above quote. 55% win rate is damn near world class and you will find very, very few people that can achieve said win rate over the course of 250 plays, let alone the thousands upon thousands of plays required to generate meaningful profits.

My other issue is Gambler's Ruin. If you know of it and chose to ignore it, fine. At least place the disclaimer, as I am now that as you move along and the amount of wagers placed increases consistently, the 'cold runs' will increase in size and scope. The streaks will become longer and the losses deeper.

Which leads me to my final point, one beaten to death I am sure on the pages of this forum. Kelly is flawed because you can never accurately measure your edge. It is impossible. Garbage in, garbage out.

But I truly appreciate the effort and work you put in on this and am in no way knocking you. Just another point of view is all.

good luck

Kelly is optimal if you know your edge...if you do not know your edge exactly (Which you are right..nobody does), then use a .75 or .5 kelly...

**XS$z** · 01-27-12, 07:25 PM

Very interesting. Thanks for this.

**mathdotcom** · 01-27-12, 08:31 PM

Sorry for being the devil's advocate here but how useful is this exactly? We all know the intuition behind Kelly and how variance will change with our bet size, that even with nice edges we can go on losing streaks etc etc.

I'd rather spend my time improving the efficiency of my model -- any bettor with serious experience knows all about variance.

Just my two cents.

**tukkk** · 01-28-12, 05:07 AM

Sorry for being the devil's advocate here but how useful is this exactly? We all know the intuition behind Kelly and how variance will change with our bet size, that even with nice edges we can go on losing streaks etc etc.

I think its a solid 10-minute amusement. Maybe a few will for a moment become more philosophical about their own ventures.

I'd rather spend my time improving the efficiency of my model -- any bettor with serious experience knows all about variance.

My model has reached the stage, where everything is automated. Its connected 24/7, refreshing, gathering various data, arranging it into db, sending me text messages with projections when lines come out. I usually check everything once a day , which takes me 15 min. Other than that, i have no responsibilities.

**CrimsonQueen** · 01-28-12, 10:43 AM

Good stuff, man. Thanks for sharing.

**Gaze73** · 10-01-21, 05:18 PM

the download link doesn't work

**solring** · 10-02-21, 09:40 AM

Originally posted by Gaze73

the download link doesn't work

After nine years? No way...

**BeatTheJerk** · 10-02-21, 06:57 PM

Originally posted by solring

After nine years? No way...

“He be” stargazing …

**veriableodds** · 10-03-21, 04:53 PM

Want to know how bad in depth models really are the guy is? http://www.williamleiss.com/cfbmyplays.php a complete stat nerd, and despite I see no profit in any sports. The guy pours his heart into to

Yall need to come up with something different