Win Percentage and Expectancy example, Excel equity curve graph with Monte Carlo
Welcome to futures.io.
Welcome, Guest!
This forum was established to help traders (especially futures traders) by openly sharing indicators, strategies, methods, trading journals and discussing the psychology of trading.
We are fundamentally different than most other trading forums:
We work extremely hard to keep things positive on our forums.
We do not tolerate rude behavior, trolling, or vendor advertising in posts.
We firmly believe in openness and encourage sharing. The holy grail is within you, it is not something tangible you can download.
We expect our members to participate and become a part of the community. Help yourself by helping others.
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, and we will never resell your private information.
Win Percentage and Expectancy example, Excel equity curve graph with Monte Carlo
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
Due to time constraints, please do not PM me if your question can be resolved or answered on the forum.
Need help? 1) Stop changing things. No new indicators, charts, or methods. Be consistent with what is in front of you first. 2) Start a journal and post to it daily with the trades you made to show your strengths and weaknesses. 3) Set goals for yourself to reach daily. Make them about how you trade, not how much money you make. 4) Accept responsibility for your actions. Stop looking elsewhere to explain away poor performance. 5) Where to start as a trader? Watch this webinar and read this thread for hundreds of questions and answers. 6) Help using the forum? Watch this video to learn general tips on using the site.
If you want to support our community, become an Elite Member.
The following 2 users say Thank You to Big Mike for this post:
Quick Summary is created and edited by users like you... Add FAQ's, Links and other Relevant Information by clicking the edit button in the lower right hand corner of this message.
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.
The following user says Thank You to vvhg for this post:
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.
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.
Last edited by vvhg; October 7th, 2012 at 09:11 AM.
The following 6 users say Thank You to vvhg for this post:
Excellent @vvhg, it looks to have most of what I was wanting. I will spend some time with it.
Mike
Due to time constraints, please do not PM me if your question can be resolved or answered on the forum.
Need help? 1) Stop changing things. No new indicators, charts, or methods. Be consistent with what is in front of you first. 2) Start a journal and post to it daily with the trades you made to show your strengths and weaknesses. 3) Set goals for yourself to reach daily. Make them about how you trade, not how much money you make. 4) Accept responsibility for your actions. Stop looking elsewhere to explain away poor performance. 5) Where to start as a trader? Watch this webinar and read this thread for hundreds of questions and answers. 6) Help using the forum? Watch this video to learn general tips on using the site.
If you want to support our community, become an Elite Member.
The following user says Thank You to Big Mike for this post:
Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).
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.
Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).
Mike
Due to time constraints, please do not PM me if your question can be resolved or answered on the forum.
Need help? 1) Stop changing things. No new indicators, charts, or methods. Be consistent with what is in front of you first. 2) Start a journal and post to it daily with the trades you made to show your strengths and weaknesses. 3) Set goals for yourself to reach daily. Make them about how you trade, not how much money you make. 4) Accept responsibility for your actions. Stop looking elsewhere to explain away poor performance. 5) Where to start as a trader? Watch this webinar and read this thread for hundreds of questions and answers. 6) Help using the forum? Watch this video to learn general tips on using the site.
If you want to support our community, become an Elite Member.
The following user says Thank You to Big Mike for this 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.
The following user says Thank You to trendisyourfriend for this post:
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.
The following 3 users say Thank You to vvhg for this post: