How Does It Work?
Although this model has some very powerful analytic features, it does not use macrocode. The model
was built using Bloomberg’s Excel add-in and the Bloomberg’s cell referencing DDE formulas. It uses
Bloomberg’s Bulk data formula to retrieve the entire option chain for a particular instrument. After
the instrument codes for the options are retrieved into the cells of a column on the worksheet,
these cells are referenced by Bloomberg’s BLP formula to retrieve real-time trading information on
the individual option contracts. Bloomberg offers many fields related to options trading. The fields
we have selected include:
- The Striking Price
- Put or Call Identifier
- The Expiration Date
- The Last Price
- The Bid
- The Ask
- The Open Interest
- The Volume
- The Intrinsic Value
- The Time Value
- The Theoretical Value (calculated field)
- The Delta (calculated field)
Since the DDE formulas allow cell referencing to retrieve the latest real-time option data on all
the options available for a particular instrument, all you need to do is to type the instrument symbol
in a single cell. Excel’s calculation engine does all the data retrieval. The bulk data formula retrieves
the instrument codes for all the available options and the BLP DDE formula retrieves the fields of
information for the option contracts.
You then use Excel’s Auto Filter to make this mass of information meaningful. Since the option
information is in tabular form you can apply Excel’s auto filter feature by:
- clicking on a cell in the table
- selecting Filter from Excel’s Data menu
- checking Auto Filter
When the auto filter is applied, you will see the down pointing triangles in the field name headings
of your database table. Clicking on these triangles lets you apply criteria which will make the information
meaningful. If you just wanted to view call options, which expire in May, you would click in the auto
filter triangle in the Put or Call field and select Call.
You would then click on auto filter triangle in the Expiration Date field and select the expiration date
for May. You can now monitor the trading only in the May call options. All the other unnecessary information
is hidden.
If you want to do more complex analysis such as evaluating a spread, you would click on the Strike Price auto
filter and select Custom. If you wanted to view only the options with strike price of 25 and 30 you would enter
criteria which would show options whose strike price equals 25 or the strike price equals 30.
|
