I don't use Schwab, but if you can get to a screen that shows your account activity (buy, sell, interest charge, etc) in rows, I am sure one of the following will work for it as well.
Conversion/Capture Procedures for Account Transaction Detail
Waterhouse Data Import
No WORD setup
1) Select History Period; Highlight and Copy Fields
2) Excel - new worksheet; paste
3) Ready for Cleanup
Datek Data Import Steps
1) Copy all items and paste into a NEW WORD doc.
2) Save as Txt Only File
3) IMPORT Txt Only file to new Excel Worksheet; i.e., open the txt only file, follow delimit column prompts.
4) Save as Excel file for reload and review.
5) Review and clean up fields and rows. Delete CASH$ Hdr rows, also Note rows. Delete unwanted columns.
Etrade Data Import
1) Display Activity; Check Date Range,Select Range
2) Copy
3) Paste into a new WORD document. Save as TXT ONLY file.
4) Excel - Open TXT ONLY file; Folow Delimit Prompts, Verify Cell Breaks!
5) Cleanup - Remove bad lines; Adjust Pos Amts on Sales, Delete Bad Lines, Remove Dead Columns
These work great AS LONG AS you can bring up a screen for the timeframe you want. For example, Waterhouse has an account history page that will pull activities for 90 days.
Datek's accunt activity history page only goes back about 45 days (I think), so you have to do this step every month I believe.
Etrade - don't know how long their history is.
You can use Excel's "replace all" function to do clean up easily. For example, if your transaction type must all end up being the words "buy" and "sell", you can change files that have the words "b", "s", "bought", "sold" using the replace all function (under EDIT on excel tool bar) after selecting/highlighting the column that has the buy/sell keywords in it.
What I do is every 30 days, I do a 'copy' capture for that month, follow the above procedures to move it into my annual excel worksheet for each broker.
After you have all the buy/sell data in a worksheet, you need to get this into a tax application as an import data. The options here are numerous.
Options here I am experimenting with are:
FM - Fund Manager - This is a shareware application. Supposedly, it can import txt files with a specified format easily generated by excel as a txt file. It will cacluate the capital gains with all wash sales calculated, and generate a IRS accepted Capital Gains/Loss tax form. This is my initial strategy. Will let you know if this works.
Money2000 - If FM doesn't work, I plan to import the buy/sell transactions as a QIF file to Money2000, and see if Money2000 will handle wash sales. This is unclear if Money2000 will do this.
Quicken2000 - same as Money2000.
Tax Software - have not bought yet, but will research if the files from excel or from Money2000/Quicken2000 can be imported, and have it handle all the washsale calcs.
In any case, you MUST have some mechanism to adjust for stock splits and acquired companies (i.e., stock X,you own 100 shares, is bought, and now becomes stock Y with some number of shares).
Excel makes cleanup easy. For example, you can use Excel's date format to get your dates in the transaction date columns to be consistent since, of course, each broker uses a different format for date. You can delete unwanted columns, or only a range of colums and rows, and allow Excel to adjust (move over) adjacent cells the way you want.
My goal at this time is to get all my buy/sell transactions from my 3 accounts into 3 separate spreadsheets (1 for Datek, 1 for TDW, 1 for Etrade). This is now complete, and really did not take very long since I do this monthly (takes about 5 minutes for each account each month).
IMPT: I also have another Word Document that has all my split info (stock, split ratio, cutover date) and acq. info (orig stock symbol, new stock symbol, acq ratio, effective date). I know that FM and Money2000 have functions to do splits and acquired stock changeover so this should be easy to enter manually these split/acq adjustments and let the application (FM, M2000, Q2000, TurboTax...) generate the adjustments.
So the only thing I am left with is the wash sale function which I am working on.
buck |
