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

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


No comments:

Post a Comment