Posts Tagged ‘IBES’

Getting a linking table from Compustat via WRDS Cloud using SSH and SAS

27 June 2017 Leave a comment

Researchers commonly need to combine data from multiple sources. Even when the sources are offered through the same platform such as Wharton Research Data Service (WRDS), this can still be a difficult process with many choices. Essentially, you need to find a common entity identifier in both sources; if none exists, find a reference table or other third source.

The help provided by WRDS is often sparse in detail and tailored for their more advanced users. I have previously written in detail about using WRDS Cloud and SAS to gain access to resources not available on the WRDS website, in the blog post Getting S&P 500 constituents from CRSP via WRDS Cloud using SSH and SAS.

In this post, I will suggest a way to link I/B/E/S data with Compustat North America data. I will summarise the points that are addressed in the aforementioned blog post.

Get a linking table from Compustat North America

You will need the following to begin.

  1. AUTHENTICATION. Your WRDS username and password, specifically an individual PGR or Academic login.
  2. SENDING COMMANDS. Windows users: the Putty program. Mac/Linux users: the Terminal app or command line.
  3. FILE TRANSFER. Windows users: WinSCP or FileZilla. Mac/Linux users: CyberDuck or FileZilla or command line.
  4. FILE EDITING: Notepad, Notepad++ or another text editor. Excel or other spreadsheet application.

The method is very similar to getting the S&P 500 constituents. Essentially, Compustat has a dataset called securities which lists all the identifiers for all the securities. It is in SAS format. It cannot be accessed directly by us, but we can write a SAS script to read its entire contents to a plain text file, then copy the file to our own PCs. This file is kept at location /wrds/comp/sasdata/d_na/security/security.sas7bdat for North American data and is updated often.

Your script to read this file should look like this:

options nosource nodate nocenter nonumber ps=max ls = max;
title ' ';
options source;
proc print noobs width=UNIFORM;

This is different from last time in a few ways.

  1. The ‘ls’ or ‘line size’ option has been changed from ’72’ to ‘max’. This is because there are more columns for Compustat securities than for S&P 500 constituents.
  2. The data table we are reading from is ‘’.
  3. The ‘labels’ option has been removed so we get the clean variable names as the header.
  4. The ‘width’ option is set to ‘UNIFORM’ so the columns are the same number of characters wide on each ‘page’.

The method is as follows; remember to refer to the aforementioned blog post.

  1. Save the SAS script to a new text file on your computer, or similar.
  2. Use WinSCP or equivalent to copy this file to your own space on WRDS Cloud.
  3. Use Putty or equivalent to run the script, log in and type qsas and press Enter.
  4. Wait 10 seconds and use WinSCP to download the generated files compsecurity.log and compsecurity.lst to your computer. The ‘lst’ file contains the data, if the program was successful (read the ‘log’ file to check).
  5. Use Notepad++ or equivalent to remove the blank lines from the top of compsecurity.lst and save it as compsecurity.txt – then check every 32,764 lines for a few more blank lines and repeat of the headers to be removed (these are ‘page breaks’).
  6. In Excel, open compsecurity.txt (via Data > Get External Data from Text) and use fixed column width option, carefully setting the locations for the column breaks, remembering to set most columns as Text format instead of General (particularly GVKEY and CUSIP). Save as CSV or Excel format.

Compustat securities via WRDS Cloud

Using the linking table

You will need the columns GVKEY and IBTIC (IBES Ticker).

  • If your Compustat and I/B/E/S data are in Stata format, import this table and consider joining the tables.
  • If your Compustat and I/B/E/S data are in Excel format, use VLOOKUP formulas (some more processing may be required).

Still to do: there is not necessarily a one-to-one match between these two identifiers. The same GVKEY will appear on more than one row, but will those rows contain more than one unique ticker? And is the data frequency the same in each source? You will need to find the answers to these questions out for yourself.

Number of Analysts from IBES

The premier research database for historical numerical analyst forecast data is IBES (or I/B/E/S ) from Thomson Reuters.

If you want the number of analysts who are covering a company the best estimate is the number of analysts who provide an earnings per share estimate (EPS) for the next (to be announced) financial year (FY1). This is the variable estimated by most analysts.

At the University of Manchester we have access to IBES through WRDS. For the number of analysts we can use the IBES summary data rather than the detailed data at the individual analyst level.

IBES number of analysts estimating EPS FY1 (click to expand)

IBES number of analysts estimating EPS FY1 (click to expand)

Selecting IBES – Summary History – Summary Statistics

Select dates and the company codes

Measures – EPS (Earnings Per Share)

Forecast Period Indicator – Fiscal Year 1

Identifying Information – <as required>

Other Variables – Number of Estimates

The results will be similar to these.

INTEL CP (ticker INTC, cusip 45814010) in January 2010 had 43 analysts giving an estimate for EPS of next forecast period (fpi 1). The next forecast period was the company fiscal year ending 31 December 2010 (fpedats 20101231) and the number of analysts was calculated on 14 January 20101  (statpers 20100114).

Note that the number of analysts (numest) varies monthly. IBES considers that an analysts estimate is only valid for a certain period of time. If analysts do not update or confirm their estimate within this period then it is removed from the number of estimates.

The IBES summary data is also available through Datastream.

The Datastream variable EPS1NE – Earnings Per Share Total Number of Estimates in the Mean FY1 – is the same variable that was obtained using the WRDS IBES Summary Statistics query described above.

Datastream makes it easy to retrieve EPS1NET – EPS Total Number of Estimates (including those excluded from the Mean) FY1 – in January 2010 this was 44, rather than 43 for EPS1NE.

This expands on the July 2010 post No of Analysts covering a company

Categories: company information Tags: , ,

Thomson One Banker Excel Add-in and IBES

21 February 2011 Leave a comment

We recently got access to IBES (I/B/E/S – Insitutional Brokers’ Estimate System) from Thomson Reuters on WRDS. This is excellent for researchers who want to access large quantities of brokers’ estimate data, for example when dealing with the detailed estimates collected on an individual broker basis.
Thomson Reuters
Summary (or Consensus) IBES data is also available on Thomson One Banker and Datastream. This consensus data is gathered monthly so gives a record of how brokers’ expected/expect a company to perform in terms of several variables. (Earnings per Share (EPS) is the most forecast of these.)

Thomson One Banker Excel Add-in IBES example is a spreadsheet that uses the Thomson Reuters PFDL functions to retrieve IBES data. (You need the TOB Excel add-in installed to use this.)

No of Analysts covering a company

13 July 2010 1 comment

How many analysts provide estimates for a company? It sounds like a simple question but there are some complications (see below). However, we can relatively easily find:

Number of analysts providing EPS (Earnings per Share) estimates for the next financial year (FY1).

EPS is the most estimated financial variable and most analysts covering a company will estimate it for the next financial year.

This variable is part of the IBES summary data available through Thomson One Banker or Datastream:

  • ThomsonOneBanker – data item  IBH.EPSNbrEstFY1
  • Datastream – datatype  F1NE  (or F1NET  if available)

This data is available monthly (not all analysts covering a company will estimate every month so it can vary over the year).

See also Number of Analysts from IBES (posted June 2014) that gives details of how to get this information from IBES on WRDS.

Why knowing exactly how many analysts cover a company is hard.

First, there is no requirement for brokers and analysts to record what companies they are following. The ThomsonReuters IBES database, which is the most used analysts estimates database for research, is gathered by asking brokers/analysts to be involved and provide their estimates on a monthly basis.

Second, analysts coverage of a company can vary in detail. They provide estimates for a number of key financial variables but the choice of variables will vary between analysts.

Using the IBES detailed database, it would be possible to write a program to count the number of distinct analysts who provided an estimate for any financial variable for a company within a chosen time period. However, if within a broker one analyst estimates EPS, and another analyst estimates Sales, is that 2 analysts covering the company or just one?

EPS announcement date

Research on analysts’ estimates often involves comparing the estimates with the actual reported values, and knowing the date that the actual value was reported can be important. For example, the Glaxosmithkline EPS for the year to Dec 2009, was reported in 4 Feb 2010.

While IBES on Datastream gives access to the consensus forecasts for EPS and other variables, it does not include this actual report date.

The actual report date is also available from:

  • ThomsonOneBanker – database IBES, variable EPSActualReportDate
  • IBES – Detailed Actuals File, Announcement Date
  • FactSet ExcelConnect – [to be confirmed]
  • Compustat (for North American companies) – RDQ, Report Date of Quarterly Earnings (in Fundamentals Quarterly)