Get the Greeks in your Sheets!

View Demo · Report Bug · Request a Feature

Explore the docs »

Table of Contents

1. About The Project
• Resources
• TL;DR Option Greek Definitions
• Delta
• Gamma
• Theta
• Vega
2. Getting Started
   • Installation
   • File Description
   • Google App Scripts Resources
3. Usage
4. Contributing
5. License
6. Contact

About The Project

The inspiration for this App Scripts was my curiosity in simulating how changes in Greeks influence option pricing. While calculating BSM is fairly straight forward, I wanted to use custom functions for simplicity and to prevent convoluted formulas. BSM sheets creates Google Sheets functions to retrieve all standard option greeks and contract pricing.

Some interesting things you can do:

1. Create a "profitability" matrix to see how the price of the underlying impacts the option price, through time.
2. How does increase in volatility, with no change in underlying price, impacts the option price, through time.
3. Generating matrices for different strikes to text theta/vega impact, from above.

Some things you cannot do:

1. Generate alpha

This is 100% an educational resource and you should not expect a way to generate any alpha. The calculations are "close enough" for general learning but are absolutely not sufficient to price risk in the market.

(back to top)

Resources

All functions were written within Google App Scripts and have to be added manually to the desired sheet.

• Google App Scripts

The following papers were used to create the functions:

• Theory of rational Option Pricing - Merton

The following link helped in the original creation of the script:

• Excel implementation of Black-Scholes

If you need a general overview of option greeks:

• CBOE - Learning the Greeks: An Expert's Perspective

Option Greeks

This is a TL;DR version of the greeks. For a full description, please read the CBOE paper, above.

Delta

Delta represents the relative increase in the price of an option, given an increase in the price of the underlying.

Gamma

Gamma represents the change in Delta, given a change in the underlying price.

Theta

Theta is the change in option price, given a 1 day change in time.

Vega

Vega is the change in price of an option for a 1pt increase in the implied volatility of the underlying.

(back to top)

---

Getting Started

If you have never worked with Google App Scripts, don't worry, its straight forward and there a lot of documentation on the internet. So much so, that instead of giving step-by-step instructions, I'm going to link to some of the best guides I have found.

Installation

Instead of creating a tutorial, here are some I've found useful to install App Scripts into your sheet. The first article is really in-depth and comprehensive.

• Comprehensive guide
• Video Tutorial

Currently, index.gs is the stable release. All classes have been moved to ./classes folder for future development and implementation of a build system. Since GAS does not use import statement, the classes are not guaranteed to load in the correct order.

For a simple installation, follow the guide above and copy the index.gs content into App Scripts. This file is condensed to include all modules so you can start working right away. Otherwise, you can add each module in its own file. App Scripts auto include all files in a project and no import is necessary.

File Description

The file naming convention follow standard JS principals but you can name them as you wish. Please see description of each file below:

File	Description
Derivatives.gs	Encapsulates all derivative math.
Greeks.gs	Encapsulates all option greeks math.
NormalDistribution.gs	JS implementation of normal distribution function (NORMDIST).
index.gs	A single file that includes all above for a quick copy/paste into Google Sheets.

Google App Scripts Resources

If you are not familiar with google Scripts, here are some resources to get you started.

• Google App Scripts Home
• Beginner's Guide
• Custom Function Documentation

(back to top)

---

Usage

There are two primary ways to interact with the script:

1. Retrieve a contracts full quote, which includes price + greeks, for both Calls and Puts.
2. Retrieve specific information, as needed (ex: get Gamma for Calls or Price for Puts).

BSM_QUOTE(price, strike, time, rate, iv, divYield, quote = "", type = "CALL")

Default return of a 2D array (two rows) with price and all the Greeks for put and call. Specifying quote will trim the output to the the specified request. Table below lists all valid input.

Quote Input

Name	Description
default	array Returns the full quote for both sides of the contract (CALL|PUT).
price	float Returns the price of the contract. type defaults to CALL.
delta	float Returns the Delta value of the contract. type defaults to CALL.
gamma	float Returns the Gamma value of the contract. Bi directional (CALL|PUT) does not change value.
vega	float Returns the Vega value of the contract. Bi directional (CALL|PUT) does not change value.
rho	float Returns the Rho value of the contract. type defaults to CALL.
theta	float Returns the Theta value of the contract. type defaults to CALL.

Function Arguments

Name	Description
price	float Spot price of underlying stock.
strike	float Selected option strike.
time	float Time to maturity (expiry - today) / 365.
rate	float Suggested default: 10 year bond rate ("TNX").
iv	float Implied volatility from your broker or Yahoo Finance.
divYield	float Annualized dividend yield for the company.
quote	string Default return of full option contract. Can specify to return subsect of contract. See table above.
type	string Default CALL but can be specified when retrieving subsect data.

_Return format:_

	Col 1	Col 2	Col 3	Col 4	Col 5	Col 6
Row 1	CALL PRICE	DELTA	GAMMA	Theta	VEGA	RHO
Row 2	PUT PRICE	DELTA	GAMMA	Theta	VEGA	RHO

_Return types:_

Price	Delta	Gamma	Theta	Vega	Rho
float	float	float	float	float	float

---

_calculatePrice(type, nsd_d1, nsd_d2, price, strike time, rate, divYield)

Calculates an approximate price of an option contract.

Name	Description
type	string Specify CALL or PUT. Default CALL.
nsd_d1	float Normal Standard Distribution of the first derivative.
nsd_d2	float Normal Standard Distribution of the second derivative.
price	float Spot price of underlying stock.
strike	float Selected option strike.
time	float Time to maturity (expiry - today) / 365.
rate	float Suggested default: 10 year bond rate ("TNX").
divYield	float Annualized dividend yield for the company.

Return:

Name	Type
price	float

---

_calcGamma(nd1, price, iv, time)

Gamma represents the rate of change between an option's Delta and the underlying asset's price. Higher Gamma values indicate that the Delta could change dramatically with even very small price changes in the underlying stock.

Name	Description
nd1	float Probability density of the normal distribution.
price	float Spot price of underlying stock.
iv	float Implied volatility from your broker or Yahoo Finance.
time	float Time to maturity (expiry - today) / 365.

Return:

Name	Type
gamma	float

---

_calcTheta(type, nd1, nsd_d2, price, strike, time, rate, iv)

Theta represents, in theory, how much an option's premium may decay each day with all other factors remaining the same.

Name	Description
type	string Specify CALL or PUT. Default CALL.
nd1	float Probability density of the normal distribution.
nsd_d2	float Normal Standard Distribution of the second derivative.
price	float Spot price of underlying stock.
strike	float Selected option strike.
time	float Time to maturity (expiry - today) / 365.
rate	float Annualized dividend yield for the company.
iv	float Annualized dividend yield for the company.

Return:

Name	Type
theta	float

---

_calcVega(nd1, price, strike, time, rate, iv, divYield)

Vega measures the amount of increase or decrease in an option premium based on a 1% change in implied volatility.

Name	Description
nd1	float Probability density of the normal distribution.
price	float Spot price of underlying stock.
strike	float Selected option strike.
time	float Time to maturity (expiry - today) / 365.
rate	float Suggested default: 10 year bond rate ("TNX").
iv	float Implied volatility from your broker or Yahoo Finance.
divYield	float Annualized dividend yield for the company.

Return:

Name	Type
vega	float

---

_calcRho(type, nsd_d2, strike, time, rate)

Rho measures an option's sensitivity to changes in the risk-free rate of interest (the interest rate paid on US Treasury bills) and is expressed as the amount of money an option will lose or gain with a 1% change in interest rates.

Name	Description
type	string Specify CALL or PUT. Default CALL.
nsd_d2	float Normal Standard Distribution of the second derivative.
strike	float Selected option strike.
time	float Time to maturity (expiry - today) / 365.
rate	float Suggested default: 10 year bond rate ("TNX").

Return:

Name	Type
rho	float

---

(back to top)

Contributing

Please see the open issues for a full list of proposed features (and known issues).

Contributions are what make the open source community such an amazing place to learn, inspire, and create. Any contributions you make are greatly appreciated.

If you have a suggestion that would make this better, please fork the repo and create a pull request. You can also simply open an issue with the tag "enhancement". Don't forget to give the project a star! Thanks again!

1. Fork the Project
2. Create your Feature Branch (git checkout -b feature/AmazingFeature)
3. Commit your Changes (git commit -m 'Add some AmazingFeature')
4. Push to the Branch (git push origin feature/AmazingFeature)
5. Open a Pull Request

(back to top)

License

Distributed under the MIT License. See LICENSE.txt for more information.

(back to top)