Multiple Securities for same Ticker

Mike Bray 's Avatar

Mike Bray

29 Aug, 2021 12:11 PM

Hi Sean,
We have a situation where a user has been forced into having multiple Security records for the same ticker. This is created by an investment house using different CUSIP ids for the same Security when the security is present in multiple accounts. Quote Loader did not cater for this as it expects only one entry per ticker. I have changed Quote Loader to recognize tickers in the format ttttt#eeee where ttttt is the ticker as registered on the Exchange and eeee is any characters that uniquely identifies the record.

Though we now have a solution for this can you consider whether or not Moneydance could store multiple CUSIP ids and allow the online download of transactions to be associated the correct record. In my mind this would be at the Account/Security level which would require a new field on the Add Security screen for investment accounts, storage in the account record (most likely a parameter).

The changes to Quote Loader are waiting to be verified and released, please can you do this.
Thanks
Mike

  1. Support Staff 1 Posted by Sean Reilly on 29 Aug, 2021 02:25 PM

    Sean Reilly's Avatar

    Hi Mike,

    To be honest, I'd prefer a different approach to handling this. I wonder if a special object that contained a mapping from CUSIPs to currency objects would solve this in a simpler and less breakable way? Such a table would also provide a much faster way to get the security that is associated with any given CUSIP.

    I'll update your extensions ASAP.

    Thanks!
    Sean

    --
    Sean Reilly
    Developer, The Infinite Kind
    https://infinitekind.com

  2. 2 Posted by Mike Bray on 29 Aug, 2021 02:41 PM

    Mike Bray 's Avatar

    Hi Sean, it is entirely up to you which model you use, all I was really
    doing was raising the issue where the current model does not work for
    all situations.  Of course if you go the route you are proposing the
    multiple records for same ticker is no longer needed as long as you can
    handle the download of investment transactions with different CUSIP ids.
    Regards
    Mike

  3. 3 Posted by Stuart Beesley ... on 29 Aug, 2021 03:56 PM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar

    To chip in… (I helped the user identify the original cusip issue..). I agree with Mike that the design of the appropriate solution is up to you Sean. But it would seem sensible (to me) to have the multiple cusip mapping on the actual (currency)security master record as this is all about the security.

    I would say that whatever solution is used, that there needs to be a simple gui to allow the user to change/edit the mappings as otherwise they have to resort to tools like toolbox to do this, when they should be able to do it in the ui.

    One last point…… please can the tools/security screen block any attempts to add / edit a security if the ticker is set and if a new duplicate ticker is detected / would be created (clearly it should allow any duplicates that already exist)? The ui should block this.

    IMHO, it should also block attempts to add securities or currencies using the same .getIDString() too. Just my two-pennies.

  4. Support Staff 4 Posted by Sean Reilly on 30 Aug, 2021 07:34 AM

    Sean Reilly's Avatar

    If we store the CUSIPs in the currencies themselves then it's pretty easy to get the same CUSIP in two different currencies. Also, that means the "map" of CUSIPs to currencies is spread across the data set, which is ok in general but in this case I think a centralised map is preferred because it the data model would match your mental model of it. It would also make it a lot easier to make a UI to edit the mapping of CUSIPs to securities.

    I also wouldn't store this in the root account, because that's getting a bit overloaded already and if we put all kinds of data in it then it will start losing some people's changes to sync conflicts. The solution to this is to create a new MoneydanceSyncable object with a hard-coded reserved ID. I wouldn't recommend any extension do this without checking with me first, but if we can specify an agreed upon item ID to hard code then this would be a great place to keep such a map. I do think in a few places in Moneydance with code like the following:

        // I've just added SECURITY_SUBTYPES_ITEM_ID = "security_subtypes" in MoneydanceSyncableItem
        MoneydanceSyncableItem item = book.getItemForID(MoneydanceSyncableItem.SECURITY_SUBTYPES_ITEM_ID);
        if (item == null) {
          item = new MoneydanceSyncableItem(book);
          item.setParameter(SyncableItem.ITEM_KEY_ID, MoneydanceSyncableItem.SECURITY_SUBTYPES_ITEM_ID);
          item.setParameter(SyncableItem.ITEM_TYPE_KEY, MoneydanceSyncableItem.SECURITY_SUBTYPES_ITEM_TYPE);
        }
    

    This lets you have an independent object where you can put related information and have it synced along with the data file. In item we can build a map of CUSIPs to currency UUIDs using something like setCurrencyParameter("cusip."+cusip, mappedCurrencyOrNull) and get them with getCurrencyParameter("cusip."+cusip, null).

    --
    Sean Reilly
    Developer, The Infinite Kind
    https://infinitekind.com

  5. Support Staff 5 Posted by Sean Reilly on 30 Aug, 2021 07:42 AM

    Sean Reilly's Avatar

    I think in this case it would makes sense to use the code below for the CUSIP map. Please feel free to use it in your extension. I should make convenience methods in MoneydanceSyncableItem, or maybe AccountBook to access these sorts of standard identified objects.

        MoneydanceSyncableItem cusipMap = book.getItemForID("md.cusip_map");
        if (cusipMap == null) {
          cusipMap = new MoneydanceSyncableItem(book);
          cusipMap.setParameter(SyncableItem.ITEM_KEY_ID, "md.cusip_map");
          cusipMap.setParameter(SyncableItem.ITEM_TYPE_KEY, "misc");
        }
    

    --
    Sean Reilly
    Developer, The Infinite Kind
    https://infinitekind.com

  6. Support Staff 6 Posted by Sean Reilly on 30 Aug, 2021 07:43 AM

    Sean Reilly's Avatar

    in fact, this object might be a great place to hold more general information about syncing/updated securities and currencies... so maybe a more general object ID would be preferred?

    --
    Sean Reilly
    Developer, The Infinite Kind
    https://infinitekind.com

  7. 7 Posted by Stuart Beesley ... on 30 Aug, 2021 07:49 AM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar

    Hi Sean... Your logic makes sense (of course)... We mustn't forget the scheme name as well as the CUSIP(ID).. (as they come as a pair). This is something you would have to initially code (of course) as you interrogate and set the CUSIP in your Investment OFX download routines.... etc.... You would also have to migrate CUSIPs over to the new map. I.E. an extension editing this new table would be of no use if the core MD code is not using it...?

  8. Support Staff 8 Posted by Sean Reilly on 30 Aug, 2021 07:55 AM

    Sean Reilly's Avatar

    Yes, good points. I would be happy to migrate Moneydance's CUSIP code over to this. I can't do it just yet, but I will get to this soon after the next release, which I really hope is imminent.

    Thanks,
    Sean

    --
    Sean Reilly
    Developer, The Infinite Kind
    https://infinitekind.com

  9. 9 Posted by Stuart Beesley ... on 30 Aug, 2021 07:59 AM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar

    Understood. Perhaps ping me when you start to look at this in the future and we can collaborate?

  10. Support Staff 10 Posted by Sean Reilly on 30 Aug, 2021 08:01 AM

    Sean Reilly's Avatar

    👍

    --
    Sean Reilly
    Developer, The Infinite Kind
    https://infinitekind.com

  11. 11 Posted by Mike Bray on 30 Aug, 2021 10:04 AM

    Mike Bray 's Avatar

    My latest change to Quote Loader can stay in place as I do not touch CUSIP(ID), just allow for multiple Security Currency records with the same root ticker. I will not need to do any changes for what you are suggesting. Should I change QL to be more intelligent about CUSIP(ID), using the CurrencyParameters to store data is useful as my extension runs on all MD builds from 1802 onwards. I don't want new APIs which are only available new builds.

  12. 12 Posted by Stuart Beesley ... on 30 Aug, 2021 01:03 PM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar

    Personally, from a non technical perspective, your enhancement to QL, ignoring cusip, for duplicate tickers is great as it is.

  13. System closed this discussion on 29 Nov, 2021 01:10 PM.

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