Exporting Data from TD Ameritrade

This topic explains how to export data from TD Ameritrade. After you export your data from TD Ameritrade, you will be ready to import it into Morningstar Office. Once imported, you will be able to perform portfolio reporting, portfolio accounting, and billing for your clients and accounts.

If you have never imported data into Morningstar Office before, please review the Preparing to Import Data into Morningstar Office topic.

Note: Morningstar highly recommends that you begin importing with the help of a Morningstar Import Specialist. If you have never imported from this source before, call the Support team at 1-866-215-2503 for more details.

What is the process for exporting data?

Before importing data into Morningstar Office, you must export it from TD Ameritrade.

You need to do the following:

    1. Contact TD Ameritrade to confirm that you are receiving daily data files for each Rep code.

    2. Download files. You can download all of your historical data from TD Ameritrade.

How often should I export data?

It is strongly recommended that you export from your custodian on a daily basis. This ensures that daily fixed income prices (or other prices not supplied by Morningstar) are imported.

 

How can I save time with Morningstar’s Back Office Services team?

For advisors who wish to save the time and trouble of initialization, daily importing and reconciliation, Morningstar is pleased to offer the option of outsourcing these tasks to our Back Office Services team. Working directly with you to define and meet your needs, Morningstar’s BOS team will operate as an extension of your internal staff, under your direction and terms. If you would like to learn more about this service, please contact your Client Service Consultant.

Exporting Data from TD Ameritrade

This section describes how to export data files from TD Ameritrade. The following topics are covered:

How do I contact TD Ameritrade?

Before you can begin importing transaction data from TD Ameritrade into Morningstar Office, you must ensure that you are receiving daily data files. Contact TD Ameritrade at 1-800-431-3500 to confirm that you are receiving daily data files for each of your Advisor Codes in the TDA Institutional format.

 

What file types do I need from TD Ameritrade?

When you download data from TD Ameritrade, you will receive the file types as shown in the following table:

File Name

mm=month, dd=day, yy=year

File Type

TDyymmdd.TRD or TDyymmdd.TRF

Client/Account

TDyymmdd.POS

Position

TDyymmdd.PRI

Price

TDyymmdd.SEC

Security

TDyymmdd.TRN

Transaction

TDyymmdd.CBL or ADyymmdd.CBL

Cost Basis

 

How do I create a folder structure prior to downloading data?

When you download files from TD Ameritrade, you want to ensure you have the proper folder structure in place on your computer, so the information is well organized and easy to retrieve once you begin importing data into Morningstar Office.

The organization of your file structure should be completed in the following order:

    1. Open Windows Explorer.

    2. Choose a location, and create a folder for TD Ameritrade.

    3. Under the TD Ameritrade folder, create a sub-folder containing the year.

    4. Under each year, create sub-folders for each advisor’s Rep Code. This is the folder to which you will download your custodian’s data.

 

How do I download files from TD Ameritrade?

There are two methods for downloading TD files. For downloading multiple files at once, you'll need to use Internet Explorer. The files must be downloaded individually in all other browsers.

Method 1: Internet Explorer

    1. Open Internet Explorer and go to https://www.advisorservices.com. Follow the instructions below to set the web site to display in compatibility mode.

      1. From the IE menu bar, select Tools, Compatibility View Settings.

      2. The current web site should be displayed in the Add this website field.

      3. Click Add to move the Advisor Services web site into the Compatibility View section.

      4. Click Close.

    2. Enter your UserID and Password, and then click Log on.

    3. From the Account Tools menu, select File Downloads, Download Files. The File Downloads page opens.

    1. On the File Downloads page, enter the date range for the files you wish to download. If this is the first time you are downloading files from TD Ameritrade, make sure you enter a date range wide enough to capture all available files.

Note: Do not enter a date range longer than an entire year, as this will lead some files to overwrite others. If you need to import more than one year’s worth of files, do your download in batches of one year at a time.

    1. From the Advisor drop-down field, select the Advisor Code for which you want to download files. If you plan to download and import files for multiple advisor codes, download the files for each advisor code separately, one code at a time. .

    2. From the File type drop-down field, select TDA Institutional.

    3. If you have already downloaded files and are downloading them for a second time, check the box for Include files previously downloaded.

    4. Click the Search button (above the Matching Files area). In the Matching Files area, all available files for the selected date range and advisor code will appear.

    5. Check the box(es) next to the file(s) you plan to download.

    6. Click Download. The Browse for Folder dialog box opens, prompting you to select the folder in which the files will be stored.

Note: After clicking Download, you might be prompted to install a plug-in to allow TD Ameritrade files to be downloaded. If this happens, follow the directions to download and install the plug-in, then re-run steps 9 and 10.

    1. Select the folder you created for that Rep code, then click OK. A window opens, displaying the progress of the download.

    2. Repeat these steps for the Cost Basis file, if needed. Repeat these steps for each Rep code.

Method 2: All Other Browsers

    1. In an Internet browser other than Internet Explorer (or if using Internet Explorer outside of compatibility mode), go to https://www.advisorservices.com. Enter your UserID and Password, and then click Log on.

    2. From the Account Tools menu, select File Downloads, Download Files.

    3. On the File Downloads page, enter the date range for the files you wish to download. If this is the first time you are downloading files from TD Ameritrade, make sure you enter a date range wide enough to capture all available files.

Note: Do not enter a date range longer than an entire year, as this will lead some files to overwrite others. If you need to import more than one year’s worth of files, do your download in batches of one year at a time.

    1. From the Advisor drop-down field, select All Advisors.

    2. From the File type drop-down field, select TDA Institutional.

    3. If you have already downloaded files and are downloading them for a second time, check the box for Include files previously downloaded.

    4. Click the Search button (above the Matching Files area). In the Matching Files area, all available files for the selected date range appear.

    5. Click the EXE icon next to one of the files to download it.

    6. If you are prompted to save the file, click the arrow beside Save and select Save as. Locate the folder to house the file and click OK. Note: Make sure to download the EXE file to the correct Rep code file.

    7. Repeat steps 8 and 9 for each day and each Rep code.

    8. Repeat steps 3 thru 10 for Cost Basis files, if needed.

How do I extract TD Ameritrade Institutional files?

  1. When the files have been downloaded, open Windows Explorer and navigate to the folder where the files are stored. the files will have been downloaded as executable, or application, files.

  2. Extract the EXE application files by double-clicking them or double-clicking the run.bat file if available. You will end up with the TDA Institutional format files added to the folder.

    1. To create a run.bat file, open Notepad and type the following: for %%i in (W*exe) do %%i

    2. Save the file as run.bat in the Rep code folder with application files that need to be extracted.

    3. Copy this file to any other Rep code folders.

  3. After the extraction process is complete, delete the application files. The folder now contains only TDA Institutional format files, which are ready to be imported into Morningstar Office.

  4. Repeat these steps for each Rep code in your practice. Similarly, if you are importing more than one year's worth of files, repeat these steps for each year's files.

  5. Review each Rep code folder to verify that the files you downloaded are complete. Refer to the table for a complete list of files.

Now that you have exported your data from TD Ameritrade, you are ready to import it into Morningstar Office.

 

See Also

Preparing to Import Data into Morningstar Office

Handling Warnings in the Import Blotter

Troubleshooting Guide for Common Import and Reconciliation Issues