Restoring unique codes

In the past I blogged about the fact that if you work with codes like SEDOL (or any other types), to get information on listed companies, it is important to keep them in tact. Excel, for instance, has a habit of removing zeros when they are the first digits of a code.
Some databases (like SDC Platinum) will not give you a good search result if codes are incomplete when you search using a list of these abbreviated codes. Some databases are more forgiving when you search on codes and the zeros in front are gone.
The second, more important reason, however, to keep the codes in tact (including zero digits in front) is, that when you combine datasets, you will probably use codes to match them. Example:

For whatever reason, when you use Excel, you may have inadvertently have codes in a dataset that are no longer in tact. In the earlier blog post I solved this problem using a nested IF() function for Sedol codes. This example could be adapted for longer codes.
Recently, a student of the Vrije Universiteit, pointed out a more elegant solution: using the REPT() and LEN() functions. The advantage of this solution is, that it can very easily be adapted for codes of a different length. The solution function is:

=REPT(“0”;X-LEN(Cell))&Cell

Explanation: The function will repeat/add the character Zero (= 0) to the characters in a Cell and the number of zeroes depends on the X minus the Length (= len() ) of the number of characters already in the Cell. Added to this will be the characters of the original Cell.

Example for Sedol codes (always 7 digits long):

If the abbreviated Sedol code is: 68G78
The Function would be (Sedol code is 7 digits long): = REPT(“0”;7-LEN(Cell))&Cell
The result would be: 0068G78

Below you see an example for abbreviated 9-digit CUSIP codes:

 

Email

%d bloggers like this: