Cleaning out Your Quicken Data File: Deleting all Historical Transactions, Securities, and Accounts

[Note: please DO NOT ask me questions about this procedure. I did it twice myself and never expect to do it again. I will not remember any details and will not be able to provide any more help that what is described within this procedure. ]

This procedure was done using Quicken 2020. The exact sequence of buttons may change in the future.

My Quicken Data file is filled with decades of old data, long forgotten closed accounts, and stock transactions from now non-existent companies (like MP3.COM!).

If I’m going to migrate my current data to gnuCash, I’m sure I need to get rid of as much history as possible to make the migration less of a headache.

I spent several very long sessions figuring out how to remove all of this history. The old securities were particularly a problem. The process is complicated enough I decided to document it here in case any one else decides they need to do the same.

You need to backup your live data and work on a copy. Chances are you will need to go thru the process several times until you figure out exactly what works for you. As I was going thru the process, I would check point my progress by exiting quicken and copying the data file. Then if I screwed something up, I only had to return to the prior checkpoint or worst case a few checkpoints back.

Deleting Old Accounts

  • As already mentioned, copy your live quicken file and work on a copy of the live data. Once you are done you will have a file to import into gnuCash and still have your live data file so you can continue to use quicken, even if just for historical data.
  • Do a screen capture of quicken’s left panel that shows the balances of all of your accounts and your net worth. Once you are done, you will want to verify these all still match.
  • With your copy of the quicken file open, Select File | File Operations | Validate and Repair. In the dialog box, select validate file and click on OK. Depending on your data file size, this can take a while. About 5 minutes for me.
  • Open the Account List (ctl-A). Select ‘Show Hidden Accounts’ box. All of my old unused (zero balance) accounts get listed this way. I don’t think I have ever deleted an account so I have a bunch of them.
  • The next step is to reconcile all of these hidden accounts with a Zero balance. The year-end process will not delete old transactions unless they are reconciled.
  • I start at the top of the account list, open each old account, reconcile it (ctl-R) using a zero ending balance.
    • Closed Brokerage accounts  should be reconciled in the same manner.
    • For Asset(Property) and Liability accounts, you cannot do a ctl-R reconcile. Instead:
      • Select the transactions you want to ‘delete’ in year-end
      • Edit | Transaction | Reconcile State | Reconciled to mark them reconciled.
    • You may have other non-hidden accounts that you want to reconcile. For example I have a CASH, AR, and AP account that are catchalls and have decades of transactions that really aren’t important to me.
    • I’ve had a couple of cases where reconciling an Asset account has messed up another account total. I had to run transaction registers of before and after to find the screwed up transactions and fix them. I suspect the problem was due to some corruption in the data file.
  • You are ready to run Year End (YE) which will delete all transactions OLDER than a date you specify.
    • YE will not delete unreconciled transactions.
    • YE will not delete any transactions dealing with a security.
    • To start the YE, File | File Operations | Year-End Copy
      • Select I only want transactions in my current data file …
      • Typically this would be Jan 1 of the year for which you want to keep in your new quicken data file.
      • Click OK. This takes a fair amount of time.
    • Review the YE:
      • One irritating issue I’ve found – if windows file explorer has your quicken data file selected, quicken will fail the YE w/o any indication of an error. If nothing appears to have been deleted, check for that.
      • Your account balances should match what you had at the start.
      • Examine each account. In general, they will start on the specified date. If you have transactions dealing with securities or transactions to an asset/liability that you didn’t reconcile then those will still show. Generally that is fine.
  • At this point I do another File | File Operations | Validate and Repair and check the validate box just to be sure something horrible hasn’t happened to the data. Check those account balances!
  • Finally, you can delete old accounts!
    • ctrl-a to bring up accounts list. Check Show hidden accounts.
    • To delete an account from the list, click on the edit button, click on delete account button, Verify it is the right account, type in Yes, click OK.
    • Repeat until they are all gone!
    • Delete Old brokerage accounts if there are no open securities in them.
    • At this point, I no longer have any hidden accounts and my account balances are all still correct.

Deleting Old Securities

Deleting old securities is even messier than deleting accounts and transactions. Unless you’ve got many, you are probably best off not doing this. But here is how.

  • You should still have a screen capture of your account balances.
  • Take a screen capture of your open securities
  • Create a new ‘TEMP’ brokerage account to hold open securities
    • Tools | Add Account | Brokerage | Use Advanced Setup | I want to enter xactions manually
    • Click on Next, use ‘TEMP’ as account name, click on next, click on next again, click on next again, Yes for no securities entered, and finally click on Done.
    • Select No for single mutual fund account and click next then click finish.
  • Move Open positions (securities) to the TEMP brokerage account:
    • Select the brokerage account containing the securities to move
    • Click the settings gear icon at the upper far right
    • Click on Move transactions
    • In the Move Investment transactions window click on the Select Open Positions button.
    • In Move To Account box, select the new TEMP account.
    • Click on the Move Button, then click on the Done button.
  • Archive Closed positions to Archive account:
    • Select the brokerage account containing the securities to move
    • Clicke the settings gear icon at the upper far right
    • Click on Archive Transactions
    • Click on Yes, click on Backup
    • All old transactions are in a new account named <brokerageAcctName>-Archive
  • Delete this newly created Archive account. Verify net worth is still correct.
  • Now you can deleting old transactions in the brokerage account linked checking account that matched those old securities.
    • Open the brokerage account linked checking account
    • Sort on Category
    • Locate and then select all transactions with a category of [Unspecified Account]
    • ctl-D to delete the transactions
    • Your Net Worth will now be wrong and the brokerage checking account will be the culprit. That’s OK for the now.
  • Move open positions from TEMP account back to brokerage account:
    • Open the TEMP brokerage account
    • Click the settings gear icon at the upper far right
    • Click on Move transactions
    • In the Move Investment transactions window click on the Select All button.
    • In Move To Account box, select the brokerage account
    • Click on the Move Button, then click on the Done button.
    • Delete the TEMP account
  • Correcting the Brokerage Linked Checking Account Balance
    • Open the Checking Account
    • Verify the transactions are sorted by date
    • Go to the top of the transaction list (oldest) where you will find a Beginning Balance transaction.
    • The beginning balance will probably contain some crazy big negative number. Change it to zero.
    • Examine the current balance for this account.
    • Enter a new beginning balance (in the DEPOSIT field) that is <correctBalance> – <currentBalance>
  • Now, the checking account balance should be correct as well as the Net Worth
  • Open Positions should match up with what they were before.
  • Delete Old Securities (Finally!!):
    • ctl-Y will bring up the securities list
    • Each security that is closed and contains no transactions will have a DELETE button available at the far right of the security’s line.
    • Scroll down the list, deleting each of the securities that can be deleted.
  • Final Check
    • All account balances and net worth match the original values.
    • Run Validate one more time

Extra Credit

I had one special case security that took some extra thought.

Like most (I assume) brokerage accounts, mine has a money market (MM) account that money sweeps into and out of automatically (at least in the early days) and there is the obligatory $0.02 cents of interest each month. After a couple of decades this amounted to a lot of transactions.

The brokerage account had stopped using that account several years ago (the balance was moved to another MM account) and it was essentially a closed security EXCEPT they had decided to start using it again just last month. A balance was moved back to that account and it had one month of interest.

I really wanted to get rid of all of those historical transactions and just keep the 2 recent ones.

To do this, before starting the Deleting Securities procedure, I created a new transaction against that MM security that SOLD all of the shares for $0, effectively closing that position.

After moving all positions from the brokerage account (and before deleleting the archive account), I moved JUST the 2 recent transactions from the archive account back into the brokerage account.

That completes my process for getting rid of all historical transactions, securities, and accounts in the quicken data file. I now have a clean quicken data file I can use for experiments on importing data into gnuCash.

This entry was posted in c-gnuCash and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.