How to 'merge' two Quicken files?

Quicken Kathryn

How would you merge two Quicken files?

Quicken Kathryn

Hello All,
Here is a post originally added by jepollard:
--------------------------------

Merging Quicken Files

Want to merge your Quicken files? There is more than one reason to to merge Quicken files, let's use marriage for an example.

For the following assume :

File 1 = Husband; File 2 = Wife; File 3 = New merged file "Family"

1) Backup both of your data files ( File 1 and File 2 )

2) Make sure both your files have IDENTICAL Category lists

a. Do this!! It may take a while, but is well worth the investment

b. Tools > Category List (print both from File 1 and File 2)

or

Finance > Category & Transfer List

 i. Determine one list and then start making them the same by using the EDIT > FIND/Replace Function

 ii. Replace old category with the new category for each item in BOTH files.

c. If this is a Marriage merge, be sure to change your spouse's Tax categories at this time. For example, decide who is filing your tax return and the primary income holder and set up accordingly:

File 1(HUSBAND) could be SALARY: Base Pay w/ tax id as

W2:salary or wages

File 2(WIFE) change to SALARY SPOUSE: Base Pay w/ tax id as

W2:salary or wages spouse

** You don't have to do this for all previous years. Just this year for which you will be filling together.

3) If you wish to retain your Memorized transactions after the merge, visually scan the Memorized Transaction list in each file for any you will *not* want after the merge ... and delete them.

4) Backup both of your data files ( File 1 and File 2 )

5) Rename each account in File 1 and File 2 to be unique.

That is, absolutely NO account in File 1 should have the same name as any account in File 2.

Example:
"Husband - Checking"
"Husband - Savings"
"Husband - Schwab"
"Wife - Checking"
"Wife - Savings"
"Wife - E-Trade"

a. Use the EDIT Account option to rename your accounts

b. You will see how this pays off later: Once the merge has completed, you will be able to transfer funds to whatever accounts you plan on keeping around.

6) Backup both of your data files ( File 1 and File 2 )

FILE > BACKUP

7) Now, pick the File that will serve as the basis of the new Merged file. In this case we will pick File 1 - HUSBAND.

Make a copy of this file and call it the name of the new Merged quicken file. In this example, FAMILY

a. Use the FILE > FILE OPERATIONS > COPY utility

b. After the copy, check to see if all values are correct. They should be.

c. Export the Memorized Transaction List from the new FAMILY file

(which at this point is really just the husband's memorized trans)

FILE > EXPORT > QIF and check "Memorized Transaction List"

Call the output "File 1(HUSBAND) - memtrans.qif" for example.

8) Optional: if you want to retain the prices for securities for which Quicken can not download the price.

In the file you want to merge, FILE 2 -WIFE in this example, assign ticker symbols to things such as corporate and municipal bonds etc. The ticker symbols can be anything you choose (CUSIP number for example, or a totally fictional combination of letters and numbers); but they must be unique within FILE 1 *and* FILE 2.

9) Now take the file that you want to merge, FILE 2 - WIFE and export the following lists and accounts as follows. It is EXTREMELY important to do this in the manner specified. Otherwise the data will get mixed up upon the actual importing.

a. 1st : Export the Account, Category, and Security Lists

 i. Use the FILE > EXPORT > QIF utility and select all 3 lists.

De-select anything else AND make sure you select ALL accounts under the "Quicken Account to export From" box.

 ii. Make sure you also select a location to export to and give it a good name like "File 2(WIFE) - all lists.qif".

b. 2nd: Export the Memorized Transaction List to a separate file.

FILE > EXPORT > QIF > Memorized Transactions

Call the file "File 2(WIFE) - memtrans.qif" for example.

c. 3rd : Export only the Security List

 i. Follow the same instructions but only export Securities. This will become very important later if you want to keep ALL historical securities prices and import them into your new MERGED file.

 ii. Again, make sure you select All accounts and a unique name to store it under : File 2(WIFE) - SecurityList.qif

d. 4th: Exporting of individual accounts

 i. Export each account separately to it's own file. This is very important; combining them produces a mess.

 ii. Use the FILE > EXPORT > QIF utility again and select "Transactions" only.

 iii. Select the account to be exported and the unique file name to save it under: Example:

WIFE-Checking exported to "Export1-wife-checking.qif"


 * DO THIS FOR ALL ACCOUNTS *

10) Now - this is optional but I think extremely important if you want to retain YOUR historical pricing information after the merge. The Securities file that gets exported from Quicken has several major "bugs" in it. We have to massage the file a bit to get all the securities prices and format correct. This is a pain, but again, well worth the effort.

a. Note - you can skip this and just use the "download historical stock prices" option quicken offers after the merge is complete. This works fine and will save you lots of time. However, you will lose all of your historical securities prices which Quicken is not capable of downloading: bonds for example and prices for days that are not part of Quicken's formula for choosing which prices to download. It's up to you. The result will be the same from an account value perspective since the shares will all transfer appropriately.

b. To prepare the securities prices to be imported into Quicken as a "Prices" file we have to make it look like a prices file. So, edit the QIF file by changing the .qif extension to .doc and bring the file up in MS WORD.

 i. Delete all the mess at the beginning of the file until you get to the securities prices section. This is information that will get imported separately and is redundant.

 ii. Get rid of lines that look like this: "!Type:Prices"

- Use the Find and Replace function to replace "!Type:Prices" with a blank space

 iii. All of the years beyond 1999 are messed up in the file. To change each year use the Find and Replace feature of WORD to fix each year.

Ex. ' 00 to /00 will give the correct format.

' 01 to /01

 iv. The last entry for EVERY security is incorrect and will have to be deleted. So, page through the file to get to the last entry and delete it. (And if you have more than one security with the same name, this invalid entry will occur more often than just at the end of a group of prices with the same ticker symbol; delete it where ever you find it).

 v. Lastly - get rid of all lines that just have the "^" character in it just as you did for the "!Type:Prices" lines.

You should now be ready for the Actual Merge

11) Go to the new MERGE File ( File 3 - FAMILY )

12) You have to delete all Memorized transactions in the new file or the imports will get the accounts mixed up.

a. CASH FLOW > Memorized Transaction lists (or Banking > Memorized Transactions) and delete them ALL.

13) Import the Account, Categories, Securities file we exported from File 2 (WIFE).

a. Select FILE->Import->QIF

b. Select the filename, deselect everything except Accounts, Categories, Securities

c. Make sure you select ALL Accounts under the where to import to box.

You should now see all your new accounts. The File 1 files values should not have been altered and the new File 2 accounts should all be zero.

Ex:
Husband - Checking
Husband - Savings
Husband - Schwab
Wife - Checking
Wife - Savings
Wife - E-Trade

14) Now it's time to import and accept each account separately. This is very important to follow. The account values will not be correct until the last account is imported so don't worry just yet. 
[EDIT. If your Quicken version is later than Q2004, Ignore the Quicken message that says you can not import QIF files.  If you elect to import "All Accounts" (as suggested here), you can import the QIF file with no modification. If you have decided to try to import one account at a time (a process not described in this FAQ): see this community faq: http://getsatisfaction.com/quickencommunity/topics/faq-how-to-import-qif-files-into-non-cash-accounts-post-q2004...]
a. Select File > Import > Qif and select the file to import. Be sure to select its new corresponding account to import to.

DO NOT select ALL Accounts. ONLY select "transactions" & "special handling for transfers". Everything else must be de-selected.

b. For versions of Quicken newer than Q2001, after the import, you must "accept" the transactions before moving onto the next file to import. To do this, go into the register of the actual account. You will see a selection to accept all downloaded transactions.

Repeat this until you are done. Some of the accounts will not have anything to accept and that is ok. Something to do with the types of accounts.

15) At this point, the accounts without securities in them (Checking, Savings, Credit Cards, etc) should all have the correct values.

16) Import the two files containing the Memorized Transactions from both original files.

FILE > IMPORT > QIF (FILE 1(HUSBAND) - memtrans.qif

and (FILE 2(WIFE) - memtrans.qif

17) The only thing left to do is import the historical Securities prices file we created.

a. Select Portfolio View

b. Select Import prices and select the file we saved

c. After the import all accounts should reflect their original values.

Author: Chris Tomaszewski

Minor Enhancements: John Pollard

Unknown

Question 1:  In below STEP...I am unable to select my 401K Account (newly created from previous step) due to Quicken 2009 limitation on QIF IMPPORT on 401K Accounts......Any suggestions?

I also tried by created a dummy cash account and tried to import.......but when I click on NEXT button nothing happens.


========================================================
14) Now it's time to import and accept each account separately. This is very important to follow. The account values will not be correct until the last account is imported so don't worry just yet.

a. Select File > Import > Qif and select the file to import. Be sure to select its new corresponding account to import to.
=======================================================

Question 2:
10.iv:  Can you please given an exmple line/s we need to delete with sample data?  

Question 3:
10.ii:  Get rid of lines that look like this: "!Type:Prices"....I DON'T FIND ANY IN MY 401K ACCOUNT DOWNLOADED FILE FROM 2009 VERSION? ANY REASONS?

Howard Roark

"Question 1: In below STEP...."

Please post these questions in a separate discussion - and in only one discussion.

Unknown

Thanks NowayJose....but my question is related to this discussion, I though to add it here.

It seems there IS NOT SOLUTION IN QUICKEN TO MERGE 2 FILES WITH QUICKEN>?

do you know how to escalate this matter to quicken management team?  I amm relaly disapointed .....spent lot of time just to mkove one account from one file to other and NO solution?

Unknown

I agree this is a problem. In the old days, could export and then import via a QIF file. However when Quicken end that offer, I have not found any way to solve this. Marriages, consolidations of businesses and many other reasons would make you want to merge 2 Quicken Data Files together. PLEASE HELP..

Unknown

Hello all! I was just reading through the instructions, because I need to Merge my wife and my Quicken accounts. I got down to 14a, wher eit says to File > Import > Qif, and then start importing accounts. But, when I got to this screen, it says that imports to 401k, checking, etc are not available - as of Quicken 2004. However, it looks like these instructions were written much more recently.

Can anyone let me know how there were able to import Checking, etc accounts in newer versions of Quicken?

Any help would be appreciated. Thanks!

CoolSwoosh

Unknown

It is far easier to merge two ACCOUNTS than it is to merge TWO Files.

Unfortunately, the lengthy process spelled out here does not work at all with Q2010.

https://qlc.intuit.com/post/show_post_full_view/cUup8wwGer3R4sacfArImM

If you were importing two different  MS Money files , the process would be much much easier.

Quicken has long ago gotten away from the QIF importing and Exporting format.
It may be OK with older versions of Quicken but not since 2004 (I believe).

I  was lucky where I was able to use the just expired MS Money files for importing into Q2010.  With some trial and error I was able to establish a decent file(s) with minimum of duplicates etc.

I hope there is a solution in the works - somewhere-sometime.....  

Suggestion: Provide what version of Quicken you are using. It helps!

Howard Roark

The "solution" has been posted in this Community literally hundreds of times.

The original post in this thread is concerned with the steps necessary to successfully merge Quicken files. It was obviously written long before Intuit placed restrictions on importing QIF files. But those restrictions do not - and will not - prevent you from accomplishing the import(s).

And the steps posted here were worked out for the benefit of the attributed poster ... but made available by him to others voluntarily. He (I'm talking about Chris Tomaszewski) worked very hard to determine these steps and was good enough to make them public. Be thankful.

Please do not muck up this thread with concerns that don't belong because they are not related to the subject of this thread ---- answers that you could have found in existing discussions here in this Quicken Community (and in MANY other places); or by asking questions which you could have started a new discussion for ... or you could have asked of the poster who referred you here.

Caroline W. Sherman

This is very thorough and useful information.  Thank you!  Question:  Will it work for Business files?  That is, I have one file (HOME) that has checking, savings, mortgage, securities accounts, etc. but NO business/invoice accounts.  I have another file (BUSINESS) that has checking, savings, security and two business/invoice accounts.  Are there special instructions for getting the business/invoice accounts from the BUSINESS file merged into the HOME file?  Or do I treat the invoice accounts just like a checking or savings account in the merge instructions?

Thanks,
copperberry
Quicken Home & Business 2010

matt_sutliff

This is a duplicate comment I posted in a different thread (https://qlc.intuit.com/post/show_full/aR7D987war4jBgacfAralO/em-quicken-em-em-2012-em-my-humble-opinion?p=2&ppid=85930622&q=quicken+2012+budget&s=0)

I want to thank both Chris54322 and NoWayJose and many others for their helpful posts about merging separate files into one for the purposes of marriage or whatever comes one's way.

I apologize in advance if this comment should be in a different thread. I will post in the following threads as well:

https://qlc.intuit.com/post/show_full/cUup8wwGer3R4sacfArImM?cid=soc_tw_f_soc_lcanswr_shr__link&priorityCode=4657900000&rmode=Twitter

https://qlc.intuit.com/post/show_post_full_view/dQMNaWtX4r3OK1acfArQA8

I use Quicken 2011 on Windows XP and am currently smack in the middle of merging two files into one master file. It is cumbersome, lengthy, and requires solitude and quietude. The problem I am having is that most of the transfers between accounts (checking, savings, cash on hand, etc.) and splits (payroll) did not move over cleanly. I am having to go transaction by transaction to find duplicates and errors. The historical data is very important to me, especially the categories. It's still a mess, but worth the effort.
Am I led to believe that if I upgrade to Quicken 2012 I can transfer my Quicken 2011 file directly into my new master file that contains my girlfriends data with the QXF feature you mentioned? Is it like adding sugar to coffee? Or should I just continue to plug along and get my ducks in a row and comfortable with my manual merger and then upgrade?
Now I know why people that work in Mergers and Acquisitions make so much money.
Thank you, Matt

Unknown

There has got to be an easier way than this? Does Quicken 2012 add this capability?

Unknown

Quicken 2012 adds part of the solution, but not all.

Quicken can export a QXF file, this format was created to export to Quicken Essentials for Mac, which can import and export it.  Up until the Windows versions of Quicken could only export this format.  With Quicken 2012, it can import it, and it can import it into and existing Quicken data file.  The QXF format exports the whole data file not just one part of it.
Now Quicken Essentials for the Mac doesn't have investment accounts or business accounts.

So they didn't make the format export these, so you can not import them (or it might be on the import side, it doesn't matter you can't export/import them.)

If you want to do only regular accounts you can use this to merge data files.  Or maybe get part of the way there.

Please note some of the some quirks mentioned above apply to the QXF format, like being careful about categories and accounts and such.  If I remember correctly if there is a duplicate account it will import into another account with the name changed.  For categories it will not transfer them just flag them as having problems in the log.  So for  them you have to go back and rename in the original and do the import again.

And note that a category can be considered "different" for nothing more then at one point one of the data files had a description of the category and the other didn't, even if you change them to have the same description now.  The rule is look in the log if it says it has problems rename it in the original and re-import (into a fresh copy of the second data file).  And then use Quicken category merging to fix it up.

Howard Roark

The "Merging Quicken Files" post, for the most part, contains guidelines ... not requirements. I believe that those intending to merge Quicken files will still be well served reading those guidelines presented by Quicken Kathryn; even though there may be newer, better, means of merging Quicken files that were not available when the post was originally made.

While those guidelines were (obviously) created for using QIF file export/import to make the merge, that does not make them all inapplicable to merges done by other means. It should be easy enough to determine which items pertain only to QIF files. While some users may find no need to make any changes prior to merging, I believe others may benefit by cleaning up one, or both, of the files before merging.

There is little to be lost by reading the guidelines to determine whether any apply.

Unknown

The objective to my question was to find an easy way to transfer previously categorized checking and credit card transactions that I had entered in a file that I later found contained corrupted Investment Account data. After I see the complexity of what is involved in merging two Quicken files, I think life woud be easier if I opened the file that contains my categorized transactions and enter each transaction manually into my backup file. That way I can enter transfers and splits without worry. I truly appreciate the effort of all who have responded in offering solutions to such problems.

Unknown

For non-investment accounts you can use the QXF format as stated below.  Otherwise, no, nothing has changed.
It is one of the reasons we tell people they should not split their data files.

Now you talk about "created by the bank".  That sounds more like an account, not a data file.  If that is what you want you can select the transactions in one register (if not an investment account), with normal Windows Shift/Click procedures and right click and select to move the transactions.

splasher

NOTE:  Two years ago when Quick'NPerlWiz made his comment, importing a QXF file into the current version of Quicken would work, but since QW2014, there are issues with the importation, so the QXF option no longer is viable.

NotACPA

Clarification sought re: Step 2.  Do the cat lists actually have to be identical ... or is it sufficient that the File 2 cat list is a subset of what's in File 1.

There are categories that I have used in the past, that don't exist in my finacee's file.  I have no problem with adding her categories to my file, but why do I need to add my stray categories to her file if she has no transactions that use them?