Incorrect import of Quicken 2007 split transfers

mschmitt's Avatar

mschmitt

01 Nov, 2020 11:32 PM

I'm evaluating MD 2021 as a replacement for Quicken 2007. MD 2021 still has a problem that I first reported in 2017, and again in 2019, which is throwing all of my accounts off.

The situation is when a split transaction contains more than one line-item transfer to the same account.

In Quicken 2007 it consolidates the line items on the other side of the transfer. The MD import creates three entries on the other side: the consolidated entry, plus one entry per line item. The result is that it doubles up the transfer value.

Competing products can import this correctly, why can't it be fixed in Moneydance?

  1. 1 Posted by tgilbert666 on 02 Nov, 2020 12:13 AM

    tgilbert666's Avatar

    I'm also holding off importing my complete history due to this issue so

    +1 please.

  2. 2 Posted by dwg on 02 Nov, 2020 12:27 AM

    dwg's Avatar

    I'm a fellow user.

    I believe I understand what is happening here.

    Moneydance is built upon the principles of Double Entry Accounting. At the core of Double Entry is the rule that for every debit there must be a corresponding and equal credit.

    There is no provision in Accounting for aggregating entries on one side of a transaction. So I think you are seeing how double entry works with such transactions. You have individual splits on one side hence individual transactions for all intent. Moneydance replicates this on the other side (in the other account) to satisfy accounting rules. you then have what you know is a consolidated transaction. All Moneydance can see is there is not a corresponding transaction in the other account hence creates it.

    Having corresponding and equal transactions are an absolute rule in Accounting

  3. 3 Posted by dtd on 02 Nov, 2020 03:42 AM

    dtd's Avatar

    a user here
    As dwg stated, MD relies on accounting principles.

    To try to state it more simply (imo), Quicken allows the creation and destruction of money when one uses an account to apply all cash (or some cash) to its own account.

    MD doesn't allow that. I find that usually MD creates a new ACCOUNTX account to hold those funds, but maybe in a split transaction, that's not what happens.

    Still, dwg is correct - Quicken allows money creation/destruction, whereas MD insists on putting that money "somewhere" - which can be confusing, even if it is Quicken that is not following proper accounting principles.

  4. 4 Posted by mschmitt on 03 Nov, 2020 01:04 AM

    mschmitt's Avatar

    @dtd: I know what you mean about Quicken allowing using the own account as a source of money. In no transaction am I doing that, except for the opening balance transaction, which Moneydance doesn't have a problem with.

    @dwg: The issue here is not what Moneydance allows or does not allow. The problem here is that it is unable to import the QIF and convert the transactions correctly into what it does support.

    What we have here is a transaction matching problem. On one side of the transfer, the QIF contains two split details for the same account. On the other side of the transfer, the QIF contains a single entry for the aggregate amount. This balances perfectly, if you match up the two sides of the transfer.

    Moneydance has three options for how to handle this, depending on what it supports:

    1. It could import as in Quicken: separate line items on the split side, aggregate on the other side.

    2. It could combine the two split details into an aggregate on the split side, so that they then will match to the aggregate entry on the other side. (The disadvantage would be if the two line items have different memo values, you'd lose the distinction.)

    3. It determine that the aggregate on the other side is a match for the two line items, and replace the aggregate entry with two separate transactions.

    What it should not do is import what is in the QIF and then create extra transactions on both sides. That is just wrong.

    Note: I have a reduced test case that demonstrates this issue, with a small QIF file, if you want to see exactly what is happening. I can upload it.

  5. 5 Posted by dtd on 03 Nov, 2020 01:25 AM

    dtd's Avatar

    You should upload it. That way some may test it (sorry, I am in the middle of a few things, but there are many here who will check it out)

    Here's my interpretation of what you said, so if I don't understand, you can clarify.

    You have a transaction, and it is a split. You have a memo, and a category, but the value goes into an account, so it is a transfer. You have another memo, and a category, and that value goes into the same account.

    Could you give an example of this (other than the QIF file) where this makes more than just theoretical sense?

    Thanks!

  6. 6 Posted by mschmitt on 03 Nov, 2020 05:46 AM

    mschmitt's Avatar

    I'd have to hunt down all the cases where I have this kind of double-transfer to see why, but I can assure you I have a good reason in each case.

    In the particular transaction I was looking at this time, it was because I had paid for a purchase using three sources of money: cash, plus 2 gift cards. The transaction was entered in the cash account. The transfers were from an account that held the gift card balances. It was two transfers to show how much was paid from each of the two gift cards.

    Another example would be a paycheck, with contributions for Roth IRA plus Roth IRA Catch-up. They are two separate line items because that's how they are on the paycheck, but they are transferring out to the same Roth IRA account.

  7. 7 Posted by mschmitt on 03 Nov, 2020 05:55 AM

    mschmitt's Avatar

    Attached is a recreation of both import problems in a reduced test case.

    The zip file contains:

    • A small Quicken 2007 data file
    • The exported QIF of that file
    • The Moneydance 2021 data file that results when you import this QIF
    • A screen shot of the Quicken 2007 accounts

    The Quicken file contains three accounts:

    • Cash: This account has 1 transaction
    • Checking: This account has 3 transactions
    • Brokerage: This account has 1 transaction

    On a successful import, all three accounts would have a $0 balance. The Market value in the Brokerage account would be $100.01.

    The actual results are that the Cash account is $38.65, the Checking account is -138.65, the Brokerage account has a $100 cash balance.

    The import shows the following problems:

    1. It created an empty account “Unknown: PAYEE” for no apparent reason.

    2. The Cash account created an extra transaction depositing $38.65.

    3. The Checking account double-posted the 7/2 transactions, and created an extra BuyX transaction on 7/14. Also note that the BuyX transaction has a blank description.

    4. The Brokerage account created an extra transaction. Which transaction is the extra one depends what you think it should be doing here.

    The trick to creating the Brokerage problem is this:

    1. Start with a Checking transaction that is a split.

    2. One of the split line items transfers $ to a “Mutual Fund” account. This creates a special situation: A BUY transaction that is transferring money from a split line item. This is the only way to create this situation; it’s not possible when transferring money to a Brokerage account.
      That might be enough to create the Moneydance issue, but in this example I then:

    3. Convert the Mutual Fund account into a Brokerage account, by:
      3a. Create a brokerage account
      3b. In the Quicken Portfolio, drag the security from the Mutual Fund account to the Brokerage account.

  8. 8 Posted by mschmitt on 03 Nov, 2020 05:57 AM

    mschmitt's Avatar

    Hmm, lost the attachment, let's try again...

  9. 9 Posted by dwg on 04 Nov, 2020 05:31 AM

    dwg's Avatar

    I'm a fellow user.

    I have examined you gift card split transaction.

    In the split your have two transactions:

    1. transfer to Cheque A/C 17.43

    2. Transfer to Cheque A/c 21.22

    For a total of 38.65

    Quicken has the ability to take these two transactions and to post a single transaction for the total amount to the Cheque A/C which is what has happened.

    From a Quicken perspective this is just fine.

    For any program that is based on accounting principles this is a totally invalid thing to do. You just do not do this in accounting.

    When receiving this data Moneydance needs to resolve the problem.

    There is no linkage between the 2 split transactions and the aggregate transaction in the QIF data, there is no way in QIF of having anything that would indicate there is a relationship. In Quicken there is no linkage either, the Data Entry Wizzard is what created the aggregate transaction. The underlying Quicken data structures do not following accounting principles.

    The decision Moneydance has to make is how to make the transactions comply with the rules. The data coming from another program is deemed to have been reconciled and is correct as it is, there is no sense that this data should be altered in any way, and in any case Moneydance would not know how to change it, the software does not have the underlying knowledge the user does on the data.

    The software is left with the option of adding in the transaction that the existing transaction would indicate is required.

    An entry in one register for 38.65 that is a transfer from another account where there is no corresponding entry points to a need to have a transactions in that account for this amount.

    Likewise with the transactions of 17.43 & 21.22. They do not exist in the other account so are created.

    A basic principle of double entry accounting that for every debit transaction there must be a corresponding and equal credit transaction.

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

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