I will not go through all the systems tested, but simply describe the process so that
you can do it yourself on your own systems, later on.
Column A, in Figure 22.1, contains all the historical and hypothetical trades,
copied and pasted from the comma-separated text file they were exported to with
the help of the exportfunction. (Before you copy the trades into column A,
remember to erase any old data you might have saved in column A from the last
time you used the spreadsheet.)
Column C holds an adjusted value for each trade. Depending on whether the
individual trade is a winner or loser, its outcome can be adjusted downwards or
upwards, respectively, to minimize the effect of winners or losers that are deemed
too large. This is a useful feature if you’d like to make sure the results aren’t too
dependent on any exceptionally large winners or losers (especially the winners).
The specified levels can be set in cells D3 and D4 in the subsheet “Tables” (see
Figure 22.2). If the system still produces profitable random trading sequences with
the value in cell D3, in subsheet “Tables” set to one or two standard deviations, it’s
even more likely it will continue to work in the future, and perhaps even be better
than expected, because the exceptionally large winners now come as happy sur-
prises instead of necessary life savers.
The formula in cell C3 in Figure 22.1 looks like this:IF(A3>0,MIN(A3,Tables!F$4),MAX(A3,Tables!F$5))Copy the formula in cell C3 all the way down to the last row containing a
trade in column A.
Columns E to X hold randomly picked trades from column C.
The formula in cell E3 looks like this:INDEX($C:$C,RANDBETWEEN(1,Tables!$D$2))Copy it 20 columns to the right and 200 rows down, to create 20 random
trading sequences with 200 trades each (or 200 random trading sequences with 20
trades each; you can look at it both ways) The formula will pick a trade at random
from column C.
In Figure 22.2, the subsheet “Tables” contains a few basic values and for-
mulas. The number of trades in columns A and C in subsheet “Data” is typed into
cell D2. The number of standard deviations that should not be surpassed by the
winners and losers are typed into cells D3 and D4. The average profit per trade,
based on the data in column A, subsheet “Data,” is calculated in cell F2 [formula:
AVERAGE(Data!A:A)]. The standard deviation of the data in column A, sub-
sheet “Data,” is calculated in cell F3 [formula: STDEV(Data!A:A)]. The actual
values not to be surpassed by the data in column C, subsheet “Data,” are calculat-
ed in cells F4 and F5 [formulas: F2D3*F3 and F2D4*F3]. Note that these
cell references go into cell C3 in subsheet “Data.”CHAPTER 22 Variables 275