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.
First you copy US44044KAA97 into B1. In B2 you enter this formula:
=BDP(B1 & ” corp”,”FUNDAMENTALS_TICKER”)
In B3 you use the contents of B2 and use concatenate to put “Equity” behind it. Use this formula:
=B2& ” Equity”
The last step is to use this function in cell B4:
to find the ISIN you are looking for.
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 “@”.
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
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
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.
Below you’ll find an example of a search list for cross border deals.
In your report you can use “Cross Border Deal Flag ” to verify the deals.
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.
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.
The ‘Code Lookup’ option in CRSP contains live and dead companies. In the Code Lookup results you can check the column ‘Last Date’.
‘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.
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.
We advise to use an identifier, like the Global Company Key, instead of the name, because names can change.
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.
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.
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.
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.
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.
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:
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
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.
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:
You will see for example:
How to solve this?
- You can delete the formatting in Word (in this example the ), 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:
When you delete the question marks, you have identifiers that can be recognized by Datastream.