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

Most important financial ratios in databases

The most commonly used ratios in finance.
How to interpret the text:
Data types presented in RED are downloadable data
If ratios or their components are not ready for download I try to present ways to calculate them (indirectly)
1. Debt to equity ratio
2. Current ratio
3. Quick ratio
4. Return on equity (ROE)
5. Net profit margin

1. Debt-to-Equity Ratio
Total Liabilities / Shareholders Equity
Shareholder equity = book value x number of shares

Compustat
Direct download: DLC 
DLC represents the total amount of short-term notes and the current portion of long-term debt (debt due in one year).
LT = Total liabilites
BKVLPS (book value per share)
(CSHO) Common Shares Outstanding
Formula:
LT / (BKVLPS x CSHO) = debt to equity

Amadeus

Book value per equity =  Book value per share x number of shares outstanding
Instead of  Book value of equity, Market Value ( MV) can be used (market capitalisation)
(closing price x shares outstanding)
In Amadeus only closing price frequencies monthly and weekly, not daily 
And: market value per year can be downloaded 
Total liabilities = current liabilities + long term liabilities

Formula:
Total debt / total (shareholder) equity = Total liabilities / (market capitalisation x number of shares outstanding) OR (market capitalisation x book value per share)

Datastream*

WC03351 Total liabilities: 
WC03995 Shareholder equity: (book value per equity)
NOSH Number of shares outstanding
Instead of Book value of equity, Market Value ( MV) is used (market capitalization)
Market Value MV = share price (P) x number of shares (NOSH) (market capitalisation)

Formula:
1. WC03351 / WC03995
2. WC03351  / MV

*) using search terms debt equity ratio in datatypes provides to following data producing datatypes:
WC08231 (total debt % common equity); WC08221 (total debt % total capital/std); WC08231A (total debt % common equity); WC08231R (total debt % common equity) !! Not all  companies provide data. Error types like No data values found; no world scope data; access denied

For more downloadables on income statement and balance sheet see this

2. Current Ratio (working capital ratio)
Current Assets / Current Liabilities

Compustat

Current assets total / current liabilities = current ratio
ACT = Current assets total
LCT = Current Liabilities - Total

Formula:
Current ratio = ACT / LCT  

Amadeus

Formula:
Current ratio Current assets total   / current liabilities

Datastream

Formula:
WC08106 Current ratio = (current assets total  [WC02201] / current liabilities [WC03101] )
Only industrials apply

3. Quick Ratio (quick assets ratio)
(Current Assets – Inventories) / Current Liabilities

Compustat

INVT -- Inventories - Total
ACT = Current assets total
LCT = Current Liabilities - Total

Formula:
(ACT - INV) / LCT

Amadeus
Inventories not in AMADEUS

Datastream

Quick ratio WC08101
Inventories total WC02101
Current assets WC02201
Current liabilities WC03101

Formula:
(WC02201 - WC02101) / WC03101 = WC08101

Not all companies provide data

 4. Return on Equity (ROE) (return on net worth)
Net Income / Shareholder's Equity or
(net earnings (after taxes) - preferred dividends) / common equity

Compustat

NI Net income
(BKVLPS x CSHO) shareholder equity
BKVLPS (book value per share)
CSHO Common Shares Outstanding

Formula: 
NI / (BKVLPS x CSHO)

Amadeus

P/ L for period = Net Income
Book value per share x number of shares outstanding = Book value per equity
ROE using net Income %

Formula: 
(P/L) / (Book value per share x number of shares outstanding) = ROE

Datastream

ROE (total): WC08301
WC03995 Shareholder equity: (book value per equity)
NOSH Number of shares outstanding
Instead of Book value of equity, Market Value ( MV) is used (market capitalization)

Market Value MV = share price (P) x number of shares (NOSH) (market capitalisation)

Formula: WC01001 / MV = WC08301 OR WC01001 / WC03995 = WC08301

5. Net Profit Margin
Net Profit / Net Sales 

Compustat

Gross profit margin
Gross Income / Net Sales or Revenues * 100 = UGI / SALE x 100
Gross profit: GP (loss)
Gross income: UGI
. the difference between sales or revenues and cost of goods sold and depreciation.
Net sales or revenues: SALE (sales/turnover)
gross sales and other operating revenue less discounts, returns and allowances. 
REVT = Revenue - total
GP = UGI / SALE x 100

Addition Compustat from (copyright) WWU Münster Data- items and ratios

Amadeus

Gross profit
Profit margin (%)
Sales

Formula: 
Gross profit / sales (not exactly)

Datastream

DWNM = net profit margin
WC01001 Net sales or revenues 
WC01540 Net profit( operating income)  
* Components can't be all downloaded. Not all companies provide data


LINKS
Compustat: For more downloadables on income statement and balance sheet see this
Amadeus: For more downloadables on income statement and balance sheet see this
Datastream: for more downloadables on income statement and bsalance sheet, see this

DJSI - Dow Jones Sustainability Indices

Sustainability has become a hot topic in business.
The indices founded to investigate, measure and promote company to embrade a social responsibility agenda, currently the 2500 largest from Dow Jones Global Total Stock Market Index (DJGTSMI).
It covers the top-ten percent of these companies in terms of economic, environmental, and social criteria which equals about 300 companies. The DJSI World has two subset indexes, which are the Dow Jones Sustainability Index World 80 (DJSI World 80) and the Dow Jones Sustainability Index World ex US 80 (DJSI World ex US 80) (wikipedia)

Companies are consequently invited to join the "DJSI family".
The DJSI is based on corporate economic, environmental and social performance criteria, containing corporate governance, green agenda, social agenda etc.
The DJSI is managed by S&P Dow Jones Indices and RobecoSAM* (sustainability asset management)
The main Index is DJSI world

An overview of the DJSI family (linked from http://www.sustainability-indices.com)































Options on sustainability and sustainability indices:

Datastream contains an ESG Asset4 database providing sustainability information, and data about CSR (Company Social Responsibility)
Wharton/ Compustat doesn't contain any sustainability data (ESG related)
Reprisk** does.

Neither Wharton (Compustat Index Constituent), Compustat Global Index Constituent nor Datastream  constituent lists from indexes result in company lists.


Dow Jones Global Total Stock Market aggregated index is available in Datastream
As far as I can see, Wharton databases don't.

According to Datastream (static search djsi > constituent lists)
the index meniotned (ex tobacco) Clicking the rule mentiones constituent for three indexes. > so I called the three indexes:
1. Dow Jones Sustainability Indices World Excluding Tobacco (Dollar)
2. Dow Jones Sustainability Indices World Excluding Tobacco (Euro)
3. Dow Jones Sustainability Indices World Excluding Tobacco (Swiss Franc)

1:  Three constituent lists > No data, no constituents

DJSI World Composite (LDJSWDCE) > Constituent list >

EITHER SEARCH: results in NA (not available)

Sources for companies:
http://djindexes.com/sustainability/?go=literature

* RobecoSAM > this company deals with sustainability specifically.
(water, energy, climate, agriculture, and liveability )

** Reprisk:
https://www.reprisk.com/

DJSI family:
Dow Jones Sustainability World Index
Dow Jones Sustainability Europe Index
Dow Jones Sustainability North America Index
Dow Jones Sustainability Asia Pacific Index
Dow Jones Sustainability Korea Index

CONCLUSION:
Idealy it would be easy to retrieve the companies composing those indices. But it isn't.
As for the companies constituing the index: the list impossible to retireve anywhere. The only resolve is to contact RobecoSAM (Academic requests: http://www.sustainability-indices.com/academic-request.jsp

New website interface Wharton/ Compustat

Mid July 2015 Wharton changed its website for a test and published a beta version.
Luckily I was in the opportunity of checking it out.
Here are a couple of impressions

Old Interface

New Interface

1st impression. Non subscribed databases don't show on the starting page.
Example search Compustat > Compustat Global >  Fundamentals Annual > data searching

Compustat Global OLD

Compustat New

After this the screens look familiar.

For explanations on Wharton / Compustat in the old Interface > check out these blog items

Auditor independence, auditor tenure and restatements

Auditor tenure:

It is obvious that (internal/ external) auditors are supposed to be independent, otherwise the need to report after discovering accounting errors and such could be biased.

Therefore it is healthy to rotate auditors, lest they don' t get (financially involved in the company they are auditing) Independence stimulates integrity and objectivity.

SEC has formulated Requirements Regarding Auditor Independence.

The rest of the world should follow suit shortly.

 
Restatements happen when things in the statement are found incorrect.
The cause could be anything from accounting errors, noncompliance with accounting principles, misinterpretations, fraudulent activities or other.
Restatements can be positive or negative. No need to say that a negative restatement can influence stock trade and or company performance. ( source Investopedia)
Needed extra statements:
8-K
10-Q
10-K

Recommendations and regulations

. auditor and consultants shouldn't come from same company

. auditing companies shouldn't have a large stake in he client's company

. reviews of the auditing by a peer auditor on 3 annual basis (USA)

. formation of audit committees

. rotation of auditors to insure independence 

> objection is lack of insight in the company when  high rotation occurs (discussion on rotation frequency) 

> in fact one might expect, like in any work situation, that efficiency improves over time until it peaks and after that declines

Databases dealing with mentioned subjects

Amadeus

XXXX

Wharton

Auditor tenure as WRDS explains it.
LAST REPORTED AUDITOR FKEY
LAST REPORTED AUDITOR NAME
LAST REPORTED AUDITOR SINCE EVENT DATE
LAST REPORTED AUDITOR SINCE EVENT TYPE

Audit Analytics (North America) Auditor changes
For instance looking for accounting errors or fraude.

Results list

(the output can be exported to Excel)

Also see this post:

Auditor information

Environment and sustainability - databases

Sustainability awareness has become an important issue for companies. Many publish aside from the financial report, a sustainability report. Enviromental awareness grants goodwill.
This post will focus on both: goodwill and sustainability.

Sustainability

Environmental Performance Index (EPI)
World wide environment index of countries.
http://epi.yale.edu/  (open access)


Asset4 database (through Datastream)
Output in Excel, through the Excel interface. Information is on company level
!!! Not all listed companies  provide data to Asset4 database !!
Important to test which companies do:
How it works:
. Create a static work set  with at least data types, Name, ISIN,ASSET4 Company ID (provides a yes or a no)
. Filter out all companies with Asset4 (auto filter in Excel)
. Upload new list into Datastream
. Download Asset4 data as datatypes
In pictures:

Only 2 comp apply

Filtered.
Time series> asset4 datatypes more than shown

Try to search for alternative terms (items) like emissions etc
result on CSR (Corporate Social responsibility, 2 comp)

More info on Asset4 (board)



Webdirectory
https://www.webdirectory.com/Databases/

Examples: Supra National organizations

World Bank
http://www.worldbank.org/en/topic/environment
Choose: data

United Nations
http://geodata.grid.unep.ch/

Select an item and press continue

A couple of options is presented next

E.G. show table
As you can see, all options remain available, ordered in tabs.

China in chart

Select TAB download (national) > extensions available from csv, Excel etc
Define dataset

Submit.

In my humble opinion: a bit disappointing result, (showing only 2010) compared to the graph



Publications of the International Food Policy Research Institute


Statistics sites (not specifically focused on environment, sustainability)
CBS
Monthly Bulletin of Statistics
ODS

OFFstats - Official Statistics on the Web
Source OECD


Goodwill
Goodwill is measured in many ways. It is presented in annual reports in the balance sheet (iow: companies try to validate goodwill)

Goodwill (finance): An intangible asset that arises as a result of the acquisition of one company by another for a premium value.
The value of a company’s brand name, solid customer base, good customer relations, good employee relations and any patents or proprietary technology represent goodwill. Goodwill is considered an intangible asset because it is not a physical asset like buildings or equipment. (investopedia)
Charity, good customer services, sustainability, working conditions etc are contribute to goodwill.
Wikipedia: according to International Financial Reporting Standards (IFRS), goodwill is never amortized.

Datastream

In total: 327 data types apply to search term Goodwill
(for explanations on using Datastream see these items)

Compustat

North America: 9 datatypes
Global: only 1 datatype >

(explanations on using Compustat see these items)

SDC

In Mergers & Acquisitions database, the focus is solely on the acquiror. See definition

(for explanations on using SDC)

Suggestions?