This post offers solutions to parsing data in Excel using string functions. As an example, this RTD formula returns the long description of a Crude Oil calendar spread:
=RTD("cqg.rtd", ,"ContractData", "CLES12", "LongDescription",, "T")Which returns:
Crude Light (Globex) Calendar Spread 12, May 24, May 25
Perhaps all you really need in your spreadsheet are the two delivery months and the other information wastes space. You can see in the returned text above the two months are listed following the first comma. Therefore, we need to determine the count of how many characters including spaces from the start of the string is to the comma. Excel has the FIND function for that purpose:
=FIND(",",RTD("cqg.rtd", ,"ContractData", "CLES12", "LongDescription",, "T"))= 40
The search is reviewing the output from the RTD function for the Long Description.
The RTD function for the Long Description could be in a cell, such as cell B1:
=FIND(",",B1)= 40
Also, the text could be in the formula:
=FIND(",","Crude Light (Globex) Calendar Spread 12, May 24, May 25")= 40
Notice, quotes are required for actual text, but are not used for a Cell reference.
The comma is 40 characters to the right of the first character. Next, the full length of the string is needed. Excel has the LEN function for that purpose:
=LEN(RTD("cqg.rtd", ,"ContractData", "CLES12", "LongDescription",, "T"))= 55
Now that the length of the string is determined and the location of the first comma then calculate the difference and use the RIGHT Function, which will return the specified number of characters from the right end of the string:
=RIGHT(RTD("cqg.rtd", ,"ContractData", "CLES12", "LongDescription",, "T"),LEN(RTD("cqg.rtd", ,"ContractData", "CLES12", "LongDescription",, "T"))-FIND(",",RTD("cqg.rtd", ,"ContractData","CLES12", "LongDescription",, "T"))-1)= May 24, May 25
Please note the "-1" at the end of the Excel formula above. This is used to remove the space following the comma. Below are the results of using the above function.
Perhaps the requirement is to use the actual market and the delivery months and remove the other text. This requires using both the LEFT and the RIGHT functions.
The LEFT Function is used with the FIND function (the symbol is in cell A14):
=LEFT(RTD("cqg.rtd", ,"ContractData",A14, "LongDescription",, "T"),FIND("(",RTD("cqg.rtd", ,"ContractData",A14, "LongDescription",, "T"))-1) = Crude LightUse the "&" to concatenate the above function to the RIGHT function:
=LEFT(RTD("cqg.rtd", ,"ContractData",A14, "LongDescription",, "T"),FIND("(",RTD("cqg.rtd", ,"ContractData",A14, "LongDescription",, "T"))-1)&RIGHT(RTD("cqg.rtd", ,"ContractData",A14, "LongDescription",, "T"),LEN(RTD("cqg.rtd", ,"ContractData",A14, "LongDescription",, "T"))-FIND(",",RTD("cqg.rtd", ,"ContractData",A14, "LongDescription",, "T"))-1)= Crude Light May 24, Jun 24
Another method to parse data is to use the SUBSTITUE function. For example, the requirement is to remove "(Globex)" from the Long Description. This function is replacing "(Globex)" with a space:
=SUBSTITUTE(RTD("cqg.rtd", ,"ContractData",A25, "LongDescription",, "T"),"(Globex)","")= Crude Light Calendar Spread 1, May 24, Jun 24
And, if by chance an extra space is created, the TRIM function can remove extra spaces:
=TRIM(SUBSTITUTE(RTD("cqg.rtd", ,"ContractData",A25, "LongDescription",, "T"),"(Globex)",""))= Crude Light Calendar Spread 1, May 24, Jun 24
However, some Long Descriptions will have a comma following "(Globex)" and some will not:
10yr US Treasury Notes (Globex), Jun 24
Crude Light (Globex) Calendar Spread 12, May 24, May 25
So, if just "(Globex)" is substituted there will be a floating comma displayed. One technique is to use the IFERROR function. If the Find function does not find "(Globex)," in the Long Description an error will be returned then in that case just "(Globex)" will be substituted with a space. In this function below there is a check for the Find function returning a positive value for "(Globex)," and if it not found then the IFERROR function jumps to substituting "(Globex)" with a space:
=IFERROR(IF(FIND("(Globex),",RTD("cqg.rtd", ,"ContractData",A30, "LongDescription",, "T"))>0,TRIM(SUBSTITUTE(RTD("cqg.rtd", ,"ContractData",A30, "LongDescription",, "T"),"(Globex),",""))),TRIM(SUBSTITUTE(RTD("cqg.rtd", ,"ContractData",A30, "LongDescription",, "T"),"(Globex)","")))= Crude Light Calendar Spread 12, May 24, May 25
Now, a different requirement may be merging data instead of parsing data. For example, text may be merged with data, i.e., Open: with RTD call for the Open.
Open: 5263.25
High: 5269.25
Low: 5236.5
Last Trade: 5260.25
The "&" can be used. This is referred to as Concatenate. Note, the Quotes around the text "Open: " with an extra space.
="Open: "&RTD("cqg.rtd", ,"ContractData","EP", "OPen",, "T")="High: "&RTD("cqg.rtd", ,"ContractData","EP", "High",, "T")="Low: "&RTD("cqg.rtd", ,"ContractData","EP", "Low",, "T")="Last Trade: "&TEXT(RTD("cqg.rtd", ,"ContractData","EP", "LastTrade",, "T"),"#.00")The Last trade uses the TEXT function to format the price to always use two decimals.
This link is to Microsoft's Help file for the TEXT function.
The downloadable Excel sample at the bottom of the post has all of the functions.
Screen space in Excel dashboards are a premium and these Excel string functions can assist in better screen management,
Requires CQG Integrated Client or CQG QTrader, and Excel 2016 or more recent.