# Option Greeks Calculator: Live Excel Sheet

People who practice Options trading know very well how important ‘Option Greeks’ are. As per Investopedia, Trading options without an understanding of the Greeks – the essential risk measures and profit/loss guideposts in options strategies – is synonymous to flying a plane without the ability to read instruments. Option Greeks, denoted by certain Greek alphabets, are the parameters that determine how Option price varies with the change in external factors like time, volatility, and underlying stock Price.

These Greeks are calculated based on the Black and Scholes options pricing model, which was first published by Fisher Black and Myron Scholes (hence the name Black & Scholes) in 1973. In this post, we’ll go through an Option Greeks Calculator which updates real-time and calculate Greek values for all the strike prices of options traded in NSE.

## Options Greeks definition

Image Source: http://optionspedia.blogspot.in/2012/10/option-greeks.html

1. Delta – Measures the exposure of option price to the movement of underlying stock price
2. Gamma – Measures the exposure of the option delta to the movement of the underlying stock price
3. Theta – Measures the exposure of the option price to the passage of time
4. Vega – Measures the exposure of the option price to changes in the volatility of the underlying
5. Rho– Measures the exposure of the option price to changes in interest rate

## Option Greeks Calculator: Excel Sheet overview

 Parameter Description Worksheet Name Greeks Inputs Symbol (Cell J8) Symbol of the stock or index from NSE Symbol Type (Cell L8) Type of symbol; Index or Stock Expiry Date (Cell O7) Expiration date of Option series. Pick the value from drop down Risk free interest rate (Cell O8) This is risk free rate prevailing in the economy. Use the RBI 91 day Treasury bill rate for this purpose. You can get the rate from the RBI website, RBI has made it available on their landing page here. Dividend Yield (Cell O9) This is the dividend per share expected in the stock, provided the stock goes ex dividend within the expiry period. Outputs Cells B11:R29 Option Greeks values for each strike price.

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?

Below are the simple steps to use Option Greeks Calculator excel sheet. There is very little manual intervention required for this.

Step 1: Open this Excel file and make sure you are connected to internet. Please accept if it asks to enable Macros and Data connections.

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:

Step 3: Input the required fields, Symbol, Symbol Type, Expiry Date, Risk free interest rate and Dividend yield.

Step 4: The Greek values would automatically get updated. The sheet refreshes every five minutes.

Step 5: 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.

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.

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

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.

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.

#### Related Posts

1. Manikandan S

Your doing a great job …..your posts are highly informative. Every post is making me to learn new stuff.Thank you for your great work

Thank you Manikandan!

• Shakeer

Can you please include the strikes at 50’s also, and do you have a similar sheet for Bank nifty

• Arun

Hi Sir,
To be frank you are doing a great job . i never used to comment but the way you are sharing the knowledge is Awesome. Thanks buddy .

Regards,

Thanks for your kind words Arun!

2. Rohit

Hello Admin,What is difference between NSE Call LTP and CallPrice.
Could you please let us know.

3. Manish

Great Great job.
All are giving real time thats great. If you add one 2 more feature if possible one is Greek atleast historcial last 10 days atleast on closing basis atleast with graph. That helps user to understand and use it proper basis. And also strategies expected loss and profit. Thanz

4. Manish

Can it be possible to get AFL for greeks in amibroker ?It will be of great help.

Hi Manish,

We’ll try to post it going forward. Stay tuned!

5. Dinesh

Hi … I am new to Options Trading : Downloaded Excel sheet .. based on this shall we buy (Put/Call) !!! if yes kindly explain how to proceed , thank you , God Bless You and Your Family ….

6. ruchi

sir can u help me to use it. where to buy and sell . i dont know much about it?. plz help thanks and waiting for ur kind reply

Hi Ruchi,

dear sir your posts are amazing
i regularly visit your website , thx for helping newbies like me
greatfull to you for all your posts

8. Murali

Hi sir,
I am new to trading, I want to develop more technical knowledge in this area. Can you please share a material which have basic concepts and step by step flow of concepts it will really help beginners. Please kindly drop a mail.
Thanks!

Is there a way to force a refresh a data load? On my system after the first load of data I don’t get a refresh

Hi Vohi,

In the excel sheet, go to Data tab in the top and click ‘Refresh All’.

10. BHARATH

hi sir , i have downloaded the excel sheet for option greek and none of the columns show correct values or sometimes it does not show anything in the delta vega rho theta columns pls help!

Thank you for such a wonder excel file on options. It helps in making descisions; can you please let us know what thie difference between NSE CALL LTP and Call Price?
Thank you for you quick response!

12. Atul Jain

do we have a way to get the Bank Nifty data into excel?

14. Raju

I have developed Bank Nifty Option Greeks,its under trail , soon
I will post here. Thank you.

Hi Raju,

Thanks a ton for your continuous contribution. Looking forward for it.

15. PARAG MEHTA

Thanks for the fantastic work ,It will help a lot to adjust position during delta neutral.Can you please post same for Bank nifty ?and also please explain how to do it for any script / Index.

16. RD Patel

Well done friend! Undoubtedly this is cool and very helpful to option traders!

Best wishes!

17. ABDUL WAJID

18. pankaj

what to enter dividend value for nifty call put greeks.

Hi Pankaj,

It should be 0 for Nifty.

19. RDP

Your work is excellent!!! Is it possible to see the greeks of next month options in this excel? Also please check why ITM Call option greeks are not being calculated. Thanks and keep up the good work please….

20. Namit

Excellent Share, Thanks a lot. May I request the same sheet for Bank Nifty?

21. Namit Jain

Excellent Share, Thanks a lot. May I request the same sheet for Bank Nifty?

Hi Namit,

We would share the similar sheet for Banknifty ASAP.

• Namit Jain

Thanks a lot. Waiting for the share!

22. Virendra

Hi Can this sheet be used for other stocks like YesBank etc?

23. Rajiv Varma

Sir, I some how feel the Gamma column in Option greek is eronous , it I think does not give the right figure . it is same for all the strike or r zero , pl check

HI , The theoretical value and the actual price difference is huge . How do i get the exact calculation ? . and addition to this is there any formula to calculate the implied volatility for a particular strike with out (Actual Option Price ) ? .

Kindly suggest
Kindly suggest .

25. Neeraj

Hi,
I try to entry the today date and it does not work.

Hi ,
Which pricing model do you use to calculate the greeks ? Kindly reply so that i can study more on those models .

Calculation is based on Black–Scholes model

Thank you first of all , in BS model we need the following variables in order to get the price of the option premium ( Ie Spot Price , Strike Price, No Days to expiry , Implied Volatility , interest rate and dividend) .

My Question here is : How NSE Option Chain publish the IV ( Is there any mechanism available to Predict the IV and if possible kindly let me know the Calculation Procedure for the same )

2nd Question is : Lets say nifty spot price is 9940 and for Call option strike 10000 the IV is 7.5 % as per the NSE option chain , in case if the nifty spot price is 9970 what would be the IV for the strike of 10000 for th e same expiry cycle .

Thank you for all your efforts !

Thank you first of all , in BS model we need the following variables in order to get the price of the option premium ( Ie Spot Price , Strike Price, No Days to expiry , Implied Volatility , interest rate and dividend) .

My Question here is : How NSE Option Chain publish the IV ( Is there any mechanism available to Predict the IV and if possible kindly let me know the Calculation Procedure for the same )

2nd Question is : Lets say nifty spot price is 9940 and for Call option strike 10000 the IV is 7.5 % as per the NSE option chain , in case if the nifty spot price is 9970 what would be the IV for the strike of 10000 for th e same expiry cycle .

Thank you for all your efforts !

27. Sachin

Hi,
in “data” sheet IV values are not getting filled due to which greeks are not getting calculated in the main sheet. Please help.

• Sachin

Problem is resolved. Thanks.

28. Rakesh

Hi,

This is great work. Is it possible for you to enable the VBA code so that we can customize it. If not, would it be possible for you to make following changes,
1. Allow the user to enter the symbol and display the greeks for specified symbol.
2. Not all the strikes are available. Strikes only in multiple of 100 are displayed. Can we make it multiple of 50?
It would be of really great help.

Thanks,
Rakesh

29. Raj

This website gives lots of info. However, the excel sheet is a bit old and does not update even after refresh. Kindly check the same and upload corrected one. Thanks.

Hi Raj,

Its working fine at my end. Please update “28-12-2017” in the expiry data column and let me know if you face the same issue.

30. BHARAT KANODIA

Hi The options greek sheet is not working. The Data Sheet is not there, can u please email me or upload.

Hi Bharat,

Please update expiry date on the sheet. For ex: change it to 25-01-2018 for Jan expiry.

31. Bhanudas Pingale

Just Like Nifty “Greek Calculator” Is it available for Bank nifty & stock Option “Greek Calculator”

32. Raj

Nice job for posting good articles. Is there a workbook for BankNifty weekly options as well. If yes the please share the link.
Thanks in advance and continue to post good articles on this website. Raj

Admin, Thanks for the excel sheets. can you please provide with stock option greek calculator similar to nifty option green calculator. Thanks a ton

34. Chetan Prakash

Congrats on doing such a fine job at TradingTuitions.
And Thanks.
Request share a Greek sheet for Bank Nifty.

35. PRAMOD KUMAR

In Nifty strike prices are available in multiples of 50, whereas in your excel sheet it is in multiples of 100. Why this difference.

Hello sir

how can we find risk free interest rate and dividend yield for nifty

Hi Raju,

Dividend yield for Nifty is 0, and risk free interest rate can be found at below link:
https://www.rbi.org.in/

37. Vinay

From where we will get the dividend yield?

Hi Vinay,

Dividend yield is 0 for Nifty

38. vimal kumar

not update sir
after enabling error value coming please guide

Hi Vimal,

Have you changed the expiry date?

39. Tushar

Great work with the excel sheet. It is very useful in understanding options and taking trade positions. I have modified the sheet to include calculate the greeks for NSE stock options. I have combined OI and Greeks analysis for stock options on NSE. The expiry date is auto calculated as the last thursday of current month. The stock option to be analysed must be selected on first sheet from drop down list. Some stock futures have been delisted from NSE recently. I haven’t updated those changes. You can find the sheet at: https://drive.google.com/open?id=1GY2j8fCPf1EnG1N_bxwezeHurf6ZWV0J

Great Tushar! We will review and share our comments soon.

• Tushar

Thanks. Is there any way to fetch stock dividend yield and ex-dividend date? This can be incorporated in the sheet above.

• srinivasan

hi sir how to select symbol not change so pls help me…

40. sanjay

The excel sheet shows Greek values only from 9700-10300 . for other strikes error is shown. Can you please correct this.

41. pavan

Hello Admin, This looks exciting Can you make greeks sheet for for Bank Nifty as well Thanks

42. jo

I feel the options calculator has bug in it. Please check.
When you open the spreadsheet it downloads data and does the calculation for theoretical price for all contracts. today market ended @ 10527 and all the calculations of theoretical price are correct. suppose if i want to check what would be the theoretical prices if market ended @ 10700, then it doesnt provide the correct information, rather it provides the theoretical price of market when it was @ 10527.
Note: I changed the current market price from 10527 to 10700 to check the theretical price of contracts when market is @ 10700

43. jo

You may please ignore my previous comment. I found the issue. its from my end. sorry for the inconvenience.
Do you have any option calculation for expiry day . Current the option calculatior considers number of days to expiry in the calculation. is there anything for expiry day pricing calculation
thanks

44. Siva

hi sir
iam unable to open option greeks file getting message file is corrupted

46. PKS RATHOR

you r too goog, great work . keep it up

47. Bhavesh

You are doing a great job….

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.

49. Dennis Felix

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

Hi Dennis,

Something to do with your firewall or antivirus. Which version of office you are using?

50. SHRIDHAR JOSHI

data source failed what should i do?

Hi Shridhar,

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

51. het