Options
Equity option data can be queried using the Quick Data icons (Quotes, Chart and History) in two methods in addition to the Options Prices icon.
In the first method, you can enter the ticker symbol into the search for symbol dialogue and if the security has listed options there will be an Options hyperlink (this method will also work for equity index options).
Once you click on Options, all of the available expirations for the underlying stock will display.
After selecting an expiration, all of the strikes will now be visible.
Now you can select individual strikes (double click), use Shift+ arrow keys to select multiple options at once or use Ctrl + A to select every listed option for the expiration.
When all options for this expiration are selected and you want to add options from another expiration for this underlying, simply click the arrow icon and you will view all of the expirations.
You can also use the “X” to return to the search for symbol box and add more options or underlying stocks or symbols. When all strikes and underlying contracts are selected and all appropriate fields are applied, click insert and option data will be displayed in your Excel sheet.
The second method of pulling equity option data via the Quick Data icons is to use the Exchange branches in the symbol browser and select a stock exchange.
Under stock exchanges the securities are then broken down alphabetically by company name. Once you select the branch required for your stock, another leaf will display labeled Options.
Selecting Options will narrow the list to only securities with listed options. When a ticker like AAPL is selected, all expirations are displayed.
Clicking on an expiration will open up the options and you can select any or all strikes using the same hotkeys mentioned in the first method of obtaining option data. Once all of the applicable fields and history selections have been made click insert and the data will render in the spreadsheet.
Options Prices Icon
Using the Options Prices icon you insert futures, equities, and equity index options chains into excel.
After clicking on the Options Prices icon or the Options Chain selection, the Insert Options dialogue will display where you can use the search for symbol field or find optionable stocks and futures options through the symbol browser tree.
Once the underlying instrument has been selected, add any fields you wish to display in the options chain. Note that the fields will vary depending on whether an equity option or futures option has been selected.
When a equity or equity index is applied, Put / Call values are available to be added as fields. These values will display atop the option chain once inserted into the sheet.
Now the Parameters can be selected. Here you can change the expiration using the ‘Expiration Dates’ drop down, update the 'View' type and number of strikes and change the ‘Strike Display’. The Static strike display will lock the option chain as the underlying moves. Dynamic strike display' will update the range of strikes as the underlying moves.
If you would like to insert all expirations at one time into your excel sheet, the ‘All’ selection will insert all open expiries. You can also insert all monthly or all weekly expirations at one time by clicking on those respective links. Multiple expiries can also be added through the Expiration dates drop down. Here you can select all expirations that you want to insert by checking the box next to expiration date.
There are three different ‘Views’ that can be applied for option data. The default is Side-by-side with 10 strikes applied. This view will display the 10 closest calls and puts near ‘at the money.’ By clicking on the default 10 drop down, you can opt to show additional strikes, add your own value of strikes to display or opt to view all open strikes for that expiration(s).
The second view is Side-by-side range where you can set a custom range of strikes to display.
The last view is Stacked. Similar to that of Barchart.com, the Stacked view lists calls and puts one on top of the other. Using Stacked, you can set the amount of strikes in and out of the money you wish to display for both calls and puts. Using the Apply to all option will update all in and out of the money fields at one time with the selected value.
Once completed, click Insert and the equity option chain will display in the excel spreadsheet.
For futures options, depending on the underlying contract, selecting the option type will be necessary (default is American). The Expiration dates drop down will allow you to choose different expirations available regardless of the underlying selected. Number rows (strikes) can updated and the strike display can be static (rows do not update with underlying) or dynamic. The Select All Expirations button can applied to display all expirations of the options type selected, or individual expiries can be selected from the expirations drop down.
By default, the Highlight Strikes parameter is checked. This will ‘highlight’ the strikes column similar to Barchart.com.
If multiple expirations with different underlying contracts are selected, hover your cursor over the futures symbols to view all of the underlying contracts.
Favorites
After inserting any underlying’s option chain, the Favorites tab will save these instruments for easy insertion later.
Upon opening Favorites, you will see a list of the last 20 symbols whose option data was viewed. Double click or click and drag a symbol to add the underlying to the insert dialogue.
The Favorites dialogue can be enlarged by clicking on the pin icon in the upper right corner and then hidden by clicking the pin icon once again.
Option Removal
Options inserted into a worksheet can be quickly removed by highlighting the Option anchor cell, Clicking on the Option icon and selecting the Clear button.