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:

How to use this Excel Sheet?

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.

Banknifty OI Analysis

1. Saravanakkumar

Sir
It is not working pl help…

What is the error message you are facing?

2. Gurdip

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.

3. Gurdip Pujji

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.

4. G SINGH

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

• G SINGH

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.

5. Raaj

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

6. G SINGH

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.”

Is there any position option excel for nifty and bank nifty

8. srikrishna

Sir,

Its in the upper right where you enter Expiry date

9. Raju

sir,
After macro and data content enabled , page is auto refreshed all valued gets null , when I changed the expire date and pressed get data button it goes to macro page where error line is highlighted and I have checked data tab also data is not updating ,Please solve the problem and update as soon as possible. I will be very thankful to you.

10. raju

sir,
After macro and data content enabled , page is auto refreshed all valued gets
null , when I changed the expire date and pressed get data button it goes to
macro page where error line is highlighted and I have checked data tab also data is not updating ,Please solve the problem and update as soon as possible. I will be very thankful to you.

11. fei fei

Hello there,
Nice work, thank you!
How can I get OI of SPY?
Thanks,
Fei

Right now this excel sheet is supported only for NSE stocks

12. Raju

Sir,
Now excel works perfect, after small change in data tab. Here is the screen shot, http://prnt.sc/e4dz9y , I dont know how to upload excel ?
Thank you for your amazing work.

Hi Raju.

I definitely owe you a treat for fixing this pro-actively. If possible, please send the updated excel sheet to support@tradingtuitions.com

• Raju

Sir ,
I have send copy to your given mail , Please check it.

Thanks Raju. I have attached the corrected Excel in the post.

• Ramesh

Hello Raju, Could you please share the updated excel sheet ? My email id kylix3511gmail.com.

Also Raju, your snapshot looks awesome. How to change the expiry for next month or following month.

Admin, your work is really amazing and takes us to the next level in trading using Excel. Simply awesome. Thanks so much for your hard work.

Thanks Ramesh! I have attached excel sheet corrected by Raju in the original post.

• Ramesh

• manju

Plz share the updated sheet to my email id

• Rajarajan

Hello SIr, Can you please let me know how to get the option datas for stocks.Is it possible to do the same for stocks like u did for nifty & Banknifty.If possible tell us how to do ?

• Mukesh

Sir please send me excel sheet plz

13. Raju

Sure i will do it now itself.

14. Mangesh

Sir please send me updated excel sheet of Banknifty Options Open Interest Analysis Excel Sheet

• Raju

Hi Mangesh,
I have send copy to Admin , pls check with him updated one.
Thanks
Raju

• Mangesh

Thanks Raju and Admin sir for updated excel sheet

15. ashish

First of All,

Thanks for the sheet. Great work done. Simplified the whole process.
Enabled Macros. then error 1004.The address of the site is not valid. Gives you 2 options End & Debug. Click on end & wait. Data is fetched & the sheet works.
Thanks once again . Happy trading.

Thank you for the workaround Ashish. The issue is with some specific versions of Excel.

• Rajarajan

Hello Sir,
How to update the datas for banknifty weekly options.by default sheet is showing the next expiry i.e 14th DEC is it possible to get 21st expiry and 28th expiry.

Hi Rajarajan,

Unfortunalely there is no support for weekly options right now

16. Mr.Bhanudas Pingale

Pl.give Excel sheet for Stock option trading

17. gaurav

Sir please send me updated excel sheet of Banknifty Options Open Interest Analysis Excel Sheet

18. Karthik

Sir, is there anyway we can know at which strike price is the call/put writing is happening? At an open interest point of view we can only know the change and we can’t figure out whether it is buying or selling. Can we specifically know about the selling only?

19. Ravi

Hey, is there any OI analysis file for weekly BN options as well?

20. shankar

Good job.

Shankar

21. Nilesh Patel

Unable to update the excel sheet…pls help

Hi Nilesh,

Please change expiry in cell R3, the sheet fetched correct data then

22. Amit

We can see historical data in this excel ? Right ?

Hi Amit,

No, you can only see the current data

23. rahul

great man, thanks Alot i was preparing the same and suddenly i git this , thanks alot !!

24. sudesh

nice work sir.
can u prepaid same for f & O stock

25. satyapal Kumar

Hello Sir,First of all i would like to say million thanks for you effort. I am not able to understand when buy CALL or PUT.