Duplicate Transactions In Moneydance on QIF Import
I'm trying to port 25 years of transactions from MS Money to Moneydance using the procedure described in the knowledge base.
Many of my transactions in MS Money are transfers from one account to another, for example a monthly payment from a checking account to a credit card account.
When I import the QIF for the checking account into Moneydance, everything looks fine. But then I import the QIF for the credit card account, and every payment transaction shows up duplicated in the Moneydance checking account.
The knowledge base troubleshooting tip for this problem suggests that it might be caused by having different dates in the two accounts involved in the same transaction. I've checked, the dates are the same in my checking and credit card accounts.
I suppose I could find and delete all the duplicates manually, but that's a lot of work for 25 years of data.
Is there a better way?
I've attached a screen shot highlighting some of the duplicates.
Screenshot_2024-01-17_at_11.23.50 PM.png
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
1 Posted by dwg on 18 Jan, 2024 07:26 AM
Are the transactions also duplicated in the credit card account?
2 Posted by coatley on 18 Jan, 2024 02:15 PM
The short answer is No.
But as I try to recreate the problem, it seems more complex than just duplicate transactions.
I've started over from a backup with all accounts defined, but no transactions imported.
Now I've done just one import: the QIF for a checking account. The transactions in the checking account look correct. The import also created many transfer transactions in other accounts, but it appears that updating the other accounts during the import was hit or miss. Some accounts that should have recorded transfer transactions from the checking account show no transactions at all.
I wonder if this indicates that Moneydance doesn't handle large QIF imports correctly? The imported QIF contains about 7,000 transactions. It doesn't seem like a huge quantity, but maybe it is?
I think I need to solve this problem before going any further.
Support Staff 3 Posted by Maddy on 18 Jan, 2024 03:06 PM
You may want to try importing your data one more time by following the steps below:
In order to import your data from Microsoft Money, you have to export one QIF file per account. (you cannot export all data in a single QIF).
You need to use MS Money to export your data in a QIF file type, one QIF file for each account, including any closed accounts that have transfers to any open accounts, re-open these accounts, if necessary.
Open one of the QIF files in a text editor.
You are looking for the date format used in the file e.g. D25/03'2019 - Day Month Year or D5/ 17'19 or D05/31/2017- Month Day Year etc.
You will need this information later.
If you get this wrong, it will be apparent as the dates in Moneydance will be all over the place and you will need to start again creating a new data set.
QIF files were never meant for transferring data from one program to another, but it’s all that is available, you will need to spend time cleaning things up when the import process is complete.
When you first start Moneydance, it opens in the Welcome window.
Click on “Create a new account set”. If you have opened Moneydance before, create a new data set under File (Moneydance On Mac)> New.
Select “Create New File” and click Next. Type in an Account Set Name (Data Set) and click Next.
Change Primary Currency (base currency), if required.
File Type, leave at “Standard Account set”, click Finish.
In the side bar (list on left) under Bank, delete the two default Bank accounts, highlighting them in turn and using Account--> Delete Account.
Select one of your QIF account files, the one with the most transfers to other accounts.
1. If in step 3 you don’t see the name of the account you are importing click Cancel and setup the account. Account--> New Account - select account type – Next – Enter the name of the account exactly as it appears in your old program under “Account Name” – OK.
2. Drag and drop the QIF file into the side bar (list on left).
3. Waited for import window to appear, this can take a few minutes.
IMPORTANT: Check all listed accounts are of the correct type and currency, and apply changes, if required.
4. Find the name of the account you are importing in the list and highlight it.
5. The “Date Format” should be set to the date format used in the QIF file, not the date format you want to use in Moneydance.
6. Select the correct Decimal Character. Do not tick “File was downloaded”.
7. Click import and wait for account values to appear. Again this can take some time!.
Accounts that have transfers within the QIF file you imported will be automatically created. Don’t worry it their balances are incorrect, as you import the other QIF account files things will sort themselves out.
Some accounts may be hidden under the “+” at the bottom of the side bar, added them back.
Repeat the process steps 1 to 5 for each QIF account file.
Some QIF files don’t include initial account balances. If you think this is the case, you can add the "Initial balance" to affected accounts, as follows.
Highlight an account, then click Account--> Edit Account and add an Initial Balance.
For investment accounts,QIF file don’t always carry security prices other than buy/sell prices, so investment account balances may be wrong until you update with current security prices.
- Reminders are not included in QIF files. Any accounts that are inactive (closed) should be made "inactive" by highlighting the account in the side bar and clicking Account--> Edit Account and ticking the inactive box. To remove them from the side bar right click and select Remove from Side Bar.
Finally , you would need to check the balances and tidy things up a little.
Please keep us posted on your progress, Thank you!
--
Maddy, Infinite Kind Support
4 Posted by coatley on 18 Jan, 2024 07:09 PM
Thanks for the quick reply. Some of the steps you suggest are different from what I found here: https://infinitekind.tenderapp.com/kb/importing-data-from-other-programs/importing-from-microsoft-money
Specifically, letting Moneydance create the "transfer-to" and "transfer-from accounts", rather than creating all accounts in Moneydance before importing any QIF files. I'll give this a try.
5 Posted by coatley on 18 Jan, 2024 09:25 PM
I've made three attempts, with inconsistent results, each wrong in different ways.
I suspect there may be some corruption in my MS Money data file, although it runs with no errors on my PC. I'll try again after running the Money file repair tool.
I also suspect that I might avoid some Moneydance problems if I don't try to import my closed accounts. I'd like to keep all my accounts in an integrated database, but I don't really need that for closed accounts.
6 Posted by dwg on 18 Jan, 2024 09:46 PM
You need closed accounts if there are transfers to/from them from still open accounts. Moneydance will create any accounts that it needs to handle the data if there are not there.
It is quite odd that you are only seeing duplicate in one account. When you import the Cheque account, any transfer to the credit card account would be created in both accounts, then when you do the credit card account I would expect likewise if the software is creating duplicates. In most instances the software is smart enough to detect this and not create any duplicates. It suggests that the transactions are not quite identical.
7 Posted by coatley on 19 Jan, 2024 12:04 AM
I might be making a little progress. I've now successfully imported the checking account and two credit card accounts with no apparent errors. Each time an import looks correct, I'm exporting a backup so I can start again from that point if I see another error.
At this point, I think most of the errors I've seen may be due to operator error. I'll keep going.
8 Posted by coatley on 19 Jan, 2024 12:30 AM
I seem to be making good progress. So far I've imported 5 bank accounts and 7 credit card accounts, and things look OK, except that Moneydance has created two accounts that don't make sense:
1. A bank account with the same name as a real bank account with the letter x appended to the account name. There are no transactions in this account.
2. A bank account with the same name a real credit card account. This account has only one transaction: an Opening Balance transaction.
I think I can probably delete both of these stray accounts, but I'm not ready to try that yet.
9 Posted by dwg on 19 Jan, 2024 02:55 AM
Migrations are never smooth, none of the developers ever intend for you to change programs so make no provision.
Some comments on your two accounts
1. The account with the appended X we often see, especially with data from Quicken. It is because of what can only be called dodgy transactions usually a transfer to/from the account when that same account is the source/destination of the funds, it is a type of circular reference, that has no place in software that follows accounting rules, hence Moneydance has to do something that makes it fir into the rules.
2. QIF was never designed for migrations, it is a support tool. It is somewhat ambiguous so some interpretation is necessary, while Moneydance gets it right more often than not, there is plenty of scope to get it wrong and there are times when it will not get it right.
If there are no transactions it is pretty safe to delete, if there are you need to identify if they should be elsewhere.
10 Posted by dtd on 19 Jan, 2024 04:51 AM
You do seem to be making progress. As dwg said, migrations with QIF can have issues, but it is all that (any program) has to convert the data.
As for # of transactions, you seem to be past that, but I personally had to convert (from Quicken, not Money) 35+ years of data. It was also definitely not easy, but the end result has been very good for 3 years now.
Oh, and yes, you probably need all your closed accounts moved over. You can rename them as inactive when it's all done so something like Checking Account 1990-1999 doesn't haunt you forever, but it is moved.
11 Posted by coatley on 21 Jan, 2024 05:30 AM
I've made a lot of progress, successfully importing all my open and closed bank accounts and credit card accounts.
I haven't had any success importing investment accounts, open or closed. I think I will give up on importing their MS Money history, and start fresh by typing in their current balances manually.
Before I do this, I think I will need to manually force to a zero balance each of the investment accounts that Moneydance created automatically when I imported my bank account QIF files. A single transaction in each investment account should offset its current balance. Then I can mark those zero-balance accounts as inactive, and put them out of sight.
Does this sound reasonable?
System closed this discussion on 21 Apr, 2024 05:40 AM.