ConverExcel2TXF - Broker Instructions    Add ConvertExcel2TXF to cart View Cart

Below you'll specific instructions for getting your capital gains from your broker into Excel. This list is a work in progress. If you don't see your broker listed, and you figure out the necessary steps for your broker, please email them to me and I will add them to the list. If you have any questions or comments, send me an email.

E*trade
Interactive Brokers
IZone (by TDAmeritrade)
OptionsXpress
Schwab
Scottrade
TDAmeritrade
TradeKing
TradeStation

E*Trade
Unfortunately, ETrade's format is a little screwed up. But, even though the following steps may look like a real pain, the whole process will only take a few minutes.
1. Goto "Trading & Portfolios" and then "Portfolios".
2. Click on the "Gains & Losses" tab.
3. Select the appropriate year in the "Tax Year" box and then click "submit".
4. You should now see a "Download" link next to a green arrow at the upper right-hand side.
5. Unfortunately, Etrade's download format is screwed up - they claim it is in Excel format (.xls), but is actually an html file. So, here's the best way to handle it:
a. When downloading the file (or after), change the file extension from .xls to .html.
b. Open the file in your BROWSER (i.e. Internet Explorer).
c. Select and copy everything (ctrl-a, then ctrl-c).
d. Paste into Notepad.
e. Save the Notepad file with the extension .csv (when saving you also need to change the "Save as type" box to "All Files").
6. Now the file will open correctly in Excel, however there is still one more problem with Etrade's data: they list the 'Open' and 'Close' transactions, rather than the 'Buy' and 'Sell' transactions, so if you have any short transactions the data will be in the wrong order. Short transactions will have a "BC" (Buy to Close) in the Order column. If you have any short transactions, here are the additional steps you need to take:
a. Download this file. It contains a macro that will help you fix the order of the columns.
b. First make a backup copy of your spreadsheet - just in case something goes wrong.
c. Open the Excel file that you downloaded in step a.
d. From within YOUR spreadsheet, select Tools > Macro > Macros...
e. Select the 'FixShortSellDates!Run' macro and click Run
f. Select the beginning and ending rows for your data.
g. Click Run. It will switch the opening and closing columns for all short transactions, and rename the column headers to the more standard "Buy Date", "Sell Date", etc.
That's it! Now you're ready to run the ConvertExcel2TXF macro to create the .TXF file for your tax software (see the tutorial). If you're an active trader, consider also adding my CombineDailyTrades macro.

Interactive Brokers
IB's 'Schedule D' tax report is a new feature starting in 2006, and is very easy to use. They don't yet offer a direct TXF download, but do offer an MS Excel download. Here are the steps:
1. Login to Account Management.
2. Click on "Report Management" and then "Tax Forms".
3. Select the option for "Gain and Loss Report (Schedule D)".
4. Select MS Excel as the download format."
That's it! Now you're ready to run the ConvertExcel2TXF macro to create the .TXF file for your tax software. Note: IB already combines the quantity with the description into a single column, so you'll just leave the quantity column box on the user form blank (see the tutorial). If you're an active trader, consider also adding my CombineDailyTrades macro.

IZone by TDAmeritrade
I don't yet have the details for generating the download, but it should be farily straightforward. Unfortunately, as with several other brokers, IZone only offers a download of individual buy and sell transactions, but what you need are the combined transactions, where the buy and sell orders have been matched up. But, don't worry - with the help of my easy to use MatchBuySell macro, you can get your transactions matched up in a matter of seconds. You can purchase the macro for a mere $2.50 by clicking on the previous link. That's it! Now you're ready to run the ConvertExcel2TXF macro to create the .TXF file for your tax software. If you're an active trader, consider also adding my CombineDailyTrades macro.

OptionsXpress
First, login to your account and follow the steps below:
1. Go to "Account" tab.
2. Select "Activity".
3. Select the period you want to download. (in the drop down menu you can select "last year".
4. Verify the "file type" is correct. (you can choose "all trades", "options trades", "futures trades" etc)
5. Click on the box "show commissions & regulatory fees.
6. In the drop down menu for "download this activity to" select "Excel".
7. Click "go" to view the selected transactions, then click "Download".
8. Select "Save to disk" and "OK".
Unfortunately, OptionsXpress only offers a download of individual buy and sell transactions, but what you need are the combined transactions, where the buy and sell orders have been matched up. But, don't worry - with the help of my easy to use MatchBuySell macro, you can get your transactions matched up in a matter of seconds. You can purchase the macro for a mere $2.50 by clicking on the previous link. That's it! Now you're ready to run the ConvertExcel2TXF macro to create the .TXF file for your tax software. If you're an active trader, consider also adding my CombineDailyTrades macro.

Schwab
I don't have the specific steps for generating the gain/loss download from the Schwab site (if you are a Schwab user, please send them to me), but Schwab does have a peculiar problem - for some reason the purchase date is not included in the download. Strictly speaking, this would mean that you would have to go back through all of your statements to find the missing purchase dates. However, the download does indicate whether the gain is short- or long-term, so there is a potential workaround. The IRS doesn't really care about the specific dates, as long as the short-term and long-term status is correct. So, you can just "approximate" a purchase date as long as the short- or long-term status is maintained. This can be done automatically with an Excel formula. Just follow the steps below:
1. Download this sample Schwab Excel file.
2. You'll see that I've added a new column (J) for the "approximate" purchase dates. The cells in this column have a formula that checks to see if the gain is short- or long-term. If it's short-term, the purchase date is calculated by subtracting one month and one day from the sale date. If the gain is long-term, the purchase date is calculated by subtracting one year, one month and one day from the sale date.
3. Just select and copy (ctrl-c) any cell in column J.
4. Now, in YOUR Excel file, just select column J over the whole range of your transaction data and paste (ctrl-v) the formula from step 3.
Now you're ready to run the ConvertExcel2TXF macro to create the .TXF file for your tax software, and all of your transactions will be imported into the appropriate short- or long-term schedules. If you're an active trader, consider also adding my CombineDailyTrades macro.

Scottrade
If you are a Scottrade customer, you do not need the ConvertExcel2TXF macro. Scottrade (for now) provides its customers with TXF files which you can import into TaxCut and TurboTax. For more details, see the Scottrade website. However, if you are an active trader, you may consider just downloading to Excel, and then using my CombineDailyTrades macro coupled with the ConvertExcel2TXF macro to consolidate and clean up your trades.

TD Ameritrade
If you are an Ameritrade customer, you do not need the ConvertExcel2TXF macro. Ameritrade (for now) provides its customers with free access to Gainskeeper, which should work seamlessly with TaxCut and TurboTax. Here are the steps for generating a .TXF file:
1. Click on "Portfolio & Accounts" from the top menu.
2. Click on "Gain/Loss".
3. Select "Export Data".
4. Select "Get Report as Tax Software TXF File"
However, if you are an active trader, you may consider just downloading to Excel, and then using my CombineDailyTrades macro coupled with the ConvertExcel2TXF macro to consolidate and clean up your trades.

TradeKing
Getting your data out of TradeKing is very straightforward. Here are the steps:
1. Goto Accounts -> Download Data
2. Click the checkbox for "Excel" under "Choose Format".
3. Choose "Realized Gain/Loss (Prior Year)" under Choose Data pull-down menu.
4. Click the "Download" button.
That's it! Now you're ready to run the ConvertExcel2TXF macro to create the .TXF file for your tax software. If you're an active trader, consider also adding my CombineDailyTrades macro.

TradeStation
First, login to your account and follow the steps below:
1. Under the "Account Services" tab, click "Balances, Positions & Activity"
2. Click "tax activity".
3. Select the date range you want. Only 3 months can be selected at a time. You will have to download 4 quarters and then combine them to a single file later.
4. Under "Security type" select either "All".
5. Under "Transaction type" select "All".
6. Under file type select "Excel" from the drop down menu
7. Click "Download" and then "Save to disk" and then "OK".
Unfortunately, TradeStation only offers a download of individual buy and sell transactions, but what you need are the combined transactions, where the buy and sell orders have been matched up. But, don't worry - with the help of my easy to use MatchBuySell macro, you can get your transactions matched up in a matter of seconds. You can purchase the macro for a mere $2.50 by clicking on the previous link. That's it! Now you're ready to run the ConvertExcel2TXF macro to create the .TXF file for your tax software. If you're an active trader, consider also adding my CombineDailyTrades macro.