Microsoft® Excel 365 includes a Text function that makes pulling text out of a string much easier than before Excel 365 was released. To illustrate, we will review the steps before Excel 365 was released.
Excel's Right function will trim a text string from the right. For example, the RTD call for the Long Description:
=RTD("cqg.rtd", ,"ContractData","CLE", "LongDescription",, "T")
Returns:
Crude Light (Globex), Feb 24
And, if you want your Excel dashboard to only display the month and the year for the futures contract to save space you can use the Right function set to six characters:
=RIGHT(RTD("cqg.rtd", ,"ContractData","CLE", "LongDescription",, "T"),6)
Returns:
Feb 24
All good, however, if you changed the symbol to be a calendar spread, such as CLES1 the Long description returns:
Crude Light (Globex) Calendar Spread 1, Jan 24, Feb 24
But using the same Right function:
=RIGHT(RTD("cqg.rtd", ,"ContractData","CLES1", "LongDescription",, "T"),6)
Returns:
Feb 24
Not:
Jan 24, Feb 24
The solution is to first determine the length of the text string, second find the location of the first comma in the string and for the Right function use the difference between the length and the location of the first comma:
The Length (LEN) function is:
=LEN(RTD("cqg.rtd", ,"ContractData", “CLES1”, "LongDescription",, "T"))
Returns:
54
The FIND function looks for the location of ",":
=FIND(",",RTD("cqg.rtd", ,"ContractData",“CLES1”, "LongDescription",, "T"))
Returns:
39
The difference between the two is 15.
Use the difference between the two functions to calculate the number of characters for the RIGHT function:
=RIGHT(RTD("cqg.rtd", ,"ContractData","CLES1", "LongDescription",, "T"),LEN(RTD("cqg.rtd", ,"ContractData", "CLES1", "LongDescription",, "T"))-FIND(",",RTD("cqg.rtd", ,"ContractData", "CLES1", "LongDescription",, "T")))
Returns:
Jan 24, Feb 24
Excel 365 offers the TEXTAFTER function. This new function makes this process much easier. The function returns text that occurs after a given character or string, in this case it is ",":
=TEXTAFTER(RTD("cqg.rtd", ,"ContractData","CLES1", "LongDescription",, "T"),",")
Returns:
Jan 24, Feb 24
There is a space after the delimiter which can be removed by using the TRIM function. Finally, if cell references for the RTD function are used for the symbols, and you enter a symbol such as SPY (the S&P 500 ETF) then the TEXTAFTER function will display an error as there is no “,” in the Long Description. IFERROR can be used to display the original Long Description.
=IFERROR(TRIM(TEXTAFTER(RTD("cqg.rtd", ,"ContractData",A2, "LongDescription",, "T"),",")),RTD("cqg.rtd", ,"ContractData",A2, "LongDescription",, "T"))
There is a downloadable sample spreadsheet at the bottom of the post.
Requirements: CQG Integrated Client or QTrader, and Excel 365 (locally installed, not in the Cloud) or more recent.