We received a lot of questions recently regarding the practical usage of our IVolatility SDK. In this newsletter we will show one of the practical uses of our SDK by showing an example of how to build a simple portfolio risk analysis tool in Microsoft Excel using our IVolatility SDK. Of course this risk example is only one way to utilize the IVolatility SDK.
We take a very simple portfolio for this illustration consisting of an OTM Bull Vertical Spread on DLM:NYSE (DEL MONTE FOODS CO). This trade was found to be promising, using one of our other services, Real-time Strategy Scanner. Now we will show how to imitate risk analysis functionality of this service, using the IVolatility SDK. In certain aspects, the SDK is more powerful due to its flexibility - for example, you can create portfolios of options on different stocks and analyze their aggregated risks etc. All data is 20-minute delayed.
The sample is available for download, see also a screenshot below. Please note that you need IVolatility SDK service subscription to use this sample Excel application.
It consists of 3 sections: Portfolio or Risk view, a very basic Hedging module and quite powerful Risk simulation module. Naturally, you can create a portfolio there, view & analyze its risks, hedge them and simulate portfolio performance. The sample is very simple and easy to use, but you can also enhance it very significantly, using the SDK data and analysis functionality and great flexibility of MS Excel. Let's take a look at the underlying logic behind this screen.
Here, you are expected to enter the option symbol(s), number of contracts and trade direction (Buy/Sell) for each position (note the fields with orange color-code). Other values shown are either taken from our data feed or calculated in MS Excel (blue and pink color-code). The syntax is common for DDE Calls, for example, if you need to know bid price for option DLMLB, just type the following:
Exactly the same thing for other columns in this grid; for Greeks (Delta, Vega, Gamma and Theta) we perform some further simple adjustments in MS Excel in order to get these values for the entire trade and some reasonable shifts. To be precise, the Greeks shown in this sample are all in $:
Delta - change in position value for $1 advance in underlying price.
Vega - change in position value for 1% advance in implied volatility (absolute, like from IV of 21.36% to 22.36%).
Gamma - convexity, change in Delta value for $1 change in underlying price.
Theta - decay, change in position value from today to tomorrow.
The bottom line aggregates the risks - basically, just sums up the Greeks for two legs of this spread. Of course, you can implement a more sophisticated risk aggregation algorithm, with bucketing, topography, etc. - we are just giving an illustration of usage here.
As you see from the figure above, the spread has good positive Gamma, negligible Vega and Theta, and quite a considerable positive Delta. This tells us that the position is most sensitive to changes in underlying price and can show losses for downward ones. We can try to hedge this risk.
"Hedging module" for this portfolio is very simple - we simply calculate how many stock lots (100 shares each) needs to be sold to neutralize the spread Delta. For this, we divide spread Delta by underlying price and by 100 (lot size). Selling 600 shares of DLM decreases the position delta from almost $7000 to just $300, or, 27 shares. The position becomes delta-neutral, so, in the first approximation we have a pure positive Gamma-trade, which is quite easy to manage. Now we are going to check out what profit or loss is expected from this position for different values of forward underlying price, volatility and time passed.
This module is probably the most sophisticated part. It uses functions of Intermark Toolkits to calculate the option prices for future dates and volatility & forward price data inputted by user (orange colorcode for manual input again). In this sample, we calculate total P&L as a function of forward price and days passed, given fixed values of volatility for each leg.
To make the functions of Intermark Financial Toolkits available, you need to activate the "Optoolkit" add-in. The file "optoolkit.xla" is placed into the IVolatility SDK installation folder. Please use the Tools -> Add-Ins ... menu item in Microsoft Excel and press the Browse button. Navigate then to IVolatility SDK installation folder and find this file, press OK. Now, all the different option calculation models implemented in Intermark Toolkits are available for using in your MS Excel application!
To calculate the future option prices in this sample, we use a simple IMUOA_Price() function from the toolkits; you need to pass the following parameters to it, in the order below:
interest rate (as decimal, or in % format)
time to expiry (in years)
dividend yield (as decimal, or in % format)
call/put (1 for Call, 0 for Put)
calculation model (1 for Cox-Ross-Rubinstein binomial tree, 0 for Black-Scholes formula)