Trading PnL Monte Carlo - googlesheets implementation

[rcjr 2]

There was some discussion on win rates and risk reward ratios on the BBT main chat the other day. I'd always toyed with the idea of building a spreadsheet simulator using monte carlo type modelling, as it's fairly easy and quick to do. 

The primary reason (for myself) was to run through multiple scenarios of the same Reward to Risk setup and Expected Win percentages and see how PnL fluctuates with various levels of amounts risked. It would help determine a good risk % that one would be comfortable for your trading strategy. I also think it would be a good from a psychological standpoint when you see your strategy hit a string of losses, and you would feel like throwing away the strategy. You will see some crazy drawdowns , if you run the simulation a few times, even when you have a positive expectation edge. It should put things in perspective from a confidence standpoint.

I'm not much of an excel guy, but i figured google spreadsheet would be the easiest way for everyone to have access to this. Please note that all cells are protected and you would need to make a copy of the spreadsheet in your own google drive account to enable you to plug your own numbers in.  An easy way to rerun the simulation is to click on a blank cell and hit the delete key. I tried adding in a button and it was running even slower than normal, so I just got rid of it.

It is based in google sheets, so a bit slow and takes a few seconds for each run to complete. I'm sure the googlesheet functions are not the most efficient and can be tweaked, but I haven't spent too much time on it. Feel free to modify and post updated versions if you like.

There's a readme tab that describes the fields that you can edit and play with. I've limited the trade count a range of 10-2000. you can change that if you want.

Here's the link

https://docs.google.com/spreadsheets/d/1xEuIvMc5wBNLGu5rdNohRONaVg30NbmUc_payNaUgXo/edit?usp=sharing

Hope you find it useful.

[Abiel 475]

Great tool to understand  that there is a random distribution between wins and losses for any given set of variables that define an edge (Mark Douglas).  This tool and your journal will show if you are in the right path or not. 

Win Rate % & Return/Risk Ratio are crucial, if both are acceptable you are doing good, if both are great you are doing great, if one is big an the other is acceptable you are doing ok, if both are low you are losing your time and your money and it is time to go back to sim and build your edge. 

[Abiel 475]

I just ran a simulation for my trading in june. As every loser in the simulation = -1R my balance is red. Most of my "losers" are <-1R or B/E, maybe adding a variable of "average loser -R" would be more realistic. Then you can use -1R to see how honoring your stop loss will keep you away from losing big, and using a value > 1R you will understand that if you don't respect your stop loss you have no place in the trading business. 

[edequev 1]

Great tool,  thanks for sharing !!! 

[rcjr 2]

32 minutes ago, edequev said:

Great tool,  thanks for sharing !!! 

Thank You. It's a starting point. I might add a few more statistical risk metrics when I have the time.

[rcjr 2]

4 hours ago, Abiel said:

I just ran a simulation for my trading in june. As every loser in the simulation = -1R my balance is red. Most of my "losers" are <-1R or B/E, maybe adding a variable of "average loser -R" would be more realistic. Then you can use -1R to see how honoring your stop loss will keep you away from losing big, and using a value > 1R you will understand that if you don't respect your stop loss you have no place in the trading business. 

I'm not sure I understand what variable R would be in this context.

In this case -1R would be the average loss you're expecting to take per trade, if it goes south on you. Given your capital, the percentage allocation would be the amount you're willing to Risk on average.

If you have a variable R per trade and a variable take profit, you would need to average it out on both sides to get the risk reward your looking for.  For example, if you lose $100 on average but make $500 on average on the winners then you can set your ratio to 5.

This simulation is not sophisticated enough to break down trades into ranges of R risked, or ranges of R multiples of take profits either. It's pretty basic.  

Edited July 4, 2020 by rcjr