V.6:7 (268-271): Loops: static and dynamic Part 7 by Jim Summers, Ph.D.

Loops: static and dynamic Part 7 by Jim Summers, Ph.D.

This month, we develop a loop to manage the calculation of the trading range (TR 1) used in J. Welles Wilder's Directional Movement Index (DMI) system. The previous columns developed the formula for line-by-line processing of this indicator using {Put} and @ Index(...) commands.

Loops are an important topic; knowing how to use them with {Put} and @ Index(...) make up one of the strongest Lotus 1-2-3 tools for managing la rge amounts of data in technical anal ysis. We'll be using the data i n Figure 1, taken from Wilder's book , New Concepts in Technical Trading Systems.

For those of you who have been on board awhile, you will remember that the values in our spreadsheet's TR1 column were first calculated in the April 1988 column b y using range names. That method for processing a loop created several range names (High0, TR1, etc.) This rudimentar y loop accomplished the calculations in terms of the range names, then moved them all down and recreated them for each row.

My machine took two minutes to process a mere 14 rows using this method. Phenomenal speed improvement occurs using {Put} and @ Index(...), which avoid cursor movement and changes in range names. For purposes of continuit y, Figure 2 is the formula as developed through the last column.

Recall that the function @ Index( Data,3,0) refers to a cell address in the rang e Data. The first number is the column and the second is the ro w. In both cases, the o ffset starts counting with zero. Thus, @Index(Data,3,0) refers to the number 272.75, the close on 6/1/77. The o ffsets in the {Put} use the same conventions.