Bankscope cancelled and replaced

Bureau van Dijk (BvD) publishes the Bankscope database. It was announced recently that Fitch, the main provider of the Bankscope data, has terminated it’s contract with BvD. As a result, the availability of bank data will undergo some important changes, with possibly disruptive consequences.  BvD is currently working on a new Bankscope data product: Orbis Bank Focus. However, the coverage of the new database is limited at the moment: historical coverage in this new database is severely restricted, with up to only three years of data (Bankscope has ten (or more) years of history). BvD are prioritizing cross-sectional coverage, and are working on including recent & historical data for banks in different countries. The new product is obviously a serious restriction for academic research.

The current Bankscope database will remain available until the end of 2016, at which point the contract between Fitch and BvD will end, and BvD will need to take down the data. If this affects your research, I advise you to download data as soon as possible.

Email

Changing Datastream data & Stata

The past few weeks I have been learning about and working with Stata. This program can handle a lot of data and uses commands to edit data or analyse it. A sequence of commands can be saved in .do files and then rerun as a script. There are many commands available and one of them is very handy when it comes to changing data from columns to rows. It is similar to the transpose option that Microsoft Excel offers for quick changes. It is the reshape command.

In this blog post I will use the reshape command to change Datastream data as an example. Similar work can be done for other downloads from databases like Amadeus or Bankscope.
For downloads it can take a bit of work to change the data and rework it before Stata can be used to merge it with other data. In the case of Datastream this has everything to do with the fact that Datastream does not repeat an ID for each year of data you download (unless using a Request Table search). Example screenshot original download from Datastream (transposed):

The following changes need to be made in Excel:

1) Get the ID back from the download without the extra Datastream codes. This can be done using the function MID(Cell,Start,Number) (= in Dutch versions of Excel: the Deel() function). MID() allows you to get part of the contents back from a Cell. Example:

2) Next you need to create column year headers that start with a text character and then the year of the column (this step is important for Stata later). It can be done smartly using a specific cell for the header and then combining it with the original year above each column as follows with the formula =($A$1&(B$1)). The dollar signs fix certain cells or a row (or column). Example:

3) Now save the Datastream Excel file without these formulas. If necessary use copy > paste special > Values

4) Next we start up Stata and get the file. In this case we prepared the Datastream file nicely and can therefore use the command: import excel using DS-Prepared.xlsx, firstrow
The firstrow option tells Stata that this row has the variable names and lables. Example:

5) When we look at the data with the browse command you see that the data looks basically the same as how it appeared in Excel:

Only two things are important here:

  • The data is not yet formatted as we need it: the numerical data are now strings (red) and need to be changed later
  • To later merge data the company ID’s need to be combined with the years

6) We now transpose the data using the command Reshape. This command has many options and allows you to rework tables. If you need more explanation on how it works you can type the command help reshape and Stata will provide much information on how to use the command. For this example the command is:

reshape long Y, i(CompanyCode) j(year)

In a nutshell this tells Stata that the years for the columns need to be repeated for the observations/records and can be found in the names of the variables. The ID for each company also needs te be repeated for the years of all observations. Example result:

When you use the command browse again you see the result of the data change:

7) We now need to create a unique ID combination to later merge the data. To do this we need to turn the years back from numerical values into strings using the tostring command: tostring year, replace. The final step is creating the unique ID combination UID with the command: gen UID = CompanyCode+year

8) Now there are a few steps left to finalize the Datastream data but not all of it is necessary. One thing is necessary: for Stata the numerical data is still a (text) string and now need to be changed. Use the destring command to as follows: destring Y, replace force

Destring also removes any values that were not numbers (but text) and replaces them with an empty cell (.) because of the option force (this can be dangerous if the content of a cell is a combination of a numerical value and text).

Optional stuff:

  • Use the drop command to remove unnecessary columns
  • Remove observations without data using, for example: drop if mi(Y)
  • The command order can be used to reorder data and put the UID variable in the first column
  • The command rename can be used to rename variables

The end result could look as follows:

To make stuff easier you could create a .do file that contains all the commands in sequence and this allows you to reuse it in similar situations for similar downloads. Here you can download an example .do file that shows examples of all the commands.

This example applies to Stata editions 13 and 14.

Email

Historical shareholder lists & Amadeus

A database like Amadeus (from Bureau van Dijk) has shareholder data that can go back to 2002. In a previous post I mentioned how you could find out how the types of shareholders could be identified using the codes. If your license / subscription includes the ownership module the information is available for European companies of the industrial type. Amadeus does not include financial type company data like Banks or Insurance companies. If you need these types of companies or a Global geographical coverage you could go for a database like Orbis or Bankscope.

The only way to view the full historical data for the current and previous shareholders for companies, is using the Ownership Data option that appears in the menu (on the right side of the screen) when you call up the basic Snapshot on a company from your results list by clicking on the name.

There are two presentation options for the Shareholder data:

1) History – all data
The first option presents a long list of all recorded data in the database. At the top the current shareholders are listed and below this the previous shareholders:

2) History – at each closing date
This option presents the recorded shareholders in columns by year. Allthough this makes it easier to view the history of ownership, if many changes occur in each year this makes interpreting the data not always easier:


Information on shareholders includes data like: type, percentage direct ownership, total percentage ownership, country, etc. Also included is information on Source Identity and Date of Info. Bureau van Dijk uses many sources for the Shareholder type data and the main sources include:

  • Annual Reports (RS or RM)
  • Private correspondence (PC)
  • SEC Filings (PX or NQ)
  • Stock exchanges (SE)
  • Company web-sites (WW)
  • Telephone calls (TC)
  • Press News (PN)
  • Additional sources (OS)
  • Information providers (IP)

The choice for the data listed as ownership (for each month) depends on the sources. The Ownership Guide also explains how the sources are used and also has a full list of the Information Providers used. This allows you to find out which Source Ident. codes concern what source for each month.

Email

Companies (Banks) and Fair Value

I recently got a question on the availability of Fair Value data for Banks. Fair value is defined as: a rational and unbiased estimate of the potential market price of a good, service, or asset. It takes into account objective and subjective factors to arrive at the fair value of an asset or liability. In accounting, fair value is used as a certainty of the market value of an asset (or liability) for which a market price cannot be determined (usually because there is no established market for the asset). Under US GAAP (FAS 157), fair value is the amount at which the asset could be bought or sold in a current transaction between willing parties, or transferred to an equivalent party, other than in a liquidation sale.
More information on the concept of Fair Value, as opposed to the historical value, is available here. Three levels can be distinguished:

  • Level One: The preferred inputs to valuation efforts are “quoted prices in active markets for identical assets or liabilities,” with the caveat that the reporting entity must have access to that market.
  • Level Two: This is valuation based on market observables. The second arises when the owned assets and owed liabilities are similar to, but not the same as, those traded in a market.
  • Level Three: This is a valuation based on “unobservable.” If inputs from levels 1 and 2 are not available, this category allows for situations in which there is little, if any, market activity for the asset or liability at the measurement date. The “observable inputs” are gathered from sources other than the reporting company and that they are expected to reflect assumptions made by market participants.

The following databases carry fair value data: Compustat, Datastream and Bankscope.

The Compustat North America Fundamentals Annual & Fundamentals Quarterly databases have some Fair value variables. Compustat Global databases do not have any of these. The Compustat database Bank Fundamentals Annual and Bank Fundamentals Quarterly databases also have the following items:

  • Fair Value of Options Granted
  • Total Fair Value Assets
  • Total Fair Value Changes including Earnings
  • Total Fair Value Liabilities.

Datastream covers some limited information but not much is available for most companies. A separate license is required for some of the data items which are not included in a default subscription to WorldScope (annual report data source). The more commonly availabe variable here is: Fair Value of Options Awarded. A list of currently available datatypes / variables is available here:

The database Bankscope covers banks worldwide and includes many more data variables that are related to Fair Value. This includes  Fair Value variables for Total Assets, Total Libailities and changes. In addition, there are also separate level 1,2 & 3 variables for both total assets and total liabilities. See screenshot for an example:

N.B.: In Bankscope you first make a selection of banks. When you have called up a list of these banks you can change the list presentation to include any available variables by clicking the button Columns (in the middle of the screen) or the button Add (to the right side of the list).

Email

Shareholder types in BVD databases

Many of the databases of Bureau Van Dijk (BVD) allow you to call up information on the ownership of companies. When you call up a full report for a company in the database it is possible to go to a specific report with historical information on shareholders. Not only does it list the shareholders, but also percentages of ownership, month and year of the information, and type of ownership. Below you see an example screenshot for a company from the REACH database:


The type of shareholder is indicated in this overview with letters: A, B, C, etc. You can adjust the presentation of the type of information presented using the option Filters at the top of the list at the Options. You can also view a subsset of the available types of shareholders:

Unfortunately the list only shows letters and the filter option do not tell you which letters represent what types. If you do some further research in the online support/help information you can find out which letters stand for what types. Below you find an overview in the Dutch and English Languages:

Dutch Translation (used in REACH):
A = Verzekeringsmaatschappij
B = Bank
C = Handel- & Industrie-organisatie
D = Naamloze particuliere aandeelhouders, geaggregeerd
E = Beleggings- & pensioenfonds / Nominee / Trust / Trustee
F = Financiële instelling
I = Eén of meer bij naam genoemde individu(en) of familie(s)
J = Stichting / Onderzoeksinstituut
L = Ander naamloze aandeelhouders, geaggregeerd
M = Werknemers / Managers / Directeuren
P = Private Equity firma’s
S = Overheidsinstantie / Staat / Regering
V = Durfkapitaal
Y = Hedgefondsen
Z = Beursgenoteerde bedrijven

English Translation (used in Amadeus, Bankscope, etc.):
A = Insurance company
B = Bank
C = Trade & Industry organisation
D = Nameless private stockholders, aggregated
E = Mutual & Pension fund / Nominee / Trust / Trustee
F = Financial company
I = One or more named individuals or families
J = Foundation / Research Institute
L = Other named shareholders, aggregated
M = Employees/Managers/Directors
P = Private Equity firms
S = Public authority/State/Government
V = Venture Capital
Y = Hedge funds
Z = Public (Publicly listed companies)

MINT/ORBIS Additional categories:
H = Self ownership
Q = Branch

NB: In the help section it is also possible to get some definitions for some of these catagories (see the Orbis/Mint ownership specific ownership section).

Email

Bankscope and Consolidation codes

In the previous two posts I went into consolidation codes and how these can be used in Amadeus. Bankscope also uses consolidation codes to indicate the level of consolidation for the different financial statements. In a previous post I mentioned how the use of these codes might interfere with finding out how many banks you can actually get when you do a search. Limiting a search to companies with certain types of statements in Bankscope works similar to the Amadeus database: through the search menu. In the (advanced) search menu select: Accounting data > Consolidation code.

The types of consolidation codes available in Bankscope vary slightly from those in Amadeus. Below you see an overview of the codes and their explanation:

  • C1: statement of a mother bank integrating the statements of its controlled subsidiaries or branches with no unconsolidated companion
  • C2: statement of a mother bank integrating the statements of its controlled subsidiaries or branches with an unconsolidated companion
  • C* Additional Consolidated statement
  • U1: statement not integrating the statements of the possible controlled subsidiaries or branches of the concerned bank with no consolidated companion.
  • U2: statement not integrating the statements of the possible controlled subsidiaries or branches of the concerned bank with an consolidated companion.
  • U* Additional Unconsolidated statement

Additional companion statements (C* or U*)
The additional statements might differ from the main statements according to one or more of the following criteria:

  • The source used to spread the data is different (FDIC, SEC…)
  • The accounting standard (generally historical accounts in Local GAAP, more recent ones in IFRS are in the main statements)
  • Inflation adjusted vs nominal values
  • Proforma accounts vs original accounts

NB: When you are using both databases to select companies for your research you also have to take into account, of course, that similar codes in Amadeus and Bankscope may get you data, but the accounting rules for the data items from the Annual Accounts are different for different types of companies and therefore are difficult to compare between both databases. The same applies too to companies in a single database.

Email

Bankscope: identifying banks

Bankscope is a database that covers banks around the world. Each bank report contains detailed data from consolidated and/or unconsolidated balance sheets and income statements. Also included are Fitch Ratings and other sources. Bankscope offers company and country risk ratings, reports, ownership, and some security and price information. This database is produced by Bureau van Dijk and, depending on the license, covers 30.000 banks and up to 16 years of data (for example: the license at the Vrije Universiteit currently covers only banks from OECD countries and about 8 years).

When you are searching for companies it may initially seem odd, that in the search result in Bankscope there are many duplicate banks. See the example below:

When you look carefully you see that in (the middle) column “Cons. code” you can identify specific statements from companies. If you want to look at specific company reports or when you want to find out how many actual banks you initially found you can do this as follows:

  • Click “Show Search Strategy” above list of banks
  • Choose the option “Add a search step
  • Click the Search option: “Account Availability” and select “Living Banks“.


The search result will now list how many banks were actually found instead of the number of financial statements that were found. The example below shows that from the initial search result of 81 statements these were from 19 actual banks. Keep in mind that numbers in a search result can change quickly because the Bankscope database is often updated and new statements or banks may be added.

Email