Import bug and workaround for Quicken files: Reconcile Previous Balance incorrect

davidamis's Avatar

davidamis

18 Mar, 2021 04:49 PM

This bug occurs when importing quicken QIF files into Moneydance and two conditions are met.
1. There are transfers between two accounts, one regularly reconciled and the other not.
2. The name of the reconciled account comes alphabetically after the name of the other account.

The result for the user is that after importing the QIF file, the first time the user tries to reconcile the previous balance is off.

The following is my experience and a workaround.

In Quicken I had a checking and a Paypal account. All of the Paypal transactions were debited from the checking account, so you wouldn't need a separate account, except that when you do a credit or return, the money may stay in your Paypal account, so I created a separate Paypal account. Almost every Paypal purchase had a transaction in the Paypal account and a transfer from the checking account. I always reconciled the checking account, but never the Paypal account. So in the QIF file that I exported from Quicken, all of the transfers on the checking account side are marked as reconciled, e.g.:

Example of a transfer transaction in TCB checking:

D5/1/20
PPaypal          Inst Xfer
MElectronic/ACH Debit
T-41.36
A
A
A
A
A
A
CX
L[PayPal]
^
Note the line "CX", which means reconciled. "C*" means cleared but not reconciled. But the same transfer in the Paypal account was not marked as reconciled, there is no "C" line:

The same transfer transaction in the PayPal account:

D5/1/20
PPaypal          Inst Xfer
MElectronic/ACH Debit
T41.36
A
A
A
A
A
A
L[TCB checking]
^
When Moneydance imported the QIF, none of the transfers were marked as cleared in the checking account, even though they were marked as reconciled or cleared in the QIF file.

The imported account balances were all correct, but when I tried to reconcile the checking account the previous balance was off by an amount equal to all of those Paypal transfers, nearly a thousand transactions. After reading posts on the Infinitekind forum, I focused on the cleared state of the transactions and found all the transfers uncleared. My fist thought was to use the Find/Replace extension, which was very helpful in finding the problem, to fix it, but alas the one thing it can't replace is the cleared/uncleared/reconciling state.

Modifying the Find/Replace extension (the source code is available) would have taken a lot of time, and while writing Python or Java code is an option, I'd be starting from scratch. Luckily, I haven't entered many new transactions, so I could solve the problem by modifying the QIF file and re-import into Moneydance.

After a lot of experimentation, I discovered a simple solution. It appears the MoneyDance QIF importer gets the cleared state from the first side of a transfer transaction it imports. In my Quicken-generated QIF file, the Paypal account came before the TCB checking account (the accounts are exported alphabetically). By reordering the accounts so that TCB checking came before Paypal in the file, the cleared state of all of the transfer transactions came from the transactions in the TCB checking part of the QIF file.

Here is what I did. You may need to do something similar, so perhaps someone else will find it useful. To make the change, I moved the Paypal account to the end of the list of accounts. There is a list of accounts at the beginning of the QIF file, and later all of the transactions follow a header for the account. To reorder:

The account list is at the beginning of the file. Find "Paypal" and select the entry for the Paypal account:

NPayPal
D
X
TCash
B51.63
^
Find the end of the list by searching for "!Clear:AutoSwitch" and move the Paypal entry just before it.

Find the beginning of the Paypal transaction list: Find "\n!Account\nNPayPal" and note the line number in your text editor. Find the next account: Find "\n!Account". Select all the lines (header + all transactions) starting with "!Account" for Paypal and ending with the line before the next "!Account". Move this text to the end of the QIF file.

Save and import into Moneydance.

  1. 1 Posted by tgilbert666 on 19 Mar, 2021 02:19 AM

    tgilbert666's Avatar

    Excellent!! Great tip - thankyou.

    I believe there have been changes to the importer in recent versions/builds. I'm not sure whether that will matter, but for posterity perhaps you could clarify which absolute version and build of MD you were working with?

    Cheers,
    Tony (a fellow user)

  2. 2 Posted by davidamis on 19 Mar, 2021 01:41 PM

    davidamis's Avatar

    Tony,
    Yes, i hadn't gathered the the version numbers for this bug report, so here they are:
    Primary testing was with the current version of Moneydance: 2021 (2006)

    The importer is broken in the current version, and produces the following when attempting to import:
    Error reading file: java.lang.NullPointerException
    This bug is a known problem.

    Initially I regressed to the previous version (just for import): 2020.2 (1929)
    For subsequent testing I used (again, just for importing on another machine) non-release build 2021.1 (3036).

    The QIF file was from Quicken for Mac 2007.

    The Find and Replace extension was Build 1215.

    Machines running MacOS 10.11.6

    While researching this bug, I found two bugs in the Find and Replace extension. One is that double-clicking a search result that is a transfer opens the wrong side of the transaction. The other is that after making a change to the cleared state on the wrong side, a subsequent find gave erroneous results. It would take some time to create a proper bug report on these, I mention it because it's disheartening to run into 4 bugs while trying to become a new user of Moneydance. I think financial software should be very reliable. Although Quicken 2007 was quite out-of-date, it's still very reliable software, which is why I was still using it up til now. I hoped Moneydance would be as just as bug-free.

    Thanks,
    David

  3. System closed this discussion on 18 Jun, 2021 01:50 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