Import TD Ameritrade Data to Sheets
In this guide, we’ll walk through how to connect to the TD Ameritrade API from Google Sheets, using the API Connector add-on for Sheets. Endpoints require authentication via a consumer key, which is retrieved by creating an app.
CONTENTS
- Before You Begin
- Part 1: Create a TD Ameritrade App
- Part 2: Create an API Request URL
- Part 3: Pull TD Ameritrade API Data into Sheets
- Part 4: Get TD Ameritrade Options Data
BEFORE YOU BEGIN
Click here to install the API Connector add-on from the Google Marketplace.
PART 1: CREATE A TD AMERITRADE APP
- Register a TD Ameritrade Developer account at https://developer.tdameritrade.com/user/register
- Navigate to https://developer.tdameritrade.com/user/me/apps and click Add a New App
- Fill in your app details. The callback URL can be anything.
- Click Create App and then click on the App name
- You’ll now see your app details displayed. Note your Consumer Key as you’ll need it shortly.
PART 2: CREATE AN API REQUEST URL
For our first request, we’ll connect to a stock quote endpoint that requires only an API key (other endpoints require OAuth2 authentication, which is more complex).
- API root: https://api.tdameritrade.com/v1
- Endpoint: /marketdata/AAPL/quotes
- Query string: ?apikey=YOUR_CONSUMER_KEY
Putting it all together, we get the full API Request URL.
https://api.tdameritrade.com/v1/marketdata/AAPL/quotes?apikey=YOUR_CONSUMER_KEY
PART 3: PULL TD AMERITRADE DATA INTO SHEETS
Now let’s enter our URL into API Connector and import TD Ameritrade data into Google Sheets.
- In API Connector, click Create, and paste in the Request URL we created above.
- We don’t need any headers so just leave that section empty. We don’t need any OAuth2 authentication either, so just leave that set to none.
- Create a new tab and click ‘Set current’ to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see some quotes about Apple stock populate your sheet.
PART 4: GET TD AMERITRADE OPTIONS DATA IN SHEETS
- To see more API endpoints and example responses, check out out the TD Ameritrade docs located at https://developer.tdameritrade.com/apis. In this case we’ll be looking at options data, so head over to https://developer.tdameritrade.com/option-chains/apis/get/marketdata/chains. They’ve provided a handy form where you can enter your API key and fill out your request filters like this:
- Now click Send and click on the response tab to see the JSON response in your sheet:
- Once you’ve confirmed that you’ve retrieved the correct set of data, click on the cURL tab and copy the entire request URL that appears there.
- In this example the URL is
https://api.tdameritrade.com/v1/marketdata/chains?apikey=YOUR_KEY&symbol=AAPL&contractType=CALL&fromDate=2021-02-19&toDate=2021-02-19
. We can now copy and paste this URL into API Connector. Select the ‘compact’ report style (under Output Options) to produce the following report:
Of course there is much more you can do with the TD Ameritrade API but that should get you started!