FAQ: how to deal with SEDOL-codes retrieved from the M&A module of ThomsonOne?

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s