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.
This article currently only discusses getting public TD Ameritrade data. Check back soon for information on accessing private account data.
- Before You Begin
- Part 1: Get Your TD Ameritrade API Key
- Part 2: Create an API Request URL
- Part 3: Pull TD Ameritrade API Data into Sheets
- Part 4: Get TD Ameritrade Options Data
- Part 5: API Documentation
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Get Your TD Ameritrade API Key
- 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. Here I’ve used https://google.com
- 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.
Part 3: Pull TD Ameritrade API Data into Sheets
Now let’s enter our URL into API Connector.
- In API Connector, click Create, and paste in the Request URL we created above.
- We don’t need any OAuth2 authentication, so just leave that set to none. We don’t need any headers either so just leave that section empty.
- 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 into Sheets
- 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 ‘grid‘ 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.
Part 5: API Documentation
To see more API endpoints and example responses, check out out the TD Ameritrade docs located at https://developer.tdameritrade.com/apis.