In this article, we’ll learn how to import option chain data to Google Sheets. We can import options data from various exchanges with a simple tool called Amigo.
Amigo is a Google Sheets extension that we can use to pull data from scores of sources into Google Sheets. Amigo imports the data to Google Sheets and automatically updates them so that we’re always served with fresh data. And more, it sends us the updated data to our inbox or Slack.
Install Amigo and we’ll walk together through the steps on how to import option chain data to Google Sheets.
If you’ve installed the add-on, then importing option chain data to Google Sheets can be achieved in two steps.
Here is a foretaste of what you’d get after you import option chain data to Google Sheets.
How to import option chain data to Google Sheets
Step 1: Get the API endpoint URL
We’ll use a data provider called Twelve Data to retrieve the option chain data and import them to Google Sheets with Amigo. The Twelve Data API documentation can be found on its website as well in RapidAPI, an API marketplace. We’ll use the latter as it is simpler but offers all the functionalities.
Sign up or log in to RapidAPI.
Search for Twelve Data from the RapidAPI Hub and click the first option from the results. Or go directly to the Twelve Data API documentation page.
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 option chain data Google Sheets.
Click Endpoints to go back to the API Playground. On the left of the page are the available endpoints.
Option chain data can be found under the Fundamentals heading. Expand the section and click Options Chain.
In the Required Parameters section, enter the ticker symbol of the stock or index for which you want to import option chain data to Google Sheets. The symbol of Apple is filled by default. We’ll leave it as it is.
We’ll need to specify the expiration_date and/or the option_id parameters to retrieve the option chain data for that option; and leave the other parameters blank. Though they’re placed within Optional Parameters, they’re required.
We’ll import the option chain data to Google Sheets the Apple option that expires on 2023-02-17. The expiration dates can be got with the Options Expiration endpoint.
To view the result, click the Test Endpoint button. The result will be displayed in JSON format under the Results section on the right, if the parameters are valid.
The API endpoint URL along with the headers–which will be required to import option chain data to Google Sheets–can be found in the Code Snippets section.
Change the language from Node.js (Axios) to Shell→cURL. Copy the URL and note also the headers.
Step 2: Import option chain data to Google Sheets
Open Google Sheets and launch Amigo. To connect to Amigo, go to Extensions→Amigo: Data Exports and Reports→Connect.
If you haven’t yet installed the extension, you can get it from the link below.
We’ll be presented with a list of sources to connect and import data. Select Custom API.
Paste the endpoint URL (without the apostrophes) in the API URL field.
Then expand the Details tab and enter the headers. The headers can be found 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-RapidAPI-Host, Value: twelve-data1.p.rapidapi.com
Name: X-RapidAPI-Key, Value: YOUR_API_KEY
Click the PREVIEW button. Flatten and expand the nested datasets and delete the columns that are not required. Then click IMPORT.
The data will be imported into Google Sheets in a moment. The data include all the information about the option such as call price, put price, bid and ask prices for calls and puts, volumes and volatilities, and so on.
Below is a snippet of the imported data.
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 in your inbox and Slack so that the data follow you.
Know more about setting up alerts and automatic refresh schedule in Amigo.
Some commonly asked questions
Why am I getting a “No data” error?
There could be a few reasons why you’re getting the “No data” error. Some of these are entering invalid ticker symbols or incorrect option expiration date, exceeding the data quota for the subscribed plan or not subscribing to a plan, or the exchange from which you’re trying to retrieve the data is not supported by the API.
Is there a way to automate the process of updating option chain data in Google Sheets?
You can easily automate the process of updating option chain data to Google Sheets with Amigo. Just set a refresh schedule specifying the time and period, and the data will be automatically updated.
How to get NSE option chain data in Google Sheets?
You can get NSE option chain data in Google Sheets like you’d for any other exchanges. Find the ticker symbol of the stock you want to get the option chain data and the expiration date of the option chain. (You can find these on the NSE website.) Then them as parameters and follow the steps mentioned above.
Can I use Google Sheets to analyse option chain data?
Yes, you can. In fact, Google Sheets is one of the best tools for analyzing data of any sort–be they stock, marketing, or company data. Google Sheets is free, easy to use, flexible, and comes with lots of options for data visualisations that make analysing data easier and more efficient. There are also various templates that we can use for analysing data.
Some recommended articles:
Import Stock And Options Trading Data To Google Sheets