# 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:

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:

# 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: Make sure you are connected to Internet. The Excel sheet will update every 5 minutes.

Open Interest Simplified

1. Jobin Jose

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

Specially likes ur work on spreadsheets.
If we meet someday, Beer will be on me! Cheers!!

Then we’ll meet sooner 🙂

3. Chandrabhusan singh

Excel sheet

4. Jayprakash

Do you provide any service?

5. Sunil

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’?

6. Raja

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

7. rajas

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

8. Debashis

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

9. prakash

is it possible to reduce the refresh rate to 1 minute

10. Darshan

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 ?

11. Manish

Can it be possible to get afl for option greeks in afl. it will be of great help/

12. sivaraman

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!!

• Sk

Do you get reply how to update file

• Skumar

Can anybody tell me how to update file

13. srinivas

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.

14. Prabakaran

How to change this excel for banknifty

15. Mauro

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.

16. arvy

iam getting all # symbols in cells,
what to do?

Hi Arvy,

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

17. Sarvesh

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

18. Krishna

Not updating 8100,8200,8300,8400,8500+ data in first sheet.

19. G Singh

Sir, could you help me to use this excel sheet for bank nifty….? Please, sir….

We are working on it. Will post shortly

20. G Singh

Thank you, sir. Looking forward to hearing from you soon…

21. G Singh

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

22. G Singh

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.

23. Raaj

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

24. Asha

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

Small query – How to get Future open interest data ?

Thanks Asha. You may get Futures OI from NSE website.

25. Hipul Jhaveri

What change do I need to make for it to work for bank nifty?

26. Rohit

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!!

Thanks for your kind words Rohit. Cheers 🙂

27. shekhar

Nise. kindly explain with stock example

28. Sudhir

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.

• Sudhir

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.

29. krishna

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

Pls. explain how to update this file

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

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

32. Gaurav Patel

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

Hi Gaurav,

Few of our strategies are coded in Google spreadsheets too. Check out below ones:

33. siva

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

34. Arunava Ghosh

I could not understand

35. Mei

Hi,
Is it possible to use the spreadsheet to retreive US Crude Oil data?

Currently this works only for NSE options.

36. Sumeet

37. pratick N Modi

How to use this excel for other companies contract like reliance, ibulhsgfnce??

Hi Pratick,

Right now it only supports Nifty and BNF.

38. pratick N Modi

How to use this for other companies contract??

39. Utsav

How to use this for options on other companies. Like HDFC Bank. Can anyone describe the excel, what is it that you are using, that is fetching data. I dont see any macro enabled. Please tell me, so that I can update it for other stocks as well.

Hi Utsav,

It is fetching data using Web queries. To use for any other scrip, it would require modification in formulas

• Girish Kumar M

hi, it has been a great use of these xls sheets, thanks for the effort and i please request for a stock option analysis can be done in one xls sheet by input of the company name so that it would be useful to trade on stocks

40. Girish Kumar M

Thanks for the excel sheets and also i would please request you to make an excel sheet for stock option analysis by input of any name of the stock, so if we trade in stocks we can get some idea on stock analysis

41. Kislay Kumar

Thanks a lot . Your effort of simplifying the trading is amazing . A very good tool provided to trading community. keep it up.

Thanks for your kind words Kislay

42. RASHID Y ALSULAITI

Can someone help me with this sheet to work for American options market ?

43. KAZIM ABBAS

u said for every new position open intrest will increase by one unit
does this mean both buy & SELL positions or only buy positions?
u didnt mentioned about short selling? infact new positions may also be created due to short selling na!!!

44. jatin

can i get it for commodity markets??

Hi Jatin,

Currently this excel do not support commodity markets.

45. Yugesh

Hello
I noticed that in datanifty sheet the A and B columns were not get being correctly calculated after the 89th row that’s why the Main OI sheet was not showing any data for most strike prices barring first 3. The error is solved by just dragging down the formula in A and B columns for rest of the cells.
You may want to check up on this and fix it in the source script.

Thanks for this wonderful tool by the way. Really appreciate it.

Hi Yugesh,

This is a great insight for all the people who are not able to see correct values in the sheet. Thanks a lot!

46. Anuj Awasthi

Hello Sir,
Great work… Can you also add functionality to provide stock options aswell.
Please provide xls with selection of stock to provide stock option data.

BR,
Anuj Awasthi

Hi Anuj,

This request is alrready in our enhancement list. Will let you know once completed.

• Mohamed

47. paresh bhangale

can you make simillar excel for individual stocks as well

48. Jayandra Singh

Hi,