Brokerage Account Reconciliation Process

Unknown

I have several brokerage accounts I reconcile monthly. I’m on the latest release of Quicken 2018 Home & Business.



I frequently download transactions and security data from my institutions.



Reconciling is necessarily a two step process. Cash reconciliation and portfolio reconciliation.



Cash reconciliation is reasonably straightforward but to reconcile the portfolio you first have to again set the statement date then compare portfolio values.



If the portfolio values don’t agree then you need to painstakingly go through every security to confirm holdings and unit price to find the error.



Sometimes a portfolio may have different types of securities (stocks and bonds for example). Generally the printed statements gives subtotals for holdings by type. Quicken however offers no such facility as far as I can tell. So users are left to flip all around paper statements to reconcile line by line with the alphabetical ordered portfolio view.



Could you beef This up by creating a brokerage account reconciliation process that you only need to put the date of statement in once and that allows reordering and subtotalling of the portfolio view in order to help more quickly find inaccuracies.



Thank you

Unknown

Even though I agree it would be nice to have what you describe I would like to share a few thoughts on the subject.

One centers around place holders and why Quicken "calls for them".

When you download transactions the financial institution also sends the number of shares in each security (and the cash balance).

When the "Compare account portfolio after download" option is on for a given account (Edit -> Preferences -> Downloaded transactions) Quicken will do a compare after each download and warn/suggest place holders for any difference in the number of shares.

Even though I never recommend accepting a place holder this is the "flag" to let you know that you are now not reconciled with your financial institution.

There is also a "Reconcile shares" menu item on the gear icon menu that does this same test.

By doing this after every download you know that your shares are constantly reconciled.

And these days I have taken to just go ahead and do the cash reconcile too.  So basically I'm always reconciled and as such don't have to go back to some statement with a lot of transactions on it.

And one more note, always do the share reconcile before the cash one.

Jim_Harman

[Edit - QPW beat me to many of the comments below]

That is an interesting idea, but may be difficult to implement, because in my experience each brokerage has a different way of grouping the securities, and sometimes even variations on the name for the same security - is it Home Depot or The Home Depot Inc.? Also some include tickers in statements and some do not.

If you are downloading from the brokerage, you can go to Edit > Preferences > Downloaded transactions an next to Compare Account Portfolio after Download, click on Edit Settings and select the accounts you want to compare. With this setting, Quicken will compare its holdings to your online portfolio every time you download and identify any discrepancies. Rather than letting Quicken create any Placeholders, I resolve the discrepancies (usually due to a round-off error) before proceeding. This will keep Quicken's data in agreement with the FI as you go, rather than waiting till there is a statement.

Also at any time you can click the gear at the top right of the transaction list (register) and select Reconcile Shares. and it will compare Quicken's holdings to the downloaded numbers.

If you want to compare to a paper statement, you can make sure your downloads are up to date then click on Holdings and set the As of date the to the statement date. For me the account value usually agrees with the statement to the penny so there is nothing further to reconcile.

If there is a discrepancy, then you do have to compare the share counts for each security, which can be tedious. For me it is usually easiest to print out both lists and check them off as I go.

One trick that sometimes works if you are looking at the account online is to sort the holdings in value order from largest to smallest and put the list in Quicken in the same order. That reduces the amount of jumping around in the lists.

If the share counts agree and there is still a small discrepancy in the balance, I don't generally bother tracking down small price differences. If you want to do this, you can still take advantage of the ordered list as mentioned above.

Unknown

Jim Harman said:

[Edit - QPW beat me to many of the comments below]

That is an interesting idea, but may be difficult to implement, because in my experience each brokerage has a different way of grouping the securities, and sometimes even variations on the name for the same security - is it Home Depot or The Home Depot Inc.? Also some include tickers in statements and some do not.

If you are downloading from the brokerage, you can go to Edit > Preferences > Downloaded transactions an next to Compare Account Portfolio after Download, click on Edit Settings and select the accounts you want to compare. With this setting, Quicken will compare its holdings to your online portfolio every time you download and identify any discrepancies. Rather than letting Quicken create any Placeholders, I resolve the discrepancies (usually due to a round-off error) before proceeding. This will keep Quicken's data in agreement with the FI as you go, rather than waiting till there is a statement.

Also at any time you can click the gear at the top right of the transaction list (register) and select Reconcile Shares. and it will compare Quicken's holdings to the downloaded numbers.

If you want to compare to a paper statement, you can make sure your downloads are up to date then click on Holdings and set the As of date the to the statement date. For me the account value usually agrees with the statement to the penny so there is nothing further to reconcile.

If there is a discrepancy, then you do have to compare the share counts for each security, which can be tedious. For me it is usually easiest to print out both lists and check them off as I go.

One trick that sometimes works if you are looking at the account online is to sort the holdings in value order from largest to smallest and put the list in Quicken in the same order. That reduces the amount of jumping around in the lists.

If the share counts agree and there is still a small discrepancy in the balance, I don't generally bother tracking down small price differences. If you want to do this, you can still take advantage of the ordered list as mentioned above.

That happens to me all the time.  I generally type in messages that take a long time to compose, and others are much faster on the draw than me.   :-)

K.O. (Win-Premier)

I am a bit confused by the original post.  I reconcile one of my brokerage accounts every month.  I enter the cash balance and then I click off every transaction in Quicken that matches a transaction on my statement.  Since for this account (Schwab) I also download transactions if everything goes well all the transactions are already check marked (i.e. cleared) when I start the reconciliation process and it is easy.

Using this process, if it is done every month then the overall share balances are accurate for the account and nothing further needs to be done.

Note this does not reconcile the overall account value as that is dependant on the value of each security at the time of the statement close which doesn't make much sense as the individual prices of each security is not dependant on any transaction and just a function of the market or what Quicken has stored as the security price.  If you are trying to verify security prices downloaded by Quicken that isn't really related to reconciliation of the transactions in your register.

mshiggins

Have you looked at the Portfolio? It has 18 fully customizable views and each of those views can be grouped by account, industry, security, security type, investing goal, or asset class.

Jim_Harman

One other thought:

If you want your securities grouped the same way as your FI groups them, you can go to Edit Security Details in Quicken and set the Security Type to match the FI's categories if they don't already. You can create your own types if the built in ones don't match the FI's.

Then you can set up an Investing > Portfolio view or Portfolio Value report to include just the account you are working with, set the As of date to match the statement, and group by security type to make the view match your statement.

This even subtotals by type as you requested!

splasher

I believe that there are only two important # for each security, basis and # of shares.  Trying to compare a security at Market Value (Q vs brokerage) requires that both sources of information have the exact same quotes, do you really care about that?  It just makes the process harder.

As QPW stated, Quicken does the # of shares compare at each download.  For the basis you have to make sure that you enter the correct total cost when the security is purchased.

Jim_Harman

splasher said:

I believe that there are only two important # for each security, basis and # of shares.  Trying to compare a security at Market Value (Q vs brokerage) requires that both sources of information have the exact same quotes, do you really care about that?  It just makes the process harder.

As QPW stated, Quicken does the # of shares compare at each download.  For the basis you have to make sure that you enter the correct total cost when the security is purchased.

Of course you need the purchase date of each tax lot also.

splasher

splasher said:

I believe that there are only two important # for each security, basis and # of shares.  Trying to compare a security at Market Value (Q vs brokerage) requires that both sources of information have the exact same quotes, do you really care about that?  It just makes the process harder.

As QPW stated, Quicken does the # of shares compare at each download.  For the basis you have to make sure that you enter the correct total cost when the security is purchased.

Agreed.  I was tunnel-visioned on reconciling.

q_lurker

splasher said:

I believe that there are only two important # for each security, basis and # of shares.  Trying to compare a security at Market Value (Q vs brokerage) requires that both sources of information have the exact same quotes, do you really care about that?  It just makes the process harder.

As QPW stated, Quicken does the # of shares compare at each download.  For the basis you have to make sure that you enter the correct total cost when the security is purchased.

there are only two important # for each security, basis and # of shares

IMO and depending on at what stage the user is on, I would consider current market value equally important.  In a reconciliation process, if my broker tells me the portfolio is worth $1000 and Quicken says it is worth $900, I want to identify the difference - and that is a market value question, not a basis question.

splasher

splasher said:

I believe that there are only two important # for each security, basis and # of shares.  Trying to compare a security at Market Value (Q vs brokerage) requires that both sources of information have the exact same quotes, do you really care about that?  It just makes the process harder.

As QPW stated, Quicken does the # of shares compare at each download.  For the basis you have to make sure that you enter the correct total cost when the security is purchased.

But if I know my quantities are correct, then I look at quotes, the MV will only be wrong if the quote is wrong and there are generally less digits in quotes so they are easier to compare, at least for me.

mshiggins

splasher said:

I believe that there are only two important # for each security, basis and # of shares.  Trying to compare a security at Market Value (Q vs brokerage) requires that both sources of information have the exact same quotes, do you really care about that?  It just makes the process harder.

As QPW stated, Quicken does the # of shares compare at each download.  For the basis you have to make sure that you enter the correct total cost when the security is purchased.

The Portfolio can display market value, cost basis, and share balance. Sliced and diced any way you like it.

Jim_Harman

splasher said:

I believe that there are only two important # for each security, basis and # of shares.  Trying to compare a security at Market Value (Q vs brokerage) requires that both sources of information have the exact same quotes, do you really care about that?  It just makes the process harder.

As QPW stated, Quicken does the # of shares compare at each download.  For the basis you have to make sure that you enter the correct total cost when the security is purchased.

Those 100,000s of shares are hard to keep track of, eh?

BTW keeping round numbers of shares and not reinvesting distributions helps keep things simple and reduces round-off issues too.

mshiggins

splasher said:

I believe that there are only two important # for each security, basis and # of shares.  Trying to compare a security at Market Value (Q vs brokerage) requires that both sources of information have the exact same quotes, do you really care about that?  It just makes the process harder.

As QPW stated, Quicken does the # of shares compare at each download.  For the basis you have to make sure that you enter the correct total cost when the security is purchased.

There should not be any rounding errors if you enter the distribution reinvestments with the correct share amounts.

Jim_Harman

splasher said:

I believe that there are only two important # for each security, basis and # of shares.  Trying to compare a security at Market Value (Q vs brokerage) requires that both sources of information have the exact same quotes, do you really care about that?  It just makes the process harder.

As QPW stated, Quicken does the # of shares compare at each download.  For the basis you have to make sure that you enter the correct total cost when the security is purchased.

There should not be any rounding errors if you enter the distribution reinvestments with the correct share amounts.

Please explain that the T Rowe Price!

All my 401(k) transactions are downloaded and every few months there is a mismatch of .0013 shares or so, sometimes plus, sometimes minus. I have to go to the offending Reinvest transaction and adjust the share count by the right amount to make it balance. I suppose I could just ignore the difference if it is small, but I am picky.

And they call all the distributions ReinvInt rather than ReinvDiv, not that it matters in a tax deferred account.