Demystifying Open Interest: A Vital Indicator
Open Interest is a key indicator, widely embraced by traders and investors for confirming market trends. When used in conjunction with volume and price, it not only affirms the market’s direction—whether it’s rising, falling, or moving sideways—but also gauges the flow of capital within the market. In this article, we delve into the concept of open interest and present an Excel tool for open interest analysis concerning option contracts. You can download the Excel sheet at the end of this article.
Explore More Excel Resources
If you’re interested in Excel resources for financial analysis, don’t miss the array of Excel tools available on this blog.
Note: Previously, this website featured an article titled “Banknifty Options Open Interest Analysis Excel Sheet.” It has now been redirected to this updated article because the Excel sheet can now fetch open interest data for Nifty, Banknifty, and other indices or stocks of your choice.
Understanding Open Interest in Depth
Open Interest represents the total number of active contracts in both futures and options markets. For options, each strike price of the option contract holds its own open interest value. It’s essentially the sum of futures or option contracts that remain open, yet to be squared off. On the derivatives market’s expiration day, open interest typically drops to zero as traders close their outstanding positions.
For a visual representation, examine the image below that illustrates the Open Interest of Nifty futures:
Illustrating Open Interest Through Scenarios
Let’s explore a hypothetical scenario to grasp the concept of Open Interest more vividly:
Day 1: Ayesha buys one lot of Nifty 8300 CE, while Pooja sells one lot of Nifty 8300 CE. This results in a Total Open Interest of 1.
Day 2: Deepika buys two lots of Nifty 8300 CE, and Heena sells two lots of Nifty 8300 CE. This leads to a Total Open Interest of 3.
Day 3: Ayesha decides to close her entire position (1 lot), and Heena buys one lot. Consequently, one lot of Nifty CE is effectively removed from the market. The Total Open Interest now stands at 2.
Day 4: Pooja and Heena buy one lot of Nifty 8300 CE, while Deepika sells her two lots. This brings the Total Open Interest back to 0.
In summary, if both parties in a trade open new positions (one as a buyer and the other as a seller), open interest increases by one contract. If both traders close existing or old positions (one as an old buyer and the other as an old seller), open interest decreases by one contract. The third scenario occurs when an old trader transfers their position to a new trader (an old buyer sells to a new buyer), and in this case, open interest remains unchanged.
Exploring the Relationship Between Open Interest, Price, and Volume
Open Interest, Price, and Volume share a profound connection that aids in interpreting market trends effectively. When open interest increases alongside a rise in prices, it confirms an upward trend. Conversely, when open interest rises as prices fall, it indicates a downward trend. If prices rise or fall while open interest remains stable or declines, it may suggest a potential trend reversal. The relationship between Open Interest, Price, and Volume is summarized in the table below:
Using the Open Interest Analysis Excel Sheet
The provided Excel sheet is designed to forecast the trend for each option strike.
The trend analysis is based on changes in option prices and open interest from the previous day. The Excel sheet includes calculations such as those depicted below:
For a visual guide, please refer to the screenshots below:
How to Use This Excel Sheet?
Here are the simple steps to utilize the Option Greeks Calculator Excel sheet, with minimal manual intervention:
Step 1: Open the Excel file and ensure you have an internet connection. Enable Macros and Data connections if prompted.
Step 2: You may encounter privacy settings prompts for the NSE India website when opening the sheet for the first time. Set it to “public” as shown in the screenshots below:
Step 3: Fill in the required fields: Symbol, Symbol Type, Expiry Date, Risk-free Interest Rate, and Dividend Yield.
Step 4: The Greek values will be automatically updated. The sheet refreshes every five minutes.
Step 5: If the data doesn’t refresh correctly or times out, follow the instructions in the next section to set the cookie value.
Setting Up Cookie Value (Optional)
If the Excel sheet doesn’t fetch data automatically due to web scraping restrictions on the new NSE website, follow these steps:
Head to the Cookie worksheet and paste the cookie value from your browser into cell A2.
Refer to the instructions below to retrieve the cookie value in Google Chrome and Mozilla 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 web page and select “Inspect.”
Step 3: Navigate to the Network menu within the Inspect window and press CTRL + R to reload the page.
Step 4: Once the page reloads, click on Option Chain.
Step 5: Choose “option-chain-indices?symbol=NIFTY” from the list. In the headers, copy the value of bm_sv located in Response Headers –> set-cookie.
Step 6: Paste the copied value in cell A2 of the cookie worksheet.
Mozilla Firefox
Step 1: Open this URL in your browser.
Step 2: Right-click anywhere on the web page and select “Inspect Element.”
Step 3: Access the Network menu within the Inspect window.
Step 4: Click on Option Chain from 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 in cell A2 of the cookie worksheet.
Data Refresh Frequency
The sheet’s data refreshes 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 to your preference 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, please let us know in the comments section.
Common Errors and Troubleshooting
Error Message
The 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:
- Set the privacy levels to “public” when you first access the sheet, as described in “How to use this Excel Sheet –> Step 2” above. If you missed it, 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 “Ignore the privacy levels and potentially improve performance.”
Error Message
The error message “[DataSource.Error] The request was aborted: Could not create SSL/TLS secure channel Microsoft excel 2016.”
Troubleshooting Steps:
- If you are using Excel 2016, ensure your OS is Windows 10 or above. Otherwise, this error may occur.
- This error is often due to TLS version mismatch. Check this link for a possible solution that may require modifying the Windows registry.
- Some users have resolved the error by reinstalling MS Office.
