Banknifty Options Open Interest Analysis Excel Sheet

On our readers request we have developed Banknifty options Open interest analysis excel sheet. This is the similar Excel sheet we have posted for Nifty options here. This Excel has an additional feature to select the expiry date of option series to accommodate both weekly and monthly options. The Excel sheet can be downloaded from the end of this post. Let’s have a brief introduction of Open interest concepts before understanding how to use the Excel sheet.

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.

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

Banknifty Options Open Interest Analysis Excel Sheet

This Excel sheet is designed to predict the Trend of each Option Strike of Banknifty. 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

Below are the screen shots:

Banknifty Options Open Interest Analysis Excel Sheet

Banknifty Options Open Interest Analysis Excel Sheet1

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: Input the valid Expiry date for Banknifty in the format DDMONYYYY and click on Get Data

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

Download Link

Please download Banknifty options Open interest analysis excel sheet from the below link. Let us know in comments section if you have any queries.

Banknifty OI Analysis

12 Comments

  1. Dear Admin. Banknifty excel sheet giving error, no values showing. Error saying to run macros as high, but its already high, enabled, thanx

    • Hi Gurdip,

      Can you please download the Excel sheet again and click on Get Data button to refresh the sheet.

  2. Bank nifty option excel sheet not being populated. Tried to alter macros, security settings. Even inatalled excel 2016 . I have two laptops but this is the case eventhere. However nifty options r working fine. My idea is to analyse tht eithr put or calls whose OI are more OTM plus change in O I are maximum, then if we short them , profit is generated by time decay. The strategy can be double confirmed by hourly supertrend. I have been making some profits by this in nifty options recently but feel tht as weekly banknifty can give early profits as thus cycle repeats every week.

    • Hi Gurdip,

      Thanks for sharing the strategy. We will check the Excel issue and let you know.

  3. Respected Sir, Firstly I want to thank you from the bottom of my heart for your efforts.Your efforts are worth a noble prize…:) Secondly this sheet is not working….:( No values are shown.

    • Thanks for your kind words. The Excel sheet and it works fine in my machine and some of the other systems I tested. We will check this issue again and let you know

      • Sir, I can send you a screen shot of this sheet showing no values………..Please help…

        • I have the screenshot from other user, and it happens with me too sometimes. But as soon as I press the Get Data button, the values gets populated again. Can you please download the latest copy from this post and try again.

  4. hi Admin ,

    the banknifty option excel is noot working , please help me , no error but it doesnot display data , even after putting month and then get data , please mail me the correct excel for bank nifty

  5. Sir, I again downloaded the XL sheet. But when I click at Get Data then I get the following message;
    “Run time error 1004.The address of this site is not valid.Check the address and try again.”

Leave a Reply

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