Excel and Filling up blank cells

Recently I got a question on how to arrange the data in Excel for an analysis where some data was missing. Below here you see an example download from the database Joint Ventures & Alliances from SDC Platinum. Not every row has data but each deal starts on a new row with the date variable “Alliance Date Announced”. Each alliance also has a unique Deal number (3rd column):

In some programs (to do an analysis of the data) what you need is to have the data repeated for each row (observation) related to that specific instance and not any rows with blanks (unless a variable for a row/observation contains no data of course). In the following example the data looks like the previous picture, incomplete:

You want it to look as follows:

Excel has an option that allows you to automatically fill in all the blanks. This works as follows:

Step 1: Make sure that you click on a cell inside the data table

Step 2: In Excel click the option in the Excel ribbon: Find & Select > Go To Special

Step 3: In the pop-up mark the option: Blanks

Step 4: You see that Excel now tries to find all the empty cells in the active table and they are highlighted

Step 5: Use your keyboard to type “=
Step 6: Now click the Upwards arrow on your keyboard
Step 7: Use the keycombination: CTRL + Enter

N.B. 1: Please remember that after the final row Excel tends to add another row. In this case you need to delete it again as this would only add a duplicate observation to the table.
N.B. 2: Dates may be filled in as numbers. This is fine because in Excel dates are really numbers (number 1 is 1-1-1900). Just use formatting to make sure these numbers are presented as dates again

Warning: When you use the option to complete data for each observation, be sure that the correct data is duplicated. In the example output from SDC Platinum (below) you see columns that relate to events but which are spread over more rows. It may be an idea to copy parts of the data to separate sheets (for instance using unique event/deal numbers) to duplicate data there and later merge the data tables again. See columns G, I and J.


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.