Quicken Community is moving to Single Sign On! Starting 1/22/21, you'll sign in to the community with your Quicken ID. For more information: http://bit.ly/CommunitySSO

What data fields fill the Account Balances Report in the Investment Accounts section?

One of my 401K accounts shows only negative numbers in The Account Balances Report. I ran it via Reports->Reports & Graphics Center->Net Worth & Balances->Account Balances. The report is set-up to Include All Dates with Interval Month. Under the section Investment Accounts, the 401K account in question is listed (John Hancock) as well as a previous 401K (Vanguard) and an IRA (Vanguard) that it was rolled over to. The Vanguard accounts are fine. The account that shows negatives is the John Hancock account. I wonder if it has to do with the field types the report pulls from? Transactions are downloaded from the John Hancock website (no Express Web Connect). It adds two contribution Deposits and one Bought transaction each week. I have to manually enter a quarterly fee and any dividend transactions. The report appears to calculate by subtracting the deposits, adding the fees & dividends, ignoring the shares Bought - giving a negative monthly balance instead of a positive. The Vanguard account are primarily BoughtX transactions, fees use the Removed Action, and dividends are a ReinvDiv transaction. The columns for both accounts are the same; Share Bal. Inv Amt, Cash Amt, Cash Bal. The BoughtX transaction adds shares to the balance, has an Inv Amt with a + number, a Cash Amt with a + number and a Cash Bal of zero. For the Deposit/Bought transactions, shares are added to the balance, the Deposit creates a Cash Amt with a + number, and the Bought creates an Inv Amt with a + number and the Cash Amt is a - number, which makes sense. After the Deposits and Bought transactions are entered, the Cash Bal for the account is zero. However, their website enters the Bought transaction before the Deposits, adding to my suspicion and desire to know which data fields Q is using to calculate the Monthly balance on the Account Balance Report. Could it be the format John Hancock downloads their data? Why wouldn't a Bought transaction with a + Inv Amt number be used to calculate the account balance? With an Investment account, it appears Q is only calculating the Cash Amt.

Thanks in advance for looking at this for me.
Karen             





Best Answer

  • Quicken Sarah
    Quicken Sarah Administrator, Moderator mod
    Accepted Answer
    Hi Karen,

    I'm sorry to hear that the validate process stalls when checking stock splits and thank you for posting back with the updated information.

    If you haven't already, I would recommend creating a copy of the file through the File menu > File Operations > Copy command.  Then open the copied file and try to run the Validate & Repair tool once more.

    Copying a file through the File > File Operations > Copy, essentially rebuilds the internal database tables and list data in the backend of the file and may help to correct the data so the validate and repair tool can complete.

    Please let us know how it goes and if the copied file still fails to complete validation.

    Thank you,

    Sarah

Answers

  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    edited April 2019
    I would start by going to the problem account and clicking the Holdings button. Are the share balances correct and the Cash zero and Total market value correct?

    Are there Placeholders in the account? Go to Edit > Preferences > Investing Transactions and make sure Show hidden transactions is checked.

    If that looks OK, does the Net worth report look OK for this account? It should be the same but I think it is calculated differently.

    Also in the Account Balances report click the gear at the top right click on the Seurities tab and make sure all securities, including No Security (includes cash) are selected

    And in the Account Balances report if you set the interval to None is the final balance OK?

    I have more ideas based on your answers....
    QWin Premier subscription
  • Karen
    Karen Member ✭✭
    Thanks Jim,
    I wrote a reply last night but it is not posted here. :(

    The Share balances are correct, the Cash is zero, the Total market value is correct, there are no Placeholders. No previous issues with Net worth reports. All Securities, including none, are checked.

    I closed Q last night and upon reopening it, the Account Balances report now has positive numbers for the account in question. However, for the months of Feb-Nov of 2018, the report is only providing the Cash balance - until Dec where I see the Cash bal and Share bal are combined to provide an accurate number. And, so far in 2019, the accurate balance is being reflected on the report. The graph & report are therefore inaccurate for most of the months of 2018. Data entry began in Feb 2018. 

    I can run an accurate Transaction report with Deposits & Buys in 2018 for this account. I can run an accurate Net Worth report with No Interval.

    A few months ago I was having trouble entering a transaction that was accidentally deleted (or missing) for Shares Bought with Contributions Deposited in Feb 2018. Q stops responding, the program needs to be closed in Task Manager and then reopened. I, instead, entered a transaction to Buy the missing Shares for the same date in 2019 (2/12/19). This fixed the Cash Balance.

    I suspect a bug was fixed and any data prior to Dec 2018 in a 401K is fixed because I can change data in a checking account without this problem. Could this now explain the Share balance missing on the report? My Vanguard 401K Transactions (downloaded) don't have this issue. 




  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    Hi @Karen,

    I have also had a problem occasionally with replies not posting here. I have taken to copying my reply to the clipboard before posting in case the reply goes into the bit bucket!

    Have you tried validating you data, in case there is some damage to your file?

    Go to File > File Operations > Validatate and Repair, check Validate file and OK

    Reading your response carefully, it sounds like you have told Quicken that you deposited cash in Feb 2018 but did not buy the shares until 2/12/2019. If that is the case, then of course the shares will not be in the account until Feb 2019. To fix this, you should be able to edit the Feb 2019 Buy and correct the date to Feb 2018.
    QWin Premier subscription
  • Karen
    Karen Member ✭✭
    Catching back up to you Jim.

    I ran a Validate on the file (my partner's) and ran into a snag. It stops running Q at Checking Stock Splits. I have to close the program via Task Manager in Windows. I decided to check a few other things but have not been able to get Validate to run on this file. I have another file (my own) and it runs fine. I even found a few anomalies that were minor repairs.

    Any suggestion on how to attack this at this point? There are no stock splits in my partner's file. In the Investing section, I'm dealing with five 401K/IRA accounts. Three are closed. In the John Hancock account that is showing the problem, and affects reporting, do I need to delete it and start all over?  
  • Quicken Sarah
    Quicken Sarah Administrator, Moderator mod
    Accepted Answer
    Hi Karen,

    I'm sorry to hear that the validate process stalls when checking stock splits and thank you for posting back with the updated information.

    If you haven't already, I would recommend creating a copy of the file through the File menu > File Operations > Copy command.  Then open the copied file and try to run the Validate & Repair tool once more.

    Copying a file through the File > File Operations > Copy, essentially rebuilds the internal database tables and list data in the backend of the file and may help to correct the data so the validate and repair tool can complete.

    Please let us know how it goes and if the copied file still fails to complete validation.

    Thank you,

    Sarah
This discussion has been closed.