Skip to main content

Max Pain theory in Options Trading is a relatively new but highly popular strategy. This approach is built on the premise that around 95% of option buyers end up losing money. We have personally employed the max pain strategy and found it to be quite successful. This article aims to elucidate the max pain theory in options and how you can profit from it. Additionally, we will explore an Options max pain calculator in Excel, which is available for download at the end of this post.

Max Pain Theory: Explanation and Application

The concept behind the Max Pain theory revolves around the idea that option writers often manipulate the expiration price of stocks, indices, or commodities to maximize their gains. While there’s no concrete evidence of how they manipulate prices, this theory is widely accepted. Given that the majority of options expire worthless, and options trading is a zero-sum game, the theory seems plausible. Max Pain is essentially the strike price at which, if the underlying asset expires, it would result in the maximum loss or “pain” for option buyers. And who benefits from this loss? You guessed it – option sellers. Many of these option sellers are large institutions, investors, or hedge funds that can hedge their positions and potentially influence stock prices to align with their open options positions.

There are various ways to capitalize on the Max Pain theory. Essentially, Max Pain provides you with an approximate level at which the contract is likely to expire. Armed with this knowledge, you can write call options slightly above this level and collect premiums upon expiration. Similarly, you can write put options slightly below this level. For conservative option traders, there are hedging strategies like Bull Spreads or Bear Spreads.

Options Max Pain Calculator

The Max Pain value is determined using the Open Interest of options. The calculation is relatively straightforward and can be summarized as follows:

  1. List all the different strikes in the option chain along with their associated open interests.
  2. For each strike, calculate the profit/loss that option writers would incur if the underlying asset expires at that level. This calculation should consider both put and call options at that strike price.
  3. The strike price at which the loss is minimized is referred to as the Max Pain strike price.

Automated Excel Sheet for Max Pain Calculation

We have developed an Excel sheet for calculating Max Pain, which automates these calculations. You don’t need to manually input any data; the sheet handles it all. Currently, this Excel sheet supports both Nifty and Banknifty from NSE. When you open the sheet, you may encounter some warnings, which you can safely ignore. The sheet updates every 5 minutes.

Here are a few screenshots for reference:

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:

  1. You open the sheet
  2. Click on Data –> Refresh All
  3. 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:

  1. 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.
  2. 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:

  1. If you are using Excel 2016, ensure your OS is Windows 10 or above. Otherwise, this error may occur.
  2. This error is often due to TLS version mismatch. Check this link for a possible solution that may require modifying the Windows registry.
  3. Some users have resolved the error by reinstalling MS Office.

Leave a Reply