Open Interest Analysis Excel for Options

Open Interest is believed as a Confirming Indicator by numerous traders across the globe. It generally confirms the market trend (whether its rising, falling or sideways) when used in conjunction with other parameters like volume and price. It also measures the flow of money in the market. This article would introduce you to the concept of Open Interest, and also demonstrate Open Interest Analysis Excel for Option contracts. The Excel sheet is downloadable from the end of this article.

Check out the other popular Excel sheets posted in this blog here.

Introduction to Open Interest

Open Interest represents the number of outstanding contracts for Future and Option contracts. For Options, every strike price of Option contract has an individual Open Interest value. It can also be defined as the total number of Future or Option contracts that has not been squared off. Open Interest tends to zero value at the expiry day of derivatives market as every one squares off their open positions.

Check out the below image which shows Open Interest of Nifty Futures:

Open Interest Nifty

Let’s consider a hypothetical situation to understand the concept of Open Interest in a better way:

Day 1: Ayesha buys one lot of Nifty 8300 CE, while Pooja sells one lot of Nifty 8300 CE. Total Open Interest: 1

Day 2: Deepika buys two lots of Nifty 8300 CE, and Heena sells two lots of Nifty 8300 CE. Total Open Interest:3

Day 3: Ayesha squares off her entire position (1 lot), and Heena buys one lot. Thus, effectively one Lot of Nifty CE gets offloaded from the market. Total Open Interest:2

Day 4: Pooja and Heena buy one lot of Nifty 8300 CE, and Deepika sells her two lots. Total Open Interest:0

To summarize, if both parties to the trade are initiating a new position ( one new buyer and one new seller), open interest will increase by one contract. If both traders are closing an existing or old position ( one old buyer and one old seller) open interest will decline by one contract. The third and final possibility is one old trader passing off his position to a new trader ( one old buyer sells to one new buyer). In this case the open interest will not change.

Relationship between Open Interest,Price and Volume

There is a very tight co-relation between Open Interest, Price and Volume. It helps to interpret the trend of Market very effectively. An increase in open interest along with an increase in price is said to confirm an upward trend. Similarly, an increase in open interest along with a decrease in price confirms a downward trend. An increase or decrease in prices while open interest remains flat or declining may indicate a possible trend reversal. Te relationship between Open Interest, Price and Volume can be summarized in the following table:

Price Volume Open Interest Market Trend
Rising Up Up Strong
Rising Down Down Weak
Declining Up Up Weak
Declining Down Down Strong

Open Interest Analysis Excel Sheet

This Excel sheet is designed to predict the Trend of each Option Strike of Nifty Futures. Please note that this is not our creation, as it is freely available in many online forums. We have just tried to make it more presentable so that even beginners can understand it. The Excel sheet attached with this post is for Nifty Options. It can be modified very easily for any other contract too.

The Trend determination is based on Option Price change and change in Open Interest with respect to previous day. Below is the summary of calculations present in Excel Sheet:

Price Change Change in Open Interest Signal
<0  <0 Long Liquidation
<0  >0  Short Buildup
>0  >0  Long Buildup
>0  <0  Short Covering

See the below screenshot:

Options Open Interest Analysis

How to use this Excel Sheet

Step 1: Download the Excel sheet from the link provided at the end of this post.

Step 2: Double click and open the downloaded file. It will throw some warnings for the first time. Click on OK.

Step 3: Make sure you are connected to Internet. The Excel sheet will update every 5 minutes.

Download Link

Please download Open Interest Analysis Excel from the below link. Let us know in comments section if you have any queries.

Open Interest Simplified

26 Comments

  1. Hi Admin,

    Thank you for the sheet. Can you give an example where to buy or Sell based on the sheet

    • Hi Jobin,

      This sheet does not indicate any buy/sell signals. The purpose of this sheet is to find the overall trend of market based on Open Interest.

    • In order to decide the buy or sell levels, focus on Call Net OI and Put Net OI columns. Check which Call option strike price has highest open interest and which put otion strike price has highest open interest. As of August 4th 2016, the highest Oi for put option is 8500. It means that market has a huge support at 8500 and will not break this level so easily. So we have to buy Nifty futures near this level or buy a call option near this level.
      As expected, on aug 5th, market opened gap up with 70 points and trading above 8600. so people who bought call option by following the above method would have gained substantially.
      As of today, 9000 call option has high open interest, so market has resistance at very higher level and we could expect the market to go near 9000 level in this expiry.

      Thanks,
      http://www.Squareoff.in team

  2. @Admin: Sincerely admires ur knowledge & will to share it.
    Specially likes ur work on spreadsheets.
    If we meet someday, Beer will be on me! Cheers!!

  3. Hey Thanks for the sheet.. but it doesnt seem to update… shows the last upate as on 11th July 2016… could you email it to me please?Thanks in advance.

    • Hi Sunil,

      The sheet should auto update. Do you see any warning message below menu bar saying ‘Enable Data Content’?

  4. Hi, Great work thanks for your valid time… could you please elaborate more about the following technical fields used in this document
    – Long liquidation
    – Short buildup
    – Long buildup
    – Short covering
    Thanks in advance….

  5. Thanks for your valid presentation.. could you please elaborate the following technical fields used in this document
    – Long liquidation
    – Short buildup
    – Long buildup
    – Short covering
    Thanks in advance…

  6. Thank you so much for all the wonderful spreadsheets. Really useful. Thanks again Guys.

  7. Dear Admin,
    I sincerely appreciate your knowledge and efforts in making this excel sheet. Since long time I am facing the following issues. It would be great if you can assist me
    1 – As soon as we open the file, data in sheet “Data Nifty” gets updated automatically. Can you please share how we can to that ?
    2 – Based on sheet “MAIN OI” can we also calculate Options Greeks ?

  8. thank u very much for this great work..the sheet is not updated..and i have to download again and again to see the latest time frame update of the sheet..please tell me how to enable auto update, thank you!!

  9. Hi Admin, indeed as compared to option chain, this is a wonderful template. Buy/Sell would need a lot more real-time info. I have a general observation (not regd.template) probably, many might be facing. Safe trading rules suggest to buy at day’s low and sell at day’s high. H & L can be decided based on daily volatility %. But, against our expectations, Before a buy order is placed, Offer Price keeps jumping up. After a buy order, it goes down. Before a sell order is placed, Bid Price keeps going down. After sell order, it goes up.
    Depending on whether market is bearish or bullish at the time of placing order, one can place buy order first OR sell order first. What could be the best strategy to resolve the ordering issue?

    • Hi Srinivas,

      Unfortunately retails traders do not have any workaround for this, as HFT algorithms manipulate prices very often. You should take this into consideration as slippage while devising your strategy.

  10. Hi, this worksheet is awesome. ¿Can you please explain how to use it for another ticker?

    • Hi Mauro,

      Right now it works only for Nifty, we will update it soon for other scrips.

    • Hi Arvy,

      #### means that the value is not fitting in the cell. Just drag the cell width to accommodate the value.

  11. Sir,
    The excel sheet is upto 8800 only. How to increase / add upto 9000 ( Two more Rows)

Leave a Reply

Your email address will not be published. Required fields are marked *