Bond ISIN to company ISIN

Suppose you created a list with bonds and the accompanying ISIN codes. You can’t use these ISIN codes to download information from the issuer of this bond. But here is way to use the ISIN codes you collected so far to find the ISIN code of the issuer

An example: the ISIN code US44044KAA97 belongs to a bond. To find the ISIN from the company you can use Excel on the Bloomberg terminal.

b1

First you copy US44044KAA97 into B1. In B2 you enter this formula:

=BDP(B1 & ” corp”,”FUNDAMENTALS_TICKER”)

b2

In B3 you use the contents of B2 and use concatenate to put “Equity” behind it. Use this formula:

=B2& ” Equity”

b3

The last step is to use this function in cell B4:

=BDP(B3,”ID_ISIN”)

b4

to find the ISIN you are looking for.

US tickers to Datastream mnemonics

If you have US tickers as output  from e.g. Compustat index constituents and you want to get stock prices from Datastream that’s possible. You can use a ticker to create a Datastream mnemonic. You have to add country code, so “U:” would do the trick. You can easily do this in Excel by concatenate U: with the column with ticker.

This solution works fine for all but the Nasdaq firms. Those will need a “@” sign in front not “U:” So just to be sure you have everything and you don’t miss any data do both so a list with “U:” and one with “@”.

Example

Screenshot_USticker_mnemonics_v2

The ones that don’t make sense will give errors but this way you won’t miss any. So with Ford U:F will work and give data, @ F won’t it will give an error. Similarly Micosoft @MSFT will work and give data, U:MSFT won’t it will give an error

Cross border deals

Searching for cross border deals in Thomson is quite easy. You’ll need to use the “Advanced Search”. Go to Screening & Analysis > Deals & League tables > M&A > Advanced Search

Screenshot 1 - Advanced Search

When you include targets from a specific nation and exclude the acquirors from that same specific nation, you’ll get cross border deals within a country.

Screenshot 2 - Exclude acquiror

Below you’ll find an example of a search list for cross border deals.

Screenshot 3 - Search list

In your report you can use “Cross Border Deal Flag ” to verify the deals.

Screenshot 4 - Cross Border Deal Flag

 

 

FAQ: How can I find ‘dead’ companies?

Bloomberg

Use the function SECF (Security Finder). Open the tab Eqty – you will have more search options there. Search for the name of the ‘dead’ company. When the list is too long, you can limit the results by selecting a particular Trading Status, for example ‘Acquired’. In the list of results, click on the name of the security you think is the correct one. Then choose the DES (Security Description) to get more information about that particular security. In some cases you will see ‘acquired by …’ on top of the page.

Datastream

In Datastream Navigator select the category Equities (you can find the option ‘Choose a single category’ in the left upper corner). Search by name. In the list of results you can recognize ‘dead’ equities by the greyed out information in the History column. When you click the arrow in front of a name, you can get a preview of the price chart – there you can also see the equity is ‘dead’.
A tip: When you download for example the stock price (datatype = P) for a ‘dead’ equity, the last known value is often repeated (padded). Instead of P enter P#T – then the data is no longer padded.

CRSP

The ‘Code Lookup’ option in CRSP contains live and dead companies. In the Code Lookup results you can check the column ‘Last Date’.

ThomsonOne/WorldScope

‘Dead’ companies are hard to find in ThomsonOne/WorldScope, even when you search with an identifier like SEDOL. However, with the Excel add-in it is often possible to get data for ‘dead’ companies. In the Report Wizard the SEDOL or ISIN codes are recognized. You can collect them for example with Datastream, or you find the SEDOL-code in the M&A module of ThomsonOne.

CompuStat Global/North America

With the Company Lookup you can fin ‘Dead’ companies are included in CompuStat – by default active and inactive companies are included in your output.

FAQ: I only want companies with data for all requested years in my sample – CompuStat

When you download data from CompuStat you specify the time frame of your request at step 1, for example 2004-2013. Some companies will have data for all the years in this time frame, but others have not – because they became a public company after 2004 or were no longer public before 2013.

You can delete these ‘incomplete’ companies in Excel, by using the function ‘CountIF’. With this function you count the number of times the identifier of the company appear in the complete list of identifiers. Is that number below the maximum number of years in your sample, then there are missing years.

In the function CountIF you have to enter the Range – that is the column with the identifiers – and the Criteria – that is the cell with the identifier of one particular company. You can drag this function to the end of your list.

countif

We advise to use an identifier, like the Global Company Key, instead of the name, because names can change.

Cross-listings in Bloomberg

The Equity Screening (EQS) module in Bloomberg offers ‘multi-listed’ as a search criterium. This is not shown by default: click 45) More Categories to see more screening criteria – then you will see 39) Multi-Listed (‘screens for securities listed on multiple exchanges’). You can select exchanges or countries from the list.

multi-listed

For example: when you choose ‘Euronext Amsterdam’ and ‘New York’ you will get a list of the companies that are listed on both exchanges and the tickers of these listings. The number of matches is the number of securities. In the list of results you will only see the listings on the exchanges you selected, so it’s possible these companies are listed on other exchanges as well.

result multi-listed

In this example we added the ISINs and the SEDOL codes to the display. The results are grouped by ‘Parent Company’ – you can find that option at the bottom of the screen.

 

Headers in output from CompuStat Global

Until recently, when you downloaded data from CompuStat Global, the first row of the data contained the header, for example ‘Sales-Turnover (net)’. Unfortunately we now only get the codes used by CompuStat Global in the header.

CompuStat Global headers

You can add the headers to your output manually, but you can also do this:

  • In CompuStat Global open Variable Descriptions at the top of the page.
  • Select and copy the information under Variable Reference – this table contains the codes and the descriptions of (most of) the variables used in CompuStat Global.
  • Paste this in a new excel sheet in the file with your CompuStat data and delete the empty column.
  • In the sheet with the CompuStat data, insert an empty row, above or below the row with the codes.
  • Now you can use the Excel-function Vlookup to add the description. The screening variables, like company status (COSTAT) and country code (FIC) are not in the list and thus not recognized.

CompuStat Global headers2

FAQ: CUSIPs in WRDS output

When you download data from WRDS to an Excel spreadsheet and include CUSIPs in your file, you often have CUSIPs that that look weird. See this example from CompuStat North America Index Constituents, the column Company Cusip should contain 9-digit CUSIPs:

cusips with E

The E’s in the CUSIPs of Dun & Bradstreet and Dr Pepper are recognized by Excel as a mathematical constant (2,718) and the CUSIPs of Amazon.com and Apple are incomplete (they start with zero’s, but these are not visible). This can very unhandy if you use these codes as input in other databases.

You can solve this by downloading the data from WRDS in another output format (comma-delimited text or tab-delimited text) and then:

  • Open a new Excel sheet
  • Open the ribbon Data
  • Choose From Text
  • Browse to the CSV or txt file and click Import
  • In the Text Import Wizard, mark Delimited and click Next
  • In step 2 of the Text Import Wizard choose tab or comma (depending on the output format of your file) and click Next
  • In step 3 of the Text Import Wizard search for the column with the CUSIPs in the Data preview and mark Text under Column data format.
  • Click Finish to import the data

Text import wizard 3

You will now have 9-digit cusips in the output. The disadvantage is the header of the columns: the headers in an Excel download from WRDS contain more information. You can solve this by downloading the data as Excel as well and copy and paste the headers.

cusips with E2

The Datastream Event Study Matching Tool – Run-time Error ‘1004’

When you use the Datastream Event Study Matching Tool, you can get the error message “Run-time error ‘1004’: Application-defined or object-defined error’. When you click the button Debug you’ll see there is ‘something’ wrong with the dates (but what?).

1004

From what we have seen this error message appears when there is no data or not enough data for a particular company in the requested window, often because the base date of the serie in Datastream is after the event date entered in the tool.

You can solve this by deleting that particular event from your list of events and running the tool again. This can be very time consuming and you have to check for other events as well.

You can also do this: leave all events in your list, but before hitting the button Process Table in the tool, in the sheet REQUEST_TABLE, click the button Options and enter something in the box No data string (for example NA). Datastream will put NA in cells without data, and the macro in the Event Study Matching Tool will return NA’s in the output of the tool. This allows the macro to run with (hopefully) not run-time errors and you can easily find the events you have to delete in the output.

no data string

na in output

Error message in Datastream – caused by hidden formatting

Recently some students got error messages when they tried to download data from Datastream in a Time Series Request ($$”ER”, E100, INVALID CODE OR EXPRESSION ENTERED), while the identifiers used were correct according to Datastream Navigator.

We found out that Datastream doesn’t recognize the identifiers, because there is some hidden formatting in the cells in Excel. Unfortunately the human eye can’t see this; to us the identifiers look correct. You can make this visible by copying the identifiers, pasting them in a Word file and activate this button: show hide

You will see for example:

hidden formatting

 

How to solve this?

  • You can delete the formatting in Word (in this example the formatting ​), and copy the list back into Excel.
  • When you have a list of identifiers with always the same number of digits, you can use the left function. In this example, you can use =left(cell, 8), because all identifiers should have 8 digits. Counting from the left side of the identifier, only the first 8 digits are returned. It might be a good idea to copy this new identifiers and paste them back into Excel with Paste Special > Values.
  • When you do a Static Request in Datastream, and request for example the datatype ‘name’, Datastream will show you all identifiers, for example like this:

Excel

When you delete the question marks, you have identifiers that can be recognized by Datastream.