Options Analytics Using Inputs and Models

This Microsoft Excel® dashboard uses the CQG RTD OptVal formula to calculate implied volatility, theoretical value, delta, and the Greeks using data inputs from other RTD formulas.

For example, the implied volatility calculation is using data from other cells;

RTD("cqg.rtd",,"ContractData","OptVal("&C4&",ImpliedVolatility,"&H3&","&B8&","&C8&","&E8&","&D8&","&A8&")","Close",,"T")

Here are the cell locations:

  • Symbol: cell C4
  • Model: cell H3
  • Underlying Price: cell B8
  • Days to Expiration: cell C8
  • Volatility: average is cell E8 or implied is cell F8
  • Interest Rate: cell D8
  • Option Price: cell A8

This allows you to do What-If scenarios as you can substitute your own input values. For example, in the theoretical value calculation, you could reference a different cell location for the volatility input then using cell F8, enter in a higher implied volatility in that cell, and see the impact on the theoretical value.

The Excel IFERROR function is used to display a blank value to avoid seeing #VALUE errors momentarily while Excel updates.

To manage the update rate, you can adjust your Excel RealTimeData (RTD) throttle to a level higher than 0 milliseconds. Learn how to do that here.

Requires CQG Integrated Client or CQG QTrader, data enablements for all symbols displayed in this spreadsheet, and Excel 2013 or more recent.

Downloads

Disclaimer

Trading and investment carry a high level of risk, and CQG, Inc. does not make any recommendations for buying or selling any financial instruments. We offer educational information on ways to use our sophisticated CQG trading tools, but it is up to our customers and other readers to make their own trading and investment decisions or to consult with a registered investment advisor. The opinions expressed here are solely those of the author and do not reflect the opinions of CQG, Inc. or its affiliates.