Help

Excel & Google Sheets Integration

Our data - Your workflow.

Users can use Option Samurai to import live data and calculations directly from our app to their worksheets. This enables you to create custom workflows, advanced models, reports, unique calculations, watchlists, trade-logs, scans, and more. The possibilities are endless, and the use-cases are flexible enough to fit your workflow.

In addition, the sheets will constantly update and have the most recent data every time you refresh, so you can create them once and use them forever.

One of the killer applications for this plugin is importing options data and integrating it with stock data and your workflow to have custom tools for your options trading.

Overview

The plug-in creates several new functions that allow you to call specific data points directly into cells. The functions are built modularly, so you can refer to other cells and change the stocks or data points imported on the fly.

Main Functions:

OPTIONSAMURAI.STOCK - Imports stock data. The first argument is the ticker; then, you can add as many data points as you want.
OPTIONSAMURAI.OPTION - Imports options data. The first four arguments are used to call the specific option (ticker, call/put, expiration, strike); then, you can add as many data points as you want.
OPTIONSAMURAI.HEADER - Imports the data points' names. No need to call a specific ticker.

In Google Sheets the names of the formulas are a bit different (without the 'OptionSamurai' in the beginning). But they work the same.

Function Example:

In the stock function, the first part is the ticker, followed by the data fields. All fields can point to other cells to create a dynamic sheet.

In the option function, The first four fields are used to point to the option:

Ticker
Call or Put
Expiration (in the format YYYY-MM-DD)
Strike

The following fields are the data you want to import.

Unique options integration

Option Samurai plugin allows you to connect options in a 'floating' formula - instead of using a specific option. For example, this means that you can ask for the option that has 30 days to expiration and is three strikes OTM for the stock in cell B2. The main benefit of the floating formula is that you can create the sheet once and use it many times in many different use-cases. This is more important since options expire, and thus models will become less valuable if not updated.

This exciting feature allows you to build custom tools to help with your trading style every day.

How to use the floating formula?

The easiest way to use the formula initially is by using the formula builder on the right pane. Below we describe the parameters you can use.

Parameters to control:

Expiration date:
- Specific - Enter a specific date
- Days to expiration: Always greater than a number. Write as ">30" for more than 30 DTE (closest to)
Strike:
- Specific - Enter a specific value. (read more below)
- Moneyness - Distance of the strike from the current stock price. Write as "5%." for 5% above current price and "-5%" for 5% below.
- N-th - Counts the strikes in the chain and chooses the N strike from the closest ATM. For example, write "3N" for the 3rd strike ABOVE ATM. Write "-4N" for the 4th strike BELOW the ATM strike.
- ITM - Short for In The Money - Similar to N-th - Counts the strikes ITM and returns the N number. For example, write "2ITM" for the 2nd ITM strike (below for Calls and above for puts).
- OTM - Short for Out The Money - Similar to ITM - Counts the strikes OTM and returns the N number. For example, write "4OTM" for the 4th OTM strike (above for Calls and below for puts).

Examples:

=OPTIONSAMURAI.OPTION("AAPL", "CALL", ">20", "-5%", "name", "stock_last", "strike")

(Excel)

The above formula will return the call for ticker AAPL (Apple Inc.) with the expiration closest and above 20 days. The strike will be 5% below the current stock price (Moneyness filter), and the formula will return the name of the stock, the stock's last price, and the strike for the option.

=OPTIONSAMURAI.OPTION("AA", "CALL", ">30", "0OTM", "name", "stock_last", "strike", "mid")

(Excel)

The above formula will return the call option for AA (Alcoa Corporation) with the expiration date of closest and above 30 days. The strike will be the closest out-the-money (above in our case, as it's a call). The formula will return the stock name, last price, and the option's strike and mid-price.

=option("FB", "CALL", ">30", "25d", "name", "stock_last", "strike", "mid", "moneyness")

(Google Sheets)

The above formula will return the Call option for Meta Platforms (FB) with an expiration date greater than 30 days. The strike will be with the closest to 25 deltas. The formula will return the stock's name and last price and the option's strike and mid-price.

Installing the plug-in

The installation instructions are a bit different, depending on the version you work with. You can read detailed installation instructions for each version in this article.

The Process Overview:

You will need to install the plugin (from the store or download it from our site).
Find your API key from the 'settings' tab. You will see installation instructions there as well.
Login to Excel or Google Sheets (office.com, for example).
Activate the plug-in.
Once the plug-in is activated, sign in:
1. Click on the Samurai icon on the Home tab.
2. Click on the login link
3. Copy the Access Token code from the setting tab in OptionSamurai.com

For detailed instructions. Check here.

What data is available?

The plug-in enables you to access 100s of data points on millions of options. You can see the available data by pressing the Samurai logo and using the Option Samurai pane and scrolling through the list.

The data available is similar to the data on the scanner. You can see a list if you go to our scanner and click the 'add filter' button.

The above sources are updated with the most recent data points we offer. If you wish to get an overview, you can see this Excel file with the data we provide (but it will not be updated as often).

Example Template

Good covered calls trades on Dividend Aristocrats: We've created an Excel template example to help you get started and see some of the benefits you can achieve. Read this blog post about the Excel integration for more information and the template.

Getting Started Template: You can check out our blog post and read more about our getting started quick template. We have templates available for Excel and Google Sheets that will help you import options data and stock data to the platform of your choice. It is a great tool to learn how to use the plugin and get started with your own creation.

Usecases

The possibilities for this integration are endless. You can create any custom workflow you think of and save time and gain a sharper edge in the market.

We will be adding examples in the future. But some of the use-cases are:

Custom watchlists with options data
Calculators for custom strategies
Enrich your trade log and open positions to see aggregated statistics (greeks, beta weight, etc.)
An automatic valuation model for stocks
Automatically compare several strategies and stock holding in different scenarios.
Create unique calculations for options.
And much more.

Usecase video

We've presented the Excel integration in a webinar we did. Check out this video that shows how you can use our plug in to build a valuation model to find a company's fair price:

Installation instructions in Knowledgebase.
Troubleshooting the Excel/Google sheets plugin
A blog post about the Excel integration (blog).
Use case Example: Dividend Aristocrats Excel Template - Usecase
Getting started importing stocks and options data into Google Sheets and Excel Blog post.
Go to the Settings page (App)
Go to the main page of Option Samurai (pricing, overview, etc.)

Was this article helpful?

How can we help you?