Problem Importing Investment Transactions from MS Money

[lmacri]

Does anyone have DETAILED instructions on how to correctly import investment data from MS Money 2005 into the trial version of MyMoney 2.0.52? I’ve tried more than once and followed the instructions on the wiki but I’m still having problems.

Here’s a few lines from my CSV-12 file I created with the MS Money Investment Transactions report:

Date,Account,Investment,Activity,C,Quantity,Price,Commission,Total,Memo,Transfer Account,Category
23/02/1994,Direct 610020-A,Templeton Growth,Buy,,711.004,8.27,120.00,6000.00,,Direct 610020-A (Cash),
31/03/1994,Direct 610020-A,Altamira Income,Reinvest Interest,,5.512,7.24,,39.91,,,Investment Income : Interest

I’ve been getting incorrect dates (some of my imported transactions have the years 2011 and 2012 and dd/MM/yyyy is sometimes switched to MM/dd/yyyy) and my computer freezes for about 30 minutes while the CSV import get struck at the point where progress bar says “Removing duplicates 3 of 0” (this freeze is reproducible and happens even if I start again with a blank database). I think I need to make some configuration changes in the View | Options | Import/Export section but I’m not sure exactly which items need to be fixed to ensure a proper import.

Also, the account cash balance and market value of investments in the Portfolio Manager are incorrect and I can only see investment transactions like buys, sells and dividend reinvestments in the Account Manager (i.e., I can’t see cash deposits / withdrawals), so I’m guessing that I have to export these cash transactions as a CSV-8 file with the MS Money Account Transactions report and then manually delete any duplicate transactions after they’ve been imported into MyMoney.

Other software programs I’ve tested like Fund Manager Personal 10.4, MoneyDance 2010 and AceMoney Lite 3.20 let me import the two QIFs exported from MS Money (one for the investment transactions and one for the cash transactions) and everything works quickly and accurately. Having to fuss with CSV files in MyMoney is a huge headache.
_____________

MS Windows Vista Home Premium SP2 (32-bit)
MyMoney 2.0.52 (trial version)
Sun Java version 6 update 18 (build 1.6.0_18-b07)
Intel Core2Duo CPU T5550 @ 1.83 GHz, 3.0 GB RAM, NVIDIA GeForce 8400 GS

[mthadm]

Hello,


Could you please send us a screenshot of incorrect date in the register and may be a fragment of a csv file that has date that was translated wrong, this is obviously a bug and we would like to have it fixed.

The investments part in general is very new and is still in beta so please bear with us while we transition it into a higher quality software,

Thank you,

[lmacri]

Hi mthadm:

I figured out the problem with the dates – I opened the CSV file in Excel to check the data and the date format was inadvertently changed to the Excel default format when the file was resaved.

However, I discovered at least 6 other problems with the import of the CSV-12 from MS Money

1. Commissions Subtracted (Not Added) to the Total Amounts

It looks like commissions are not being added to the gross cost of investment transactions

Commissions are not reflected anywhere in the Account Manager (cash account). For example, a buy of Trimark Fund on 23-Feb-1994 imports correctly into the Portfolio Manager (320.786@$18.33 = $5,880 and $120.00 commission) but the total cost in both the Portfolio Manager investment transaction and the Account Manager cash transaction appears as the net investment cost of $5,880, and not the gross cost of $6,000 as is shown in the MS Money CSV file.

If the commission is not associated with in the total cost of the transaction then this will
a) Mess up the cash balance in the cash account
b) Affect the calculation of net capital gains (or losses) and average cost basis when a position is sold.

2. Payees in the Account Manager Don’t Always Match Investment Names In the Portfolio

For example several transactions for CI Harbour Growth and Income were recorded in the Account Manager (cash account) as Altimira Growth and Income. This even happened when I created an investment transaction manually for CI Harbour Growth and Income in the Portfolio Manager.

Disabling data matching in View | Options | Import/Export may have solved this problem.

3. DRIPS (Dividend/Income Re-investments) are Shown as Cash Deposits

DRIPs that are re-invested back into an investment are showing up as cash deposits in the Account Manager (cash account). It also looks like Market Value for each investment displayed in the Portfolio summary is just the sum of all buys and dividend/income reinvestments for that investment rather than the current price x the total number of units owned.

4. No Cash Deposits / Withdrawals / Interest Paid to Cash Account Shown the Account Manager

This makes sense since none of these cash transactions in my default MS Money cash account were included in the CSV-12 export. I’m assuming I still have to create a CSV-8 file with the MS Money Account Transactions report (excluding the Category Income : Capital Gains?) and import these transactions into the Account Manager as well. I tried doing this but the import of the CSV-8 wiped out all my investment transactions in the Portfolio Manager. Argh!

5. Java Sometimes Fails During Import

I also think I figured out what was causing my system to freeze during the CSV-12 import. If I try to create a new file (database) and don’t delete all traces of my old files, the intermittent MTH pop-up message “*** MTH My Money is not free software. *** After a 60-day trial period…” will freeze my system if it pops during the import with the Windows error messsage "Java(TM) Platform SE Binary is not responding".

6. Rounding of Prices in CSV-12 File Causes Errors in Total Costs

This is an inherent problem with the CSV-12 file created by the MS Money Investment Transactions report. All transaction prices are rounded to 2 decimal places in this report. The import ignores the total cost in the last column of the CSV-12 and instead recalculates the total cost in the MyMoney register. For example, if I bought 1000 shares of a fund at a price of $15.646 = $15,646.00 (which would be the “correct” total cost shown in the CSV-12 file). But after the import, the cost is recalculated in MyMoney as 1000 x $15.65 = $15, 650. This means that the total cost for almost every imported transaction is out by a few cents or even a few dollars, and this can add up over time if you’re monitoring the cash balance on your account or trying to calculate your average cost basis. There’s no way I’m going to go through the thousands of transactions in my portfolio and check the total cost of every single transaction.

Importing CSV-8 files might work for a simple ledger account but I don’t see how MHT can avoid using QIF files for importing investment transactions.

This is a pretty buggy piece of software, even if you consider Portfolio Manger to be in beta testing. MTH really should have done more in-house testing before releasing MyMoney 2.0.52 to the general public.

[mthadm]

We are planning to add an option to calculate either the price or the number of units based on the total in CSV-12. You would need to pick which one you would want to be adjusted during the import since we can't adjust both; we plan on adding this in a couple of releases.

Both QIF and OFX investments imports are supported and they don't have the rounding problems, this is a very specific Microsoft Money issue.

Commissions, fees and tax calculations have been changed and this will be released in 2.0.53, due out in a week or so,

Thank you,

[lmacri]

Hi mthadm:

Do you currently support QIF imports from MS Money, and if so are there any instructions for importing both QIF files (i.e., the investment account and its associated "hidden" cash account) on the wiki?

I tried importing the QIF for the "hidden" MS Money cash account and some transactions imported correctly (just the interest accrued and cash withdrawals – cash deposits were missing). I couldn't get the QIF import for the investment account to work at all.

[mthadm]

Hello,

MyMoney fully supporst both QIF and OFX investment statements import.

To our knowledge, Microsoft Money does not export its investments portion as a QIF format, hence the need to use CSV-12 which has rounding problems.

Thank you,

[lmacri]

Hi mthadm:

I use MS Money 2005 Standard Edition (v. 14.0.8.1105) and you can export QIF files in either loose or strict format (Files | Export). As I noted in my original post, I've imported my investments into trial version of of Fund Manager Personal 10.4 (from Beiley Software), MoneyDance 2010, AceMoney 3.20 and BankTree 2.0 using loose QIF files exported from MS Money and the imports worked without a hitch - cash and investment transactions balanced perfectly with no tweaking required.

The trick with MS Money is that you have to export two loose QIF files for each investment account - one for the cash transactions and one for the investment transactions. After you select Files | Export | Loose QIF and browse to the folder where you want to save the QIF files, the export wizard will ask you if you want to export an "Investment" account (which will dispaly a list of all the investment accounts tracking your buys/sells/re-invested distributions) or to export to a "Regular" account (which will display a list of the "hidden" cash accounts associated with each of your investment accounts that are used to track cash deposits/withdrawals/paid dividends). That means you have to export two QIF files (one Investment and one Regular) for every investment account created in MS Money. Each of the financial software packages I mentioned above requires you to import both QIFs to ensure that cash balances are correct but it's a seamless process and all the account/transaction reconciliation is handled in the background by the software with little user intervention.

[mthadm]

Hello,

We double-checked, and you are right, it is possible to export QIF from MS Money in either strict or loose formats. We also checked that how we handle these QIFs and in our tests there were no issues, we import them with no problems. This could be due to the fact that we run on a newer version of MyMoney that has had a month worth of bug fixes applied to it, or due to specific issues in your QIF files.

If we could ask you, would you mind taking a snapshot of some of those problem QIFs that you can't import, sanitize them, remove account numbers, etc and send it to us either as a PM or email to info@mthbuilt.com

We will make sure that whatever blocks them is fixed, if it hasn't been fixed already,


Thank you,

[lmacri]

Hi mthadm:

I tried sending a PM but couldn't figure out how to attach files. I'll send an e-mail to info@mthbuild.com instead.

[mthadm]

should be info@mthbuilt.com

Thanks