u/David_Beroff

I'm paper-trading the Wheel option trading strategy, (see r/OptionsWheel), and when I first tried to set up my tracking spreadsheet, Google Sheets falsely reported that I had a circular dependency. The strategy involves a well-defined sequence over three different variables that change over time, and because these three columns all reference each other over a long chained calculation, Google assumed it was self-referential.

Since it isn't, I decided to use SCAN() to manage the Wheel logic, building from a known starting condition, and then walking through each of the steps sequentially. SCAN()'s "accumulator" is actually the set of three variables, separated with the pipe ("|") character.

Here's my tracking spreadsheet.

I employ the following helper function to make things faster to mentally parse:

SUB( string, n )
   // Extracts the n'th part from a "|"-delimited string.
   =index( split( string, "|", false, false ), 0, n )

The heart of the Wheel strategy logic is contained in cell Study!Q8, as follows:

                // Initialize the state as the triplet: {Strike, Option Type, Assigned}.
                // Option Type is true for Puts, false for Calls.  Assigned is also boolean.
=scan( "0|true|false", 
                // The input is the options' Underlying security Price, captured twice a day.
   G8:G, lambda( state, u_price, if( u_price <> "", 

                // Build the new triplet.  If we're at the Market Open:
   join( "|", if( mod( row( u_price ), 2 ) = 0, 
                // If we were Assigned last night, switch the Option Type from a Put 
                // to a Call, or from a Call to a Put, and if we weren't, then don't.
      let( new_type, xor( SUB( state, _assigned ), SUB( state, _put ) ), 
                // Set the Strike to just below the Underlying Price for a Put, 
                // and simply don't change it for a Call.
      { if( new_type, int( u_price ), SUB( state, _strike ) ), 
                // Pass along the new Option Type and the prior state of Assignment.
      new_type, SUB( state, _assigned ) } ), 

                // If we're at the Market Close:
                // Retain the Strike and the Option Type.
      { SUB( state, _strike ), SUB( state, _put ), 
                // Put options are Assigned when the Underlying Price falls at or 
                // below the Strike, and Calls are Assigned when it's at or above.
      if( SUB( state, _put ), u_price <= SUB( state, _strike ), 
                              u_price >= SUB( state, _strike ) ) } ) ), ) ) )

The logic for the second false circular dependency area, the compounding calculation, is actually far simpler than the above; it's literally just a matter of dividing the available cash by the contract collateralization requirement each day to determine the total number of Put contracts that can be sold. Unfortunately, since that ultimately feeds back into the cash balance at Market Close, Google once again claimed it's circular, so I used much the same approach.

I'm not going to walk through the full formula for cell Study!AT8, but I do want to point out that I used the identical pipe-delimiting method to get around the fact that SCAN() only takes one input column, in addition to only generating one output column.

So, I start off by building my initial state as before, and then I join() my 6 input variables, including the 3 being output to column Q from the above calculation.

=scan( "|||||" & initial_balance & "|" & initial_time & "||", 
   byrow( { Q8:Q, sequence( rows( Q8:Q ), 1, 8, 1 ), AL8:AL, F8:F }, 
      lambda( i, join( "|", i ) ) ), 
   lambda( state, inputs, 
      if( len( regexreplace( inputs, "[^|]", "" ) ) + 1 = comp_expected_inputs, 
         let( ...

In cell Study!AU8, I format the 9 output variables into columns for human consumption:

=ArrayFormula( split( array_constrain( AT8:AT, counta( AT8:AT ), 1 ), "|", false, false ) )

I'm curious if others have also run into this false circular dependency issue, and how you got around it.