Ameritrade Transactions for over 2 years

[aceball]

Hello, I am trying to import transactions from my Ameritrade account dating back to 2009. I have downloaded the CSV file with transactions for individual years, but I keep getting a format error in Fund Manager. I can open the file in excel, and can make out the transactions but cant get Fund Manager to read it. Can you point me in the right direction, my search didnt turn up what I needed.

Thank you!

[Mark]

Hi aceball,

You would use "File / Import / Transactions / Generic..." to import from a CSV file. Open up that dialog, and press "Help" and read that online help topic. This is a powerful and slightly complex feature, so read through that online help topic, and see some of the examples in there as well. You may very well have to massage your downloaded CSV file into a format compatible with FM before importing. For example, this command requires you use BUY and SELL for buy/sell transactions, and TD may not use the same transaction code.

If you still have questions, post a couple lines from your CSV file, plus the format string you're entering.

One tip is to open your CSV file in Notepad (or any text editor), so you can see the raw formatting, that sometimes gets hidden when viewing in Excel.

[aceball]

Hi Mark,

Thanks for the quick response....So here is a snippet of the CSV file, it opens nicely on excel, but it looks like its going to need a lot of massaging to play nice with FM.

DATE,TRANSACTION ID,DESCRIPTION,QUANTITY,SYMBOL,PRICE,COMMISSION,AMOUNT,NET CASH BALANCE,REG FEE,SHORT-TERM RDM FEE,FUND REDEMPTION FEE, DEFERRED SALES CHARGE
09/21/2009,5299379216,PERSONAL CHECK RECEIPT,,,,,1500.00,1500.00,,,,
09/22/2009,5301940353,MONEY MARKET PURCHASE,,,,,-1500.00,0.00,,,,
09/22/2009,5301940355,MONEY MARKET PURCHASE (MMDA1),1500,MMDA1,,,0.00,0.00,,,,
09/25/2009,5314827540,Bought 25 TDN @ 18.3494,25,TDN,18.3494,9.99,-470.73,-470.73,,,,
09/25/2009,5315236889,Bought 98.912 PFBDX @ 10.11,98.912,PFBDX,10.11,,-1000.00,-1470.73,,,,
09/28/2009,5316366909,MONEY MARKET REDEMPTION,,,,,1000.00,-470.73,,,,
09/28/2009,5316366912,MONEY MARKET REDEMPTION (MMDA1),1000,MMDA1,,,0.00,-470.73,,,,
09/30/2009,5324910117,MONEY MARKET REDEMPTION (MMDA1),470.73,MMDA1,,,0.00,-470.73,,,,
09/30/2009,5324910102,MONEY MARKET REDEMPTION,,,,,470.73,0.00,,,,
09/30/2009,5328667083,PAPER STATEMENT FEE,,,,,-2.00,-2.00,,,,
09/30/2009,5330936461,MONEY MARKET INTEREST (MMDA1),0.01,MMDA1,,,0.00,-2.00,,,,
10/01/2009,5339824480,MONEY MARKET REDEMPTION,,,,,2.00,0.00,,,,

So actually just noticed that the formatting comes out much nicer in when cutting and pasting into here and in wordpad. What format string would you recommend for this type of data?

Thanks again for helping me with this.

[Mark]

Hi aceball,

I would probably re-save this as a copy without all the fields you won't be using. That makes it easier to write the format string. I would suggest reading that online help topic and it should become clearer. You would need the date, symbol, transaction code, price, shares, and commission. Other fields can be deleted. You can also keep the description as a memo if you want. For a quick example, the date such as "09/21/2009" would match with a format string segment:

MM/DD/YY

You also need a transaction code, like BUY or SELL on each line. So, when you get it formatted a little closer, it will look something like:

09/25/2009,BUY,TDN,25,18.3494,9.99,Bought 25 TDN @ 18.3494

which would import with a format string of:

MM/DD/YY,TRAN,SYMB,BNUM,ANUM,CNUM,MEMO

[aceball]

Whats the best way to format the cash deposits coming into the account? Also, what should I do with the money market redemptions that occur when buying and selling?

[Mark]

Hi aceball,

A deposit of cash is recorded as a buy transaction. If you deposit $1,000, record a buy of 1,000 shares, price of $1, value of $1,000.

If you're using a default cash account, and have the money market designated as your default cash account, you don't need to import these money market side of the transactions. For example, if you buy a stock for $1,500, importing the stock buy with a default cash account assigned will record both the buy of the stock, plus a sell of the proceeds from the assigned default cash account. You don't have to use a default cash account, so if you want to import the cash side of all your transactions, just don't designate a default cash account.

[aceball]

Hi mark, thanks for the help so far...Im still having trouble with importing cash in generic csv

09/22/2009,BUY,1500,CASH,1,,,
09/25/2009,BUY,25,TDN,18.3494,9.99,
09/25/2009,Buy,98.912,PFBDX,10.11,9.99,

With the above, FM recognizes the 2nd and 3rd lines but cash still not showing up.

[Mark]

Hi aceball,

What format string are you using? I assume you are using:

MM/DD/YY,TRAN,BNUM,SYMB,ANUM,CNUM,MEMO

Also, you only have 2 of the 3 required numbers on that first line. You must have price, shares, and commission for a BUY or SELL transaction. If the commission is 0, enter a 0, but it cannot be blank.

[aceball]

Yeah, thats the format string and the problem was leaving cnum blank.

Quick question, when dealing with dividend reinvestment, do I have to import the original dividend distribution or can I just use the reinv_d transaction alone?
Thanks for all your help.

[Mark]

Hi aceball,

You can just import using REINV_D and that records the whole reinvested distribution. Do not import both a DIST_D and REINV_D, as that would give you 2 distributions, one distributed, and one reinvested.

[aceball]

Thanks for your all the help Mark, I am well on my way of importing my transactions from 2009 to 2015. After that, will all be automatically imported. Thanks again.