You can use Microsoft Excel® and data from CQG to expand the features of your own market display dashboards. This powerful combination can be tailored to your specific needs. This article shows you how to build an Excel dashboard and provides a few tips for getting the correct data.
First, the simplest method for entering the RTD formula in Excel is to add the market data you need to the CQG Quote SpreadSheet version 2.
Then, right-click on a particular cell and select Copy-To-Excel. The RTD formula is now on your clipboard and you can paste it into Excel. Here is an example formula for Last Price for symbol CLEK5:
=RTD("CQG.RTD", ,"ContractData", "F.US.CLEK15", "LastQuoteToday",, "T")If you want the entire row, right-click the symbol and select Copy-To-Excel. All of the RTD formulas for that row can now be pasted into Excel. The same will happen if you right-click the column title: All of the RTD formulas for the column can be pasted into Excel.
Notice for symbol CLEK5 there are values for each column. That is because CLEK5 is both a tradable symbol and can be charted on an open, high, low, and close bar chart. However, symbol CLEK5-CLEM5 is not tradable, so there is no bid/ask DOM data, and on a chart you will see a simple line chart and not open, high, low and close prices.
The symbol Spread(CLEK5-CLEM5) is tradable, so you see bid/ask DOM data. On a chart, though, there is only close data, which gives you just a line chart.
The symbol BAR(CLE?1-CLE?2,1) is the spread bar chart function using one-minute captures of the spread. This symbol can be used in a bar chart, and will give you open, high, low, and close values. You can apply studies in the chart that require a high-low range using this symbol. For examples of using RTD studies and bar types, please visit https://news.cqg.com/workspaces/main/2012/11/cqg-to-rtd-excel.html.
If you want to bring in option prices, implied volatility, and the Greeks into Excel, you are strongly encouraged to use the Options window. Just select the strike and right-click to choose the RTD formula you want. This way you will use the correct option symbol in the RTD call.
Finally, a great feature in RTD is the ability to reference information from another cell. For example, if
=RTD("CQG.RTD", ,"ContractData", "F.US.CLEK15", "LastQuoteToday",, "T") is in cell B1, and if the symbol is in cell A1, then replace F.US.CLEK15 with A1. Now, you can have one column of cells, and copy and paste the RTD formula in cell B1 and Excel will read the symbols from the cells in column A.