When you download data from the M&A module of ThomsonOne, you’ll see that the SEDOL-codes are incorrect. If you want to use them for example in the Datastream Event Study Tool, there are three problems:
- The comma’s within the codes – you can solve this by selecting the columns with the SEDOL-codes, use Format Cells, choose Custom and enter 7 zero’s (0000000) under Type.
- SEDOL-codes starting with a zero or multiple zeroes are incomplete (you can see them, but Excel can’t) – solving this is a bit more work: sort your data by the SEDOL-codes: the smallest on top (make sure the rest of your data is also sorted!). Then, add a new column and use the function Concatenate (in dutch: samenvoegen) to add extra zeroes. Copy the new SEDOL-codes and paste them with Paste Special > Values over the old codes. You can ignore the little green corners in the cells.
- SEDOL-codes starting with a B are not recognized by Datastream. You can solve this by adding UK in front of them, also when the companies aren’t UK-companies! The easiest way is to sort the list of SEDOL-codes by putting the largest on top. Then use the function Concatenate to add UK. Copy the new SEDOL-codes and paste them with Paste Special > Values over the old codes.
Then you can delete the extra column and resort your data.