Blog2Blog Maak je eigen Blog2Blog | Gratis je eigen blog c.q weblog op internet
Financial Databases and Research
Financial Databases and Research

Identification of equities in Compustat

Posted in Wharton

In addition to information from annual reports Compustat also offers security/equity information in several part-databases. Compustat North America has two: Security Daily, and Security Monthly. Compustat Global has the database Security Daily.
If you want to identify each individual issue in a download for a company, you need the following combination of variables:
  • GVKEY = 6 digit Company identification code (full description)
  • Issue Type Code (TPCI) = a code that identifies the type of issue/security (full description)
  • IID = this code identifies a specific issue/security from other issues/securities of a company (full description).
The IID has several names/descriptions in the part-databases:
IID -- Issue ID - Dividends = IID name in the North America Security Daily database
IID -- Issue ID - Security Monthly Descriptor = IID name in the North America Security Monthly database
IID -- Issue ID - Daily Price = IID name in the Global Security Daily database

The only variable you do not need to select for your download the the GVKEY. It will automatically be included with any selection of data you download. You will need to specifically select the other two to include them in a download.
Below you see an example download for the company Dollar General Corp for the period 2007 - 2011. Apparently the first issue of ordinary shares was discontinued in August 2007 and the second issue of ordinary shares started in November 2009:

Important: When I am posted this item on the blog the variable "Issue Type Code (tpci)" was missing from downloads from Compustat Global Security Daily using the Wharton portal. I have contacted the support helpdesk about this and I have been told this error will be corrected in the next few days.

Author information: LinkedIn

10:41 AM - 8 July 2011

Downloading information from Compustat North America

Posted in Wharton


Today I tried to download financial information from Compustat North America using a list of unique GVKEY's. The GVKEY's were the identication codes for a number of commercial banks. When I tried to download information I sometimes did not get any data. At first I thought it had to do with the underlying database(s) and I contacted the S&P helpdesk. When they told me that this was not a problem with the Compustat North America database(s) I contacted the WRDS helpdesk to find out if I made any mistake.


It turns out that I should have changed the search. When I first did the search I assumed I had to mark only the option for FS (which is Financial Services). It turns out that banks report financial data in more than one way or format. Some data can only be found if I also mark the option for INDL (which is Industrials). The FS and INDL options can be found in the Wharton search screen at step 2: "How would you like to search this dataset?" at the section "Screening variables":



Examples of items that can not be downloaded if you forget to marke INDL are NI -- Net Income (Loss), and CAPX -- Capital Expenditures. When you mark INDL and FS the download may contain duplicate records for each year. You will have to filter out the duplicates using the Autofilter function in Excel or a similar option in a different spreadsheet program.


Author information: LinkedIn


05:16 PM - 16 January 2011

Companies from specific countries in Compustat Global

Posted in Wharton


Compustat Global has several options to find all companies from a specific country. The easiest way to find all companies (both active and inactive) is using the filtering options at the top of the search screen at Step 1 and 2 (in Wharton).

Example: all active or inactive Dutch companies from the Compustat Global Fundamentals Annual database.


  • Step 0: Database.
    Go to Wharton and specifically Compustat Global Fundamentals Annual
  • Step 1: Date Range
    Change the it to include the maximum range: Jan 1987 to now
  • Step 2: Search variables:
    - Mark the option "Search the entire database"
    - Check both boxes at Industry Format to include Industrial as well as
     type companies
    - Mark both Active and Inactive options at "Company Status"
    - Below at the screening variables, select the Country Code NLD (= Netherlands).

  • Step 3: Download variables
    - Company Name
    - International Security ID
    - Sedol
  • Step 4: Output Format
    Choose either Comma-delimited text or Tab-delimited text

The result of the search should be all Dutch active (± 149) or inactive companies (±159). The search result will, of course, contain many duplicate identification codes as the search was done for all the years in the database. If you want to get a short list of unique company codes you can deduplicate them using Microsoft Access using the standard grouping option in a query that is available there (see an example of deduplicating codes here). Before importing the text-file in Access you can import everything into Excel as text but you can do the same thing also in Microsoft Access as well using the Import Wizard available there.

NB: You may wonder why it is necessary to download the companies from Compustat Global as a text file and also why you should use the text format in Excel or Access. The reason is that if you download the list directly into Excel, the software may alter/format the company identification codes. The same thing may happen with the text file if you do not import everytning as text. If you need the codes later on to do another search in this or another financial database the codes will not always retrieve company data if they have been formatted by Excel or Access.


Author information: LinkedIn


10:13 AM - 15 September 2010

ExecuComp database changes in 2006

Posted in Wharton


The Compustat database ExecuComp is a database specifically about executive compensation for companies that are part of the S&P 1500 index. The start date for the database is 1992. In 2005/2006 some major changes took place in the SEC Def 14A reports on executive compensation due to the implementation of FASB 158 in FAS123R.


On September 29, 2006, the Financial Accounting Standards Board (FASB) issued Statement No. 158, titled Employers’ Accounting for Defined Benefit Pension and Other Postretirement Plans. The objective of this statement is, in effect, to extend, improve, and consolidate the guidance offered on the reporting of the overall deficit for surplus pensions and Other Postretirement Employee Benefits (“OPEB”) on the financial statements (balance sheets).


The new accounting standard FAS123R requires companies to value their employee stock options. Previously, companies had a choice between expensing the options or disclosing the valuation. Only a  few companies chose the expense method. FAS No. 123R, “Share Based Payments” which amends FAS No. 123 “Accounting for Stock-Based Compensation” is effective for fiscal years beginning after June 15, 2005, and requires the expensing of stock options. Most companies adopted FASB 158 between 2006 and 2007.


What does this mean for ExecuComp?

The following (part) databases contain data items that are collected beginning with adoption of the new FASB 158 standard:

  • Annual Compensation
  • Plan Based Awards
  • Outstanding Equity Awards
  • Deferred Compensation
  • Pension Benefits
  • Director Compensation

The following (part) databases contain data from the old format ONLY:

  • Company Financial and Director Compensation for 2005 and prior
  • Long Term Incentive Awards - 1992 Format
  • Stock Option Grants - 1992 Format

In the Online Compustat Manual the data item definitions for the ExecuComp databases will note which items are no longer being reported. When you look at the variable descriptions for the part-databases some of them will have the variable "Old_Datafmt_Flag". When this item is "True" in a download it means that the data for these company executives was only available for the 1992 reporting regulations.

Historical information will still be available for these companies/executives, but data will no longer be reported by the companies after SFAS 158 is adopted and, therefore, will not be collected.


Author information: LinkedIn


11:51 AM - 9 September 2010

Compustat Global financial data & currencies

Posted in Wharton


The following variables/codes  identify the currency in which the company's financial data is available in the Compustat Global databases. When this item is not equal to CURNCD, Compustat has translated the data from CURNCD to CURCD (or CURCDQ) using the translation rate found in the variable: CURRTR.


Mnemonic: CURCD
Periodicity: Annual
Format: Code


Mnemonic: CURCDQ
Periodicity: Quarterly
Format: Code


The historic exchange rates can be found by obtaining the variable exratd and applying it with the appropriate ‘tocurd’ and ‘fromcurd’ variables. The data manual for Compustat Global (Understanding The Data) has much more information in a specific chapter called: Chapter 8: Currency. This section of the manual says a lot about recalculating using exchange rates and gives variable options depending on the section of the Financial statements.


Author information: LinkedIn


10:52 PM - 18 June 2010

Last Page
Next Page
This weblog will deal with all kinds of tips and tricks on the use of several financial databases.

User Profile
--- Amadeus
--- Company.Info
--- Datastream
--- GMID
--- General
--- International Statistics
--- LexisNexis
--- SDC Platinum
--- S&P Indexes
--- Wharton
--- Zephyr
Recent Entries
- Announcement: New Blog
- International Human Development Indicators
- Global Reporting Initiative
- WorldScope company coverage update
- World Bank Commodity Prices - update November 2011

External Links:
--- NYSE Euronext
--- Finabase Blog
--- VU Law Blogspot