141

Open Interest Analysis Excel for Options

Posted on by admin

Open Interest is believed as a Confirming Indicator by the majority of traders and investors. It generally confirms the market trend (whether it’s 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 on this blog here.

Note : There was another article titled “Banknifty Options Open Interest Analysis Excel Sheet” in this website. It has been redirected to the current article as the updated version of excel sheet can now fetch open interest data for Nifty, Banknifty, and any other index or stock of your choice.

Introduction to Open Interest

Open Interest represents the number of outstanding contracts for future and option contracts. For options, every strike price of the option contract has an individual open interest value. It can also be defined as the total number of future or option contracts that have not been squared off. Open interest tends to zero value at the expiry day of the derivatives market as everyone 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 the 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. The relationship between Open Interest, Price, and Volume can be summarized in the following table:

Price Volume OI Comparison

Open Interest Analysis Excel Sheet

This Excel sheet is designed to predict the trend of each option strike.

The trend determination is based on option price change and change in open interest for the previous day. Below is the summary of calculations present in excel sheet:

Price Change OI Change Interpretation

See the below screenshots:

Open Interest Analysis Excel- Screenshot

Open Interest Analysis Excel- Screenshot Charts

New Updates – 1st May 2020

We have rolled out some updates to the sheet based on requests from several users. See below the details:

  1. Added a feature to select any stock or index from NSE. Based on your selection the data would get updated automatically.
  2. Added a feature to select the expiry date from the drop-down provided.
  3. The sheet now connects to the new NSE website.
  4. Programmatic scraping of data from NSE is blocked natively. As a workaround, added the cookie parameter in the sheet to allow this.
  5. Changed the auto-refresh frequency from 1 min to 5 min. You can set it as per your convenience.

How to use this Excel Sheet?

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

Step 2: You’ll also see some prompts to set the privacy level for the NSE India website when you open this excel for the first time. Just set it to “public” as per the below screenshots:

Open Interest Analysis Excel- Privacy Settings 1

Open Interest Analysis Excel- Privacy Settings 2

Step 3: Make sure you are connected to the Internet. The Excel sheet will auto-refresh whenever you open it.

Step 4: You can change the Symbol, Symbol Type, and Expiry Date to get the relevant data. Please note that Symbol Type and Expiry Date are dropdowns, so you can select the desired value from the list.

For example:

To fetch open interest data for NSE Nifty, the symbol should be “NIFTY” and symbol type should be “Index”. For Banknifty, the symbol should be “BANKNIFTY” and symbol type “Index”.

And if you are looking for open interest data for any stock like Reliance, the symbol should be “RIL” and symbol type should be “Stock”

Step 5: Once the data gets refreshed, you can analyze “Interpretation” and “Trend” columns to understand the market sentiment. In case the data doesn’t refresh properly OR it gets timed out, please follow the instructions in the next section to set up the cookie value.

Setup Cookie Value (Optional)

There is a possibility that this excel sheet won’t fetch data for you automatically because of web scraping restrictions imposed on the new NSE website.

Don’t worry about it, we do have a workaround for you in case that happens. You’ll need to head over to the Cookie worksheet and paste the cookie value from your browser in the cell A2.

Set Cookie Value

See the instructions below to fetch the value of the cookie in Chrome and Firefox browsers:

Google Chrome

Step 1: Open this URL from your browser

Step 2: Press CTRL + SHIFT + I to open Inspect window OR right-click anywhere on the web page and click on “Inspect”

Step 3: Navigate to Network menu from the Inspect window and press CTRL + R to reload the page

Step 4: Once the page is reloaded click on Option Chain.

Step 5: Select “option-chain-indices?symbol=NIFTY” from the list. Go to headers and copy the value of bm_sv located in Response Headers –> set-cookie

Step 6: Paste the copied value in cell A2 of cookie worksheet.

Check out the below video illustration:

 

Mozilla Firefox

Step 1: Open this URL from your browser

Step 2: Right-click anywhere on the web page and click on “Inspect Element”

Step 3: Navigate to Network menu from the Inspect window

Step 4: Click on Option Chain from the webpage.

Step 5: Select “option-chain-indices?symbol=NIFTY” from the list. Go to Cookies and copy the value of bm_sv

Step 6: Paste the copied value in cell A2 of cookie worksheet.

Check out the below video illustration:

 

Data Refresh Frequency

The data in the sheet gets refreshed automatically when:

  1. You open the sheet
  2. Click on Data –> Refresh All
  3. Every 5 minutes (as specified in the connection properties)

You may change the frequency as per your convenience from the connection properties.

Download Link

Enter you Email address below and get the sheet in your mailbox. Alternatively, you can use this link.

Let us know in comments section if you have any queries.

Common Errors and Troubleshooting

Error Message

Query ‘option-chain-nse-new’ (step ‘AutoRemovedColumns1’) is accessing data sources that have privacy levels that cannot be used together. Please rebuild this data combination.

Troubleshooting Steps:

  1. You should set the privacy levels to “public” when you accessed the sheet for the first time. This step is described in “How to use this Excel Sheet –> Step 2” above. In case you missed to set it to public, you can modify the privacy settings from Data –> Get Data –> Data Source Settings
  2. If the above step doesn’t work, go to Data –> Get Data –> Query Options. Select ‘Privacy’ option in the left and click on “Ignore the privacy levels and potentially improve performance”.

Privacy Levels

Error Message

[DataSource.Error] The request was aborted: Could not create SSL/TLS secure channel Microsoft excel 2016

Troubleshooting Steps:

  1. If you are using Excel 2016, then the OS should be Windows 10 or above. Otherwise, this error may pop up.
  2. This error happens due to the TLS version mismatch. Check this link for a possible solution that may need a modification to the Windows registry.
  3. Few of the users were able to resolve the error by re-installing MS office

Spreadsheets for Success

Related Posts

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

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

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

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

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

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

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

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

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

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

  22. 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. can we have banknifty excel that also gives us option details , preferably weekly

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

    Small query – How to get Future open interest data ?

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

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

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

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

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

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

  34. Please guide me as to how can I trade with your excellent sheet in option market?
    I could not understand

  35. Hi,
    Thanks for the spreadsheet!
    Is it possible to use the spreadsheet to retreive US Crude Oil data?

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

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

      • 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. 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. Thanks a lot . Your effort of simplifying the trading is amazing . A very good tool provided to trading community. keep it up.

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

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

  48. Hi,
    Can we upload this excel on google spreadsheet and get the live data ?
    Actually I have tried after uploading excel on google sheet but not getting live data. Is there any way to do it than please help me out.

    Thanks
    Jayandra.

    • Hi Jayandra,

      The excel logic needs to be changed for that. We have noted your request and work on this soon

  50. I would like to know how to take option trading decisions based on data generated in this sheet

  51. Will it work in android phone phone’s excel sheet??
    After download it is opening in smartphone excel but showing last update 06 jun 2017.
    And today is 26 jan 19
    Plse suggest.

    • Hi,

      There may be loss of functionalities in mobile version of Excel. Hence desktop version is recommended

  52. dear sir, is it possible to kindly update the excel sheet as per monthly expiry. The sheet is showing the data of current week expiry not the monthly expiry…thanks

  53. Thank you very much Sir, for your great efforts and great kindness for sharing your work for free, I have just downloaded it, and will try to understand it first. However , I will have to learn how to use it to get Buy or Sell signals out of it.

  54. @Admin I am software Eng and would like to customise the sheet to allow to query any INDEX or Stock script for a selection of expiry dates would it be possible to publish this here so people can test and give their opinion

  56. Sir, Shall we get OI for commodity and I need to trace every seconds OI data for particular strike price its available

  57. Hi Admin, I am getting the fllowing error even after following the instructions.
    “Initialization of data source failed.”
    Can you help please ?

    • Hi Subh,

      Which version of excel you are using? The sheet works best with 2016 and above versions

  58. Dear Sir,
    I have not received link to download excel. Please share the link.

    Thanks,
    Dipak

  60. Data is not downloading. I am not able to find any “xhr file” named as ” option-chain-indices?symbol=NIFTY”.

    I am using 2016 version.

    Kindly assist.

  61. sir after opening the excel the error comes as , check the database server or contact your database administrator

    • Hi Vikash,

      The excel sheet works only on MS office 2016 version and above. You’ll need to upgrade in case you are using a older version

  62. I have also tried to download excel sheet but haven’t received any mail
    Please help
    Thanks in advance

  63. Actually this error is visible in Mac office (excel) not in windows Excel….Can you suggest how to get this corrected. Just suggestion would be enough. The issue seems is in privacy setting. Privacy level options did not appear in Mac office.

    1. Query ‘option-chain-nse-new’ (step ‘AutoRemovedColumns1’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

  64. The excl link send is not for the updated excel where all F&O stocks with all expiry can be seen.

    • Hi Kumar,

      Kindly read the instruction in the blog post. You can chose any stock symbol you want

  65. sir,
    today excel file dose not download option chain any data from website.

  66. Hi,
    I just downloaded new sheet and setup the cookie. Only getting data in SPOT Price, strike. For rest of the field no data only #N/A is displaying.
    Please help to resolve.

    Regards

  67. Hello,
    Net OI & change in OI numbers ot matching with NSe\E web site.
    Eg: excel shows 10200 Call side (Net OI: 20893 & change OI: -4064).. But in NSe site it is (Net OI: 2,062,800 & change OI:-525,900).. Please clarify

    • Hi Raj,

      Please check the NSE new website. In the old site OI was multiplied by lot size

  68. HI am getting error as ” Query Underlaying price ” (step auto removed column 1) is acessing data source that have privacy level which can not be used together , please rebuild this data combination “

    • Hi Shailesh,

      Added a new section in the article called “Common Errors and Troubleshooting”. Please check it for the possible resolution of this error.

  71. i am using excel 2007 can i use this excel?
    there is no default setting as told by you

  73. dear sir,
    I got an error message from yesterday “we couldn’t authenticate with the credentials provided. please try again” kindly guide me to resolve the issue.
    Thanks

  74. Getting an error message from [29-Au-2020] “we couldn’t authenticate with the credentials provided. please try again” kindly guide me to resolve the issue.

  75. Sir, we are facing few errors :
    DataFormat Error : We found an extra character at the end of json file.

    And
    We couldn’t authenticate with the credentials provided. please try again

    Kindly Assit

  77. [PErmisson Error] is coming…Credentials not supportive..
    Cookie Refresh also done…Nothing Works

  80. Sir, I am not receiving Bank nifty/nifty option chain excel sheet even after 1 week. Humble Request you to please provide me personally via mail I lost my job during the lockdown period.

  82. Great Work Sir,
    On NSE website data is 3mins delayed so can we get real time data using broker’s API like Zerodha and Upstox

    • Hi Rehan,

      Sure, you can. You’ll need to subscribe to broker’s API and use it in Excel through macros

  83. Can you please share the password to edit the VBA code? So that I can customize it myself as per my needs.

  84. Dear Team,
    I have also built a OI tool like this, but I am again getting cookies error.
    This error only persists Whenever I do analysis on stocks, other on nifty or BN it works fine
    Few of the images of my file are
    https://ibb.co/42cNG1y

    Could you please me resolve it

Leave a Reply

Your email address will not be published.