Calculating Implied Volatility

If you use RTD formulas for data from the options markets, then Excel will pull data using your settings in CQG, such as the options model you have selected. An RTD formula for implied volatility will pull the Implied Volatility (IV) for the symbol and use your model:

=RTD("CQG.RTD", , "ContractData", "C.EP?","ImpliedVolatility",,"T")

This formula will allow you to use a specific model:

=RTD("CQG.RTD", , "ContractData", "OptVal(C.EP?,ImpliedVolatility,Black)", "Close")

However, if you substitute Bid, Ask, or Settlement for Close, you will not get a value because here, Close is looking for the last value of the Option Valuation, not the last price of the option itself (CQG uses close and last interchangeably).

We offer this RTD Contract Labels RTD spreadsheet:

http://news.cqg.com/workspaces/main/2012/09/cqg-to-rtd-excel-syntax-for-...

There is a tab titled Options, and there are three types of options RTD formulas detailed. The last one uses model inputs.

=RTD("CQG.RTD", , "ContractData", "OptVal(C.EP?,ImpliedVolatility,Black,1520.5,30,0.25,0.05,35.55)", "Close")

Where:

UndPrice = 152050, DTE = 30, Volatility = 25%, IntRate = 5%, OptionPrice = 3555

We can substitute other RTD formulas for the inputs, such as for the DTE input (Days to Expiration):

=RTD("cqg.rtd", ,"ContractData","C.EP?", "OptionDaysToExp",, "T")

And, we can use RTD to pull in the current bid or ask or settlement for the OptionPrice.

The spreadsheet is set up to provide the implied volatility (Black-Scholes model) using the bid prices, ask prices, and settlement prices for the underlying and the option. The Excel ISERROR function is used so you do not see errors if there is not a current bid or ask price. To use a different model please refer here.

Also, if you use settlement, you will have to add one day back to the days to expiration in cell B15 during the week or add three if it is Monday.

Requires CQG Integrated Client or CQG QTrader, data enablements for all symbols used in this spreadsheet, and Excel 2010 or higher.

Download the Excel File ‌‍‍‍‍

Microsoft Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries.