Import Option Chain Data From Thinkorswim In Excel(Step By Step Guide)
An option chain, also known as an options matrix, is a listing of all the available option contracts, both puts and calls, for a given security. In an Option chain, traders typically focus on ‘last price’, ‘net change’, ‘bid’ and ‘ask’ columns to hedge their stock positions from rapid price fluctuations.
In this article, I intend to cover how we can export Option chain data from the Thinkorswim platform to excel and use it with some basic techniques to hedge our stock positions. So, let’s cut to the chase without further wasting much time.
Here are the steps that you need to perform to import option chain data in excel
Go to the scan section (as shown in the pic above). Here you will enter all the options that you want to monitor in an excel sheet.
For this example, I will use options of those stocks which are common in both the S&P 100 and DOW JONES industrial average. So, you will fill in the required info for the Scan in & intersect with options respectively.
Continuing with the filtering process further, let’s say I want options with minimum & maximum delta of -0.5 & 0.5 respectively. I will enter it in the required fields as shown above & after that, I will choose options in the dropdown shown above. With the requirements being stated I will hit the Scan button.
The platform will show all the options that satisfy the required conditions stated in the previous steps. Now, click on the print section marked by an arrow above & select Export to Excel in the dropdown. After this, a pop-up message will appear instructing you how to paste the copied data. Click ok on it.
Open a new excel sheet and paste the copied content as shown above. This will add the dynamic content to your excel sheet. Great! now you know how to import data to your excel sheet.
With this, let’s introduce you to some of the basic techniques that you can use to hedge your stock positions using options. Assuming you are a beginner, I will try to use as little of the technical jargon as possible. Let’s get started.
Suppose Mr. X buys a stock at $12 per share. Mr. X is of the assumption that the stock price will go up but just to cover the downside risk he buys a put option at $5 with a strike price of $9 for a 1 year period. Now, at the end of the year, if the stock price reaches $16 he won’t exercise the option & will have lost $5. However, if in 6 months, the value of the stock decreases to $7, he can sell the stock that he bought (at $12) at $9. With the put option, Mr. X limited his losses to $3 per share. Without the put option, Mr. X would have lost $5 per share.
With this being said, let’s learn about another strategy to cover moderate volatility. This strategy is called the Bear put spread. In this strategy, the investor buys a put with a higher strike price and also sells one with a lower strike price, both with the same expiration date. This only provides limited protection against volatility because the maximum payout is the difference between the two strike prices. Let’s understand this with the help of an example.
Suppose I am moderately bearish on a stock & the stock is trading at $40. To apply the strategy I will then purchase a put option contract with a strike price of $45 for a cost of $4.75 and sell one put option contract with a strike price of $40 for $1.75, both for the same time period (let’s say a year). In this case, the investor will need to pay a total of $300 to set up this strategy ($4.75 – $1.75 i.e. $3). So now, what could be the possible cases that might arise at the end of the year?
|Stock Price at Expiration||Long 45 Put Profit/(Loss) at Expiration||Short 40 Put Profit/(Loss) at Expiration||Bear Put Spread Profit/(Loss) at Expiration|
Case 1: When the stock price is above the higher strike price.
For example, let’s say that the stock price is $46. In such a case, we can’t exercise our put option thus we subtract 4.75 from our calculation of the final position. At the same time, the party which bought the option from us also won’t exercise the option thus we add 1.75 to the calculation which leaves us at a loss of $3.
Case 2: When the stock price is in between the higher & lower strike price.
Let’s say that the stock price at the end is $41. In such a case, we will exercise our option, leaving us a total of -0.75, after including option cost (i.e. $4 – $4.75). On the other hand, the other person won’t exercise the option, giving us $1.75. This leaves us with a profit of $1.
Case 3: When the stock price is below the lower strike price
Let’s say that the stock price is $37. In this case, the value that we derive from our long put position is $3.25 (i.e. $8 – $4.75). Contrarily, we lose $1.25 from our short put position (i.e. $3 -$1.75). This leaves us with a total of $2 profit.
As we can see, this strategy can help a lot to minimize risk because for small deviations the final value derived from the respective put positions hovers around 0.
With this example, I bring this article to an end. Hope you understood the techniques of hedging and are ready to put your knowledge to practice with the Thinkorswim platform.
None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person.
The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein.
the article is written for helping users collect the required information from various sources deemed to be an authority in their content. The trademarks if any are the property of their owners and no representations are made
Get Real-Time Options Pricing in Excel
Get started today