When designing Excel quote dashboards there should be a commitment to have it be a clean looking dashboard. However, sometimes an RTD call for market data can generate an error in Excel. For example, this is the RTD call for percent net change.
RTD("cqg.rtd",,"ContractData",A3,"PerCentNetLastTrade",,"T")
But, the value displayed when the cell is formatted for percentages is 100 times to large. Easy enough to fix by simply dividing by 100. However, before a market open the previous session data is cleared and the RTD percent net change value returned is a blank cell. Dividing by 100 generates the #Value! error. (Below there are two % Net Change columns because conditional formatting would normally be used.
One solution could be using an IF Then function to display a blank cell if there is not a value returned. A better solution is using Excel’s IFError function.
The IFERROR function enables you to determine what is displayed if Excel sees an error. The IFERROR function recognizes these errors:
- #DIV/0!
- #NAME?
- #N/A
- #VALUE!
- #REF!
- #NUM!
- #NULL!
You use the IFError function to display what you want if the there is an error:
=IFERROR(RTD("cqg.rtd",,"ContractData",A3,"PerCentNetLastTrade",,"T")/100,"")
The above formula will display a blank cell (the double quotes) if there is an error.
If there is not an error then the RTD call works as planned.
You can also use a message such as “Not Open”:
IFERROR(RTD("cqg.rtd", ,"ContractData",B3, "PerCentNetLastTrade",, "T")/100,"Not Open")
RTD functions will return a text message for an error, such as the symbol used as a reference to a cell may be blank due to some issues. The RTD text message error for that case is:
“Obligatory parameter <contract>(position - 1) is not specified”
As this is an RTD generated text error then Excel’s IFERROR function will not recognize it as an error. The solution is to multiply RTD functions for price or study values by 1, then the “#VALUE!” error is returned and the IFERROR function recognizes the error:
=IFERROR(RTD("cqg.rtd", ,"ContractData",B3, "LastTrade",, "T")*1,"")
Now the cell displays a blank cell if cell B3 does not have a symbol.
Another useful feature of the IFERROR function is using it as part of VLookup. When using VLookup if Excel cannot locate the data being searched for then Excel generates the #N/A error.
The second tab of the downloadable sample is the constituents of the S&P 500 as of 4/22/2022. There are RTD formulas for typical market data, such as open, high, low, last and net change. Instead of scrolling down over 500 rows for a particular symbol to see today’s performance there is cell L1 to enter a symbol. If you entered the wrong symbol then VLookup returns the #N/A error. However, using the IFERROR function then “Not Found” is returned in cell K3.
=IFERROR(VLOOKUP(L1,A3:H507,8,FALSE),"Not Found")
The other RTD formulas to the right of K3 look for “Not Found” and return blank cells.
Here is a link to maintain the current holdings of the S&P 500.
https://www.ssga.com/us/en/institutional/etfs/funds/spdr-sp-500-etf-trust-spy