Clearing out bogus security price values

Avi Dee

The chart of my investment accounts has random dips, and it seems to be that I have bad historical data for some of my securities. When I dig into the data, I see days when the securities “closed at 0” which is obviously wrong. This file was imported from Quicken 2007, which crashed a lot, so there’s historic baggage.

It’s really easy to identify the bogus entries in the ZSECURITYQUOTE and ZSECURITYQUOTEDETAIL tables. First,

select s.ZNAME, datetime(q.ZQUOTEDATE+978307200, 'unixepoch')
from ZSECURITYQUOTE q, ZSECURITY s
where q.ZSECURITY = s.Z_PK AND q.ZCLOSINGPRICE = 0;

identifies 294 rows in ZSECURITYQUOTE with a zero closing price, and

select d.ZCLOSINGPRICE as "detail", q.ZCLOSINGPRICE as "quote"
from ZSECURITYQUOTEDETAIL d, ZSECURITYQUOTE q, ZSECURITY s
where q.ZSECURITY = s.Z_PK and d.ZSECURITYQUOTE = q.Z_PK
AND d.ZCLOSINGPRICE = 0;

shows exactly 294 rows in ZSECURITYQUOTEDETAIL that have zero closing prices, and they all match up.

Can I just delete rows from ZSECURITYQUOTE and ZSECURITYQUOTEDETAIL? Will that work?

Thanks!

jacobs

@Avi Dee What you write makes sense to me, but because I (and most Quicken Mac users) don't have experience modifying the database via SQL editing, I can't say with certainty that just deleting those rows will work and have no unintended consequences.

I would make a copy of your database and try it -- and then do every view and report that you can to make sure things look as you expect them to.

There is a safe, albeit slower, way to delete the the zero dollar closing prices. (Whether this is viable for you depends on whether you have a modest number of securities or very many.)  Go to Window > Securities, click on one of your securities with one or more zero dollar closing values, click on the Price History tab, and you'll see a table of the price history. Click on the Closing Price heading, to sort the table by closing price -- and any zero dollar closings will be at the top. Click on the first one, scroll to the last one and Shift-Click to select them all, and press delete. Then click done. 

Avi Dee

Thank you.

So yeah, the question of the safety is kinda paramount, so it’s probably wiser to go your way.

My updated SQL for anyone following along in the future is:

select s.ZNAME, count(s.ZNAME)
from ZSECURITYQUOTE q, ZSECURITY s
where q.ZSECURITY = s.Z_PK AND q.ZCLOSINGPRICE = 0
GROUP BY s.ZNAME ORDER BY s.ZNAME;

which gives a list of securities that have 0 entries and their count. Then go down the list that you get, one by one, and do what Jacob says, which is to sort by closing price and delete 0 entries.

Avi Dee

(BTW, for anyone confused, 978307200 is the number of seconds difference between the Unix epoch in 1970 and the Mac’s Foundation epoch in 2001.)

jacobs

@Avi Dee What you write makes sense to me, but because I (and most Quicken Mac users) don't have experience modifying the database via SQL editing, I can't say with certainty that just deleting those rows will work and have no unintended consequences.

I would make a copy of your database and try it -- and then do every view and report that you can to make sure things look as you expect them to.

There is a safe, albeit slower, way to delete the the zero dollar closing prices. (Whether this is viable for you depends on whether you have a modest number of securities or very many.)  Go to Window > Securities, click on one of your securities with one or more zero dollar closing values, click on the Price History tab, and you'll see a table of the price history. Click on the Closing Price heading, to sort the table by closing price -- and any zero dollar closings will be at the top. Click on the first one, scroll to the last one and Shift-Click to select them all, and press delete. Then click done. 

Avi Dee

Thank you.

So yeah, the question of the safety is kinda paramount, so it’s probably wiser to go your way.

My updated SQL for anyone following along in the future is:

select s.ZNAME, count(s.ZNAME)
from ZSECURITYQUOTE q, ZSECURITY s
where q.ZSECURITY = s.Z_PK AND q.ZCLOSINGPRICE = 0
GROUP BY s.ZNAME ORDER BY s.ZNAME;

which gives a list of securities that have 0 entries and their count. Then go down the list that you get, one by one, and do what Jacob says, which is to sort by closing price and delete 0 entries.

Avi Dee

Also, I did a refresh, and Quicken dropped a few more zero entries into my Vanguard 401k securities. (These aren’t openly traded, so the pricing information is only gotten through the connection to Vanguard.) How can I file a bug against Quicken to not add these bogus 0 entries?

jacobs

On the Help menu in Quicken, you can use the Report a Problem menu option to describe a problem. It allows you to send screenshots, log files, even a sanitized (all identifying information stripped) copy of your data file. The clearer the description of the problem, including screenshots, the better the chances someone will act on it. But I should warn you that this channel is a one-way street; they do not respond to reports (except in rare instances if they want additional information). And in my experience, if it's something the triage person at Quicken can't readily reproduce, it may not get acted upon -- and since this is specific to your funds and accounts at Vanguard, I'd say the chances of them confirming that there's a problem and acting on it are on the slim side. But it can't hurt to try.

The only other course of action is contacting Quicken Support via their phone support during weekday hours that they're open, and having an agent do a screen share to see what you're seeing. Again, if they can document it as a bug, they can escalate it, but if they can't have you replicate it -- which you likely can't do upon demand -- this might lead nowhere. 

Avi Dee

> @jacobs said:
> On the Help menu in Quicken, you can use the Report a Problem menu option to describe a problem.

Awesome; thank you. I’ll give that a try.

The other big issue with Quicken is that when reconciling hundreds of entries, it constantly locks up, beachballing. The Report a Problem won’t work, and my experience with the phone support has been terrible and I have zero faith in them.

Oh well. Thank you for your help here.