Combine multiple Quicken files into one.

Philip107

In the beginning I created a new Quicken file every year which I stopped doing in 2006. So, I have about 9 individual files starting 1998 that I'd like to combine into one. I use these old files mainly to lookup items that were purchased to find dates and amounts. If they were in one file it would be much simpler.

I guess the resultant file wouldn't have to be a Quicken file but a CSV or other text file may work just as well for lookup.

I was wondering if anyone has accomplished this and can share lessons learned in doing it. I know that some of the transactions will be in multiple files from the way the year end consolidation was performed.

thanks,   -phil

Greg_the_Geek

https://community.quicken.com/discussion/7150406/how-to-merge-two-quicken-files

splasher

Here is a thread on merging files: https://community.quicken.com/discussion/7150406/how-to-merge-two-quicken-files

You are in for a bunch of work.

In addition to that, have you opened those files in the past to keep them up to date with the latest version of Quicken that you are running?

If not, see this thread: Convert Data from an Earlier version of Quicken

Philip107

splasher said:

Here is a thread on merging files: https://community.quicken.com/discussion/7150406/how-to-merge-two-quicken-files

You are in for a bunch of work.

In addition to that, have you opened those files in the past to keep them up to date with the latest version of Quicken that you are running?

If not, see this thread: Convert Data from an Earlier version of Quicken

Yes I have been opening them through the years. Just did it again yesterday without any issues. Just had to log in to Quicken with my new Quicken ID on a couple of them.

splasher

Good luck.  Let us know if you succeed, it will not be fun but worthwhile when done.

Philip107

Greg_the_Geek said:

https://community.quicken.com/discussion/7150406/how-to-merge-two-quicken-files

I looked through that merging process and decided it's not worth the effort to keep them in the Quicken format. Guess I'll just use a report to extract all the transactions in a CSV format and then append all the files together. Maybe I can use Excel or something to delete duplicate rows.

splasher

Run your reports for a single year, that way you should eliminate any duplicates carried over from a prior year because they weren't cleared yet.
Like I said, it is a bunch of work just to just merge two, your situation just multiplied it several times.

Philip107

All Done. Exported transaction reports from each file as Excel files. Copy and pasted all the data together and then ran remove duplicate rows. Ended up with 26,500 rows.

wiensken

Having spent the last few weeks trying to merge two files (mine and my recently acquired spouse), I can agree it is a lot of work. After several weeks of effort I have actually abandoned the effort. The documentation referred to earlier is extremely helpful, and if all you do is have a couple banking accounts, you might be OK. Here are some of the issues I discovered though:

If you use multicurrency at all (like having a US and CAD investing accounts), start by buying a large bottle of rum.

Investing accounts loose a lot (and I really mean a LOT) of information on the QIF export / import.

When you import an account that you originally classified as a "retirement account" - it will become a standard brokerage account. Best to manually set up all accounts in the receiving file first. You won't like the defaults that the QIF import gives you.

I am in Canada and have a mixture of Canadian and US currency brokerage accounts. All of the US accounts got converted to Canadian and could not be converted back. I attempted to resolve this by setting up all brokerage accounts manually first - before importing the transactions - making sure names were exactly the same as in the file being imported. This helped a bit.

All US stocks got converted to Canadian stocks. I tried to resolve this by changing them back to US exchanges. That almost worked, but quicken insisted on thinking those US stocks were quoted in Canadian $. The currency field is grayed out so you can't change it. Perhaps if you set up all your stocks in Quicken before you imported the transactions this might work. I deal in about 300 stocks - so this wouldn't be a lot of fun - however I suspect you only need to do this for stocks that are not in your base currency.
Might also be time for a second bottle of rum.

All cash transfers between US brokerage accounts and Canadian brokerage accounts really failed badly. Originally these transactions were done using the "Cash transferred out of account" transaction - which creates 1 transaction in the "incoming" account and a linked matching half of the transfer in the "outgoing" account. Some of the ways this fails:

1) the exchange rate you used for the original transaction is lost. Quicken will pull out a new exchange rate based on its bank history file for the rate that day (which is usually not what your bank charged you)

2) that "link" between the xout and xin sides of the transfer is broken after the import. It visually looks linked - but it really isn't.

3) each of these transactions is duplicated on each side of the transaction - one using the original numbers and one that would have been calculated with a different exchange rate. For example if I transfer $1000 CAD (from account CAD1) to $790US (into account USD1) I will get in the CAD1 account:

Xout $1000 -> to USD1
Xout $975 -> to USD1
(so my register says I transferred out $1975 instead of $1000)

In the USD1 account I'll also have two Xin transactions, one correct amount and one slightly incorrect amount (because the import lost the exchange rate I used when I did this transaction and uses the bank rate instead. You need to have the original values for both sides of any currency exchange transaction as you will be manually reentering them all.

Even though it says the transaction is linked to matching transactions in the USD1 account - they are not linked. In the USD1 account there will be two similar transactions. The only way I have found to fix this is to delete every transaction that transfers cash between CAD and US and reenter them manually. I have several thousand of these, and actually tried for a while to do this. Thus you need to delete 4 transactions (2 in each account) and recreate 1 proper one. You might want to switch to beer as the rum is getting kind of expensive at this point.

Because when quicken imports the security list is looses the currency and market (defaults in my case to the TSE when it should have been NYSE) - you can end up with duplicate stocks (if you already had that stock in the base file). The original stock in your file (set up properly) and a second stock with the same symbol but in the market place the QIF defaulted it to. All your imported purchase, sale, dividend etc transactions will go to the wrong stock - you will need to reenter every transaction associated with that imported stock.

I'm putting this process on hold for a month or so. I think eventually I will need to manually re-enter every transaction that involved any banking or investing accounts that were not in my base currency. My quicken file goes back to 1997, but the one I'd like to import only goes back 5 years. I suspect this will keep me occupied my about a month to reenter those transactions 1 by 1.

Perhaps I should have stayed single or at least learned to like cheaper rum :-)

Chris_QPW

@wiensken Very good write up.  And yes as soon as you bring in multiple currencies an already very difficult process magnifies 10 fold.

Note that even with one currency just exporting a QIF file and importing it into a new "blank" data file is likely to fail, mostly because of the transfers and partly because of the limitations you mentioned in the QIF format has in the area of investment transactions (for instance no support for keep track of what security lots go together).  I think you might be right about setting up all the securities in advance.  I think you would only have to setup the ones that aren't the Home currency, but I have never tried it.

The only way I have found around the transfer problems is to convert the transfers to regular categories.  You lose the "linking" functionality on that given transfer, but the double book accounting still works.
Here is a webpage on that and it has a link back into this forum about a lot of the limitations of QIF importing:
http://www.quicknperlwiz.com/changetransfers.html