Best Practices to Reconcile an Investment Account Against Statements

Rich

Quicken Windows Classic Premier, Subscription

Ver. R60.20

I’m seeking guidance and advice on the best practices for reconciling investment accounts against the statements provided by my financial institution.

Between my wife and I, we have a fairly large number of accounts, which include taxable brokerage, IRA, Roth IRA and 401K. Reconciling these accounts requires a significant time commitment and I would like to find more efficient ways to do it.

Here's my current process:

1. **Gather Statements**: I typically accumulate statements for multiple months before I do statement reconciliations.

2. **Review Transactions**: I manually accept downloads and go through each transaction listed in the statement, comparing it with the records in my Quicken account. For each entry that I verify, I then mark it as reconciled. For those that I can’t initially verify, I troubleshoot until I’m able to determine what correction is needed. A couple of these accounts have many transactions each month, which can compound troubleshooting.

3. **Check for Discrepancies**: After reconciling transactions, I check for any discrepancies in the holdings by a line-by-line comparison between the period statement and a printed copy of the Quicken Portfolio Value Report for the as of date matching that statement period. Depending on the account complexity, the frequency of this step may vary from one to three months among the accounts.

4. **Adjust Records**: If I find any discrepancies, I adjust my Quicken records accordingly to match the statement.

While this process works, I find it to be very time-consuming and sometimes prone to human error. I am interested in learning from the experiences of others who may have found more efficient methods or tools to streamline this process.

Specifically, I am interested in:

- **Automated Tools or Features**: Are there any automated tools or features within Quicken that can help with reconciling investment accounts more efficiently? I’m aware that Quicken has a reconciliation tool, but I'm not experienced using it. I’m not clear whether it is limited to comparison only with current Quicken holdings and whether it automatically displays discrepancies. I tried to research whether the as of date can be adjusted to make a comparison, but I found information that seemed to be conflicting. If using it that way is possible, that would be a significant time saving for my process.

- **Best Practices**: What are the best practices you follow to ensure accuracy and efficiency in reconciling your investment accounts?

- **Tips and Tricks**: Any tips and tricks that you have discovered over the years that could save time and reduce errors?

I would greatly appreciate any insights or recommendations anyone could provide.

splasher

My only suggestion involves #1, do it monthly, waiting to do several months at once is a BIG TASK, but monthly is more manageable and not as big a headache.

Chris_QPW

Personally, I'm in Quicken pretty much every day and I trade infrequently as such I never have more than a few transactions to deal with at a time.

But clearly not everyone wants to do it that frequently depending on the how many transactions they have in a given period of time should dictate the frequency they do this. Where the more transactions there are the more frequently, they should downloading/processing them.

When I see someone that has "lots of transactions" and reconciles infrequently I have to wonder what purpose they have in using Quicken for this.

It is sort like the people that come in and want to download the full last year "so they can do their taxes". The process of properly getting those transactions into Quicken and categorized correctly, just so they can import them into their Tax program is many times more time consuming than just using the resources like 1099s downloaded/or entered manually or get reports from their bank or such. The only way Quicken makes it better is if the person is doing it throughout the year, a little bit at a time.

If you have lots of transactions and are reconciling infrequently. I don't see how you can be "checking up on the financial institution" (which is almost never the cause of a reconcile problem, it mostly getting it into Quicken correctly). If it is for taxes, well then what I said above tends to apply. If on the other hand you really don't use this for taxes (just use the 1099s and such) then maybe Simple investing mode is what you might want. It just keeps track of the number of shares in each security, and the cash balance.

As for the procedure I actually use, which might not be suitable for a lot of users because of either their financial institution information is constantly downloaded wrong, or other concerns.

I use the option to import the transactions directly into the investment register. This actually turns off the automatic comparing of the shares to the downloaded ones (sort of a bug in the preferences), but even if the preferences worked right, I would turn it off anyways because I want to do it manually at the time I'm ready to do it, and because at times it will give me a difference in shares because Chase has the transactions and the downloaded share balances out of sync while the transaction(s) are waiting to close (2 day closing period). I also, don't pre-enter transactions. Mostly because I don't want to take the time to do it, but also because if I create a "New Match" there is a bug where in the automatic transaction entry mode you won't be able to see it, because it is shown in the hidden Downloaded Transactions tab.

Anyways my process is:

• Download transactions
• Run "Reconcile Shares" (I have icons in the action bar setup for each of these). If it comes up with that, they are the same, then I have that part taken care of. If there is a difference, I look at what security it is. If it is one that Chase uses for cash that is process for the trade, I ignore it. Otherwise, I will track down the reason for the difference in the transactions.
• It the run "Update Cash Balance". I'm not doing that to actually update the cash balance. In this dialog it will give me what the downloaded cash balance is, and I'm making sure that what Quicken has and the downloaded one are the same. Again, if they are different, I will be looking to see what transaction(s) might have thrown Quicken's off. It also allows me to copy the one from the text box to be using in the next cash reconcile.
• The last step is to run "Reconcile" which is the cash reconcile. I put in the cash balance that I got from the last step and the reconcile dialog comes up and basically, I know that it will reconcile because I have already checked the cash balance. I guess one could enter the online balance even if it was wrong and do the checking of transactions that might be wrong here, but I tend to like to just do it in the register. At this point if it balances, I can accept it and then the transactions are marked reconciled.

BTW doing this more often also helps to get a feel for what financial institutions do wrong. For instance, for my wife's 401K account Merril Lynch always downloads (in a QFX file, which is all that is supported) the reinvested dividends as regular dividends, and as such once a month I have to go in and change the security action for those from dividend to reinvest dividend.

q_lurker

"Best practices" as so much a choice of the individual, I think it is hard to be prescriptive. So much depends on the number of investments, the depth of trading, number of accounts, financial acumen of the user/investor, and the financial record-keeping needs of the user/investor. That said, I understand you are looking for tips and suggestions.

I agree with @splasher that more frequent can make things easier. Even if you are only gathering the statements 'every few months', you can break the process into monthly period. But that also raises the question of how frequent your statements are. Maybe some are quarterly. It depends.

Another major major issue is how the transactions are getting entered. I did not see you identify that. Manual entry — do it more frequently. If you are downloading from the financial institution, you then get into questions as to how accurate you find their downloads.

Another aspect to consider is the pricing of securities. If you update prices frequently, you are assured of catching the correct month-end prices. If you download prices only "every few months" or only via brokerage downloads, you are more likely to miss some or many month-end prices making the month-end reconciliation of security values much more difficult.

Also, an aspect that reconciliation may miss is capital gains on sales. To get that right you need to know what the brokerage did and then match that with the proper Quicken selection. Or if such detail is not so important, you skip that aspect.

You included the statement "For each entry that I verify, I then mark it as reconciled." I do not know exactly what you mean by that? If you are manually changing the Clr status from blank to "R", I would skip that step. I find it slow and clunky. At such time as I reconcile the cash balance (Chris's 4th step above), all those transactions will get their "R" set.

In short summary, I suggest keeping up with investment account transactions on a more frequent basis (at least every two weeks?) even though you may not truly reconcile each time. I do not do any auto-entry or auto reconciliation because I want to know and understand what is going on. That is more personal preference and not a specific criticism of such tools.