Stock Option Chain Analysis with Excel

Introduction

Simple Strategies for Trend Analysis in Stock Option Data

Stock market data analysis is one of the most endearing and comprehensive topics. Affectionate because who does not want to make a profit in the stock market. Comprehensive because the length and breadth of this topic are infinite. You can easily get lost and overwhelmed with the amount of information that bounces back when you explore this topic.. Then, in this article, I will focus on a particular type of stock analysis, namely, option chain analysis using Excel.

The option chain comprises data pertaining to the option strikes of a particular stock or index in a single frame. Gives you all the specifics you need while trading options. In this article, I will list all the key concepts needed to understand the option chain. I will show how to import option data to Excel and create custom reports based on option strategies. These reports, at the same time, help you predict trends in options trading.

Key Concepts for Stock Option Chain Analysis

Derivative – it is an instrument that derives its value from a specific asset. It is a contract that is made between two people.

Option contract – is a kind of derivative. They are of two types, Call (THIS) y Put (ON). The 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 specific strike price on a specific date.

Before – is the amount that is paid to reserve a purchase or sale option contract. This amount is decided by the seller.

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

Date of Expiry – is the date the option contract expires. Normally, each option contract expires on the last Thursday of each month. According to expiration, the option contract is classified into 3 groups: Option contract in execution (nearest expiration), Intermediate option contract (average maturity), Far option contract (later expiration). For instance, if the closest expiration date for a contract is the last Thursday of March, the average maturity will be the last Thursday in April and the far maturity will be the last Thursday in May. Once the contract expires, a new contract is generated for the following month. As buyer the seller, you can keep the contract until expiration. Since then, if you do not buy or sell, the contract expires and you will lose the premium amount.

Purchase option contract – is a contract that gives the buyer the right, but not the obligation, to buy an asset. A premium must be paid to the seller for reserving the asset. For instance, Suppose the strike price of a contract is 150 rupees when the buyer hired him for a premium of 20 rupees. Now, After one month, if the asset price increases to 200 rupees, the buyer can go ahead and buy and record a profit of 30 rupees after deducting the premium. Suppose that if the price falls to 100 rupees, the buyer is not obliged to buy. Here the buyer only runs the risk of losing the amount of the premium. This is known as a purchase option contract (right to buy).

Put option contract – it is a contract that gives the buyer the right but not the obligation to sell an asset. A premium must be paid to the seller for reserving the asset. For instance, Suppose the strike price of a contract is 200 rupees when the buyer hired him at a premium of 20 rupees. Now, After one month, if the asset price decreases to Rs. 150, then the buyer can sell the asset and record a profit of Rs. 30 after deducting the premium. Suppose that if the price increases to 300 u.m., the buyer is not obliged to sell the asset as the price has increased. Here the buyer only runs the risk of losing the amount of the premium. This is known as Put option contract (right to sell).

ATM, IT, OTM – based on the underlying price of the asset, Option contracts can be classified as In the Money (IT), At the Money (ATM) y Out of the Money (OTM). If the strike price is less than the market price, so it's ITM, if the strike price equals the market price, so it's ATM, and if the strike price is higher than the market price, then it's OTM.

In options trading, contracts are bought or sold in parts / lots. For instance, a contract will include 100 Actions. Therefore, always buy or sell in terms of the number of contracts and not the number of shares each contract has.

Deconstructed option chain

An option chain is a list of all the option contracts available for an index / determined action. Provide detailed quotes and pricing information. Shows all listed buy and sell options, its expiration, the strike prices and volume of a single underlying asset within a given maturity period. The option chain is categorized by expiration date and segmented by call and put options. Here is a screenshot of a part of the option chain for Nifty taken from NSE website.

The data in the option chain chart is grouped into 4 quadrants. Two for calls (yellow and white) and two for put (yellow and white). The data in the yellow quadrant is for In the Money contracts and the data in the white quadrant is for Out of the Money contracts. This is applicable for both Call and Put, but the meaning of ITM and OTM has been reversed accordingly.

Some of the key columns required to understand the chart / options string array are:

HI (open interest) – is the number of contracts that are negotiated but not exercised. Indicates traders' interest in an option at the given strike price. A higher OI means more interest among traders and, Thus, indicates high liquidity for the buyer / seller negotiate your options.

CHANGE IN OI – is the change in OI within the expiration period. 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 daily.

IV (implied volatility) – it is the indication of how the market reacts to the movement of the price of an underlying asset.

LTP (last negotiated price) – is the last traded price or premium price of an option.

CHANGE – is the net change in LTP. Indicate as positive or negative value. A positive change means an increase in price (shown in green). A negative change means a decrease in price (shown in red).

QUANTITY OF OFFER – is the number of purchase orders at a specific strike price. Indicates the current demand of the order.

OFFER PRICE – is the price of the last purchase order. If this price is higher than the LTP, indicates a higher demand for the option and vice versa.

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

CONSULT QUANTITY – is the number of open sell orders. Indicates the offer of options.

Importing option data into Excel

Now that you understand the option chain, in this section i will show you how to import option string data into 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 the options data from the NSE website. The link to download the CSV file is at the top of the option chain chart. Once you select the type of option contract or symbol, expiration date or strike price, download the CSV file.

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

For option chain data analysis, I will use only a few key columns and remove the remaining ones. The criteria for selecting columns will be explained when you analyze the strategy. For now, the columns that I will retain on the CALL and PUT sides are: HI, CHNG IN OI, VOLUME, LTP, CHNG y STRIKE PRICE. Once the unwanted columns are removed, fill empty cells with zero so calculations are not affected by dashes. These dashes on the chart indicate that there is no activity during the given period for the respective strike price.

Option chain data analysis strategy

Preprocessed data is now ready for analysis. Before diving into data analysis, you must understand the strategy for this analysis. There is at least 100 different strategies based on which traders analyze data. I will focus here on some commonly used strategies that will help you understand the market trend..

The key features of the option box that is used to build the strategy are Change in price, open interest, change in open interest and volume. Few strategies skip volume, few include other features like LTP and implied volatility. As I mentioned before, there are several combinations that can be used to understand the data and its movement. Buying is called Long and sell as short. The uptrend of the market is known as bullish and the downtrend is bearish... Based on these terms and characteristics, I have prepared a strategy table that will help build 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 a change in open interest. Here, "Square" means that a trader buys or sells a particular number of stocks or options and later in the day reverses the transaction, hoping to make a profit. Reserving profits means exercising the options contract.

Option chain data analysis

Now that the data is ready and the strategy is in place, time to build the analysis. For it, insert the appropriate number of interpretation columns (four each) both on the Call and Put side of the box. Then use the following IF conditions to customize the result:

Change of price– = YES ([@[CHNG Put]]> 0, “ABOVE”, AND ([@[CHNG Put]]<0, "DOWN", ””))

RO change– = YES ([@[CHNG IN OI Put]]> 0, “ABOVE”, AND ([@[CHNG IN OI Put]]<0, "DOWN", ””))

Interpretation– = YES (Y ([@[OI Change]]= “ABOVE”,[@[Price Change]]= “UP”), “Long Buildup”, IF (Y ([@[OI Change]]= “ABOVE”,[@[Price Change]]= “DOWN”), “Short accumulation”, AND (Y ([@[OI Change]]= “DOWN”,[@[Price Change]]= ”DOWN”), "Long unwind", AND (Y ([@[OI Change]]= “DOWN”,[@[Price Change]]= "UP"), "Short coverage", ””))))

Trend– = YES (O ([@Interpretation]= “Long Buildup”,[@Interpretation]= “Short coverage”), “Bullish”, AND (O ([@Interpretation]= “Short accumulation”,[@Interpretation]= ”Long unwind”), "Bass guitarist", ””))

Then, use conditional formatting to improve the visualization of your data interpretation. You can use a combination of text-based and number-based formatting. Use icon set options, data bars and color scales in conditional format for a more varied analysis of different chart characteristics (courtesy: Excelling Trade).

Now the chart is ready to be presented as a report for trend analysis in options trading. You can make this graph dynamic by connecting it to live data. You can also import the data for different due dates and update it automatically. According to the strategies, the analysis also varies. You can opt for technical analysis using line and bar charts.

Conclution

The amount of information you can extract from different strategies in option chain analysis is infinite. I'll be releasing more articles in this series that will delve into connecting to real-time options data and technical analysis using Excel.. Meanwhile, for more information on trading options and stocks in general, You can refer to this complete guide to Zerodha.

Related