How to adjust "Market Value" to match actual?

RCinNJ

In a brokerage account QM shows a negative cash value at the bottom of the Portfolio page that is exactly how much the account is off from its actual value at Vanguard. I've gone through months of transactions and checked every holding against what is shown at Vanguard and can't find where this error comes from. I just want to make an adjustment to remove this negative amount and bring the Market Value up to match the actual value. Since I know the Vanguard information is correct and I will use their info for tax purposes I'm not that concerned where the error comes from, but if anyone knows where to look please let me know.

Over the years I've adjusted values on a few types of accounts using Payment/Deposit but I can't find a way to do it now. Perhaps Quicken made a change that no longer works the same way? I've tried Payment/Deposit and Add Shares but it doesn't change the Market Value or change the negative Cash shown at the bottom. Does anyone know how to do this?
I have included a screenshot to show one attempted correction.

Thanks!

Frankx

Hi @RCinNJ,

Just curious, are you taking any periodic payments (or other distributions) from your Vanguard account and if so, are you recording those in Quicken?  Similarly, this problem could be related to payroll withholdings where your employer may be withholding 401(k), or profit sharing plan, deductions that go into the Vanguard account but those aren't being recorded properly in Quicken.

Frankx

RCinNJ

Frank, Thanks for the ideas. However, there are no scheduled transfers into or out of this account, and I'm able to account for the transactions in and out. Also, no payroll or other transactions which involve withholding. As I said, I'd be pleased if I can figure out where Quicken went off, but satisfied to learn how to make a manual adjustment and move on.

jacobs

Scroll down in your list of transactions to see where the balance turns negative. What transaction(s) led to the negative balance? Did you buy shares of a security? Where did the cash come from for the transaction(s). 

In my case with Vanguard, when I purchase shares of a security, I need to precede that with a sale of shares from my Vanguard money market security, or to transfer cash in from my checking account. 

RCinNJ

Thanks jacobs! Your suggestions have mostly fixed the problem. My reply is long in hopes of giving information that leads to a complete solution, and also in hopes of helping anyone with similar problems who finds this thread in the future. I’ve spent many hours trying to figure this out. This may also reveal my ignorance of how QM works under the hood! Your understanding is MUCH greater and maybe this will suggest a solution to you. Note that I have accounts at both Vanguard and Fidelity and how cash is reported at the two companies (at least by QM) is very different.

Your suggestion to look through the Balance Column got me on track to mostly figure out the problem. Although, looking for a change to negative values didn’t help because the order of downloaded transactions, and the difference between when transactions are reported and when they clear, means the account frequently shows a negative value in the Balance column. In fact I’m not 100% sure what “Balance” refers to, but at Vanguard I believe it is the difference between actual account value as reported by the brokerage house and what QM is aware of (hence the usually temporary positive or negative cash values at the bottom of the Portfolio page)? Note that how QM reports cash at Vanguard and my other brokerage, Fidelity, is totally different. QM reports Vanguard account cash as in their Money Market Fund, but does not show Fidelity’s cash in their Money Market Fund and just puts all cash at the bottom of the Portfolio page— and this is the value shown in the Balance column. When QM is matching correctly and all transactions have cleared, the Cash value at the bottom of the Portfolio page disappears for Vanguard accounts. For Fidelity, since the Cash value is supposed to match the amount of cash in the account, I need to go online to check the actual cash amount and make sure there is no difference with the cash shown at the bottom of the Portfolio page. I don’t know if QM could make changes to have Fidelity’s Money Market Account show instead of putting all the cash at the bottom. I sympathize with Quicken for how complex this is with each bank and brokerage house seeming to have their own system for reporting transactions.

First, I discovered something which may be a bug or a programming choice that could perhaps be changed. I’ve tried for months to track down the problem but never got a match when I entered the difference in the search box (set to All Visible Columns). When I do a search for the dollar amount of the difference ($8,781.30) QM comes up blank. After your post, I realized that QM is not searching the Balance column! However, now I knew to look at the Balance column and I kept seeing the $8,781.30 number repeating. Unfortunately, since QM also does not allow sorting the Balance column to bring all the same numbers together there is no choice but to look through every transaction looking for the first instance. Sorting by other columns does not work because this just scrambles where the difference appears (which may be why there is no option to sort by Balance since the values change with the order). This account goes back years and has thousands of transactions to look through.

I searched manually to find the first instance of 8,781.30 and found it stems from at least three transactions. Most of the difference stemmed from two bank transfers where QM picked up the transaction but not the Transfer part and I didn't notice to fix manually. For some reason putting the correct transfer in the Transfer column fixed this part. However, it is still off by $218,70 and following that back to the first instance in the Balance column does not reveal an obvious (to me) error. 

By making changes I’ve gotten the Market Value on the account to match what Vanguard reports. However, there is still a $218.70 “Cash” value at the bottom of the Portfolio page and in the Balance column. I have gone through every investment in this account and everything matches perfectly between Vanguard and QM. Since every dollar seems accounted for I don’t understand where this $218.70 value comes from and just want to zero it out.  

If I try to correct with a Payment/Deposit manual entry by subtracting $218.70 the Balance column goes to $0 but the cash at the bottom of the Portfolio page doesn’t zero out AND the account balance shown in the Sidebar changes by this amount and becomes incorrect by this amount, BUT the Market Value reported on the top right does not change (i.e. stays correct) for some reason. I have removed this adjustment, and am still looking for a way to remove the incorrect Cash amount without affecting the correct account balance.

Thanks again to jacobs for helping me mostly fix this! Perhaps jacobs or someone else will have a complete solution!

Frankx

Hi @RCin NJ,

Your very comprehensive explanation of your search to balance your account is interesting.  Thanks for taking the time to report this.

So, I have something you need to know.  I am pretty sure that it is not a coincidence that - if you add the two numbers up you mentioned above (i.e. $8,782.30 plus $218.70) you get $9,000.00.  In accounting, differences of this type are almost always caused by transpositions.  So, when a balance is off by a 9 - it is almost always due to a transposition in an entry.  For example when a transaction - is posted as 110,000.00 when it should have been 101,000.00 - there will be a 9,000.00 difference in the account.  It is also the same for a situation when you should have entered a 10,234.56 but you entered a 1,234.56 - the account will be out of balance by 9,000.00.

This is all to say - take a good look at ay transactions where the digit in the "ten thousand position" and the digit in the "thousand position" may have been transposed.  If your review those, I believe that you will likely fix your out of balance situation.

Let me know of you have any followups.

Frankx 

jacobs

RCinNJ said:

Your suggestion to look through the Balance Column got me on track to mostly figure out the problem. Although, looking for a change to negative values didn’t help because the order of downloaded transactions, and the difference between when transactions are reported and when they clear, means the account frequently shows a negative value in the Balance column. In fact I’m not 100% sure what “Balance” refers to, but at Vanguard I believe it is the difference between actual account value as reported by the brokerage house and what QM is aware of (hence the usually temporary positive or negative cash values at the bottom of the Portfolio page)?

I should have offered another tip for skimming the transaction register: click on the Columns icon in the bottom toolbar and activate the Cash In and Cash Out columns. This will make it easier for you to quickly spot money going out, rather than looking for a negative in the Balance column. (You can always de-activate these columns later if you don't need them, in order to save horizontal space in your register.) While you can't sort the register by the Balance column, since that would obviously change the balance because the transactions would be in different order, you can sort by the Cash In and Cash Out columns to  hopefully identify where there's a discrepancy. 

I enter my transactions manually for my Vanguard accounts. The "cash balance" in any of these Quicken accounts is a brief transitory step which doesn't actually happen at Vanguard. The cash balance should always be zero, expect on the day transactions are taking place. Let's say you purchase $10,000 in shares of the Vanguard Index 500 Fund, using funds from the Vanguard Cash Reserves Federal money market fund. At Vanguard, that's shows up as a single transaction on one day: the sale of $10,000 from the money market fund and the purchase of the stock index fund. In Quicken, it's two separate transactions: (1) a Sell transaction from the Cash Reserve fund, which creates $10,000 in cash in your Vanguard account in Quicken; then (2) the Buy transaction in the Index 500 fund, which uses up the $10,000 in cash.

RCinNJ

Frankx and jacobs, thank you both for taking the time to give such detailed suggestions. I have found this very enlightening. I’m still not there but wanted to add some info.

Frank, so interesting about transposition errors, and good spotting seeing that $8,781.30 plus $218.70 = $9,000. There may well be one or more transposition errors although I’ve looked and can’t find them. However, one of the transfers out was for $9000 and this might be where this number comes from. It may be that fixing the $9000 transfer only revealed the $218.70 error. And since I can’t find one $218.70 error this might be a sum of several smaller errors brought about by some manual corrections I’ve made (kludges). I download transactions and only make manual corrections when QM fails in some way to download a transaction or a manual override is needed due to complexity that QM doesn’t handle well (like spin-offs). 

One type of manual correction I’ve made includes when I converted from a Vanguard mutual fund to their equivalent ETF (a non-taxable event). Quicken wants to mark all the previous buys as Sold and create one new buy on this conversion date. However, Vanguard tracks the original cost basis, so I want to keep all the purchase dates and prices in the Portfolio view to give a more accurate idea of gains or losses. Since the fund and ETF trade at different prices, I need to make an adjustment that keeps my purchase history and gains close to accurate. Also, when I have received stock in spin-offs Vanguard doesn’t immediately give a value for the new shares and Quicken marks them with a $0 cost. I apply a cost as close to the actual price as I can on that date to keep the new shares cost basis close to correct. This is so I can better see how an investment is doing and give me an idea of the tax consequences if I sell.

Sometimes I’ve made these with incomplete information because Vanguard can’t report exact numbers until they get information from the fund or stock (which can be weeks and is only available by tracking down the information on their website). I’ve also found, for a few holdings, some small differences between the cost basis that Quicken shows and Vanguard reports. The cost basis shown by Vanguard for one or more holdings is off by a few dollars from what I paid (so Quicken is correct about purchase price). I suspect these have reported either some Return of Capital or Capital Gains distributions which affect the Cost Basis for taxes when eventually sold. I don’t want to touch these values because it also changes QMs calculation of Market Value and I would have to figure out how to compensate to get the Market Value correct again. I’m not going to touch these since they don’t make a material difference and taxes will eventually be paid according to Vanguard’s records not QMs.

Also, I do have the Cash In and Cash Out columns visible but I’m not seeing anything obviously wrong. Since they were visible, and are searchable, it shows that the error amounts were not single numbers in those columns. I think the error is likely either from a transaction that Quicken didn’t download (like a dividend) or more likely one or more manual transactions I created as explained above. But again, all the current values for all holdings, including the Vanguard Money Market fund are correct. At this point it is only the $218.70 shown as Cash at the bottom of the Portfolio page that is incorrect and which I would like to get rid of. If it is from a single transposition I don't see it, so I just don’t know where it comes from or how to make it go away without affecting the Market Value of the account which is now correct. If you have any further suggestions for finding an error do let me know. Still hoping one of you or someone else knows if eliminating this Cash value can be done.

Thanks again!