Tax Season – How To Properly Import Trades From Scottrade And InteractiveBrokers
| Share |
Updated: February 24th, 2009
Why do I dread doing taxes every year? One of the main reason was having to figure out ways to import hundreds of transactions from my stock brokers: Scottrade and InteractiveBrokers. I love IB but come on, it's 2008 and they still don't provide .txf files to import into TurboTax (or TaxCut, but I use TurboTax myself). Scottrade, on the other hand, is directly importable through TurboTax but it only imports sale amounts and not purchases, so TurboTax thinks I got my stocks for free and wants to tax me on the full sale amount rather than the profits. That is, of course, very incorrect. Here are the best solutions I could find to these problems:
1. Scottrade.
This one is easy. Instead of doing a direct import through the tax program itself, log into your Scottrade account, go to My Account -> GainsKeeper -> Export Data and download this report available in .txf format: Aggregate Realized for 20XX (whatever the tax year is). It contains both the properly filled out cost bases (amounts paid + commissions) and final sale amounts for all stocks traded. Then just import this file into your tax software. Done.
2. InteractiveBrokers (IB).
Since these guys have been too lazy to write a .txf exporter, only providing HTML, Excel, and PDF reports, you would either have to import all trades manually (yeah right), write a quick script (meh, boring), or use an existing tool to convert these reports into .txf. There are a few tools potentially suitable for this task, some costing hundreds of dollars but the absolute best and cost efficient option I found is InvestoTek Excel macro called ConverExcel2TXF. It only costs $5 and does the job perfectly. Here's what to do:
- log into your InteractiveBrokers account -> Report Management -> Tax Forms -> Schedule D Gain/Loss Summary Worksheet, select your tax year, MS Excel, and View Report. Now you have an Excel spreadsheet with all trades made.
- purchase and download the ConvertExcel2TXF macro. There's a trial macro that converts only 2 trades here, so try it out before you buy (but seriously, $5 is nothing). The macro comes as an Excel spreadsheet. Fire it up and enable macros.
- with the macro spreadsheet running, fire up the IB spreadsheet downloaded earlier. Go to View -> Macros (at least that's where they sit in my Office 2007) and run ConvertExcel2TXF.xls!Run
- Here's where you have to tell the macro which fields are which – I think it's all pretty self-explanatory. Take a look at a sample from my report:
- click Generate TXF File and import it into your tax software.
Well, that should do it. Tax time is arduous and stressful, hopefully you'll find this article helpful. Do you know a better way to import trades? Do you do it differently? Please share in the comments.
Artem Russakovskii is a San Francisco programmer, blogger, and future millionaire (that last part is in the works). Follow Artem on Twitter (@ArtemR) or subscribe to the RSS feed.
In the meantime, if you found this article useful, feel free to buy me a cup of coffee below.
beer planet is a blog about technology, programming, computers, and geek life. It is run by Artem Russakovskii - a local San Francisco geek who currently works at 
Artem loves commenting on other people's blogs and reading comments on his own, so go ahead and leave one.
As earthsci mentioned in the comments on FatWallet, someone wrote a free TXF generator for Interactivebrokers, so you can just copy the transaction list from IB, which saves you $5. I've reimported my data using the tool and it matched what I got with investotek.
http://tax.earthsci.net/
What about calculating wash sales, etc.?
I also find that my IB schedule D form contains errors for stocks that I transferred to IB from other brokers… it puts in a cost basis that comes from who knows where.
Thanks!~
dan, I think wash sales are not supported by either program :-S but I'm not entirely sure. Thankfully, I didn't have any.
You can email Jeff from investotek and ask him any questions about it. I found him very responsive, which is especially important a week before taxes are due.
Last year I used SimplyTrack for IB and it worked wonderfully, including sorting out wash sales. It costs $50 though and I'm not confident that it works on this year's IB forms.
Great article. Too bad your solution doesn't work.
There is no "proceeds" columns or a "shares" column on the IB report, so you're left with manually reformatting the data yourself.
Never mind, my bad. IB's "sale price" is net proceeds. I thought this was teh per share price.
Yup, that's the one
does JEff's program calculate wash sales when imported into turbotax?
josh, please see my reply to dan above.
Artem: thanks i emailed Jeff.
With the SimplyTrack, were you able to calculate wash sales this year with IB? Did it work the same way (export to excel then export to txf)
Thanks for replying so soon. IB has been a nightmare to do taxes with.
Josh
Josh, I used SimplyTrack to do my taxes for the year of 2006 and it calculated everything wonderfully, including wash sales. I have not tried it for 2007, however, so I can't promise it would still work. Wash sales confuse the crap out of me, so I try to be a lot less of a day trader nowadays. Good luck!
just in case you might be interested in my new finding. I tried http://tax.earthsci.net/ for the free converter. It has to work with Excel which I don't have. I found another website (http://www.txf-express.com/)which you could upload the .html file from your broker. It will then send the txf file to your email box. It cost me 3 bucks, but that is cheaper than buying Excel:-)
Thanks to the finding by floormap. I used TXF Express's online service to convert a total of 1624 IB trades to TXF for the year 2008. The cost is a mere $6.24. A big time saver. Just a few simple mouse clicks, I am done with downloading the generated TXF file.
It seems IB has included new ways to export data via the Activity Downloads section. You can only do one month at a time, so it'll have to be 12 separate exports for 2008, but it includes the following formats: MS WORD, MS EXCEL, MS MONEY, QUICKEN, CAPTOOLS, TRADELOG, GAINSKEEPER. Are any of these formats more directly importable into TurboTax or TaxCut?
what is the best way to do this process from IB to TaxCut while generating the appropriate wash sale entries? I see some ideas mentioned but no confirmations…
It looks like scottrade has removed (or at least moved) the export button from gainskeeper. I can't seem to find it, while I'm now trying to do my 2008 taxes.
Can anyone help?
Thanks,
Glen
Glen, you might give it a try at http://www.txf-express.com/. They have included support for Scottrade as well.
Very Helpful! Thank you.
I noticed that when I export trades from scottrade, I see only sales not purchases. I called Scottrade and they said that the purchases should be entered manually. Is this true? Any one tried exporting .txf file from scottrade? How do you handle purchases? Please help
Thanks
Amy
@Amy
Are you saying that on the report for every reported sale, the purchase price is not stated?
Or are you saying the report just doesn't include unsold purchased stock? In which case, that's normal as you only report to the IRS when you sell.
Artem,
The file I exported from scottrade has the following columns
Close date
Rec type
Open date
security date
shares sold
sales
cost
st gain/loss
Lt gain/loss
This has the cost (purchase price) and sales (sale price). Is the above data enough to report to IRS? What confuses me is when I called scottrade, the rep told me that I should enter the purchase data seperately. Is this true? If the exported data has cost of the stocks purchased, why should I enter the purchase seperately. Please bear with me as this is the first time I am filing a return for stocks
Thanks for your help & time
Amy
Amy, of course I would advise you to talk to a tax professional but here's what I think:
- you only report what you sell. If you haven't sold it yet, you don't have to report it.
- you don't need to enter purchases, just sales.
- in the end, the numbers for your sales should add up to what your Schedule D form says
I have the TXF file from Scottrade, however, I can not import the file using the on-line program? Comments?
Ok I got the full version of TurboTax and was able to import …. however, the wash sales all messed up. It seems to be counting them as short term losses. Any advise? Thanks!
Thanks a lot for this writeup. A direct import from Scottrade into TT made things horrible. Your article saved me hours of headaches. Thanks!
FYI, guys, InteractiveBrokers now have a .TXF export option in the tax section of your account area. Not sure if it deals with all the wash sales though.
Will the converter work with futures contracts and short selling stocks at Interactive Brokers???
thank you , thank you, thank you. I was looking for a solution for this turbo tax solution. Nice. Thanks
The converter is really cool (and cheap). Thanks for blogging about it. For filing next year's taxes, take a look at http://www.tax-compare.com We compare 4 top companies of online tax software with the plans they offer.
I am faced with a massive wash sales this 2009 tax year. I tried to import the IB Gain/Loss Worksheet TXF directly into TurboTax, but too many errors. I tried the Convert2Excel macro (worked great last year) and Jeff even revised it for me for this year's unique IB format (includes a new Wash Sales Adj line)…but TurboTax pukes on it.
It looks like I will be forced to buy either Gainskeeper or SimplyTrack. I have no idea how well SimplyTrack works but I'm amazed at its low price ($50) compared to GainsKeeper ($149/1,000 for transactions/annual subscription).
Anyone else using SimplyTrack? Good/bad?
P.S. New tougher IRS regs effective in 2010 will require brokerages to report the COST BASIS as well as SALES PROCEEDS of all your brokerage transactions.
More here: http://accountant.intuit.com/practice_resources/articles/tax/article.aspx?file=tmdd_StockSales
I have the same problem of wash sales. I know nothing about Simplytrack. But I did find another tool called iScheduleD (http://www.txf-express.com/software/ischeduled_portfolio_manager.html) which handles my wash sales pretty well. It is also much cheaper (~$20) than Simply track. I don't know how well iScheduleD copes with massive wash sales (I don't have many). But I guess you can try the free demo to find out.
UPDATE: I worked with Jeff Harrison some more over at Investotek.com with his nifty little $5 ConvertExcel2TXF macro. We finally got it to convert the IB data and upload the Schedule D-1 error free into TurboTax. He is a great resource. Very responsive and helpful.
I did consider SimplyTrack and would have chosen that solution if I had truly needed it. I downloaded the app and ran a test with my 282 transactions. It was lightning fast. Unfortunately you cannot generate a pro-forma Schedule D-1 without buying the software (still a pretty good deal at $50).
Luckily for me, all of my wash sales were moot because I did not trade in January 2010 and I closed all my positions in December.
If I download the current version will it work for me? I am using Interactive Brokers. I was going to download the software, but it talks about Interactive Brokers not having a .txf file when they actually do. Please help me. Thankx
This country's tax codes are ridiculous…and making a mistake on all this could send someone into bankruptcy…
Putting all that aside.. I just need someone to tell me what I have to do to get my IB trades into Turbotax. I have a plethora of wash sales. And I just want the simplest solution at whatever cost.
I just finished my taxes with H&R Block At Home Pro (formerly TaxCut). exported gain/loss summary from Interactive Brokers in TXF format; imported TXF info into H&R Block; click continue on the scary-looking confirmation screen including every "row", all numbers and items were perfect except for wash sales and expired options. wash sales had an extra line item for the wash sale adjustment so just had to delete the extra line and mark the original wash sale as such. the replacement shares already had the correct adjusted numbers. expired options just needed a "0" for the sale price. all-in-all relatively painless, and no extra software/tools needed.
I just imported IB trades from .txf straight into turbo tax desktop. Got alot of questions on the blank boxes that IB leaves for wash sales and such. I enetered in zero for all of them and everything seems to be on the up and up. MY total gains were correct so I guess( and hope ) im good to go. Hope this helps someone somehow.
Thanks for the great tips! I used to use GainsKeeper which charges a hefty fee. I decided to give those less-known but much cheaper wash sales calculator a try. I tried out iScheduleD from txf-express.com and I must say the customer service is great! First there was some discrepancy between the numbers iScheduleD and GainsKeeper report. I contacted the customer service and they got back to me in a few hours and helped me solve the discrepancy. At a fraction of the price of GainsKeeper, it is a very good deal.
I think wash sales are not supported by either program the replacement shares already had the correct adjusted numbers.
define "supported"… did it import perfectly? no. was it super easy to fix and was all the math complete? yes.