Search API Connector Documentation

Print

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

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
    tdameritrade-img1
  3. Fill in your app details. The callback URL can be anything.
    tdameritrade-img2
  4. Click Create App and then click on the App name
    tdameritrade-img3
  5. You’ll now see your app details displayed. Note your Consumer Key as you’ll need it shortly.
    tdameritrade-img4

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.
    tdameritrade-img9
  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.
    tdameritrade-img10

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:
    tdameritrade-img11
  2. Now click Send and click on the response tab to see the JSON response in your sheet:
    tdameritrade-img12
  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.
    tdameritrade-img13
  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:
    tdameritrade-img14

Of course there is much more you can do with the TD Ameritrade API but that should get you started!

4 thoughts on “Import TD Ameritrade Data to Sheets”

  1. how do you extract the complete json response for options data with the stock price and its ticker for along with the list of contracts ?

    Reply
    • I’m not totally sure what you’re looking for, but the example in the article shows how to get options data and customize the fields returned using TD Ameritrade’s URL generator form. So I’d play around with the options in that form until you get what you want, then you can just copy and paste the URL they give you to see that same data in Sheets. If it’s not available via that form then it means TD Ameritrade doesn’t provide that data via API.

      Reply

Leave a Comment