Thomson content update February 2017

At the end of January a change was made that has impacted the content of SDC Platinum, Thomson One and the newer Eikon product. Thomson Reuters uses league tables that capture market/deal activity for both the mergers and acquisitions, equity issuance, debt issuance, syndicated loans and US municipal bond markets. The rankings in the league tables are (in part) based on imputed investment banking fee variables.

Investment banking league tables rank investment banks on their level of involvement in specific types of transactions. An individual league table might, for example, rank the investment advisors of merger and Acquisition deals (usually by aggregate deal volume) and another might rank the underwriters of equity or fixed income offerings. (Source: SC Johnson College of Business website)”

The Thomson Reuters historical imputed fee data & history was created & added to the Thomson database(s) from 1998. Freeman & Company have been an exclusive content provider of investment banking imputed deal fees to Thomson Reuters since 2005. Freeman’s fee engine is powered by Thomson Reuters Deals data.

Starting in February 2017 new imputed fee data is created & added according to the proprietary StarMine model of Thomson. The older Freeman imputed fee data will remain available in the databases. Only in a limited number of cases some of the older data may be replaced with newer imputed fee data: historical deals where fee related fields are updated will also be given a new Thomson Reuters imputed fee.

The following variables are impacted in SDC Platinum databases:

Mergers & Acquisitions database (Deals database):


Global New Issues database (Equity, Bonds & Syndicated Loans):


An update of the SDC Platinum software is required to keep everything up to date and available as per usual. The newer Thomson products Thomson One and Eikon are also impacted but as these products are web-based no software updates on the client side are necessary. The data in the newer products has already been updated. More background information can be downloaded here.


SDC Platinum save problem

In some SDC databases it may happen that you get an error when you are exporting / saving data. Most commonly you encounter this problem when you try to use a custom report. The database where you most often get problems is the VentureXpert part database that has information on funds and firm investments. The error that pops up is “Invalid column“. See example:

The problem may be related to the output option that was chosen. There are just two choices for output: Excel and Text (= plain, fixed width). When you are downloading much data the text choice is usually the best option because the (old) SDC software works with the limits of the old(er) Excel editions and Windows. Also, the text download is much faster than the Excel download. By right-clicking the report you can change the output to text format. In addition, you also need to change the Options. See example:

In the options tab “Titles and Options” you need to mark the option Text (instead of Columnar Grid). See example:

After the changes you get a plain (fixed-width) text file that may be used in programs like Excel, Stata and SPSS.


SDC Platinum & Coco bonds

Contingent Convertible Bonds (Coco bonds) are a relatively new financial instrument that are used by banks (among others) to meet current capital requirements without running too much risk compared to traditional bonds or convertible bonds. A Coco bond can be converted into regular equity. Below follow two defintions:

Wikipedia definition:
A contingent convertible bond (CoCo), also known as an enhanced capital note (ECN) is a fixed-income instrument that is convertible into equity if a pre-specified trigger event occurs.

Investopdia definition:
Contingent convertibles (CoCos) refer to a security similar to a traditional convertible bond in that there is a strike price (the cost of the stock when the bond converts into stock). What differs is that there is another price, even higher than the strike price, which the company’s stock price must reach before an investor has the right to make that conversion (known as the “upside contingency”).

The COCO bond data is available in the SDC Platinum database called Global New Issue. It requires a combination search of Security Type and Transaction Type to get all the data from both the Equity databases and Bonds databases.

  • Security Type: Thomson Reuters code for the type of security being offered
  • Transaction Type: Similar to security type. Itemizes the major characteristics of the issue. Data item is useful for searching all debentures. Rather than having to choose all security types within security type that have debenture in the security type, this can be used to choose an overall security type.

The search in SDC goes as follows:

1) Start up SDC and go to the tab with Global New Issue databases

2) Select both the Equity and Bonds databases. This is necessary because Coco bonds can be found in both types of part databases:

3) For the example I searched all years of the databases. Now I use the variable Security Type to narrow down to type 2006 = Coco bonds. This variable applies to the Equity databases. See:

4) Now use the variable Transaction type to search for Coco bonds. This variable searches in the Bonds databases. See:

When you select the Transaction type you need a question will pop up asking you if this search item should be used in all part databases, even though not applicable. Reply no to this question to avoid getting inaccurate data:

5) When the search is run the result will cover both Equity and Bond databases. I combined the variables with USE and logical operators. See a previous post for an example of how to do this. Result:

Important: The amount of Coco bonds in this result is not large: approximately 260 (at this point in time). Based on papers from the past I am unsure if the SDC covers the whole market where Coco bonds are concerned. According to the Thomson Reuters helpdesk, however, these are the only options to get Coco bonds from their SDC databases. I tried using alternate keywords like Enhanced Capital Note or ECN but was unable to find more. Possible alternatives for finding data on Coco bonds are: the Dealscan database or Bloomberg.


Industry and economic classification systems

Many people who do research on companies and markets use a system like the SIC codes or NAICS codes. Both systems allow you to determine the activities for companies. The SIC code system is the oldest of the two and can be downloaded as a variable for companies in many databases. A company can get assigned one or more SIC codes. Sometimes a primary SIC code is given, which indicates the main activity. In essence, the Standard Industrial Classification (SIC) is a system for classifying industries by a four-digit code. The system was originally developed in the United States in 1937, and it is used by US government agencies to classify industry activities.  Basic information is available on Wikipedia.
If your research covers a long time frame it may be necessary to determine whether changes that occur over time in the SIC system have an effect. Luckily, an older version of the SIC code system is available. The U.S. Department of Labor, Occupational Safety & Health Administration website allows the user to search the 1987 version SIC codes.
A more recent version of the SIC system is available on the SEC website of the Division of Corporation Finance: Standard Industrial Classification (SIC) 2011 Code List.

The SIC system is also used by agencies in other countries. In the United ingdom they have developed their own version of the SIC codes. This United Kingdom Standard Industrial Classification of Economic Activities (UK SIC) is used to classify business establishments and other standard units by the type of economic activity in which they are engaged. The new version of these codes (SIC 2007) was adopted by the UK as from 1st January 2008. Older versions of the UK SIC system are also available online, specifically, the UK Standard Industrial Classifications 2007, 2003, and 1992 versions.

The North American Industry Classification System (NAICS) is the system used by US Federal statistical agencies for classifying businesses for the purpose of collecting, analyzing, and publishing statistical data related to the U.S. business economy. The NAICS system was developed under the auspices of the Office of Management and Budget (OMB), and adopted in 1997 to replace the Standard Industrial Classification (SIC) system. From 2002 the system is increasingly used. Different versions are available through the US Census website.

The NACE-code (Nomenclature générale des Activités économiques) is a code which is largely used in the European Union and its member states use it to classify commercial and non-commercial economic activities. It is mainly developed as a useful instrument when collecting data and publishing economic statistical overviews. Many companies and organizations exhibit a diverse range of activities. Companies only get assigned a single NACE code, however: the code that indicates the primary acrivity which contributes the most to the total added value of a company. The first version of the NACE was created sometime around 1970. The first revision was published in 1990 and was called NACE Rev. 1. The second major revision (NACE Rev. 1.1) took place in 2002. The second revision was intended to synchronize the system with the  “International Standard Industrial Classification of all economic activities” ISIC of the United Nations. The different NACE versions can be found on the Eurostat website. The Dutch national SBI code system (which replaced the original BIK code system) is based on the NACE system.

The International Standard Industrial Classification of all economic activities, abbreviated as ISIC, is a standard used by the United Nations Statistics Division (UNSD). The ISIC is used to classify economic activities so that entities can be classified according to the activity they carry out.
The ISIC classification combines the statistical units according to their character, technology, organisation and financing of production. The ISIC is used widely, both nationally and internationally, in classifying economic activity data in the fields of population, production, employment, gross domestic product and other economic activities. It is a basic tool for studying economic phenomena, fostering international comparability of data and for promoting the development of sound national statistical systems.
The current and older ISIC versions are available on the statistical website of the United Nations.

When you are using a database, always check the help information to find out what version of an industry code system is being used. If a code system is available but no information on what version it is, you should find out by contacting the owner of the database. Some databases collect data but gather only historical (industry code) information from other sources without using their own version of such as system. The SDC Platinum databases have historical SIC and NAICS codes as they were indicated by the sources that Thomson Reuters uses to collect data.

N.B.: In addition to the aforementioned industry codes there are many more code systems that were developed for statistical purposes in specific countries. The database Amadeus has an easy tool in the help section that allows you to translate specific codes to other codes. This tool is available in the Bureau Van Dijk version of Amadeus:


Selecting Cross-border M&A deals in SDC

The Mergers & Acquisitions database of SDC Platinum has a variable that easily allows you to select deals between nations. This variable is the Cross Border Transaction Flag which says Yes or No depending on the situation. When you want to use this variable you need to be aware that the Cross-border variable indicates that the country/nation of the (direct) target is different from the country/nation of the acquiring ultimate company. The Immediate acquiring company may be in the same country or even state!

SDC definition of the variable CROSS: Cross Border Transaction Flag: Yes/No flag set to ‘Y’ when the target company (or assets being sold) in the deal is not located in the same country as the acquiror ultimate parent.

Example screenshot when only using the cross-border variable:

If your research requires that the companies that are directly involved in the deal, to be in different countries/nations, then you need to add additional search steps to limit the search result. You can select all target companies that are located in a certain country: for instance the United States. You can use the variable Target Nation for this. Next, you can add the seaarch variable Acquiror Nation and exclude (direct) acquiring companies that are also located in the same country: in this case also United States.
Example screenshot when including the extra steps:

NB: Make sure to define nations when appropriate. The above example I used was United States. In this case you would have had to decide if the following should be included: Guam, Hawaii, etc.


Bonds of the Islamic type

A recent research project of a student involved Islamic finance. This is a financial system that operates according to Islamic law (which is called sharia) and is, therefore, sharia-compliant (background information). The project of the student focused on the Sukuk bonds (background information).
One of the few sources that carries information on bonds is Datastream. At first I tried to get the historical prices etc. through this system but I was only able to find approximately 100 Sukuk bonds by doing a very basic search on the text: Sukuk. Other search options did not seem to get me the specific Sukuk bonds.

I contacted the Thomson Reuters helpdesk and from their answers it is now clear that Datastream (at the moment) is not the best source for information on these bonds. The helpdesk literally said: “We have no specific data type for this. We have very limited coverage of this in Datastream.” and ““I have closely worked with our data team and can confirm that there are no plans to extend coverage in 2014. There are plans to add more markets in 2015 however.
That means, that, for now, Datastream is not an option. A second source on Bonds data, however, proved more useful: SDC Platinum’s Global New Issue database (recent name: Thomson One’s Deals database). In the GNI database I could find Islamic bonds using the variable “Islamic Finance Flag” (available through the All Items search tab). The variable allows you to select these types of bonds.

The variable definition according to SDC is: Islamic Financing Flag (Y/N): A yes/no flag to indicate where the Loan Type of Bond Security Type is Islamic Finance. Islamic Finance transactions are deals that prohibit the making or receiving of interest payments.  Trade financing for these transactions is obtained through the purchase of inventory that will be resold at a pre-determined price on a future date (or “Murabaha”).

Because I do not know all that much about Islamic Finance, at first I thought I needed to limit the search further in the GNI database to get the bonds. Using the variable “Security Type” it is possible to limit the search to Bonds. Using the variable the number of records was narrowed down by a factor of 20 an I was left with only 124. When I removed again the limitation and created a report that included the “Security Type” variable it was clear immediately that most Islamic Finance bonds also have the name “Islamic Finance” as Security Type. In the end I could find about 2450 bonds. The amount of data on these bonds is limited, however.

If you want to have additional historical time series data like prices, you will have to find another source for this. I have seen no examples but I have heard that Bloomberg might be an option.


Combining data: Another Vlookup example

In the past I created a Vlookup example to show it is possible to use Microsoft Excel to combine data from datasets. The example was a small example based on CRSP and Compustat. I sometimes get questions on how to use Excel to do this and that is why I thought another example using other databases may prove useful. This time I use output data from SDC Platinum (sometoimes known as ThomsonOne) and combined it with data from Datastream.

Before you use Excel to combine data it is important to remember that (in my experience) Excel and Excel worksheets usually work from left to right. That means that very often searches will go through worksheets and within worksheets in rows fromk left to right while searching. The original source sheets where I want to extract data from, I usually put to the left of the destination sheet where I want to combine the data. This is usually my final worksheet on the right.


1) Download from SDC Platinum’s database Global New Issue
This database is also available from Thomson Reuters as the Deals database in Thomson One. My starting point for the exercise is a download of IPO data for companies that issued ordinary or common shares at an American exchange. I made sure that I included identification codes like ISIN in the download. Using these ISIN codes I will look up additional data and combine it later (other codes may also be used). As a first step I needed to filter the SDC output to make sure that I have only IPO’s with ISIN codes. The filter option in Excel is available in the first Tab (Home) on the right side and in the tab Data (= Gegevens).

  • Select the first row and click the Filter option.
  • Click the Filter icon and (in the ISIN column) unmark the option: Blanks (= empty cells / lege cellen).

2) Copy the filtered data

  • Use excel to copy the filtered result to another Excel sheet. We will use this sheet to add Datastream data to at a later stage. This will be the destination worksheet.
  • Make sure that the ISIN codes are copied and pasted as the first Column in this sheet: only 1 column of ISIN codes should remain!
  • Now copy the the column ISIN codes to a new sheet (If necessary remove duplicate ISIN codes using this option in the Excel Data tab [= Gegevens]).
  • Use the Datastream Add-in to upload the ISIN codes. Click the Datastream option called: Create list (/from range) and follow the steps.

3) Using the list we can now find out what the first trading days are for each stock.

  • Go to the Datastream tab/Add-in
  • Click the option Static search
  • Using the List picker button (below the orange button “Find series”) to select the uploaded list
  • Use the Datatype Base date (BDATE) and click the submit button to download these dates.

4) For the example we also need price data from Datastream
In this example we want the price data for the first 5 trading days, and specifically the original historical unaltered closing price: UP.

  • To download the data using the Request Table option in Datastream we first need to calculate the dates 5 days after each Base date. Do this by adding 5: like you would with regular numbers:
  • Copy the result in the column next to it. Use the option Past Special > values. This way you lose the function but keep the calculated dates.
  • Now use the ISIN codes as well as the column Base Dates and Calculated end dates to create a Request table search in Datastream.Make sure to have the original ISIN codes returned as well in the output! Another blog post explains exactly how to do a Request Table search. The output from Datastream should look as follows:

5) Remove unnecessary rows in Excel
For the matching data we first need to do the following:

  • Use the Excel Text filter option to kep only the price data and the ISIN codes. See example 8 and 9.
  • Now insert a column to the right of the column with the ISIN codes AND datatype code UP. Use the Left() (= links() ) function to extract the 12 digit ISIN codes.
  • Copy the result to a new sheet. Use Past Special > Values to keep the ISIN codes and remove the Excel functions.

6) Matching data using Vlookup
Important: make sure that both the source/data-sheets where you get data from are in the same Excel worksheet but to the left of the worksheet in which you want to combine the data (destinatin worksheet).

  • Go to the destination sheet with the IPO data from step 2 and click in the cell to the right of the last column with data
  • Use the Excel function search option (fx button) to find the Vlookup function (= Verticaal.zoeken):
  • The Vlookup popup has 4 arguments that need to be used:
  • Argument/box 1: indicate the value that needs to be looked up in a data/source worksheet: in this example the isin code of the first row:
  • Argument/box 2: indicate the full table where it needs to search for this specific value (= ISIN code). This includes the first column of ISIN codes and the second column with the first trading date = Base Date.
  • Argument/box 3: type in the number of the column where the data comes from that you want to see in the destination sheet (IPO data from SDC) from the source sheet (= output from static Datastream search). In this case column 2 (= B).
  • Argument/box 4: indicate with the text TRUE or FALSE whether the code should be an exact or approximate match. In this case we need FALSE as we need an exact match.
  • Click OK to continue. The result for the first row: it shows the base date from the original result sheet with the output of the Static search in Datastream.Copy this Vlookup function downward for the other rows.
  • In the column next to it (right side) we can add the first price from another worksheet (created at step 5) using a similar Vlookup option: