Notes on Importing 25 Years of Complex Quicken Data
Moneydance ("MD") says it can import Quicken QIF. I've learned it has issues with large, complex accounting. Note that some of what I say here also applies to importing complex Quicken ("Q") into other software, such as GnuCash.
It doesn't really matter whether bad results are MD's "fault" or the fault of the QIF format or bugs in Q. If I can get a high-fidelity import, I can use MD. If I can't get a clean import, I can't use MD. My data is way too large for extensive manual editing.
If you have a complex, multi-decade history, you're going to need to invest lots of time in the import, and you're going to need to do some programming to alter the Q data into transactions which work for MD. Here's how I went about transferring 25 years of Q data, with a dozen+ credit cards, a half-dozen bank accounts, a dozen investment accounts, IRA, 401K, etc. For 25 years I tried to capture everything in Q, and that's what I want to transfer.
There are stories on the MD forum that you should import each Q account separately. Balderdash. It makes things worse. My bet is those users had unique data issues and that they had small enough data that they could manually 'massage' it better, an account at a time.
The basic process is:
- Export ALL transactions from ALL accounts in Q to a QIF file.
- Write/run some code to massage the QIF file.
- Import the massaged QIF file into MD. This is very slow. Around 10 minutes for my 11 MB QIF file on a 4-core i7 with 16GB RAM (Macbook Pro Mid-2014).
- Be sure to let it finish. Check
top
or Task Manager and let it run if it is still burning CPU.
- Be sure to let it finish. Check
- Examine the data in MD with Q.
- When you find a recurring problem, update the code to work around it; when you find one-off transaction problems, manually edit the data in Q and start over.
- Note: When you need to start over with MD: Quit MD and delete /Users/USERNAME/Library/Containers/com.infinitekind.MoneydanceOSX.
Before you write code, it can be useful to manually edit the QIF file and manually make a few example changes which reflect what you intend to do with the code. This way you can confirm your understanding before you spend time coding.
Issues:
- MD doesn't handle ContribX or WithdrwX transactions well.
- Symtom: A ContribX shows up in MD as TWO transactions. One transfers money INTO the account and one transfers it right back out.
- Comments: These transaction types record transfers into/out of retirement accounts. They are tax-advantaged treatments of XIn and XOut. I don't use Q to do my taxes, so I don't need to deal with 'tax-advantaged.' I think the import is failing to pair up the QIF record from the sending account with the QIF record in the receiving account, and it is gets the sign confused on one of those.
- Solution: Write code to DELETE ContribX transactions.
- This is OK because the sending account and the receiving account BOTH have a record of the transaction, and the importer just uses the other side of the transaction to figure things out.
- I found this in a GnuCash mailing list:
- Problem: ContribX, WithdrwX, and Cash transactions aren't recognized by the QIF importer...
- ContribX can be treated as an Xin.
- WithdrwX can be treated as an XOut.
- Cash can be treated as a MiscInc (not MiscExp, due to a negative sign).
- Experimentation shows that Quicken refuses to change ContribX to Xin in Quicken.
- Mass replace all "NContribX" with "NXin".
- Mass replace all "NWithdrwX" with "NXout".
- Problem: ContribX, WithdrwX, and Cash transactions aren't recognized by the QIF importer...
- MD does not like transfers from an account back into the same account, for initial balances. These are sometimes used/useful in Q.
- Symptom: If your account is named Foo, MD manufacturs a FooX account and transfers the money from there. So you wind up with a bunch of FooX accounts with negative balances.
- Comments: Q uses these for initial balances. With an initial balance, money just springs into existence out of nowhere. Logically, you'd expect the result of transferring $10 from MyBank to MyBank to net to $0, but Q just manufactures $10 out of thin air. MD wants to do things more like double entry bookkeeping, so the money must come from somewhere.
- Solution: Manually update Q to transfer the initial balance from somewhere.
- If the initial balance is $0, just transfer $0 from [Cash Account]. (Create one if you don't have it.)
- If the initial balance is non-zero, record it as an income (or an expense, depending on the account).
- If that really doesn't work for some cases, categorize it as Reimbursed (which is a category I systematically exclude from reports and graphs).
- MD does not like transfers from an account back into the same account, for fees. I entered these in Q.
- Symptom:
- Comments: I used these to capture fees. When the mutual fund sold stock to cover fees, the money just vanished. By using a SellX transferring the money back into the same account, I made it vanish.
- Solution: Write code to convert SellX which is back into the same account, into a Sell with a commission equal the amount of the sale. (This assumes that ALL of my SellX are for fees. If I find one which isn't, I'll have to manually edit Quicken to change it to a Sell and an XOut.)
- MD does not handle DivX transactions well.
- Symptom: MD generated balancing reverse transactions.
- Comments: (none)
- Solution: Write code to convert DivX into two transactions: A Div and an XOut.
- Q "placeholder" transactions mess up MD.
- Symptom: This is another case where MD fabricates a FooX account for a Foo account.
- Comments: Q generates placeholder transactions when it is unable to explain a difference between your downloaded balance and your register balance in an investment account. It sees that you are missing money or shares and so it generates a placeholder to fix things.
- Solution: You have to manually remove the need for the placeholder and then delete the placeholder in Q. These can be really, really hard to sort out. I spent an entire (long) day fixing 4 placeholders.
- MD cannot handle Q WriteChk transactions.
- Symptom: MD turned this into two transactions. I think it created double the desired results, but it might have generated a reversing transaction.
- Comments: I don't have many of these. If you have lots of these, you'll need to write code.
- It turns out Q can't search for WriteChk. There's something funky about WriteChk in Q. You can (at least) sort by "Action" column and then scroll to where WriteChk should be alphabetically.
- Solution: Manually update the transactions in the Q register from WriteChk to an XOut.
- Disconnected transfers in Q get worse in MD.
- Symptom: Transactions with a date of 0/00/0000. Maybe others.
- Comments: As a database guy, I think of the transaction as being what you'd store, with each account presenting its end of the transaction. It appears that Q stores data about a transfer in both accounts. You can wind up with what looks like a transfer, but the different ends only sorta match up.
- Solution: Manually delete the old transaction in the Q register. (May have to delete both ends.) Manually re-enter the deleted transactions.
- Tip: Screenshot them before you delete them!
- Interest income in an investment account gets treated as an expense.
- Symptom: When the investment account has (positive) _IntInc transactions in Q, they wind up as negative in MD.
- Comments: Q maps many different transaction types to "NCash" records in the QIF file. Recognition as to whether they are credits or debits is contextual. MD does not recognize all contexts correctly. You can't just flip the sign on all _IntInc. e.g. _IntInc from a Bank account is correct, but from an Investment account it is reversed. In the Investment account, there is an "NCash" line in the QIF, but no such line on the Bank account.
- Solution: Write code: If the transaction has "NCash" and "L_IntInc" rows, flip the sign on the U and T lines (if they exist).
At this point I gave up. I could fully evaluate the feasibility of MD if I could run it in parallel with Q for a quarter. If I can't succeed with a high-fidelity import, I can't run in parallel.
MD is the closest to an alternative for Q that I've seen. Perhaps the project of transferring 25 years of complex, complete financial data is simply not feasible. There probably aren't enough people who started using Q in the 1990s to create a market for a high-fidelity data transfer.
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
System closed this discussion on 02 Feb, 2020 08:40 PM.