Fixed Income US Treasury traders may require the settlement date for calculating yields in Excel. Unfortunately, there is not an RTD function for pulling in the settlement date from CQG. However, there is a function in Excel that is perfect for the task: the WORKDAY.INTL() function.
According to Excel’s Help file: “This function returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. Weekend parameters indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.”
In the downloadable Excel sample, cell B1 uses the RTD formula for today’s date for the BrokerTec 2-year T-Note, symbol BTC02. Any US Treasury symbol can be used.
=RTD("cqg.rtd", ,"ContractData", "BTC02", "T_Date",, "T")
The Settlement date in cell B2 uses the WORKDAY.INTL() function:
=WORKDAY.INTL(B1,1,1,F2:J12)
Looking at the function arguments we see that the start date is from cell B1.
Treasuries settle the next day business day from the trade date. Therefore, the second parameter is “1”. The third parameter, Weekend, is set to 1, which indicates that Saturday and Sunday are not working days. Finally, this function has a feature where you can list holiday dates or access a table (here, cells F2:J12 are the holiday dates) and the function will not consider a holiday a business day.
For example, Friday (1/14/2022) is the business day before Martin Luther King’s birthday (1/17/2022) so Excel knows to return Tuesday (1/18/2022) for the settlement date.
This holiday table goes out to 2025.