January 18, 2012 | By: Kerry Langstaff | Tech

If stock market pundits like Franklin Templeton’s Mark Mobius are to be believed, markets are going to be just as volatile as they were in 2011. The VIX index, a popular measure of implied volatility, made multiple excursions into the 40s last year, finally ending 2011 at 23.8, up almost 32% from 2010.

Options trading strategies cater well to volatility, offering the opportunity to capitalize on market volatility as a source of investment. That being said, most option traders typically focus on one option Greek Delta. While Delta certainly helps in mitigating the risk of an open position, it’s important to trade Delta in conjunction with the other option Greeks Vega, Theta, and Gamma. Each option Greek measures a different dimension of the risk in an option position. The aim of an options trader is to manage the option Greeks so that all risks are acceptable.

In this post, I’ll provide step-by-step instructions on how to create your own real-time Excel-based option Greeks calculator with on-demand options data from Xignites market data cloud. This will facilitate your option trading process, by allowing you to look at all the option Greeks, side by side.

Although, this is our fifth post in the series on combining the power of Xignite and Excel, it’s our first one where we describe our powerful API mash-up platform, Splice. which provides you with the flexibility to get all the data you need in a single web service call.

Creating a Composite Black-Scholes Web Service API

Options are never traded by examining the option Greeks or the underlying stock in isolation. Traders need to look at the complete picture before making a decision. The composite web service API, we will create, addresses this need. It also provides the Black-Scholes option value, which can be used as a benchmark, for your own option Greek calculations. In this example, we will mash-up the following web service operations:

A composite web service can be created by combining any of Xignite’s web service APIs. Keep in mind; you must be logged into Splice to do the same. If you don’t have an account yet, you can just sign up for a free trial .

After you login, click on create Splice, at the top of the page.

Clicking on the create Splice feature, will take you to the Splice editor, which offers a simple drag and drop UI.

The left pane lists all the web service APIs that Xignite offers. The right pane acts as a whiteboard where the web service APIs can be mashed-up.

The next step just involves dragging any web service from the left pane to the white board and linking the inputs and outputs you require for your web service call.

In this example, we used the GetLastSale. GetEquityOption and GetBlackScholesOptionValue web service APIs. The mash-up I created is called BS. You can access it on the Splice Studio community. You can also clone it and make modifications.

Note: For a more detailed explanation on how to create a Splice, watch this 5 minute intro video .

Importing Option Data into Excel

We covered how to import data into Excel. using web services in a previous post. The only difference in this post is that, you are now using a composite web service API that has been created by you (Isn’t that exciting. ).

After you have imported data from the customized Black-Scholes API, there is another piece of data you need to import to complete the option Greek calculations. One of the most important inputs in option Greeks calculations is the volatility. In this example, I calculated historical volatility based on Google’s (GOOG) one year price movements. The historical data can be accessed using Xignite’s GetHistoricalQuotesAsof web service API. It lets you specify the period type (daily, weekly, monthly, quarterly or annually) and period, so that you can set up volatility calculations based on your volatility strategies. The GetHistoricQuotesAsof web service API produces a table as shown below:

Setting up an Option Greeks Calculator

All you need to do now is, provide the inputs and set up the calculations. The formula for all the option Greeks is present in the attached Excel spreadsheet .

To the make the process more intuitive, the attached spreadsheet contains a button that updates the stock quotes and also calculates the latest option Greeks. The macro that does this is given below:

20Greeks.jpg /%

Clicking on the “Calculate Option Greeks” button takes the inputs from the excel sheet, plugs it into the Black-Scholes formula, and returns all the option Greeks in the format as shown below.

As you saw in this post, using the Splice platform gives you the ability to create custom web service APIs that match your unique option trading strategies. Coupling these customized web service APIs with Excel gives you ability to quickly create a real-time options data Greeks calculator so that you can trade on volatility with all the important option indicators at your fingertips. Give it a try and let me know what think..

__________

For more info on how Xignites market data cloud can improve your business processes, request a consultation with one of our data experts .

To test drive our market data APIs and experience for yourself how easy they are to integrate into your applications, request a free trial .