Quicken Portfolio Not Matching Brokerage Statement

My brokerage statements have been matching to the Quicken Portfolio Summary until this month. For this month, even the ETFs match OR they are exactly double. Cash is also off. Naturally the total is off. What would be causing this?
«1

Answers

  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    I'll assume the "Quicken Portfolio Summary" is what you see when you click Quicken's "Investing" tab, click "Portfolio", then select "Value" from the drop down menu in the "Show" box.
    What's unclear is if you only have one "Investing" Account that you're looking at on this page or if you have multiple Investing Accounts.
    If you go to the Account or Accounts themselves and click on the Holdings button, are you seeing the same thing?  Without more to go on it seems like there's a couple of obvious possibilities here:
    1. There's specious or spurious entries in your transaction lists - maybe duplicated "Buys" or some other entries, or
    2. You have some sort of data corruption in your file.
    Have you reviewed all the transactions in you Investment Account or Accounts to spot possible problems?
  • gksmith5
    gksmith5 Member ✭✭
    Thank you for your reply. But I am very confused! I do have several investment accounts, but the ones that do not tie are two from TDA. My other accounts seem to tie. Yes, I click on the "Investing" tab, then click "Portfolio", but I then click on "Holdings", believing this should show them same as my TDA statement segment "Holdings Detail". I change the date to tie to the statement date.

    Two factors definitely confuse me:
    1) Two months ago, when reviewing the statements, the account detail of the statements tied to the penny of most of the Quicken holdings, except for one, which I deleted. But now when I do the same process, not all holdings tie to each other. Some are to the penny, some are exactly double, some are just in the ballpark. Furthermore, I cannot go back in time and get the same comparison that I once had. So something has occurred in the interim between the two times that I compared the statements to Quicken reports.

    2) Quicken shows one holding in one account that the TDA statement does not show. Where would this come from? If I deleted this holding, the total would be closer, but not exact. It is a true company, but one that is not owned per the TDA statement.

    Today I did a file repair, investment repair, and rebuilt the investment history. My investment total in Quicken is now closer to reality, but variations still exist.

    Any further suggestions or ideas? This is really frustrating as I am losing the confidence I once had with the Quicken investment reports.
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    When you say you cannot go back in time and get the same comparison, do you mean that Quicken will not let you change the "As of" date, or that when you change the date to a date when the Quicken data agreed with your statement, it no longer agrees?

    Do you download transactions from TDA, or do you enter the transactions manually? If downloading, did you start downloading recently, and did the problems start around that time?
    QWin Premier subscription
  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    edited June 2021
    "Yes, I click on the 'Investing' tab, then click 'Portfolio', but I then click on 'Holdings', believing this should show them same as my TDA statement segment 'Holdings Detail'".
    There's no way to click on a "Holdings" button in the manner you describe so I assume that you have gone into each TDA Account and clicked the Holdings button in the upper left hand corner, right above the transaction list:
    ( You could also see the correct detail for each Account in your file by clicking the Investment tab, then Portfolio, and choose "Group by" Account.)
    You are correct that if the date selected in the Holdings window is the same as the date of a statement received from TDA you would expect them to be the same.  (Though the don't have to be exactly the same; I've encountered occasional timing differences between the two.)
    So that brings us to the other obvious possibility I mentioned previously, namely that there are transactions in the transaction list that either duplicate other transactions or transactions that are missing, or something else.  So now you should determine exactly which security holdings are misstated.  It could be "wrong number of shares", "wrong quote", or a combination of the two.  If the quote alone is wrong, that's easily fixed by changing the quote.  If the share number is wrong - maybe combined with an incorrect quote - then you have to find what's wrong with each of those securities' transactions.  An Investment transaction report, run for each security and from the period where the Accounts were properly stated until the TDA statement date should reveal the problem.
  • gksmith5
    gksmith5 Member ✭✭
    Jim Harman - First to answer your questions. 1) Yes, when I change the date to a date when the Quicken data formerly agreed with my statement, it no longer agrees. The results are different. I checked again to verify this, as I have printouts from when I was trying to troubleshoot another problem I had a couple of months ago that you helped me with that I thought I had solved. I made a printout today with the same settings a couple of months ago with the same date setting. Some securities show the same, some additional securities show now, some securities show different share numbers, some securities show double shares compare to the previous number, and the Market Value of the security is not necessarily double (but sometimes is) even if the shares number showed double. In reality, very little correlation exist between the two printouts, value wise, with the same settings. The accounts are mutual funds with over 20 securities in each account.

    2) TDA accounts are downloaded automatically and I have done this for years. I have not compared the statements with the Quicken values on a regular basis, as in the past I always found very close correlation in values. A couple of months ago, I found a problem that I worked with you on, where Quicken showed one security that my TDA statement did not show. I was able to delete it and my investment values became very close to each other (TDA compared to Quicken). But this past week, I noticed that Quicken was showing a much higher total for my TDA accounts that what their statements for May31 showed, hence my deeper investigating and this posting.

    Tom Young - I click on the Investment button in the menu bar, then Portfolio. A dropdown box titled "Show" opens that gives multiple options, of which Holdings is one, Historical Performance is another, along with many others. You mention something similar to this in the statement you made in parenthesis. It is baffling that some of the securities show negative shares and negative values. Yes, I can open each security and examine the transactions, but each account, since it is a mutual fund, shows over 20 securities and each security shows many transactions, so this seems like a daunting task. Consequently, I am searching for a feasible way of solving the problem for these two accounts. I have three other accounts that are not having this problem.

    Any ideas from anyone??? Thanks!
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    OK, instead of looking at the Investing > Portfolio > Holdings view, I suggest you work on one account at a time.

    While viewing the account's Transaction list (register), click on the Holdings button at the top left, just below the account name. This will open a window that is similar to the Portfolio views, but it is limited to just the securities in the current account. This is what you should be comparing to your statements. You can change the As of date just the way you do in the Portfolio views.

    As in our earlier discussion, please be sure you are not confusing Accounts, Securities, and Mutual funds. An Account in Quicken can hold multiple Securities. A Mutual Fund is a type of security. Stocks and ETFs are other types of securities.

    To avoid confusion, you should name your Accounts to match the name of the financial institution and the type of account, like "TDA Brokerage" or "My Vanguard IRA" and not to match the name of the the first security you bought in that account, even if it is the only security in the account.

    It is possible to have an Account that can only hold one mutual fund, but it doesn't sound like that is your issue. 


    QWin Premier subscription
  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    In your original post it seemed things were OK "until this month", suggesting that there was some problem with transaction in "this month", but your most recent post now makes it clear that prior months have been affected too. 
    It might be difficult at this point to recover but I'd suggest taking one or two of the securities that are "off" and stepping back a month at a time - setting the "As of" date in the Holdings window to earlier and earlier statement dates - to try and determine if the Holdings window number agreed at some point for these securities,  If you do find a date where things match then try running an Investment Transactions report for the subsequent month - one security at a time - and compare these transactions to that month's statement from TDA.  That might shed some light here as to what's going on.  Post back with the results.
    Have you, by any chance, recently deactivated and then reactivated either of the Accounts for downloading.  Duplicated Buy entries might explain what you're seeing.
  • gksmith5
    gksmith5 Member ✭✭
    Jim - I do name, and always have named, the accounts differently per the brokerage and the holder (me and my wife) of the accounts, as you suggest. I have been guilty in the past of calling securities as accounts, but I think I have broken this habit. I am guilty of considering mutual funds and ETFs as securities, but are they not? My accounts are managed retirement accounts and consist of a combination of mutual funds, ETFs, bonds, etc. But they all appear as securities in Quicken reports. Perhaps I am missing something that I should be aware of.

    I have always looked at the portfolio view, but I did as you suggested and looked at holdings under the individual accounts. The results are exactly the same as I see under the portfolio view, as one would suggest.

    Tom - Yes, I opened this discussion upon only looking at the past month's statement and comparing it to Quicken. But, as I investigated more deeply, I found more discrepancies, hence further discussion regarding past months. I should try your suggestion trying to go back in time and find a matching statement and Quicken report, but this will take time that I will have trouble finding. It seems quite daunting with the number of securities in each account. But I do respect your suggestion.

    To both Jim and Tom - what would happen if I deleted the TDA accounts and then reopened them? Regardless of what Quicken shows, I have to take the brokerage statements as "Gospel" for valuation. I would hate to lose history, as I have used Quicken for 20 years, but I have only downloaded my investment accounts only five years or so. And I believe that I saw that Quicken does rebuild history for five years from downloaded information. Comments?

    I certainly appreciate the willingness of both of you to help me with my problem!
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    gksmith5 said:
     I am guilty of considering mutual funds and ETFs as securities, but are they not? My accounts are managed retirement accounts and consist of a combination of mutual funds, ETFs, bonds, etc. But they all appear as securities in Quicken reports. Perhaps I am missing something that I should be aware of.

    There is nothing to be guilty about there. Mutual funds and ETFs ARE securities. My issue was when you said, "The accounts are mutual funds with over 20 securities in each account." I think you meant to say "The accounts hold mutual funds and other securities, with over 20 securities in each account." 

    The reason I suggested that you click on the Holdings button in the account rather than using a Portfolio view is that the Portfolio views can be customized so that for example they do not include all of your securities. The account Holdings button will always show all the securities in the account, and only that account. This eliminates one source of confusion.

    It would be a bad idea to delete the TDA accounts, because for example that would cause transfers into and out of those accounts to go to "Unknown account" or something like that.

    Financial institutions vary in how much historical data they download when you first connect to Quicken. Some only do 90 days or so, others a year or more. Quicken's rebuilding process is to correct its internal data and download quotes, it does not re-download transactions.
    QWin Premier subscription
  • gksmith5
    gksmith5 Member ✭✭
    In exploring my investment accounts from their individual accounts, I noticed that that was a method for reconciling the accounts. I tried doing this, using the cash balance from my TDA statements and accepting all entries. I was surprised at the results. It definitely did not help the situation! My account became lower than the statement whereas my wife's account zoomed upward about 60%, make the Quicken deviation from the statements even worse! Am I misunderstanding the purpose of reconciling investment accounts? I had never done this before as I had never found the option. Fortunately I made a backup of my files before doing this. Again, comments? I am feeling more ignorant all the time!
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    Unfortunately as you have learned, reconciling will not fix the problems you are seeing. 

    Have you gone to Edit >Preferences > Investment transactions and made sure that the "Show hidden transactions" box is checked? This will reveal any Placeholders in your accounts that may be affecting things. If you allow it to, Quicken will create these Placeholders after you have accepted the downloaded transactions in an attempt to force your share balance of each security to match the balance reported by the brokerage. 

    You will need to resolve any Placeholders in the process of repairing the transactions in these accounts.
    QWin Premier subscription
  • gksmith5
    gksmith5 Member ✭✭
    Jim - Yes you are correct in what I meant to say and I understand the difference. I will follow your advice and NOT delete the accounts, as I was concerned about the ramifications. But, how do I get back to a correct situation? Can I somehow make manual adjustments to tie to an existing statement? Almost every security does not tie to the TDA statement.

    Is there a way of showing stock symbols in viewing the holdings from the individual accounts? I could add this feature to the portfolio view.
  • gksmith5
    gksmith5 Member ✭✭
    Yes, I have gone to Edit>Preferences... and the appropriate box for hidden transactions was checked. But, not knowing what to do with placeholders as they appeared in the past, I have deleted them. There have not been that many at a time, at least not nearly as many as the number of securities that do not tie to the statements. But, I now realize that I need to learn what to do with them. How do I go forward from here?
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    What you can/should do depends on whether you just want to patch things up and go forward, or whether you want Quicken to track your cost basis and performance accurately. To track the cost basis and performance in Quicken, you will have to find and correct the incorrect transactions and/or Placeholders in the account.

    There is no way to add or remove columns from the account Holdings view. What I ended up doing was to rename my securities so they begin with the symbol - so instead of a fund being called "Vanguard 500 Index Fund Admiral Shares" for example, it is called "VFIAX - Vang 500 Admiral" 
    QWin Premier subscription
  • gksmith5
    gksmith5 Member ✭✭
    I am in too deep to correct past mistakes, but I would like to learn my lessons and be more accurate from now on. So, how do I patch things up now? It seems like that is what I have to do from the practicality standpoint.

    I did not realize you can rename the securities. I will have to try that.
  • gksmith5
    gksmith5 Member ✭✭
    I am discovering that I do have many duplicate entries. One security had over twenty, many of which occurred on a monthly basis. Other securities might only have one. But, when I delete the entries, the market value of the security ties. In retrospect, I deleted 135 placeholders earlier, with them labeled as duplicate entries. I suspect that this is definitely tied to what I am currently discovering. What is probably causing or has caused these duplicate entries?
  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    "What is probably causing or has caused these duplicate entries?"
    This is only a guess, but my guess is some funkiness in the OFX log you're downloading from TDA.  That is, a transaction involving a purchase of shares, e.g. a dividend reinvestment, is sent to you but the security balance sent in the same download has not been updated for the most recent purchase.  So Quicken adds the newly purchased shares to its internal balance, then compares that number of shares to the erroneous number of shares TDA says you own, a figure that's incorrect because its the number of shares you owned before the purchase
    Quicken proposes a "Placeholder" and you, not stopping to think "Why is this happening?" accept the placeholder, or let Quicken automatically make all entries "for you."
    Lesson learned: NEVER ACCEPT PLACEHOLDERS.  More generally, (in my opinion), never let Quicken automatically accept downloaded transactions.
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    Have you been entering the transactions manually and then downloading, expecting the downloaded transactions to match the ones you have entered, do you review each downloaded transaction before accepting it, or do you automatically accept the downloaded transactions, or what?

    Knowing what process you have been using would help to unravel the reason for the duplicates.
    QWin Premier subscription
  • gksmith5
    gksmith5 Member ✭✭
    I am making headway! But still have problems and questions. To answer Jim - I have never made manual entries into the investment accounts, at least not recently. I attempted this years ago and gave up. The duplicate entries are since I have started the download process and thought I was having great success with it. They range from 2017 to present. I see that a good share of the duplicate entries (but not all in any way) occurred from a download that took place about a week ago, 6/21/21. I cannot tie that date to anything that I have done, as I frequently access Quicken (almost daily) and frequently do a "One Step Update".

    I am following your suggestion, Tom, regarding finding the duplicates. And I am learning to not ignore placeholders. I think I am finding what the placeholders were trying to tell me. But I feel that I must disagree with your statement of not doing downloads, as I feel that would be impractical for me. Since these investment accounts are managed, I do not know what transactions take place until I receive the statements. If things go correctly, the downloads should be more accurate, especially if I watch the results more closely and not take things for granted as I have done.

    I now have the following questions:
    1) The two investment accounts I am working with shows a CUSIP, which is a Dreyfus fund. But this fund does not show on the TDA statements. Quicken will not let me delete it. There is not value to it, but a number of shares show, but Quicken would not let me adjust them to zero. Similarly, there is at least another fund that TDA does not show. So, what do I do with it?

    2) Contrarily, the TDA statement shows at least one security that Quicken does not show. How do I handle that?

    3) How do I hand the discrepancy in the cash balance between Quicken and TDA? Is that where the reconciliation that I attempted earlier takes place?

    4) Is there a way of printing the Quicken report of the account holdings? I have done it from Portfolio view, so I have to go to that view to do so.

    You two have been great in responding to my problems! I certainly appreciate it!
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    It sounds like despite saying earlier that you just want to fix things up going forward, you are diving in and trying to find the incorrect transactions in your accounts.

    1 & 2) I think you are still using the wrong terminology, which makes it difficult to follow what you are saying. Do you have more than one actual account at TDA?

    Did you mean to say, "In my TDA account in Quicken, there are two mutual funds that I do not currently hold, and there is one security that shows on the TDA statement but not in the Quicken account."?

    If not, please explain exactly what you are seeing.

    3) Let's start by getting the securities you hold and the share balances correct. Hopefully the cash balance will be correct when you have done that, otherwise we will tackle the cash separately.

    4) I don't think there is any way to print the Account Overview (the page that comes up when you click on Holdings) If you would like to print a matching Portfolio view, make sure that you have customized it to include just the account you are working on and you have selected all securities. As a check, the total Market value at the bottom of the Portfolio view should be the same as Market value at the bottom of the Holdings section of the Account Overview.
    QWin Premier subscription
  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    "But I feel that I must disagree with your statement of not doing downloads, as I feel that would be impractical for me. "
    No, I never said that.  What I said is don't mindlessly accept every downloaded transaction and don't allow Quicken to automatically enter the downloaded transactions into your transaction list. 
    I download most of my security transactions - well all of them really, although some I enter manually before the download shows up for a "match" - but I sequester downloads in the Downloaded Transactions window and and allow them into the transaction list one at a time after determining that the transaction makes sense, or I delete the downloaded transaction(s) and make my own entrie(s).
    "How do I hand the discrepancy in the cash balance between Quicken and TDA? Is that where the reconciliation that I attempted earlier takes place?"
    Once you have your security positions properly stated then if the cash is still off, click the gearwheel in the upper right hand corner and select "Update cash balance..."  That allows you to enter the correct cash balance per TDA.
    As far as your "there's a security in Quicken that's not in TDA and a security in TDA that's not in Quicken" issue I would guess that might be "user error."  Since you're not making manual entries into Quicken, the first time you buy a "new to you" security in the real world and that buy is downloaded into Quicken, Quicken will usually ask if the new security really is one of your already-established security.  it's possible to connect the new security to the wrong security in this process. 
    Go into the "Edit Security Details" window for the security in Quicken that doesn't exist in TDA and untick the "Matched with online security" box and see if you can get the correct security established in Quicken.
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    To build on what @Tom Young said, we recommend that to avoid incorrect transactions, you should go to Edit > Preferences > Downloaded Transactions and un-check the "Automatically add to investment transaction lists" box. This will give you the opportunity to review each transaction before accepting it, to make sure it makes sense, is not a duplicate, etc.

    Also in that same Preferences section, you should click on Settings next to "Compare account portfolio after download" and select all of you investing accounts. With this selection (despite what the Help text says) when you click on Done after accepting transactions, Quicken will compare the share count for each security in the account to the data provided by the broker and report any differences of more than .001 share.

    Do not let Quicken create Placeholders at this point, but rather note the differences and correct the transactions. When you are done making corrections, you can glick on the gear at the top right of the transaction list and select "Reconcile shares" This will repeat the comparison and report if there are still any discrepancies. This will prevent problems going forward.
    QWin Premier subscription
  • gksmith5
    gksmith5 Member ✭✭
    Yes, I will make the suggested changes.
    To briefly answer previous questions from Jim:
    1) I do have two TDA accounts -- one for my wife and one for me. Yes, I have dived in to try to make things proper. But I am only working on my my own account at present.

    2) The TDA account has one American Fund showing 35.966 shares. The Quicken Holding report shows an American Fund security with a different stock symbol with 35.966 shares. Neither security shows on the opposite report. This must be more than coincidence, but it is certainly confusing!

    2A) My most vexing current problem is that the Quicken Holding report shows several securities with negative shares and negative balances. There are six of them in my account. What do I do with them?

    3) Cash balances are still way off, but I am sure it is due to the above comment.

    4) I print out the Portfolio Holding report with all accounts collapsed and then expand the account that I am working on. It works well.

    I hope you can help me with #2A. That is the biggest concern at the moment.

    As always, thank you to the both of you!!
  • gksmith5
    gksmith5 Member ✭✭
    FYI I attempted to make the suggested changes in preferences, but they were already set as you suggested.

    Tom, I misunderstood what you stated, so I appreciate your explanation. Also thank you for the other suggestions.

    Now, if I can take care of the negative shares and values in the Account Holding report, I might be able to see daylight!
  • gksmith5
    gksmith5 Member ✭✭
    FYI I have discovered that the negative balances are also caused by duplicate entries. Once I trace them down, then the security disappears in the holdings report. Most of the problems seem to stem from an update that occurred on 6/21/21. So, I will finish finding these duplicates and see where I stand.
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    2) That sounds like a mismatched security - The actual security at TDA is matched to a different security in Quicken. Are these two entirely different securities, are they different share classes of the same fund, did one fund acquire another, or what? The steps to repair this issue will depend on your answer to this.

    Did you do something unusual just before the 6/21 update - Deactivate/Reactivate or reset the online services for example? That could account for the duplicates.
    QWin Premier subscription
  • gksmith5
    gksmith5 Member ✭✭
    The security at TDA is BFIGX American Funds Inflation Linked Bond Fund and the security showing in Quicken is ABALX American Funds Balanced Funds Class A.

    TDA shows BFIGX being purchased 05/03/21 with current shares number at35.996. ABALX does not appear on the TDA statement.

    Quicken shows ABALX also being purchased 05/03/21 and the same number of shares. But since they are different securities, the costs
    are different. And this purchase appears from the strange 06/21update. Furthermore, an update of 06/26 (which I had never seen before) shows it being sold on 06/25. So, do you think it will self correct? But where is BFIGX?

    I cannot tie 6/21 to anything particular. The only possibility I can think of is that I do most of my Quicken work on the desktop PC in my office. I keep the data file in the cloud. However I occasionally look at my Quicken output on my laptop. Supposedly it should open the same data file that the desktop creates. But I have noticed that this is not always the case. The only thing that I can think of is that I did a OneStep Update from the laptop that created this situation. It is the only plausible possibility that I can think of.

    Things are definitely looking up at the moment for this account if we can get this American Funds mix-up solved. I am within about $1,000 of tying! And best of all, I have learned a lot!
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    You should always keep your working Quicken file on your local hard drive, not on a shared network drive, Dropbox, Google drive, OneDrive, etc. 

    A new option in Quicken lets you create a reference or archive copy of your data file that will be disconnected from any of Quicken's online features. This might be a good option in your case, and should reduce the chance of any corruption. If you save the copy on a shared drive, you should still copy it to a local drive on the laptop before opening it.

    Disclosure: I have not tried this, perhaps others have more experience with it.
    QWin Premier subscription
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    On your BFIGX/ABALX issue, you should go to the Security Detail view for ABALX, click on Edit Details, and un-check the Matched with online security box.

     Then the next time Quicken tries to download transactions for BFIGX, you should see a dialog that asks how to match the security. If you have ever held BFIGX, it should be on the list of suggested securities, or if you have never held BFIGX pick Add Security. You will still have to go back to the existing transactions for ABALX and change them to BFIGX.
    QWin Premier subscription
  • gksmith5
    gksmith5 Member ✭✭
    I will stop using the cloud for my active Quicken data. The cloud is really handy to use, though. What do you think of the cloud as a backup place for the data? I know that security is a concern, but the data is not something that I just ignore. But it would be a source of identity theft.

    I have my wife's TDA statement tied to Quicken to the penny! And I have my account within less than 1%. And most of this 1% is due to the two peculiar securities that are switched. Hopefully this will resolve itself. I unchecked the Match box as you suggested.

    My biggest question at the moment is how to handle the placeholders. Do I delete the ones that I still have? My wife's account shows that 138 still remain! I open them, but I cannot figure out what to do. I deleted the ones for my account early in the game and I show now three placeholders. They often say to replace missing history, but I do not know what is missing.

    I have spent many hours on this, but I believe I have a handle on the situation thanks to you and Tom Again, many thanks!