The Options Facility refers to a portion of the MIM Data Warehouse that has been organized to allow specialized analysis of options data. There are several LIM applications developed for extracting data from the Options Facility. This section specifically outlines how to use the Get Options feature in the MIM Excel Add-in. For more information on extracting data using other tools, please see the Options Facility document on the LIM Web site.
The data in the Options Facility can be divided into three distinct categories:
Options contracts
Standardized at-the-money forwards
Underlying equities
LIM has over 6 years of US equity and index option history available including: a complete history of the individual options contracts, standardized at-the-money forwards, and a full compliment of options related information for the underlying equities.
The options contracts and standardized options are updated daily for pricing, premium, implied volatility, and sensitivities. The underlying equities have options summary data including: option volume totals, open interest totals, and implied volatility.
There are two applications that have been developed to extract data from the Options Facility: the Get Options feature in the MIM Excel Add-in and a customized Options Command Line application.
The MIM API was enhanced to allow this integration of customized applications with options analysis. By using C API calls to XmimGetRecordsOptions our applications were developed to extract data from the Options Facility.
Using these tools, one can filter the options data by put/call, strike price, expiration date, and perform special handling for non-computable values. In addition, the options product suite extends the capability to unlimited possibilities for an options trader to develop and monitor trading methodologies.
Important Note for Options Contracts The standardized at-the-money forwards and underlying equities can be researched using any of our suite of analysis tools: XMIM, MIMIC or the MIM Excel Add-in. It is important to make the distinction that the options contracts are accessible using the following tools: the MIM Excel Add-in and the Options Command Line application. For more information on the Options Command Line application, please see the Options Facility document on the LIM Web site. |
The following list outlines the columns available for use with options data:
Date (values always returned)
Expiration Date (values always returned)
Put/Call (values always returned)
Strike Price (values always returned)
BidPrice
OfferPrice
LastTradeDate
Volume
OpenInterest
ImpVol
OptionDelta
OptionGamma
OptionVega
OptionTheta
Each equity, as the underlying issue, will have some additional columns of options related data:
CallVol
PutVol
TotalVol
ImpVol
Note that this column is the average of the 30 put and 30 call in the standardized options.
CallOI
PutOI
TotalOI
Use the MIM Excel Add-in Get Options feature to access the special columns established for analyzing options data. This function takes the data that is entered on the spreadsheet and automatically populates the relation, column, date, expiration date, strike price and put/call fields in the program.
Look at the option Gamma for IBM for dates ranging from 5/7/2002 to 6/12/2002 and expiration dates ranging from 5/02 to 6/02 with strike prices ranging from 50 to 70.
In order for the software to automatically pick up the entries from the spreadsheet and fill in the dialog box, the user must fill out the spreadsheet in a specified order and click on the first relation cell entry before choosing Get Options from the MIM menu. Selecting the first relation cell entry on the spreadsheet, tells the program where the data entries begin on the spreadsheet. All the data entered will automatically populate the corresponding fields in the display box.
| The user may start entering data from any point on the spreadsheet but must follow the specified order from that point on. |
Enter relation data on any row of the spreadsheet. On the next row enter the column data. The following row is reserved for the date fields in the first two columns, the expiration dates in the next two columns, the strike price range in the next two columns and the put/call/both option in the last column cell. The data in the spreadsheet must be aligned in a contiguous manner so that all the data will be selected. If this format is not followed the data will not populate the next dialog box correctly.
| The user has the option of entering data on any of the cells in the spreadsheet, not in a contiguous manner. After Get Options is selected from the MIM menu the dialog box will display. Instead of having the data automatically populate the fields in the display box, the user can select the individual cells in the spreadsheet to fill in each field of the display box. |
The following is an example of how to select information in a cell on a spreadsheet to populate a field in the display box:
Select the bar next to the destination cell.
The dialog box will close and the Excel
Worksheet opens.Select the desired cell on the spreadsheet, then select the
icon to return to the dialog box. The
dialog will now contain the spreadsheet entry.
The following outlines the process in detail. Follow along with this example to learn how to enter options data in a spreadsheet and have the entries automatically populate the query dialog box.
| An older version of the Options Facility allowed the user to enter as many relations as they wanted, but because of the new formatting functionality in the latest version, the user can only enter and view data for one relation at a time. |
Enter the symbol names in the first row. Type IBM_Options in cell A:1.
Next, enter the column data. Type OptionGamma in cell A:2. If you wanted more than one column, then you would enter the next column data in cell B:2, then C:3 etc.
On the next row, enter the dates, expiration dates, strike price ranges and the put/call options. Enter P or p for Put and C or c for Call. If left blank the default is to display both Puts and Calls. The following graphic details the entries:
In Excel, sometimes the cells pickup the formatting of a previous cell entry. The Strike Price entries may get reformatted as Date entries. To fix this, right-click on the Strike Price cells and select Format Cells, then change the formatting to General.
Select cell A:1 then select Get Options from the MIM menu.
You must select the first relation cell entry to designate where the program will start pulling the data from the spreadsheet.
The following screen displays:
The fields will automatically populate the fields. For Source Ranges, cells A:1 will display for the Relation Name cells. Cells A:2 will display for the Column Name cells.
For Destination Range, the data will populate in cell A:4.
For Put/Call, the default is for the Both box to be checked. Checking the Both box will list both Puts and Calls. On our spreadsheet, we entered a “P” for Put so the Put box is automatically checked.
For Data Format, the default is for Unformatted to be checked. If you want to format the output so that the data is listed in a column format check Formatted. For the following example keep the default Unformatted option. At the end of the example we will show how the Formatted data will display.
For Non-Computable Values, select this check box so that –99.99 non-computable values will not display on the spreadsheet. For our example, check the Skip all Non-Computable Values check box. By default, this box is not checked. Note: “non-computable value (-99.99)” means that the value is trading outside its intrinsic value and therefore is not meaningful or applicable.
The Date Range field will automatically populate with a date range from cell A:3 for the From box and B:3 for the To box. The date range fields are optional. If a Date Range is not entered, all the dates for the symbol will be queried. The Date Range must be entered in the following format: mm/dd/yyyy .
The Expiration Date Range field will automatically populate with a date range from cell C:3 for the From box and D:3 for the To box. The expiration date range fields are optional. If an Expiration Date Range is not entered, all the dates for the symbol will be queried. The Expiration Date Range may be entered as mm/yy or mm/dd/yyyy. If mm/yy is entered, the cell will display as mm/01/yyyy.
The Strike Price Range field will automatically populate with a date range from cell E:3 for the From box and F:3 for the To box. The Strike Price Range fields are optional.
Select a Fill Option from the pull-down menu. A default NaN is filled if there is no data point available for a given date. Use the pull-down menu to have the data filled in other ways (e.g. Filled Forward or Backward, Linear, Geometric or Logarithmic interpolated values). If Skip all-NaN records is selected then all the NaN values will not display.
Select OK to continue.
The following shows a portion of the results of the query with the Unformatted option selected:
For the next example, we've added a few more greeks to the list: OptionDelta, OptionTheta and OptionVega. We've started the entries on cell B:1 to show that you can start your entries anywhere on the spreadsheet. This time we're just entering the start date 5/2/2002. All the data will display back to 5/2/2002 and both Puts and Calls will be listed.
Now put your cursor back on the IBM_Options cell, cell B:1 and select MIM>Get Options. Remember that we can enter data starting at any point on the spreadsheet and we have to select the starting cell before we choose Get Options. We are going to show how the data displays when the Formatted option is selected.
Note that under Data Format, the Formatted option is checked.
The following graphic shows a portion of the results.
The Quote date, Expiration date and Underlying prices are listed as well as both Puts and Calls and each Option type.