Microsoft Excel 2016 introduced the IFS function. This function can replace using nested IF functions. Below is an example of nested IF functions:
=IF(N2=0,"#",IF(N2=1,"#.0",IF(N2=2,"#.00",IF(N2=3,"#.000",IF(N2=4,"#.0000",IF(N2=5,"#.00000",IF(N2=6,"#.000000",IF(N2=7,"#.0000000"))))))))
The above can be replaced with this:
=IFS(N2=0,"#",N2=1,"#.0",N2=2,"#.00",N2=3,"#.000",N2=4,"#.0000",N2=5,"#.00000",N2=6,"#.000000",N2=7,"#.0000000")
Clearly, the IFS function is easier to use compared to using nested IF functions. In addition, the IFS function allows you to test up to 127 different conditions. However, it is not recommended nesting too many conditions with IF or IFS statements due to the increased load on the processor. By the way, the two formulas above are used to return the formatting parameter for the TEXT function, which is discussed next.
The downloadable sample file uses the IFS function as the second step to formatting prices based on the tick size of the symbol. Why do this? The reason is Excel will format a price by a "general" or a "fixed" format set by the user. If a price cell is set to "general" for the last trade for a market such as the E-Mini S&P then a series of prices displayed will jump from no decimals to two decimals, i.e., 4747 and then 4747.25 and back to 4747. The prices will move sideways. If you set the decimal formatting to two, then that solves the problem unless you change to another symbol with perhaps four decimals, then you manually have to reset the decimal format.
The downloadable sample spreadsheet will automatically set the decimal format based on the symbols tick size.
Column N has the following formula:
=IF(LEN(RTD("cqg.rtd", ,"ContractData", "Tsize("&A2&")", "LastQuoteToday",,"T"))=1,0,LEN(RTD("cqg.rtd", ,"ContractData", "Tsize("&A2&")", "LastQuoteToday",,"T"))-2)The formula is first checking that if the length of the tick size is 1, then the symbol's price does not use decimals and returns a 0. If the tick size is less than 1 then the length of the tick size is returned and is reduced by 2. For example. The tick size for the symbol "EP" is 0.25, which is four characters, and the length return is 2.
Column O uses the IFS function and returns the value used as a parameter in the TEXT function for formatting the price:
=IFS(N2=0,"#",N2=1,"#.0",N2=2,"#.00",N2=3,"#.000",N2=4,"#.0000",N2=5,"#.00000",N2=6,"#.000000",N2=7,"#.0000000")
Finally, all of the price based RTD formulas are formatted using the TEXT function and the value in the O column for the formatting parameters. For example, in cell B2:
=TEXT(RTD("cqg.rtd", ,"ContractData",A2, "LastTrade",, "T"),O2)And the parameter from O2 is "#.00".
This spreadsheet will automatically format the prices based on the symbol entered in column A.
Requirements: CQG Integrated Client or QTrader, data enablements for all symbols displayed in this spreadsheet, and Excel 2016 or more recent.