Reconciling and Cleared Balances

David Green

I suspect the answer is that I moved my data file from Quicken for Mac to Quicken for Windows. I exported the file as a .qfx file from Quicken for Mac and imported it to Quicken for Windows. Before I did the export I reconciled all of the accounts to the latest statement in Quicken for Mac. I am trying to reconcile two of the accounts for the first time in Quicken for Windows, but the cleared balance is much too high on the accounts and does not match my last statement balance that I enter. The same account in Quicken for Mac the balances still come out correctly. I am reconciling to the statement balance, not the online balance.

I think it's because Quicken for Windows sees the previously reconciled transactions in the account as cleared but doesn't have a reconciliation record. Or something like that. The transactions all show up in Quicken for Windows with an R in the cleared column. Is there a way to resolve this other than marking all the transactions as uncleared and then re-reconciling from the start of the account (I have data back to 2008 on some of my accounts), or putting in a balance adjustment?

Chris_QPW

Quicken Windows doesn't have a "reconcile record".  The cleared balance is calculated based on the transactions statuses (the Clr field).

Given that you reconciled before exporting the QXF file all your transactions in the register should be marked reconciled (Clr == R).  And if you go to do the reconcile (Ctrl+R) it should actually tell you that you have nothing to reconcile.

So the first question I have is did you add new transactions since importing the QXF file?
Another question is after importing the QXF file did you check to see if the balances were correct?

David Green

I downloaded new transactions. I did not add any manually. I expected the accounts that I reconciled to show up with a 0 balance difference on the reconcile. I still have Quicken for Mac installed and the reconcile there works as expected.

The online balance is correct in both Quicken for Mac and Quicken for Windows. For two credit card accounts in Quicken for Mac the the register balance matches the online balance. In Quicken for Windows, the same 2 accounts it does not. Both accounts are showing a negative balance of many thousand dollars. Each account gets paid in full every month so Quicken for Windows is missing several of the payments. Quicken for Mac has them correctly recorded.

I have not checked the other account that I tried to reconcile, which is my main checking account. That account goes back years with many more transactions, but I'll assume that in the conversion if transactions are missing in Windows from the credit card accounts, then they're missing from other accounts.

My wife's business account also has different register/ending balances. In Quicken for Mac it matches the online balance. In quicken for Windows it is higher by several hundred dollars.

Chris_QPW

You didn't answer the question about if you checked all the balances after the QXF file import.  It is important to know that it was or wasn't correct before the first download.  For now I will assume it was.

Unfortunately there are many things that might have gone wrong on your first download into an account that already has transactions in it.

First off Quicken might have put in/changed your opening balance.  Search for Opening Balance and make sure there is only one at the beginning of the register, and it is correct.

Another possibility is duplicate transactions.  Quicken relies on the Downloaded ID to identify a given transaction.  If the connection is setup as Direct Connect or Web Connect (downloading a QFX file) then the ID comes from the financial institution and should be the same.  But for Express Web Connect (What is called Quicken Connect in Quicken Mac) the ID might be coming from Intuit depending on how the financial institution gives them the data.  If Intuit is supplying the ID it is going to be different and you are going to have duplicate transactions to deal with.  Even resetting the connection or deactivating/reactivating such an account will cause downloading about 30 days of duplicate transactions.
You can tell if Intuit is supplying the ID by if they start with INTUIT-.
Select this icon to turn on the Downloaded ID column:





Given the fact that you reconciled all your transactions before doing the download, with the exception of the Opening Balances transaction(s), all the new transactions should have c in the Clr status, and New or New Match in the Status (blue dot) column (you can expand that column to see the words).

That should help you narrow down which are duplicate that need to be deleted.

David Green

I did not verify the balances before I did the first transaction download after importing the data from Quicken for Mac. Checking a few of the accounts, my main checking account has a mix of transactions that start with Intuit, and others that do not. The credit card accounts have no transactions that I can find that the ID starts with Intuit.

In Quicken for Mac, downloaded transactions are marked with a blue check After they are reconciled, they are marked with a green check. In Quicken for Windows after the import the same transactions are marked with an R.

There is likely a problem with duplicates, but there are several transactions that are simply missing. I can list the payments in my credit card accounts and see that many are missing in Quicken for Windows, on multiple credit card accounts. That suggests to me that there is a problem with either the import or the export.

David Green

Looking at the FTID column on my Mac for my checking account, all of the transaction IDs there start with INTUIT. On Windows in the same account they do not. I switched banks last year as part of a merger. I can see on Windows and Mac that the transactions with IDs that start with INTUIT start when I moved to the new bank. However on Windows, downloads from the new bank the IDs don't start with INTUIT, so indeed there are a lot of duplicates. If I were to start over with a fresh export from my Mac as a new data file, would Quicken for Windows download all of the transactions again so that I could just start over?

Chris_QPW

Each data file is completely separate, provided you create them with File -> New Quicken File.
As such you should be able to repeat the process without any "interference" with the other data file.

The first thing I would do is import the QXF file into a new data file and check the balances are all correct and the transactions are all reconciled.  Once all of that is done, make sure you do a backup so that you can restore to this point if need be.

And just so you know Quicken Windows has two modes for Reviewing/Accepting transactions.
Edit -> Preferences


For new data files the default is Automatically add to banking transactions, which is similar to what Quicken Mac does.  This can also be overridden in the Accounts Details -> Online Services tab on a per account basis.

It looks like this:


Clicking on the status brings up the menu to mark a transaction reviewed, and blanking that status.
Note this doesn't affect the reconcile state (the c in the Clr column).  This is the "Review process" where you make sure that things like the payee and categories are correct.  Note that if you reconcile a transaction not only will the c change to R, but also the New status will be cleared.  Also editing a transaction clears the New status.

With that option off it changes to "Accept" process".  It looks like this:


You select the transaction in the Downloaded Transactions tab, and it shows you that transaction in the register with the category and other information filled in.  If it looks good you hit the Accept button or Enter and it moves to the next transaction.  Note don't use the Accept All button, it will put all the transactions into the register with no way to tell which ones are new, with the exception that the cleared status will be c.

And BTW if Quicken changes/adds the Opening Balance transaction this will happen outside of either of these processes.

One more warning, that might be at the heart of some of these problems.  If you have setup to sync to Mobile/Web, don't on this data file.  At least until you are sure everything is correct for this first downloading.  Many problems can be tracked to the sync to Mobile/Web on Quicken Windows.

David Green

Thank you for the details. I updated everything through today on my Mac and did another export. I did an import to a new quicken windows file. The balances, etc matched what my Mac has. I backed the file up, I’ll wait a few days and run an update and see if that causes problems

David Green

I spent more time working with the new file that I created yesterday. I created a backup after the initial import. I added online access to one account at a time and verified them against my copy on Quicken for Mac. I created a backup after adding online access to each account. I added my main checking account last. It was the only one that had a problem. Here you can see 2 transactions that are duplicate. . The problem is just as you described. the transaction IDs are different so Quicken thinks they are different transactions. This goes back to last July when we were moved to a new bank as part of a merger. All of the transactions that have IDs that start with 'Intuit' are from my Mac and are the ones marked with 'R'. If I were to delete all of those transactions and re-reconcile starting from last July, would that fix it? It would be a lot of work but I want the best long term fix for this.

I appreciate the help in unraveling this.

Chris_QPW

If everything was in balance before you enabled downloading for your checking account then fixing it should be as easy as deleting the downloaded transactions (but not any that are truly new).

You can get all of the downloaded transactions to be sorted to the end of the register by selecting the Clr column header.  Once they are together you can delete them in mass by selecting the first transaction and then hold down the shift key and right click on the last transaction you want to delete.  This will select the range of transactions and bring up the context menu where you can select to delete the transactions.

Be sure to click on the Date column header to resort by date when you are finished.

Also just so you know another way to do this sort in the case where you hadn't reconciled before downloading you can select the gear icon in the upper right of the register, select Sorting options -> By Order Entered.

If there are new transactions (non duplicate), you should just be able to reconcile again, no reason to go back to older times.

David Green

Thanks for all of your help. I did some more work last night and it looks like everything is correct. When I removed the duplicate transactions from my bank account it also removed the payments from some credit card accounts. There was not a way to avoid that without causing the balance in my checking account to be off. I had to restore from backup a couple times before I figured out what would work. I got my checking account correct, then exported the credit payments from the CC accounts on my Mac to CSV and used ImportQIF to import them to Quicken on Windows. that worked well. Thanks for that utility. The lesson is moving from Mac to Windows is possible, but not easy.

Chris_QPW

I'm glad you got it all sorted out.

It sounds like "transfer detection" kicked in and caused problems.  Transfer Detection is just a guess that a given deposit and withdraw for the same amount, around the same date, in different accounts, are in fact a transfer.  If Quicken thinks it is it will link the two transactions removing one of them.

At a minimum you should ensure that it asks for conformation before doing that.

David Green

Thanks. That option was turned on. It didn't seem to matter what I did. Several of the duplicated transactions in my checking account were credit card payments. 1 Out of each duplicated pair was coded as a transfer to that CC account. If I deleted the non-coded one, my checking balance was off but the CC was correct. If I deleted the coded one, my checking was correct but the CC balance was off. I finally just deleted the one so checking was correct, and exported the payments from my Mac as CSV then used your utility to import to Windows. There might have been an easier way, but it's all good now.