How to verify the integrity of your Quicken file
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
