restore from backup good but data screws up sfter synchronizing with bank. lost about $20k

Glenn Dawe

Quicken Version 6.1.1 (Build 601.37924.100)
macOS Big Sur 11.2.2

Quicken_Tyka

Hello @Glenn Dawe

Thank you for taking the time to visit the Community to report this issue, although I apologize that you have not received a response.

I have a few questions to better help me better understand what could be happening.

First, can you verify where the data file is stored? This can be located by holding the "Control" key on the keyboard and clicking the Q icon to the left.

 

 

This will display the file path. In this path, do you see any mention of Dropbox or iCloud?  If the active file is stored in a cloud drive, this can cause the behavior that you have described.

 Please let me know what you find!

 -Quicken Tyka

Glenn Dawe

Thank you for your response @Quicken_Tyka. Right off the bat I learned something new about finding the file path in quicken for Mac. I laid in bed last night worrying about the cryptic message i had posted.

My file is held in <McIntosh HD\Users\Glenn\Documents\Quicken finances>. (for the purpose of this discussion I am calling my finance file quicken finances.)

My automatic back up files are stored to my Dropbox\Automatic Backups folder in the account that was provided by my Quicken subscription. The most recent, accurate file I had was from February 5, 2021. This file can be fully reconciled with my bank statements. With the file restored, I go to update my banking information online and the data becomes totally corrupted, with multiple duplicate entries and the bank balance suddenly dropping by about $20,000. I believe Quicken for Windows used to have a function where the data could be verified but I do not see that option with the Mac version.

After reviewing my file and ensuring the reconciliation is up-to-date I save another back up and then try to synchronize online again and run into the exact same problem occurring. It happens across multiple accounts with my checking account the one that is hit hardest. Other accounts have issues with multiple duplicate entries but the balances are not that far out of whack.

From my TimeLine Mac backup on an external hard drive, the same problem occurs as when I restore the back up file from Dropbox.

jacobs

@Glenn Dawe One additional question relating to possible problems caused by cloud storage… You note that your Quicken data file is stored in your Documents folder. Can you check other your Documents folder is being store on iCloud, which is something Apple likes to enable.

In your System Preferences, click on Apple ID, then click on iCloud. Is iCloud Drive checked or unchecked? If it is checked, click Options and see if Desktop and Documents Folders is checked. If so, it means that your Documents folder, and your Quicken data file, are actually being stored on iCloud instead of your local Mac. If that's the case, post back and I can explain how to move your Quicken file to a location on your Mac which is not store on iCloud. (I don't know if this accounts for the problem you've described above, but it's definitely good practice to keep your Quicken data file stored locally in any case.)

Glenn Dawe

@jacobs
I usually keep a close eye on storage limitations of iCloud, especially photos etc.
Desktop and Documents is the only box there that is not checked.

Searched for Quicken files on my Mac and all are in my Documents folder. Main data file is in Glenn\Documents while all backups are in Glenn\Dropbox\Automatic Backups (17 copies, various dates/times) or Glenn\Dropbox\Automatic Backups\Automatic Backups (27 copies, various dates/times)

jacobs

@Glenn Dawe  Great! You've ruled out the data file living on Dropbox or iCloud, which, as Tyka said originally, can cause some odd results. Which brings things back to your original question...

I certainly don't believe your data file is being corrupted, but I do understand there are duplicate transactions which mess up your account balance. If you look at your transaction running balance as of January 31 in your backup file when you first open it, does it still match in the file updated after transactions download, or is it showing that the balance changed into the past? Can you go through and manually eliminate the duplicates to get things back to accurate? And after that, do subsequent transaction downloads behave normally?

Here are a few additional pieces of data which might be helpful in working the problem…
What bank is it?
What connection method is Quicken using for this bank?
Is this problem with one bank account or multiple accounts? And if multiple, is it at multiple financial institutions?

Glenn Dawe

@jacobs
The wrong data goes all the way back to October 31, 2018 comparing my Quicken file with that of my bank. I am using RBC Canada. A similar problem happens on Tangerine which is an E bank acquired a few years ago by Scotiabank. I have not gone back in that account to see when the Quicken data matches what the bank shows. I've also not gone through transactions following October 31, 2018 to see when that data actually starts to go funky.
I know that when I first restore from backup the data is fine with the correct bank balances; it just acts up when I try to synchronize online.

Both the RBC and Tangerine accounts use Quicken Connect. My other accounts are using Web connect and do not seem to exhibit as much of a problem although I have tried to just download the files and update RBC as I would with Web connect but still run into the same problems.

Glenn Dawe

@jacobs
Just double checked my AMEX account that is web connect. Quicken tells me the online balance (shown in the top right corner) is $50 while when I open Quicken it shows me the actual online balance of -$35. It looks like those bank balances are being all screwed up too as according to that I've been in the hole since September last year anyway although I make it a point of paying off the balance completely every month.

jacobs

So if I understand correctly, when you restore the early-February backup, all your balances are fine. But after you download transactions, your balances are wrong, not just as of today, but going back for at least a few years. Is that right?

My first thought is to go back to check the opening balance transaction for one of these accounts. It sounds as if Quicken is changing the opening balance (something it can do if the opening transaction is a "Live Opening Balance") or is creating a placeholder transaction, in order to force the current balance to match a value from the bank. It's possible, but seems unlikely, that the download of recent transactions is changing any other transactions far back in the past.

This is a case where it would be helpful to be able to open two Quicken files simultaneously to compare them side by side, but you can't do that unless you have a second computer handy. So I'd open the "good" file (restored, but no downloads), go back to the beginning of time, note the opening transaction, and then write down the running balance as of the last day of each year. (If you have many many years, then do every second or third or fifth year.) Then open the "bad" file (restored plus downloads) and do the same thing, to see where the discrepancy comes from. As I said, I suspect you'll find it right at the start, but if not, find out where it's right and where it goes wrong. Go back and forth between the two files until you can find exactly where something changes -- and see if that gives you a clue about what's happening when you do the download.

Glenn Dawe

@jacobs
I don’t have a second Mac so I’ll need to restore the file and then go back to take a look at the first transactions. After that I’ll do the updating/synchronizing and go back to review the transaction history again. I’ll try to get to that tomorrow.

Can I print the register to Excel or .csv file to facilitate review?

jacobs

Yes, you can export register transactions to a .csv file and open in Excel. Or print to a PDF.

Glenn Dawe

FYI, I am still working on the CSV files trying to rationalize what’s going on. It seems the more I work with them the deeper the errors go so I’m trying to get something that makes a little more sense.

After synchronizing I’ve found that quite a few transactions are duplicated and other entries where specific transaction comes up with a an earlier date. For example where I reconciled with my bank statement on February 5, after synchronizing that same transaction it changes to February 6.

I’ll be in touch again once I’ve spent a little more time trying to sort things out in the CSV files.

Glenn Dawe

I think I finally figured it out. I've been receiving Long Term Disability payments since May 2018 and for some reason, synchronizing the file deleted 5 of them. Several other transactions were jockeyed around but didn't impact the total.

I also found out if I took the restored file from back up and exported it to a QXF file prior to synchronizing, I could then import it after synchronization and things would clear up substantially. The only problem with that approach is that it creates duplicate accounts so I have to go through and delete my "Savings 2", "Checking 2", "credit card 2", etc. accounts. Should hopefully be wrapped up tomorrow