MonteCarlo Simulations of Trade Data in Excel

“What can be tested, must be tested.” -Victor Niederhoffer

Summary

1.  MonteCarlo simulation allows us to find the probability of certain outcomes based on random inputs and repeat this process thousands of times.

2.  First, take your sample data and find the descriptive statistics and frequency distribution using Excel’s descriptive statistics and Histogram functions.

3.  Second, assign a random variable based on the frequency distribution of the sample data.

4.  Third, assign a Normal Random Variable to the random data by using the Mean and Standard Deviation of the random data using the =NORMINV() formula.

5.  Fourth, run the descriptive statistics and the frequency distribution on your randomized data.

6.  Fifth, compare the descriptive statistics and the frequency for the sample data and the randomized data and make conclusions.

If you have been around the financial world even a little bit, you are probably familiar with MonteCarlo simulations.  If not, let me explain.  A MonteCarlo simulation is a problem solving technique used to approximate the probability of certain outcomes by running multiple trial runs, called simulations, using random variables.  Investopedia.  Monte Carlo simulation enables us to model situations that present uncertainty and play them out thousands of times on a computer.  Use of a random variable ensures that we are not biasing the results – that is, we are leaving up to chance whether the outcome fits within our parameters.

Excel is a popular tool to generate random variables.  Use of the =Rand() formula in a cell returns a value that is equally likely to return a value between 0 and 1.  By pressing the F9 key, you can reset the value in the cell.

Back to the Coin Toss Example

Remember our fair coin where the odds of getting a head or tails are even?  For this example, let’s say we want to answer 2 questions and we can’t use a formula to find the result.

1.  What are the odds that a coin will come up with heads exactly 7 times when flipped 10 times?

2.  What are the odds that a coin will come up with at least 7 heads when flipped 10 times?

Using the =Randbetween() formula in Excel will help us answer this question.  The =Randbetween() formula returns a random result for the entries that we make.  So if we enter:

=Randbetween(0,1)

Excel will return 0 or 1 randomly.  We can assign Heads to equal 1 and Tails to equal 0.

A summary of building this spreadsheet is excellently presented here.

Here are the results for flipping exactly N number of heads:

Result (exactly)	No. of Trials	Probability	Cumulative Probability
0 Heads	11	0.09%	0.09%
1 Head	137	1.09%	1.17%
2 Heads	515	4.08%	5.25%
3 Heads	1547	12.26%	17.51%
4 Heads	2636	20.89%	38.40%
5 Heads	3074	24.36%	62.76%
6 Heads	2574	20.40%	83.16%
7 Heads	1418	11.24%	94.40%
8 Heads	568	4.50%	98.90%
9 Heads	130	1.03%	99.93%
10 Heads	9	0.07%	100.00%
TOTAL	12608	100%

For the spreadsheet I built, we assumed 12,608 trials of flipping a coin 10 times.  7 heads came up in 1,418 of those trials so, 1,418/12608 = 11.24% chance that heads will come up exactly 7 times in 10 coin flips.  The actual probability for this is 11.7% so our model does a pretty good job of approximating.  The cumulative probability column shows that there is a 94.40% chance that in 10 flips of a coin, you will have 7 or fewer heads show up.  Stated otherwise, there is only a 5.6% chance of 8 or more heads will show up when you flip a coin 10 times.

This chart is what is referred to a histogram and shows the number of occurrences of exactly N number of heads in our 12,608 iterations (where 1 iteration is 10 coin flips).  Notice the bell shape?  That is because this is a normal distribution.  More on that later.

This chart shows the individual and cumulative probability of flipping N heads in 10 coin flips.

Here is the results for the test where we are looking for at least 7 or more heads in 10 coin flips:

Results	No. Trials	Probability
at least 1 head	12608	99.91%
at least 2 heads	12471	98.83%
at least 3 heads	11956	94.75%
at least 4 heads	10409	82.49%
at least 5 heads	7773	61.60%
at least 6 heads	4699	37.24%
at least 7 heads	2125	16.84%
at least 8 heads	707	5.60%
at least 9 heads	139	1.10%
at least 10 heads	9	0.07%
greater than 10	0	0.00%

Thus, in 12,608 trials, the odds of flipping 7, 8, 9 or 10 heads in 10 coin flips is 16.84%.  The actual result is 17.2% so our model again does a fair job of predicting the actual odds.  If we had run the iteration say 100,000 times, our probability formula would have gotten closer and closer to the actual result. 

Applying Monte Carlo Simulations to Trading Data

Monte Carlo simulation is a statistical tool. The primary to use it in trading is to monitor a strategy. For example, if we have 300 trades in a market, we have a set of data. We

can then take sample from the data set over and over again to build up a trading record that didn’t actually happen (by using random variables), but could have potentially happened.  You then compare the hypothetical track record to some back tested results and to the actual results, and they should all match. If they don’t match, and the real time trading is doing significantly poorly, then we can only assume that that strategy needs to stop being traded.

Let’s say that I have a trading system that trades the E-Mini S&P 500 contract.  The trading system has completed 306 trades and has the following profit probabilities:

Points	Count	Probability	Cumulative Probability
0	22	7.31%	7.31%
1	21	6.98%	14.29%
2	49	16.28%	30.56%
3	34	11.30%	41.86%
4	42	13.95%	55.81%
5	32	10.63%	66.45%
6	19	6.31%	72.76%
7	16	5.32%	78.07%
8	16	5.32%	83.39%
9	15	4.98%	88.37%
10	35	11.63%	100.00%

You can read the table as follows:  there is a 7.31% chance that the system will make less than 0 points, there is a 6.98% probability that the system will make between 0 and 1 points, etc.  Or, there is a 14.29% probability that a given trade will make a profit of less 1 point, a 30.56% chance that a given trade will make a profit of less than 2 points, etc.

Remember, that if we are to accept that the market has a random distribution of outcomes, then using a random variable to determine a given outcome should align closely with the trade data that we have.

Step 1 – Find the descriptive statistics of the sample data

Using your raw data from your trading statistics, let Excel do the heavy lifting for you and run the descriptive statistics.  For a quick outline on how to run descriptive statistics in Excel, click here.

Raw Data
Mean	4.64951
Standard Error	0.211601
Median	3.75
Mode	0
Standard Deviation	3.701505
Sample Variance	13.70114
Kurtosis	0.359983
Skewness	0.984527
Range	15.75
Minimum	0
Maximum	15.75
Sum	1422.75
Count	306

A quick review of the descriptive statistics output above:

-  The Mean is the average profit for all of the trades.  The trading system averaged 4.65 points for every trade it took.

-  The Median is the “number in the middle” ie if you were to number all the profits from high to low, what is the number that falls in the middle of the sample.

-  The Mode is the most frequently repeated number in the data.

-  The Minimum and Maximum is the lowest and highest number in the sample.

-  The Count is the total number of trades you took.

-  The Range is the Maximum minus the Minimum.

-  The Sample Variance is a measure of the data from the Mean – it is essentially the average of the squared distance from the Mean.  A higher sample variance means more dispersion from the Mean.

-  The Standard Deviation is the square root of the variance.  It measures how close the data is to the Average or Mean.

-  The Standard Error indicates how close the Mean is to the true population and is calculated by dividing the standard deviation by the square root of the total number of observations.  Standard Error gives us confidence intervals surrounding the average:

Z	% Certainty	Lower Bound	Upper Bound
1 (.99)	68%	4.44	4.86
2 (1.96)	95%	4.23	5.07
3 (2.58)	99%	4.01	5.07

Step 2 – Assign a random variable to each profit/loss point target

We can then associate a profit target probability with a given random variable.  Recall that Excel will generate a random number between 0 and 1 by using the =Rand() formula. The table below applies a random number generated by Excel and returns a given profit objective.

Points	Count	Probability	Cumulative Probability	Random Number Assigned
0	22	7.31%	7.31%	Less than .0731
1	21	6.98%	14.29%	Greater than or equal to .0731 and less than .1429
2	49	16.28%	30.56%	Greater than or equal to 14.29 and less than .3056
3	34	11.30%	41.86%	Greater than or equal to .3056 and less than .4186
4	42	13.95%	55.81%	Greater than or equal to .4186 and less than .5581
5	32	10.63%	66.45%	Greater than or equal to .5581 and less than .6645
6	19	6.31%	72.76%	Greater than or equal to .6645 and less than .7276
7	16	5.32%	78.07%	Greater than or equal to .7276 and less than .7807
8	16	5.32%	83.39%	Greater than or equal to .7807 and less than .8339
9	15	4.98%	88.37%	Greater than or equal to .8339 and less than .8837
10	35	11.63%	100.00%	Greater than or equal to .8837

For example, a random number of “.06” will return a point value of “0”.  Running the random number generator a number of times will ensure that we get 7.31% of our outcomes to equal “0” points.

The key to this is to create two tables.  The first table is as follows:

Vlookup Table
0	0
0.07309	1
0.142857	2
0.305648	3
0.418605	4
0.55814	5
0.664452	6
0.727575	7
0.780731	8
0.833887	9
0.883721	10

This table will act as your random number generator lookup table.  Notice that it corresponds to the table above.

The second table is as follows (remember this table goes down 10,000 rows):

Trial	Rand	Result
1	0.4346237	4
2	0.4944565	4
3	0.0626749	0
4	0.6907086	6
5	0.1438746	2
6	0.5635747	5
7	0.8293976	8
8	0.9459425	10
9	0.7881337	8
10	0.8844642	10

The column labeled “Trial” is simply the trial or experiment number.  The column labeled “Rand” is where we entered the formula =Rand() to return a random number between 0 and 1.  The “Result” column is where we entered the following formula:

=VLOOKUP (Rand , VLookupTable, 2)

Let me break down this formula.  =VLOOKUP() is a formula that searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.  The formula above tells Excel the following:

1.  Search for the Random number generated in the “Rand” column

2.  Search in the first column of the VlookupTable

3.  Return the value in the second column of VlookupTable as the “Result”

The cool thing about =VLOOKUP() is that it looks for a value that is equal to exactly what you are looking for and if it can’t find it, it returns the closest match so long as it is not greater than the value it is searching for.

So for Trial #1 above, Excel searches for the value .4346237 in the VLookupTable and returns the Value 4.  This is exactly what we wanted – remember 4 points is associated with a probability greater than or equal to .4186 and less than .5584.

Make sure you have enough iterations on your spreadsheet.  For this example, I made 10,000 iterations of the above test.

Step 3 – Create a Normal Random Variable for your Random Data

The next step is to create a Normal Random Variable for the Random Data.  In probability theory, the normal (or Gaussian) distribution is a continuous probability distribution that has a bell-shaped probability density function, known as the Gaussian function or informally the bell curve.  Excel will simulate a normal random variable with a Mean and Standard Deviation that you calculate by using the formula:

 =NORMINV (rand(), Mean, StandardDeviation)

First, calculate the Mean and Standard Deviation of the “Rand” column using the =AVERAGE() and =STDEV() functions in excel.  For my random distribution, I found the following:

Mean	4.61
St Dev	3.03

Second, for each random Trial, calculate a Normal Random Variable using the =NORMINV() function and the average and standard deviation you just calculated.  You will then have a table that looks like this (and goes down 10,000 rows):

Trial	Rand	Result	Normal Rv
1	0.4346237	4	4.111805545
2	0.4944565	4	4.569135141
3	0.0626749	0	-0.039411122
4	0.6907086	6	6.121969224
5	0.1438746	2	1.385592354
6	0.5635747	5	5.096909138
7	0.8293976	8	7.499332975
8	0.9459425	10	9.486621837
9	0.7881337	8	7.038646925
10	0.8844642	10	8.245218044

A little bit of explanation on the Normal Rv column – for Trial No. 1, you are telling Excel to find the 43^rd percentile of a normal random variable with a with an average (mean) of 4.61 and a standard deviation of 3.03.

Step 4 – Get Descriptive Statistics and Frequency Distributions for the Random Data

Now the fun part – we are going to compare the original raw data with the random test results to see if our sample data.

First, run descriptive statistics and Frequency or Histogram on the random test data.

We get this Frequency distribution for the random trials:

Points	Frequency	Individual %	Cumulative %
0	620	6.20%	6.20%
1	517	5.17%	11.37%
2	746	7.46%	18.83%
3	1036	10.36%	29.19%
4	1283	12.83%	42.02%
5	1339	13.39%	55.41%
6	1250	12.50%	67.91%
7	1078	10.78%	78.69%
8	809	8.09%	86.78%
9	605	6.05%	92.83%
10	338	3.38%	96.21%
More	379	3.79%	100.00%

Here is the Histogram chart:

Here are the random data descriptive statistics compared with the sample data’s descriptive statistics:

	Random Data	Sample Data
Mean	4.62	4.65
Standard Error	0.03	0.21
Median	4.56	3.75
Mode	#N/A	0.00
Standard Deviation	3.02	3.70
Sample Variance	9.11	13.70
Kurtosis	0.09	0.36
Skewness	0.03	0.98
Range	24.93	15.75
Minimum	-7.35	0.00
Maximum	17.57	15.75
Sum	46195.60	1422.75
Count	10000.00	306.00

Here are the two Histogram charts compared:

Step 5 – Draw conclusions from the data

The Mean or average in both cases was close 4.62 for the random data and 4.65 for the sample data.  This tells us that we will average about 4.6 points if we trade this system enough times.

The Standard Deviation was much higher for the Sample Data – which was to be expected because in the real world, our returns will vary quite a bit more.  For the Sample Data, we can conclude that about 68% of our trades will be +/- 3.06 points around the average of 4.65.

Looking at the Histogram, we can also see that the Sample Data was skewed to the left – meaning that it is more likely to get a return of less than 4 points.  In fact, we have a 55.81% chance of getting a return less than 4 points in our sample data.  In the randomized data, this was only likely 42% of the time.

Putting All of Our Trade Statistics Together

“Insanity: doing the same thing over and over again and expecting different results.” Albert Einstein

In the last couple of posts, I have outlined a number of statistics we can use to evaluate the performance of a trading system.  In this post, I will outline the usage of an Excel document I use to evaluate trading systems and introduce a couple of more statistics to use to tell us if we have an effective trading strategy or not.  I will do this by reviewing a trading system that I traded live and failed miserably with.  But I don’t consider it a failure; in fact, it taught me a lot about what works and what doesn’t and moving forward I want to make sure that I don’t make the same mistake twice.

Inputs for Trading System

The table below outlines the inputs for the trading system that we are going to examine:

Winning Percentage	70.00%
Average Win	50
Average Loss	150
Account Size	15,000
Sample Size	100
Standard Dev (All Trades)	150
Largest Losing Trade	150

Right off the bat, you can probably already tell that this is not the greatest system in the world because the average risk to reward is 3 to 1 (Avg Loss/Avg Win).  But I was totally taken aback that you could win 70% of the time!  I needed to be right and this led to the failure of this specific trading system.

Trading Statistics Calculator: Outputs

To link to this Trading Statistics Calculator on the web, go here.

The outputs for the trading system are given below:

Outputs
	$	%
Expectancy Per Trade	-10.00	-
Edge	-	-0.07%
Fractional Gain (FG)	50	0.33%
Fractional Loss (FL)	150	1.00%
Initial Risk Per Trade (Unit Size)	150	1.00%
Win/Loss Ratio (Profit Ratio)	-	0.33
Capital Units Available	-	100.00
Minimum Win % Required to Be Profitable1	-	75.00%
Maximum Average Loss to Be Profitable3	116.67	0.78%
Minimum Avg Win to Be Profitable2	64.29	0.43%
Risk of Ruin	-	114.26%
t-Test	-	3.33
Optimal F	-	-0.2
Maximum Leverage	-750	-

Expectancy

Expectancy was discussed before and is given by the following formula:

Exp = (Win% * AvgWin) –  ((1-Win%) * AvgLoss)

Where,

Win% = The percent of profitable trades

AvgWin = The average dollar amount made per profitable trade

AvgLoss = The average dollar amount lost per losing trade

For this trading system, Exp was calculated as

Exp = (.7 * 50) * ((1 - .7) * 150) = -10%

A negative expectancy means that you will lose money over multiple trades.  A positive expectancy means that you should make money over multiple trades.  For this system, for every dollar you put into the trade, you were expected to lose 10% or stated another way, for every $150 risked, you were expected to lose $15.  Expectancy does not take into account your account size, only how much you can expect to win or lose over a large sample of trades.

Edge

Edge is similar to expectancy, in that it predicts your average return per trade.  However, Edge takes into account your initial account size and, for this example, assumes that you will not vary your risk as your account increases or decreases.

Edge = FG * Win% - FL * (1 – Win%)

Where,

FG = Fractional Gain or Profit Per Trade = AvgWin / Account Size

FL = Fraction Loss or Loss Per Trade = AvgLoss / Account Size

Win% = The percent of profitable trades

For this system, Edge was calculated as:

Edge = (50/1500) * .7 – (150/1500) * (1 - .7) = -.07%

Again, a negative edge means you will lose money and a positive edge means you will make money.  For this system, we were expected to lose an average of .07% of our initial account size for every trade we put on.  Since -.07% of our initial account was -$10, we could expect to have, on average, a balance of $14,000 after 100 trades of this system.  This can be found by the following formula:

Account Balance After N Trades = AcctSize + (AcctSize * Edge *N)

Where,

AcctSize = Initial Account Size

Edge = Formula given above

N = Number of trades

This formula is just an estimate; remember, there is a random distribution of wins and losses for any given set of trades.  So instead of having 70 wins and 30 losses, we could have had 60 wins and 40 losses etc.  These formulas are just used to give us an average number that we can work off of in a probabilistic manner.

Another formula for edge is:

Edge = ((Profit Ratio + 1) * Win%) – 1

Where,

Profit Ratio = Win Loss Ratio = The AvgWin / AvgLoss

Win% = The percent of profitable trades

Minimum Win % Required to Be Profitable

This statistic shows the minimum Win% required to make the trading system profitable, assuming that the Avg Win Per Trade and the Avg Loss Per Trade remain the same.  This was found by plugging winning percentages from 1% to 100% into excel, solving for the expectancy, graphing the results and finding where the Exp was greater than 0.

You can see from that graph below that any Win% greater than 75 would turn the system into a positive Exp system.  This is useful information because we could then determine if we could increase the Win% through optimizing the system or if we should go back to the drawing board and start designing a new trading system.

Maximum Average Loss to Be Profitable

This statistic tells us the minimum average loss the trading system would have to have to make it a positive expectancy system, assuming the Win% and AvgWin remained the same. 

This is found by the following formula:

Max Average Loss = FG * Win% / (1 – Win%)

Where,

FG = Fractional Gain or Profit Per Trade = (AvgWin / Account Size)

Win% = The percentage of profitable Trades

For this trading system, the Max Average Loss the system could sustain and have the system be profitable with a 70% Win% and $50 AvgWin is $116.67.  Anything less than a $116.67 loss would result in a positive Exp system.  This is useful because it tells us (1) where to set our stop loss to create a breakeven system and (2) if more optimization is required to get a smaller AvgLoss.

Minimum Average Win to Be Profitable

The next statistic is the minimum average win to have the system be profitable or have a positive Exp, assuming the Win% and AvgLoss remained the same.

This is found by the following formula:

Min Avg Win = AcctSize * (1 – Win%)/(1-(1-Win%))

Where,

AcctSize = Account Size

Win% = The percent of profitable trades

For this trading system, assuming the Win% stayed at 70% and AvgLoss stayed at $150, and AvgWin of greater than $64.29 would have turned the system into a positive expectancy system.  This is good to know because we can then go back and optimize the trading system to make the profit target larger.

Risk of Ruin

The risk of ruin is a great statistic to know to determine how likely it is that your capital will be wiped out with the trade settings you have created.  As you now know, even with a system with positive Exp and a small AvgLoss, it is possible to clear out your trading account if you encounter a large string of losses.  The risk of ruin statistic attempts to quantify this probability.

The formula is:

Risk of Ruin = ((1-Edge)/(1+Edge))^Capital Units

Where,

Edge = As calculated above

Capital Units = AcctSize / AvgLoss

You are looking for a risk of ruin as close to zero as possible.  A risk of ruin of 114% for this trading system virtually ensures that if we trade this system enough, we will eventually blow out our account.  Risk of ruin is a great statistic when optimizing your system because it can tell you that you either need increase your edge or increase your account size (or both).

We already know what the Optimal F, T-test and maximum leverage calculations are from previous posts.  Remember, Optimal F and maximum leverage determines the size of your account and the T-test determines whether your results were obtained by chance.  You can find those definitions here.

Optimal Risk

The last two statistics are under the heading “Optimal” risk and are given in the table below.  These two formulas below define the “optimum” percentage of capital to place at risk for your system.  I use “optimum” in quotes because there is a lot of controversy surrounding both of these equations and whether you should use them.

Optimal Risk
Leibfarth Formula	-0.02%
Kelly Formula	-20.00%

Leibfarth % = Edge * FG / FL

Where,

Edge = Edge as calculated above

FG = Fractional gain on each trade

FL = Fractional loss on each trade

Kelly % = Win% – [(1 – Win%) / Win/Loss Ratio]

Where,

Win% = The percentage of profitable trades

Win/Loss Ratio = Profit Ratio = AvgWin / AvgLoss

A negative result on either the Leibfarth% or the Kelly% means that you shouldn’t risk any money using the system.  In our trading system, we get -.02% and -20% respectively for each equation.  This means we do not have a tradeable system.

For more on why the Kelly % is not a tradeable formula, click here.

Edge v Win/Loss Ratio Chart

The chart below gives you a good idea of what it takes to have a system with a positive Edge.  The y-axis shows the projected Edge.  The x-axis is the win/loss ratio or profit ratio.  Each line represents a given Win%.

For example, look at the second line from the top.  This assumes a 70% Win% on all trades.  If we were testing, what Win/Loss ratio should we be shooting for to make it a system with a positive edge?  You can easily find it by looking at the chart – we are looking for a system with a Win/Loss ratio greater than 0.4.  That is, the system’s AvgWin divided by the systems AvgLoss would have to be greater than 0.4 to have a positive edge – something like an AvgWin of $40 and an AvgLoss of $100.  This tells us to to disregard any system with a Win% less than 30% because no matter how large your Win/Loss ratio, the system will never have a positive edge (unless the Win/Loss ratio approached 4 to 5 which is very high).

Minimum Sample Size, Were Results Obtained by Chance and Minimum Account Size Required

To build on the last post, which covered expectancy and R-Multiple distribution, let’s take a look at other important statistical measures.  This will be important when we start building and testing our trading systems.

Summary

1.         Determine the minimum sample size of trades to test a trading system. 

2.         The minimum sample size will ensure that you have an acceptable margin of error (+/- 5% generally) and that the sample results are within 95% (2 standard deviations) of the average value of the entire population.

3.         The T-Test determines if your results occurred by chance.

4.         Optimal F determines the minimum account size required to trade the idea.

Determining Sample Size

Sample size determination is the act of choosing the number of observations to include in a statistical sample. You see a lot of rules of thumb out there for how many trades you should have before you can verify if you have a robust trading system.  I have often seen that you should have “n=30” or “n=20” where n is equal to the number of trades you should have. 

It’s important to distinguish between a sample and a population.  A sample is a subset of a population.  A population is the entire set of values that are potentially observable.  We take a random sample from the population to make estimations about the entire population and make our testing more manageable.  Samples are collected and statistics are calculated from the samples so that one can make inferences or extrapolations from the sample to the population.

Say we want to know how many people in your town drink coffee.  The total population of the town is 20,000 people.  It would be highly impractical to ask all 20,000 people if they drink coffee.  So we could randomly stop 100 or 200 or 1000 people on the street and ask them if they drink coffee.  We can use the formulas below to determine what the random sample tells us about the population.

The sampling margin of error or “level of precision” is the range in which the true value of the population is estimated to be. This range is often expressed in percentage points, (e.g., ±5 percent), in the same way that results for political campaign polls are reported by the media. Thus, in our survey above, if we want to ensure that 50% of people in the sample drink coffee with a precision rate of ±5%, then we would conclude that between 45% and 55% of the sample drink coffee.

The confidence or risk level is based on the Central Limit Theorem (which we will discuss later). The key idea encompassed in the Central Limit Theorem is that when a population is repeatedly sampled, the average value of the attribute obtained by those samples is equal to the true population value. Furthermore, the values obtained by these samples are distributed normally about the true value, with some samples having a higher value and some obtaining a lower score than the true population value. In a normal distribution, approximately 95% of the sample values are within two standard deviations of the true population value (e.g., mean).

If a 95% confidence level is selected, 95 out of 100 samples will have the true population value within the range of precision specified earlier. For example, if we choose a 95% confidence level for our coffee drinkers, then we know that 95% of the population drinks coffee with a level of precision of +/- 5%/  There is always a chance that the sample we obtain does not represent the true population value. This risk is reduced for 99% confidence levels and increased for 90% (or lower) confidence levels.

The degree of variability in the attributes being measured refers to the distribution of attributes in the population. The more heterogeneous a population, the larger the sample size required to obtain a given level of precision. The less variable (more homogeneous) a population, the smaller the sample size. Note that a proportion of 50% indicates a greater level of variability than either 20% or 80%. This is because 20% and 80% indicate that a large majority do not or do, respectively, have the attribute of interest. Because a proportion of .5 indicates the maximum variability in a population, it is often used in determining a more conservative sample size, that is, the sample size may be larger than if the true variability of the population attribute were used.

Sample Size Formula #1

The first minimum sample size formula you can use is as follows:

Ss1 = N / 1 + N * (e) ^ 2

Where

ss = sample size

N = Total Population

e = sampling error required (expressed as a decimal)

Sample Size Formula #2

A second minimum sample size formula that we can use is as follows:

Ss2 = Z^2 * (p) * (1 – p) / C^2

where

ss = sample size

Z = Z-value (for example, use 1.96 for a 95% confidence level and 2.576 for a 99% level)

p = degrees of error (always use .5 for this)

C = sampling error (level of precision), expressed as a decimal (i.e., .04 = +/- 4)

Let’s apply the minimum sample size formulas to the coffee drinking problem above.

Ss1 = 20,000/1 + 20,000 * (.05) ^2 = 392

This tells us that we will need to randomly stop 392 people to have a level of precision of +/- 5%.  If we randomly ask 392 people if they drink coffee and 60% of them say yes, then we can conclude that, of the entire 20,000 people in the town, 12,000 of them probably drink coffee with a level of precision of +/- 600 people or 11,400 to 12,600.

Ss2 = 1.96^2 * .5 * (1-.5) / .05^2 = 372

If 50% of all the people in a population of 20,000 people drink coffee in the morning, and if you were repeat the survey of 372 people ("Did you drink coffee this morning?"), then 95% of the time, your survey would find that between 45% and 55% of the people in your sample answered "Yes". The remaining 5% of the time, or for 1 in 20 survey questions, you would expect the survey response to more than the margin of error away from the true answer. When you survey a sample of the population, you don't know that you've found the correct answer, but you do know that there's a 95% chance that you're within the margin of error of the correct answer.

In trading, because we don’t always know the entire population, it is probably better to use the Ss2 equation.  Say we were testing a system, and need to know how many trades to sample.  We would plug in our required confidence level (generally 95% or 99%) and level of precision (generally +/- 5%) to get our required sample size.  You can see that using a sample size of 30 trades is probably not enough in many cases. 

Determining if the Test Results Occurred by Chance Alone

The t-Test is a simple statistical test to determine if the results of a system occurred by chance alone.  The t-Test is calculated as follows:

t = square root (n) * (ATNP / SDev of All Trades)

where

n = sample size

ATNP = Average Trade Net Profit

SDev of All Trades = Standard Deviation of All Trades

You want to make sure that the t-Test has a value greater than 1.6 (or less than -1.6).  A t-Test of less than 1.6 favors chance and a test greater than 1.6 means you have a tradable idea.

Determining Optimal-F and Maximum Leverage

“Optimal f is the market’s line in the sand for a trading system. Optimal f is the maximum number of contracts you can trade given your account size. Trade any more contracts and your account becomes more and more likely to break under risk of ruin. You don’t have to trade the optimal f number of contracts, but you should never trade more than the optimal f number. The optimal f value, when divided into the largest losing trade for the idea gives the maximum leverage that can be applied to the idea and still avoid risk of ruin. Maximum leverage converts into the minimum account size required to trade n contracts of this idea.”  Henry Carstens, Vertical Solutions.

optimal f = (((1 + win loss ratio) * probability of winning trade) - 1)/ (win loss ratio)

maximum leverage = largest losing trade / optimal f

Example of T-Test, Optimal F and Maximum Leverage

For this example, you are testing a trading idea using 400 sample trades (found with the equation above), your average trade net profit is $200 or 4 ES points and the standard deviation of all trades is $450 or 9 points.  Our largest loss for this system was $700, our win/loss ratio was 1.5 and our winning percentage was 0.6.

Since your average trade has a profit of 4 points, a standard deviation of 9 points means that 68.2% of the trades fall within one standard deviation of the mean or between -5 pts and +13 pts and approximately 95% of all trades fall within two standard deviations of the mean or between -14 pts and +22 pts.

Did the results occur by chance?

T-test = SqRoot(400) * (200/450) = 8.888

Because the T-test is greater than 1.6, we can conclude that the results of this test are not by chance alone.

How many contracts should we trade?

optimal f = (((1 + 1.5) * .6) - 1) / 1.5

optimal f = .333

maximum leverage = 700 / .33 = $2121

maximum leverage = 1 contract for every $2121 in the account

The thinking is that if we keep $2121 in the account for each contract we trade, we will never blow out the account

Links

Sample size calculator.  Calculator.

Survey size calculator.  Survey Says.

Table of sample sizes for given populations.  Table of Sample Sizes Based on Population.

Formulas explained, including level of precision, confidence level and degree of variability.  Formulas for Determining Sample Size.

Z-scores for given levels of precision and confidence levels.  Z-score Table.

T-test, Optimal F and Maximum Leverage.  Vertical Solutions.

PDF of Introduction to Testing Trading Ideas.  Testing Trading Ideas PDF.

Edge, Expectancy, R-Multiples & Links

Risk management, probability and statistics are one of those "nod and wink" topics discussed by most traders.  They know they should rigorously implement these three topics into their business plan but if most traders fail, I doubt that any new trader is probably doing so from day one.

Summary

1.  Define your edge and risk reward profile before putting on a trade.
2.  Edge can be expressed in terms of expectancy per dollar risked or  Expectancy = (Winning Percentage * Average Win) - (Losing Percentage * Average Loss).
3.  Think in terms of probabilities over a large sample size and realize that each trade is unique.
4.  An R-Multiple is really just a simple expression of Reward to Risk for a given trade. An R-Multiple of 2 means that for that trade, you are rewarded with $2 for every $1 risked.
5.  R-Multiple is Expected Gain - Expected Loss or (Exit Price - Entry Price) - (Entry Price - Stop Loss).
6.  Often times you will see 1R calculated which is just the Expected Loss or (Entry Price - Stop Loss).
7.  R-Multiple Distributions are an easy way to express the expectancy of a given trading system based on the system's average risk/reward ratio per trade.
8.  An R-Multiple distribution is the average or sample mean of R-Multiples of a number of sample trades.

Define Your Edge

When I first got into trading, I wasn't sure what an "Edge" was.  How can someone know what will happen in the future?  It is still something that I struggle with - simply knowing how to calculate an edge is different than internalizing what your edge truly is.  Stated another way, readiness to start trading is different than knowing how to trade.  The best way I have found to look at edge is by thinking about edge in terms of the law of large numbers.

Definition:  In probability theory, the law of large numbers (LLN) is a theorem that describes the result of performing the same experiment a large number of times. According to the law, the average of the results obtained from a large number of trials should be close to the expected value, and will tend to become closer as more trials are performed.  From:  Wikipedia, Law of Large Numbers.

Let's look at an example.  Take a six sided dice and let's calculate what the odds are that you will roll a 5.  The statistical edge is easy to calculate because the outcome of any roll is determined by a mechanical probability system (ie, the six sided dice).  The exact edge can be calculated by thinking through all possible outcomes of the game.  The die has six sides so the odds that a 5 will be rolled on a given roll is 1 in 6 or 16.66%.  However, five of the six sides will not roll a 5 so the odds of not rolling a 5 are 5 in 6 of 83.33%.

Let's say that you offer a friend the following bet:  he wins $4 for every time a 5 is rolled and you win $1 every time any other number but 5 is rolled.  If you roll the dice one time, the odds of him winning $4 is 16.66% and the odds of him losing $1 is 83.33%.  But let's say you change the rules of the game and require the dice to be rolled 100 times before you will pay him.  At the end of 100 rolls, you would expect to have won the $1 wager 83 times for a total profit of $83.  You friend would have won about 17 times, delivering a loss of $68.  This is what can be referred to as expectancy.

Expectancy = (Winning Percentage * Average Win) - (Losing Percentage * Average Loss)

So our Expectancy from the dice roll bet over 100 rolls is:

Expectancy = (.8333 * $1) - (.1667 * $1) = $15 or 15 percent profit for each dollar wagered.

According to the law of large numbers, the average of the results obtained from a large number of trials should be close to the expected value, and will tend to become closer as more trials are performed.

So how does this relate back to trading?  It is important to internalize that you can never predict the outcome of a single event or trade.  When you put a trade on, you don't care to predict what the outcome of that specific trade will be because you cannot predict the outcome.

Mark Douglas, in Trading in the Zone, defines two levels of thinking in probabilities:

1.  Micro Level - Each Trade is Unique - The outcome of each individual trade is statistically independent of every other trade and there is a random distribution between wins and losses in any given string of trades.  You have to believe in the uncertainty and unpredictability of the outcome of each individual trade.

2.  Macro Level - Think in Terms of Probabilities - You have to believe that the outcome over a series of trades is relatively certain and predictable. The degree of certainty is based on the fixed or constant variables that are known in advance and specifically designed to give an advantage (edge) to one side or the other.

Risk to Reward Ratios

So how does edge and expectancy interact with risk to reward ratios.  Remember, edge or expectancy is a function of the probability of a losing event versus its positive risk to reward ratio.

Van Tharp uses something called an R-Multiple.  An R-Multiple is the expected gain on a trade minus the expected loss on a trade. An R-Multiple can be expressed as the amount risked per unit or the total risk.  In it's most basic form, an R-Multiple is simply an expression of Risk to Reward.

R-Multiple = Expected Gain on a Trade - Expected Loss on a Trade


R-Multiple = (Expected Exit Price - Entry Price) - (Entry Price - Stop Loss)

Gains are positive R-Multiples.  Losses are negative R-Multiples.

Let's look at two examples.

Trade #1:  Say you buy a stock for $20 and you enter a stop loss order for $15.  Your profit target on the trade is $30.  So your initial R-Multiple for this trade is:

R-Multiple = ($30 Exit Price - $20 Entry Price)/($20 Entry Price - $15 Stop Loss) = 2R

Trade#2:  But say now you buy a stock for $20 and enter a stop loss order for $10.  Your profit target is $40 but the stock fails to rally and you are stopped out at $10.  You R-Multiple for this trade is:

R-Multiple = ($10 Exit Price - $20 Entry Price)/($20 Entry Price - $15 Stop Loss) = -2R

To make the calculation a little easier, you will often see R-Multiples expressed as 1R.  1R is simply defined as the risk of any one trade or:

1R = Entry Price - Stop Loss


For Trade #1, 1R is equal to $5.  For Trade #2, 1R is equal to $10.

Okay, so now that your mind is swimming with R-Multiples and such, how do we relate R- Multiple, which is basically an expression of our risk to reward on a given trade to expectancy, which we learned was how much you can expect to make on the average, per dollar risked, over a number of trades.

Van Tharp directs you to look at R-Multiple Distributions to determine your expectancy for a given trading system.

Let's go back to the dice problem again.  The law of large numbers states that as the sample size grows, the closer the average of the past outcomes approaches the statistical prediction of the probable outcome.  So what is the expected value of a single dice roll?

The formula is the sum of each individual outcome divided by the total number of outcomes or:



According to the law of large numbers, if a large number of six-sided dice are rolled, the average of their values (sometimes called the sample mean) is likely to be close to 3.5, with the accuracy increasing as more dice are rolled. From:  Wikipedia, Law of Large Numbers.

As you roll the dice more and more times and take the average of these rolls, the sample mean should approach 3.5.  

Back to R-Multiple distributions, you can determine the expectancy or probable outcome of any trading system by taking the average of you R-Multiples.  As more and more trades are taken, the average or "sample mean" of all of the trades should approach the probable outcome of the trading system, just like with the dice.

Let's look at an example of 5 Trades to calculate the R-Multiple distribution for a given system.

Trade #1:  Entry: $100, Stop: $90, 1R = 10, Exit Price: 120.  R-Multiple = 2R
Trade #2:  Entry: $50, Stop: $45, 1R = 5, Exit Price: 43.  R-Multiple = -1.4
Trade #3:  Entry: $120, Stop: $90, 1R = 30, Exit Price: 150.  R-Multiple = 1R
Trade #4:  Entry: $100, Stop: $80, 1R = 20, Exit Price: 300.  R-Multiple = 10R
Trade #5:  Entry: $78, Stop: $70, 1R = 8, Exit Price: 95.  R-Multiple = 2.13R

An R-Multiple distribution is found by calculating the R-Multiple for each trade and then taking the average or sample mean for all trades.  For the 5 trades above, this system has an average R-Multiple or expectancy of 2.745R.

R-Multiple Distribution = Sum of All R-Multiples for Each Trade / No. of Trades

Over 100 trades, we can expect to make 274.5R.  So if we keep R at $20 for all trades, we can expect to make 274.5 * 20 = $5490.  5 trades is far to small of a sample size to determine if we have a profitable system (we will get into sample size requirements later) but you can see that how R works.

Putting it All Together

So now you should be able to take a given trading system's results and determine the expectancy of the system using the R-Multiple Distribution calculation.  A couple of points to remember/rules of thumb:

1.  R-Multiple is an easy way to determine the Risk to Reward ratio of a given system and compare systems across the board.  If a trader approaches you and says he has a system that makes $10,000 across a population of 500 trades that sounds great.  But what if the R-Multiple Distribution of the system is 0.1R.  That is, for every $1 risked, you can expect $0.1 in return.  That is a damn risky system in my opinion!

2.  Generally, a rule of thumb is to only use a system that gives you a "2 to 1" or "3 to 1" risk reward profile.  This can be expressed as a 2R or 3R system.

3.  Don't focus on winning percentages alone!  Focusing only on winning percentages of a system ignores the risk reward profile and expectancy (edge) we just talked about.  I made this mistake early on and paid dearly for it.

Let's run the numbers.  For these examples, assume you have a $10,000 account, you trade 100 times and $100 is risked on each trade.  We will need these formulas:

Winning Percentage = % of Profitable Trades
Number of Winners = Total Number of Trades Taken * Winning Percentage
Total Profit = Number of Winners * Avg Profit Per Trade


Losing Percentage = (1 - % of Profitable Trades)
Number of Losers = Total Number of Trades Taken * Losing Percentage
Total Loss = Number of Losers * Average Loss


Net Profit = Total Profit + Total Loss

Assume System #1 has a winning percentage of 70% and System #2 has a winning percentage of 25%.  What does this tell us?  Really nothing because we don't know what the average win, average loss and expectancy are.  Assume System #1 has a $30 average win and a $100 average loss.  Assume System #2 has a $500 average win and a $100 average loss.

System #1 Analysis


What is the expectancy for this system?

Expectancy = (Winning Percentage * Average Win) - (Losing Percentage * Average Loss)
Expectancy = (.7*$30) - (.3*$100) = -9 percent expectancy or a loss of $9 for every $100 risked on the trade.


What is the Net Profit of this System?

No. Winners = 100 Trades * 70% Winners = 70 Winners
Total Profit  = 70 Winners * $30 Average Profit Per Trade = $2100

No. Losers = 100 Trades * 30% Losers = 30 Losers
Total Loss = 30 Losers * $100 Average Loss Per Trade  = $3000

Net Profit = Total Profit - Total Loss = $2100 - $3000 = -$900.

You could have easily found the Net Profit doing this:

Expectancy * Total # Trades * Total Risked Per Trade = -.09 * 100 * $100 = -$900

System #2 Analysis



What is the expectancy for this system?

Expectancy = (Winning Percentage * Average Win) - (Losing Percentage * Average Loss)
Expectancy = (.25*$500) - (.75*$100) = 50 percent expectancy or $50 for every $100 risked.



What is the Net Profit of this System?

No. Winners = 100 Trades * 25% Winners = 25 Winners
Total Profit  = 25 Winners * $500 Average Profit Per Trade = $12,500

No. Losers = 100 Trades * 75% Losers = 75 Losers
Total Loss = 75 Losers * $100 Average Loss = $7,500

Net Profit = Total Profit - Total Loss = $12,500 - $7,500 = $5,000.

Again Net Profit was .5 * 100 * $100 = $5,000.

As you can see, a high winning percentage does not guarantee a high rate of return for a trading system.  System #1 had a -9% expectancy even with a 70% win rate!

Links:

A good article explaining expectancy and R-Multiples from BackTestingBlog.com.

Van Tharp's article on R-Multiples and R-Multiple Distributions.  Van Tharp Institute.

A note to the R-Multiple Haters.  TraderMike.

A mathematical approach to eliminating emotions.  FX360.