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

56 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)

  12. I am surprised how have I not seen such a magnificent website earlier……I applaud your work..

  13. Happy Christmas to the entire team of this amazing website.I hope I will get bank nifty options open interest analysis excel sheet soon.Happy new year 2017 in advance.

  14. can we have banknifty excel that also gives us option details , preferably weekly

  15. Great excel sheet, download today and worked without any issue, good formatting.

    Small query – How to get Future open interest data ?

  16. Sincerely admire ur knowledge & will to share it.
    Specially likes ur work on spreadsheets. Learning a lot from it as a beginner.
    Meet someday, treat will be on me! Cheers!!

  17. Hi. I downloaded the xl sheet and the data was for NIFTY for 11th July 2016. I was under the impression that I would get to see the data as of today ie 25 May 2017. Also, is there an xl sheet or any link to see such information for specific stocks.Thanks.

    • In addition to my question, my other comment is that your grid specifying trends is incomplete. For example what is the trend if the price is rising, OI is up but volume is decreasing. This is not a hypothetical situation but is actually seen today in Axis bank scrip.

  18. Open Interest Excel sheet not working since 2/3 days.
    It just showing N/A

    • Hi Krishna,

      Thanks for spotting this. The issue has been fixed now. Please check again

    • Hi Naushad,

      It gets updated by itself if you are connected to internet and Macros and enabled in your excel sheet.

  19. Hello Admin,

    Thanks for the excel sheet. Its very good work. Do we have same sheet available for Nifty 50 Stocks? If yes please share. Its very good wowrk form your side.
    Keep it up.

    Thanks
    Hemant

  20. Hello Sir,
    1st thank you so much for sharing your precious knowledge to publicly ….i really appreciate work effort…God Bless you.
    Sir i’m new in this stock market field …i’m in learning stage..and your website n blogs i must say very helpful…i learned numerous new things…
    Sir i just want to know this excel sheets…all excel sheets..
    bank nifty open interest
    breakout
    intra day
    is it work on google spreadsheet…??
    if yes then how to do that…?
    or can you make this excels sheets strategy in google spreadsheets as well…it will very big helpfull….plz ans me…ill wait for ur reply..

  21. Hi,
    Excellent work..!!
    Can you provide this same sheet for individual stocks?

Leave a Reply

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