Basic information on financial databases: cook books, tips and tricks & economic news

This blog contains schematic easy to grasp - hands on - help in performing searches in economic databases, making work sets and making them inter-exchangeable between the databases.

* Disclaimer. I am not a finance professional. Most posts are the result of personal findings.

Note:
All presented images are scaled and can be enlarged to original size (click the picture).

Search This Blog

Loading...

8/16/2016

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
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)
(CSHOCommon 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 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
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

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

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

2/22/2016

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
Didn't find it. It should be somewhere under this data item: MSFHDR


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