Financial Data on WRDS Platform
undefined
Financial Data on WRDS
Intro to COMPUSTAT, CRSP and WRDS Analytics
January 31
st
, 2024
Presenter: Eunji Oh, Ph.D., Research Support Director, WRDS
Agenda
2
Overview of WRDS
What is WRDS?
•
Financial Database / Analytics platform
•
Data vendor products
•
WRDS own products
•
Research hub
•
WRDS Cloud, PC-SAS, SAS Studio,
Jupyter lab
•
Sample programs
•
Knowledge base
•
Supporting over 75,000 researchers at
500+ institutions in 35+ countries
Wharton Research Data Services
4
Evolution of WRDS
•
Data Aggregator (1993 - present)
•
Access to Cloud Servers with Research Data and Macros
•
Economy of Scale in Data distribution
•
Full-time Technical support
•
Online help, email support, and 24/7 network monitoring
•
Research Platform (2001– present)
•
Knowledge Base: Data Overviews, Research Applications, and SAS macros
•
Full-time Research support
•
Research Team: 8 Ph.D.s in Economics and Finance
•
Research Analytics (since 2013)
5
WRDS Data Vendors
6
WRDS advantages over financial portal
•
Batch download of data:
•
Ex: One can download CRSP’s historical stock data from 1926 – current
•
Optimal structure for research
•
WRDS review original data from vendor and restructure it so that it can be ready for research
•
Academic research analytics (ex – linking suite, financial ratio, SEC analytics, etc.)
•
WRDS provides research analytics and research program that can help academic research
•
One stop shop for academic research
•
User can access WRDS data and run programs on WRDS cloud
•
User can link their Dropbox to WRDS and easily use outside data on WRDS cloud or share research output with
co-authors
7
Agenda
8
Accessing Methods on WRDS
Multiple Access Options
10
10
•
W
e
b
Q
u
e
r
i
e
s
:
F
o
r
D
o
w
n
l
o
a
d
i
n
g
•
U
n
i
x
W
R
D
S
C
l
o
u
d
:
For efficient querying and
storage access
•
P
C
S
A
S
,
R
,
P
y
t
h
o
n
,
S
T
A
T
A
,
e
t
c
.
:
For programming language
users
•
W
e
b
-
b
a
s
e
d
A
p
p
l
i
c
a
t
i
o
n
s
:
SAS Studio / Jupyter Hub
Agenda
11
11
Financial Data on WRDS
Sources of Financial Data (see full data list in the
appendix
)
•
Fundamentals/Filings
•
Yearly/Quarterly Snapshots of a Firm (Unit: Firm)
•
Assets/Trading
•
Daily/Monthly Trading Price/Volume of a Security(Equity, Bond, Derivatives) (Unit: Issue)
•
Industry Specific
•
(Ex) Bank specific database (Unit: Firm/Industry)
•
Events/Transactions
•
M&A, Security Issuance(IPO, SEO), News Datasets (Unit: Firm)
•
Institutional Investors (Hedge Funds and Mutual Funds)
•
Characteristics, Ownership, Return of Funds(Mutual/Hedge) (Unit: Fund)
•
Firm Behavior/Individuals
•
Corporate Governance, Social Responsibility (Unit: Firm)
•
Analysts, Human Networks (Unit: Individual)
13
13
Browse Data by Concept
https://wrds-www.wharton.upenn.edu/pages/get-data/
14
14
Global Data on WRDS
•
Financial Fundamentals:
•
Public companies: Compustat Global, Worldscope, Factset Fundamental
•
Private companies: Bvd Orbis, Bvd Amadeus
•
Stock prices: Compustat Global, Datastream, and more
•
Ownership: Thomson Global Ownership
•
M&A: Thomson SDC
•
Others: Wind, Toyo Keizai, ESG databases, and more
15
15
WRDS Video Resources
•
Remote Learning
•
Data
•
Analytics
•
Research
•
Technology
https://wrds-www.wharton.upenn.edu/pages/video-support/
16
16
Agenda
17
17
COMPUSTAT / CRSP
Accounting and Financial Data in COMPUSTAT
•
C
O
M
P
U
S
T
A
T
N
o
r
t
h
-
A
m
e
r
i
c
a
:
U
.
S
.
a
n
d
C
a
n
a
d
i
a
n
f
u
n
d
a
m
e
n
t
a
l
a
n
d
m
a
r
k
e
t
i
n
f
o
r
m
a
t
i
o
n
o
n
3
5
,
0
0
0
+
a
c
t
i
v
e
a
n
d
i
n
a
c
t
i
v
e
p
u
b
l
i
c
c
o
m
p
a
n
i
e
s
,
f
r
o
m
1
9
5
0
-
p
r
e
s
e
n
t
•
C
O
M
P
U
S
T
A
T
G
l
o
b
a
l
:
F
u
n
d
a
m
e
n
t
a
l
d
a
t
a
o
f
3
9
,
0
0
0
+
a
c
t
i
v
e
a
n
d
i
n
a
c
t
i
v
e
c
o
m
p
a
n
i
e
s
i
n
8
0
+
c
o
u
n
t
r
i
e
s
,
t
h
e
e
a
r
l
i
e
s
t
d
a
t
a
i
s
f
r
o
m
1
9
8
6
•
F
u
n
d
a
m
e
n
t
a
l
d
a
t
a
a
v
a
i
l
a
b
l
e
o
n
a
n
a
n
n
u
a
l
a
n
d
q
u
a
r
t
e
r
l
y
f
r
e
q
u
e
n
c
i
e
s
w
i
t
h
t
h
o
u
s
a
n
d
s
o
f
I
n
c
o
m
e
S
t
a
t
e
m
e
n
t
,
B
a
l
a
n
c
e
S
h
e
e
t
,
S
t
a
t
e
m
e
n
t
o
f
C
a
s
h
F
l
o
w
s
,
a
n
d
s
u
p
p
l
e
m
e
n
t
a
l
&
i
n
d
u
s
t
r
y
-
s
p
e
c
i
f
i
c
d
a
t
a
i
t
e
m
s
•
M
a
r
k
e
t
d
a
t
a
a
v
a
i
l
a
b
l
e
o
n
a
m
o
n
t
h
l
y
a
n
d
d
a
i
l
y
f
r
e
q
u
e
n
c
i
e
s
w
i
t
h
P
r
i
c
e
s
,
D
i
v
i
d
e
n
d
s
,
R
e
t
u
r
n
s
,
T
r
a
d
i
n
g
V
o
l
u
m
e
,
S
h
a
r
e
s
O
u
t
s
t
a
n
d
i
n
g
a
n
d
S
h
o
r
t
-
I
n
t
e
r
e
s
t
I
n
f
o
r
m
a
t
i
o
n
(
V
e
r
y
S
i
m
i
l
a
r
t
o
C
R
S
P
i
n
m
a
n
y
d
i
m
e
n
s
i
o
n
s
)
Both Fundamental Data and Market Data are now in the same data feed and are
updated daily
19
19
COMPUSTAT Coverage
20
20
COMPUSTAT Example
•
Get Current Assets, Assets for IBM, Microsoft, and Apple between Jan 2019 and Dec 2021
21
21
COMPUSTAT Examples
•
Finding Financial Statement Variables for IBM
•
Finding Financial Statement Variables for unknown ID
•
Finding ~ for more than one company with known IDs
•
Add web query output filters
•
Choose the output format you need
22
22
WRDS Support for COMPUSTAT
•
Compustat manual
•
WRDS Documents
•
Sample Programs (extracts, point-in-time, etc.)
Research → Sample Programs → Compustat
•
Research Applications (book-to-market, P/E Ratio )
Research → Applications
Remote learning clip
part a
and
part b
23
23
Primary Identifiers
•
The Primary Identifiers
•
G
V
K
E
Y
a
t
f
i
r
m
-
l
e
v
e
l
•
G
V
K
E
Y
+
I
I
D
a
t
s
e
c
u
r
i
t
y
-
l
e
v
e
l
•
G
V
K
E
Y
i
s
a
u
n
i
q
u
e
s
i
x
-
d
i
g
i
t
i
d
e
n
t
i
f
i
e
r
a
n
d
p
r
i
m
a
r
y
k
e
y
f
o
r
e
a
c
h
c
o
m
p
a
n
y
a
n
d
a
s
s
i
g
n
e
d
b
y
C
O
M
P
U
S
T
A
T
:
P
e
r
m
a
n
e
n
t
,
a
n
d
U
n
i
q
u
e
(
N
o
t
R
e
c
y
c
l
a
b
l
e
)
.
•
I
I
D
i
s
a
s
s
i
g
n
e
d
f
o
r
e
a
c
h
s
e
c
u
r
i
t
i
e
s
•
Alphabet’s class A stocks: IID = 01
•
Alphabet’s class C stocks: IID = 03
24
24
Common Secondary Identifiers
•
S
t
o
c
k
T
i
c
k
e
r
s
:
E
x
c
h
a
n
g
e
S
p
e
c
i
f
i
c
,
w
i
t
h
S
u
f
f
i
x
e
s
,
N
O
T
P
e
r
m
a
n
e
n
t
,
a
n
d
R
e
c
y
c
l
a
b
l
e
•
C
U
S
I
P
a
l
s
o
i
s
N
O
T
P
e
r
m
a
n
e
n
t
b
u
t
U
n
i
q
u
e
a
n
d
N
O
T
E
x
c
h
a
n
g
e
S
p
e
c
i
f
i
c
.
•
8- or 9-digit CUSIP is assigned for issues (e.g. equities and bonds).
•
6-digit CUSIP is assigned for issuers (e.g. firms)
•
C
I
K
:
U
S
f
i
l
i
n
g
n
u
m
b
e
r
a
t
t
h
e
S
E
C
,
a
n
d
C
h
a
n
g
e
a
b
l
e
•
C
o
m
p
a
n
y
N
a
m
e
s
a
r
e
o
f
t
e
n
a
b
b
r
e
v
i
a
t
e
d
/
m
a
n
i
p
u
l
a
t
e
d
b
y
d
a
t
a
v
e
n
d
o
r
s
•
Very difficult to have a reliable merging code by using company names
25
25
Identifiers example
26
26
Alphabet & Meta (
source table: Compustat’s comp.SECURITY
)
Alphabet & Meta (
source table: Compustat’s comp.NAMES
)
C
o
m
p
u
s
t
a
t
’
s
T
I
C
K
E
R
s
,
C
U
S
I
P
s
,
C
I
K
s
a
r
e
u
p
d
a
t
e
d
t
o
t
h
e
m
o
s
t
c
u
r
r
e
n
t
i
n
f
o
r
m
a
t
i
o
n
(
h
e
a
d
e
r
)
Source: CRSP Historical Names file
Compustat Data – Basic screening
Screening with filing type, data source, and data
type
•
Consolidation Level:
•
Consolidated (
C
) / Non-Consolidated (
N
) / etc.
•
Industry Format:
•
Industrial (
INDL
) / Financial (
FS
)
•
Data Format:
•
Standard (
STD
) / Summary (
SUMM_STD
) / etc.
•
Population Source:
•
Domestic (
D
) / International (
I
)
•
Company Status:
•
Active (
A
) / Inactive (
I
)
•
Data Items / Footnotes / Data Codes
27
27
Compustat Fundamental Data Sample
28
28
Stock Market Data in CRSP
•
Center for Research in Security Prices (CRSP) is a research center at the Booth
School of Business of the University of Chicago
•
Comprehensive collection of daily and monthly security records for the
NYSE/AMEX/NASDAQ/ARCA trading activities
•
Daily and Monthly data for roughly 28K + securities of Domestic companies and
ADRs traded on major exchanges from 1925–present
•
Complete historical information :
•
Accurate records of special distributions and stock splits in return calculation
•
Keep delisting companies (pre-M&A or bankruptcies), and delisting returns
29
29
Primary Identifiers
•
Primary Identifiers
•
P
E
R
M
N
O
a
t
s
e
c
u
r
i
t
y
l
e
v
e
l
•
P
E
R
M
C
O
a
t
f
i
r
m
l
e
v
e
l
•
P
E
R
M
N
O
i
s
a
u
n
i
q
u
e
p
e
r
m
a
n
e
n
t
i
d
e
n
t
i
f
i
e
r
a
n
d
p
r
i
m
a
r
y
k
e
y
a
s
s
i
g
n
e
d
b
y
C
R
S
P
t
o
e
a
c
h
s
e
c
u
r
i
t
y
:
P
e
r
m
a
n
e
n
t
,
a
n
d
U
n
i
q
u
e
(
N
o
t
R
e
c
y
c
l
a
b
l
e
)
.
•
P
E
R
M
C
O
i
s
a
u
n
i
q
u
e
i
d
e
n
t
i
f
i
e
r
f
o
r
e
a
c
h
c
o
m
p
a
n
y
a
s
s
i
g
n
e
d
b
y
C
R
S
P
:
P
e
r
m
a
n
e
n
t
,
a
n
d
U
n
i
q
u
e
(
N
o
t
R
e
c
y
c
l
a
b
l
e
)
.
•
Date Identifiers
•
C
a
l
e
n
d
a
r
d
a
t
e
:
d
a
t
e
•
In monthly stock file, date is set to be the end of the month
30
30
Common Secondary Identifiers
•
S
t
o
c
k
T
i
c
k
e
r
s
:
E
x
c
h
a
n
g
e
S
p
e
c
i
f
i
c
,
w
i
t
h
S
u
f
f
i
x
e
s
,
N
O
T
P
e
r
m
a
n
e
n
t
,
a
n
d
R
e
c
y
c
l
a
b
l
e
•
CRSP keeps historical changes in Ticker symbols
•
C
U
S
I
P
a
l
s
o
i
s
N
O
T
P
e
r
m
a
n
e
n
t
b
u
t
U
n
i
q
u
e
a
n
d
N
O
T
E
x
c
h
a
n
g
e
S
p
e
c
i
f
i
c
.
•
8- or 9-digit CUSIP is assigned for issues (e.g. equities and bonds).
•
6-digit CUSIP is assigned for issuers (e.g. firms)
•
C
R
S
P
k
e
e
p
s
b
o
t
h
m
o
s
t
c
u
r
r
e
n
t
C
U
S
I
P
a
n
d
h
i
s
t
o
r
i
c
a
l
C
U
S
I
P
(
N
C
U
S
I
P
)
•
C
o
m
p
a
n
y
N
a
m
e
s
a
r
e
o
f
t
e
n
a
b
b
r
e
v
i
a
t
e
d
/
m
a
n
i
p
u
l
a
t
e
d
b
y
d
a
t
a
v
e
n
d
o
r
s
•
CRSP keeps historical changes in company names
•
Very difficult to have a reliable merging code by using company names
31
31
Identifiers Example
32
32
10 Different
CUSIPs
5 Different
Tickers
8 Different
Names
1 PERMNO
per stock
1 PERMCO
per
company
CRSP Example
•
Get monthly prices, return and volume information for Microsoft and Ford from 1925 to 2008
33
33
CRSP Examples
•
Stock returns
•
Stock headers
•
Dividends/share repurchases
•
Distribution
•
Adjustment factors
https://wrds-www.wharton.upenn.edu/pages/grid-items/crsp-basics/
34
34
WRDS Support for CRSP
•
Sample Programs
•
Data Extracts, CCM and merging by CUSIP
•
Calculate CAPM beta and excess returns
Research → Sample Programs → CRSP Sample Programs
•
Research Applications
•
Compound returns, Momentum and Governance Portfolios
•
Fama-French factors and B/M portfolios, Beta Estimation
Research → Applications
•
Remote learning
•
Useful Variables
,
Basics
,
Coverage
,
Database Structure
35
35
How to Link CRSP and COMPUSTAT
•
Major Difficulties to link CRSP and COMPUSTAT
•
Different Frequencies:
•
COMPUSTAT – Accounting items are on annual-basis or quarterly-basis
•
CRSP– Security trading data is Monthly or Daily
•
Different Universe of Companies
•
CRSP: Equities listed in NYSE, AMEX, NASDAQ, ARCA, and BATS
•
COMPUSTAT: 10K and 10Q Filers to the SEC
•
S
o
l
u
t
i
o
n
:
U
s
e
t
h
e
C
C
M
(
C
R
S
P
-
C
O
M
P
U
S
T
A
T
M
e
r
g
e
d
)
D
a
t
a
s
e
t
o
r
t
h
e
l
i
n
k
i
n
g
m
a
c
r
o
36
36
Agenda
37
37
Advanced Data Access on WRDS
•
What if a web query does not do all that you need it to do?
•
Filtering data on multiple conditions
•
Some data tables are only accessible on WRDS cloud
•
More ways to access finance data at WRDS
•
W
R
D
S
C
l
o
u
d
g
i
v
e
s
y
o
u
1
0
G
B
s
e
c
u
r
e
,
p
e
r
s
o
n
a
l
h
o
m
e
d
i
r
e
c
t
o
r
y
s
t
o
r
a
g
e
a
n
d
a
c
c
e
s
s
t
o
5
0
0
G
B
s
h
a
r
e
d
d
i
r
e
c
t
o
r
y
s
t
o
r
a
g
e
w
i
t
h
i
n
y
o
u
r
i
n
s
t
i
t
u
t
i
o
n
.
H
e
l
p
f
u
l
f
o
r
s
t
o
r
i
n
g
c
o
d
e
a
n
d
r
e
s
u
l
t
s
!
Customize your software (R, Python, SAS, etc) and connect to WRDS cloud server
Advanced Features – Access Data Remotely on WRDS Server
39
39
WRDS SAS Studio
https://wrds-cloud.wharton.upenn.edu/SASStudio/
A web-based SAS application running on WRDS cloud
WRDS SAS Studio support:
https://wrds-
www.wharton.upenn.edu/pages/support/programming-wrds/programming-sas/sas-
web-sasstudio/
40
40
WRDS Jupyter Hub
https://wrds-jupyter.wharton.upenn.edu/hub/
Jupyter Hub is a web-based notebook style Interactive Development Environment
Python3 and R are supported
41
41
WRDS RStudio
https://wrds-rstudio.wharton.upenn.edu/
A web-based R application running on WRDS cloud
WRDS RStudio support:
https://wrds-
www.wharton.upenn.edu/pages/support/programming-wrds/programming-r/r-from-
the-web/
42
42
PC-SAS / Connect – Concept
•
SAS software installed on your
Windows PC
•
Some local SAS dataset in your PC
•
Remote access WRDS data by
connecting to WRDS server
•
Use WRDS powerful Unix server
processing resources
•
Access Unix permanent (10GB) &
temp (4.5TB) disk spaces
43
43
•
Steps:
1.
Connect to WRDS server
%let wrds = wrds-cloud.wharton.upenn.edu
4016;
options comamid = TCP remote = WRDS;
signon username = _prompt_;
2.
Remote Submit
rsubmit;
{Program}endrsubmit;
3.
Sign off
signoff;
Support → Getting Started -> Accessing WRDS -> PC-SAS Connect
PC-SAS / Connect – An Example
Example: Find all companies
in 1997
①
with
sales >= 1 billion
②
,
total assets >= 5
billions
③
, and
with >= 30 years of publicly reported financial statements
④
.
44
44
/* PC-SAS/Connect Communication Block */
%let
wrds = wrds-cloud.wharton.upenn.edu 4016;
options
comamid
=TCP remote=WRDS;
signon
username=_prompt_;
/* Submit SAS code to WRDS Unix Server */
rsubmit ;
/* SAS CODE submitted to WRDS Sever */
proc
sql
;
create
table
demo(
where
= (fyear=
1997
and
sale>=
1000
and
at>=
5000
and
firm_age>=
30
))
as
select
fyear, conm, tic, gvkey, sale, at, (fyear - min(fyear))
as
Firm_Age
from
comp.funda
where
not
missing(at)
and
consol=
"C"
and
indfmt=
"INDL"
and
datafmt=
"STD"
and
popsrc=
"D"
group
by
gvkey;
quit
;
/* Remote SAS CODE submission Ends */
endrsubmit
;
/* Sign off from PC-SAS/Connect */
Signoff;
How to Access SAS Data in UNIX
1.
Install SFTP Client software in your
PC (freeware such as WIN SCP)
2.
Set SFTP Client to be connected to
the server:
wrds-
cloud.wharton.upenn.edu
with your
wrds (wharton) username
and
password
3.
Retrieve and Upload files in SAS and
other formats among your local
machine and remote UNIX server.
Support → Getting Started -> Accessing WRDS -
> SSH & UNIX
45
45
How to run SAS in UNIX
1.
Install SSH Secure Shell (free)
software in your PC
2.
Set SSH to be connected to the
server:
wrds-
cloud.wharton.upenn.edu
with your
wrds (wharton) username
and
password
3.
Create, Edit and Run your SAS
program using Unix commands.
46
46
Step-by-step Remote Access Setup
SAS
Python
R
STATA
UNIX(SFTP)
47
47
Agenda
48
48
WRDS Research Products
WRDS Research Products
50
50
W
R
D
S
R
e
s
e
a
r
c
h
:
h
t
t
p
s
:
/
/
w
r
d
s
-
w
w
w
.
w
h
a
r
t
o
n
.
u
p
e
n
n
.
e
d
u
/
p
a
g
e
s
/
s
u
p
p
o
r
t
/
w
r
d
s
-
r
e
s
e
a
r
c
h
/
Analytics Example: Event Study
51
51
•
A complimentary research
analytics with subscriptions to
CRSP
,
Compustat Global
, and
etc.
•
User-friendly web interface
•
Example: Event study for
Announcement of M&A (target
companies)
Link:
WRDS US Daily Event Study
Event Study - Example
Stock market reaction for M&A target companies around the M&A announcements
made in 2021
•
M&A data: SDC (via WRDS)
•
Stock data: CRSP
52
52
WRDS Financial Ratio Suite
•
Pre-calculated 65 + Financial Ratio including P/E,
B/M, ROA, etc.
•
Web query:
https://wrds-
www.wharton.upenn.edu/pages/get-data/financial-
ratios-suite-wrds/financial-ratios/financial-ratios-firm-
level-by-wrds-beta/
•
Source code (SAS):
https://wrds-
www.wharton.upenn.edu/pages/support/manuals-
and-overviews/wrds-financial-ratios/financial-ratios-
sas-code/
53
53
WRDS Linking Suite
•
WRDS-created linking tables across different
databases (
link
)
•
Complimentary with the database subscriptions
•
E
x
–
B
o
a
r
d
E
X
-
C
R
S
P
-
C
o
m
p
u
s
t
a
t
L
i
n
k
i
s
o
n
l
y
a
v
a
i
l
a
b
l
e
f
o
r
w
h
o
h
a
s
s
u
b
s
c
r
i
p
t
i
o
n
s
t
o
a
l
l
t
h
r
e
e
p
r
o
d
u
c
t
s
•
Worldscope Datastream Link
•
WRDS created a linking table between Worldscope and
Datastream so that users do international research more
conveniently
•
The latest addition:
WRDS People Link
•
Link people across various databases on WRDS including
Execucomp, BoardEx, Thompson/Refinitiv Insiders, CIQ
People Intelligence, and 2IQ Insiders.
54
54
Sample Research Program
•
WRDS Research produces
sample research programs,
research macros in SAS and
Python.
55
55
WRDS Support
WRDS Support
57
57
•
Submit a Zendesk ticket by clicking “contact”
•
Provide details in your question (descriptions, research paper references, screenshots,
error messages, etc.)
Research and Technical Support
•
Online Help
•
Database Manuals plus additional support documentation
Data Overviews
Research Applications
Sample Programs
Variable Search
Company Search
•
WRDS Knowledge Base
: FAQ archive of answers to common user questions
•
Email support at
wrds-support@wharton.upenn.edu
(Monday-Friday, 9a-5p EST)
Researchers and Technical Experts ready to assist with:
•
Data access, merging, and management
•
Technical problems
58
58
Thank You!
Explore the comprehensive financial data available on the WRDS platform through an insightful presentation covering topics such as accessing the data, overview of COMPUSTAT and CRSP, advanced access features, and WRDS research products and support. Learn about the evolution of WRDS, advantages over financial portals, and the various data vendors associated with the platform.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Financial Data on WRDS Intro to COMPUSTAT, CRSP and WRDS Analytics January 31st, 2024 Presenter: Eunji Oh, Ph.D., Research Support Director, WRDS
Agenda 1 2 What is WRDS? Accessing the Data 3 Financial Data on WRDS 4 COMPUSTAT / CRSP overview 5 Advanced Access Features 6 WRDS Research Products & Support 2
What is WRDS? Financial Database / Analytics platform Data vendor products WRDS own products Research hub WRDS Cloud, PC-SAS, SAS Studio, Jupyter lab Sample programs Knowledge base Supporting over 75,000 researchers at 500+ institutions in 35+ countries Wharton Research Data Services 4
Evolution of WRDS Data Aggregator (1993 - present) Access to Cloud Servers with Research Data and Macros Economy of Scale in Data distribution Full-time Technical support Online help, email support, and 24/7 network monitoring Research Platform (2001 present) Knowledge Base: Data Overviews, Research Applications, and SAS macros Full-time Research support Research Team: 8 Ph.D.s in Economics and Finance Research Analytics (since 2013) 5
WRDS advantages over financial portal Batch download of data: Ex: One can download CRSP s historical stock data from 1926 current Optimal structure for research WRDS review original data from vendor and restructure it so that it can be ready for research Academic research analytics (ex linking suite, financial ratio, SEC analytics, etc.) WRDS provides research analytics and research program that can help academic research One stop shop for academic research User can access WRDS data and run programs on WRDS cloud User can link their Dropbox to WRDS and easily use outside data on WRDS cloud or share research output with co-authors 7
Agenda 1 2 What is WRDS? Accessing the Data 3 Financial Data on WRDS 4 COMPUSTAT / CRSP overview 5 Advanced Access Features 6 WRDS Research Products & Support 8
Multiple Access Options WRDS Internet Web Queries: For Downloading Unix WRDS Cloud: For efficient querying and storage access PC SAS, R, Python, STATA, etc.: For programming language users Web-based Applications: SAS Studio / Jupyter Hub 10
Agenda 1 2 What is WRDS? Accessing the Data 3 Financial Data on WRDS 4 COMPUSTAT / CRSP overview 5 Advanced Access Features 6 WRDS Research Products & Support 11
Sources of Financial Data (see full data list in the appendix) Fundamentals/Filings Yearly/Quarterly Snapshots of a Firm (Unit: Firm) Assets/Trading Daily/Monthly Trading Price/Volume of a Security(Equity, Bond, Derivatives) (Unit: Issue) Industry Specific (Ex) Bank specific database (Unit: Firm/Industry) Events/Transactions M&A, Security Issuance(IPO, SEO), News Datasets (Unit: Firm) Institutional Investors (Hedge Funds and Mutual Funds) Characteristics, Ownership, Return of Funds(Mutual/Hedge) (Unit: Fund) Firm Behavior/Individuals Corporate Governance, Social Responsibility (Unit: Firm) Analysts, Human Networks (Unit: Individual) 13
Browse Data by Concept https://wrds-www.wharton.upenn.edu/pages/get-data/ 14
Global Data on WRDS Financial Fundamentals: Public companies: Compustat Global, Worldscope, Factset Fundamental Private companies: Bvd Orbis, Bvd Amadeus Stock prices: Compustat Global, Datastream, and more Ownership: Thomson Global Ownership M&A: Thomson SDC Others: Wind, Toyo Keizai, ESG databases, and more 15
WRDS Video Resources Remote Learning Data Analytics Research Technology https://wrds-www.wharton.upenn.edu/pages/video-support/ 16
Agenda 1 2 What is WRDS? Accessing the Data 3 Financial Data on WRDS 4 COMPUSTAT / CRSP overview 5 Advanced Access Features 6 WRDS Research Products & Support 17
Accounting and Financial Data in COMPUSTAT COMPUSTAT North-America: U.S. and Canadian fundamental and market information on 35,000+ active and inactive public companies, from 1950-present COMPUSTAT Global: Fundamental data of 39,000+ active and inactive companies in 80+ countries, the earliest data is from 1986 Fundamental data available on an annual and quarterly frequencies with thousands of Income Statement, Balance Sheet, Statement of Cash Flows, and supplemental & industry-specific data items Market data available on a monthly and daily frequencies with Prices, Dividends, Returns, Trading Volume, Shares Outstanding and Short-Interest Information (Very Similar to CRSP in many dimensions) Both Fundamental Data and Market Data are now in the same data feed and are updated daily 19
COMPUSTAT Coverage COMPUSTAT North-America COMPUSTAT Global Region U.S. and Canada About 80 countries excluding North America 39,000+ active and inactive public companies Annual, Quarterly for Fundamental, Daily for Securities 1987 - present 1987 - present Coverage 35,000+ active and inactive public companies Annual and Quarterly for Fundamental, Daily for Securities 1950 - present 1962 - present 1962 - present 1983 - present Frequency Annual Quarterly Monthly Daily Fundamental Date Range Security Date Range 1985 - present Currency Values in original currency 20
COMPUSTAT Example Get Current Assets, Assets for IBM, Microsoft, and Apple between Jan 2019 and Dec 2021 21
COMPUSTAT Examples Finding Financial Statement Variables for IBM Finding Financial Statement Variables for unknown ID Finding ~ for more than one company with known IDs Add web query output filters Choose the output format you need 22
WRDS Support for COMPUSTAT Compustat manual WRDS Documents Sample Programs (extracts, point-in-time, etc.) Research Sample Programs Compustat Research Applications (book-to-market, P/E Ratio ) Research Applications Remote learning clip part a and part b 23
Primary Identifiers The Primary Identifiers GVKEY at firm-level GVKEY + IID at security-level GVKEY is a unique six-digit identifier and primary key for each company and assigned by COMPUSTAT: Permanent, and Unique (Not Recyclable). IID is assigned for each securities Alphabet s class A stocks: IID = 01 Alphabet s class C stocks: IID = 03 24
Common Secondary Identifiers Stock Tickers: Exchange Specific, with Suffixes, NOT Permanent, and Recyclable CUSIP also is NOT Permanent but Unique and NOT Exchange Specific. 8- or 9-digit CUSIP is assigned for issues (e.g. equities and bonds). 6-digit CUSIP is assigned for issuers (e.g. firms) CIK: US filing number at the SEC, and Changeable Company Names are often abbreviated/manipulated by data vendors Very difficult to have a reliable merging code by using company names 25
Identifiers example Alphabet & Meta (source table: Compustat s comp.SECURITY) Alphabet & Meta (source table: Compustat s comp.NAMES) Compustat s TICKERs, CUSIPs, CIKs are updated to the most current information (header) Source: CRSP Historical Names file 26
Compustat Data Basic screening Screening with filing type, data source, and data type Consolidation Level: Consolidated (C) / Non-Consolidated (N) / etc. Industry Format: Industrial (INDL) / Financial (FS) Data Format: Standard (STD) / Summary (SUMM_STD) / etc. Population Source: Domestic (D) / International (I) Company Status: Active (A) / Inactive (I) Data Items / Footnotes / Data Codes 27
Stock Market Data in CRSP Center for Research in Security Prices (CRSP) is a research center at the Booth School of Business of the University of Chicago Comprehensive collection of daily and monthly security records for the NYSE/AMEX/NASDAQ/ARCA trading activities Daily and Monthly data for roughly 28K + securities of Domestic companies and ADRs traded on major exchanges from 1925 present Complete historical information : Accurate records of special distributions and stock splits in return calculation Keep delisting companies (pre-M&A or bankruptcies), and delisting returns 29
Primary Identifiers Primary Identifiers PERMNO at security level PERMCO at firm level PERMNO is a unique permanent identifier and primary key assigned by CRSP to each security: Permanent, and Unique (Not Recyclable). PERMCO is a unique identifier for each company assigned by CRSP : Permanent, and Unique (Not Recyclable). Date Identifiers Calendar date: date In monthly stock file, date is set to be the end of the month 30
Common Secondary Identifiers Stock Tickers: Exchange Specific, with Suffixes, NOT Permanent, and Recyclable CRSP keeps historical changes in Ticker symbols CUSIP also is NOT Permanent but Unique and NOT Exchange Specific. 8- or 9-digit CUSIP is assigned for issues (e.g. equities and bonds). 6-digit CUSIP is assigned for issuers (e.g. firms) CRSP keeps both most current CUSIP and historical CUSIP (NCUSIP) Company Names are often abbreviated/manipulated by data vendors CRSP keeps historical changes in company names Very difficult to have a reliable merging code by using company names 31
Identifiers Example CRSP PERMANENT CRSP PERMANENT START DATE OF END DATE OF EFFECTIVE CUSIP IDENTIFIER EXCHANGE COMPANY NAME - HISTORICAL NUMBER COMPANY NUMBER 665 EFFECTIVE NAME NAME - HISTORICAL TICKER SYMBOL - HISTORICAL BRST 86079 19801204 19890131 11003510 BRISTOL GAMING CORP 86079 665 19890201 19920419 11003520 BRST BRISTOL HOLDINGS INC 86079 665 19920420 19950822 84892010 SPTK SPORTS TECH INC 86079 665 19950823 19970630 01662710 ALCM ALL COMM MEDIA CORP 86079 665 19970701 20010729 57090710 MSGI MARKETING SERVICES GROUP INC 86079 665 20010730 20011014 57090710 MKTG MARKETING SERVICES GROUP INC 86079 665 20011015 20020331 57090720 MKTG MARKETING SERVICES GROUP INC 86079 665 20020401 20030126 55308X10 MKTG M K T G SERVICES INC 86079 665 20030127 20031127 55308X30 MKTG M K T G SERVICES INC 86079 665 20031128 20040111 55308X30 MSGI M K T G SERVICES INC 86079 665 20040112 20050208 58445910 MSGI MEDIA SERVICES GROUP INC 86079 665 20050209 20061019 55357010 MSGI M S G I SECURITY SOLUTIONS INC 1 PERMCO per company 1 PERMNO per stock 10 Different CUSIPs 5 Different Tickers 8 Different Names 32
CRSP Example Get monthly prices, return and volume information for Microsoft and Ford from 1925 to 2008 33
CRSP Examples Stock returns Stock headers Dividends/share repurchases Distribution Adjustment factors https://wrds-www.wharton.upenn.edu/pages/grid-items/crsp-basics/ 34
WRDS Support for CRSP Sample Programs Data Extracts, CCM and merging by CUSIP Calculate CAPM beta and excess returns Research Sample Programs CRSP Sample Programs Research Applications Compound returns, Momentum and Governance Portfolios Fama-French factors and B/M portfolios, Beta Estimation Research Applications Remote learning Useful Variables, Basics, Coverage, Database Structure 35
How to Link CRSP and COMPUSTAT Major Difficulties to link CRSP and COMPUSTAT Different Frequencies: COMPUSTAT Accounting items are on annual-basis or quarterly-basis CRSP Security trading data is Monthly or Daily Different Universe of Companies CRSP: Equities listed in NYSE, AMEX, NASDAQ, ARCA, and BATS COMPUSTAT: 10K and 10Q Filers to the SEC Solution: Use the CCM(CRSP-COMPUSTAT Merged) Dataset or the linking macro 36
Agenda 1 2 What is WRDS? Accessing the Data 3 Financial Data on WRDS 4 COMPUSTAT / CRSP overview 5 Advanced Access Features 6 WRDS Research Products & Support 37
Advanced Features Access Data Remotely on WRDS Server What if a web query does not do all that you need it to do? Filtering data on multiple conditions Some data tables are only accessible on WRDS cloud More ways to access finance data at WRDS Advantage Batch Running Fast remote data access Interactive Programming Programming language needs to be installed PC-SAS has slow remote data access Easy access and work from anywhere online No installation required UNIX (SSH) Remote connection via local computer Web-based applications (WRDS SAS Studio, WRDS Jupyter Hub, RStudio) WRDS Cloud gives you 10GB secure, personal home directory storage and access to 500GB shared directory storage within your institution. Helpful for storing code and results! Customize your software (R, Python, SAS, etc) and connect to WRDS cloud server 39
WRDS SAS Studio https://wrds-cloud.wharton.upenn.edu/SASStudio/ A web-based SAS application running on WRDS cloud WRDS SAS Studio support: https://wrds- www.wharton.upenn.edu/pages/support/programming-wrds/programming-sas/sas- web-sasstudio/ 40
WRDS Jupyter Hub https://wrds-jupyter.wharton.upenn.edu/hub/ Jupyter Hub is a web-based notebook style Interactive Development Environment Python3 and R are supported 41
WRDS RStudio https://wrds-rstudio.wharton.upenn.edu/ A web-based R application running on WRDS cloud WRDS RStudio support: https://wrds- www.wharton.upenn.edu/pages/support/programming-wrds/programming-r/r-from- the-web/ 42
PC-SAS / Connect Concept Steps: SAS software installed on your Windows PC 1. Connect to WRDS server %let wrds = wrds-cloud.wharton.upenn.edu 4016; Some local SAS dataset in your PC options comamid = TCP remote = WRDS; Remote access WRDS data by connecting to WRDS server signon username = _prompt_; 2. Remote Submit rsubmit; {Program} endrsubmit; Use WRDS powerful Unix server processing resources Access Unix permanent (10GB) & temp (4.5TB) disk spaces 3. Sign off signoff; Support Getting Started -> Accessing WRDS -> PC-SAS Connect 43
PC-SAS / Connect An Example Example: Find all companies in 1997 with sales >= 1 billion , total assets >= 5 billions , and with >= 30 years of publicly reported financial statements . /* PC-SAS/Connect Communication Block */ %let wrds = wrds-cloud.wharton.upenn.edu 4016; options comamid=TCP remote=WRDS; signon username=_prompt_; /* Submit SAS code to WRDS Unix Server */ rsubmit ; /* SAS CODE submitted to WRDS Sever */ proc sql; create table demo( where= (fyear=1997 and sale>=1000 and at>=5000 and firm_age>=30)) as select fyear, conm, tic, gvkey, sale, at, (fyear - min(fyear)) as Firm_Age from comp.funda where not missing(at) and consol="C" and indfmt="INDL" and datafmt="STD" and popsrc="D" group by gvkey; quit; /* Remote SAS CODE submission Ends */ endrsubmit; /* Sign off from PC-SAS/Connect */ Signoff; 44
How to Access SAS Data in UNIX 1. Install SFTP Client software in your PC (freeware such as WIN SCP) 2. Set SFTP Client to be connected to the server: wrds- cloud.wharton.upenn.edu with your wrds (wharton) username and password 3. Retrieve and Upload files in SAS and other formats among your local machine and remote UNIX server. Support Getting Started -> Accessing WRDS - > SSH & UNIX 45
How to run SAS in UNIX 1. Install SSH Secure Shell (free) software in your PC 2. Set SSH to be connected to the server: wrds- cloud.wharton.upenn.edu with your wrds (wharton) username and password 3. Create, Edit and Run your SAS program using Unix commands. 46
Step-by-step Remote Access Setup SAS Python R STATA UNIX(SFTP) 47
Agenda 1 2 What is WRDS? Accessing the Data 3 Financial Data on WRDS 4 COMPUSTAT /CRSP overview 5 Advanced Access Features 6 WRDS Research Products & Support 48
WRDS Research Products WRDS Research:https://wrds-www.wharton.upenn.edu/pages/support/wrds-research/ Data Research Analytics Research Programs & Guides 50
