Import Stock And Options Trading Data To Google Sheets

How to import stock and options trading data to Google Sheets
Reading Time: 4 minutes

It needs no utterance but, in the stock market, a second–in fact a millisecond–can make all the difference between making thousands and a few hundred, indeed between winning and losing. That’s why keeping watch over the market is critical. But critical as it is, we do not have the eyes and the attention necessary to constantly keep watch over it.

Luckily, there’s a solution, albeit an imperfect one, I admit. We can import stock and options trading data to Google Sheets and automate the process so that we always have the latest information at our fingertips, literally, as we are alerted of the data via email or Slack.

The way this is done is by using a user-friendly extension in Google Sheets that lets us connect Google Sheets to the stock API and retrieve stock and options data instantaneously. The extension is Amigo. Install Amigo amigos and we’ll walk together through the steps on how to import stock and options trading data to Google Sheets. Get it here.

Installed? To the meaty part, then.

To import stock and options trading data to Google Sheets, we’ll take only two steps–or strides.

  1. Getting the API endpoint URL
  2. Importing the data with Amigo

Steps to import stock and options trading data to Google Sheets

Step 1: Get the API endpoint URL

Sign up or log in to RapidAPI.

Search for Stock and Options Trading Data Provider from the RapidAPI Hub and click the first option from the results. Or click this link to go directly to the Stock and Options Trading Data Provider  API documentation page.

Stock and options trading data API

Click on the Subscribe to Test button and subscribe to a plan of your choice, post which we’ll be able to run the test and import stock and options trading data to Google Sheets.

Subscribing to the API

Click Endpoints to go back to the API Playground. On the left of the page are the available endpoints.

Available endpoints

Click the Options endpoint to get the options data for a stock or an ETF.

The parameters are filled automatically with default values. We can change the ticker symbol in the Required Parameters to any stock or exchange-traded fund (ETF) that we want to retrieve data. For example, enter AMZN (the parameter is case insensitive) to fetch options data for Amazon.

The ticker symbol
API endpoint URL and headers

Step 2: Import stock and options trading data to Google Sheets

Open Google Sheets and launch Amigo. To connect to Amigo, click Extensions→Amigo: Data Exports and Reports→Connect.

Launching the Amigo Add-on

We’ll be presented with a list of sources to connect and import data from on the sidebar. Select Custom API.

Select Custom API as the source to connect

Paste the endpoint URL in the API URL field. Do not include the apostrophes. (Likewise for the headers.)

Entering the endpoint URL

Then expand the Details tab and enter the headers. The headers are given in the Code Snippets. The characters to the left of the colon are the header names and those to the right are the header values. Enter these as separate headers. (Do not include the apostrophes.)

Name: X-RapidAAPI-Proxy-Secret, Value: a755b180-f5a9-11e9-9f69-7bf51e845926

Name: X-RapidAPI-Key, Value: YOUR_API_KEY

Name: X-RapidAPI-Host, Value: stock-and-options-trading-data-provider.p.rapidapi.com

Entering the headers

Preview and flatten the data before importing

The data will be imported into Google Sheets in a moment. Below is a snippet of the imported data.

Snippet of the imported data

The imported data include stock profit margin, EBITA, current price, earnings growth, return on assets, cash flow, and options data such as puts and calls.

To retrieve options straddle data use the Straddle endpoint. And, like the options endpoint, specify the stock or ETF using the ticker symbol. And import following the same procedure.

Once the data have been imported, you need only set a refresh schedule and the data will be updated automatically at regular intervals. And not only that, you can receive the updated data as alert in your inbox and Slack so that the data follow you. Amigo is your faithful amigo.

Click here to know more about setting up alerts and automatic refresh schedule in Amigo.

Some recommended articles:

Automate data imports from Bloomberg to Google Sheets

Import TD Ameritrade data to Google Sheets

Import Data From Nasdaq To Google Sheets