Importing Quicken QIF doesn't work right - accounts/categories mixed up, balances incorrect

EboMike's Avatar

EboMike

10 Jun, 2010 03:57 AM

There are lots of topics about problems with importing QIF data, but I haven't seen any mention of this particular problem:

I tried the 740 beta as well as 751, and the import finished in record time, but it seems like it got confused at some point.

Account "A" seemed fine (with "Category/Tags" showing the category as it was in Quicken), but then Account "B" was a pretty mixed up, and all of its category tags were actually account names - most of them that of Account A, which had nothing to do with Account B. Not to mention that it claimed I had $-302,000 in my checking account. Same thing for all subsequent accounts - they all had account names for categories (mostly Account A, but sometimes others as well), they never showed the category as it had been in Quicken.

In fact, Account A shows most of my transactions across the board - all other accounts mostly show transfers inbetween accounts.

I had exported all my accounts with Quicken '07 as a single QIF file on Windows and followed the instructions on how to import multiple accounts with a single QIF. (I ran MD on Mac, I haven't tried the Linux version yet, I'll do that next).

I really want to purchase MD, but obviously I need to get this issue resolved first since I can't lose 8 years worth of data.

-Mike

  1. 1 Posted by EboMike on 10 Jun, 2010 06:39 AM

    EboMike's Avatar

    Okay, I realized that I didn't set "Import To:" to "New Account" during the second import, and things are better, but still incorrect.

    Transfers across accounts with different currencies are broken - they show up as two transactions, one with each value: If I transfer EUR 100 from account A to account B, it shows up as a transfer of USD 100 from A to B, and then as a transfer of USD 120 (EUR 100 converted to USD at that time) from A to B again.

    So in the end, I still have two accounts with absurdly low/high balances on them.

  2. 2 Posted by Ben Spencer on 15 Jun, 2010 03:38 PM

    Ben Spencer's Avatar

    Hi

    If you export each of your accounts in Quicken their own QIF file and then import those QIF files on at a time into Moneydance you are going to get duplicate transaction where ever there were transfers between those accounts. Instead when you are exporting your data from quicken select the option and export all accounts to a single QIF file. Then when you import this QIF file Moneydance will be able to identify the duplicates and remove them. For more information on import QIF files from quicken please read the following article.

    http://help.infinitekind.com/faqs/importing-from-quicken-or-ms-mone...

    Sincerely
    Ben Spencer

  3. 3 Posted by EboMike on 15 Jun, 2010 04:18 PM

    EboMike's Avatar

    Ben, thanks for your answer.

    As I mentioned in the OP, I used a single QIF file. Also, as I mentioned in the second post, I only get duplicates when the transaction is across accounts with two different currencies, i.e. when the transaction involves two different values.

    -Mike

  4. 4 Posted by Ben Spencer on 23 Jun, 2010 05:59 PM

    Ben Spencer's Avatar

    I am sorri I didn't get back to your sooner. I have acouple of follow up questions. Can you confirm that you imported your QIF file in the two step process. First with the "account information only" check box checked and then without? And can you confirm that after you did the first import that you went to each of your accounts in Moneydance and verified that the currency for the accounts were set correctly by selecting Account->Edit Account?

    Ben

  5. 5 Posted by EboMike on 24 Jun, 2010 03:46 AM

    EboMike's Avatar

    Hello Ben, thanks for your reply. Yes, those are the steps I did, including making sure that the currency was set up correctly in each account.

    I just tried it again and got the same result. One additional thing I noticed is the first one of each of those twin transactions has a value under the "Payment Rate" column - something like 0.6785.

    -Mike

  6. 6 Posted by Ben Spencer on 05 Jul, 2010 06:11 PM

    Ben Spencer's Avatar

    Hi Mike

    I spoke with the developers about this issue and I am afraid I have bad news. The QIF file format does not include the exchange rate used on a transfer. The only thing you can do is manually enter the exchange rate. You will also have duplicates in Moneydance you will have to manually delete the duplicates as well.

    I realize this is a huge amount of clean up work and I am afraid that due to the imitations of the QIF file format that isn't anything we can do about this automatically.

    I am sorry for the inconvenience.

    Sincerely
    Ben Specner
    Moneydance Support

  7. 7 Posted by EboMike on 07 Jul, 2010 04:02 AM

    EboMike's Avatar

    Ben,

    that is not a very good response. The QIF file is pretty fragile and limited, but the information is definitely available.

    First of all, Moneydance is aware that the two accounts involved have different currencies (you explicitly ask the user to provide that information inbetween the two QIF imports).

    Secondly, each transfer results in two entries in the QIF file - one for each account. Both entries have the exact same date and description, so it'd be trivial to link them together assuming there is only one transfer with the same description on that day (if there are several, you could either assume the QIF file stored them in the same user, or prompt the user. But for personal banking, it's unlikely to happen in the first place).

    As an example - let's say I transfer EUR 9000 (resulting in $8803) from Account A to Account B, I will see this under Account A:

    D3/25' 2
    U8,803.00
    T8,803.00
    PWire Transfer
    L[Account B]
    ^

    And this under Account B:

    D3/25' 2
    U-9,000.00
    T-9,000.00
    PWire Transfer
    L[Account A]
    ^

    Now Moneydance knows Account A is dealing with USD and Account B is dealing with EUR. In fact, it wouldn't even matter. It is clear from this information that 9000 units (of whatever currency) were taken from acount B and 8803 were added to account A. However, Moneydance messes that information up and shows this information as two discrete transactions, both from account to B to account A, one for $9000, one for $8803.

    Since there is no matching transaction with the same amount (which Moneydance seems to be looking for), and since both accounts use different currencies, Moneydance should be trying to match the two transactions by date/description only.

  8. 8 Posted by Ben Spencer on 07 Jul, 2010 07:19 PM

    Ben Spencer's Avatar

    I agree it would be possible from the information provided in your example to infer that the two transactions are infact the two sides of the same transaction. Moneydance currently will automatically remove duplicates imported if the amount is the same. Since the amount is not the same with foreign currency transfers they are not picked up by the duplicate removal algorithm. The trouble is that because the amounts are different there is no certainty that the transactions are related. For example what should the correct behaviour be if there were two transfers between accounts A and B on the same day? Moneydance could not determine which was the related transaction.

    Sincerely
    Ben Spencer

  9. 9 Posted by EboMike on 07 Jul, 2010 08:05 PM

    EboMike's Avatar

    Ben,

    I appreciate the quick response! As you pointed out, the QIF file is somewhat inadequate and allows for ambiguity. However, if there is a transfer between accounts with different currencies (which Moneydance is aware of), then it should be assumed that you have two transfers with different amounts which can then be matched up.

    If in doubt, you could add a checkbox to the import dialog (which already has several options) and make the "fishing" for matching transfers optional.

    However, in real life, I would argue that at least 95% of all cases with transfers across accounts with different currencies would be properly fixed with that algorithm.

    As it stands, Moneydance is not usable for me since I have two accounts with completely incorrect balances due to hundreds of incorrect transactions, and the "transfer matching mode" should be relatively trivial to implement and would solve this problem.

    I might write a simple script to fix up the QIF file, but having this built into Moneydance would be much better, and I would assume that it would benefit at least some of MD's customers.

    Thanks,
    -Mike

  10. 10 Posted by Brian Adler on 26 Jul, 2010 02:52 PM

    Brian Adler's Avatar

    Mike,

    I have created a suggestion ticket in lighthouse (the system we use to track suggestion requests and bug reports) reflecting your desire that multi currency transfer imports from QIF be improved/fixed.

    Sincerely,

    Brian Adler

    Moneydance Support

  11. Brian Adler closed this discussion on 26 Jul, 2010 02:52 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac

Recent Discussions

27 Jan, 2021 10:58 AM
27 Jan, 2021 10:37 AM
27 Jan, 2021 10:09 AM
27 Jan, 2021 09:02 AM
27 Jan, 2021 06:55 AM