Importing past transactions problems

by **BillWild** » Sat Nov 22, 2014 2:59 pm

Mark,

I'm taking a look at FM but having trouble with transaction imports.
Using the "TRN" button, FM manages pretty well to retrieve from the brokerage website (in my case TDAmeritrade), at least back through 2013, a time limit I think TDA imposes. Trying to do an import by incrementally inputting 1/1/2009 to 12/31/2009, the same for 2010, 2011, etc. comes up retrieving nothing.

Next, I dowloaded csv files for '09, '10, '11, and '12 and tried using
File/Import/Transactions/Generic. That confronted me with a dialog that looks pretty daunting.

TDAmeritrade's transaction header looks like this:

DATE,TRANSACTION ID,DESCRIPTION,QUANTITY,SYMBOL,PRICE,COMMISSION,AMOUNT,NET CASH BALANCE,REG FEE,SHORT-TERM RDM FEE,FUND REDEMPTION FEE, DEFERRED SALES CHARGE

Aside from the fact that TDA's date format "YYYY/MM/DD" is not among FM's choices, it looks like finding a correct translation for all the other elements would be very hard.

Where am I going wrong?

Bill

by **Mark** » Sun Nov 23, 2014 10:34 am

Hi Bill,

That import feature can be a little daunting, but it is powerful. You can match a YYYY/MM/DD date format using the format string:

YY/MM/DD

The "YY" keyword matches either a 2 or 4 digit year.

You will likely need to massage the downloaded CSV file from TD into a format compatible with importing into Fund Manager. The key data you need are: date, transaction type, price, shares, and commission. You can delete/ignore everything else. I would suggest pressing "Help" on that import dialog, and it explains how to use this command, and has some examples. If you need help, just post a sample line from your CSV file, along with the format string you're trying to use. I can help with the specifics.

by **BillWild** » Sun Nov 23, 2014 3:43 pm

Mark,

I can only imagine how hard it must be to find translations from the myriad brokerage data formats into ones recognizable to FM. I only have to deal with one.

For now, I think I have other setup issues that need resolving before I worry about this. Writing a pre-processor to whip TDA's csv files into shape should be straightforward once I get familiar with how FM works.

Thanks for the quick reply.

Bill

by **BillWild** » Sun Nov 23, 2014 8:14 pm

Mark,
Ok, since for now I can't import past transactions, I'm trying to do an end run on the problem.
Since several years of gains/losses/cash deposits are missing, there's quite a run out on what FM thinks my net worth is and what my broker thinks. (I believe my broker...)
Because so much detail is missing, an FM Investment Transactions report looks ridiculous.

A reconciliation shows that everything is spot-on except for my cash account.

How can I enter a ... transaction that will swing the cash account back into agreement with my broker? Nothing seems to be appropriate in the data register screen.
Thanks,
Bill

by **Mark** » Sun Nov 23, 2014 10:30 pm

Hi Bill,

To change the cash balance just record a buy or sell of cash.

by **BillWild** » Tue Nov 25, 2014 5:26 pm

Hi, Mark.
Ok on the cash adjustment. Now, on to processing the transaction files:

The TDAmeritrade format$ looks like this, at least as much of it as I need.

DATE,TRANSACTION ID,DESCRIPTION,QUANTITY,SYMBOL,PRICE,COMMISSION,AMOUNT,NET CASH BALANCE,REG FEE,,, Below is how it translates into the FM format:

Code: Select all: TDA Format $ FM $ ----------------------------- DATE DD/MM/YY TRANSACTION ID XX Don't care DESCRIPTION TRAN QUANTITY BNUM SYMBOL SYMB PRICE ANUM COMMISSION CNUM AMOUNT Extension, like price X shares Money Mkt Redemption total NET CASH BALANCE Accum total in money mkt acct REG FEE ? Don't know what this is ($0.16 in 5yr)

From your previous comments, this is what I should do: Replace the TDA format string with this

MM/DD/YYY,XX,TRAN,BNUM,SYMB,ANUM,CNUM,,,,,,

Then, replace each TDA transaction "Description" string, where they occur,
with FM TRAN values.

Code: Select all: TDA FM --------------------------------------------------- MONEY MARKET INTEREST (MMDA1) DIST_I MONEY MARKET REDEMPTION ? ie. Cash deposit from me. OFF-CYCLE INTEREST (MMDA1) DIST_I Bought BUY Sold SELL ORDINARY DIVIDEND DIST_S QUALIFIED DIVIDEND DIST_L FOREIGN TAX WITHHELD ? PARTNERSHIP DISTRIBUTION DIST_O SHORT TERM CAPITAL GAINS DIST_S LONG TERM GAIN DISTRIBUTION DIST_L

If you would, please look this over to see if I've got it right.
I don't know how to handle the two TRAN items marked "?" Can you give me a hand there? If you don't have a sample of a TDA transaction log, I can email one to you. Seems that you might need that.

Thanks for your help,
Bill

by **Mark** » Tue Nov 25, 2014 6:03 pm

Hi Bill,

A money market redemption is just a sell of cash, so that would be a SELL transaction code. Foreign tax withheld is a negative distribution value of type "Foreign Tax", so this is a transaction code of DIST_X. Usually this is a lowering of your received distribution, so the value should be negative.

If you'd like help with specific transactions, save the file to a .CSV, and then open it in Notepad, and copy/paste the lines here. It is good to look at the CSV file in Notepad, so you can see how it is actually formatted, instead of looking at it in Excel, which can hide formatting characters, like double quotes, etc.

by **BillWild** » Tue Nov 25, 2014 8:07 pm

Mark.
I think you've covered everything. It may help you to know I've been writing string handling code since the early 80's so I know that part.

All that's left is will this format string make FM happy:

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

Thanks,
Bill

by **Mark** » Tue Nov 25, 2014 9:49 pm

Hi Bill,

Yes, that is a valid format string, you just need to make sure your data file matches that format.

by **BillWild** » Wed Nov 26, 2014 2:29 pm

Mark,
Well, the Great Transaction Translation went well. Here's a tiny bit of the .crv:

Code: Select all: MM/DD/YY,TRAN,BNUM,SYMB,ANUM,CNUM 08/06/2008,SELL,1,,100000.00,,,!REM..........CASH DEPOSIT 08/28/2008,BUY,200,NLY,14.33,9.99,,!REM......Bought 200 NLY @ 14.33 08/28/2008,BUY,300,FRO,59.65,9.99,,!REM......Bought 300 FRO @ 59.65 08/28/2008,BUY,600,ACAS,21.59,9.99,,!REM.....Bought 600 ACAS @ 21.59 08/28/2008,BUY,500,BPT,88.93,9.99,,!REM......Bought 500 BPT @ 88.93 08/28/2008,BUY,500,PBT,24.1899,9.99,,!REM....Bought 500 PBT @ 24.1899 08/29/2008,DIST_I,,,17.1,,,!REM..............MONEY MARKET INTEREST (MMDA1) 09/19/2008,DIST_L,,FRO,900.00,,,!REM.........QUALIFIED DIVIDEND (FRO) 09/29/2008,SELL,300,FRO,46.0501,10.07,,!REM..Sold 300 FRO @ 46.0501 09/29/2008,SELL,200,NLY,13.8501,10.01,,!REM..Sold 200 NLY @ 13.8501 09/30/2008,SELL,500,ACAS,24.2705,10.06,,!REM.Sold 500 ACAS @ 24.2705 09/30/2008,DIST_I,,,1.75,,,!REM..............MONEY MARKET INTEREST (MMDA1) 10/03/2008,SELL,100,ACAS,21.81,10.01,,!REM...Sold 100 ACAS @ 21.81 10/06/2008,SELL,100,BPT,85.01,10.04,,!REM....Sold 100 BPT @ 85.01 10/06/2008,SELL,100,BPT,84.93,0.05,,!REM.....Sold 100 BPT @ 84.93 10/06/2008,SELL,300,BPT,84.53,0.15,,!REM.....Sold 300 BPT @ 84.53 10/06/2008,SELL,500,PBT,20.00,10.05,,!REM....Sold 500 PBT @ 20 10/14/2008,DIST_O,,ACAS,105.00,,,!REM........SUBSTITUTE PAYMENT (ACAS) 10/15/2008,DIST_O,,PBT,123.38,,,!REM.........NATURAL RESOURCE ROYALTIES (PBT) 10/29/2008,DIST_S,,NLY,110.00,,,!REM.........ORDINARY DIVIDEND (NLY) 10/31/2008,DIST_I,,,6.66,,,!REM..............MONEY MARKET INTEREST (MMDA1) 11/28/2008,DIST_I,,,7.73,,,!REM..............MONEY MARKET INTEREST (MMDA1) 12/31/2008,DIST_I,,,7.99,,,!REM..............MONEY MARKET INTEREST (MMDA1) 01/30/2009,DIST_I,,,4.13,,,!REM..............MONEY MARKET INTEREST (MMDA1)

(The string after the !REM is the TDAmeritrade transaction description.)

Unfortunately the only transactions that are being imported are BUY and SELL. None of the many, many DIST_* trans are being imported.

Any ideas?

Bill

by **BillWild** » Wed Nov 26, 2014 2:34 pm

Mark,
Whoops, sent wrong version of .crv. The first one had some missing symbols.

Code: Select all: MM/DD/YY,TRAN,BNUM,SYMB,ANUM,CNUM 08/06/2008,SELL,1,,100000.00,,,!REM..........CLIENT REQUESTED ELECTRONIC FUNDING RECEIPT (FUNDS NOW) 08/28/2008,BUY,200,NLY,14.33,9.99,,!REM......Bought 200 NLY @ 14.33 08/28/2008,BUY,300,FRO,59.65,9.99,,!REM......Bought 300 FRO @ 59.65 08/28/2008,BUY,600,ACAS,21.59,9.99,,!REM.....Bought 600 ACAS @ 21.59 08/28/2008,BUY,500,BPT,88.93,9.99,,!REM......Bought 500 BPT @ 88.93 08/28/2008,BUY,500,PBT,24.1899,9.99,,!REM....Bought 500 PBT @ 24.1899 08/29/2008,DIST_I,,MMDA1,17.1,,,!REM.........MONEY MARKET INTEREST (MMDA1) 09/19/2008,DIST_L,,FRO,900.00,,,!REM.........QUALIFIED DIVIDEND (FRO) 09/29/2008,SELL,300,FRO,46.0501,10.07,,!REM..Sold 300 FRO @ 46.0501 09/29/2008,SELL,200,NLY,13.8501,10.01,,!REM..Sold 200 NLY @ 13.8501 09/30/2008,SELL,500,ACAS,24.2705,10.06,,!REM.Sold 500 ACAS @ 24.2705 09/30/2008,DIST_I,,MMDA1,1.75,,,!REM.........MONEY MARKET INTEREST (MMDA1) 10/03/2008,SELL,100,ACAS,21.81,10.01,,!REM...Sold 100 ACAS @ 21.81 10/06/2008,SELL,100,BPT,85.01,10.04,,!REM....Sold 100 BPT @ 85.01 10/06/2008,SELL,100,BPT,84.93,0.05,,!REM.....Sold 100 BPT @ 84.93 10/06/2008,SELL,300,BPT,84.53,0.15,,!REM.....Sold 300 BPT @ 84.53 10/06/2008,SELL,500,PBT,20.00,10.05,,!REM....Sold 500 PBT @ 20 10/14/2008,DIST_O,,ACAS,105.00,,,!REM........SUBSTITUTE PAYMENT (ACAS) 10/15/2008,DIST_O,,PBT,123.38,,,!REM.........NATURAL RESOURCE ROYALTIES (PBT) 10/29/2008,DIST_S,,NLY,110.00,,,!REM.........ORDINARY DIVIDEND (NLY) 10/31/2008,DIST_I,,MMDA1,6.66,,,!REM.........MONEY MARKET INTEREST (MMDA1) 11/28/2008,DIST_I,,MMDA1,7.73,,,!REM.........MONEY MARKET INTEREST (MMDA1) 12/31/2008,DIST_I,,MMDA1,7.99,,,!REM.........MONEY MARKET INTEREST (MMDA1) 01/30/2009,DIST_I,,MMDA1,4.13,,,!REM.........MONEY MARKET INTEREST (MMDA1) 02/27/2009,DIST_I,,MMDA1,1.51,,,!REM.........MONEY MARKET INTEREST (MMDA1) 03/31/2009,DIST_I,,MMDA1,1.28,,,!REM.........MONEY MARKET INTEREST (MMDA1)

Bill

by **Mark** » Wed Nov 26, 2014 3:01 pm

Hi Bill,

Your DIST_* lines do not have a value for BNUM and CNUM. Even if they aren't used, there has to be a number... You can put a 0 there, but it can't be blank.

Also, the !REM is meant for the format string, not the data line. If you want to ignore everything after the CNUM field, use a format string like:

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

by **BillWild** » Wed Nov 26, 2014 4:11 pm

Mark,

Mark wrote:Your DIST_* lines do not have a value for BNUM and CNUM. Even if they aren't used, there has to be a number... You can put a 0 there, but it can't be blank.

Ok, that kinda runs contrary to what's implied in the help file but I changed it.

I misread the !REM thing. Actually, it's cleaner putting it in the format string.

Anyway, with the above changes, it imports now. Now I've got to go through it in detail to figure out why FM thinks I've got more money than TDAmeritrade.

BTW, I've got to say your speedy replies and detailed comments are very much appreciated.

Thanks again,
Bill

Importing past transactions problems

Who is online