Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
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 for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
Win Percentage and Expectancy example, Excel equity curve graph with Monte Carlo
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.
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...
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
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.
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.
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
