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.
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