Reconciling Stock Split

dtd's Avatar

dtd

01 Sep, 2020 02:56 AM

Yeah, Apple or Tesla - so this may be a good topic in general.

How do you reconcile the stock split properly? I recorded the stock split using the stock split button. Things looked good.

But Schwab of course downloaded a buyxfr for the split, which resulted in a 4x set of shares (apple). I know I can't delete Schwab's info, which is correct, but it doesn't jive with me telling MD about the split.

What's the correct way to reconcile? (I could change the shares schwab says I got today to zero, but that doesn't feel right.

  1. 1 Posted by dtd on 02 Sep, 2020 04:35 AM

    dtd's Avatar

    Ok, went ahead - I zeroed out the Schwab transaction, but didn't delete it so that it wouldn't keep downloading.

    I then hit the stock split button in MD.

    All seems fine.

    Would love to know if there was a better way than altering the (proper) download from Schwab.

  2. 2 Posted by jonh on 02 Sep, 2020 04:38 AM

    jonh's Avatar

    I'd like to know this answer also. I had the same problem so restored the prior day backup but did not do the stock split routine. My security download handled the split BUT it also multiplied my basis.

    That does not seem correct to me. The dollar value of the basis as shown on the portfolio view should remain remain the same should it not? Am I missing something here?

  3. 3 Posted by dwg on 02 Sep, 2020 04:42 AM

    dwg's Avatar

    I do not understand how Schwab are using a BuyXfr. There is no buy associate with a stock split. Such an event increases the number of shares on issue but it does so by spreading the cost basis over a greater number of shares. No money changes hands.

  4. 4 Posted by jonh on 02 Sep, 2020 04:58 AM

    jonh's Avatar

    @dwg: Same here. I don't use Schwab but anytime there is an internal transfer within my security account MoneyDance uses Buy/Xfr, I have not looked at the actual file retrieved from the broker to see if it is also tagged Buy/Xfr.

    My broker uses my bank account as a dummy for this action. In the Portfolio Register it shows that account in the Transfer column. The bank account shows it as an entry with zero and also a special symbol. Previous transactions that produced this type of action (not a split) never showed on my bank statement so I am guessing it is just a dummy entry internal to MD to accommodate the transaction.

    In the case of a normal purchase it takes 5 days to settle. So maybe in a few more days there will be some other entry to wonder about.

  5. 5 Posted by dwg on 02 Sep, 2020 05:17 AM

    dwg's Avatar

    In determining how to handle a transaction the only information that Moneydance has is the OFX data coming from the institution, hence this is the key n determining why Moneydance is doing what is it and perhaps analyzing if there are other ways it could be handled again based solely on what is in the data.

  6. 6 Posted by jonh on 02 Sep, 2020 05:30 AM

    jonh's Avatar

    @dwg: Right, I could not recall the name of that data file since it's all so transparent.

    That said, I don't think MD handled this info correctly. I have not yet checked the .pdf manual so it's possible I did not do something right. But based solely on OFX download my cost/share went from $72 to $93 and that does not seem correct. Assuming $72 was correct, after a 4:1 split I think it should be $18/share, not $93.

    Still researching this.

  7. 7 Posted by jonh on 02 Sep, 2020 05:35 AM

    jonh's Avatar

    Checked my broker site, my basis is as I stated above, $18 so MD is incorrectly showing $93. That said, I have not yet executed the stock split function but when I first tried it (prior to restoring a backup) the numbers were truly bizarre. Again, maybe I just didn't do it right. Heading for the MD Manual...

  8. 8 Posted by jonh on 02 Sep, 2020 05:47 AM

    jonh's Avatar

    From what little reading i've done it seems that the 'stock split' function is designed for manually entering info. I don't want to mess up my db by trying it again but I am guessing here that if you are downloading .ofx data you do not want to be using the stock split function.

    I'd sure like to get a clarification on this.

  9. 9 Posted by dwg on 02 Sep, 2020 06:08 AM

    dwg's Avatar

    The point I am trying to make is that it depends on the data that Moneydance is being presented with and how it is being presented that determined what can be done with it. How data may be shown on a broker's website does not indicate anything about the download.

    The OFX specification does define how stock splits should be presented, but the institutions have been getting increasingly more creative in how they are presenting the data, some even manage to stuff up QIF files.

    The attached shows the OFX definition of a split

  10. 10 Posted by dtd on 02 Sep, 2020 09:01 PM

    dtd's Avatar

    I'm blessed, because my Apple shares are in a Roth IRA account, so I can ignore the cost basis (and I have).

    dwg, I suspect you are right that banks are "doing it their way" vs. following protocols.

    In Schwab's case, I'm sure the OFX file would show a buyxfr of 3x of the shares I owned, with a buyprice of zero. I opened the Transaction details screen (appears no way to copy/paste it) and it does show a BUY, of X shares, at a cost basis of 0, with a cost of 0. It does mention a CASH transaction (of zero), and that might trigger a BUYXFR in MD

    So, basically, Schwab just throws the split shares at me, and from their POV, everything is cool. (don't know about cost basis). And actually, MD considers it cool, too (once I change BUYXFR to BUY so as not to invoke an actual transfer)

    BUT, I can't then do the MD stock split feature, as I "have" all the shares due to the Schwab transaction. So, the graph shows that Apple precipitously fell that day.

    My personal solution was to zero out the share transaction, such that it existed and not redownloaded, but such that I still "needed the split". THEN the stock split feature quadrupled my shares (vs. Schwab) and made the graph look good.

    Again, haven't looked at effect on cost basis.

    jonh - I suggest you make an exported backup of your database file, then play. I'd love to hear a report back about the cost basis, as I'm a curious person.

  11. 11 Posted by jonh on 02 Sep, 2020 09:08 PM

    jonh's Avatar

    @dtd:
    Actually, mine is in a Roth also but I want it to be right. And I have assets in TIRA and who knows when this split issue will next rise it's ugly head.

    Good idea on export. I was planning on copying my db file and doing a rename. Either way will probably work fine. I'm already bumping up against my max backups so I gotta get a move on or I lose that ability to experiment.

  12. 12 Posted by dwg on 02 Sep, 2020 09:40 PM

    dwg's Avatar

    Dan,

    What you are saying make it look like Swab is providing the transaction in a convenient way for them to do so. In providing a transaction like that there is no way for Moneydance to determine it is a stock split.

    A buy of X shares at zero cost when performed in Moneydance can give correct results in some places under certain conditions, for example it does not work with Lot matching at all. The cost basis report will be rubbish but the total cost shown in some places will be correct. Buying something yet it not costing anything is a concept Moneydance struggles with.

    A stock split is really not a transactions as such it is more an event. It has no value in itself but it affects your holdings not in the total value but at the individual level, its really more like a restructure. This why with programs that support stock splits have a specific function to deal with it they do not use standard transactions.

    How to deal with it is another issue. Getting the institutions to do it right is probably a pipe dream. Deleting it is possibly just going to result in it downloading again, so I would suggest another approach that may work for you.

    I would look to zero out the value and the number of shares in the transaction. I would also look to make entries in the Description and/or memo field to indicated that a stock split occurred on this date and the details of it. Hence the entry is becoming more like a placeholder with a description of what happened you may or may not want to keep it as a Buy transaction, if you are doing lot matching you would not as it would break it. Changing it to something like MiscInc gets it off many places but needs a category, you could make one called Notes or even one called Stock Splits and thus give you the ability to generate a report covering all stock splits you have had.

    I would then enter the Stock split using Moneydance's own function to complete the recording.

  13. 13 Posted by dtd on 02 Sep, 2020 09:56 PM

    dtd's Avatar

    I've basically done as you said already. The only "missing piece" would be changing Buy to MiscInc, which I can do, as I already have a "Notes" category.

    But would Buy 0 shares affect anything? (Even lot matching)
    Just curious.

  14. 14 Posted by jonh on 02 Sep, 2020 10:28 PM

    jonh's Avatar

    @dwg:
    This is what my brokerage sends when I select to download data. I have to agree that the OFX data apparently is done in a manner convenient to the company. It's starting to look like there is no solution to this issue.

    The brokerage is a large national mutual fund company. Certain details have been altered with 'x's'

    <TRANSFER>
    <INVTRAN>
    <FITID>935226689
    <DTTRADE>20200828160000.000[-5:EST]
    <DTSETTLE>20200828160000.000[-5:EST]
    <MEMO>STOCK SPLIT RECEIVEDSTOCK SPLIT RECEIVED
    </INVTRAN>
    <SECID>
    <UNIQUEID>037833100
    <UNIQUEIDTYPE>CUSIP
    </SECID>
    <SUBACCTSEC>CASH
    <UNITS>xx.xx
    <TFERACTION>IN
    <POSTYPE>LONG
    </TRANSFER>
    </INVTRANLIST>
    <INVPOSLIST>
    <POSSTOCK>
    <INVPOS>
    <SECID>
    <UNIQUEID>037833100
    <UNIQUEIDTYPE>CUSIP
    </SECID>
    <HELDINACCT>CASH
    <POSTYPE>LONG
    <UNITS>xx.xx
    <UNITPRICE>131.4
    <MKTVAL>xxx.xxx
    <DTPRICEASOF>20200901160000.000[-5:EST]
    <MEMO>Price as of date based on closing price

    The entry on the Security Register used my Bank Checking Acct as a the Transfer with a zero entry.

  15. 15 Posted by dtd on 02 Sep, 2020 10:35 PM

    dtd's Avatar

    well as dwg said - making investment firms change is probably a pipe dream.

    Sounds like dwg and I reached a similar conclusion - ignore the split transaction via zeroing out (as to how much i.e. buy vs miscinc, still discussing) then use MD split function.

    so - no solution - but maybe a workaround.

    still - i wonder about the cost basis issue.

  16. 16 Posted by dwg on 02 Sep, 2020 10:55 PM

    dwg's Avatar

    A buy of 0 shares at 0 cost is in undefined territory. It comes down to if the developer has thought about such a situation and a graceful way of handling it. At best it is an unlikely transactions to occur as it is nothing. It is the sort of transaction that could impact reporting

    Most programs that I have seen do not have Lot matching it seems to be too complex for many, too bad if you have to manage shares this way as we do here. Working with Lot Matching in programs that do support it - Moneydance, Reckon/Quicken has shown me it is rather sensitive and that such a function often does not cope with something outside of the norm very well, it can get really sensitive with splits when it does not result in a whole number of shares for example.

    Jon, I do not see that Moneydance could have done much else with the data it has received, as a human I can analysis it and work out it should be a split but computer software is looking at the structure and what it is told and that does not say stock split, words in a memo are not what it looks to.

    Institutions usually offer banking data reasonably well, less scope to stuff up I guess, but investment data is another matter, if it is available at all. This data will often involve intervention, I've suggested the sort of action you can take for this situation which hopefully gets the data right but also prevents the software downloading the transaction again.

  17. 17 Posted by dwg on 02 Sep, 2020 11:06 PM

    dwg's Avatar

    I do not recall how the cost base turned out, you would have to test it.

    I do not get downloads, in some cases they do not exist in others they are useless CSV files, and where I am I have to do Lot matching, and I also have Returns of Capital. Because of this combination I only keep total values in Moneydance and manage them accordingly. MD gives me the totals correctly o provides the high level detail and the income and expense reporting I need, I already know my individual cost base in Moneydance is largely rubbish. Yes I have had many discussions with Sean over the inclusion of Return of Capital in the software.

    For things like the cost basis I have a little spreadsheet for each security that I use to maintain these values, and I store them in the Moneydance data path so they get backed up with the Moneydance data files.

  18. 18 Posted by dtd on 02 Sep, 2020 11:25 PM

    dtd's Avatar

    my question about cost basis was mostly to jonh as he is most concerned about it.

    personally, i'm in a time and place where cost basis does not affect me much, and I deal with it in the very few places it does.

    Given your assertions, I'm changing the BUY (formerly BUYXFR) to MiscInc - thanks for the thought.

  19. 19 Posted by dwg on 02 Sep, 2020 11:33 PM

    dwg's Avatar

    I take the view that the Institutions are not perfect (did anyone every think they were?), Moneydance is not perfect, and FWIW neither is Quicken/Reckon. They all have their shortcomings, just mostly in different places so I use a combination of tools to maintain financial information, while pushing for what I would see are useful enhancements.

  20. 20 Posted by jonh on 11 Sep, 2020 08:59 PM

    jonh's Avatar

    @dtd, @dwg:
    OK, I bit the bullet and backed up my DB, noting that Apple stock had been modified by the ofx download about 2 weeks ago. The cost basis was way outta wack.

    I found an Aug backup and verified the expected cost basis and number of shares.

    I performed the stock split function which further blew the numbers out of wack. I then zeroed the registry entry for Apple, it still showed buy/xfer. That did fix the basis. Then as @dwg suggested, I changed the entry to buy. That removed the entry from my transfer account, which happens to be my checking account, thus cleaning it up so that I will not be confused by the zero entry in the future.

    So bottom line is that the stock split function, even applied 2 weeks later, fixed the error in cost basis created by the ofx download. The history still shows a huge price spike on the day of split but other than that the rest of the history was properly adjusted.

    So thanks you two for pushing me to step over that crack. I still think IK should add functionality to adjust cost basis.

  21. 21 Posted by dwg on 11 Sep, 2020 10:23 PM

    dwg's Avatar

    As I said elsewhere the cost basis is a calculated value based on transactions (eg buys and sells) and actions (eg StkSplt) and a lot of variables come into it.

    Average cost vs lot matching is a big one, since in lot matching every lot must have the cost basis maintained independently. Buys and sells of course come into it because a stock split only applies to the shares owned on that day so such an action does not apply to any shares previously sold and if those shares form only part of a lot and you still own the rest it gets complicated.

    The basic problem is that investment houses do things wrong - often - A stock split in no way, shape or form should be a BuyXFR. You did not buy anything, it did not cost any money and no money was taken from any account. To my way of thinking it is an event not a transaction. A BuyXFR that draws on the account itself, which sounds like what they might be doing, smells of the way that Quicken does some things, it is a totally bogus way of doing things. As Moneydance follows accounting rules it will not work.

    To me a basic transaction is something that has value. A stock split has no value at the point in time it is implemented, the total value of the investment is unchanged, it has implications moving forward but no intrinsic value, it is an action that has occurred rather than a transaction.

    In the price history it is likely to cause a disconnect, here you go from one day owning X shares at Y $'s per share to the next day owning A shares at B $s per share. Some say the price history should changed to give an ongoing series, yet if you did that and then look at values on a specific day in the past they would be wrong because the share number would not be changed (nor should it)

  22. 22 Posted by dtd on 12 Sep, 2020 03:09 AM

    dtd's Avatar

    The history still shows a huge price spike on the day of split but other than that the rest of the history was properly adjusted.

    Did you set the stock split date to the actual date? That might fix the price spike.

  23. 23 Posted by jonh on 12 Sep, 2020 04:18 AM

    jonh's Avatar

    I set the split date to the date indicated on my broker's statement. That was the 28th although in prior news reports I had heard it would be 1st.

    I tried editing the history and all it did was move the spike to a different date. All the rest of the history looks good so I am not going to worry about a single day spike. I'm just happy that the cost basis is correct.

  24. 24 Posted by dtd on 12 Sep, 2020 05:55 AM

    dtd's Avatar

    Glad you feel this is resolved. Fantastic.

    My stock split date was set as 8/31/2020 - and FOR ME, it resolved the spike.

    Take care, glad this worked out.

    also - you could edit history and delete the spike dates. if you want visual niceness.

  25. 25 Posted by jonh on 12 Sep, 2020 06:11 AM

    jonh's Avatar

    I'm not inclined to undo the split and start over again. I'll live with the spike.
    I will fiddle with the history and see if the 31st works for me. I chose the 28th for the split because that was the date used in the register. Is your now zero'd register showing that it was the 28th or the 31st?

  26. 26 Posted by dtd on 12 Sep, 2020 06:16 AM

    dtd's Avatar

    You can edit the split, you don't have to undo it.

    My zeroed register is based on when Schwab "sent" me the new shares - oh so wrong. It was 8/29/2020 - I just looked.

    8/29 was a saturday, so they avoided the business day. I used 8/31 because it was a monday - when everything changed.

    Again - I am glad you got the cost basis thing figured out. This is just graphical bits.

    Again, you can edit the split date - you don't have to undo it.

  27. 27 Posted by jonh on 12 Sep, 2020 06:31 AM

    jonh's Avatar

    Thanks. Changing to 31st fixed it. I didn't realize that the split could be edited. I found it at the bottom of the history screen and also surprised to see that I had forgotten it also split in 2014.
    Anyway, all is good now.

  28. 28 Posted by dtd on 12 Sep, 2020 06:37 AM

    dtd's Avatar

    As a suggestion to support - I suggest a knowledge base article on stock splits would be extremely useful - it would note that in stock splits, the OFX download is often simply a convenience and should be zeroed out in favor of the MD stock split issue, to indeed, maintain cost basis and so on.

    Whoever writes this should read this thread as background material.

    To jonh - HUZZAH!

  29. 29 Posted by dwg on 12 Sep, 2020 08:28 AM

    dwg's Avatar

    Splits can be a date sensitive event in other ways too. If you have a purchase or a sale on the wrong side of a split date it can throw you share count and value out significantly. People will often enter the date as being when they receive the shares, but the date needs to be when the split was executed.

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac