Importing transactions from custom format csv file etc...

[tedzo]

Hi,
Ok, 2 issues-
Issue 1-
I have transaction history in the form of a csv file (because thats what my financial institution provided me). The format is as follows-

Trade Date,Action,Symbol,Security Description,Security Type,Quantity,Price ($),Commission ($),Fees ($),Accrued Interest ($),Amount ($),Settlement Date

I need to import this data and I figured I could use the generic import functionality. However that seems to be hard because of a couple of reasons-

1. I am not sure what TRAN should be. The values for "Action" in the csv file are-
DIVIDEND RECEIVED, REINVESTMENT, LONG-TERM CAP GAIN, SHORT-TERM CAP GAIN, TRANSFERRED TO BROKERAGE ACT, BOUGHT. What should the corresponding TRAN values be?(I plan to do a find/replace in the csv file...)

2. ANUM can be either Price or Value depending on whether it a dividend or not. Price and Value are 2 separate columns in the csv file. Since dividend and non-dividend transactions are mixed, I am not sure where to place ANUM in the format string. My layman solution was to try and separate the dividend and non-dividend transactions and import them separately. Painful...

Any ideas on how I can import with as little pain as possible?

Issue 2-
I initially tried to convert the csv file to ofx using a tool. However, FM doesn't recognize any transactions from the ofx file. The ofx file does contain transactions- I verified by opening it from Notepad. What may be the issue? If this approach worked, it would be the most ideal solution to my problem...

Thanks for your time.

[Mark]

Hi tedzo,

1) To determine the value for the TRAN field, see the documentation here:

http://www.fundmanagersoftware.com/help ... neric.html

For example, a dividend received would be DIST_D. A dividend reinvested would be REINV_D.

You may want to use Excel to massage your data into a format compatible with Fund Manager. One technique would be to create a new sheet and map the fields from your current CSV onto a new sheet where you can use formulas. For example, you can use IF statements to control the field mapping. Once you've created this second mapped sheet, you can save this to a new CSV file for importing.

2) When you imported the OFX file, did you have on the option to create new investments as needed?

[tedzo]

Mark,
Thanks for the response.

When I imported, FundManager gave me the option to create new investments as required and I checked the box. However, I had no such option in the software that did the conversion from csv to ofx. Its too bad that the conversion fails silently. Would be nice if FM displayed some error message with reasons. Is there a log that I can look into to glean more information regarding the cause of the failure to import?

I am not much of a Excel guy. So, I think I will stick to find/replace for now. I looked at the help for TRAN and I think I can figure that out. Do you have any thoughts about how I can handle ANUM (that it could represent either Price or Value depending on TRAN)?

Thanks.

[Mark]

Hi tedzo,

When importing from an OFX file, there will be two log files you can look at:

"xmldump.txt" and "ofxmemtrandump.txt"

For more details on these log files, read the bottom of this page:

http://www.fundmanagersoftware.com/tintrtv.html

On the Excel question... The best solution is to use an IF statement. If you are not comfortable with that, you can do the import in two steps and remove one set of transactions for each of the two imports. This second approach will be much more manual.

[tedzo]

Mark,
Thanks for the response.
I am still pursuing the path of least resistance I plan to go to the manual process as a last resort...

I looked and found xmldump.txt It appears to have all the transactions that were in the original csv file. So why won't FM import from the equivalent ofx file? It obviously got all the information from the ofx file. I don't see any error messages in the file either...

Thanks.

[Mark]

Hi tedzo,

You might re-check your import options. Did you have on the option to create new investments as needed? How about the date filter? If this doesn't help you can email me your ofx file as an attachment, and I'll take a look.

[tedzo]

Mark,
I am sure all the options are turned on and the date filter is set to all dates.
Can you pm me your email Id so that I may send you the file?

Thanks for taking the time.

[Mark]

Hi tedzo,

Please go to "Contact Us" and then "e-mail us" to fill out any short note. After you submit you''ll get an email address you can use.

[tedzo]

Ok, I have sent out the email via the Contact Us page.

[Mark]

Hi tedzo,

Unfortunately that OFX file doesn't have any "investment" transactions. It has a couple "other" banking transactions, but these will be ignored by Fund Manager. If the tool you used has some other formatting options you might try to steer it towards investment transactions instead.

[tedzo]

Mark,
Thank you for the response.
There doesn't seem to be any way to tweak the output from the ofx converter tool. I think at this point I will give up trying to convert to ofx. Rather surprising not to find a tool on the web that does such a conversion. I would have thought it would be a fairly common. Anyway.

After looking through the data, I have realized that the generic function will likely work just fine if can figure out 2 issues (below). So, I have decided to just use the generic import functionality. I have 2 questions though-
1. In my csv file, every DIVIDEND RECIEVED, LTCG, STCG entry has a corresponding REINVESTMENT entry (for the same amount). This is because all the proceeds from the dividend, ltcg and stcg were reinvested. The dividend, ltcg and stcg entries have a negative amount where as the reinvestment entries have a positive amount.
Question- What should the TRAN values be for the DIVIDEND RECIEVED, LTCG, STCG entries? For the REINVESTMENT entries, I can use the corresponding TRAN values (REINV_D, REINV_L, REINV_S).

2. I have transfered money (as in cash) from the account in question to another. This is represented in the csv file as
TRANSFERRED FROM THIS ACT TO BROKERAGE entry.
What should the TRAN value be for this entry? It is essentially money moving out of this account to another.

Thank you for your help.

[Mark]

Hi tedzo,

1) If you record the reinvestments as REINV_D then you would actually skip the DIVIDEND RECEIVED transactions altogether. The REINV_D is a reinvestment, which is a dividend received and then reinvested.

Alternatively, you could record the reinvestments as 'BUY' transactions, and then record the DIVIDEND RECEIVED as 'DIST_D' transactions. Basically, a single REINV_D is equivalent to a DIST_D plus a BUY. You wouldn't want to do a REINV_D and a DIST_D, as that is double counting the distribution.

2) It is simply a 'SELL' from the cash investment in this account.

[tedzo]

Mark,
Thank you for your response.

I cleaned up the file (it wasn't so bad). I came up with a format string. However, FM complains that my format string is illegal and I can't figure out why. Here is what I have-

One line of data from the csv file-
12/31/2007, REINV_D, FDRXX, FIDELITY CASH RESERVES,Cash,15.010,1.00,,,,-15.01,

What the line means-
Trade Date, Action, Symbol, Security Description,Security Type,Quantity,Price ($),Commission ($),Fees ($),Accrued Interest ($),Amount ($),Settlement Date,

Notice there are no spaces between fields after "security description". There is a "," after the last field.

My format string-
MM/DD/YYYY, TRAN, SYMB, MEMO,ITYPE,BNUM,ANUM,CNUM,XX

Whats wrong with this format string? I tried adding "0" for Commission, in case some value is required, even if 0. That didn't help.

Thanks.

[Mark]

Hi tedzo,

One problem is that you are using YYYY instead of YY. The 'year' key is only 2 Y characters, and it will match either a 2 or 4 digit year. Try fixing that, and see if you can get any further.

[L Larson]

I have used (really!) an apparent workaround for the conversion of the now outdated OFX file format.

It appeared to work, tho I have not wrung it out for the full 200 laps.

Its basically as you presented but with less variables, which might not do the job. I cant recall if it was in a special location on the Quicken site....

Frankly by the time I get thru playing in the alphabet soup of all this, it makes one want to put all his letters and numbers in the hands of a paid professional!!!!!!

L