Since the introduction of the spreadsheet,
I've used its abilities in the continuing search for a profit-making
trading system. In the process, I've developed several automated
procedures, or macros. Some simply help by yielding additional ease in
copying formulas or selecting graph ranges. Others are more extensive,
such as subroutines awaiting inputs so that Fibonacci ratios and Elliott
wave theories can provide price predictions.
I want to present two such macros, the continuous lead contract and
the y-axis translator. (The latter macro is presented in a sidebar, "Input
for y-axis translator,"page 28.) These macros were originally developed
using Lotus Symphony. They have been converted and are presented here as
Excel macros in Visual Basic.
The motivation for developing the continuous lead contract macro came
from the time-consuming problems caused by expiring futures contracts.
The difficulty is caused when the most actively traded contract - that
is, the lead contract - expires, or rolls over to the next in line. Formulas
requiring preceding data, such as moving averages or statistics, have to
be handled carefully, because of discontinuous inputs when switching contracts
- say, from the June Standard & Poor's 500 futures contract
to the September.
Indicators for the new lead contract must be recomputed using data from
the September contract, which makes it impossible to compare the indicators
directly with the results from previous contracts. This is particularly
true if their function involves an accumulation of values over time to
arrive at significant support or resistance levels, or for divergence comparisons
with price.
One of the best solutions to avoiding such problems was derived by CSI
founder Robert Pelletier, and published in Commodities magazine
(now Futures) in 1983. I tried to use Pelletier's method
in the mid-1980s, but was discouraged by the large amount of resulting
calculated data. Later on, I noticed lead contract rollover occurs over
a short period of time for the S&P 500 futures (about 14-20 trading
days). So I developed a simple procedure that provides a continuous lead
contract (CLC), using prorated volume and open interest.
Because several indicators require volume and open interest as inputs,
all of these components are prorated to provide a calculated set of values,
but only over the short rollover period. Figure 1 shows S&P 500 CLC
data ended with the expiration of the June 2001 contract. Figure 2 shows
the S&P 500 cash data in the form of the Spy contract, which is 1/10
the value of the S&P 500 cash index. Note how well the three calculated
rollover areas of the S&P 500 CLC chart agree with the values of the
cash index.
Figure 1: S&P 500 futures. Note the rollover periods in the continuous lead contract.
...Continued in the December 2001 issue of Technical Analysis of
STOCKS & COMMODITIES
Excerpted from an article originally published in the December
2001 issue of Technical Analysis of STOCKS & COMMODITIES magazine.
All rights reserved. © Copyright 2001, Technical Analysis, Inc.
