probability of X losing?

[John Caines]

Hello All.
I came across a interesting table , that showed the probability of successive loses with various win %'s.
Here's a screen shot of it.

http://tinyurl.com/5v2uf4y

My question is,
Does anyone know how is this table is calculated in Excel?
Are there any probability experts out there?

I've seen a formula for probability that uses the =LN function,,,,,,
Which after googling I have read it to be;"Returns the natural logarithm of a number"

I'm not sure if these are calculated with the =LN in the formula at all?

Can anyone work out how 1 of the cells is calculated?
I just can't understand how these figures are generated.
I just don't get it?,,,I just can't see how this table is worked out.

Many thanks for your time.
Best Regards;
John Caines

 

[John Caines]

SHG4421,
I can't stop playing with your 'Probability Of A Run' spreadsheet

As a note,,, I did put another post here that was related to this;
http://www.mrexcel.com/forum/showthread.php?p=2619550#post2619550

This post was related to a Streak Loss Calculator here;

http://www.sbrforum.com/betting-tools/streak-calculator/

SHG4421,, playing with your calculator,, and using the settings with this online version,
IE if I set the series length the same & so the Loss rate would equal the same,,,
looking at the returned %'s for the losing streaks SHG4421,, they are the same!

I'm sure yours is correct.
As a note SHG4421,
In your spreadsheet;
Your MC Model Tab,, The Trades go down to 50 (As In My original Post it was a 50 length sample).
But If your sheet was set to go down to 100,,, the results wouldn't be altered in anyway?
I can see on Sheet 1 you can input the No of Trades,, but having just 50 & not 100 In The MC Model,, is the same,, it makes no difference to the results yes? (I think I'm right in saying this)

To really perfect your spreadsheet SHG4421 you could add 1 more small table, that had a dynamic Win Rate %. You could now enter your known Win Rate % just like you can do with the online Streak Calculator,,, and it would return the correct line of results of consecutive losses related to that Win Rate %.
Just an Idea for your version 2 'Probability Of A Run Spreadsheet' SHG4421,,, it is very well done indeed,, but this would cap it off perfectly.

All the best SHG4421,
Just thought I'd let you know I was testing it against the online streak calc,, and its past with flying colours
All the best
John Caines

 

[John Caines]

SHG4221,
I tried to add a quick cal to your spreadsheet,
http://dl.dropbox.com/u/16052166/MrE 530528 - Probability of a Run-v2.xlsm

Thing is,,,
Somehow I wanted to insert a macro 'Calculate' Button, and I don't know what I done,, but I inserted a label,,,, I've tried to delete it and can't?
It's strange,,, the mouse cursor won't even let me click on the cell the label is in?

Anyway,, it was just an Idea,,, not sure of the formula though for cell AC15.
I can see in your VB,, that you have;

        For l = 0 To n \ (r + 1)
            Beta = Beta + (-1) ^ l * .Combin(n - l * r, l) * (q * p ^ r) ^ l

I see in the defined names,, 'N' is cell B5,, Entered No. Trades,, (I think )
Actually,, I'm lost,, Not sure what q is?

Can the above formula be adapted SHG4421 so where I have label 1 (By Mistake,, should be a Calculate Button,, but I don't know how to delete the label now ),,,,so the formula works for the quick calc cell AC15??

This would really finish this off SHG4421.
Hope the above makes sense.
Many thanks again SHG4421.
I'm still clicking run now,, I can't stop playing with it,, sad I know,, but true!
All the best
John Caines

 

[shg]

I added the ability to do up to 100 trials, and the 'streak calculator' (it doesn't give exactly the same results as your on-line version). Same link: http://www.box.net/shared/gppxepyl82

Not sure what q is?

p is the probability of 'heads;' q is the complementary probability of tails = 1-p

 

[John Caines]

Many Thanks SHG4421,, brilliant stuff.

I noticed that before you were using a Correlation In Cell N9,,,
now you are using a RMS Diff???
I've just wiki'd it
http://en.wikipedia.org/wiki/Root_mean_square
It says ""It is the square root of the mean of the squares of the values."

I couldn't understand the formulas,, above me SHG4421,,, but as I read down the page,, by the time I got 3/4's down the Wiki page I read this,,,

Root mean square error
Main article: Root mean square error
=============================
When two data sets---one set from theoretical prediction and the other from actual measurement of some physical variable, for instance---are compared, the RMS of the pairwise differences of the two data sets can serve as a measure how far on average the error is from 0.

The mean of the pairwise differences does not measure the variability of the difference, and the variability as indicated by the standard deviation is around the mean instead of 0. Therefore, the RMS of the differences is a meaningful measure of the error.
=============================
So,,,, I think if I'm correct that if the RMS Diff is showing say 0.24% after the Monte Carlo Simulation,,
it means the theoretical prediction & the actual measurement only differ by less than an AVERAGE of 1/4 of 1%.
I think that is right SHG4421????
I see it's an Array

{=SQRT(AVERAGE((C10:L28-rgnOut)^2))}

Where as before the Correlation was;

=CORREL(C10:L28,O10:X28)

So I take it that the RMS Diff is a more accurate measure of the difference between theoretical & Actual?

Again, many thanks for this SHG4421.
A great sheet.
Still playing with it now,, how sad I am
All the best
john Caines

 

[shg]

Squared errors are what you minimize in a regression, so yes, it's a meaningful metric.

if the RMS Diff is showing say 0.24% after the Monte Carlo Simulation, it means the theoretical prediction & the actual measurement only differ by less than a 1/4 of 1%

An average, yes. My statistics isn't strong enough to infer the extent to which the Monte Carlo trial validates the calculation.

 

[Volar]

new here.

Great stuff.

I love the model, but am confused on 1 odd item

how do you lablel "run" and "p"

I have not seen labeling like that- not coded in VBA that i could see and the Label seems dynamic?

Thanks for posting.

 

[shg]

Welcome to the board.

They are relative references; run refers to the cell in row 9 in the same column as the referencing cell. p is defined similarly.

 

[Volar]

I have been looking for that for some time--- cleans up some option model formulas!

 

[shg]

One last change. I added a wrapper function so you can just do

=ProbRun(n, r, p) where

n = rumber of trials

r = length of run of consecutive heads

p = probability of heads

Same link.

 

[John Caines]

Hello all.
Been playing with this great spreadsheet again; (Many thanks SHG for this).

I wanted to increase the number of trial trades.
So,, I went to the MC Model tab and copied the cells down to give me 1000 (Was originally 100).
Then I thought I'd try this now on the setting of 1,000 Trades.
At first glance the results seemed ok,, but I'm looking at it closer now and I'm not sure; but I think it has some errors?
There are some with negative values in cells when I would have thought there shouldn't be,, also so rather long returned %'s which seem,, well; too long?
I'm not sure at all.
Maybe it was me when I pasted down the cells in the MC Model,, but I think I did this correctly.
SHG's original copy is here;
http://www.box.net/shared/gppxepyl82
Version altered by me (So it can calculate 1000 trades) is here;
http://dl.dropbox.com/u/16052166/Probability of a Run-1000trade verson.xls
He's a screen shot as well to illustrate what I'm seeing;

If anybody can tell me what's wrong (If there is something wrong) I would be most grateful.
Maybe I did something wrong when I copied the cells down in the MC Model tab,, but I retried it with the same results...
Hey ho.

I hope somebody can advise.
It's really been bugging me this last few days.

Many thanks for all your time.
Best Regards;
JC