Search API Connector Documentation

Print

Import TD Ameritrade Data to Sheets

Last Updated On
byAna
< Knowledge Base

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.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get Your TD Ameritrade API Key

  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. Here I’ve used https://google.com
    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 API Data into Sheets

Now let’s enter our URL into API Connector.

  1. In API Connector, click Create, and paste in the Request URL we created above.
    tdameritrade-url
  2. 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.
  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-results

Part 4: Get TD Ameritrade Options Data into Sheets

  1. 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 ‘grid‘ report style (under Output Options) to produce the following report:
    tdameritrade-results2

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.

Previous Import SurveyMonkey Data to Google Sheets
Next Import TikTok Ads Data to Google Sheets

10 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

    • Sorry, I haven’t connected to TD Ameritrade via OAuth2 as I don’t have a TD Ameritrade account myself.
      This article shows how to create a custom OAuth2 connection: https://mixedanalytics.com/knowledge-base/create-a-custom-oauth2-connection/
      And TD Ameritrade’s OAuth2 documentation is here: https://developer.tdameritrade.com/content/authentication-faq, with additional instructions here: https://developer.tdameritrade.com/content/getting-started. Some of their info seems weird / inconsistent, like I don’t see any mention of a client secret, and they only mention in one place that the client ID is yourConsumerKey@AMER.OAUTHAP. So it would definitely require some testing.

      Reply

      • Never done API’s before but trying to do this now. I’m struggling to figure out how to retrieve cash balance in Sheets from TD. Setup the API Connector, filled out the TD API link here https://developer.tdameritrade.com/account-access/apis/get/accounts/%7BaccountId%7D-0
        This API has an OAuth 2.0 and I filled that in with login credentials and it shows I’m authenticated. I did not enter any header authorization/token because that’s over my head and I feel like the OAuth 2.0 is authenticated so why wouldn’t it work. Then I copied the cURL to the API Connector GET function on Google Sheets, ran it and it says “1) : Completed with errors – Invalid argument:” then the cURL code. If I need to supply a Header Parameter with the token thing I have no idea how to do this, but tried, just a foreign language to me. Any help is appreciated, you’re user guide above worked well, it’s just the actual account information that seems to be tedious.

      • Hey Doug, this one is hard for me to help with as I don’t have a TD Ameritrade account, but from what I can tell from their docs it should be something like this:
        1) Create an app on their site. As part of this process they will request a redirect URL, which you can find in this article.

        2) Back in API Connector, fill out the custom OAuth form like this:
        Client ID: yourConsumerKey@AMER.OAUTHAP
        Client Secret: leave blank or enter a space
        Base Auth URL: https://auth.tdameritrade.com/auth
        Token URL: https://developer.tdameritrade.com/authentication/apis/post/token-0

        3) Run this request URL: https://api.tdameritrade.com/v1/orders
        You don’t need any headers as the OAuth connection manager will automatically set those for you.
        Please check if that works for you.

  3. Great product! Awesome and the trial is going well so far.

    I’m trying to use the IMPORTAPI function along with TD Ameritrade API to create a put options screener. I want to use the ticker symbol (in column A) along with the strike price and expiration date using using “&A1&”, “&B1&”, “&C1&” within the URL portion of the formula to reference these variables.

    What is the best way to do this b/c when I create the initial API connector, a ticker symbol, expiration date, strike price is required to create the initial URL for the but I will be updating this in each cell formula based on the row.

    Another option would be to use a separate sheet and run the API connectors for several ticker symbols then vlookup, but I’d prefer the IMPORTAPI if possible. Thanks in advance for any help you can provide.

    Reply

    • Hey Stan, when you use IMPORTAPI you can override the URL saved in the base request, so it doesn’t really matter what you put in there. Just save a request with the setup options you’re interested in (e.g. choose your report style, edit the fields, and so on).
      If you then save that request, let’s say as “Options”, you can then make an IMPORTAPI query like this:
      =IMPORTAPI("Options","https://api.tdameritrade.com/v1/marketdata/chains?apikey=YOUR_KEY&symbol="&A1&"&strikePrice="&A2&"&daysToExpiration="&A3)

      That way whatever values you add into those cells will dynamically become part of your IMPORTAPI request.

      Reply

  4. Thank you for the info on creating a custom OAuth for TDAmeritrade. Following your instructions above I discovered that the Callback URL on TDAmeritrade API creation site requires the Callback URL found on your creating a custom OAuth2 connector page. With out it, it provides a security warning and doesn’t provide the TDAmeritrade login page, however…

    With that API updated, I went back to my Google Sheet, clicked on the Connect button under my TDAmeritrade Custom Connections. It now provides me the TDAmeritrade authorization page to login. I enter in my info, successfully login, but it now gives me an Access Error page from script.google.com. The error begins as follows: “An error has occurred: Error: Token response not valid JSON: SyntaxError: Unexpected token”

    Any suggestions?

    Reply

    • Hey Ben, sorry, I think I need a TD Ameritrade account to properly troubleshoot this one. I’ll investigate and try to add a preset connection for this one in the next few months.

      Reply

Leave a Comment

Table of Contents