One important step when building Excel market dashboards is managing screen real estate. For example, in a recent post "CQG CME Markets Dashboards" a section displayed Corn 1-month Calendar spreads.
The symbol "ZCES1?1" using wild cards is calling for the first Corn 1-Month Calendar spread. The long description shown above displays the legs (1*ZCEN25-1*ZCEU25).
=RTD("cqg.rtd", ,"ContractData","ZCES1?1", "longDescription")Everything to the left of the legs is not necessary and is a waste of space.
This post "Excel 365 TEXTAFTER Function" provided a solution.
Excel 365 offers the TEXTAFTER function. This function makes this streamlining process 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"),",")And returns:
(1*CLEQ25-1*CLEU25)
If you wanted to remove the parentheses, then the TEXTBEFORE function could be used:
=RIGHT(TEXTBEFORE(RTD("cqg.rtd", ,"ContractData","ZCES1?1", "longDescription"),")"),LEN(RTD("cqg.rtd", ,"ContractData","ZCES1?1", "longDescription"))-LEN(TEXTBEFORE(RTD("cqg.rtd", ,"ContractData","ZCES1?1", "longDescription"),"("))-2)The function is finding the number of characters between the first and last parentheses and using the RIGHT function to remove the parentheses.
This function returns:
1*ZCEN25-1*ZCEU25
The TEXTAFTER function looked for the "," as the delimiter. The TEXTBEFORE function looked for the "(" and ")" as the delimiters.
Another solution is to use the TEXTSPLIT function. For example, the Soybean Crush spread long description, which is this symbol "F.US.SOMI01" returns:
Soybean Crush (Meal, Oil after Beans exp), (-10*ZSEX25+9*ZLEZ25+11*ZMEZ25)
Above, we have multiple parenthesis and commas. Using the TEXTSPLIT function looking for the comma:
=TEXTSPLIT(RTD("cqg.rtd", ,"ContractData","F.US.SOMI01", "longDescription"),",")Returns an array:
The INDEX function can be used to return the third output.
=INDEX(TEXTSPLIT(RTD("cqg.rtd", ,"ContractData","F.US.SOMI01", "longDescription"),"("),3)
The INDEX Function can be modified to remove the parenthesis.
=LEFT(INDEX(TEXTSPLIT(RTD("cqg.rtd", ,"ContractData","F.US.SOMI01", "longDescription"),"("),3),LEN(RTD("cqg.rtd", ,"ContractData","F.US.SOMI01", "longDescription"))-LEN(TEXTBEFORE(RTD("cqg.rtd", ,"ContractData","F.US.SOMI01", "longDescription"),"(",-1))-2)
Below is an image of the downloadable sample.
The downloadable sample spreadsheet uses the detailed functions.
Requirements: CQG Integrated Client or QTrader, and Excel 365 (locally installed, not in the Cloud) or more recent.