Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Logout MarketXLS
OptionsOptions strategies

Import Option Chain Data From Thinkorswim In Excel(Step By Step Guide)

Written by Vishal Nayyar (Individual Contributor)
Thu Nov 26 2020
Thinkorswim
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Thinkorswim
 

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

Step 1

ThinkorSwim platform

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.

Step 2

ThinkorSwim platform

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.

Step 3

ThinkorSwim platform

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.

Step 4

ThinkorSwim platform

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.

Step 5

imported option chain to excel

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
46 -4.75 1.75 -3
45 -4.75 1.75 -3
44 -3.75 1.75 -2
43 -2.75 1.75 -1
42 -1.75 1.75 0
41 -0.75 1.75 1
40 0.25 1.75 2
39 1.25 0.75 2
38 2.25 -0.25 2
37 3.25 -1.25 2
  • 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

 References:

  1. https://www.youtube.com/watch?v=TWdOYvN9eIs
  2. https://www.youtube.com/watch?v=yD_3StDkIqI

 

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Stock screens
Public saved screens

Top 100 Gainers Today

Top 100 losers Today

Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONSGet started today
Search for a stock

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

Top MarketXLS Rank stocks

Dick's Sporting Goods Inc logo

Dick's Sporting Goods Inc

109.82
 
USD
 
5.18
 
(4.95%)
86
Rank
Optionable: Yes
Market Cap: 8,757 M
Industry: Specialty Retail
52 week range    
63.45   
   142.78
Dillard's Inc. logo

Dillard's Inc.

285.06
 
USD
 
12.30
 
(4.51%)
80
Rank
Optionable: Yes
Market Cap: 4,506 M
Industry: Department Stores
52 week range    
184.12   
   416.71
EOG Resources Inc. logo

EOG Resources Inc.

119.93
 
USD
 
8.20
 
(7.34%)
76
Rank
Optionable: Yes
Market Cap: 61,798 M
Industry: Oil & Gas E&P
52 week range    
80.67   
   147.99
STORE Capital Corporation logo

STORE Capital Corporation

31.36
 
USD
 
0.03
 
(0.10%)
75
Rank
Optionable: Yes
Market Cap: 8,941 M
Industry: REIT - Diversified
52 week range    
24.48   
   36.13
National Research Corporation logo

National Research Corporation

40.64
 
USD
 
0.84
 
(2.11%)
75
Rank
Optionable: No
Market Cap: 983 M
Industry: Diagnostics & Research
52 week range    
30.93   
   52.36
Chesapeake Energy Corporation logo

Chesapeake Energy Corporation

98.93
 
USD
 
4.72
 
(5.01%)
75
Rank
Optionable: Yes
Market Cap: 11,105 M
Industry: Oil & Gas E&P
52 week range    
56.75   
   105.93
NACCO Industries Inc. logo

NACCO Industries Inc.

48.70
 
USD
 
1.67
 
(3.55%)
74
Rank
Optionable: No
Market Cap: 346 M
Industry: Coal
52 week range    
28.32   
   63.19
Marathon Oil Corporation logo

Marathon Oil Corporation

24.97
 
USD
 
2.39
 
(10.58%)
74
Rank
Optionable: Yes
Market Cap: 14,494 M
Industry: Oil & Gas E&P
52 week range    
14.29   
   33.24
Destination XL Group Inc. logo

Destination XL Group Inc.

5.75
 
USD
 
0.33
 
(6.09%)
74
Rank
Optionable: No
Market Cap: 340 M
Industry: Apparel Stores
52 week range    
3.27   
   9.00
CF Industries Holdings Inc. logo

CF Industries Holdings Inc.

100.30
 
USD
 
4.05
 
(4.21%)
74
Rank
Optionable: Yes
Market Cap: 19,103 M
Industry: Agricultural Inputs
52 week range    
55.29   
   119.60

More Features