Site Administrator Swing Trader Data Scientist & DevOps

Manta, Ecuador

Experience: Advanced

Platform: Custom solution

Trading: Futures & Crypto

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.

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.

The following 6 users say Thank You to vvhg for this post:

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.

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:

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.

The following user says Thank You to vvhg for this 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.

The following user says Thank You to Fatfish for this post:

@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.