Trading Spreadsheet - Random win/loss outcome against established Win/Loss %

[cruze2005]

Hey all,

I have attached the spreadsheet but what I am looking for is a way to randomise the Win/Loss outcome based on the select Win%.

In the spreadsheet I have the CHOOSE command which appears to work but only on a 50% outcome. I have tested this by coping and pasting (values only) the outcomes on a 2nd sheet, using the IF command to assign a value based on Win =1 and Loss = 0, Autosumming the totals and dividing against the total outcomes. From the last 15 refreshes the best result I have got is 57% Wins.

I know the sample size is small but ideally I would like to see a higher % than 57 if I am looking at a system of 85% wins (or what ever the amount is).

Thanks

Ill be around so please drop a question if my ramble makes no sense.

Trading Random Outcome and Growth.xlsx

[joeu2004]

I have attached the spreadsheet but what I am looking for is a way to randomise the Win/Loss outcome based on the select Win%. In the spreadsheet I have the CHOOSE command which appears to work but only on a 50% outcome. [....] I know the sample size is small but ideally I would like to see a higher % than 57 if I am looking at a system of 85% wins (or what ever the amount is).
Attachment 333107

Instead of =CHOOSE(RANDBETWEEN(1,2),"Win","Loss") in D6, use:

=IF(RAND()<$B$2,"Win","Loss")

However, if you want to be 95% confident that the %wins is B2 +/-0.05 (i.e. between 80% and 90% when B2=85%), the sample size n should be:

=1+INT($B$2*$B$3/(0.05/TINV(1-95%,N-1))^2)

where B2 is the expected %wins, B3 is the expected %losses (1-B2), and N is the intended sample size. Note: 1+INT(...) = ROUNDUP(...,0).

Note that this is a bootstrap formula; we substitute N=n each iteration. For example, if N=34 initially, the formula returns 212. Substituting N=212, the formula returns 199. Substituting N=199, the formula returns 199 again.

So the sample size should be at least n=199 in order to be 95% confident that the %wins will be between 80% and 90%, given B2=85%.

[cruze2005]

Instead of =CHOOSE(RANDBETWEEN(1,2),"Win","Loss") in D6, use:

=IF(RAND()<$B$2,"Win","Loss")

However, if you want to be 95% confident that the %wins is B2 +/-0.05 (i.e. between 80% and 90% when B2=85%), the sample size n should be (1+INT(...) = ROUNDUP(...,0)):

=1+INT($B$2*$B$3/(0.05/TINV(1-95%,N-1))^2)

where B2 is the expected %wins, B3 is the expected %losses (1-B2), and N is the intended sample size.

Note that this is a bootstrap formula; we substitute N=n each iteration. For example, if N=34 initially, the formula returns 212. Substituting N=212, the formula returns 199. Substituting N=199, the formula returns 199 again.

So the sample size should be at least n=199 in order to be 95% confident that the %wins will be between 80% and 90%, given B2=85%.

Thanks so much this has helped wonderfully.