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.