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 pull data from the TD Ameritrade API directly into Google Sheets, using the API Connector add-on for Sheets.

Contents

Before You Begin

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

Part 1: Connect to the TD Ameritrade API

The easiest way to get started with the TD Ameritrade API is through API Connector’s built-in integration.

  1. Select TD Ameritrade from the drop-down list of applications
    tdameritrade-application
  2. Under Authorization, click Connect to TD Ameritrade
    tdameritrade-authorization
  3. You will see a modal asking you to approve the connection. Click Allow.
    tdameritrade-allow
  4. You’ll then be returned to your Google Sheet, and can verify that your connection is active.

Part 2: Pull Data from TD Ameritrade to Sheets

Now that we’re connected, let’s pull some data into Sheets.

  1. Under Endpoint, choose /accounts to get information about your accounts
    tdameritrade-endpoints
  2. Select a destination sheet, name your request, and click Run.
    tdameritrade-response
  3. Note the accountId as you can plug that into other requests.

Part 3: Create a Custom Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the endpoints and parameters shown in the API documentation. Here's an example request setup.

  • Application: Custom
  • Method: GET
  • Request URL: https://api.tdameritrade.com/v1/marketdata/AAPL/quotes
  • OAuth: TD Ameritrade

tdameritrade-results

Part 4: Example Custom API Requests

  • Option chains (set to 'grid' report style)
    https://api.tdameritrade.com/v1/marketdata/chains?symbol=AAPL&contractType=CALL&fromDate=2021-02-19&toDate=2021-02-19
  • User Principals
    https://api.tdameritrade.com/v1/userprincipals
  • User Transactions 
    https://api.tdameritrade.com/v1/accounts/YOUR_ACCOUNT_ID/transactions

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.

They also provide URL generator forms, you can see an example here: https://developer.tdameritrade.com/option-chains/apis/get/marketdata/chains

16 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 [email protected]. So it would definitely require some testing.
      Update: I've added a preset OAuth connection for TD Ameritrade.

      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.

  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.
      Update: added a preset connection for TD Ameritrade, please check it out!

      Reply

    • Thanks Jonathan! Their /accounts endpoint description says "Account balances, positions, and orders for all linked accounts" so it looks like that's the one. But if it doesn't have enough detail I think you'd need to calculate it yourself by getting transaction data from the /transactions endpoint.

      Reply

  6. Trying to register for a TD Dev Account here:
    https://developer.tdameritrade.com/user/me/apps

    Results in:
    "Encountered an Error. Please go back to our site"

    Which redirects you to the homepage:
    https://developer.tdameritrade.com/

    Which says:
    "New user registration for the TD Ameritrade API has been disabled in preparation for the Charles Schwab integration. Click here for more information on the API program as related to integration."

    Which redirects you here:
    https://developer.tdameritrade.com/content/trader-api-schwab-integration-guide

    Since the date of these instructions is Jan 2023, but the Schwab acquisition has been announced for over a year now, where are your updated instructions to set up your API with TDA as of now?

    Thank you

    Reply

    • Sorry for the confusion, seems they recently disabled accessing the API through a key in preparation for the Schwab integration. I removed that section. That means you have to connect through our OAuth connection instead as that will continue to work through their migration. The integration guide says they haven't enabled the Schwab developer portal yet, so there aren't any updated instructions to share yet, but we'll add that once it's available.

      Reply

Leave a Comment

Jump To