RTD allows you to build elaborate quote displays that go beyond straight market quotes such as spread quotes between two markets. However, with more distant delivery months, there may be instances where one market is being quoted, but another market is not. In other words, one market may post a bid and an ask, but the other market may post a bid but no ask. If you are calculating the spread between the bid of the first market and the ask of the second market, and Excel® sees that there is no ask quote, then Excel considers this to be an error and displays #Value in the cell, which clutters up the display.
One way to deal with this is to use the ISERROR function in a IF function.
To use these two functions, first pick a cell to do the spread calculation, such as cell H5. Below, cell H5 is calculating the difference between the bid quote for the eighth contract out the forward curve for crude oil traded on Globex and the ask quote for the eighth contract out the forward curve for crude oil traded on ICE.
Cell H5:
=RTD("cqg.rtd",,"ContractData",CLE?8,"Bid")-RTD("cqg.rtd",,"ContractData",QO?8,"Ask")Next, pick a cell to display the spread price. Here, cell C5 is set to display the spread price and will use an if-then statement to check for an error. The function below is set to check for the error first. If Excel sees an error, then it displays an empty cell (two quotation marks display a blank cell), and if there is a spread price, then that spread price is displayed.
Cell C5:
=IF(ISERROR(H5),"",H5)
The right corner of this energy view is displaying blank cells when both prices are not available for the spread calculation.
Review Excel functions and you can find other ways to better visualize your data in Excel.