Add new Sedol security idenitification for new AJ Bell Securities Limited PDF-Importer

[Nirus2000]

https://forum.portfolio-performance.info/t/pdf-import-from-ajbell-youinvest/21679 https://forum.portfolio-performance.info/t/pdf-import-from-aj-bell-securities-youinvest/21679/3 Add new datetime format in PDFExtractorUtils.java

Add new "Sedol" security idenitification for PDF Importer (same like ISIN or WKN) https://en.wikipedia.org/wiki/SEDOL

Hello @buchen i for this PDF importer a new security identifier is needed. This I have hopefully been able to implement completely. I add also some new testCase and improve some source code. At least I tried :grimacing:

However, I failed with the implementation in the protobuf. I don't understand this one. If necessary, something would still have to be triggered via the client factory that updates the existing securities.

Greetings Alex

[buchen]

(grrr... I just typed up a long response and now the input field is empty again 😬... let me type up the short version)

I never heard about Sedol. I understand it as a country-local security identification number very similar to the WKN (which is the local identification number for Germany).

And reading the Wikipedia, one is also convertible:

SEDOLs serve as the National Securities Identifying Number for all securities issued in the United Kingdom and are therefore part of the security's ISIN as well.

and

For British securities, SEDOLs are converted to ISINs by padding the front with two zeros, then adding the country code on the front and the ISIN check digit at the end.

I am hesitant to add new fields. Maybe the mistake was already to add "WKN" (which is the German Wertpapierkennnummer). If we add something for the Britisch/Irish, then what about the French, Spanish, ...?

What can we do?

• We could "rebrand" the WKN as the "country-specific security identifier" (rebrand because we cannot rename it in the XML for compatibility reasons). If the users enters the Sedol in the WKN field, it will just work.
• We could construct the ISIN out of the Sedol and then use the ISIN to identify the security
• We could extend the attribute mechanism to allow for arbitrary local security identifier. The users could configure as many as they want (and even custom ones like the idNotation which is used by popular websites)

[Nirus2000]

Hello @buchen

(grrr... I just typed up a long response and now the input field is empty again 😬... let me type up the short version)

I know, this has also happened to me several times... :disappointed: :disappointed: :disappointed:

---

I understand your point of view. In principle I know 6 major security identification systems.

• ISIN
• WKN
• CUSIP
• VALOR
• SEDOL
• FIGI

The CUSIP is used e.g. in IBFlexQuery or also in the Score Priority Corp. PDF importer. The valor numbers mostly in the Swiss PDF importers.... and so on.

Let's make a separate branch for this again, then I can do some of the work again. 👍🏻

I like the idea that we convert the WKN into a country specific security identifier. I have also already searched the net to see if there is a source on how to convert these. (https://www.isindb.com/)

What would we need to consider?

• How do we do the recognition of which system it is?

Ah... there I went again with a hit.... well, PP is developing globally and we should set the course for this. The slovak translation is already finished... #2893 ... unbelievable how fast this is going forward.

Greetings Alex

[buchen]

In principle I know 6 major security identification systems.

❤️

Thanks. That is a nice overview. This is something we can target. (And then we can throw in the notationId for good measure).

Before we implement, let's try to understand some boundary conditions.

Can a security have a WKN (german identification) and and Sedol (British) at the same time? I am asking because the transformation rules to the ISIN would be different (the British would add the GB, pad some 0, and add the check digit.

What about this behavior: a security can have any of those identification numbers. From the UI, they are not all displayed. Only if the security has it maintained, it is shown. But any can be added (say: "add CUSIP" via some button/dropdown). We have a predefined list of possible identification numbers (because the code must understand that it now looks for the SEDOL).

From the persistence point of view, I am torn between adding direct XML properties (as you have done in the PR) or using the Attribute mechanism (which is backed by a hash map). The former is easier in the code, but bloats the XML, can never be changed. The latter is more flexible, keeps the storage format nimble and the XML small, but requires some helper code. We would have to try, but we could use the Attributes "as is" but attached to the InvestmentVehicle (instead of Security) and have a configuration that is not changeable by the users

[jamescrowley]

@buchen to answer one of your questions - yes, it's possible for a security to have both a SEDOL and a WKN. I found the following from a random Google:

ISIN: LU0346392219 SEDOL: BNTC223 WKN: A0NGXR Bloomberg: FFGINCY LX

(https://www.fidelityinternational.com/FILPS/Documents/en/current/ret.en.xx.LU0346392219.pdf)

I'm guessing it's common when listed on multiple exchanges.

I'm also not sure the SEDOL -> ISIN conversion you mention is often used - particularly as often funds will not be domiciled in the UK. Searching for GB00BNTC2230 (adding GB00 and a 0 ISIN check code to the above SEDOL) for instance yields zero references.

[Nirus2000]

Hello @buchen I am absolutely with you on this.

As @jamescrowley mentioned, a security can have any or all of these identification numbers. Another constraint is that the quotes can also be searched with each of these identification numbers. I'm also thinking of a simple DropDown field where we set a HashMap and add an "idNotation" to it. A pre-check, could be created by a syntax comparison and then check it. If a unique syntax is found e.g. ISIN.

ISIN: Patter: ^(([A-Z]{2})([A-Z0-9]{9}))([\d]{1})$ -> Mathematical

WKN: Patter: ([ABCDEFGHJKLMNPQRSTUVWZ0-9]{6}) -> Mathematical (Patter; excludes the letters "I" and "O")

CUSIP: Patter: ^([0-9]{3})([A-Z0-9]{5})([\d]{1})$ -> Mathematical

VALOR: Patter: ^([A-Z0-9]{5,9})$ -> Mathematical

SEDOL: Patter: ^([B-Db-dF-Hf-hJ-Nj-nP-Tp-tV-Xv-xYyZz\d]{6})([\d]{1})$ -> Mathematical

FIGI: Patter: ^(?!B[SM]|G[GBH]|KY|VG)([A-Z]{2})G((?=[^AEIOU_])\w){8}([\d]{1})$ -> Mathematical

[loucasal]

@Nirus2000 any reason this was closed? It would be great to have this work brought to fruition eventually.

[Nirus2000]

@loucasal it is ready with https://github.com/portfolio-performance/portfolio/pull/3695 and will be released with the next version, as far as everything is ok.