Stock Options Chain Analysis Using Excel

Introduction

Simple strategies for trend analysis in stock options data

Stock data analysis is one of the most endearing and exhaustive topics. Endearing because who does not want to earn profits in the stock market. Exhaustive because the length and breadth of this topic are infinite. You can easily get lost and overwhelmed with the amount of information that bounces at you when you explore this topic. So in this article, I will be focusing on one particular type of stock analysis i.e Options Chain analysis using Excel.

Option chain comprises data pertaining to option strikes of a particular stock or index in a single frame. It gives you all the specific data you need while trading in options. In this article, I will list out all the key concepts required to understand the option chain. I will show how to import option data to Excel and build custom reports based on option strategies. These reports will in turn help you to predict trends for options trading.

This article was published as a part of the Data Science Blogathon.

What is Option Chain Analysis?

Option chain analysis involves studying the available options contracts for an underlying asset, such as a stock, commodity, or index. An option chain is a comprehensive list that displays the options contracts for the underlying asset, including the strike price, expiration date, implied volatility, and bid/ask prices.

Traders can use option chain analysis to identify potential trading opportunities by examining the relationships between different options contracts and their corresponding prices. This analysis helps traders to determine the most favourable options to buy or sell based on their investment goals and risk tolerance.

Option chain analysis is also useful in evaluating potential profits or losses and identifying potential market trends or shifts in sentiment. By analyzing the option chain, traders can identify potential shifts in the demand for the underlying asset and make informed trading decisions based on this information.

Key Concepts for Stock Options Chain Analysis

Derivative – is an instrument that derives its value from a specified asset. It is a contract that takes place between two people.

Option Contract – is a type of Derivative. These are of two types, Call (CE) and Put (PE). Option contract takes place between a buyer and a seller (writer). An option contract gives the buyer the right but not the obligation to buy or sell an underlying asset at a specified strike price on a specified date.

Premium – is the amount paid to book a call or put option contract. This amount is decided by the seller.

Strike Price – is the price at which a specific derivative contract can be exercised.

Expiry Date – is the date at which the option contract expires. Normally every option contract expires on the last Thursday of every month. Based on expiry, the option contract is categorized into 3 groups, Running option contract (nearest expiry), Middle option contract (mid expiry), Far option contract (farther expiry). For example, if for a contract the nearest expiry is last Thursday of March, then mid expiry will be last Thursday of April, and far expiry will be last Thursday of May. Once the contract expires, a new contract for the next month is generated. As a buyer or seller, you can hold the contract till the expiry. Thereafter if you don’t buy or sell then the contract expires, and you will lose the premium amount.

Call option contract – is a contract that gives the buyer the right but not the obligation to buy an asset. A premium amount must be paid to the seller for booking the asset. For example, say the strike price for a contract is Rs.150 when the buyer booked it for a premium of Rs.20. Now, after one month if the price of the asset increases to Rs.200, then the buyer can go ahead and buy and book a profit of Rs.30 after deducting the premium. Suppose if the price decreases to Rs.100 then the buyer is not obligated to buy. Here the buyer only stands to lose the premium amount. This is known as a Call option contract (Right to buy).

Put option contract – is a contract that gives the buyer the right but not the obligation to sell an asset. A premium amount must be paid to the seller for booking the asset. For example, say the strike price for a contract is Rs.200 when the buyer booked it for a premium of Rs.20. Now, after one month if the price of the asset decreases to Rs.150, then the buyer can sell the asset and book a profit of Rs.30 after deducting the premium. Suppose if the price increases to Rs.300 then the buyer is not obligated to sell the asset as the price has risen. Here the buyer only stands to lose the premium amount. This is known as Put option contract (Right to sell).

ATM, ITM, OTM – based on the underlying price of the asset, options contracts can be categorized as In the Money (ITM), At the Money (ATM), and Out of the Money (OTM). If the strike price is less than the market price then it is ITM, if the strike price is equal to the market price then it is ATM, and if the strike price is greater than the market price then it is OTM.

In options trading, contracts are bought or sold in chunks/lots. For example, one contract will comprise 100 shares. So, you always buy or sell in terms of the number of contracts and not the number of shares that each contract has.

Option Chain Deconstructed

An options chain is a listing of all available options contracts for a given index/stock. It provides detailed quotes and price information. It shows all listed puts, calls, their expiration, strike prices, and volume for a single underlying asset within a given maturity period. The option chain is categorized by expiration date and segmented by calls and puts. Here is a screenshot of a portion of the option chain for Nifty taken from the NSE website.

Data in the option chain chart is grouped into 4 quadrants. Two for Calls (Yellow and White) and two for Puts (Yellow and White). The Yellow quadrant data is for In the Money contracts and the White quadrant data is for Out of the Money contracts. This is applicable for both Call and Put, but the meaning of ITM and OTM has reversed accordingly.

Some of the key columns that are required to understand the option chain chart/matrix are:

OI (Open Interest) – is the number of contracts that are traded but not exercised. It indicates the interest of traders for an option at the given strike price. Higher OI means more interest among traders, and hence indicates high liquidity for the buyer/seller to trade their options.

CHNG IN OI – is the change in OI within the expiration period. It indicates the number of contracts that are closed or exercised.

VOLUME – is the total number of contracts that are traded for a specific strike price in a given period. It is calculated on daily basis.

IV (Implied Volatility) – is the indication of how the market reacts to the price movement of an underlying asset.

LTP (Last Traded Price) – is the last traded price or premium price of an option.

CHNG – is the net change in LTP. It is indicated as a positive or negative value. Positive change means a rise in price (shown in green). A negative change means a decrease in price (shown in red).

BID QTY – is the number of orders for buying at a specific strike price. It indicates the current demand for the order.

BID PRICE – is the price for the latest buy order. If this price is higher than the LTP then it indicates higher demand for the option and vice versa.

ASK PRICE – is the price of the latest sell order.

ASK QTY – is the number of sell orders that are open. It indicates the option supply.

Importing Options Data in Excel

Now that you have an understanding of the option chain, I will show in this section how to import option chain data in Excel. Once the data is loaded you will learn various strategies to analyze this data and predict trends.

There are two options to get the data. One is the simple and straightforward method of downloading the CSV file for options data from the NSE website. The link to download the CSV file is given at the top of the option chain chart. Once you select the Options Contracts type or Symbol, Expiry Date, or Strike Price, download the CSV file.

Another option is to link to live data on the NSE website, to analyze options data in real-time. The data is in JSON format that has to be parsed from the NSE website. I will be explaining the process for this in the next part of this article along with different types of technical analysis.

For the options chain data analysis, I will use only some key columns and delete the remaining. The criteria for column selection will be explained when I discuss the strategy. For now, the columns that I will retain in both CALL and PUT sides are: OI, CHNG IN OI, VOLUME, LTP, CHNG, and STRIKE PRICE. Once the unwanted columns are deleted fill the empty cells with zero so that the computations are not affected by hyphens. These hyphens in the chart indicate no activity happening for the given period for the respective strike price.

Options Chain Data Analysis Strategy

The preprocessed data is now ready for analysis. Before diving into analyzing the data, you need to understand the strategy for this analysis. There are at least 100 different strategies based on which traders analyze the data. I will focus here on few commonly used strategies that will help you understand the market trend.

The key features of the options chart that is used for building the strategy are Change in price, Open interest, Change in open interest, and Volume. Few strategies omit Volume, few include other features like LTP and Implied volatility. As I mentioned earlier there are several combinations that can be used to understand the data and its movement. Buy is termed as Long and Sell as Short. The upward market trend is referred to as Bullish and the downward trend is Bearish. Based on these terms and features I have prepared a strategy table that will help in building the analysis.

Based on the strategy shown above, I have used conditional formatting and IF conditions in Excel to format my data. I have considered two conditions, less than zero and greater than zero to indicate the increase and decrease in a price change and change in open interest. Here “squaring” means a trader buys or sells a particular quantity of stock or option and later in the day reverses the transaction, hoping to earn a profit. Profit booking means exercising the options contract.

Options Chain Data Analysis

Now that the data is prepped up and strategy is in place it’s time to build the analysis. For this, insert the appropriate number of interpretation columns (four each) on both Call and Put side of the chart. Then use the following IF conditions to customize the outcome:

Price Change — =IF([@[CHNG Put]]>0,”UP”,IF([@[CHNG Put]]<0,”DOWN”,””))

OI Change — =IF([@[CHNG IN OI Put]]>0,”UP”,IF([@[CHNG IN OI Put]]<0,”DOWN”,””))

Interpretation — =IF(AND([@[OI Change]]=”UP”,[@[Price Change]]=”UP”),”Long Buildup”,IF(AND([@[OI Change]]=”UP”,[@[Price Change]]=”DOWN”),”Short Buildup”,IF(AND([@[OI Change]]=”DOWN”,[@[Price Change]]=”DOWN”),”Long Unwinding”,IF(AND([@[OI Change]]=”DOWN”,[@[Price Change]]=”UP”),”Short Covering”,””))))

Trend — =IF(OR([@Interpretation]=”Long Buildup”,[@Interpretation]=”Short Covering”),”Bullish”,IF(OR([@Interpretation]=”Short Buildup”,[@Interpretation]=”Long Unwinding”),”Bearish”,””))

Next use conditional formatting to enhance the visualization of the data interpretation. You can use a combination of formatting based on text and numbers. Use Icon sets, Data bars, and Color scales options in Conditional formatting for more varied analysis of different features in the chart (courtesy: Excelling Trade).

Now the chart is ready to be presented as a report for trend analysis in Options trading. You can make this chart dynamic by connecting it to live data. You can also import the data for different expiry dates and automatically refresh it. Based on strategies the analysis also varies. You can opt for technical analysis using line and bar graphs.

Frequently Asked Questions

Conclusion

There is no end to the amount of information you can extract from different strategies in Options Chain analysis. I will roll out more articles in this series that will delve into connecting to real-time options data and technical analysis using Excel. Meanwhile, to learn more about options trading and stocks in general you can check out this comprehensive guide from Zerodha.

The media shown in this article are not owned by Analytics Vidhya and is used at the Author’s discretion.

[email protected]

I am a tech-savvy data analyst and a passionate tech blogger. With an insatiable curiosity for the latest in technology and a knack for turning raw data into meaningful insights, I'm on a constant quest to explore the ever-evolving digital landscape.