+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    07-18-2014
    Location
    Perth
    MS-Off Ver
    2013
    Posts
    5

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

    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

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

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

    Quote Originally Posted by cruze2005 View Post
    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%.
    Last edited by joeu2004; 07-18-2014 at 05:12 AM. Reason: errata: RAND()<B2; cosmetic: 1+INT(...) instead of ROUNDUP(...,0)

  3. #3
    Registered User
    Join Date
    07-18-2014
    Location
    Perth
    MS-Off Ver
    2013
    Posts
    5

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

    Quote Originally Posted by joeu2004 View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Consecutive win/loss and current win/loss streak
    By TK2013 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-18-2013, 09:15 AM
  2. Fancy Fat Loss Spreadsheet
    By gregfirr in forum Excel General
    Replies: 1
    Last Post: 02-10-2012, 10:16 PM
  3. win loss compulation spreadsheet
    By TuckerMtn in forum Excel General
    Replies: 1
    Last Post: 10-11-2010, 02:12 PM
  4. Stop Loss Formulas for Stock Trading
    By zmr325 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-28-2005, 03:25 PM
  5. [SOLVED] Weight loss line chart to monitor weight loss progress
    By S Fox in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-08-2005, 01:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1