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.