67

Options Max Pain Calculator: Excel Sheet

Max Pain theory in Options Trading is probably one of the newest bust most popular strategies for Options trading. This theory is backed upon the fact that about 95% of option buyers lose money. We have personally traded based on max pain and the success ratio is pretty good. This article intends to explain max pain theory of options and how to profit from it. Also, we would go through Options max pain calculator excel sheet which can be downloaded from the end of this post.

Max Pain Theory: Definition and Usage

The idea behind Max Pain theory is the fact that Option writers tend to manipulate the expiry price of stock, index or commodity so that they benefit the most out of their positions. Although there is no authentic proof that how they manipulate the prices, yet this theory is widely accepted. As 95% of the options expire worthless and options is a zero sum game, this theory seems very realistic and logical. By definition, Max Pain is the strike price where if the underlying settles on expiry will cause maximum loss or maximum pain to the option buyers. Guess who would benefit from their loss? Yes, its Option sellers. Most of these Option Sellers are large investors / institutions or hedge funds that will hedge their positions and have the ability to manipulate the stock price to suit their open Options position.

There are enormous ways in which you can benefit from Option pain. Basically, max pain tells you the approximate level at which the contract is going to expire. If you know know this level, you can simply write Call options slightly above this level and pocket the premiums on expiry. Similarly, you can write Put options below this level. If you are a conservative option trader you can employ different hedging strategies like Bull Spreads or Bear Spreads.

Options Max Pain Calculator

Max pain is calculated using the Open Interest of options. The calculation is fairly simple and is summarized below:

  1. List down the different strikes of the option chain along with their open interests.
  2. For each strike, calculate the profit/loss incurred to the option writers if the underlying expires at that level. This calculation should sum up the profit/loss for put as well as call option at that strike price.
  3. The strike price at the the loss is minimum is termed as Max Pain strike price.

Automated Excel sheet for Max Pain calculation

We have created Options max pain calculator excel sheet which does all the background calculations and shows the Max Pain strike price. This excel sheet is automated and you need not to manually enter any data into the sheet. Currently this excel sheet supports both Nifty and Banknifty from NSE. When you open the sheet it would throw some warnings which can be safely ignored. The sheet would refresh every 5 minutes.

Below are few screenshots:

Options Max Pain Calculator

Options Max Pain Calculator Chart

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.

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 check the “Max Pain” value. 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

67 Comments

  1. SIR, WHEN YOU HAD TRADED PERSONALLY, WHAT SUCCESS RATIO YOU GOT & FOR HOW MANY YEARS YOU HAD TRADED, KINDLY UPDATE.

    • Hi Karan,

      Based on my experience, writing call/puts for 10% above/below max pain gave 90% success rate.

      • SIR, ARE YOU SAYING THAT IF MAX PAIN PRICE IS AT 8700, WRITING PRICE FOR CALL SHOULD BE 9500 + & PUT SHOULD BE 7900, KINDLY GUIDE & UPDATE.
        THANKS & REGARDS

  2. Hi Sir,
    Can you please share your thoughts for below query
    Is the max pain analysis give benefits only when it is close to expiry of contract or even can we use this for intraday, if yes please quote an example! Which will be really helpful.
    Thank you for updating with new analysis and strategies.

    • Hi Murali,
      Trading based on max pain would not be much helpful in intraday. You should take positions atleast 10-15 days before expiry

      • Thank you so much sir!
        As you mentioned in the comment 10% above or below Max pain gives 90% success, it means either we need write 8800 Call or 8600 put is it right!!

    • yesterday morning in that max pain in that max pain value index is 18500 but i cannot identify to buy ce or pe but they give that level is very superb

  3. SIR, KINDLY GUIDE AS HOW & WHEN TO ENTER THE TRADE AS PER MAX PAIN ( AT THE START OF THE EXPIRY ETC.) AND WHEN TO EXIT THE TRADE.
    THANKS & REGARDS

    • Hi Karan,
      We recommend to enter the trade 10-15 days before expiry, and exit on expiry day.

  4. SIR, YOU ARE SAYING 10% ABOVE OR BELOW THE MAX PAIN PRICE GIVES 90% SUCCESS RATIO, SO IF THE MAX PAIN PRICE IS 8700, WRITING PRICE FOR CALL WILL BE 9500 + & PUT WILL BE 7900, KINDLY GUIDE & UPDATE.
    THANKS & REGARDS

      • SIR, LAST TRADED PRICE OF 9500 CALL IS 1.10 & 7900 PUT IS 2.55, DUE YOU ADVICE TO TAKE SUCH TRADE. IN CASE SUCCESS RATIO IS EVEN 100%, YOU WILL EARN APP. 24 POINTS IN A YEAR = RS. 1800 LESS BROKERAGE ETC. 600 = NET PROFIT 1200 IN A YEAR ON AN INVESTMENT OF APP. 50,000/- IN CASE YOU KEEP SAME AMOUNT IN BANK FD YOU WILL EARN APP. 4000 AS INTEREST IN A YEAR, KINDLY ADVICE HOW WISE IT WILL BE TO TRADE WITH THIS STRATEGY.

        • Hi Karan,

          Risk and reward are always inversely proportional. You cannot expect a better profit if you are looking for 100% success ratio. While if you decrease the threshold % to 5 or less, then your profit will dramatically increase. But there would be a fair chance of loss too. Its just a game of probability 🙂

          • SIR, MY CALCULATION IS AS PER YOUR GUIDANCE.
            DECREASE IN THRESHOLD WILL DECREASE YOU SUCCESS RATIO, IN CASE THRESHOLD IS DECREASED TO 1%, SUCCESS RATIO MAY BE 25% OR LESS

  5. The nifty MAX pain is not working for this month. I have tried changing the symbolCode=-10003 but no luck. Please check

    • woh maximum pain main index point correct de raha hai but usmain call buy karna hai ya put buy karna hai woh kaise malum karna hai sir please reply and one thing intraday ke liye bhi bahut acha hai sir 9th dec 2016 ke din 18500 ka maximum pain bataya tha sir aur call option main acha profit huwa compare to other index woh call buy karna hai ya put buy karna hai woh malum kaise karna hai please reply it is a humble request i am waiting ur reply

    • Hi Jairam,

      Max pain tells you the approximate level at which the contract is going to expire. If you know know this level, you can simply write Call options slightly above this level and pocket the premiums on expiry. Similarly, you can write Put options below this level.

    • Hi Rajesh,

      It would be difficult to create an Excel sheet for all stocks. Please specify few of them so that we can help.

      • Hi Admin,
        You can select the most liquid stocks like, Reliance, DLF, etc etc for Max Pain of Stocks

  6. Hi,
    I fail to understand in the table why does it zero “0” out at 9100 and not at 9400

  7. Hi,
    I fail to understand in the table why does it zero “0” out at 9100 and not at 9400

  8. Sir i buy 8200 PE , 8300PE, 8400 PE and 8400 CE pls suggested me which one close near by expiry

  9. Hello, Are we able to download Bank Nifty data in excel, the way one is able to download the Nifty 50 data, using webquery?

  10. Hello sir, I’m confused with the values.
    May i know how to find thses call and put values?

    • Hi Koushal,

      Call and Put values would be populated automatically. No manual work is needed

  11. Thank you, Calculations are OK but the data in all the fields is to be entered manually, as the auto update function is not active in file which I downloaded from here

  12. Dear Sir,
    I was just going through your max pain spread sheet. I didn’t understand the Put value formula “=IFERROR(SUMPRODUCT($B2:B$14,$C2:C$14)-$B2*SUM($C2:C$14),0)”
    given in the spread sheet at cell E2. How did u get the value “0” in cell E14 at the strike price “9800”. Just was trying to learn this forumla. I would be happy if u can elaborate on this formula specially the “0” part

  13. HI Team,
    The attached spread-sheet is not working properly. Could you please upload a new version with BNF tab in working condition.

  14. I downloaded the sheet and check for BNkNifty max pain it is showing as 18300 but as of banknifty is at 24498 and seems bullish. It is really impossible unless somekind of crash. Also, I noticed that it shows data till 18900 which may be wrong. Can you check and correct the error please?
    BTW I am impressed with your work and its really wonderful Thanks a lot.

  15. in bank nifty max pain pl.specify contract if it is current month expiry or weekly.
    if month expiry can v have the same for weekly or it will be risky to be avoided ?

  16. Hi based on Max pain If market opens sharp down or sharp up beyond the level on expiry day it is an super bumper offer and in 52 weeks on bank nifty expiry 10 to 12 Thursday happens like this .on other it’s very closer and we can enter after 2 pm if we have a chance
    By the way may I know is it working for f&o stocks which has good call put OI ?if yes name of those like sbin, canbank, tatasteel like that? do you have any data to back test it. Your valuable feedback is much appreciated thank you

    • Hi Sristi,

      Thanks for your insights on Banknifty! Yes, this strategy works for F&O stocks with good call/put ratio, but unfortunately I don’t have any backtest results as of now.

  17. Dear Sir,
    I downloaded Open option analysys for nifty and bank nifty and also option pain.
    you deed very good job.
    is it possible to incorporate max pain graph in Option anaysys sheet of nifty and banknifty, so that we get all data at one place.
    thank you.

  18. PAINNIFTY sheet is not getting refreshed automatically. Even BNF sheet is getting refreshed only once we click on Refresh.

  19. Hi Admin, I am looking to calculate the Max pain myself. Doing a cumulative calculation of all the available strike prices while assuming the market expires at the current spot rate, so I deduct the total points loss on the strike price from there. Once done, I cumulatively add the total loss on every strike price and add the no for call & put… is that the right way to go? I can send a sample copy of my file.. if required to reference further.

    Urgently need help please.

  20. By using Max pain how can we buy option..? What will be the strategy..? Please explain sir..

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

  22. Dear sir,
    only index values are being updated. When i entered stock names its not showing analysis rather just NA kind of visible.
    Kindly help

  23. Hello sir, I am download excell sheet from your site and also follow your instruction but it not working in my excell. How it run and update excell sheet. Kindly help me sir.

    • Hi,

      What is the error you are getting? And which version of MS office you are using?

      • Hi Sir,
        You have done a great job here.But when am trying to open this(using micro soft excel 2016) an error occurred.Displaying “[DataSource.Error] The request was aborted:Could not create SSL/TLS secure channel microsoft excel 2016”.Can you please help me to fix this issue.Sir!!!
        Thank you in advance
        Rajes

        • Hi Rajes,

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

  24. Can you explain the formula in the put value column? Why does it 0 out at 9800 strike and then go negative? Why is row 18 of significance? What if the options chain had more strikes how would the put formula change? Shouldn’t the 0 of the put value be at row 22 not 18?=IFERROR(SUMPRODUCT($B6:B$18,$C6:C$18)-$B6*SUM($C6:C$18),0)

  25. Hi Admin… you said that this sheet should be used 15 days earlier. But the optio OI changes very frequently, how to cope up with this

Leave a Reply

Your email address will not be published.