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 spreadshee t 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.
Note: 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.
Note: 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_Optionsin cell A:1.Next, enter the column data. Type
OptionGammain 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
Porpfor Put andCorcfor 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
Pfor 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.
The 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 a 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, a few more greeks were added to the list: OptionDelta, OptionTheta and OptionVega. The entries were started on cell B:1 to show that you can start your entries anywhere on the spreadsheet. This time just the start date 5/7/2002 was entered. All the data will display back to 5/7/2002 and both Puts and Calls will be listed.
Put your cursor back on the IBM_Options cell, cell B:1 and select MIM>Get Options from the MIM menu. Remember that you can enter data starting at any point on the spreadsheet and that you have to select the starting cell before you choose Get Options. The following show how the data displays when the Formatted option is selected.
| Note that under Data Format, the Formatted option is checked: |
Now for the results:
The Quote date, Expiration date and Underlying prices are listed as well as both Puts and Calls and each Option type.