IPO data and quality

One of the most often used sources for data on Initial Public Offerings (IPO) and follow-ons is the databse Global New Issue(s) from Securities Data Company (= SDC, now part of Thomson Financial/Thomson Reuters). While looking for information on IPO’s I came across two websites that report on IPO data and data issues when dealing with data from SDC.

The first website is created by Jay R. Ritter, Professor of Finance at the Warrington College of the University of Florida. A number of historical data files or refferals are listed/offered that provide additional data or alternative sources. The oldest data available is from the Nineteen seventies.

The second website is created by Alexander Ljungqvist, Professor of the Stern School of Business at the New York University. He has compared data from IPO’s with data gathered from SDC Platinum’s Global New Issue database and reports on problems for shares outstanding, venture backing, and syndicate size.


Combining searches in SDC Platinum

When using the (older) windows software to access SDC databases each search is automatically combined and you get less and less records in your search result. It is, however, also possible to combine searches with logical operators that are similar to boolean operators like OR, AND, and  NOT. This allows you to create different types of searches. The operators that are available in SDC are called: Union (= OR), Intersection (= AND), and Difference (= Minus).

The example I give here concerns software companies and the SDC database Global New Issue (GNI). To find software companies in the GNI database you can use different types of classification. Examples that can be used in the GNI database are the Standard Industry Classification (SIC) and the North American Industry Classification System (NAICS or NAIC). The NAIC classification was developed since 1997 and is/was intended to replace the SIC. So far, the SIC system is still being used today in many databases.

According to the SIC system the code for Software is: 7372 (= prepackaged software). The NAIC classification uses two codes that (combined) compare to this: 334611 (= Software reproducing) and 511210 (= Software Publishers). If you want to find out how SIC or NAICS codes compare or can be ranslated into matching codes, you can use either the website of the NAICS association or the translation tool of the Amadeus database (which allows the translation of more than 10 different classification systems in comparable codes).

Step 1

  • Start up the GNI database using the SDC software
  • Select the variable “Issuer/Borrower All SIC
  • Search on “Software” and select code 7372 by Double-clicking

Step 2

  • Go to the top of the screen and click the button USE

Step 3

  • Click the line “Use Request 2
  • Right-click this line and select the option Modify 

  • In the pop-up that now appears: change the number 2 to the number 1 and click OK

Step 4

  • Go to the tabAll items” and search on the keyword NAIC
  • From te search result select the variable “Issuer/Borrower All NAIC (code)
  • Search on “Software” and select both codes 334611 and 511210 by Double-clicking
  • Click OK to continue

Step 5

  • From the menu at the top in SDC now click: Utilities
  • From the pull-own menu click the option: Logical Set Operations

Step 6

  •  A new pop-up has appeared. Using the mouse now “click and drag” line 2 into the box Request
  •  Now do the same for search line 4
  • Check that the operation Union (= OR) is marked (radio button) and click OK.
  • The result is an extra search line: Logical Set Operations 2 Union 4.
  • Now click the button Execute at the top of the screen in SDC.

In the example you see that the OR combination of SIC and NAICS gives a higher number of records. The same type of search and combination allows you to (for instance) combine lists of companies where you have ISIN codes for companies in list1 and CUSIP codes for companies in list 2. The logical operators are also available to be used in other SDC database (Mergers & Acquisitions etc.) !


Datastream and Accounting Standards

Information on accounting Standards in Datastream is available through the Worldscope database. The code to get this data for annual statements is WC07536. Below you see an example search result or companies:

Currently, the following types of (historical) Accounting Standards are separately identified and indicated using this Worldscope code:
01 Local standards
02 International standards
03 U.S. standards (GAAP)
04 Commonwealth countries standards
05 EEC standards
06 International standards and some EEC  guidelines
07 Specific standards set by the group
08 Local standards with EEC and IASC guidelines
09 Not disclosed
10 Local standards with some EEC guidelines
11 Local standards – inconsistency problems
12 International standards – inconsistency problems
13 US standards – inconsistency problems
14 Commonwealth standards – inconsistency problems
15 EEC standards – inconsistency   problems
16 International standards and some EEC guidelines – inconsistency problems
17 Local standards with some OECD guidelines
18 Local standards with some IASC guidelines
19 Local standards with OECD and IASC guidelines
20 US GAAP reclassified from local standards
21 Local standards with a certain reclassification for foreigners
22 other

Depending on the individual company information available through Worldscope, you can also do a search that will provide the same type of information for quarterly statements. Just add an A after the Worldscope code and change the frequency of the download: WC07536A. below you see an example search:


Using Excel Vlookup to match data from datasets

Many people use the Excel Vlookup function to merge data from 1 or more sheets and have it presented in a single table for use later. This can be to create a single overview with all the data together from several downloads from 1 or more databases. Alternatively Vlookup can be used to match company identifiers from more than one database as a preparation for an event study. An example of the last situation could be as follows:

1) You may want to get the report dates for the quarterly statements of companies from the Fundamentals quarterly database of Compustat North America. You want to see what the price did within a certain event window before and immediately after in a small window around the report dates.

2) The first step would be to download your selection of companies and data from the Compustat North America Fundamentals quarterly database. In your download you automatically get the unique Global Company Key (= GVKEY) for each quarterly reported date (for the time period you choose). This means that you get (duplicate) GVKEYs for each quarter in the download.

3) As fiscal years and report dates differ a lot between many companies, you would have to do many searches in the Compustat North America Security Daily database to get data for all the (event) windows. There is an easier way to do this: use the CRSP tool Daily Extract with Time Window. Using ths tool all you need are the report dates for all the quarters. To use the tool, however, you need to match the company Identifiers for the Compustat north America dataset with the Permno‘s for CRSP. The event tool can only handle Permno’s. Luckily there is a CRSP/Compustat merged database.

4) Create a text file with all the GVKEYs from the Compustat download you did earlier. To speed things up you could use the Excel option remove duplicates to remove duplicate GVKEYS. I explained how to do this in an earlier post.

5) Upload the text file with GVKEYs in CRSP/Compustat merged database and download the necessary identifying data. The download should include the variable “Historical CRSP PERMNO Link to COMPUSTAT Record“.

6) Using the Compustat download and the CRSP/Compustat merged download you can now add the Permno identification codes you need for each quarter. You can find a short movie here that shows how you can get the Permno in the Compustat dataset using a dataset from the CRSP/Compustat database. It shows how to use the Excel Vlookup function:

7) The final step is easy: Copy the column with the report dates and the column with the Permno‘s to a new sheet and format them for use with the tool CRSP – Daily Extract with Time Window.
The text file that you can use should look as follows:
Permno Reportdate
29161 19761020
15763 19841229
10093 19860215
10161 19860821
10161 19860925
10341 19860925
65111 19880203