Conversion from QMac 2007 - only had 4 decimal places - now holdings are way off (edit)

Options
ALAI-2024
ALAI-2024 Member

I have a big problem with the conversion. Since Quicken for Mac 2007 constrained asset prices to 4 decimal points, many of my brokerage transactions are wrong in terms of dollar amounts since the conversion engine assumes that the number of shares and share price are correct. Instead, I should have the ability to use the "share price" as the plug number, which is what I did in the old Quicken for Mac 2007. Now I'm off by pennies per transaction but over 10 years, it is off by a lot. And I can't possibly go back and reconcile every transaction. I have dozens of brokerage accounts each with years of transactions. I'm inclined to cancel rather than go backwards to reconcile every trade. PLEASE HELP!!!!

Answers

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    Options

    I had a few problems like this when I converted from Quicken 2007, but not the magnitude you're describing. The problem was really the incorrect way Quicken 2007 did it; I sometimes used the Commission field to handle transactions which were off by a penny or two. In modern Quicken Mac, the default is to enter the cost and number of shares of a transaction and Quicken calculates the price per share, although it does give you the option of entering number of shares and price per shares, with Quicken calculating the cost.

    How much are you really off? Perhaps I'm not understanding the magnitude of the problem; I wouldn't think the discrepancies would be big enough that you wouldn't want to continue with Quicken. My thought is that you should just make one transaction for each of your securities to Add or Remove Shares to get to the correct number of shares, picking an arbitrary date a year or two in the past for these transactions. (If you wanted to be a little more precise, for as far back as you have brokerage statements, you could enter adjustment transactions on the last date of each year.) The only downside is that if you sell part of your holdings of a security, and specify the specific lots you're selling, Quicken may not be able to give you the gain or loss correct to the penny. For me, this wouldn't pose a problem, because Quicken would be very close to correct, and I always rely on the 1099 statement from my brokerages for my tax reporting, not Quicken.

    Quicken Mac Subscription • Quicken user since 1993
  • ALAI-2024
    ALAI-2024 Member
    Options

    10+ accounts each 10+ years, month end balances are off at least half the time. Seems to me like it should be an easy coding where the user is allowed to designate which 2 of 3 variables to hold unchanged (i.e., number of shares, share price, total dollars). And if someone doesn't designate it, then have a default option to be number of shares and share price. This is exactly the problem I dreaded making this switch.

  • Ps56k2
    Ps56k2 SuperUser ✭✭✭✭✭
    Options

    would love to see an example …

    QWin - R54.16 - Win10

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    Options

    Seems to me like it should be an easy coding where the user is allowed to designate which 2 of 3 variables to hold unchanged (i.e., number of shares, share price, total dollars).

    And that is exactly how the modern Quicken Mac is programmed to work!

    And if someone doesn't designate it, then have a default option to be number of shares and share price.

    The default is number of shares and total cost, with Quicken calculating the share price. Once click allows you to do it the way you're wanting to.

    But I'd note the entering shares and total cost is what's actually most accurate. Here's a quick example: if I purchase 90 shares of a security for $1,000, then those two numbers are precise; the calculated share price is $11.1111111. If your brokerage statement shows only two decimal places for price, as is the case with some, and you enter 90 shares at $11.11, Quicken will decrease your cash balance by $999.90 instead of $1,000. With three decimal places, (entering 90 shares at $11.111), you'd still be off by a penny. The point is that there is always going to be the potential for a small rounding discrepancy, and you want that to be with the share price rather than the number of shares or the cost.

    Quicken 2007 did what you want: enter number of shares and share price. And you sometimes had to fudge the total cost because of it. (I would use the commission field for plus or minus a penny when rounding led the total cost to be off.) When I first switched to modern Quicken Mac, I thought the program was "doing it wrong" because it was different than Quicken 2007, but after a little thought about it, I came to realize the modern Quicken Mac default is a preferable way of capturing investment transactions because there is never a discrepancy in cash or shares; nonetheless, the developers subsequently added the option to use whichever two values you want.

    As I said, when I converted from Quicken 2007, I had to fix balances in a few accounts which were off by a few cents due to the different way transactions were calculated in the two programs. I believe I had 8 investment accounts at the time, some with data going back 20+ years. But I'm not an active trader, so the discrepancies were small and it didn't take too long to fix them. And once I did, I've never had a problem with my month end care balances or cash balances being completely accurate.

    I asked above how big a discrepancy you have in your accounts, and whether the values which are off are your number of shares or your cash balances in those accounts. You originally said you are off by "a lot". In your subsequent post, you said "month end balances are off at least half the time", which suggests that the balances are correct sometimes and not others? Since I don't know the scope of the problem, I can only say that if you take a little time to adjust the balances of each account and/or security one time, you shouldn't have any recurring problems (especially if you use the default method of entering number of shares and total price, rather than share price, going forward).

    You've said "this is exactly the problem I dreaded making this switch" and "I'm inclined to cancel". This really shouldn't present such a significant problem for you. Over the past decade, hundreds of thousands of Quicken 2007 users have made the same switch to modern Quicken Mac which you just made, and we've all made any necessary tweaks and moved forward. If your discrepancies are very large amounts, or if there's a reason you feel you can't make one-time adjustments to get your holdings accurate to move forward, please post back here and provide more details, and we can hopefully offer additional suggestions to get you up and running smoothly.

    Quicken Mac Subscription • Quicken user since 1993
  • ALAI-2024
    ALAI-2024 Member
    Options

    The old Quicken gave me flexibility. I could enter any 2 of the 3 variables and it would calculate the last one for me. It is just math. Yes there was rounding, but as is often the case, even share figures for mutual funds go out to 4 decimal places. So not the new quicken doesn't give you the choice of any 2 of 3 variables, so it is less flexible, not more. You cannot have number of shares as the plug. If I don't go back and make sure every month balances, then the new Quicken is not reliable, and I would still have to maintain the old Quicken for historical data. Now I'm maintaining both and might just switch off the new Quicken.

    As an example, my cash balance for one of my investment accounts is off by more than $500. That's not ideal.

  • ALAI-2024
    ALAI-2024 Member
    Options

    And even say I'm willing to concede the argument to give me less flexibility, i.e., that in the new Quicken you don't have flexibility to have number of shares as the plug number and that you have to set the dollar price and shares and new Quicken will calculate the price for you, why isn't that flexibility available in the data conversion from Quicken Mac 2007? Why does the data conversion have to be price * number of shares and then Quicken calculates dollars? Why can't the data conversion be dollars/number of shares and then the data conversion backs out the price in the new data file??

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    Options

    So no the new quicken doesn't give you the choice of any 2 of 3 variables, so it is less flexible, not more. 

    No, that's incorrect. As I've mentioned twice, Quicken Mac does give you the choice of which of the fields to enter along with number of shares. Click the pencil icon to toggle between entering Total Cost or Price per Share.

    So you can enter exactly the way you have been, if you so desire. (There isn't a way for it to ask which way you'd prefer to have transactions entered when you convert from a Quicken 2007 data file, so it but for entries going forward, you do have that choice.)

    If you'll be patient with me, I'd like to make the case for why entering Total Cost and Number of Shares is superior going forward. (I'm spending the time doing this because, just like you, I thought this was wrong when I first converted from Quicken 2007. 😉 It took me working through the math to see why entering Total Cost rather than Cost Per Share is better.) The reason comes down to the fact that both the number of shares and the total cost/proceeds of a transaction are absolute values which cannot be rounded, and I'm sure this is why the developers made Quicken Mac default to that in the modern version. So if you'll stick with me though the example I used above, done both ways, it will illustrate what I'm saying…

    Let's say you bought 900 shares of some security for $1,000. If you enter Total Cost = $1,000 and Number of Shares = 900, Quicken will calculate the share price as 1.11111111 (eight decimal places). Conversely let's say your brokerage reports the share price was 1.1111 (four decimal places), and you enter Number of Shares = 900 and Price Per Share = 1.1111; Quicken will calculate the Total Cost of $999.99, which is off by 1¢. So you'd need to make a 1¢ adjustment using either the Commission field or a separate Adjustment transaction in order for your cash balance to correctly reflect the $1,0000 added to/remove from your account.

    Does that make sense?

    If I don't go back and make sure every month balances, then the new Quicken is not reliable

    It is absolutely reliable. There's something wrong with your converted data, to be sure, but once you either hunt down the discrepancy or make a one-time adjustment to for the accounts needed to make your past data correct, and it will then stay that way. Since switching to modern Quicken Mac from Quicken 2007, after making a few small tweaks to make my balances correct, I've never had a month/quarter in any investment account where it wasn't precise to the penny between my brokerage statement and my Quicken Portfolio view. (Not have I seen reports of other Quicken Mac users having such issues.)

    I understand you're frustrated that things didn't convert perfectly, but if you take a deep breath and resolve to either find the source of the problem or to plug in an adjustment, I think you'll find smooth sailing going forward.

    As an example, my cash balance for one of my investment accounts is off by more than $500. That's not ideal.

    I agree, that's worse than "not ideal"! 😀 (My discrepancies when I converted were less than 10¢ per account, if I recall correctly.) I'd be willing to bet that a discrepancy of the size you're describing is not due to rounding differences on transactions for a few pennies, unless you have tens of thousands of transactions. It's more likely some sale or purchase involving a transfer of funds from another account wasn't recorded or converted correctly. The Quicken 2007 database had some funky errors at times, and there's a good chance there was some transfer transaction which didn't convert properly. (I had a bunch of those, but they were in my wife's cash account and her business account from long enough ago that it wasn't worth going back and fix them; we did a one-time plug to get the balances correct.)

    What I would do is pull out your brokerage statements and go back a few years, say 12/31/2020. See if the balance of that account is off by the same amount or not. Then move forward or back two years and check again. You can quickly zero in on the year, and then the month, where things got out of whack. Or, if you don't want to spend the time on this, as I've said, you can enter an adjustment at the end of last year or a prior year to make your cash balances correct, and you should find them accurate going forward.

    You should not need to continue to maintain your old Quicken 2007; it's just a matter of getting your converted data to a stable place, correctly reconciled against your brokerage statements, and then you can move forward with confidence.

    I hope this helps, and I hope I've been able to point the way for you to see that you don't need to give up on Quicken Mac, and to get squared away and join hundreds of thousands of people using the program to reliably track their investments. Best wishes!

    Quicken Mac Subscription • Quicken user since 1993
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    Options

    Sorry, I posted my long reply above before seeing your latest post, so let me quickly address that…

    Why does the data conversion have to be price * number of shares and then Quicken calculates dollars? Why can't the data conversion be dollars/number of shares and then the data conversion backs out the price in the new data file??

    I can't answer with 100% certainty for the developers, but when modern Quicken Mac was built and released back in 2014, the developers used only Total Cost and number of shares for transactions. The option to enter Share Price instead of Total Cost was added several years later. But the data conversion program hasn't changed much, if at all, over the years, and I'm sure it was built on the way modern Quicken Mac was built: Total Cost and Number of Shares, with a calculated Price per Share. Even if they had wanted to give users a choice, there was really no way to have that interactivity in the complex two-phase conversion process.

    So my answer is basically this: the conversion process is what it is, for better or worse. It's been used by many thousands of longtime Quicken users who have moved forward successfully. At this point, ten years after modern Quicken Mac came into existence, there is a very small number of people still using and converting from Quicken 2007, and I think there is a near-zero chance that the developers will go back to make any changes to the conversion program at this point.

    And that leaves you with two basic choices: (1) you can rail against what you think wasn't the best way to convert your data and stop using Quicken; or (2) you can accept it, fix/tweak your balances to get your balances and holdings exactly accurate, and move on. I'd hate to see you choose the former because this should be such an easily fixable issue, and that's why I've spent the time engaging with you with these long posts to try to get you over the hump. 😀

    Quicken Mac Subscription • Quicken user since 1993
  • ALAI-2024
    ALAI-2024 Member
    Options

    So at the end of all this, the conversion requires that I go back to every entry where the $$ amount was changed. It's about 1000 entries since I am a long time user of the old Quicken going back to 1996 with many investment accounts and transactions. That isn't an easy fix and I can see why users don't want to make the switch unless they have absolutely no choice. And if modern Quicken is based on total dollars divided by shares (and price is the dependent), the conversion doesn't run this way.

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    Options

    I'm still not sure why you don't you make a single adjustment per account if your cash balance is off, or a single adjustment per security if your share value is off? What do you feel you're gaining by going back through 1,000 entries? It still seems like a lot of work which may not be necessary.

    If it's about 1,000 entries, and say they are off by an average of 2¢ each in the same direction, that would amount to $20 in needed adjustments. It's hard to imagine how a single one of your accounts is off by $500 just due to this issue. If that account had, say, 200 of your transactions, they would need to be off by an average of $2.50 for every transaction. The difference between calculated total cost versus calculated share price just can't be that large, so I still think you'll find one or more transactions which are wrong or missing.

    I'll bow out of the conversation now, as you don't seem interested in engaging with my comments and suggestions. I wish you good luck in getting your data cleaned up to your satisfaction, and to smoother days with Quicken ahead.

    Quicken Mac Subscription • Quicken user since 1993