Entry of option/underlying prices Prices can be entered on the position sheet using various methods..
a) Manually: Enter the underlying price, for example the number 310, in E27. Then enter the option prices in column G besides the small buttons. For example enter the number 40 in G3, then click on the small button in that cell. These buttons will calculate the corresponding implied volatility for one option only. Clicking on "all call IVs" or "all put IVs" (buttons row 13/14) calculates implied volatilities for all "active option lines", i.e. those calls and puts that have a long/short position indicated in column A.
Keeping track of entry prices: You can enter the actual execution price in column F. No macro will change the entry prices, and they can be used later to keep track of your trade debit/credit (see below) . For our Iron Condor, enter the following prices into F3 / F4 / F15 / F16: 39.95 / 57.00 / 11.10 / 23.20.
b) Automatic: Much more comfortable ... In C29 you will find a dropdown menu where you can choose your data source:
Yahoo - The first choice, and by far the quickest.
CBOE - Fetches stock/index and option prices one by one, slower.
Own source - See explanation below.  Click on one of the 3 buttons in row 30 ("to open position", "to close position", "between bid-ask") to refresh underlying and option prices, enter them into your position and recalculate implied volatilities and your position value, all in one go.
Please do this now for the Iron Condor example trade. Entering the net debit / credit How can the calculator know how much your profit or loss is (and draw a risk graph of your position)? Answer: It is essential that you enter your position debit or credit into I30. If you make a change to the position, add an option leg or change an existing leg's quantity, the debit/credit needs to be updated to reflect that change. There are two buttons down at L29/L30 that will help you with this. One calculates the debit/credit based on the entry prices in column F, the other uses the model prices. If you are running a very complex trade with many adjustments, you need to either keep track of your net debit yourself, or just always "mark the position to market" using the button "calculate debit with model prices" (L30). The latter has the advantage that the risk graphs will show where the position goes from now, instead of looking at accumulated profit or losses from the past. What is "own source"? If you have a way to get the bid and ask prices of your options into cells E39 to F60, and the stock price into E36 by yourself, you can recalculate the position from those prices.
At this moment the only way to do this I am aware of is with Interactive Brokers: Link the cells mentioned above to realtime quotes using their API and TWSDde.xls sheet. After installing their API (download from their website), find the TWSDde.xls file that comes with it. Start the Trader Workstation, open the TWSDde.xls file, set it up so it has the streaming realtime quotes needed. Then open the Excel Option Calculator and link E36 to the stock/index price, and the active lines in E39 to F60 to the options' bid and ask.
The formulas look like this: No need to type the whole formula - just type the "equals" sign = , then go to the TWSDde.xls window directly, click into the cell you want to link to, then hit return.
Be careful: Make sure you have "own source" selected in C29. Retrieving data from Yahoo! or CBOE will overwrite your formulas.
I am investigating how to get quotes from OptionsXpress into the Excel Option Calculator. |
