Stock price reconciliation - Price should be taken from download file from brokerage

mgtblynch

Finally tracked down reconciliation issue with one account (John Hancock Brokerage); the Quicken download from the brokerage contains the actual stock price purchased, but Quicken seems to ignore this value in place of stock price history that may be downloaded from separate processes. The issue causes an imbalance in the reconciliation due to quicken calculating the cost from a different stock price from what the Brokerage is actually purchasing the share.

Boatnmaniac

(Edited.  Disregard my original questions.  I think I figured out what you were saying.)

@mgtblynch - Regarding "3rd party share prices":  Quicken uses a 3rd party source for downloading shares prices during One Step Update or when Updating Historical prices.  These are the prices referred to in #2 and #3 in the Hierarchy above.  These prices are not the ones the brokerages download nor are they prices you might import in a CSV file from some other source.

And, yes, the prices entered with downloaded Buy transactions should not be overwritten by the 3rd party downloaded prices.  You took the correct action to report this problem to Quicken.

Jim_Harman

Is your issue with the cost basis of the shares you purchased or with the closing price on the purchase date? If it is the cost basis, make sure you are entering the total purchase price and the number of shares and commission if any, and letting Quicken calculate the price per share. This should match the cost basis downloaded by JH. Quicken's price history records the closing price for the day if it is available, which of course may be different from the price in the purchase.

If Quicken does not have the closing price for that date, it will use the purchase price.

Boatnmaniac

Per this Support Article How to Update Security Prices [Support Article] the hierarchy for securities prices is as follows:

1. Manual price entry
2. Current Price (Quote)
3. Historical Price (Quote)
4. Price downloaded with broker data through One Step Update or Update Now
5. Import Price (CSV)
6. Transaction Price entered when manually entering a transaction in the brokerage account list. Each entry method overrides the price(s) received by the method just below it. This means that the price displayed after entering a Buy transaction (for example) would be replaced by the price imported from a CSV downloaded from an investing site (such as Yahoo! Finance), which would be replaced by the price transmitted by the broker when performing One Step Update, and so forth.

#2 and #3 are downloaded from a 3rd party that Quicken has contracted with.

The price for the downloaded Buy transaction from the brokerage should be what was actually paid for it at the time of purchase and that will lock in the cost basis. 

But after the purchase the price will vary based upon market changes and, yes, it can be different from what the brokerage downloads because they might be reporting what the pricing is at different intervals.  Quicken is not an investment trading tool so there will always be some variation in this if/when looking at it during trading hours.  But the end of day prices from the brokerage and this 3rd party should be the same.  You are not seeing that? 

mgtblynch

Thanks. The issue is actually in the register entry. Quicken seems to take the number of shares purchased along with the date, but ignores the price paid and the total investment also in the file and inserts their own from somewhere else while apparently recalculating the total line based not on what the broker sent but on the "magical" share price they get from somewhere. This causes the register and totals to be off during reconciliation which is causing massive edits to each line item to correct the balances

Jim_Harman

Do you enter the purchases manually or depend on downloads to enter them? 

Purchases should be entered as Bought transactions, not Added.

Also are these publicly traded securities or special 401(k) securities? Sometimes 401(k)s will use a security with a name similar to a publicly traded mutual fund but a very different price.

Boatnmaniac

To make sure I understand correctly: 

• You have downloaded Buy transactions from your broker.
• The downloaded Buy transactions include the correct share purchase prices from the broker. 
• Then later the broker downloaded Buy transactions prices are being replaced by the 3rd party share prices. 

Is this understanding correct?

mgtblynch

Boatnmaniac....you are correct.

I prefer to download actual buy transactions from the brokerage just lime we do for bank reconciliations, and the brokerage are buying straight up publicly available funds in a 401k account.

mgtblynch

I have documented all aspects of this issue including excerpt of the downloaded fax file and forwarded to Quicken support but have yet to hear any details

Jim_Harman

Did you use the Help > Report a problem link or did you discuss this directly with Quicken Support?

You will not generally get a response from using Report a Problem; they use those submissions for statistical purposes and to help diagnose problems they have heard about directly. If you contact them directly and they agree it is a problem, be sure to get a ticket number for future reference.

Your problem sounds very unusual. I just want to confirm that the issue you are seeing is with the share price and Investment Imount changing in the transaction list (register) changing after the downloaded transaction has been accepted.

If you are viewing the account and click on Holdings, then click on the plus sign next to the security name and find the lot you purchased, does it have the correct number of shares, purchase price, and cost basis, or something different?

mgtblynch

Boatnmaniac...correction...not sure what "3rd party share prices" you were referring too, but the price showing on the transaction in quicken after the download is not the price or the total cost of that transaction from the information within the fax download from broker...so I don't know where quicken is getting that number or how/why they are changing the total transaction cost which should come from the fax data

mgtblynch

Jim_Harman... I used the report a problem option

As for the other request, I will have to wait for the next round of transactions as the edits I had to make are complete and wouldn't be available as you requested. In all instances the share balances are always correct it is only the share price and transaction cost that are wrong and require adjustments

Jim_Harman

Is it a small adjustment that could be explained by a rounding error or maybe a commission or fee, or a larger amount?

mgtblynch

QFX download data: stock A: shares=0.143 unit price=$33.34 total=4.7676

Quicken register: shares: 0.143 @ 33.286713

No idea where the 33.286713 w/6 decimal places is coming from...normal dollar amount is anywhere between a few pennies to .19 off or more on every transaction

mgtblynch

<INVSELL>
<MEMO>American AMCAP Fund R6</MEMO>
<UNITS>-0.1430000000</UNITS>
<UNITPRICE>33.3400000000</UNITPRICE>
<COMMISSION>0.00</COMMISSION>
<TOTAL>4.7676</TOTAL>

<INVSELL>
<MEMO>Fidelity 500 Index Fd</MEMO>
<UNITS>-0.0320000000</UNITS>
<UNITPRICE>143.4600000000</UNITPRICE>
<COMMISSION>0.00</COMMISSION>
<TOTAL>4.5907</TOTAL>

<INVSELL>
<MEMO>Franklin Sm Cap Va Fd R6</MEMO>
<UNITS>-0.0560000000</UNITS>
<UNITPRICE>55.2300000000</UNITPRICE>
<COMMISSION>0.00</COMMISSION>
<TOTAL>3.0929</TOTAL>

<INVSELL>
<MEMO>The Growth Fund of America R6</MEMO>
<UNITS>-0.2210000000</UNITS>
<UNITPRICE>57.1900000000</UNITPRICE>
<COMMISSION>0.00</COMMISSION>
<TOTAL>12.6390</TOTAL>

Boatnmaniac

(Edited.  Disregard my original questions.  I think I figured out what you were saying.)

@mgtblynch - Regarding "3rd party share prices":  Quicken uses a 3rd party source for downloading shares prices during One Step Update or when Updating Historical prices.  These are the prices referred to in #2 and #3 in the Hierarchy above.  These prices are not the ones the brokerages download nor are they prices you might import in a CSV file from some other source.

And, yes, the prices entered with downloaded Buy transactions should not be overwritten by the 3rd party downloaded prices.  You took the correct action to report this problem to Quicken.

Jim_Harman

Here is my guess:

$33.34 is the closing price for that security.
You sold 0.143 shares.
0.143 x 33.34 is $4.76762 which rounds to 4.7676

The $4.7676 you received is truncated to $4.76
4.76 / .143 is a calculated share price of 33.2867132867 which rounds to 33.286713

All this can be explained by rounding and truncation. These differences in calculated share prices are biggest when the number of shares involved is very small.

Remember that when buying and selling, the important numbers are the number of shares and the total price, not the calculated price per share, as long as it is close to the correct closing price for the day.

mgtblynch

I would agree, but for Quicken to be doing it's own rounding and adjusting which is impacting the final sale price which effects the whole reason to use reconciliation, one must not (cannot) rely on the download integrity to offer anything but the option to save a few key strokes if one must adjust every single imported line in order to ensure the total balance remains...balanced with financial institutions.

All, thank you for your quick turnaround and understanding of the issue and dilemma

Greatly appreciate the assistance and technical insight

Jim_Harman

I'm not sure what adjustments you feel you need to make or why.

The important things for Quicken to record correctly are the number of shares you bought or sold and the total amount paid or received. If those are correct, your cost basis should match that reported by the brokerage. The price per share can be calculated. 

Quicken also records the closing price per share of each security in its price history and that is what is used to calculate the end-of-day value of each holding.

Chris_QPW

mgtblynch said:

I would agree, but for Quicken to be doing it's own rounding and adjusting which is impacting the final sale price which effects the whole reason to use reconciliation, one must not (cannot) rely on the download integrity to offer anything but the option to save a few key strokes if one must adjust every single imported line in order to ensure the total balance remains...balanced with financial institutions.

I would say that statement is "highly variable" depending on the financial institution and a few other variables.

There are a few variables going on here.

1. What the financial institution reports on their website isn't always what they report to Quicken.  When this happens they tend to send Quicken prices and shares rounded to a lower amount of digits than they report on their website.
2. There isn't any standard on how many digits to round to.  Different financial institutions round to different amount of digits and there is no way for Quicken to know this, and as such it rounds to 6 digits no matter what the financial institution does.
3. This also depends on the security bought.  Securities that you buy that require a whole amount of shares are unlikely to have much of a problem, but securities have partial shares are much more likely to have rounding problems.

The end result is some people will have more problems than others with rounding errors.  Note usually these are very small, but when in doubt like @Jim_Harman said the most important numbers to get right are the shares and the amount.  The amount isn't going to have the rounding problems, and Quicken actually defaults to changing the price when the calculation using the shares and the amount doesn't give the amount downloaded.  And that should be the user's approach too since having a slightly different amount means nothing in the long term where the prices is always fluctuating.

mgtblynch

Thanks. A couple of assumptions, were made though. I'm only interested in the number of shares and the total amount, not the cost basis. Obviously cost basis is important but if the total amount paid for those shares is off because of wrong share price and rounding, then cost basis is also off if not corrected.

All I'm reporting is when I download transactions, whether the company sends 2 decimals it 6, I would want quicken to either use all the variables the company sends since by contract the company needs to adhere to formatting standards...[should]...otherwise, ensure the number of shares AND the total amount paid are not touched. If the share price needs to be calculated inside quicken fine but that is not the case here. Since my company and I are actively making by weekly contributions to this account and shares are bought with those contributions, then the reconciliation effort should automatically balance to the balances of the account. After downloading transactions from the brokerage the total amount paid for a stock as being displayed in the register does not match the transaction and say there are 5-10 different funds being purchased each time that is 5-10 transactions where the total cost must be changed in order for Quicken to prompt for what the user wants to have adjusted...in your example, the default PRICE...and once again, if every line must be changed after the download then why are we using quicken to auto reconcile? Other than saving a few keystrokes and tabs, the inconvenience and added entries, option selections clicks and pressing Ok is kinda annoying for something that works well in other parts of account reconciliation.

Thanks for all the details and patience, obviously you have helped confirm the issues, whether the issue is right or wrong, and hopefully an option to fix this will be forthcoming for an app that comes as a paid subscription

Chris_QPW

To my knowledge Quicken will use all the digits for the shares sent by the financial institution unless it exceeds 6 digits after the decimal point.

You can confirm what the financial institution sent by looking at the OFX log.
Clearly if the financial institution doesn't send Quicken the exact number of shares you are expecting there isn't anything Quicken can do about it.  It would be up to the financial institution to fix the problem.  On the other hand if the amount of shares is accurate in the OFX log then indeed Quicken Inc needs to fix the problem.

You can open the OFX log by selecting:
Help -> Contact Support -> Log Files -> OFX Log

You and save that file to disk and open it.  You should look from the bottom up to get the latest update and the <UNITS> field has the number of shares.  The <UNIQUEID> is the CUSIP of the security.  If you don't know what security that line up with just search for it in the OFX file.  There is another section in the OFX file that lists the <UNIQUEID> along with the <SECNAME> field to "connect" the two.

mgtblynch

Chris_QPW...Thank you. I'm actually an IT person with a lot of development and analysis experience which is why the level of understanding of the issue has contributed to our exchange. I have looked at the files from the download which is why I'm sure quicken is ignoring portions of the data

I am waiting for the next round of investments to track and document each step before pursuing anything further and to other earlier recommendations I plan to call quicken support directly. Maybe a fix is in our future.

Thank you!

Jim_Harman

mgtblynch said:

<INVSELL>
<MEMO>American AMCAP Fund R6</MEMO>
<UNITS>-0.1430000000</UNITS>
<UNITPRICE>33.3400000000</UNITPRICE>
<COMMISSION>0.00</COMMISSION>
<TOTAL>4.7676</TOTAL>

I think part of the issue here is that the FI is sending units, unit price, and total, but the total does not equal units * unit price. 0.143 * 33.44 = 4.78192, not 4.7676.

It appears that Quicken is truncating the total it is given and dividing by the units to get the unit price. This seems like the right thing to do given the inconsistent data it is provided, except maybe it should round the the total rather than truncating.

If you go to the FI's website, what does it show for the change in cost basis after selling these lots?

It might be easier to see what is going on by looking at a purchase rather than a sale.

Chris_QPW

Oops.  Sorry, I jumped in here not realizing that you had already posted the OFX data.

mgtblynch

Just FYI...all referenced transactions provided and in error thus far are Buy transactions. I do not believe we should be concerned at this point with the cost basis. I'm only concerned with the register number of shares and beginning balance & ending balance at this point, as all downstream measures will be based on the entry point of the data...in this case the number of shares and total $$$ of transaction. I'm not too concerned what is used as the calculated share price (if the share price is taken from the FI correctly or if quicken gets it from another source...) Maybe the answer is a register or download parameter value letting the user decide....as long as the balance and number of shares are correct.

Jim_Harman

mgtblynch said:

Just FYI...all referenced transactions provided and in error thus far are Buy transactions.

The examples you sent on Aug. 9 are all Sold transactions.

mgtblynch

Yes, Sorry...i have the same issue whether buy or sale...i inadvertently sent the wrong reference...now it makes sense why all the references about cost basis...SMH...