Constructing true range by offset addressing

The cost of a named-row-walk

Creating and retargeting a range name on each price row while walking the series is described as occupying a machine for hours once several months of data are processed. That pattern is a named-row-walk: range names are retargeted as the cursor steps down successive price bars.

Indicator construction is described as fastest when the routine neither moves the cursor nor rewrites range names. Average-directional-index construction begins with a true-range series taken from ordered highs, lows, and prior closes, so the first written column is already an addressing problem.

Reads and writes by zero-based-offset

The index-lookup returns a value from a multi-cell named range by zero-based-offset column and row counts. It is stated not to operate on a single-cell range. Zero-based-offset treats the upper-left cell of a named range as column 0, row 0.

The matching put-write uses the same zero-based-offset addressing. It executes only inside a macro. If it is typed into an ordinary worksheet cell, it does nothing but store text.

A put-write may store a literal, the contents of another named range, or an index-lookup. The source and destination ranges need not be the same.

A loop-counter-cell is a single-cell named range that can be updated with a put-write whose column and row offsets are both zero. That update is presented as the way to keep a loop index without moving the cursor.

The first true-range value

True-range is the greater of the current high-low span and the two absolute distances from the prior close to the current high and the current low. In the archive wording, that is the maximum of current high minus low, the absolute high minus prior close, and the absolute low minus prior close. The offset form is stated to keep that same mathematics.

The first true-range value can be built from only the Data range by reading the current high, current low, and prior close through index-lookup offsets and writing the three-way maximum into the true-range column with a put-write.

On the illustrated 14-day March 1978 Chicago Wheat sample, the offset macro is expected to write 3 into the first true-range cell of the second data row.