Toolbox - Duplicate & Non-Hierarchical Securities Alert/Error

Gina's Avatar

Gina

31 May, 2023 11:37 PM

I'm sure this is a Stuart question. I received the following alert message when I opened Toolbox: "ALERT - Potential duplicate(d) securities found (in Tools>Securities)!" When I closed that window, there was another error message window behind it: "ERROR - NON-HIERARCHICAL SECURITY ACCT TXNS >> Run 'FIX: Non-Hierarchical Security Act Txns (& detect Orphans)'..." However, when I went into the Currency & Security Tools Menu in Toolbox, there is no 'FIX: Non-Hierarchical Security Act Txns (& detect Orphans)' Update Function. I would also like to know whether the 'FIX: Merge 'duplicate' securities (and related investment txns) into one master security record (2021.2(3089) onwards)' Update Function is what I need to use to address the potential duplicate securities alert. Thanks.

  1. 1 Posted by dwg on 31 May, 2023 11:46 PM

    dwg's Avatar

    I'm a fellow user.

    Look on the Transaction Tools menu, for the fix you referred to.

  2. 2 Posted by Gina on 31 May, 2023 11:58 PM

    Gina's Avatar

    Found it! Thank you. That resolves that error. Just need to figure out how to deal the the alert now. There are a bunch of duplicates.

  3. 3 Posted by dtd on 01 Jun, 2023 12:47 AM

    dtd's Avatar

    just a user - Stuart usually shows up in his morning - UK time...

  4. 4 Posted by Stuart Beesley ... on 01 Jun, 2023 06:35 AM

    Stuart Beesley (Mr Toolbox)'s Avatar

    MENU: Currency & Security tools:

    1) FIX - Merge 'duplicate' securities (and related Investment txns) into one master security record (by TickerSymbol). >> 2021.2 onwards

    2) FIX - Detect and merge/fix duplicate Securities within same Investment Account(s)

    Number 1 allows you to merge securities into one. Eg you accidentally created and added Apple1 and Apple2 into an investment account.

    Number 2 fixes the situation whereby you have the same security added twice within the same investment account. Eg Apple is actually added twice. There is only one Apple. This is the fix you need to run for the error highlighted above.

  5. 5 Posted by Gina on 01 Jun, 2023 09:46 PM

    Gina's Avatar

    Hi Stuart,

    Thanks for the info. I ran the "FIX - Merge…” Nothing was fixed. See attached. Should I run the other fix anyway?

    Gina

    On Jun 1, 2023, at 12:35 AM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

  6. 6 Posted by dtd on 01 Jun, 2023 10:59 PM

    dtd's Avatar

    just a user - Gina, we are past Stuart's window again (worldwide is great, but...)

    He gave two things to do, in case #1, if that fix was also an issue. (Stuart is very careful, both in his statements and in creating Toolbox).

    He does say "Number 2 is the the fix you need to run for the error highlighted above.:

    SO - MAKE SURE YOU CREATE A BACKUP (just in case) - and yes run the second fix, since Stuart says that's the "real fix you need".

    Report back of course!

  7. 7 Posted by Gina on 01 Jun, 2023 11:04 PM

    Gina's Avatar
    I ran the other one too, it couldn’t fix anything. I sent Stuart the report. Thanks. 

    On Jun 1, 2023, at 4:59 PM, dtd <[email blocked]> wrote:

     pre { width: 92%; margin: 10px 2%; padding: 5px 2%; background: rgb(239, 239, 239); border: 1px solid rgb(214, 214, 214); } blockquote { margin-left: 0px; padding-left: 1em; border-left-width: 5px; border-left-style: solid; border-left-color: rgb(204, 204, 204); }

    // Please reply above this line
    ==================================================

    From: dtd

    just a user - Gina, we are past Stuart's window again (worldwide is great, but...)

    He gave two things to do, in case #1 fix was also an issue. (Stuart is very careful, both in his statements and in creating Toolbox).

    He does say "Number 2 is the the fix you need to run for the error highlighted above.:

    SO - MAKE SURE YOU CREATE A BACKUP (just in case) - and yes run the second fix, since Stuart says that's the "real fix you need".

    Report back of course!

    On Thu, Jun 01 at 02:46 PM PDT, Gina wrote:

    Hi Stuart,

    Thanks for the info. I ran the "FIX - Merge…” Nothing was fixed. See attached. Should I run the other fix anyway?

    Gina

    On Jun 1, 2023, at 12:35 AM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

    Having trouble reading this? View this discussion online: Toolbox - Duplicate & Non-Hierarchical Securities Alert/Error.

    To unsubscribe and stop receiving emails from https://infinitekind.tenderapp.com, visit this page

  8. 8 Posted by Stuart Beesley ... on 02 Jun, 2023 04:28 AM

    Stuart Beesley (Mr Toolbox)'s Avatar

    Hi Gina, I said to run fix 2. So please run that. ??

  9. 9 Posted by Stuart Beesley ... on 02 Jun, 2023 04:30 AM

    Stuart Beesley (Mr Toolbox)'s Avatar

    After running fix 2, send me any output and also help/console.

  10. 10 Posted by Gina on 02 Jun, 2023 01:38 PM

    Gina's Avatar
    Hi Stuart,

    I actually did run both fixes. The 2nd fix wasn’t able to fix anything based on the rules. I left town for the weekend. I’ll send the results when I get back on Sunday night or Monday morning. 

    Thanks, 
    Gina

    On Jun 1, 2023, at 10:30 PM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

     pre { width: 92%; margin: 10px 2%; padding: 5px 2%; background: rgb(239, 239, 239); border: 1px solid rgb(214, 214, 214); } blockquote { margin-left: 0px; padding-left: 1em; border-left-width: 5px; border-left-style: solid; border-left-color: rgb(204, 204, 204); }

    // Please reply above this line
    ==================================================

    From: Stuart Beesley (Mr Toolbox)

    After running fix 2, send me any output and also help/console.

    On Thu, Jun 01 at 09:28 PM PDT, Stuart Beesley (Mr Toolbox) wrote:

    Hi Gina, I said to run fix 2. So please run that. ??

    Having trouble reading this? View this discussion online: Toolbox - Duplicate & Non-Hierarchical Securities Alert/Error.

    To unsubscribe and stop receiving emails from https://infinitekind.tenderapp.com, visit this page

  11. 11 Posted by Stuart Beesley ... on 02 Jun, 2023 01:46 PM

    Stuart Beesley (Mr Toolbox)'s Avatar

    Hi. The fix
    ‘FIX - Detect and merge/fix duplicate Securities within same Investment Account(s)’…. Has no rules. It just runs and fixes.

    When you restart MD and toolbox now, do you still get an error popup warning box?

  12. 12 Posted by Gina on 02 Jun, 2023 03:31 PM

    Gina's Avatar
    It was the second fix that didn’t fix anything. I set the “dummy” parameter and that worked. 

    I’ll send the results when I get back in town. 

    On Jun 2, 2023, at 7:46 AM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

     pre { width: 92%; margin: 10px 2%; padding: 5px 2%; background: rgb(239, 239, 239); border: 1px solid rgb(214, 214, 214); } blockquote { margin-left: 0px; padding-left: 1em; border-left-width: 5px; border-left-style: solid; border-left-color: rgb(204, 204, 204); }

    // Please reply above this line
    ==================================================

    From: Stuart Beesley (Mr Toolbox)

    Hi. The fix
    ‘FIX - Detect and merge/fix duplicate Securities within same Investment Account(s)’…. Has no rules. It just runs and fixes.

    When you restart MD and toolbox now, do you still get an error popup warning box?

    On Fri, Jun 02 at 06:38 AM PDT, Gina wrote:

    Hi Stuart,

    I actually did run both fixes. The 2nd fix wasn’t able to fix anything based on the rules. I left town for the weekend. I’ll send the results when I get back on Sunday night or Monday morning. 

    Thanks, 
    Gina

    On Jun 1, 2023, at 10:30 PM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

     pre { width: 92%; margin: 10px 2%; padding: 5px 2%; background: rgb(239, 239, 239); border: 1px solid rgb(214, 214, 214); } blockquote { margin-left: 0px; padding-left: 1em; border-left-width: 5px; border-left-style: solid; border-left-color: rgb(204, 204, 204); }

    // Please reply above this line
    ==================================================

    From: Stuart Beesley (Mr Toolbox)

    After running fix 2, send me any output and also help/console.

    On Thu, Jun 01 at 09:28 PM PDT, Stuart Beesley (Mr Toolbox) wrote:

    Hi Gina, I said to run fix 2. So please run that. ??

    Having trouble reading this? View this discussion online: Toolbox - Duplicate & Non-Hierarchical Securities Alert/Error.

    To unsubscribe and stop receiving emails from https://infinitekind.tenderapp.com, visit this page

    Having trouble reading this? View this discussion online: Toolbox - Duplicate & Non-Hierarchical Securities Alert/Error.

    To unsubscribe and stop receiving emails from https://infinitekind.tenderapp.com, visit this page

  13. 13 Posted by Gina on 05 Jun, 2023 06:14 PM

    Gina's Avatar

    Hi Stuart,

    I apologize for the confusing messages last week. Here are the results of trying to merge and fix the duplicate securities.

    When I run the “FIX - Merge ‘duplicate’ securities (and related Investment txns) into one master security record…” function, I get the attached message and output.

    When I run the “FIX - Detect and merge/fix duplicate Securities within the same Investment Account(s)” function, I get the attached message and output.

    If I’m understanding the messages and outputs, nothing has been merged or repaired; therefore, I will continue to get the attached ALERT message when I open Toolbox.

    I have no idea how to clean up this mess.

    Thanks,
    Gina

    On Jun 1, 2023, at 12:35 AM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

  14. 14 Posted by Stuart Beesley ... on 05 Jun, 2023 06:55 PM

    Stuart Beesley (Mr Toolbox)'s Avatar

    Hi, OK, I also need to apologise. It is actually the first fix you need... I.e.:

    FIX - Merge 'duplicate' securities (and related Investment txns) into one master security record (by TickerSymbol). >> 2021.2 onwards
    

    BUT... If you read the output, it tells you why there are no merge candidates... E.G.:

    Verifying potential 'duplicates': iShares 0-5 Year High Yield Corporate Bo(Ticker: SHYG Master ID: ^46434V407) (has 119 price history records)
     --- (Validating ID: ^DeSotoSHYG)
    ... 'iShares 0-5 Year High Yield Corpora..:ID ^DeSotoSHYG:rate 100.0:dpc 4::Shares:(0 price recs)   ' NOTE: has 0 price history records
    ... 'iShares 0-5 Year High Yield Corpora..:ID ^DeSotoSHYG:rate 100.0:dpc 4::Shares:(0 price recs)   ' CANNOT be MERGED as not the same 'Current Prices'        100.0 vs 44.09


    ... *** Failed checks - removing candidate.....
    This is a duplicated ticker, but the current prices are different... I.E. For this one, go to tools>Securities, and edit each and then ensure that the current price is the same.. Then try the toolbox again... See if it offers that one for merge... If not, post a new output...
  15. 15 Posted by Stuart Beesley ... on 05 Jun, 2023 06:58 PM

    Stuart Beesley (Mr Toolbox)'s Avatar

    ... I.E.. Only when the 'rules' are obeyed, will the candidates be offered to merge..:

                            The check / validation rules are:
                            - Find potential 'duplicates' where Securities' 'Ticker' Symbols are the same/match (cannot be blank); then Duplicate Security's...:
                            ... ID must be short and DIFFERENT (so you can identify them in this process). Examples: use '^APPL1', '^APPL2', '^APPL3'.. to merge 3 Apple Stocks
                            ....(^^Close this window and use Tools>Securities>EDIT and change the Security ID for each duplicate and then re-run this function again)
                            ...'Currency' must match
                            ...'Current Price' must match
                            ...'Prefix' & 'Suffix' must match
                            ...'Splits' data must match
                            ... hidden 'Decimal Places' setting must match **
                            - NOTE: Security Name is not matched, but you can select the Security to become the 'master', that has right details, as part of the process
    
  16. 16 Posted by Gina on 05 Jun, 2023 10:31 PM

    Gina's Avatar

    I tried to fix two securities as follows:

    I manually updated the ‘Current Price’ of the first security listed in the toolbox_output_before.txt 'GLDM World Gold Trust.' I also had two Apple securities ‘Apple, Inc.’ and ‘Apple Inc.’ I noticed they were not in the toolbox_output_before.txt and determined it was because one had the ticker symbol and the other one did not. I added the ticker symbol to the other one and set the ‘Current Prices’ the same and reran the merge fix. I got the attached info message and toolbox_output_after.txt that reflects the results. The first GLDM security entry changed, but I don’t know if it is fixed. The Apple security now appears in the list and shows it cannot be merged because the decimal places are not the same and because the prefix/suffix “:Shares vs :” are not the same. I tried to fix the decimal places in Tools>Securities>Edit, but MD did not allow me to change the decimal places and I have no idea what the prefix/suffix message means or how to fix it.

    Gina

    On Jun 5, 2023, at 12:58 PM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

  17. 17 Posted by dwg on 05 Jun, 2023 11:50 PM

    dwg's Avatar

    The prefix/suffix values are on the Securities edit screen - Take the path Tools --> Securities.

    The Securities' number of decimal places is changed using Toolbox on the Currency and Securities Menu.

  18. 18 Posted by Stuart Beesley ... on 06 Jun, 2023 04:46 AM

    Stuart Beesley (Mr Toolbox)'s Avatar

    Firstly, no, GLDM is NOT resolved. You need to refer to the output. It says this:

    Verifying potential 'duplicates': 98149E204(Ticker: GLDM Master ID: ^98149E204) (has 231 price history records)
     --- (Validating ID: ^98149E303)
    ... 'GLDM World Gold Trust..:ID ^98149E303:rate 38.8401:dpc 4::Shares:(210 price recs)              ' NOTE: has 210 price history records
    
     --- (Validating ID: ^DeSotoGLDM)
    ... 'SPDR Gold MiniShares Trust..:ID ^DeSotoGLDM:rate 100.0:dpc 4::Shares:(0 price recs)            ' NOTE: has 0 price history records
    ... 'SPDR Gold MiniShares Trust..:ID ^DeSotoGLDM:rate 100.0:dpc 4::Shares:(0 price recs)            ' CANNOT be MERGED as not the same 'Current Prices'        100.0 vs 38.8401
    
    ... *** Failed checks - removing candidate.....
    

    There are three securities with the same ticker (GLDM) and different IDs which seem to be the same thing:

    • 98149E204

    • GLDM ^98149E303

    • SPDR ^DeSotoGLDM

    So, you need to get all three aligned before the merge will be allowed. Of course, if they are not really the same security then you need to break the ticker linkage.

  19. 19 Posted by Stuart Beesley ... on 06 Jun, 2023 04:50 AM

    Stuart Beesley (Mr Toolbox)'s Avatar

    As for apple. As dwg says…. Use tools/secu ties and select both apple securities. Then edit and blank out the prefix and suffix boxes.

    Then use toolbox, edit security decimal places feature to make both apples either 4 or 5 decimal places. I suggest changing the one that’s 4dpc to 5.

    Then try again.

  20. 20 Posted by Gina on 06 Jun, 2023 04:49 PM

    Gina's Avatar

    Stuart,

    Thanks for your patience! With your explanation of the output, it is starting to make sense. I wasn’t reading the output correctly. I see now that the ". . ." is the segregation of each security with the GLDM ticker. I realize this may be asking a bit much, but any suggestions for the best way to determine whether or not they are the same security?

    As far as the Apple security, it is now ready to be merged, but there are several drop down options, and I’m not sure which to select. Should I just go with the defaults to merge?

    Gina

    On Jun 5, 2023, at 10:46 PM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

  21. 21 Posted by Stuart Beesley ... on 06 Jun, 2023 07:32 PM

    Stuart Beesley (Mr Toolbox)'s Avatar

    Hi. Surely only you know if the securities are really the same thing?

    As for the options. Run through them all. Write down the options and post them here. Abort at the end when it says are you sure. Post them here and I will try to answer the questions/responses.

  22. 22 Posted by Stuart Beesley ... on 08 Jun, 2023 06:15 AM

    Stuart Beesley (Mr Toolbox)'s Avatar

    Gina?

  23. 23 Posted by Gina on 08 Jun, 2023 03:41 PM

    Gina's Avatar

    Sorry Stuart,

    I got really busy. I will try to run through them all, but there were several drop downs each with options underneath. It will take a really long time to try every combination of options so I will try to narrow it down, if I can make an educated guess as to what it is asking.

    Gina

    On Jun 8, 2023, at 12:15 AM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

  24. 24 Posted by Stuart Beesley ... on 08 Jun, 2023 04:08 PM

    Stuart Beesley (Mr Toolbox)'s Avatar

    They should be fairly self-explanatory. If you find any, that are not, shout?

  25. 25 Posted by Gina on 08 Jun, 2023 04:19 PM

    Gina's Avatar

    Will do. Unfortunately, since my securities are managed, I am not familiar with them at all. I depend on the electronic transaction download process to keep MD up-to-date, and I assumed when the message said there was no security match, that was correct. Obviously, I was wrong so now I have a lot of duplicates that I need to resolve.

    As usual, I appreciate all your help!

    Gina

    On Jun 8, 2023, at 10:08 AM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

  26. 26 Posted by Stuart Beesley ... on 19 Jun, 2023 05:35 AM

    Stuart Beesley (Mr Toolbox)'s Avatar

    @gina - how are you getting on with this?

  27. 27 Posted by Gina on 19 Jun, 2023 12:27 PM

    Gina's Avatar
    Hi Stuart,

    I sent a message last Thursday. I’m ready to merge my Apple stock, but I’m not sure which of the attached options to select so I haven’t merged yet. 

    Gina

    Screen Shot 2023-06-15 at 8.21.28 AM.png

    On Jun 18, 2023, at 11:35 PM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

     pre { width: 92%; margin: 10px 2%; padding: 5px 2%; background: rgb(239, 239, 239); border: 1px solid rgb(214, 214, 214); } blockquote { margin-left: 0px; padding-left: 1em; border-left-width: 5px; border-left-style: solid; border-left-color: rgb(204, 204, 204); }

    // Please reply above this line
    ==================================================

    From: Stuart Beesley (Mr Toolbox)

    @gina - how are you getting on with this?

    On Thu, Jun 08 at 09:19 AM PDT, Gina wrote:

    Will do. Unfortunately, since my securities are managed, I am not familiar with them at all. I depend on the electronic transaction download process to keep MD up-to-date, and I assumed when the message said there was no security match, that was correct. Obviously, I was wrong so now I have a lot of duplicates that I need to resolve.

    As usual, I appreciate all your help!

    Gina

    On Jun 8, 2023, at 10:08 AM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

    Having trouble reading this? View this discussion online: Toolbox - Duplicate & Non-Hierarchical Securities Alert/Error.

    To unsubscribe and stop receiving emails from https://infinitekind.tenderapp.com, visit this page

  28. 28 Posted by Stuart Beesley ... on 19 Jun, 2023 12:54 PM

    Stuart Beesley (Mr Toolbox)'s Avatar

    Your message above wan't there last week...

    Anyway.. WOW!

    AAPL and 037833100 are the same
    SEDOL, ID: 4673549 - I have no idea what this is? Are you sure it's Apple?
    DE000JQ235A4 seems to be a J.P. Morgan holding of Apple

    Do you still have downloads of all of these?
    Are you sure they are all Apple holdings?

  29. 29 Posted by Gina on 20 Jun, 2023 09:10 PM

    Gina's Avatar

    Hi Stuart,

    Not sure what happened to the message I sent last Thursday because it appears in my sent folder, and it wasn’t returned undeliverable. Nevertheless, I tried to see what was going. As far as I can tell via a search for “Apple” in all my investment account registers, all my apple stock, which is through Fidelity, and named (Apple, Inc.) is tied to the first attachment. Even though the other apple security (Apple Inc.) also appears in my list of securities (see attachments 2 & 3). I couldn’t find any transaction associated with that security searching all of my registers. However, it finally dawned on me that my granddaughter’s MD account is also on under my MD license (and on my computer) because she is only 8, and I set up a MD account for her to track the UGMA account I contribute to for her, along with her accounts at the credit union. Her Apple stock is through TD Ameritrade and is using the other Apple security (Apple Inc.) see attachments 4 & 5. I also noticed that the Quote Loader extension is only getting the prices for the Apple security associated with my granddaughter’s account.

    So to answer your questions, between my account and my granddaughter’s account, there are definitely transaction downloads for APPL and 037833100 in our investment account registers. As far as the SEDOL, ID: 4673549, I don’t know what that is or where it came from, other than it being one of the options in a dropdown question during the Merge ‘duplicate’ securities function in toolbox. The last one, DE000JQ235A4, is showing up in my list of securities, but I don’t think it is connected to anything. Is there a tool that will allow me to verify whether or not it is connected to anything so I can just delete that one? Unfortunately, since both my granddaughter and I have TP Ameritrade securities that may also overlap, that is probably causing some of the other duplicates. I concerned now as to whether the APPL and 037833100 should be combined since one is connected to Aniya’s, my granddaughter’s, Apple account and one to mine. Will that screw everything up in one or the other accounts?

    Thanks,
    Gina

    On Jun 19, 2023, at 6:53 AM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:

    // Please reply above this line
    ==================================================

    From: Stuart Beesley (Mr Toolbox)

    Your message above wan't there last week...

    Anyway.. WOW!

    AAPL and 037833100 are the same
    SEDOL, ID: 4673549 - I have no idea what this is? Are you sure it's Apple?
    DE000JQ235A4 seems to be a J.P. Morgan holding of Apple

    Do you still have downloads of all of these?
    Are you sure they are all Apple holdings?

    On Mon, Jun 19 at 05:27 AM PDT, Gina wrote:

    Hi Stuart,

    I sent a message last Thursday. I’m ready to merge my Apple stock, but I’m not sure which of the attached options to select so I haven’t merged yet.

    Gina

  30. 30 Posted by Stuart Beesley ... on 20 Jun, 2023 09:29 PM

    Stuart Beesley (Mr Toolbox)'s Avatar

    FYI - don’t reply by email. Click the link and reply on the IK website instead.

    You can use toolbox. Security menu. Where is security being used (or can I delete security) to identify if any are not being used. Yes, I would delete any NOT being used.

    I think i would merge them and on the cusip popup use the one that says APPL.

    I wouldn’t worry about your daughter’s account. I assume her actual data is a separate file/open account? I also presume you have NOT mapped her account in your md+ license to one of your MD accounts?

    After you merge, then download, if you get another apple duplicate security then shout. We can then see what cusip it’s creating and which account. I don’t think this will hurt.

    BACKUP FIRST!

Comments are currently closed for this discussion. You can start a new one.

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