Win Percentage and Expectancy example, Excel equity curve graph with Monte Carlo - futures io
futures io



Win Percentage and Expectancy example, Excel equity curve graph with Monte Carlo


Discussion in Psychology and Money Management

Updated
      Top Posters
    1. looks_one vvhg with 5 posts (13 thanks)
    2. looks_two Big Mike with 4 posts (5 thanks)
    3. looks_3 trendisyourfriend with 1 posts (1 thanks)
    4. looks_4 geott with 1 posts (1 thanks)
      Best Posters
    1. looks_one vvhg with 2.6 thanks per post
    2. looks_two dryg with 2 thanks per post
    3. looks_3 Big Mike with 1.3 thanks per post
    4. looks_4 geott with 1 thanks per post
    1. trending_up 11,560 views
    2. thumb_up 25 thanks given
    3. group 15 followers
    1. forum 17 posts
    2. attach_file 4 attachments




Welcome to futures io: the largest futures trading community on the planet, with well over 125,000 members
  • Genuine reviews from real traders, not fake reviews from stealth vendors
  • Quality education from leading professional traders
  • We are a friendly, helpful, and positive community
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts
  • We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community.  It's free and simple.

-- Big Mike, Site Administrator

(If you already have an account, login at the top of the page)

 
Search this Thread
 

Win Percentage and Expectancy example, Excel equity curve graph with Monte Carlo

(login for full post details)
  #1 (permalink)
 Big Mike 
Site Administrator
Swing Trader
Data Scientist & DevOps
Manta, Ecuador
 
Experience: Advanced
Platform: Custom solution
Trading: Futures & Crypto
 
Big Mike's Avatar
 
Posts: 50,068 since Jun 2009
Thanks: 32,534 given, 98,490 received

Hi guys,

I have a request for the math and Excel guys please. I would like to have an Excel spreadsheet put together that can easily graph an equity curve over a period of trades, and the inputs would allow for examining different trading scenarios based on win percentage vs risk. The monte carlo aspect would show us the variation from run to run.

We all know that high win rates appeal to the majority of traders, but unfortunately most of these traders stop right there and focus solely on win percentage without considering the risk per trade (R-multiple, or win/loss dollar).

That said, there can be some statistical advantages to higher win rate systems when using compounded position sizing.

What I would like to do is have an Excel spreadsheet that quickly demonstrates this. I believe the inputs would simply be:

1) Number of trades [and use monte carlo simulations on top of this], I am thinking 1,000 is a nice number.
2) Win percentage, ie 60%.
3) Amount risked per trade, ie $150.00 = 1R
4) Profit target per trade, ie $200.00 = 1.33R
5) Per trade expenses, ie commission and slippage say $4.00 + $12.50 = $16.50 per round turn.

Each trade will either win or lose.

In my head, what I picture is the monte carlo giving us the ability to see how different cycles of the trade run/path will result in different end results or products, or equity curves.

In addition to the equity curve plot distribution over the trades, we should have a simple end result graph as well that shows us our maximum drawdown as well as our high-water mark, and our total expectancy.

What I wanted was for someone to be able to quickly punch in different scenarios for win percentage and see how it can change the outcomes.

I hope I am not asking too much?

Any takers? I am going to mention @vvhg only because he is the best Excel guy I know, but there are many of you guys on the forum, so I hope I can ask this favor for you guys to come together and make this.

Thx.

Mike

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/

Visit other sites? Please spread the word about your experience with our community!
Follow me on Twitter Visit my futures io Trade Journal Started this thread Reply With Quote
The following 2 users say Thank You to Big Mike for this post:

Journal Challenge April 2021 results (now extended!):
Competing for $1800 in prizes from Jigsaw
looks_oneMaking a Living with the Microsby sstheo
(602 thanks from 60 posts)
looks_twoSalao's Journalby Salao
(147 thanks from 26 posts)
looks_3Learning to Profit - A journey in algorithms and optionsby Syntax
(112 thanks from 26 posts)
looks_4Deetee’s DAX Trading Journal (time based)by Deetee
(94 thanks from 30 posts)
looks_5Maybe a little bit different journalby Malykubo
(46 thanks from 29 posts)
 
Best Threads (Most Thanked)
in the last 7 days on futures io
I finally blew up an account
524 thanks
The Crude Dude Oil Trading System
72 thanks
Spoo-nalysis ES e-mini futures S&P 500
63 thanks
The tiyfTradePlanFactory indicator
23 thanks
Building a Crypto Mining Rig
18 thanks
 
(login for full post details)
  #3 (permalink)
 vvhg 
Northern Germany
 
Experience: Intermediate
Platform: NT
Trading: FDAX, CL
 
vvhg's Avatar
 
Posts: 1,583 since Mar 2011
Thanks: 1,016 given, 2,816 received


I found this with a quick search: Risk of Ruin

But I guess it's not exactly what you mean. What should be reasonably easy is to hook that up with the Monte Carlo engine in the journal. So this generates the "original" trades and the engine from the journal makes the iterations and graphs...

vvhg

Hic Rhodos, hic salta.
Reply With Quote
The following user says Thank You to vvhg for this post:
 
(login for full post details)
  #4 (permalink)
 vvhg 
Northern Germany
 
Experience: Intermediate
Platform: NT
Trading: FDAX, CL
 
vvhg's Avatar
 
Posts: 1,583 since Mar 2011
Thanks: 1,016 given, 2,816 received

Here is a quick and dirty version.

On Sheet1 fill out the red cells, hit recalc. Then switch over to the Monte Carlo tab, functionality of it is unchanged.
Please be aware that the trades are calculated as percentage of account balance (account balance can be changed in cell C3).

The file is rather large as I didn't bother to delete all the other tabs of the journal (would require lots of fiddling with the VB code behind the scenes). They are still there as I only hid them, if this causes any problems, give me a shout...

vvhg

P.S.

Big Mike View Post
Any takers? I am going to mention @vvhg only because he is the best Excel guy I know, but there are many of you guys on the forum, so I hope I can ask this favor for you guys to come together and make this.

I'm sorry that I have to correct you, I'm not that good with Excel at all. I'm just really good with Google and copy/paste

Hic Rhodos, hic salta.
Attached Files
Register to download File Type: rar Monte Carlo standalone2.rar (1.77 MB, 211 views)
Reply With Quote
The following 6 users say Thank You to vvhg for this post:
 
(login for full post details)
  #5 (permalink)
dryg
France
 
 
Posts: 38 since May 2012
Thanks: 5 given, 17 received


Big Mike View Post
We all know that high win rates appeal to the majority of traders, but unfortunately most of these traders stop right there and focus solely on win percentage without considering the risk per trade (R-multiple, or win/loss dollar).

Mike you may want to take a look at this simulator from the web archives (not available on the website any longer):

Random Equity Curve Simulator of a trading system. Learn it before you trade

I think it does what you are looking for.

Also, check out Michael Harris' work:

The Most Important Performance Measure of Trading Strategies | Price Action Lab Blog

Reply With Quote
The following 2 users say Thank You to dryg for this post:
 
(login for full post details)
  #6 (permalink)
 Big Mike 
Site Administrator
Swing Trader
Data Scientist & DevOps
Manta, Ecuador
 
Experience: Advanced
Platform: Custom solution
Trading: Futures & Crypto
 
Big Mike's Avatar
 
Posts: 50,068 since Jun 2009
Thanks: 32,534 given, 98,490 received


vvhg View Post
Here is a quick and dirty version.

Excellent @vvhg, it looks to have most of what I was wanting. I will spend some time with it.

Mike

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/

Visit other sites? Please spread the word about your experience with our community!
Follow me on Twitter Visit my futures io Trade Journal Started this thread Reply With Quote
The following user says Thank You to Big Mike for this post:
 
(login for full post details)
  #7 (permalink)
 Big Mike 
Site Administrator
Swing Trader
Data Scientist & DevOps
Manta, Ecuador
 
Experience: Advanced
Platform: Custom solution
Trading: Futures & Crypto
 
Big Mike's Avatar
 
Posts: 50,068 since Jun 2009
Thanks: 32,534 given, 98,490 received

@vvhg,

Here is an example:



In this example, there were 21 consecutive losers in a string of trades. How can I get a graph that shows me this in some sort of distribution so I know that there is a 90% chance of a certain figure, for example? And 80% chance of a different value, 70% chance, etc.

Also an example of what I was speaking to earlier in post 1 --- but here is a 50% win rate system, with 12 losers in a row. How many traders would take all 12 of those losing trades without starting to wonder if their system was broken... the exact reason behind analysis of this nature.



Mike

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/

Visit other sites? Please spread the word about your experience with our community!
Follow me on Twitter Visit my futures io Trade Journal Started this thread Reply With Quote
The following user says Thank You to Big Mike for this post:
 
(login for full post details)
  #8 (permalink)
 trendisyourfriend 
Legendary Market Wizard
Quebec
 
Experience: Intermediate
Platform: NinjaTrader wt Rancho Dinero's profiling tools
Broker: AMP/CQG
Trading: ES, NQ, YM
 
trendisyourfriend's Avatar
 
Posts: 4,021 since Oct 2009
Thanks: 3,691 given, 5,199 received

Why don't you go at RANDOM.ORG - True Random Number Service and request a series of 1,0 etc. in the % that interests you to evaluate the max. number of consecutive 0 or 1 you can get? This way you'll be able to test different drawdown in terms of % very quickly.

Reply With Quote
The following user says Thank You to trendisyourfriend for this post:
 
(login for full post details)
  #9 (permalink)
 vvhg 
Northern Germany
 
Experience: Intermediate
Platform: NT
Trading: FDAX, CL
 
vvhg's Avatar
 
Posts: 1,583 since Mar 2011
Thanks: 1,016 given, 2,816 received


Big Mike View Post
@vvhg,

Here is an example:



In this example, there were 21 consecutive losers in a string of trades. How can I get a graph that shows me this in some sort of distribution so I know that there is a 90% chance of a certain figure, for example? And 80% chance of a different value, 70% chance, etc.

Also an example of what I was speaking to earlier in post 1 --- but here is a 50% win rate system, with 12 losers in a row. How many traders would take all 12 of those losing trades without starting to wonder if their system was broken... the exact reason behind analysis of this nature.



Mike

Well, that is a relatively simple probability question...
There is a thread about coin toss, I think there was also a formula in that thread for an unfair coin toss. I'm not at my computer, but I'll have a look in a few hours...if @Fat Tails has not pulled the formula out of his hat by then...

In the real world max draw down would probably be more important than string probabilities. These are easier to simulate, hence the Monte Carlo engine. The lower right graph shows the drawdown probabilities over the selected number of trades.(i think it's that one, but its named draw down so it's not hard to find.

As to how many traders would take the 12 losers without flinching, it is much more complicated to calculate, but the answer is 42

Vvhg

Hic Rhodos, hic salta.
Reply With Quote
The following 3 users say Thank You to vvhg for this post:
 
(login for full post details)
  #10 (permalink)
 vvhg 
Northern Germany
 
Experience: Intermediate
Platform: NT
Trading: FDAX, CL
 
vvhg's Avatar
 
Posts: 1,583 since Mar 2011
Thanks: 1,016 given, 2,816 received


I have found the formula for it, but I haven't implemented it yet.


N being the number of trades.
K being the number of consecutive losses
p being the probability of a loss on any single trade



vvhg

Hic Rhodos, hic salta.
Reply With Quote
The following 2 users say Thank You to vvhg for this post:
 
(login for full post details)
  #11 (permalink)
 vvhg 
Northern Germany
 
Experience: Intermediate
Platform: NT
Trading: FDAX, CL
 
vvhg's Avatar
 
Posts: 1,583 since Mar 2011
Thanks: 1,016 given, 2,816 received

After having tried the above formula, it doesn't seem to produce correct results. So either the formula or my implementation is wrong. Maybe it is easier to get the desired values via the Monte Carlo.

vvhg

Hic Rhodos, hic salta.
Reply With Quote
The following user says Thank You to vvhg for this post:
 
(login for full post details)
  #12 (permalink)
 Big Mike 
Site Administrator
Swing Trader
Data Scientist & DevOps
Manta, Ecuador
 
Experience: Advanced
Platform: Custom solution
Trading: Futures & Crypto
 
Big Mike's Avatar
 
Posts: 50,068 since Jun 2009
Thanks: 32,534 given, 98,490 received


vvhg View Post
After having tried the above formula, it doesn't seem to produce correct results. So either the formula or my implementation is wrong. Maybe it is easier to get the desired values via the Monte Carlo.

vvhg

Thanks for your efforts @vvhg.

Mike

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/

Visit other sites? Please spread the word about your experience with our community!
Follow me on Twitter Visit my futures io Trade Journal Started this thread Reply With Quote
The following user says Thank You to Big Mike for this post:
 
(login for full post details)
  #13 (permalink)
 Rad4633 
Greensboro NC
 
Experience: None
Platform: TOS/ NT Dorman
Trading: ES TF CL
 
Rad4633's Avatar
 
Posts: 1,352 since Sep 2011
Thanks: 2,651 given, 893 received

Thx @Big Mike and @vvhg I like numbers more than words in trading, so I ll be watching as you guys progress with this

Visit my futures io Trade Journal Reply With Quote
The following user says Thank You to Rad4633 for this post:
 
(login for full post details)
  #14 (permalink)
 geott 
France
 
Experience: Advanced
Platform: MT4/NT
Trading: YM, DAX, Fx, CFD
 
Posts: 25 since Oct 2010
Thanks: 18 given, 40 received


trendisyourfriend View Post
Why don't you go at RANDOM.ORG - True Random Number Service and request a series of 1,0 etc. in the % that interests you to evaluate the max. number of consecutive 0 or 1 you can get? This way you'll be able to test different drawdown in terms of % very quickly.

yes i would take the same way but with a serie with a range of [0;100] to condition his W/L probability.

Reply With Quote
The following user says Thank You to geott for this post:
 
(login for full post details)
  #15 (permalink)
jerrick
Singapore
 
 
Posts: 1 since Jan 2013
Thanks: 0 given, 1 received

hi,

I wasn't able to download the stuff. Does anyone knows where I can get a spreadsheet to run the figures (risk/reward, winning percentage etc?)

Reply With Quote
The following user says Thank You to jerrick for this post:
 
(login for full post details)
  #16 (permalink)
 Fatfish 
South Africa
 
Experience: Beginner
Platform: Jigsaw
Broker: AMP, CQG
Trading: ES
 
Posts: 18 since Oct 2013
Thanks: 54 given, 22 received

Hi, just recently joint the group, so although a late, I thought I'd post my version of an Excel Monte Carlo here.

It generates 1000 random numbers (with the Excel function) and generates trade results from these.

There is a macro included to write and plot 50 iterations over 1000 trades.
Plots for single iteration equity curves and underwater lines are drawn for 100, 250, 500 and 100 trades.
Envelopes for 50 iterations of 100, 250, 500 and 1000 trades are plotted.

It has fields for the following:

1. Initial account balance,
2. Maximum allowed gearing (which determines the number of contracts if fractional position sizing is selected)
3. No of winning trades from your trading / backtesting records
4. No of losing trades
5. No of break-even trades
6. Average winning trade (ticks)
7. Average losing trade (ticks)

I also incorporated inputs for trading costs, as these can take big bites out of the pie:
8. Bid / ask spread
9. Slippage
10. Brokerage fees
11. Exchange fees
12. Platform fees

Note that break even trades will show up as losses because of points 8 to 12.

I'd appreciate any feed back especially with regards to points 8 to 12.

Attached Files
Register to download File Type: rar Monte Carlo_rev1 - Copy.rar (2.21 MB, 88 views)
Reply With Quote
The following user says Thank You to Fatfish for this post:
 
(login for full post details)
  #17 (permalink)
 alejo 
madrid spain
 
Experience: Beginner
Platform: nt
Trading: None.
 
alejo's Avatar
 
Posts: 1,311 since Apr 2013
Thanks: 16,557 given, 643 received


Fatfish View Post
Hi, just recently joint the group, so although a late, I thought I'd post my version of an Excel Monte Carlo here.

It generates 1000 random numbers (with the Excel function) and generates trade results from these.

There is a macro included to write and plot 50 iterations over 1000 trades.
Plots for single iteration equity curves and underwater lines are drawn for 100, 250, 500 and 100 trades.
Envelopes for 50 iterations of 100, 250, 500 and 1000 trades are plotted.

It has fields for the following:

1. Initial account balance,
2. Maximum allowed gearing (which determines the number of contracts if fractional position sizing is selected)
3. No of winning trades from your trading / backtesting records
4. No of losing trades
5. No of break-even trades
6. Average winning trade (ticks)
7. Average losing trade (ticks)

I also incorporated inputs for trading costs, as these can take big bites out of the pie:
8. Bid / ask spread
9. Slippage
10. Brokerage fees
11. Exchange fees
12. Platform fees

Note that break even trades will show up as losses because of points 8 to 12.

I'd appreciate any feed back especially with regards to points 8 to 12.

i try to running but on contracts always say 0
any help?
thanks

alejo

Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #18 (permalink)
 DNFX 
Plovdiv Bulgaria
 
Experience: None
Platform: NinjaTrader , TradeStation
Trading: Forex
 
DNFX's Avatar
 
Posts: 3 since Nov 2013
Thanks: 9 given, 2 received

@Big Mike I'd like to share my point of view about the subject. Copule of months ago I did same research, and you are right. I make 4 trading sistems, 2 with compound and 2 without. I use R-multiples so I can use a ROI formula for seek of simplicity(ROI = win% in decimals*R, if subtract 1 you'll get your edge in percents), which is same as M(E) with R:R ratio. So i just set up system A for 80% win and R= 1.5 which is equal to ROI = 1.2, and system B with parameters 30% win rate and R= 4 with the same ROI of 1.2. The outcome was that 2 systems with same edge and compound with same amount (1% of actual capital), more profitable is the system with higher win rate. It's all about how high frequently compound. I make Excel sheet to proof that statement. I don't add spread and commissions in math, but I think it's no need, if you measure the moves in money not in pips. I'll be happy if I help.

Attached Files
Register to download File Type: xlsx Proof.xlsx (195.5 KB, 74 views)
Reply With Quote


futures io Trading Community Psychology and Money Management > Win Percentage and Expectancy example, Excel equity curve graph with Monte Carlo


Last Updated on November 26, 2013


Upcoming Webinars and Events

NinjaTrader Indicator Challenge!

Ongoing

Journal Challenge w/$1,800 in prizes!

May 7

The Cold Hard Truth: Maybe I Am Not Good Enough w/Chris Gray @ Earn2Trade

Elite only
     



Copyright © 2021 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada), info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts