Excel 365 SQUENCE function populates a column or row with sequential integers, such as 1,2,3,4, etc.
The function has four parameters:
SEQUENCE(rows, [columns], [start], [step])
Where:
- Rows is the number of rows to populate
- Columns is the number of columns, if left blank the number is 1
- Start is the value to start with
- Step is the size of the increments
This function makes setting up Excel tables one step easier. As an example, using RTD calls historical price and study can be called into Excel from CQG IC or QTrader.
The RTD call for current closes and historical closes price data:
= RTD("cqg.rtd",,"StudyData",$A$3, "BAR", "","Close",$B$3,$A4,"All","","","False","T")Where:
$A$3 = Symbol
$B$3 = Time Frame
$A4 = Bar Index
Prior to the SEQUENCE function being available the solution for the Bar Index column (column A) was to simply enter 0 in cell A4, then A4-1 in cell A5 and copy and paste down.
Using the SEQUENCE function entered in cell A4 calls for twenty-five rows starting with “0” and the value spills down the column:
=(SEQUENCE(25,,0,)*-1)
The output has to be multiplied by “-1” to pull historical data.
Notice above that when the first cell is selected a light blue line appears around the cells indicating the cells are part of an array.
A more complex example is using the SEQUENCE function to build an option chain in Excel.
The SEQUENCE function is used to build the list of option symbols.
The option symbols are starting with the symbol in cell H3, which is “C.US.EPU24” and that symbol is merged with the SEQUENCE function and builds a list of strike prices.
The SEQUENCE function is calling for 25 symbols starting with a strike price of 54000 (located in cell I3), and the strike price step is 250 (found in cell J2).
The Option Price column uses this RTD formula copied down, which uses the symbols in column I.
=RTD("cqg.rtd", ,"ContractData",I4, "OptionPrice",, "T")One last example is using the SEQUENCE function to populate a row.
=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,6),1),"mmm")
The function is using the SEQUENCE function to populate one row and then six columns to the right, based on the Date of the Year for Today, then formatting the value as Text using a monthly format.
The SEQUENCE function is one of a number of Dynamic Array functions available in Excel 365.
Requirements: CQG Integrated Client or QTrader, and Excel 365 (locally installed, not in the Cloud) or more recent.