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

1. Register a TD Ameritrade Developer account at https://developer.tdameritrade.com/user/register
2. Navigate to https://developer.tdameritrade.com/user/me/apps and click Add a New App
   
3. Fill in your app details. The callback URL can be anything.
   
4. Click Create App and then click on the App name
   
5. 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.

1. In API Connector, click Create, and paste in the Request URL we created above.
   
2. 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.
3. Create a new tab and click ‘Set current’ to use that tab as your data destination.
4. 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

1. 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:
   
2. Now click Send and click on the response tab to see the JSON response in your sheet:
   
3. 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.
   
4. 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!