Transcript Using CRSP-Compustat Merged Database with ts_print and cst

Using the
CRSP/Compustat® Merged Database
With ts_print and cst_print
CCM: CRSP/Compustat Merged Database
The CCM database is the link and link-history between CRSP and
Compustat data. It is packaged with Standard & Poor’s® Compustat
data, reformatted into a CRSPAccess® database.
The CRSP Link provides a peerless matching of CRSP market
data with Compustat fundamental data by associating identifiers
that are unique to each database: CRSP’s PERMNO® and
Compustat’s GVKEY®.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
CCM: CRSP/Compustat Merged Database
Use of the CCM database:
Requires a current subscription to Compustat data through
Standard & Poor’s
Is used in conjunction with a CRSP stock or stock & indices
database
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
CCM: CRSP/Compustat Merged Database
CCM data may be accessed in a variety of ways:
ts_print
cst_print
SASECRSP Engine (SAS versions 9.1.3 and higher)
Fortran-95* and C programs
* Fortran support currently on Windows and Linux operating systems only
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE
Let’s first do a quick review of the ts_print interface…
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE
The ts_print interface contains four screens:
Entities
Data Items
Date
Report Format
and Options
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE
Each ts_print tab is divided into the following sections:
Screen options
Screen-specific function buttons
Display options added for each tab
File-specific function buttons
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Entities Tab
The Entities Tab is used to select the security, index or portfolio for which you wish to extract data. CCM
data may be accessed on all keys that CRSP data is keyed plus the additional option of GVKEY
Individual securities or indices
An input file of securities or indices
An input portfolio file
A portfolio using all
available securities
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Data Items Tab
Select items from the Data Item Tab. Use the Find and Add Item buttons to select and add items one
at a time. In addition to daily and monthly CRSP data items, Compustat items may be selected from
a drop down menu.
Select individual or groups of items.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Data Items Tab
Compustat items appear in one of two ways. First, those items from the drop-down menu with
arrows may be selected directly from the menu.
Directly select items from the
“link” menu
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Data Items Tab
Second, when industrial or bank file options are selected, a pop-up box appears for item searching.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Date Tab
While Compustat data are comprised of quarterly and annual items, reporting options
are daily, weekly, monthly, quarterly or annual frequency, with either fixed date ranges
or a date range relative to an event date, if entered, under the Entities tab.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Report Format and Options Tab
Design the layout of your report with the Report Format and
Options Tab.
Assign Entities, Data Items and Date options to
axes. You can choose to include headers, and add
field and row delimiters. Output File Name is a
required field.
Add report format specifications using the Add
Options (F1) button.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Find Entities
Example 1:
How to Extract CRSP Prices, Compustat Earnings
and Sales for a Security for a Date Range
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Find Entities
Prices, Volumes & Returns Entity Selection
In this example, we extract Prices, Earnings,
and Sales for Microsoft, between 1995 and
2005, reported on an annual basis.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Find Entities
We start by finding the PERMNO for Microsoft. Under the Entities Tab,
click on the Find button.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Find Entities
…and search the Company Name, PERMNO, Ticker Symbols, or CUSIP
by clicking on the Locate button.
Select desired
search criteria.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Find Entities
Search results return to the Locate PERMNO window. Double click on the row that
contains the issue or index you are interested in to return to the Entities window.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Accessing CCM using the ts_print INTERFACE - Find Entities
Back in the Entities window, see that the PERMNO for Microsoft has populated the
text box next to the Find button. Click on the Add Entity (F1) button to enter.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Prices, Earnings & Sales - Data Item Selection
Click on the Data Items tab. Begin adding items by clicking on Find to bring up
the item list tree. Select Monthly, Prices, End of Period.
Click on Add Item (F1).
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Prices, Earnings & Sales - Data Item Selection
Select Compustat, Industrial Annual, locate “earnings” and double-click on
Earnings Per Share (Basic), then click the Add Item (F1) button.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Prices, Earnings & Sales - Data Item Selection
Select Compustat, Industrial Annual, locate “sales” and double click on
Sales (Net), then click the Add item (F1) button.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Prices, Earnings & Sales - Data Item Selection
You have selected Prices, Earnings, and Sales for IBM.
Click on the Date tab.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Prices, Earnings & Sales - Date Selection
In the Date Tab, select an Annual Calendar Name, a MMDDYYY Calendar
Format and enter 1995-2005 as the Fixed Date Range [1]. Click the Add Date 3
Specification (F1) button [2]. Click on the Report Format & Options tab [3].
3
1
2
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Prices, Earnings & Sales - Report Format
Use the default axis assignments, with headers [1].
Save the output file as C:\crsp\work\pes.out [2]. Click the Add Options (F1) button [3].
1
2
3
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Prices, Earnings & Sales - Report
Once all four tabs are completed, the request file can be saved and processed.
If you wish to save the request file,
Click on the SAVE FILE (F5) button at
the bottom left of the window. Save the
file to c:\crsp\work\filename.rqt.
Click on the PROCESS FILE (F7) button.
Select PROCESS SCREEN DATA.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Prices, Earnings & Sales - Report
When Processing is complete, click on the OK button.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Prices, Earnings & Sales - Report
To view your output file, click on VIEW FILE (F9) at the bottom right of the screen.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
Prices, Earnings & Sales - Report Output
The results are saved as a text file in C:\crsp\work\pes.txt.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
cst_print
cstprint is a command line program used to facilitate analysis of event and
time series data sets using Compustat data.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
BEFORE WE CAN GET STARTED
cstprint runs in a DOS window. For ease of use, we recommend that the screen buffer
be set to 5000 lines. To check this setting and change it, if necessary, click on the
upper left corner of the DOS prompt window. Select Properties Layout Screen
Buffer Size Height. Change to 5000, and click OK to close the window.
Click here
for menu
options,
then select
Properties.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
BEFORE WE CAN GET STARTED
To use cstprint, you must enter a key identifier. The default identifier to access an
entity in cstprint is Compustat’s GVKEY. Other identifiers are:
PERMNO® – CRSP’s permanent and unique security identifier that follows an entity
through name and CUSIP changes, mergers & acquisitions
PERMCO® – CRSP’s permanent and unique company identifier that follows an
entity through name changes
CUSIP
TICKER
SIC CODE
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
BEFORE WE CAN GET STARTED
We may look up an identifier in two ways:
cstsearch – a namelist search utility that will return header
information for a security
“Find” option in the ts_print interface – search on what is known
to find unknown identifiers. Example: search on company name to
return PERMNO, Ticker or CUSIP
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
FIND A GVKEY and PERMNO USING cstsearch
At a DOS prompt, type and enter:
cstsearch “microsoft”
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
FIND A GVKEY and PERMNO USING cstsearch
The results are as follows:
The GVKEY for Microsoft is 12141, the PERMNO is 10107.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
ACCESSING cstprint
You can enter criteria for cstprint in two ways from the
CRSPAccess Menu:
CRSP Command Prompt will open a DOS window and set the path
for CRSP utilities to run from the command line.
CRSPAccess Menu option “cstprint” for all Compustat data
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
ACCESSING cstprint
From the CRSP Command Prompt:
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
cstprint - EXAMPLE:
Accessing Sales, Net Income, and Shares Data
Through example, we will explore various access commands and
options.
Using IBM as our sample entity, we will access the following data:
 current identifying information
 link history
 sales, net income, shares outstanding
 pde data: Book Value per Share and 12-month EPS from
Operations
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
cstprint - EXAMPLE:
Commands for this example:
/h –
/l –
Header data, current identifying information on an issue
Link histories
/ia12 – Net Sales
/ia172 – Net Income
/ia25 – Shares Outstanding
PDE data:
/eb – Book Value per Share
/ep – 12-Month moving EPS from Operations
/dt YYYYMMDD-YYYYMMDD – Select the Date Range. (default is full history)
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
cstprint - EXAMPLE:
Any time you need help in referencing a command in cstprint, simply type “?” at the
prompt to return quick reference pages.
Typing “?”
returns quick
references to
commands
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: /h - Header Data from the CRSP Command Prompt
1.
2.
3.
4.
At C:\>, type cstprint.
Type /h at the option.
Keep previous data options? (y/n) Select Y to keep previous options or N to
browse the newly specified data. Select N.
Enter GVKEY 6066 as the identifier.
1.
2.
3.
4.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: Header Output
Following is a snapshot of IBM’s Compustat header data.
GVKEY DNUM CNUM CIC IPERM ICOMP SYMBOL
6066 7370 459200 101 12490 20990 IBM
NAME
INTL BUSINESS MACHINES CORP
AVAILABLE DATA
ANNUAL
QUARTERLY S&P_REL STK_OWN DUP INDUSTRY NAME
1950-2005 1962.1-2006.1 903
0
0 CMP PROGRAMMING,DATA PROCESS
STATE COUNTY FOREIGN
FILE ZLIST CODE CODE CODE
11
1 36
119
SPDRC
0
EMPLOYER ID CPSPIN CSSPIN CSSPII IPO-Date
13-0871985 1
10
1
0
SPCPRC/
/SDBT SDBTIM SUBDBT CPAPER NAICS
07
AVAILF GICS
CALENDAR RANGE
102 541519 3138671 45202010 19500101-20060428
--FUNDF--
CANDXC ISO COUNTRY
1 2 3 STINC /CCNDX /INCORP
0 0 0
36
0
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: /l – CRSP/Compustat Link Data
1. Type /l as the command.
2. Type N to not keep previous options.
3. Enter GVKEY 6066 or S to use the same identifier
you previously used.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: /l - Link Output
Link data for IBM – GVKEY 6066
LNKBEGDT LNKENDDT PERMNO PERMCO LINKTYPE LINKFLAG
19500101
99999999
12490
20990
LC
BBB
lnkbegdt – first date of the link (beginning of Compustat coverage is 1950)
lnkenddt – last date of the link (99999999 = no link enddate)
linktype – code providing detail usage of the link data available (LC = standard link
where company and price match)
linkflag – 3 character flag where first position = annual data cut, second = the last
monthly cut, and third = the last quarterly cut.
(B = PERMNO resides in both CRSP daily and monthly data sets.)
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: Time-series items from the Annual Industrial Files
To access time-series data, we need to use Compustat data item
numbers. These may be found in Compustat’s documentation and in
Appendices A and B in the CRSP/Compustat Merged Database
Guide.
Compustat Annual Item
Sales (Net)
Net Income (Loss)
Common Shares Outstanding
Item Number
12
172
25
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: Time-series items from the Annual Industrial Files
To browse the income statement items:
1. At the command prompt, type, /ia12,172, 25
2. For this example, select Y to return Link data along with
Returns.
3. Enter GVKEY 6066 or S to use the same identifier you
used previously.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: Time-series items from the Annual Industrial Files
To access data on a key other than GVKEY, use the /ky option,
followed by the identifier you would like to use. For example, to
access on PERMNO key:
1. Type /ky permno /ia12,172, 25 at the command.
2. Type N to not keep previous options.
3. Enter PERMNO 12490
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: Time-series Output
Either of the two previous screens will return the same output:
Data Fiscal
Year Yearend
1950
1951
1952
1953
…
2002
2003
2004
2005
Item 12
SalesNet
Item 172 Item 25
NI
ComShrOut
12
12
12
12
214.9000
266.8000
333.7000
410.0000
33.3000
27.8900
29.8700
34.1200
2.7640
2.9020
3.0470
3.1990
12
12
12
12
81186.0000
89131.0000
96293.0000
91134.0000
3579.0000
7583.0000
8430.0000
7934.0000
1722.3669
1694.5090
1645.5920
1573.9800
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: Time-series Output with Date-Range Restriction
The default date range of output for cstprint is the full time-series – beginning
1950 to present. As with stkprint, the /dt option may be used to define a date
range.
/dt 1995-2005 will return:
Data Fiscal
Year Yearend
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
12
12
12
12
12
12
12
12
12
12
12
Item 12 Item 172
Item 25
SalesNet NI
ComShrOut
71940.0000
75947.0000
78508.0000
81667.0000
87548.0000
88396.0000
85866.0000
81186.0000
89131.0000
96293.0000
91134.0000
4178.0000
5429.0000
6093.0000
6328.0000
7712.0000
8093.0000
7723.0000
3579.0000
7583.0000
8430.0000
7934.0000
547.7740
507.9810
958.0910
915.9070
1784.2159
1742.9000
1723.1940
1722.3669
1694.5090
1645.5920
1573.9800
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: Accessing PDE Data
Compustat PDE contains Prices, Dividends, and
Earnings – related data.
In this example, we will access:
/ep - 12 Month moving Earnings per Share
/eb – Book Value per Share
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: Accessing PDE Data
At the command prompt:
1. Type /eb /ep /dt200504-200604* at the
command. (*this will select the date range between
April 2005 and 2006).
2. Type N to not keep previous options.
3. Enter GVKEY 6066 or S to use the
same identifier you previously used.
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
EXAMPLE: Accessing PDE Data
PDE data, output by month, results:
MONTH
200504
200505
200506
200507
200508
200509
200510
200511
200512
200601
200602
200603
200604
BKV
18.0768
18.0768
18.0768
18.0768
18.0768
18.0768
18.0768
18.0768
18.0768
21.0282
21.0282
21.0282
21.0282
OEPS12
4.6900
4.6900
4.8000
4.8000
4.8000
5.0400
5.0400
5.0400
5.3800
5.3800
5.3800
5.6300
5.6300
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
ts_print and cstprint for the CCM Database: Summary
In this presentation, you have learned how cstprint and the ts_print
interface are used for accessing Compustat data.
You have developed the following skills:
 How to navigate ts_print and cstprint for Compustat data
 How to access various types of Compustat data items
 How to create and view your data requests
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
ts_print and cstprint for the CCM Database: Summary
We examined:
 Header and link data
 Financial data from the Industrial files
 Price, Dividend, and Earnings Files
Working through examples, we employed options to:
 Access entities through different keys – GVKEY and PERMNO
 Specified date ranges
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H
ts_print and cstprint for the CCM Database : Summary
Congratulations!
You have successfully completed the CRSP Utilities for Compustat presentation!
Please refer to our tutorials and Utility Guide or contact Client Services for
additional information on accessing CRSP data.
Tutorials:
www.crsp.chicagogsb.edu/support/documentation/database_guides
Utility Manual: www.crsp.chicagogsb.edu/support/documentation/tutorials
Client Services : [email protected]
Subscriptions: Subscriptions@crsp. ChicagoGSB.edu
Phone: 312.263.6400
C LEAN , Q UALI TY DATA F O R E X C E LLE N C E I N R E S EAR C H