excel, data & standard deviation Canceler?

[sagefr0g]

Canceler you there? lol ......... help, lol......

errhh ok, i got some data.
daily earnings
and i know what the expected value for daily earnings is
so is there a function in excel that i can use to estimate the standard deviation?
i was gonna use STDEV but there's no where in that function to enter the known expected value.
any help?:fish::fish:
edit: so maybe there shouldn't be anyway in there to enter ev, i guess, right?:whip:
like ok if i use STDEV, is that figure produced, one standard deviation?
can i come up with one of those bell curve graph's with my data?

 

[Canceler]

Sorry, was out late last night playing poker.

Okay, this will need a little thought and research. Meanwhile, regardless of Mr. Fr0g’s personalization of this thread toward me, anyone who knows the answer should feel free to chime in.

 

[Sonny]

errhh ok, i got some data.
daily earnings
and i know what the expected value for daily earnings is
so is there a function in excel that i can use to estimate the standard deviation?

It sounds like you have a daily average (EV) but not the daily standard deviation so you are trying to estimate it from a series of daily results. Is that right?

You would need a pretty large sample size (population) to get anything reliable, but if that's what you're tying to do I can help you. Ideally you would know the SD of your playing strategy and could use that to find your daily SD. Finding the SD from sample data can be dangerous, especially since most casino games have pretty wild swings that may skew your estimations one way or the other.

i was gonna use STDEV but there's no where in that function to enter the known expected value.

The reason that the Excel function does not let you input the EV is because it uses the data samples to find it. If your daily average (EV) is very different than the average of your daily results then that is a good indication that things are going to be off.

like ok if i use STDEV, is that figure produced, one standard deviation?

Yes, that would be one standard deviation.

-Sonny-

 

[sagefr0g]

It sounds like you have a daily average (EV) but not the daily standard deviation so you are trying to estimate it from a series of daily results. Is that right?

right. i could do it by plays but some of my data is kind of screwed up.

You would need a pretty large sample size (population) to get anything reliable, but if that's what you're tying to do I can help you. Ideally you would know the SD of your playing strategy and could use that to find your daily SD. Finding the SD from sample data can be dangerous, especially since most casino games have pretty wild swings that may skew your estimations one way or the other.

unfortunately, i have no idea the SD per the playing strategy.
but yes, wild swings aren't unexpected, so ok that is helpful to know that the data can skew the estimations.
erhh, dunno if this helps that conundrum, but i do know fairly accurately worst and best case scenario numbers for an average day, sorta thing.
they aren't actual data numbers but they at least define limits for best and worst swings. maybe those could be worked into the data?

The reason that the Excel function does not let you input the EV is because it uses the data samples to find it. If your daily average (EV) is very different than the average of your daily results then that is a good indication that things are going to be off.

well, so far the average of daily results is fairly close to daily average ev, as is overall results close to ev.
so but i guess that could be skewed by wild swings and a relatively modest amount of data, sorta thing.
so ok, thank you, helps clarify things quite a bit.

Yes, that would be one standard deviation.

-Sonny-

ahhh ok, thank you, and ok i realize that could be quite off since it's coming from a small data pool.
so maybe if i could get excel to turn the data into a bell curve or attempt to do so, then if it didn't look much like a bell curve, then definitely not enough data, or something off, maybe?

 

[blackjack avenger]

dust in the wind

ahhh ok, thank you, and ok i realize that could be quite off since it's coming from a small data pool.
so maybe if i could get excel to turn the data into a bell curve or attempt to do so, then if it didn't look much like a bell curve, then definitely not enough data, or something off, maybe?

If you know its not enough data then a graph won't have much meaning. If your houly EV is fairly tight you can guess the SD is not high? I would think you have a very weak idea of SD from your play.

With real tight earnings the bell would be tall and thin? To the earnings side.

If you don't know your SD hope your betting conservatively.
I dont know
1/4 Kelly comes to mind
Don't want the misses mad:cat::whip:

 

[sagefr0g]

If you know its not enough data then a graph won't have much meaning. If your houly EV is fairly tight you can guess the SD is not high? I would think you have a very weak idea of SD from your play.

With real tight earnings the bell would be tall and thin? To the earnings side.
............

so to plot a bell curve, do you just use the raw data?
to where, what? i guess sort the negative values from highest value to lowest and sort the positive values from highest value to lowest, then marry them up on the graph chart so to speak? errh but then i guess you'd need to then make the negative values positive?:
errhh sorta like i did in the image below, where the numbers in red were negative but i changed them to positive.... sorta thing?confused::whip:
edit: erhh excel gives me a SD of $265.27 for this data........
ev is $65.83

 

[blackjack avenger]

point graph?

Isn't there a graph where you just plot points and then connect the dots, that may look interesting?

How many sessions have you played?

Think of the swings in 100 coin flips, actual results are about meaningless.

In BJ we need about a 250 million and larger sample size? At least for sims?

 

[sagefr0g]

Isn't there a graph where you just plot points and then connect the dots, that may look interesting?

How many sessions have you played?

Think of the swings in 100 coin flips, actual results are about meaningless.

In BJ we need about a 250 million and larger sample size? At least for sims?

looks like a witch's hat for Halloween, lol
errhh 94 plays in 31 days.......

 

[sagefr0g]

not enough data =====>>>>

so to make those graphs reflect an ev of $65.83, i'd have to have one heck of a lot more data, no?
edit: errrhh but shouldn't the peak of the bell curve coincide with the EV?
how can it ever do that when it's topping out at nearly $800, at this point.
errhh am i not supposed to use the raw data for the chart, or what?:whip:
edit: hmmphh, must be doing something wrong here, as far as representing the data in the chart, no?

 

[blackjack avenger]

point graph

A point graph you plot the days in order of occurrence and then connect the dots?

 

[sagefr0g]

A point graph you plot the days in order of occurrence and then connect the dots?

so.... i wasn't doin it right then, no?
maybe more like this ...........?
but how's that ever gonna look like a bell even after decades of plays?:whip:

 

[sagefr0g]

hmm, maybe this way?
http://support.microsoft.com/kb/213930

.......... ehhmm, that's gonna take me a while, lol................

 

[Canceler]

Late to the party, and didn't bring much, but...

sagefr0g:

Obviously, with BJ we can run a sim and get our expected win rate and standard deviation. With other games, like poker, and whatever it is you’re doing, we have to generate our statistics based on our own experience. As we’ve been reminded, with live play it’s unlikely we will ever have enough data to generate meaningful statistics. But still, we want to have something to look at.

I’ve never seen the use of STDEV recommended as a way to do this. In post #9 of this thread, statmanhal describes exactly how to set up a spreadsheet to calculate your hourly standard deviation. If you don’t want it to be hourly you can omit that part, or just plug in “1” for the session duration to pretend that each session was only one hour.

To get your bell-shaped curve I think you’ll need to look at the number of days you get each kind of result. Say you have these kinds of results: Huge Loss, Moderate Loss, Average Outcome, Moderate Win, Huge Win. The number of days you have an average outcome should be the bulge in the middle. The number of days you have huge wins or losses should be the tails on the sides.

 

[sagefr0g]

sagefr0g:

Obviously, with BJ we can run a sim and get our expected win rate and standard deviation. With other games, like poker, and whatever it is you’re doing, we have to generate our statistics based on our own experience. As we’ve been reminded, with live play it’s unlikely we will ever have enough data to generate meaningful statistics. But still, we want to have something to look at.

exactly!

I’ve never seen the use of STDEV recommended as a way to do this. In post #9 of this thread, statmanhal describes exactly how to set up a spreadsheet to calculate your hourly standard deviation. If you don’t want it to be hourly you can omit that part, or just plug in “1” for the session duration to pretend that each session was only one hour.

ahh ok, i haven't checked it entirely closely, but i think he's using essentially stdevp a function in excel, which is like the first example ie. (dividing by n) in this link:
http://standard-deviation.appspot.com/
stdev in excel would be like the second example (ie. dividing by (n-1), ( i believe) where one doesn't know the entire population but just has a sample. so maybe i'd be best off using stdev.......

To get your bell-shaped curve I think you’ll need to look at the number of days you get each kind of result. Say you have these kinds of results: Huge Loss, Moderate Loss, Average Outcome, Moderate Win, Huge Win. The number of days you have an average outcome should be the bulge in the middle. The number of days you have huge wins or losses should be the tails on the sides.

there ya go, makes sense...... so technically i'd need one heck of a lot more data. and then would have to know how to do the math right in the first place, lol
edit: but anyway, some rainy day i think i'll try this method:http://support.microsoft.com/kb/213930

thank you

 

[sagefr0g]

......

To get your bell-shaped curve I think you’ll need to look at the number of days you get each kind of result. Say you have these kinds of results: Huge Loss, Moderate Loss, Average Outcome, Moderate Win, Huge Win. The number of days you have an average outcome should be the bulge in the middle. The number of days you have huge wins or losses should be the tails on the sides.

so, like for the image below, which is results over days.....
i can kind of just conceptualize, Huge Loss, Moderate Loss, Average Outcome, Moderate Win, Huge Win ... ect. , and at least get a 'handle' on how things have been going, sorta thing........ gives one an idea of what kind of dispersal of results has been happening, and maybe how it will tend to go in the future, and as time goes on and more data comes in, see how it all stacks up again, at least have a qualitative analysis if not quantitative, sorta thing, at least have something to look at and get a feel for how things are going, maybe stack that analysis up against what excel thinks is one standard deviation, which in this case was $265.27 and the expected value which is $65.83 ... to where for the time being at least, i can kind of say to myself, well as far as i know right now, i can expect to be within $200 or so plus or minus of a $65 result about 68% of the time...... no?

 

[sagefr0g]

....
If you don't know your SD hope your betting conservatively.
I dont know
1/4 Kelly comes to mind
Don't want the misses mad:cat::whip:

along this line of reasoning, let me ask you a question, since you seem to know a lot about Kelly stuff.
well, first off, speaking of betting, Kelly stuff and bank roll, well the roll so far has more than doubled.
the question being, when you are 'properly' betting and making positive EV plays, errrhh well, i dunno, is there some significance to reaching the point where the roll is doubled, far as Kelly theory goes?
seems i've seen a lot of talk about the point where advantage players have doubled their roll, in conjunction with Kelly stuff. can you shed any light on that subject, ie. significance of doubling the bank roll and Kelly stuff?
have anything to do with N0, maybe? :whip:

edit: like here's an example where doubling the roll is mentioned:
That's why people advocate 1/2 kelly or 1/4 kelly. If you bet half of the kelly fraction, then you have way less chance of losing 50% of your bankroll. For example, a full Kelly better has 1/3 chance of losing 50% of his bankroll before doubling, while a 1/2 Kelly better has 1/9 chance of losing 50% of his bankroll before doubling. from this post: http://www.blackjackinfo.com/bb/showpost.php?p=238704&postcount=2
just one of many examples where "doubling the roll" is emphasized when discussing Kelly stuff

 

[Canceler]

I just used the Huge Loss, Moderate Loss, etc. to give you the concept of how to get your bell-shaped curve. Not to imply that that kind of chart would have much value, but you did seem to have your heart set on it.

Nevertheless, here’s an example of what I had in mind. Being lazy, rather than make up fake data, I just used the outcomes of my last 100 sessions of 2/4 Limit Hold’em. (Astute observers will notice I was a loser at that game, as most people are, but never mind that!)

The result types are in $25 increments. Result Type A is a loss of $175 to $200. B is a loss of $150 to $174.99. H is a loss between $0 and $25. I is a win between $0 and $25. P is a win of $175 to $200.

The columns represent the number of sessions I had of each result type. With so few data points, the chart is only vaguely bell-shaped, but you get the idea. Again, this type of chart is of little value. Your SD numbers will be more useful, I think.

 

[sagefr0g]

I just used the Huge Loss, Moderate Loss, etc. to give you the concept of how to get your bell-shaped curve. Not to imply that that kind of chart would have much value, but you did seem to have your heart set on it.

yah, lol, it's virtually all i got, for now, lol
excepting, i do know the EV.
just hoping to get some kind of a handle on the swings and how they may come down, regardless of how hazy that handle may be.
but i get your point.

Nevertheless, here’s an example of what I had in mind. Being lazy, rather than make up fake data, I just used the outcomes of my last 100 sessions of 2/4 Limit Hold’em. (Astute observers will notice I was a loser at that game, as most people are, but never mind that!)

The result types are in $25 increments. Result Type A is a loss of $175 to $200. B is a loss of $150 to $174.99. H is a loss between $0 and $25. I is a win between $0 and $25. P is a win of $175 to $200.

The columns represent the number of sessions I had of each result type. With so few data points, the chart is only vaguely bell-shaped, but you get the idea. Again, this type of chart is of little value. Your SD numbers will be more useful, I think.

yup, got it, definitely makes it clear how the bell curve is arrived at, thank you.

 

[blackjack avenger]

floating point graph?

so.... i wasn't doin it right then, no?
maybe more like this ...........?
but how's that ever gonna look like a bell even after decades of plays?:whip:

I am sure there is a real name for this graph, but it escapes me.

Froggie
Start the graph at starting bank in the middle of the Y verticle axis, then let the results be plotted & added & subtracted at each point. Then connect the dots. At the final dot your bank should be at its current level. A nice thing, this graph can be done with regular notebook or graph paper. Also, this graph can show results over time, an example if you change strategy.

I started a thread in "other games" you may find interesting.

 

[blackjack avenger]

along this line of reasoning, let me ask you a question, since you seem to know a lot about Kelly stuff.
well, first off, speaking of betting, Kelly stuff and bank roll, well the roll so far has more than doubled.
the question being, when you are 'properly' betting and making positive EV plays, errrhh well, i dunno, is there some significance to reaching the point where the roll is doubled, far as Kelly theory goes?
seems i've seen a lot of talk about the point where advantage players have doubled their roll, in conjunction with Kelly stuff. can you shed any light on that subject, ie. significance of doubling the bank roll and Kelly stuff?
have anything to do with N0, maybe? :whip:

Why talk about doubling bank? Are you retiring? So assuming one is going to play on the risk of drawdown numbers approach the infinite numbers quickly.

Chances of losing % of bank with Kelly resizing:
50% chance of losing 50% of bank with Kelly
80% chance of losing 20% of bank with Kelly

Chances of losing % of bank with 1/4 Kelly:
.08% chance of losing 50% of bank with 1/4 Kelly
21% chance of losing 20% of bank with 1/4 Kelly

Begs the question. Why would anyone play a positive expectation game with an ror? Now, what if one figured the advantage correctly on an unsure game but bet kelly? They face a 50% chance of losing half, if that happens I would think confidence would suffer.

Kelly & N0? Kelly raises N0 by a factor of 9. However betting 1/4 Kelly does not raise N0 greatly though growth suffers.