[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

GEN: computing optimal f via spreadsheet



PureBytes Links

Trading Reference Links

To compute optimal f via a spreadsheet, you need a sheet 101 columns
wide, and 3 rows plus as many trades as you have history for deep
(plus two more rows at the bottom).

You may want to inset this overall table several rows and columns, so
you have edge space for titles and summary stats or whatever.

Across the top row starting at the 2nd column, put the values .01, .02,
etcetera, up to 1.0.  Using a formula adding .01 to the contents of the
box to the left is easiest, with .01 in the first box; copy the formula
out to the right to get you a total of 100 entries.  These are your f
values (actually a sample of 100 f values; possible f values are the
entire real number range from zero to one).

Across the 2nd row starting at the 2nd column, put your largest loss
ever experienced (more on that in a moment); use a negative number (or 
use a positive and reverse the sign in the formula below).  Since this
changes for each new market/system, use an assignment formula in the 2nd
to 100th position, so when you put in the largest loss in the 
first box, it automatically propogates across the 99 other row
positions.  

Across the third row starting at the 2nd column, enter a formula for the
negative of the largest loss (2nd row same columm) divided by the f
value (1st row same column.)  This will give you a range of dollar
amounts you divide your equity by to determine number of contracts.  As
the sample f values range from 0 to 1, these dollar amounts range from
100x the largest loss to the largest loss.  These figures also represent
"starting equity", the bare bones minimum amount needed to place a
single trade.  (Don't take this literally, or you could lose your entire
stake or more in your very first trade!)

Down the left most column starting at row 4 (one below the three rows
talked about above), enter the trade results, positive for winners, and
negative for losers (include all costs!).  Enter them in time order,
based on close date.  

In every box in the spreadsheet proper (4th row down 2nd column over,
across and down from there) enter a formula (enter once and copy) that
sums the previous equity (one box up) with the product of the trade
result (same row first column) and the previous equity (one box up)
divided by the equity per contract (same column, third row),  i.e., in
box b5 you'll have =b4+$a5*(b4/b$3).

As you should see, the column moving down reflects your equity after
that trade, where you start with the "starting equity" figure, and trade
a real number of contracts (i.e., "3.27") based on your equity entering
the trade and equity required per contract are executing at, which in
turn is based on the largest loss and the current f value.  Your new 
equity is the previous equity plus the number contracts traded times the
result.  (Oh yes, if I didn't say it, all trade results must be entered
based on SINGLE CONTRACTS!).

Now across the bottom of the spreadsheet (I have the table spread out
for 200 trades and have these last, additional rows at rows 204 and 205,
but you can put it immediately below your last trade), add two rows.  In
the first, enter a formula that divides the final equity (one box up) by
the starting equity.  This is your "terminal wealth relative", which is
the factor you increased your starting equity by (i.e., 500=>5000 the
TWR is 10).  Obviously, an increase of 10 is better than an increase of
3.  An increase of 10^12 is extremely cool...but impossible and insane
to actually achieve in real life, for multiple reasons.

One row below that I like to put down a copy of row 3, the "starting
equity" (or units of equity needed per contract), just so I can see it
close to the final equity figure and the TWR.

Now that you are done, you have a N x 100 table of equity values.  The
so called "optimal f" is the f value for the column with the highest
terminal wealth relative.

That's it.  I won't go into all the issues and risks of trading close to
or even at the optimal f compounding level here, that's a whole
different discussion!  Overall though, from a "goodness" perspective,
and all else being equal (never), a system with a larger optimal f value
(closer to 1), and a larger TWR, is stronger.  A system with an optimal
f value of 0.3 and a starting equity of 12000 and terminal wealth
relative of 75000 is much much weaker than a system with an optimal f
value of 0.9, a starting equity of 4600 and a terminal wealth relative
of 1.2*10^12, REGARDLESS of how you do position sizing. (Don't confuse
this with the sanity of actually trading at one contract
per 4600 of equity, as, arguably, Ralph Vince did in his book.)

-Kevin