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

 

[shg]

John,

The problem is in this routine (slightly modified from the version you have, but to no avail):

Function Beta(nToss As Long, run As Long, pHeads As Double) As Double
    ' shg 2011
    ' [URL]http://arxiv.org/PS_cache/math/pdf/0511/0511652v1.pdf[/URL]
    Dim k      As Double
    Dim l           As Long
 
    k = (1 - pHeads) * pHeads ^ run
 
    With WorksheetFunction
        For l = nToss \ (run + 1) To 0 Step -1
            Beta = Beta + .Combin(nToss - l * run, l) * (-k) ^ l
        Next l
    End With
End Function

When nToss is large (>300), the value Beta diverges, alternating between positive and negative values as the loop iterates. I don't see why after a quick look, and don't have more time to spend on it.

 

[John Caines]

Hello SHG!
Thanks for getting back to this thread. Seems to be a real difficult 1.
I'm sorry that you're fixed for time on this (We're always chasing time I know)... But it's such a great sheet though. Such a shame to leave it as is; it's so close to being right I feel.

I've been doing some digging around SHG, to see if in anyway I can help to find a quick solution,, must be others out there with this kind of problem; or posed this question before. Your code I know is way above me but I've come across posts with similar issues & solutions that might have the answers SHG.

I have come up with 1 website (I might have mentioned this before somewhere SHG, not sure now),,,a forum post that has a link to a spreadsheet also.
Here's the forum link;
http://wizardofvegas.com/forum/questions-and-answers/math/4855-ask-the-wizard-correction/
Spreadsheets here;
The first is for Excel 2007 HERE size 5.3mb
www.pulcinientertainment.com/info/Wiz-streaks-at-least1-upload.xlsb
The second for Excel 2003 and earlier HERE size 17mb
www.pulcinientertainment.com/info/Wiz-streaks-at-least1-net.xls
These are quite big sheets SHG,,, but within the post are links to other posts that seem to have some formulas that look similar to yours SHG
Just reading the posts now,,, these guys seem to know there stuff SHG.
Here's a few links to other websites that they mention (Posts),,, which look good also;
IE
http://forumserver.twoplustwo.com/25/probability/help-me-simple-probability-problem-1053105/
This seems to have the answers, links to other threads also and some docs,, IE
https://docs.google.com/viewer?a=v&...MDk3My00ZTM0LTk2OWYtMTJjOWU4N2Q0ZDI5&hl=en_US
**MAYBE THIS IS IT SHG**
This page 3 for instance SHG; Masque de Z & Bruce Z have a right old discussion on it, with a few formulas mentioned. Again, these are above me SHG, but if you read it I'm sure you'd understand them
http://forumserver.twoplustwo.com/2...imple-probability-problem-1053105/index3.html
Masque de Z mentions in one of his replies a formula;

Pm(N)=1-(1-1/2^m)*exp(-(N-m)/(2^(m+1)-m)

I only mention this as it looks similar to your code in some way! (No tvery helpful I know,, but I'm sure this thread has value SHG)

I hope the above helps SHG.
Hope maybe you can crack it SHG if you can spare a bit of time.
Talking of time; time for a cup of tea.

Many thanks for all your efforts SHG.
Somehow it will get there; the sheets just too good to leave as is!
All the best
John Caines

 

[John Caines]

Hi SHG,,
Maybe this PDF might help?
http://dl.dropbox.com/u/16052166/linrek06.pdf

It's called;
'A Summary Of Recursion Solving Techniques.'
Maybe it has nnothing to do with it at all,, just that I came across it whilst looking at probability posts.


JC

 

[John Caines]

Managed to get a reply,,,,
From Someone called "spadebidder."

###### Perl Code #####


$p = 1/2083; # Probability of event
$m = 2; # Length of streak
$n = 1000000; # Length of series

print "calculating....\n";

$iter = int( $n/($m+1) ); #ceiling( ($n-$m)/($m+1) )
$last = $n - ($iter-1)*($m+1) - $m - 1;

$c = (1-$p)*$p**$m;

$prob[$m] = $p**$m;

for ($j = 1; $j <= $iter; $j++) {

$prob[0] = $prob[$m] + (1-$prob[0])*$c;
for ($i = 1; $i <= $m; $i++) {
$prob[$i] = $prob[$i-1] + (1-$prob[$i])*$c; 
}

}

print "Probability of ", $m, " events of probability ", $p, " in a row in ", $n, " trials = ", $prob[$last], " \nor ", 1/$prob[$last]-1, " to 1";


###### End Perl Code #####

OR In JAVA

function calcStreakProb(lSeries, lStreak, dProb) {
	lSeries = lSeries.replace(/,/g, "")
	lStreak = lStreak.replace(/,/g, "")
	lSeries = parseInt(lSeries);
	lStreak = parseInt(lStreak);
	if(dProb.indexOf('%') > -1) {
		dProb = dProb.replace(/%/g, '');
		dProb = parseFloat(0+dProb/100);
	} else {
		dProb = parseFloat(0+dProb);
	}
	if (dProb < 0 || dProb > 1) {
		alert("Probability must be between 0 and 1");
		return;
	}
	if (lStreak > lSeries) {
		return 0;
	}
	var dTotProb = new Array(lSeries);
	dTotProb[lStreak] = Math.pow(dProb, lStreak);
	for (var i = lStreak+1; i<= lSeries; i++) {
		dTotProb = dTotProb[i-1] + Math.pow(dProb, lStreak) * (1-dProb) * (dTotProb[i-lStreak-1] != undefined ? 1-dTotProb[i-lStreak-1] : 1);
	}
	return fmtPercent(dTotProb[lSeries]);

Now how to convert that to this sheet in excel?
http://dl.dropbox.com/u/16052166/1-Probability-of-a-Run-100-trades-working.xls

Anybody can convert Perl or Java in this sheet?
help!

I'm still trying to get this to work 100%.
Just to good a sheet to let die
All the best
JC

 

[John Caines]

Hello All,
Really been trying to get this working 100%,, I've mentioned before it's just too good a sheet to let it die a death.

I've tried posting here also;
http://forumserver.twoplustwo.com/25/probability/help-need-rework-spreadsheet-if-poss-1122008/

Had some great help from a guy called BruceZ,,
he's just got back to me with a new VB code;
I'll have to try it tomorrow,, off to bed now,, but anybody interested in this post,, do read the link I've mentioned,, some coding here that's above me,,,
As a note,, the code suggested by BruceZ is a recode of his Java code I think,, here's his VB code;

Function ProbRun(n As Long, r As Long, p As Double)

' Probability of a r or more consecutive heads in n tosses
' of a coin having probability p of heads

    Dim prob(100)           As Double   'size of max r
    Dim c                   As Double
    Dim iter                As Long
    Dim last                As Long
    Dim i                   As Long
    Dim j                   As Long
    
    iter = n \ (r + 1)
    last = n - iter * (r + 1)

    c = (1 - p) * p ^ r

    prob(r) = p ^ r
    For j = 1 To iter
        prob(0) = prob(r) + (1 - prob(0)) * c
        For i = 1 To r
            prob(i) = prob(i - 1) + (1 - prob(i)) * c
        Next i
    Next j
    
    ProbRun = prob(last)
End Function

& I've just seen another contributor mention a possible limitation in excel regarding this;

Somebody called Pyromantha says;
"I think Excel only uses 15 significant figures so it's pretty quickly becomes useless for subtracting large but similar numbers.

I checked this by doing =10^15 in A1, =10^15-1 in B1, =A1-B1 in C1. It correctly gives 1. But if you change it to 10^16 and 10^16-1, it thinks the difference is 0.

Even under the assumption of 15 sig figs it gives some strange answers. For example A1 = 10^17, B1 = 10^17 - 1000, A1-B1 = 992?!? So perhaps it just goes crazy when the numbers get beyond 10^15 ."

I'll leave it at that now,, off to bed,,
I'll try the new code tomorrow.

Many Thanks to everyone.
JC

 

[John Caines]

Just to add,,,
why there might have been some issues in excel,,(Relates to the PDF academic paper that the original formula that SHG worked from)....
bruceZ also notes;

"I should clarify this. With the old algorithm from the paper, both Excel and VB had roundoff problems because both use a double precision floating point word of 64 bits. The reason the new algorithm doesn't have this problem is because it doesn't generate large numbers or subtract large numbers, not because it is implemented in VB."


Hope this might be useful to know for some of you.

Best Regards
JC

 

[John Caines]

Hello all,
Just thought I'd update this thread a bit.
BruceZ has kindly written some more code for the losing streak calculator (Now with multiple streaks in as well)
I'll just add it here in case anybody wants to look (I don't understand it,,it really looks alien, but heyho).

Function ProbRunMulti(n As Long, r As Long, p As Double, s As Long) As Variant

' This Spreadsheet Is Here Due To The Contributions Of BruceZ & SHG. Thank You Both.
' This Formula Is Created By Probability Guru BruceZ; From twoplustwo.com
' Many Thanks Your Help BruceZ, Eternally Grateful,
'
' Probability of 1,2,3,...s or more streaks of r or more consecutive heads in n tosses
' of a coin having probability p of heads.  Returns s values.
'
' P(j, i) = P(j, i - 1) + [P(j-1, i-r-1) - P(j, i-r-1)] * (1-p)p^r, for i > j*(r+1) - 1
'
' P(j, i) = p^(jr) * (1-p)^(j-1), for i = j*(r+1) - 1
'
' P(j, i) = 0, for i < j*(r+1) - 1
'
' P(0, i-r-1) = 1
'
' where j is the number of streaks, and i is the flip.
' Note that [P(j-1, i-r-1) - P(j, i-r-1)]is the probability of exactly j-1 streaks.
' j is taken modulo 2 in the code implementation.
' P is implmented as a linear array prob of size 0:n.

    ReDim output(1 To s) As Double  ' output values for 1,2, ..., s or more streaks
    ReDim prob(1, n) As Double      ' array of probabiites for each flip.
    Dim c            As Double      ' (1-p) * p^r
    Dim curr         As Long        ' 0 or 1 to index first dimension of prob array
    Dim prev         As Long        ' 0 or 1 to index first dimension of prob array
    Dim first        As Long        ' offset of first flip where j streaks is possible
    Dim i            As Long        ' index of flips
    Dim j            As Long        ' index of streaks
    
    c = (1 - p) * p ^ r

' 1 or more streaks

    If r <= n Then prob(1, r) = p ^ r
    For i = r + 1 To n
        prob(1, i) = prob(1, i - 1) + (1 - prob(1, i - r - 1)) * c
    Next i
    output(1) = prob(1, n)
    
' 2, 3, ... s or more streaks

    For j = 2 To s
        curr = j Mod 2
        prev = (j + 1) Mod 2
        first = j * (r + 1) - 1
        If first <= n Then prob(curr, first) = p ^ (j * r) * (1 - p) ^ (j - 1)
        For i = first + 1 To n
            prob(curr, i) = prob(curr, i - 1) + (prob(prev, i - r - 1) - prob(curr, i - r - 1)) * c
        Next i
        
        output(j) = prob(curr, n)
        
        For i = 0 To n
            prob(prev, i) = 0       ' clear for reuse
        Next i
    Next j
    
    ProbRunMulti = Application.Transpose(output)
    
End Function

Looks good eh!!
(He must work for NASA)

 

[John Caines]

I'm just finishing off some of the sheets.....
here's a screen shot;
This is the Monte Carlo "Multiple" Losing Streak Version.
I'll hopefully in about 1 week have a full blog page for it with download spreadsheets available.
i'll posyt a link here to it as soon as it's ready

 

[John Caines]

Here's a dropbox link for now of 97-2003 & excel 2010 Losing Streak Calculator sheets.
http://dl.dropbox.com/u/16052166/DTL-Losing-Streak-Probability-Spreadsheets.zip

When the webpage becomes available I'll post that link instead & make the dropbox redundant.
Should be within 7days (I hope)
I best get too it!!
All the best
John caines

 

[John Caines]

Finally a live webpage with EXCEL 2010 & 2003 Losing Streak Calculators On
<a href="http://daytradinglife.com/losing-streak-probability-calculator/" target="">Losing Streak Probability Calculators</a>
& as a note,, there are some that include the RISKAMP mersenne Twister version;
<a href="http://daytradinglife.com/losing-streak-probability-calculator/" target="">Mersenne Twister Losing Streak Probability Calculators</a>