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 nor 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.
Shown below is a snippet of Tesla options data imported into Google Sheets.
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 rather strides.
Steps to import stock and options trading data to Google Sheets
Step 1: Get the API endpoint URL
We’ll use a stock option API on RapidAPI to retrieve stock and options data and import them into Google Sheets using Amigo.
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.
Click on the Subscribe to Test button and subscribe to a plan of your choice, post which we’ll be able to use the stock and options API and import stock and options trading data to Google Sheets.
Click Endpoints to go back to the stock and options API documentation. On the left of the page are the available endpoints that we can use to retrieve stock and options trading data to Google Sheets.
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 pull the options data to Google Sheets.
For example, enter AMZN (the parameter is case insensitive) to get options data for Amazon.
Click the Test Endpoint button to verify if the ticker symbol is valid. If the ticker symbol is invalid, the query will return a null value. Otherwise it will return the options data for the specified stock in the Results section in JSON.
Click Code Snippets beside Results. Here we’ll find the endpoint URL with the query parameter appended, and also the headers. Note these, we’ll use this to import the options data to Google Sheets.
Step 2: Import stock and options trading data to Google Sheets
Open Google Sheets and launch Amigo to import the options data to Google Sheets. Click Extensions→Amigo: Data Exports and Reports→Connect.
We’ll be presented with a list of sources to connect and import data from on the sidebar. Select Custom API.
Enter the options API endpoint URL in the API URL field. Do not include the apostrophes.
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.
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
Click the PREVIEW button to retrieve and preview the options data to Google Sheets. Flatten and expand the nested datasets and delete the columns that are not required. Then click IMPORT.
Important note!
The endpoint will return several thousands of distinct data–more than the limit Google Sheets can handle. So, it is recommended (actually, necessary) that some datasets are deleted and imported separately. A suggestion is to import the stock and options data separately using the same endpoint.
The Amazon stock and options data will be imported into Google Sheets in a moment. Below is a 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.
Automatically pull stock and options data to Google Sheets
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