Return data and market value in Compustat

Many researchers are puzzled by the apparent lack of common data like return or market value in Compustat databases.

RETURN

Compustat North America
Fundamentals annual No

PRCC_C -- Price Close - Annual - Calendar
PRCC_F -- Price Close - Annual - Fiscal
AJEX
But no TRFD

Security daily No
Use
Needed data types PRCCD, AJEXDI, TRFD
((PRCCD / AJEXDI) * TRFD)t) /  ((PRCCD / AJEXDI) * TRFD)t-1) * 100

Source  WRDS: ((prccd/ ajexdi )* trfd )[ current ] /( prccd/ ajexdi )* trfd ))[ prior time period ]-1)*100)

Compustat Global
Fundamentals annual. No

Security daily
Price:Yes
Needed data types PRCCD, AJEXDI, TRFD
((PRCCD / AJEXDI) * TRFD)t) /  ((PRCCD / AJEXDI) * TRFD)t-1) * 100

source WRDS: ((prccd/ ajexdi )* trfd )[ current ] /( prccd/ ajexdi )* trfd ))[ prior time period ]-1)*100) 

CRSP
Stock/ security files > 

Monthly stock file 
No total return
This is what looking for return provides:

Daily Stock File
Looking for returns results in similar downloadables.

Conclusion: CRSP is not a good medium for return data

CRSP/ Compustat Merged
Fundamentals annual: No

Security daily: Yes
Needed data types PRCCD, AJEXDI, TRFD
((PRCCD / AJEXDI) * TRFD)t) /  ((PRCCD / AJEXDI) * TRFD)t-1) * 100

MARKET VALUE


Compustat North America
Fundamentals annual: Yes
MKVALT
Security daily: No.
Use PRCCD * CSHOC = Price Closing daily * Common shares outstanding

Compustat Global
Fundamentals annual: No
No price data either
Security daily: No
Use PRCCD * CSHOC  = Price Closing daily * Common shares outstanding

CRSP
Monthly stock file: No
Use Price * Number of Shares Outstanding =  PRC * SHROUT

Daily Stock File: No.
Use Price * Number of Shares Outstanding =  PRC * SHROUT


CRSP/ Compustat Merged

Fundamentals annual Yes
MKVALT -- Market Value - Total - Fiscal
Or 
Price close
PRCC_C -- Price Close - Annual - Calendar
PRCC_F -- Price Close - Annual - Fiscal
CSHO -- Common Shares Outstanding
Market value: Price * Shares outstanding

Security daily: no
Use PRCCD * CSHOC  = Price Closing daily * Common shares outstanding

Financial databases with IPOs, IPO-dates, company age, joint ventures.

In an ideal world all databases would contain all data and would be fully inter exchangeable.
This post aims an abridged overview of commonly used databases.
I deliberately chose not to focus solely on M&A databases.
Here are some databases.

For database with only publicly listed companies: date of incorporation (company age/ company founded) differs often from the IPO date, the date it went public. As for non public companies: no IPO date until further notice. But company age is retrievable.
Databases particularly concerning: Wharton/Compustat and Datastream (only listed companies)


AMADEUS (more Amadeus tips & tricks: here)
You can't find any data on deal types, IPOs etc. from the start menu.
So first you'll have to construct a workset of companies.
After your selections and opening the list of results, with VIEW list of results . . .











 . . . you can use  the link ADD at top right of the screen (image)
  









A new menu will open.
Note: this principle applies to all Bureau van Dijk databases: Amadeus, Bankscope, REACH, Zephyr etc.

If you can't find the desired data type, you can text search for it.
Give OK.

Joint ventures: a bit hidden
Follow above mentioned method.
Mergers & acquisitions.
Maybe it is sensible to make the selection from the start menu.

 ChooseMergers & acquisitions > M &A dels
A new window will open with selection boxes.

As you can see this conditional and focusses on a specific value. In this case, total assets.
Given the time period (-2 years) it ends up with this amount of joint-ventures.

Additional data can be gathered from the list of results.








BANKSCOPE (more bankscope tips & tricks: here)
(OECD countries)
You can't find any data on IPOs etc. from the start menu.
So first you'll have to construct a workset of companies.
On the other hand, there is the option to look for Mergers & Acquisitions

IPO date has to be searched separately: List of results > ADD > type in IPO date
After your selections and opening the list of results, with link ADD you can add extra data without further limiting your search



DATASTREAM: (for more Datastream tips & tricks: here)
Date company founded (WC18272 Static DATA)
(data SHOW as NA not available)



SDC (for SDC tricks and tips see: here)
SDC offers IPO data.
Global New Issues (GNI)
Settings for this example:
United States, all Public new issues, US common stock, time scope from 3rd January 2000 > on
In the Menu All Items, search words IPO
or browse through TAB Deal. E.g.: IPO date or IPO flag, select all IPOs.
Nr of IPOs: 3707

Make Custom Report >



I selected name, issue dates, filing date, offer price, shares offered . . . date founded

Secondary IPO data: M&A database
Most basic is to search in the menu 'All categories' searching for IPO

 
Most of presented items open new menu widows enabling to refine your search.
Looking for IPOs of companies involved in mergers and acquisitions is harder.
Maybe the following options is better.
I will stick to a limited set :
Period:01/01/2000
US targets
US acquirors
Deal value:  > $ 500M
Deal status: completed
Press Button Execute in the
TOP BAR
Nr of deals: 4006


Two options now:
Using menu, ALL Items, search word IPO >

Many of these IPO data are flags : (Y/N)
Mind: using IPO at this moment will limit your search considerably.

Better is to go to Custom report.

Again use the ALL Items, search word IPO, but now the data won't influence the size of your work set

After selecting the desired items, click OK > and say NO you don't want to go back select items.
Back in the main Menu. Execute your search.



Joint Ventures

M&A database > select joint ventures
All dates . . . Search all Items: Cross border . Y . . .  8723 hits
Make custom report to add extra data
Company names, name of the joint venture, nation, joint venture flag, status of the alliance . . .


Example.

WHARTON

(TO B
Amadeus
Ownership data : codes.
JO = Joint Venture


Compustat (North America, Compustat Global)
(more Compustat tips & tricks here)
Pathway:
Choose database > Fundamentals annual > search with CTRL-F: IPO date
WRDS explanation: This item is the date of a company's initial public stock offering. If the date of a company's initial public stock offering is not available, the first trading date in the major exchange is used.

CRSP
According to WRDS support, it should be somewhere under this data item: MSFHDR (stands for monthly security financial header, I believe)
De proper pathway is as follows: CRSP > Stock/ security files > stock header info >
The data items concerned are
Begin of stock data (in output labeled BEGDAT)
End of stock data (in output labeled ENDDAT)
Some additional complications will appear in a few cases when a firm (PERMCO) had multiple securities (PERMNOs). In those cases, you would need to take the oldest BEGDAT and the latest ENDDAT.

ZEPHYR (more Zephyr tips & tricks: here)
Zephyr has a focus on M&A data specifically
World wide and listed and unlisted companies
After your selections and opening the list of results, with link ADD you can add extra data without further limiting your search

After calling the view list of results and pressing the ADD link, this menu openns, which is devided in deal info, company info and advisor.

As you can see, you can text search info or unfold the categories.










List of results with added data

WRDS CRSP Betas

Until today I didn't know you can download betas from WRDS > be it limited, it is possible
(only US and only AMEX or NASDAQ)
I'll show you how.

Note: it is helpful to be familar with Compustat, Compustat - CRSP merged and how they relate.
CRSP - Compustat

Open up the CRSP database.
Select " Stock / Portfolio Assignments"
Select Betas deciles
From WRDS database:  Each portfolio time series is based on a portfolio type defined by CRSP and contains a history of statistics and portfolio assignments for a security. The portfolio time series can be linked to CRSP index returns data to calculate excess returns of a security against its assigned index portfolio at any time during its history.

What you can do, is retrieve a work set from another database or index constituent.

Looking up a single security: Microsoft
(unfortunately WRDS CRSP doesn't say on  which Index the security is noted)

The following is very important and determines whether you get output or not.
The safest bet is, when you draw a work set from either NYSE/ AMEX or NASDAQ, to stick with that index.

Example. From CRSP / COMPUSTAT merged.


Set Time period to investigate.
Search entire database.
Be certain to download Stock Exchange Code, as you have filter you output on that very code. 
Stock exchange code, e.g. Nasdaq (see also over view codes in Compustat manual or internet or here
Stock Exchange code for NYSE / AMEX is 11
NASDAQ is 14 

Note: this can result in very large output set of which you'll have to dismiss the majority of data since you are only interested in securities from NASDAQ or NYSE/ AMEX
Example exchange codes in output

As for output size: this output set contained 13.000 plus rows
After using auto filter (data, use funnel symbol) filtering on exchange code 11 and removing duplicates (use company identifiers like CUSIP, CIK GVKEY or PERMNO, PERMCO)
10.000 plus unique securities remain.
Large output sets!
In this example I made a single file of the Permno and uploaded them into
Stock / Portfolio Assignments > Beta deciles
Output example (download as TAB delimited TXT and open with wizard int Excel)

Note: after downloading and opening a match has to be made between initial download of the filtered set and this output.

Another method is to download the Index constituent form either these indexes.
In Compustat North America the number of downloadable index constituents is imited.
i.e. NASDAQ 100
Look here
! NYSE AMEX not among them !



Be sure to download PERMNO and / or PERMCO

I tried both and came up with either  no results or regular output.

Some helpful links
Adjusting company identifiers


Finabase: Finding historical beta in Datasream
 Financial databases and research blog

Calculating return with Excel

In one of my posts, a couple of years ago, I told something about Return, expected return and abnormal return.: This post
In this post I am going to focus on caes where return can't be downloaed, but (stock) prices can.
In Excel you can calculate the return yourself.

Today's stock prices minus yesterday's stock price, divided by yesterday's stock price

(Px-Px-1)/ Px-1 = return or

(Px-Px-1)  = return
   Px-1

Working from a table with stock, this is what you do:


Price day in i.e. cell B8 minis price in cell B7 divided by the price in cell B7
See pic

< enter>

Now double click the + in the right bottom of the screen
Result:

Now what if we want to compare this to the returns of AEX?
After downloading the Index prices simply copy the column with the Ahold returns next to the AEX returns. It will keep the formula but calculates the AEX returns. Neat.

Databases with returns data

Datastream (see examples above)

Compustat North America: Security daily > TRFD > Total return factor Daily (TRFD is monthly in the monthly securieties datbase)
Not in the other N. Am. databases.

CRSP - Daily Extract with Time Window does provide event studies with return data. (PERMNO codes only, which have to be drawn from other datasets through CRSP/Compustat merged, making it possible to work with ticker, Cusip, CIK and sector indicators like SIC and NAICS)

Compustat Global does not not, so method above mentioned might come in handy. 
Download the price data in the security daily database

Note: I am going to explore this subject further.

Also see Blog item Return, expected return and abnormal return