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,Ignorethe 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 theactual 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
