Clearing out bogus security price values

Avi Dee
Avi Dee Member ✭✭
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!

Best Answers

  • Avi Dee
    Avi Dee Member ✭✭
    Answer ✓
    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.

Answers

  • Avi Dee
    Avi Dee Member ✭✭
    (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.)
  • Avi Dee
    Avi Dee Member ✭✭
    Answer ✓
    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
    Avi Dee Member ✭✭
    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
    jacobs SuperUser, Mac Beta Beta
    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. 
    Quicken Mac Subscription • Quicken user since 1993
  • Avi Dee
    Avi Dee Member ✭✭
    > @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.
This discussion has been closed.