SCAN and STACK - 10 Times Faster

Last month, I shared how REDUCE and STACK can be used to convert financial calculations to 5g functions in Excel, offering a structure that closely mimics traditional formulas. However, while intuitive, REDUCE and STACK is often not as fast as other approaches. In many cases, switching to SCAN and STACK can yield performance improvements of 10x or more.

To illustrate the SCAN and STACK method, we will use the common corkscrew.

The basic calculations for a corkscrew are:

Open = Prior Period Closing
Flow = Each period's values
Close = Current period's Open + Current period's Flow value        

Using traditional formulas, and assuming the corkscrew starts in cell J10, the flow values are in cells J11:Z11, and the opening balance is in I12...

J10: =I12
J11: =flow values
J12: =J10 + J11        

The formulas are copied right for as many columns as needed. Using the REDUCE and STACK method posted last month, the formula could look like:

=REDUCE( I12, SEQUENCE( COLUMNS( J11:Z11)),
   LAMBDA( Acc, Period,
     LET( Open,   IF( Period = 1, Acc, INDEX( Acc, 3, Period - 1)),
          Flow,   INDEX( J11:Z11, Period),
          Close,  Open + Flow,
          Column, VSTACK( Open, Flow, Close),
          Result, IF( Period = 1, Column, HSTACK( Acc, Column)),
          Result
     )
   )        

Some of you may be asking, "How does that mimic the traditional formula?" Well, it does take a bit to see it. Keep in mind, the REDUCE function is one formula in one cell replacing ALL the other formulas. So to get a specific period's value from an row of values, we can use INDEX. The Open LET step uses INDEX to get the Close row's prior column which is essentially what the traditional Open formula does. The Flow LET step uses INDEX as well. The Close LET step adds the current period's Open and Flow values, just like the traditional formula. Because we are building an entire array of several columns, we stack Open, Flow, and Close into a column and if we are in the first period, we place that column in REDUCE's accumulator (Acc). If we are not in the first period, we stack the column against the other columns already stored in the accumulator. When all columns have been processed, REDUCE places its accumulator in Excel's cells which will look identical to the traditional formula's results.

Now let us take a look at the same corkscrew using the SCAN and STACK methodology.

=LET(
  Close,  SCAN( I12, SEQUENCE( , COLUMNS( J11:Z11)),
            LAMBDA( Acc, Period,
              LET(Flow, INDEX( J11:Z11, Period),
                  Close, Acc + Flow,
                  Close
              )
            )
          ),
  Open,   HSTACK( I12, DROP( Close, , -1)),
  Result, VSTACK( Open, J11:Z11, Close),
  Result
)
        

A major difference in this approach is we start with Close, not Open, and use SCAN, not REDUCE. Our SCAN function is setup identical to REDUCE. They both use SEQUENCE() to create a period column counter and then use INDEX with the period counter to get each Flow value. SCAN's accumulator (Acc) holds the prior column's value, which is our current period's Open amount; thus, the Flow formula is essentially the same as REDUCE's Flow LET step formula.

The next major difference is we assemble the Open row simply by starting that row with our opening amount (from I12) and placing the Close row's values after it. We drop the last value from our Close row so Open will have the same number of columns as our Flow and Close rows.

The last major difference is we do not stack columns together. Instead we stack entire rows one on top of the other.

Summary

REDUCE and STACK is more intuitive (to me, and I assume others). For many models, it is fast enough. But if our model starts to perform poorly, one area where we can significantly improve performance is to convert, where possible, REDUCE and STACK functions to the SCAN and STACK methodology.