Conditional Formatting in Excel using CQG's RTD Bate Function

One valuable feature in Excel® is conditional formatting. There is a wide array of rules available in the Conditional Formatting group after you select the Home tab in Excel. Here, we will use a simple color the cell condition if the last quote is a bid, ask, or trade based on the CQG RTD BATE function.

If you view the CQG to RTD Excel Syntax for Market Data Labels article and download the associated LabelsRTD.xls spreadsheet, you will see a Bate tab. On this spreadsheet page, there are a number of RTD formulas available, including one called Bate. Bate returns a number based on the last quote being a bid, ask, trade, or settlement. The numbers returned can be as follows:

0 = Trade
64 = Bid
128 = Ask
192 = Settlement

For example, here is the RTD formula for the symbol CLE:

=RTD("cqg.rtd", , "ContractData", CLE?,"Bate")

Using this formula in a cell will display one of the four numbers listed above based on the last quote being a trade, bid, ask, or settlement.

Now, what you can do is assign one cell to display the best ask, the next cell to display the best bid, and another cell to display the last trade or settlement. Then, in a separate cell, enter the RTD Bate formula displayed above.

Here are the steps to format the cell color of the best ask cell to be red based on the last quote being a new ask:

1. Select the ask cell.
2. Go to the Conditional Formatting editor.
3. Select New Rule.
4. Select Use a formula to determine which cells to format.
5. Enter the location of the RTD Bate function. In the image below, the formula is:
   

   =Sheet3!$Q$14=128

6. Select Format and choose the color to fill. Here, the cell is colored red if the value is 128. If it is not 128, then the fill color is left alone.

Follow the same steps for the bid cell, except it needs to see 64, and can be colored green. For the last trade cell, the value needs to be "0" and you can apply any color you choose.

This screen capture is a crude oil calendar spread matrix view designed to indicate if the last quote was a new ask (red), new bid (green), or a new trade (silver).

Exploring Excel's conditional formatting enables you to create much more informative spreadsheets.