How to verify the integrity of your Quicken file
dave344
Quicken Windows Subscription Member ✭✭✭
When it comes to accounting systems perhaps the most fundament definition of "integrity" is the system's ability to reliably explain all of the changes in your accounts over a given period of time.
Quicken provides you with the means to create an explanation but, as far as I know, does not provide you with any direct means of verifying that explanation. However, with the help of Excel, you can perform such a verification in about 10 minutes.
Here's how to do it:
1. Run a balance sheet report with beginning and ending balances and paste it into Excel (from "Customize" in the report, choose Export | Copy to Clipboard and then paste into Excel).
2. Next, run an income statement report and paste it into the same spreadsheet about 6 lines directly below the balance sheet you just added.
3. In the space between the balance sheet and income statement, create a formula that subtracts the beginning "Overall Total" of your balance sheet report (your net worth) from its ending value. In the cell below that, create a formula equal to the "Overall Total" of your income statement report (your net income). Finally, in the cell below that, create a formal that takes the difference between the two cells above.
If all is well, the net change in your balance sheet should equal your net income and your Quicken file will have passed the most basic of integrity checks.
****But beware:****: changes in your accounts can be due to more than just income and expense items. In order to create a reliable verification, you need to prepare a proper balance sheet and income statement report in Quicken that will capture these "other" changes.
I do this by creating a balance sheet report from the default "Net Worth & Balances | Net Worth" report." For the Income statement I start with the default "Spending | Income/Expense by Category" report. The trick is to configure both reports to include all possible data.
To configure each report:
1. set the interval to year
2. set the date range to "include all dates"
3. click the "customize" gear
4. under the "Accounts" tab check "selected", then check "show hidden" then click "select all"
5. Repeat #4 for the Categories, Payees, Securities (only exists on balance sheet) and Tags tabs
6a. Under the Advanced tab of the Income statement report, make sure to select "All amounts", "Include Unrealized Gains," "All Transactions," "Include All" transfers, "Show All" subcategories and then check all the status boxes.
6b. For the Balance sheet report there is only one option under the Advanced tab: "Include Unrealized Gains." Make sure this is checked.
7. Finally save the reports as something like "My Income Statement" and "My Balance Sheet"
Running these reports and pasting them into Excel as described above will allow you to check the basic integrity of your Quicken file. How so? The difference between your beginning and ending "Overall Total" in your balance sheet is the net change in all of your accounts for the period (and also the change in your net worth). You should be able to fully account for this change with the income statement you prepared (provided it was properly configured).
The income statement you have configured is, in effect, your "explanation" of all the changes that occurred in your accounts. In other words, the net change in all your accounts should be the aggregate of as many as four different items that are reflected on your income statement:
1. The income you earned
2. The expenses you incurred
3. The transfers among accounts (FROM or TO). These aren't items of expense or income but they get picked up because you selected "Include All" transfers when you configured the report. Transfers are simply movements of assets or liabilities among accounts. For example, if you move money from Fidelity to Schwab, there is no income or expense involved, it's simply a debit to account Schwab and a credit to account Fidelity. However, because the account balances have changed, Quicken provides you with a way to account for this change. This is what a "transfer" on your income statement does. Transfers can be reported in both the income and expense sections of the income statement depending on whether the account was increased (a FROM in the income section) or decreased (a TO in the expense section).
4. Any "balance forwards" (Bal Fwd) you created. Typically these are created when you add an account and specify an opening balance by entering a transaction in the account register that posts back to itself. Normally these occur only when you create your Quicken file for the first time and "bring in" the beginning balances for all your accounts. However, there are other occasions when you might create a Bal Fwd. For example, if you got married and decided to add you spouse's checking account to your Quicken file. You might create a new account called "Spouse checking" with an opening transaction of, say $100, that is posted in the "Spouse checking" register as of the date of the wedding and posted to the category of "Spouse checking." This will create a "Bal Fwd" item on your income statement because it is a change in an account that did not flow through the income statement or transfer from another account. It is an external increase or decrease to your net worth that is not reflected in income or expense. It is somewhat analogous to a capital contribution or dividend distribution in corporate accounting (although it can also be used as a hack to true up your balances when you're not sure exactly how or why they've changed).
If the difference between the change in your net worth and your net income is zero, then you have verified the basic integrity of your Quicken file. You may have other issues with Quicken but, at a minimum, you know you can properly account for all the changes in your accounts over the period in question. And if you have also reconciled all of your account balances to vendor or financial institution statements then you can feel especially confident: you know what you had, you know how it changed and you know what it's worth now. This is valuable information to know.
Sadly, my Quicken file fails this basic integrity check. I have a change in an account that I cannot explain as an item of income, expense, transfer or balance forward. So while I hope this information may be helpful to others, I would be grateful if anyone can identify any inaccuracies or misconceptions I may have on the topic or possibly expand into areas I may not have considered.
Thanks
Dave
Quicken provides you with the means to create an explanation but, as far as I know, does not provide you with any direct means of verifying that explanation. However, with the help of Excel, you can perform such a verification in about 10 minutes.
Here's how to do it:
1. Run a balance sheet report with beginning and ending balances and paste it into Excel (from "Customize" in the report, choose Export | Copy to Clipboard and then paste into Excel).
2. Next, run an income statement report and paste it into the same spreadsheet about 6 lines directly below the balance sheet you just added.
3. In the space between the balance sheet and income statement, create a formula that subtracts the beginning "Overall Total" of your balance sheet report (your net worth) from its ending value. In the cell below that, create a formula equal to the "Overall Total" of your income statement report (your net income). Finally, in the cell below that, create a formal that takes the difference between the two cells above.
If all is well, the net change in your balance sheet should equal your net income and your Quicken file will have passed the most basic of integrity checks.
****But beware:****: changes in your accounts can be due to more than just income and expense items. In order to create a reliable verification, you need to prepare a proper balance sheet and income statement report in Quicken that will capture these "other" changes.
I do this by creating a balance sheet report from the default "Net Worth & Balances | Net Worth" report." For the Income statement I start with the default "Spending | Income/Expense by Category" report. The trick is to configure both reports to include all possible data.
To configure each report:
1. set the interval to year
2. set the date range to "include all dates"
3. click the "customize" gear
4. under the "Accounts" tab check "selected", then check "show hidden" then click "select all"
5. Repeat #4 for the Categories, Payees, Securities (only exists on balance sheet) and Tags tabs
6a. Under the Advanced tab of the Income statement report, make sure to select "All amounts", "Include Unrealized Gains," "All Transactions," "Include All" transfers, "Show All" subcategories and then check all the status boxes.
6b. For the Balance sheet report there is only one option under the Advanced tab: "Include Unrealized Gains." Make sure this is checked.
7. Finally save the reports as something like "My Income Statement" and "My Balance Sheet"
Running these reports and pasting them into Excel as described above will allow you to check the basic integrity of your Quicken file. How so? The difference between your beginning and ending "Overall Total" in your balance sheet is the net change in all of your accounts for the period (and also the change in your net worth). You should be able to fully account for this change with the income statement you prepared (provided it was properly configured).
The income statement you have configured is, in effect, your "explanation" of all the changes that occurred in your accounts. In other words, the net change in all your accounts should be the aggregate of as many as four different items that are reflected on your income statement:
1. The income you earned
2. The expenses you incurred
3. The transfers among accounts (FROM or TO). These aren't items of expense or income but they get picked up because you selected "Include All" transfers when you configured the report. Transfers are simply movements of assets or liabilities among accounts. For example, if you move money from Fidelity to Schwab, there is no income or expense involved, it's simply a debit to account Schwab and a credit to account Fidelity. However, because the account balances have changed, Quicken provides you with a way to account for this change. This is what a "transfer" on your income statement does. Transfers can be reported in both the income and expense sections of the income statement depending on whether the account was increased (a FROM in the income section) or decreased (a TO in the expense section).
4. Any "balance forwards" (Bal Fwd) you created. Typically these are created when you add an account and specify an opening balance by entering a transaction in the account register that posts back to itself. Normally these occur only when you create your Quicken file for the first time and "bring in" the beginning balances for all your accounts. However, there are other occasions when you might create a Bal Fwd. For example, if you got married and decided to add you spouse's checking account to your Quicken file. You might create a new account called "Spouse checking" with an opening transaction of, say $100, that is posted in the "Spouse checking" register as of the date of the wedding and posted to the category of "Spouse checking." This will create a "Bal Fwd" item on your income statement because it is a change in an account that did not flow through the income statement or transfer from another account. It is an external increase or decrease to your net worth that is not reflected in income or expense. It is somewhat analogous to a capital contribution or dividend distribution in corporate accounting (although it can also be used as a hack to true up your balances when you're not sure exactly how or why they've changed).
If the difference between the change in your net worth and your net income is zero, then you have verified the basic integrity of your Quicken file. You may have other issues with Quicken but, at a minimum, you know you can properly account for all the changes in your accounts over the period in question. And if you have also reconciled all of your account balances to vendor or financial institution statements then you can feel especially confident: you know what you had, you know how it changed and you know what it's worth now. This is valuable information to know.
Sadly, my Quicken file fails this basic integrity check. I have a change in an account that I cannot explain as an item of income, expense, transfer or balance forward. So while I hope this information may be helpful to others, I would be grateful if anyone can identify any inaccuracies or misconceptions I may have on the topic or possibly expand into areas I may not have considered.
Thanks
Dave
1
Comments
-
A nice summary presentation. Thanks. I have not immediately processed all your statements, but I have taken a similar path in the past.
So in addition to processing that way for all your accounts, you can also do so for any one specific account as well and it sounds like you have done that. So what type of account is this that is failing - cash, asset, liability, investment, investment retirement? Can you tie down the time frame for the failure point?dave344 said:...
Sadly, my Quicken file fails this basic integrity check. I have a change in an account that I cannot explain as an item of income, expense, transfer or balance forward. So while I hope this information may be helpful to others, I would be grateful if anyone can identify any inaccuracies or misconceptions I may have on the topic or possibly expand into areas I may not have considered.
Thanks
Dave
Investment accounts might fail due to placeholders which might be hidden. Certain investment actions might also throw in a wrench - I am thinking of spinoffs and acquisitions in particular as possible danger zones. Are you off by pennies, a few dollars, $100,000?
Similarly, a self-referencing categorization could be an issue. That would be where you record a transaction in Account with the category as [Account]; probably a variation on your Balance Forward application.0 -
i posted a comment here last night but as of this morning it's still not visible?0
-
Thank you q_lurker.
I am becoming convinced the problem is not with data integrity but rather with the Quicken report writer.
Specifically, I believe the report writer does not always handle unrealized gains properly.
For example, I cannot account for all the changes in my accounts when I prepare reports that include unrealized gains. However, if I exclude unrealized gains from both the balance sheet and the income statement, then all changes in the accounts will be accounted for in the income statement.
Since the problem appears to be related to unrealized gains, I focused my attention on investment accounts. My first step was to verify the account balances reported by my brokers with those in the Quicken investment accounts. When I do this I find the market values (which include unrealized gains) reported by my brokers agree with the values listed in the "Accounts" column on the left hand side of the Quicken application. They also agree with the amounts reported in the "holdings" tab of the account register.
However, if I run a balance sheet report configured as described earlier, the net worth in the report does not equal the amount listed at the bottom of the "Accounts" column. If I examine individual accounts, I find that the difference between the "Accounts" column and the report is limited to two investment accounts; one is off by several hundred dollars, the other by $142K. Further, if I run a Portfolio Detail report, its investment accounts balances agree with those in the "Accounts" column. Thus, not all reports are inaccurate.
However, it appears the balance sheet report (which I generated from the default "Net Worth & Balances | Net Worth" report") is inaccurate. At least with respect to the way it is aggregating unrealized losses. The underlying data appears fine, the report is just not processing it properly.
I tried to duplicate this error in a test file using simulated data and have been unsuccessful. Part of the problem may be due to the volume of data in my real-world file. There are about a thousand various investment transactions in the affected accounts. So transaction volume may be part of the issue here.
However, I would be interested to know if other users with active investment accounts have encountered similar problems.
It's pretty easy to find out; just run a balance sheet (net worth) report and compare it to the value at the bottom of your "Accounts" column. Do they agree? If not, why not?0 -
@dave344, THANK YOU for this helpful write-up. In my case the difference was 24 cents - Woohoo! And I tried variations as well without issues.I do use "self-referencing categorization" @q_lurker on an infrequent but carefully consistent basis for certain specific transfers that I prefer to keep them standalone (no matching links) and that has not created any issues.
- QWin Deluxe user since 2010, US subscription on Win11
- I don't use Cloud Sync, Mobile & Web, Bill Pay/Mgr0 -
Verry Interesting. (R&M LI catch phrase for those of you old enough).
I got through 12 of 14 Investment accounts before finding a discrepancy. Actually finding a discrepancy surprised me. In that one account, the discrepancy was significant, in excess of a hundred grand. In this case, the Net worth report was 'off the shelf', including all non-hidden, non-separate accounts, all securities, unrealized gains, etc. - all default settings.
I first determined what the specific discrepancy was. I then went to a portfolio view for that account, scanned the list of 11 securities and saw that the market value of one security was $0.01 off from my computed discrepancy. It appears the Net Worth report is (for me) omitting one security. FWIW, this particular security is only held in this one account.
So I went to customize the security list for the Net Worth report. The 'omitted' security does not appear in the list of available securities to include or exclude. Hmmm? The security is not set as hidden. No clue as to why that one security has been omitted and is not included in the customization list for the report.
This does seem to confirm your observation that there is an issue with the report rather than with the raw data. Oddly, if I use the Account Balances report (rather than Net Worth), the report shows the correct current balance for this account and all other accounts. But that report also omits this security from the customization list.
I toggled the Hide status for the security to hidden, No change. I toggled it back to not hidden. No change.
So I exited Quicken and restarted. Wow. The Net worth report now comes up with the correct value, those this security is still omitted from the customization list. FWIW, I had previously opened the file from within Quicken where I had a file open, and then opened this file. This time, I was opening this file from scratch. Did that make the difference?
Final steps (for today) - Make a backup copy then validate the current file including rebuild investing lots. Several transactions and accounts "repaired", but none obvious to this account and security. No security issues reported. This particular security still missing from that particular report customization. Net Worth report still accurate (as it was after 'from-scratch' start).0 -
BK I think you should sleep very well at night with a difference of only $0.24 !2
-
As of yesterday's prices, we only have a $0.01 difference attributable to rounding.
The Account Bar appears to sum the rounded holdings of an account (like the account's Holdings view) where as the Net Worth report appears to round the sum of the holdings of an account.
Note: If customizing the Net Worth report for specific securities, do not forget to include No Security (includes Cash).1 -
Interesting analysis. In my case the discrepancy is about $6,332, and appears to be somewhere in the income and expenses. I may still have some cash balance adjustments from many years ago that I have not corrected.QWin Premier subscription0
-
I did this and my difference was about $650 for all date/transactions.
The first thing I would like to note is I really don't see the need for Excel.
If you set the interval to None then you can just look at the to overall totals and see if they are different.
Next is my hunt for that difference.
The first thing to realize is that whereas on the Net Worth/Balance report the starting date isn't important, only the ending date. But on the Income Statement you need to have from the start of your data to whatever date you pick for the end of both of them.
I actually started with checking with 12/31/1996 (the first year of my data), and then moved through each year, but one could do a binary search (dividing the periods in half until you find the right half), but note that the difference might be spread out over many "periods".
Anyways as I progressed through the years I was just off by a few cents and up to about $1 and for these I just ignored them mostly because it just isn't that important to me to find them. And I was really only looking for my big difference and what might be causing it.
Well I got to 2020 and that is where the big difference kicked in. So from there I did do a binary search to locate the problem down to one day (5/1/2020).
Checked what was changing and determined that the non investment accounts were fine.
The only investment transaction on 5/1/2020 were my manual entries for my three Lending Club account (about 5 transactions each). So all I did is go into each account and change those transactions from 5/1/2020 to 5/2/2020 and after each account check the difference again. As it turns out the difference was in the last account. Once I did that the difference dropped to $1.19.
So I then changed the dates back to 5/1/2020, and now the difference is $1.19.
Obviously this doesn't point to anything wrong with the categories or the way it was entered.
This is a Quicken "database entry glitch".Signature:
This is my website: http://www.quicknperlwiz.com/1 -
Update: I went through everything as @Chris_QPW describes and found two issues:
-- A corporate spinoff in 2001 in a 401(k) account that had been entered as a Return of Capital followed by a Bought. I deleted the ROC and replaced it with a Misc Income and that discrepancy goes away. I did not try re-entering the ROC; that might have fixed it as well.
-- An improperly resolved Placeholder in 2003. I had entered a missing bought transaction prior to the Placeholder, causing a Balancing Cash Adjustment. I had compensated for this by entering a MiscExp. Deleting both adjustments and the Placeholder fixed the problem.
After those changes, my discrepancy is $1.62, which is close enough for me. All in all it was a very worthwhile exercise and helps improve my confidence in my data.QWin Premier subscription0 -
Thank you for this simple tip @Chris_QPWChris_QPW said:
The first thing I would like to note is I really don't see the need for Excel.
If you set the interval to None then you can just look at the to overall totals and see if they are different.
- QWin Deluxe user since 2010, US subscription on Win11
- I don't use Cloud Sync, Mobile & Web, Bill Pay/Mgr0 -
I am happy to report that my "data integrity" issue has been resolved.
As I mentioned in my second post above, the issue was not one of data integrity but rather a problem with the report writer. This issue appears to be resolved with the recent release of R35.31, build 27.1.35.31. I believe this to be the case because the problem still exists in a file using Quicken R34.24 but not with the upgraded Quicken (on a different machine).
Also as I mentioned above, my problem was related to how unrealized gains were aggregated by the report writer. This morning I ran the following four reports and the unrealized gains in each were in agreement (within pennies):
1. "Portfolio Value Report." Total unrealized gains is shown at the bottom of the report. The sum of the unrealized gains and the cost of the securities equal the total value of the investment accounts under "Investing" in the "Accounts" column of the application.
2. "Spending by Category Report" (an income statement). Subtraction of the "_UnrlzdGain" reported in the top of the expense section from the "_UnrlzdGain" reported at the bottom of the income section equals net unrealized gains. This agrees with the amount of unrealized gains in #1 above.
3. "Net Worth Report" (a balance sheet) was run twice; once with unrealized gains and once without unrealized gains. The difference between the two reports agrees with the unrealized gains in #1 above.
4. "Investment Transactions" report was configured to include only the hidden category "_UnrlzdGain." The report total agrees with the unrealized gains in #1 above.
Finally, the net worth in my balance sheet report now agrees with the net worth in the "Accounts" column.
Thank you Quicken team for promptly fixing this bug.0
