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.

There are 2 ways to connect to the TD Ameritrade API:

  • Preset “Connect” button (OAuth) premium
  • API Key. Please check the appendix for detailed instructions to retrieve your token. Please note that this method will work only for their public stock and options endpoints, not for your private account data.

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: Fetch Data from TD Ameritrade

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 create a custom request instead of using API Connector’s built-in integration. When you create a custom request, you add your complete URL into the request URL field, and choose TD Ameritrade from the OAuth menu.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 (OAuth only) 
    https://api.tdameritrade.com/v1/userprincipals
  • User Transactions (OAuth only) 
    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

Appendix: Get Your TD Ameritrade API Key

This section is presented as a free alternative to the above. Instead of clicking Connect, you will get your own API key. However, the API key does not permit access to private account endpoints.

  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.
    tdameritrade-img4
  6. To make a request, append your API key like this: https://api.tdameritrade.com/v1/marketdata/AAPL/quotes?apikey=YOUR_CONSUMER_KEY
  7. Since you’re using your own API key, leave OAuth set to None.
Previous Import SurveyMonkey Data to Google Sheets
Next Import TikTok Ads Data to Google Sheets

12 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

Leave a Comment

Table of Contents