Open Interest is considered a valuable indicator by most traders and investors. When used with volume and price data, it confirms market trends—whether they’re rising, falling, or moving sideways. It also reflects the money flow within the market. In this article, we’ll introduce you to open interest and provide an open interest analysis in Excel for option contracts. You can download the Excel sheet at the end of this article.
Check out more useful Excel sheets on this blog here.
Understanding Open Interest
Open Interest indicates the number of outstanding contracts for futures and option contracts. For options, each strike price of the option contract has its own open interest value. It’s essentially the total number of future or option contracts that haven’t been closed out. At the derivatives market’s expiry day, open interest reaches zero as all open positions are squared off.
Take a look at the image below, displaying the Open Interest for Nifty futures:
Let’s illustrate the concept of Open Interest with a hypothetical scenario:
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 closes her entire position (1 lot), and Heena buys one lot. Effectively, one lot of Nifty CE is removed 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 in a trade open new positions (one new buyer and one new seller), open interest increases by one contract. If both traders close existing positions (one old buyer and one old seller), open interest decreases by one contract. The third possibility is when an old trader transfers their position to a new trader (one old buyer sells to one new buyer), in which case open interest remains unchanged.
Open Interest, Price, and Volume Relationship
There is a strong correlation between Open Interest, Price, and Volume. It helps in effectively interpreting market trends. An increase in open interest alongside a price increase confirms an upward trend. Similarly, an increase in open interest alongside a price decrease confirms a downward trend. Price changes while open interest remains stagnant or declines may signal a potential trend reversal. The relationship between Open Interest, Price, and Volume can be summarized as follows:
Open Interest Analysis in Excel Sheet
This Excel sheet is designed to predict the trend of each option strike.
The trend determination is based on option price changes and open interest changes from the previous day. Here is a summary of the calculations in the Excel sheet:
Take a look at the screenshots below:
How to Utilize This Excel Sheet?
Step 1: Double-click and open the downloaded Excel file. It may display warnings the first time; click “OK.”
Step 2: You might see prompts regarding privacy settings for the NSE India website when you open this Excel for the first time. Set it to “public” as shown in the screenshots below:
Step 3: Ensure you are connected to the internet. The Excel sheet will auto-refresh each time you open it.
Step 4: You can adjust the Symbol, Symbol Type, and Expiry Date to fetch relevant data. Symbol Type and Expiry Date are dropdowns, allowing you to select values from the list.
For instance:
To obtain open interest data for NSE Nifty, set the symbol to “NIFTY” and symbol type to “Index.” For Banknifty, use “BANKNIFTY” as the symbol and “Index” as the symbol type.
If you’re seeking open interest data for a stock like Reliance, set the symbol to “RIL” and symbol type to “Stock.”
Step 5: Once the data refreshes, you can analyze the “Interpretation” and “Trend” columns to gauge market sentiment. If the data doesn’t refresh correctly or times out, follow the instructions in the next section to set the cookie value.
Setting Up the Cookie Value (Optional)
In some cases, this Excel sheet may not automatically fetch data due to web scraping restrictions on the new NSE website.
No need to worry; there’s a workaround. You’ll need to visit the Cookie worksheet and paste the cookie value from your browser into cell A2.
Refer to the instructions below for retrieving the cookie value in Chrome and Firefox browsers:
Google Chrome
Step 1: Open this URL in your browser.
Step 2: Press CTRL + SHIFT + I to open the Inspect window, or right-click anywhere on the webpage and select “Inspect.”
Step 3: In the Inspect window, navigate to the Network menu and press CTRL + R to reload the page.
Step 4: After the page reloads, click on “Option Chain.”
Step 5: Select “option-chain-indices?symbol=NIFTY” from the list. In the Headers section, copy the value of bm_sv located in Response Headers -> set-cookie.
Step 6: Paste the copied value into cell A2 of the cookie worksheet.
Mozilla Firefox
Step 1: Open this URL in your browser.
Step 2: Right-click anywhere on the webpage and select “Inspect Element.”
Step 3: From the Inspect window, go to the Network menu.
Step 4: Click on “Option Chain” on the webpage.
Step 5: Choose “option-chain-indices?symbol=NIFTY” from the list. In the Cookies section, copy the value of bm_sv.
Step 6: Paste the copied value into cell A2 of the cookie worksheet.
Data Refresh Frequency
The data in the sheet is refreshed automatically when:
- You open the sheet
- Click on Data -> Refresh All
- Every 5 minutes (as specified in the connection properties)
You can adjust the refresh frequency as needed in the connection properties.
Download Link
Enter your email address below to receive the sheet in your mailbox. Alternatively, you can use this link.
If you have any questions, feel free to ask in the comments section.
Common Errors and Troubleshooting
Error Message
The query ‘option-chain-nse-new’ (step ‘AutoRemovedColumns1’) is accessing data sources with privacy levels that cannot be used together. Please rebuild this data combination.
Troubleshooting Steps:
- Make sure you set the privacy levels to “public” when you first accessed the sheet, as described in “How to use this Excel Sheet -> Step 2” above. If you missed this step, you can modify the privacy settings from Data -> Get Data -> Data Source Settings.
- If the above step doesn’t work, go to Data -> Get Data -> Query Options. Select ‘Privacy’ on the left and click on “Ignore the privacy levels and potentially improve performance.”
Error Message
[DataSource.Error] The request was aborted: Could not create SSL/TLS secure channel Microsoft Excel 2016Troubleshooting Steps:
- If you are using Excel 2016, ensure that the OS is Windows 10 or above. Otherwise, this error may occur.
- This error can be due to a TLS version mismatch. Check this link for a potential solution that may require modifications to the Windows registry.
- Some users resolved the error by reinstalling MS Office.
One Comment