Databaser: on financial databases

VPRO Documentary on the credit crunch

2011-12-01T04:18:00.000-08:00

Object linked from the Uitzending gemist service, which allows sharing.

Bekijk de video in andere formaten.

The Euro support fund (ESFS, European System of Financial Supervisors) and the Papandreou effect

2011-11-03T06:22:00.000-07:00

The positive effect of the hard bargained,(but half heartedly taken, measures of expanding the ESFS to € 1000 billion was short lived. The Greek PM saw himself facing an angry population, when they struck at the heart of their national celebration, Friday 28th October (OHI Day). This apparently lead to what we refer to in the Netherlands as "panic football", when he announced to call for a referendum to the Greek people.

Two days later I saw a headline that the cost of this announced amounted to more than $ 1800 billion, world wide, as expressed in the MSCI World Index.

How to find these figures?
Datastream provides figures on this index.
The easiest way is to open the navigator en perform a free text search: "msci world index". The best bet is MSCI WORLD U$ (mnemonic MSWRLD$).
The following data types provide figures, but MSPI (Price Index is what we want)

MSFI, MSTY, MSMC, MSIG, MSLE, MSSG, MSET, MSCM, MSNR, MSNE, MSPI, MSFE, MSSE,
MSRI, MSEG, MSTG

Creating a new chart in Datastream

2011-10-19T06:12:00.000-07:00

Using preformatted charts can be a huge time saver, but not all subjects are covered in Datastream, so it is not imaginary having to make a new chart.
How to begin can be found in this thread < click the link

Example:
Comparing the performance of two indexes (AEX and S&P 500 composite)
Menemonics: AMSTEOE,S&PCOMP
Method:
1. click 'charting'
2.

3. Move cursor over the tri angle and select Find new series

4. (using advanced search)
Search Menu

Noteworthy: both indexes can be selected in a single search, by selecting the 'OR' option (right side search screen) :

5. List of indices. Select BOLD Indices (in this example, the 1st and last page)

6. Click 'use selected'

7. Both indixes are selected. Now it is time to search for the desired data.
Move the cursor over this button to (pre)select:

8. resulting chart:

Datastream charting

2011-10-13T06:54:00.000-07:00

Datastream offers the possibility to make (interactieve) charts, through the option 'Charting', which can be found in the top bar at the series search screen in the navigator.

Charting offers a wide range of poissibilities: from ready made pre-created charts (i.e. key indicators), new charts, a library of ready made charts etc.

Example: Key Indicators (1st tab)

With drop down it is possible to select a specific country and a specific category.

E.g.: Netherlands and category 'Incomes'.

Clicking the link (or from the options list) provides charts like this:

Example: tab Library

> Result: (pressing link Saudi Arabia production)

(left had side of the screen)

The chart: (right hand side of the screen)

Last example: creating a chart (in progress . . .)

Did we really get wealthier? Find out.

2011-10-12T01:26:00.000-07:00

An article (blog) by John Cassidy in the New Yorker painted a picture where in forty years time the annual income of the average American hasn't grown. Measure this to increased cost of living and the avaerage will have become poorer. (See: Poverty and Income in America: The Four Lost Decades and NY Times Soaring Poverty Casts Spotlight on ‘Lost Decade’; Sabrina Tavernisse)
Some figures form these articles:
2.6 million people living under the poverty line, in US today (stated $ 22,314 in 2010)
Average household $ 49,445 (compare 1999: $ 53,252)

According to the Census figures, the median annual income for a male full-time, year-round worker in 2010 — $47,715 — was virtually unchanged, in 2010 dollars, from its level in 1973, when it was $49,065, said Sheldon Danziger, professor of public policy at the University of Michigan

Now, it is interesting to verify the data from the articles.
I used Datastream to globally check the trends the articles claim.
Method: Time series > charting > new chart.
Charting is fairly easy to use.
I rebased all data to 100 so I could present all data in a sigle chart.
The result from my search is shown underneath.

In a table (not rebased):

More criticism on credit rating agencies

2011-08-24T03:51:00.000-07:00

In short: credit rating agencies (CRAs) are companies specializing in investigating credit worthiness (abilty to pay back their loans) of companies and governments. In general concerning certain types of debt obligations like bonds and the debt instruments themselves. Practice is that companies use credit ratings to investigate whether investing in that company would be a good idea or not. But the last couple of years CRAs are primarily known for their ratings of governments and nations, think of Ireland, Greece, Italy and Spain.
In most cases, the issuers of securities are companies, special purpose entities, state and local governments, non-profit organizations, or national governments issuing debt-like securities (i.e., bonds) that can be traded on a secondary market.

The Big Three (Fitch, Moodies and S&P) failed to perform in two major events having a major import on the world economy: the Internet Bubble Burst (2002) and the Credit Crunch, where they failed to rate an important bank like Leeman Brothers, (giving a AAA shortly before it went bust thus triggering the credit crunch). Criticism on CRAs and their performances, or even questioning the usefulness of their ratings at large were and are inevitable. (one is as important as others think you are)

In 2003 the U.S. Securities and Exchange Commission submitted a report to Congress detailing plans to launch an investigation into the anti-competitive practices of credit rating agencies and issues including conflicts of interest. More recently, ratings downgrades during the European sovereign debt crisis of 2010-11 have drawn criticism from the EU and individual countries.

Considering none of the agencies are as independent as they claim to be (they are all subject to companies paying for their services, one can imagine that it's difficult to be hard on those that 'feed' you. This would make their ratings biased, the least.

July 2011 Angela Merkel suggested Europe, and the EU, are in need of a European counterpart which should be more familiar with the specific situations of the economies in members states.

German chancellor Angela Merkel (link) called Sunday for the creation of a European credit rating agency, on the back of recent discontent over the downgrading of some EU economies. . . . (cut) . . . EU leaders object that Standard & Poor's, Moody's and Fitch are an "oligopoly" issuing self-fulfilling prophecies of doom, greatly aggravating the eurozone debt crisis. (17 Jul 11)

Europe fires back at credit rating agencies (link)

The moves revived calls for the creation of a credit ratings agency in Europe to act as counter-weight in a market dominated by US-based agencies.

“We must break the oligopoly of the ratings agencies,” said German Finance Minister Wolfgang Schaeuble, adding that he wanted to “break” their power and “limit” their influence. . . (cut) . . . Amadeu Altafaj, the commission’s spokesman for economic affairs, said Moody’s timing was “questionable” and based on “hypothetical scenarios” that contradicted the European Union’s own assessments.

“This is an unfortunate episode and raises once more the issue of the appropriateness of behavior of credit agencies and of their so-called clairvoyance,” he told a news briefing.

US enters war on rating agencies (link) (13 Aug. 2011) . . . The downgrade of the US debt rating for the first time in its history has left the Obama administration with no option but to enter the war. For the first time, the US Treasury department publicly criticised S&P, suggesting that the rating agency‘s estimates of the government’s discretionary spending were 2 trillion dollars too high. The US government is also considering a proposal that would require credit raters to disclose “significant errors” in how they calculate their ratings, as Reuters reported. . . . (cut) . . . Meanwhile, the US Senate Banking Committee is looking into the possibility of holding hearings into the downgrade. Committee chairman Tim Johnson called the agency’s downgrade an “irresponsible move”, which could have a far-reaching impact.

A list of credit rating companies: (links)
A. M. Best (U.S.)
Baycorp Advantage (Australia)
Capital Intelligence (Cyprus)
Capital Standards Rating (Kuwait)
Credo line (Ukraine)
Dagong Global (People's Republic of China)
Dominion Bond Rating Service (Canada)
Egan-Jones Rating Company (U.S.)
Fitch Ratings (U.S./UK)
CIBIL (India)
Japan Credit Rating Agency, Ltd. (Japan)
Moody's Investors Service (U.S.)
Muros Ratings (Russia alternative rating agency) (Russian)
Rapid Ratings International (U.S.)
Standard & Poor's (U.S.)
Weiss Ratings (U.S.)

Prediction:
With the decline of the importance of the traditional large companies and the fast growing economies of BRIC nations (Brazil, Russia, India and China) it's not imaginary that CRAs from those regions will gain influence.

Relate pages on my blog:
Country Risk, country rating, Ireland Rating
Databases offering Debt/credit ratings
Stock rating, debt rating and stock ranking

Sources used: Wikipedia, Athens news, Inquirer Business, The Local - Germany's news in English,

Datastream Event Studies: calculating destination cells in Request Table

2011-06-20T03:29:00.000-07:00

Datastream offers Request Table for event studies, which enables you to work with different Time Series per fund/equity (etc.) Just like in my former post can working with (very) large work sets be very tedious (not to mention complicated) when you are interested in event studies.

This is what you are supposed to do: (old method)

First of all: create a new work sheet (right mouse click)

Having done that, the next step is making your destionation cells (where to put your request table data, you don't want them on your menu)

This pops up:

Go to the new work sheet and select a destination cell > next screen:

Press OK

Back in the menu it looks like this.

Imagine having to repeat this 800 times?

Here comes the time saver: (new method)

Take a new Excel work sheet, and put in this formula:
="=Blad1!$cell"&rij() (Dutch);
="=Sheet1!$cell"&row() (English)

Drag down (with the + at the right bottom corner of the cell)

Copy the result into your Request Table

Voila, loads of time saved.

If you want to investigate more than one data type, you can change the formula.
For instance, like this: (5 rows)
="=Sheet1!$A"&5*ROW()

In the Dutch language version of Excel the function would be:
="=Blad1!$A"&5*RIJ()
(the data destination shifts 5 rows)

*) many thanks to Aart Bijkerk, who pointed this out to me.

Datastream Event studies: Calculating dates for Request Table

2011-06-17T03:06:00.000-07:00

I have always found it complicated and tedious to do an event study in Datastream. Not only does the request table not understand dates coming from Excel, working with large work sets makes the request table destination dsimply annoying. Needless to say I tried to avoid it.

Calculating dates in Excel
For instance event dates using formulas =(cell number+X) and =(cell number-X), where X is the number of days that has be added or subtracted. The next step would've been copying the dates into a new work sheet and paste it as a number, next convert the numbers into dates save the sheet as a .TXT file, close and open the .TXT files as text. Those dates would finally be understood by the Request table menu.

Calculated dates around the event date
Formula =(cell number+30) or =(cell number-30) > =(A1+30) and =(A1-30)
Use the + in the right bottom of the corner to drag the calculated date

Examples of the old method

Using calculated dates (blog Mark Bruyneel)
Specific event studies (blog Mark Bruyneel)
On the VU Blackboard an instructive video can be found

Since my work demands explaining the process to our students, I was very happy to learn from a very bright student - Aart Bijkerk - that things can be made considerably easier.
Using the Excel formula
=Tekst(cell number;"dd-mm-jjjj") (Dutch)
or =Text(cell number;"dd-mm-yyyy") (English)
the dates are ready made for the request table and can be copied into Request table without copying and saving as TXT.

Important:
!! Copy the dates and PASTE (right click) paste special (values) into the Request Table menu !!

Data Destination is next

See also: Datastream Event Studies: calculating destination cells in Request Table (link)

Why rebasing?

2011-05-26T06:27:00.000-07:00

Rebasing is a quite important ‘technique’ to analyze and display stock price histories. To understand the importance of rebasing, let’s have a look at the chart below which shows the price (Pi, price index in U$) history of Aex compared to Nasdaq Comp.
These two companies’ prices are moving at a totally different price range (see table).
A rebase chart shows not an absolute price change but it displays at each point of time how much percentage the price changed from a selected date in the past. (called rebase date). In this way we can compare the performance of companies at a different price level. Actually rebasing is a very simple index calculation:

Calculated:
Rebased Price at time t RPt
P = price at day t
P0 = price at rebase date
In formula:
RPt = P / P0 x 100

In Datastream I used the Expression picker and searched for "rebase" resulting the following formula:
REB#(X,Time) (rebase last value = 100); Datastream code (which appears in the menu) 025E
X = in this case the index, Time is latest quote)

Resulting chart:

In figures, rebased and not rebased:

Finding Expressions in Datastream (2)

2011-05-26T05:26:00.000-07:00

Datastream offers a wide range of ready made expressions and functions, which can save considerable time compared to doing your own calculations. Think of (standard)Deviation, Moving average, Cumulative sum etc.

Charting Menu

This a a "dirty, but easy" method of finding expressions and functions in Datastream.

Open naviator

In Criteria selection press link Charting

The charting screen opens

If you want to search for items and data, the arrow buttons open new menus.
(examples)

The principles are similar to the regular search screens.

In this post I'll focus on this button:

Mouse over results in the following menu:

I am particularly interested in Use Stored Expression as Function, but I have also highlighted Standard deviation and rebase( later)

After pressing the option the follwoing (large) menu appears:

Note ratios and regression, risk and volatility

-- > For instance, a couple of specific ratios can be found through pressing them. But the topbar also provides a search option. *)

*) Moving average

As you can see searching fro expressions like betas and such with this menu is not complicated.

All presented expressions and formulas can be copied and used in another Datastream menu.

Excel Datastream Menu (Expression picker)

From the Datastream menu press:

Looking up in the Extranet or on Blackboard VU:

Endusers, and students VU, can enroll theemselves to the extranet of Thomson Financial to access many expressions and functions.

Extranet < click for explanation how to enroll

Popular functions and expressions < click to open

Also look up info in these posts:

On financial databases

Equities and Betas (Financial databases and research)

Abnormal return formula (Financial databases and research)

Simple functions 2 (Financial databases and research)

Simple functions 1

A couple of terms

Volatility

Volatility refers to the amount of uncertainty or risk about the size of changes in a security's value. High volatility means that the price of the security can change dramatically over a short time period in either direction.

Measuring:

A statistical measure of the dispersion of returns for a given security or market index. Volatility can either be measured by using the standard deviation or variance between returns from that same security or market index. Commonly, the higher the volatility, the riskier the security.

Historical Volatility

The realized volatility of a financial instrument over a given time period. Generally, this measure is calculated by determining the average deviation from the average price of a financial instrument in the given time period. Standard deviation is the most common but not the only way to calculate historical volatility.

Implied volatility

In addition to known factors such as market price, interest rate, expiration date, and strike price, implied volatility is used in calculating an option's premium. IV can be derived from a model such as the Black-Scholes Model.

The estimated volatility of a security's price. In general, implied volatility increases when the market is bearish and decreases when the market is bullish. This is due to the common belief that bearish markets are more risky than bullish markets.

Bearish

Believing that a particular security, a sector, or the overall market is about to fall.

Bullish

Believing that a particular security, a sector, or the overall market is about to rise.

(Sources Investopedia and Investorwords.com)

Resuming a saved search in SDC

2011-03-24T06:54:00.000-07:00

Nothing as frustrating as having to do your searches all over again.
Continuing your search in SDC has got its own complications.
In this post I'll show you how to deal with that.

1. Starting point of this item is the end stage of any particular search (execute if desired)

2. TAB top of the screen "Report" > select New Custom
(where you can add many extra data not affecting your search)

3. The next screen shows many selectable options

4. Tip: it's always a good idea to include company identifiers in your outputfile, which can come usefull when you have to continue in another datababase and you don't want to have to create your work set all over again. (SEDOL, ISIN, CUSIP and company ticker)
Downloadable info on company identifiers and the databases the University Library VU offers can be found here: click this link and Mark Bruyneel's blog (click link)

5. Click 2x OK, save the report
!! Decision moment: make up a (sub)directory (e.g. on your USB stick) or use the default folder.
6. Execute the report

7. Save (button) the report as a textfile (.txt) or Excel (.xls) in the same (sub)directory

8. Select TAB Session (top left of the screen) > select "Save as" and select the same (sub)directory

Tip: give similar names to all output files, sessions and reports to prevent mix-ups

Continuing your search

!! Important: The saved session is what you need !!
10 Start de the database you were working on

11. Click TAB Session

12. Mouse over open existing

13. Click Custom

14. Your saved session opens.
Note that all former hits are absent. Pressing button Execute (top of screen) will fix that.

15. You can continue your search

Datastream - company accounts database

2011-03-10T06:38:00.000-08:00

The Datastream menu shows three large databases: static request, time series and company accounts.
The latter enables the user to select particular parts from the balance sheet, thus saving time selecting data types from the data type menu.

This item highlights those very data types and so illustrates the specific parts of the balance sheet. Unfortunately this blog does not permit adding Excell documents.

BALANCE SHEET INCOME STATEMENT

WC01001 NET SALES OR REVENUES
WC01051 COST OF GOODS SOLD (EXCL DEPRECIATION)
WC01151 DEPRECIATION, DEPLETION AND AMORTIZATION

WC01100 GROSS INCOME

WC01101 SELLING, GENERAL & ADMINISTRATIVE EXPENSES

WC01249 OPERATING EXPENSES - TOTAL

WC01250 OPERATING INCOME
WC01253 EXTRAORDINARY CREDIT - PRETAX
WC01254 EXTRAORDINARY CHARGE - PRETAX
WC01266 NON-OPERATING INTEREST INCOME
WC18191 EARNINGS BEFORE INTEREST AND TAXES (EBIT)
WC01251 INTEREST EXPENSE ON DEBT
WC01255 INTEREST CAPITALIZED
WC01401 PRETAX INCOME
WC01451 INCOME TAXES
WC01501 MINORITY INTEREST
WC01551 NET INCOME BEFORE EXTRA ITEMS/PREFERRED DIVIDENDS
WC01601 EXTRA ITEMS & GAIN/LOSS SALE OF ASSETS
WC01651 NET INCOME BEFORE PREFERRED DIVIDENDS
WC01701 PREFERRED DIVIDEND REQUIREMENTS
WC01751 NET INCOME AVAILABLE TO COMMON
WC01001F NET SALES OR REVENUES
WC01266F NON-OPERATING INTEREST INCOME

DATA YPE BALANCE SHEET ASSETS

WC05350 DATE OF FISCAL YEAR END
WC02001 CASH & SHORT TERM INVESTMENTS
WC02003 CASH
WC02051 RECEIVABLES(NET)
WC02101 INVENTORIES - TOTAL
WC02201 CURRENT ASSETS - TOTAL
WC02250 OTHER INVESTMENTS
WC02501 PROPERTY, PLANT AND EQUIPMENT - NET
WC02649 TOTAL INTANGIBLE OTHER ASSETS - NET
WC02999 TOTAL ASSETS
WC02250F OTHER INVESTMENTS
WC02999F TOTAL ASSETS
WC01266 NON-OPERATING INTEREST INCOME
WC01251 INTEREST EXPENSE ON DEBT
WC01266 NON-OPERATING INTEREST INCOME
WC01255 INTEREST CAPITALIZED
WC01201 RESEARCH & DEVELOPMENT
WC01084 SALARIES AND BENEFITS EXPENSES
WC01253 EXTRAORDINARY CREDIT - PRETAX
WC01254 EXTRAORDINARY CHARGE - PRETAX
WC01401 PRETAX INCOME
WC01451 INCOME TAXES
WC01401 PRETAX INCOME
WC01451 INCOME TAXES
WC01501 MINORITY INTEREST
WC01601 EXTRA ITEMS & GAIN/LOSS SALE OF ASSETS
WC01701 PREFERRED DIVIDEND REQUIREMENTS
WC01551 NET INCOME BEFORE EXTRA ITEMS/PREFERRED DIVIDENDS
WC01701 PREFERRED DIVIDEND REQUIREMENTS
WC18192 DIVIDENDS PROVIDED FOR OR PAID - COMMON
WC18191 EARNINGS BEFORE INTEREST AND TAXES (EBIT)
WC18198 EBIT & DEPRECIATION
WC01706 NET INCOME USED TO CALCULATE BASIC EARNINGS PER SHARE
WC01705 NET INCOME USED TO CALCULATE DILUTED EARNINGS PER SHARE
WC01751 NET INCOME AVAILABLE TO COMMON
WC07011 EMPLOYEES
WC01001F NET SALES OR REVENUES
WC01266F NON-OPERATING INTEREST INCOME
WC01266F NON-OPERATING INTEREST INCOME
WC07011F EMPLOYEES

DATA TYPE BALANCE SHEET LIABILITIES

WC03040 ACCOUNTS PAYABLE
WC03051 SHORT TERM DEBT & CURRENT PORTION OF LONG TERM DEBT
WC03101 CURRENT LIABILITIES - TOTAL
WC03251 LONG TERM DEBT
WC03263 DEFERRED TAXES
WC03351 TOTAL LIABILITIES
WC03426 MINORITY INTEREST
WC03451 PREFERRED STOCK
WC03501 COMMON EQUITY
WC03480 COMMON STOCK
WC03999 TOTAL LIABILITIES & SHAREHOLDERS' EQUITY
WC03263F DEFERRED TAXES
WC03480F COMMON STOCK
WC03101 CURRENT LIABILITIES - TOTAL
WC03151 WORKING CAPITAL
WC03251 LONG TERM DEBT
WC03255 TOTAL DEBT
WC03255 TOTAL DEBT
WC02001 CASH & SHORT TERM INVESTMENTS
WC03263 DEFERRED TAXES
WC03426 MINORITY INTEREST
WC03480 COMMON STOCK
WC03501 COMMON EQUITY
WC03480 COMMON STOCK
WC03501 COMMON EQUITY
WC03451 PREFERRED STOCK
WC03998 TOTAL CAPITAL
WC02999F TOTAL ASSETS
WC03263F DEFERRED TAXES
WC03480F COMMON STOCK
WC03480F COMMON STOCK

DATA TYPE CASH FLOW KEY ITEMS

WC06026 NATION
WC05350 DATE OF FISCAL YEAR END
WC04201 FUNDS FROM OPERATIONS
WC04860 NET CASH FLOW - OPERATING ACTIVITIES
WC04050 AMORTIZATION OF INTANGIBLE ASSETS
WC04601 CAPITAL EXPENDITURES (ADDITIONS TO FIXED ASSETS)
WC04870 NET CASH FLOW - INVESTING
WC05376 COMMON DIVIDENDS (CASH)
WC04551 CASH DIVIDENDS PAID - TOTAL
WC04251 NET PROCEEDS FROM SALE/ISSUE OF COMMON & PREFERRED
WC04890 NET CASH FLOW - FINANCING
WC04890 NET CASH FLOW - FINANCING
WC04870 NET CASH FLOW - INVESTING
WC04860 NET CASH FLOW - OPERATING ACTIVITIES
WC04851 INCREASE/DECREASE IN CASH AND SHORT TERM INVESTMENTS

DATA TYPE KEY ACCOUNTS RATIOS

WC06026 NATION
WC05350 DATE OF FISCAL YEAR END
WC08301 RETURN ON EQUITY - TOTAL (%)
WC08376 RETURN ON INVESTED CAPITAL
WC08316 OPERATING PROFIT MARGIN
WC08321 PRETAX MARGIN
WC08366 NET MARGIN
WC08311 CASH FLOW/SALES
WC01401 PRETAX INCOME
WC01251 INTEREST EXPENSE ON DEBT
WC08126 INVENTORIES - DAYS HELD
WC08131 ACCOUNTS RECEIVABLES DAYS
WC03040 ACCOUNTS PAYABLE
WC01001 NET SALES OR REVENUES
WC01250 OPERATING INCOME
WC01151 DEPRECIATION, DEPLETION AND AMORTIZATION
WC08221 TOTAL DEBT % TOTAL CAPITAL & SHORT TERM DEBT
WC08106 CURRENT RATIO
WC08101 QUICK RATIO
WC08231 TOTAL DEBT % COMMON EQUITY
WC01001F NET SALES OR REVENUES

The list is based on output from Microsoft Corp.
A Dutch company like Koninklijke Ahold provides significantly less data types.

Ratios, values and other instruments from the balance sheet - Compustat

2011-03-03T06:29:00.000-08:00

It is interesting to investigate which balance sheet data can be retrieved from the databases the library offers. Sometimes the data are ready for download, sometimes they have to be calculated, and sometimes they are absent altogether. This post aims at offering an oversight of commonly used balance sheet figures and how they can be found in database Compustat. (North America)*

*) I have made an attempt to find the very same balance sheet items as I used for Datastream, thus making both database comparible.

Compustat Global: it is possible that Golbal does not provide certain data items. I will mention it, if this the case. As for currencies: you can check the currency in which Gobal data items are expressed by checking CURCD (Curency code); the native currency (CURNCD) may differ but will be recalculated to CURCD.
Looking up data items in Compustat can be tedious when you have to scroll along the long lists of data items.
Using the CTRL-F key combination makes it possible to search for items "as you type"

Compustat Banks: seems to have another set of data items altogether.

1. Assets
Assets = liabilities + shareholders' equity

Total Assets = non-current assets + current assets
Data items
Total assets = AT = Assets - Total (CTRL-F: assets - total)
TOTAL ASSETS represent the sum of total current assets, long term receivables, investment in unconsolidated subsidiaries, other investments, net property plant and equipment and other assets.

Current assets total = ACT = Current assets - total
CURRENT ASSETS - TOTAL represents cash and other assets that are reasonably expected to be realized in cash, sold or consumed within one year or one operating cycle.

Generally, it is the sum of cash and equivalents, receivables, inventories, prepaid expenses and other current assets.
For non-U.S. corporations, long term receivables are excluded from current assets even though included in net receivables.

Long term (non-current assets) > not in Compustat
Total Assets = non-current assets + current assets > AT - ACT = Long term assets

2. Liabilities

Total libalities = Current liabilities (LCT = Current Liabilities - Total) + long term liabilities = LT (liabilities - total). Total liabilities LT = Current liabilities total LCT + (Long term liabilties)
Total liabilities: current liabilities plus long-term debt plus other noncurrent liabilities, including deferred taxes and investment tax credit.
LCT = liabilities due within one year, including the current portion of long-term debt
Long term liabilities have to be calculated: LT - LCT = Long term liabilities

Short term liabilities: accounts payable (APC = Accounts Payable/Creditors - Customer > does not provide data), short term debt (DLCFS = Short Term Debt Total - FS > does not provide data)
Long term liabilities are long term debt (DLTT = Long-Term Debt - Total), deferred taxes (TXDB = deferred taxes, balance sheet > zero or absent, maybe depends on company) and capital leases (in compustat there are CLD2 (due in 2nd yr.; CLD3; CLD4 and CLD5; all capitalized leases > providing data depends on company)
Not in Global: DLCFS

3. Shareholders'equity

Assets - liabilities = shareholders'equity (book value of equity; BKVLPS (book value per share) )
AT - LT = shareholders' equity (BKVLPS)
Common Equity ? Liquidation Value (CEQL) divided by Common Shares Outstanding (CSHO).

CEQL = Common Equity - Liquidation Value
CEQL / CSHO = BKVLPS

4. Market value

Instead of Book value of equity, Market Value ( MKVALT) is used.
Market Value MKVALT (market value - total fiscal) = share price (*) x number of shares (CSHO - common shares outstanding) (market capitalisation)
*)
PRCC_C = Price Close - Annual - Calendar
PRCC_F = Price Close - Annual - Fiscal
Not in Global: any of these

5. Liquidation of a company

Market to book ratio
Market to book ratio = Market value of equity (MV) : Book value of equity =
MKVALT / BKVLPS = MKVALT / (AT - LT)

Debt equity ratio
DLC (DLC = Debt in Current Liabilities - Total)
DLC represents the total amount of short-term notes and the current portion of long-term debt (debt due in one year).

Total debt / total (shareholder) equity
DLC / BKVLPS
represents all interest bearing and capitalized lease obligations. It is the sum of long and short term debt. (last no data)

Total shareholder equity
represents the sum of Preferred Stock and Common Shareholders Equity.
This item is available in the annual time series and the quarterly, semi-annual and trimester interim time series. It is only available at the company level.
AT - LT = shareholders' equity (BKVLPS)

CEQL / CSHO = BKVLPS
. Total equity
Total equity = preferred stock (PSTKC) + common equity (CSHO)
CEQ = Common/Ordinary Equity - Total
item represents the common shareholders' interest in the company.
This item is a component of Shareholders' Equity Total (SEQ).
SEQ = This item represents the common and preferred shareholders' interest in the company.
PSTKC = Preferred Stock - Convertible
This item represents the carrying value of outstanding preferred stock which is reported as being convertible

Enterprise values
Market Capitalization at fiscal year end date + Preferred Stock + Minority Interest + Total Debt minus cash. Cash represents Cash & Due from Banks for Banks, Cash for Insurance Companies and Cash & Short Term Investments for all other industries.
(Market value + debt) - cash (MKVALT + DLC ) - CH
CH = Cash
CASH represents money available for use in the normal operations of the company. It is the most liquid of all of the company's assets.

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

Earning per share EPS
Net income / shares outstanding
Net income used for EPS
Net Income = NI
(common) Shares outstanding CSHO
EPS = NI / CSHO

Profitability ratio
Gross profit / sales
GP UGI / SALE
Return on equity ROE
Net income / book value on equity
NI / MKVALT = NI / (PRCC_C x CSHO)
ROE = (Net income/ sales) x (sales/ total assets) x (total assets/ total equity)
net profit margin assets turnover equity multiplier
-------------------------------------------

Please comment if there are omissions or if I placed wrong information

Ratios, values and other instruments from the balance sheet - Datastream

2011-02-17T06:57:00.000-08:00

It is interesting to investigate which balance sheet data can be retrieved from the databases the library offers. Sometimes the data are ready for download, sometimes they have to be calculated, and sometimes they are absent altogether. This post aims at offering an oversight of commonly used balance sheet figures and how they can be found in database Datastream.

The Balance Sheet - Datastream

1. Assets
Assets = liabilities + shareholders' equity

Total Assets = non-current assets + current assets
Datastream: datatypes
Total assets = DWTA
TOTAL ASSETS represent the sum of total current assets, long term receivables, investment in unconsolidated subsidiaries, other investments, net property plant and equipment and other assets.

Current assets total = WC02201
CURRENT ASSETS - TOTAL represents cash and other assets that are reasonably expected to be realized in cash, sold or consumed within one year or one operating cycle.

Generally, it is the sum of cash and equivalents, receivables, inventories, prepaid expenses and other current assets.
For non-U.S. corporations, long term receivables are excluded from current assets even though included in net receivables.
Long term (non-current assets)
DWTA - WC02201 = Long term assets

2. Liabilities
Datastream datatypes:
Total libalities = Current liabilities + long term liabilities
Short term liabilities: accounts payable (WC03040), short term debt (WC03051)
Long term liabilities are long term debt (WC03251), deferred taxes (WC03263) and capital leases (WC18381 < VU not licensed)
Total liabilities: WC03351 = Current liabilities total: WC03101 + Long term liabilties
Total liabilities & shareholders' equity : WC03999

3. Shareholders'equity
Assets - (total) liabilities = shareholders'equity (book value of equity)
DWTA - WC03351 = shareholders' equity (WC03995)

4. Market value
Instead of Book value of equity, Market Value ( MV) is used.
Market Value MV = share price (P) x number of shares (NOSH) (market capitalisation)

Liquidation of a company

Market to book ratio
Market to book ratio = Market value of equity (MV) / Book value of equity =
MV / (DWTA - WC03351)

Debt equity ratio
Total debt / total (shareholder) equity
WC03255 / WC03995
Total shareholder equity WC03995

Total debt WC03255
represents all interest bearing and capitalized lease obligations. It is the sum of long and short term debt.
Total shareholder equity
represents the sum of Preferred Stock and Common Shareholders Equity.
This item is available in the annual time series and the quarterly, semi-annual and trimester interim time series. It is only available at the company level.

Enterprise values WC18100 < ready available datatype
Market Capitalization at fiscal year end date + Preferred Stock + Minority Interest + Total Debt minus cash. Cash represents Cash & Due from Banks for Banks, Cash for Insurance Companies and Cash & Short Term Investments for all other industries.
(Market value + debt) - cash
Cash: WC02003
CASH represents money available for use in the normal operations of the company. It is the most liquid of all of the company's assets.
(MV + WC03255) - WC02003

Gross profit margin
Gross Income / Net Sales or Revenues * 100 = WC01100 / WC-1001 x 100

Gross income: WC01100
the difference between sales or revenues and cost of goods sold and depreciation.
Net sales or revenues: WC01001
gross sales and other operating revenue less discounts, returns and allowances.

Earning per share EPS WC05201
Net income / shares outstanding = WC01751 / WC05301
Net income used for EPS WC01751
Net Income Available To Common
Shares outstanding WC05301
the number of shares outstanding at the company's year end. It is the difference between issued shares and treasury shares.

Profitability ratio
Gross profit / sales

Return on equity ROE DWRE
Net income / book value on equity
WC01751 / MV

ROE = (Net income/ sales) x (sales/ total assets) x (total assets/ total equity)
             | net profit margin        assets turnover |                equity multiplier
             -------------------------------------------
            (WC01751/WC01001) x (WC0100/DWTA) x DWTA/(WC03451 + WC03501)

total equity = preferred stock (WC03451) + common equity (WC03501) = WC03451 + WC03501

*) datatypes used in this item all do provide Data within our FDatastream license.

Other ratios from the balance sheet
WC01001 Net Sales or revenues
WC01250 Operating income (sales - total operating expenses [WC01249])
WC01401 Pretax income
WC08101 Quick ratio (Cash + equivalents [WC02001] + receivables (net) [WC02051] / current liabilities total
WC08106 Current ratio (current assets total [WC02201] / current liabilities [WC03101] )
WC08221 Total debt % (total capital & short term debt)
WC08301 Return on equity - total %
WC08311 Cash flow / sales
WC08316 Operating profit margin
WC08321 Pretax margin (pretax income / net sales or revenues x 100)
WC08366 Net margin (Net Income before Preferred Dividends [WC01651] / Net Sales or Revenues [WC01001] x 100)
WC08376 Return on invested capital

Understanding Ireland's Debt Crisis (movie)

2011-01-25T02:56:00.000-08:00

Only available to Datastream Users with access to the Extranet.

Understandig Ireland's debt crisis < click to view

Coping in difficult times - Euro countries in distress

2010-12-02T06:57:00.000-08:00

Introduction:

In finance, a bond is a debt security, in which the authorized (this case government) owes the holders a debt and, depending on the terms of the bond, is obliged to pay interest (the coupon) and/or to repay the principal at a later date, termed maturity. A bond is a formal contract to repay borrowed money with interest at fixed intervals.

So, a bond acts like a loan: the issuer is the borrower (debtor), the holder is the lender (creditor), and the coupon is the interest. Coupon interest is calculated like thus: interest rate / stock value x 100%

Bondholders have a creditor stake in the company, this differs from stock holders, who own a part of the company.
Originally, a bond was an actual sheet of paper, consisting of two parts: the coupon, and the scrip. The scrip is the IOU.
Bonds are bought and traded mainly by institutions like central banks, sovereign wealth funds, pension funds, insurance companies and banks. Bonds are used as an alternative to stocks, which are more volatile than bonds (making the latter safer). Stocks yield more than bonds, so when stock trade falls, bonds tend to rise.
There are 2 parts to the bond market: Price and yield. When the Price to buy a share of a bond goes UP, it's yield goes DOWN. (When it's more expensive to buy a share of a bond on the market, sales slow, and the yield goes DOWN.) When the price goes low, yield goes up.
When the open stock market goes down, and there is more supply than demand, the money goes to the paces where there is the most opportunity for stable return, which is the bond market.
(For an easy to read explanation see Mortgage Times blog)

Credit crunch and the EuroWe can see this happing since the start of the credit crunch, when governments started trading bonds.
But the instrument as a safe bet is only partially so.
Fixed rate bonds are subject to interest rate risk meaning that their market prices will decrease in value when the generally prevailing interest rates rise. Since the payments are fixed, a decrease in the market price of the bond means an increase in its yield. Bond prices can become volatile depending on the credit rating of the issuer - for instance if the credit rating agencies like Standard and Poor's or Moody's upgrade or downgrade the credit rating of the issuer (see also: country rating )
A further downgrade could push borrowing costs higher and make it more difficult for governments to meet their debt repayments without seeking help.

This is happening to countries Ireland and Portugal, lately (2nd half 2010), making it very hard for those countries to issues bonds, since those get a very high 'Redemption Yield' ( the amount of money paid for the stock (price including accrued interest and expenses) and relate this to the future dividend and capital payments, either or both of which may be paid net of tax.)

An example from Datastream

A selection of Bonds (Portugal, redemption yield)

But the Benchmark works with 10y Government Bonds, and those are listed under Interest Rates.
Search using the criteria: name Bond (and market: Portugal) > Portugal Benchmark Bond 10 YR (datastream calculated) > it's only one.
Due to its duration and its widespread usage, the 10-year Treasury note is often used as a benchmark for government bonds overall and is considered a financial benchmark for future economic performance on a larger scale. US treasury started using 10 year bonds. (the rest followed suit?)

To get all benchmark bonds use the text benchmark instead of bond (no market)
Now you can compare between countries using (all 10y)
E.g. Germany, Netherlands, Portugal and Ireland.

More information about this topic can be found at Mark Buyneel's Blog

An overview of databases in University Library VU offering ratings data: click here

Poison Pills

2010-10-14T07:05:00.000-07:00

Poison Pill: a tactic used by companies that are threatened by a hostile take over that ensures any take over will result in events that substantially reduce the value of the company. I.E. selling a valuable assets to a friendly company / bank or the issue of securities with a conversion option enabling buyer's to purchase at reduced price. See also porcupine provisions* and staggered directorships**.

Also, even if shareholders actually support the take over, the board of directors could decide to follow a poison pill strategy (just say no defense). In current days, with increased hostile activity, it can be expected that companies will continue just say no defenses, even in spite of their own shareholders, and following law suits, between suitors and target company. (see also: "How courts see the Just say no defense, Investment Dealer's Digest, 2010, vol. 076, iss, 10, pg. 22.)

An extreme version of poison pills are the so-called Defensive Scorched Earth policies.

A reaction to a takeover attempt that involves liquidating valuable assets and assuming liabilities in an effort to make the proposed takeover unattractive to the acquiring company. Read more: http://www.investorwords.com/4410/scorched_earth_policy.html#ixzz12KzZckeY
Any measures are valid to discourage take over, e.g. getting a loan at an exhorbitant interest rate.
Method SDC: Mergers and Acquisitions US + NON-US ; dates ; Search ; All items : search word: "scorched" ; select ; close ; Execute ; Custom Report ; add any report items you wish like name, state, status, synopsis etc.

Poison pills data are hard to get.
Our library does not have a specific database that does.

SDC has got some poison pills data types in the Mergers and Acquisitions section, but most won't provide data (license).
A usefull way of retrieving data on poison pills is a work around starting off with SDC Mergers and Acquisitions and ending in Lexis/ Nexis, which one database that does provide poison pill data.
(this part is pending)
Path in SDC:
Mergers and Acquisitions > set time scale > choose TAB 'all' > search word poison or (scorched earth) > close > execute > Tab top bar Custom Report > Set preferences (see figure)

For a basic introduction to SDC, click here (M and A search)
All SDC contributions on this blog can be found here

*) Porcupine provisions. (shark repellents)

Provisions made by companies to discourage take over bids.

**) Staggered directorships. To prevent hostile take overs. If the company makes clear that directors can't be removed from office as a result of the take over, without due cause, a bidder can't gain control of the board for some time, even with a controlling interest in shares capital.

Country Risk, country rating, Ireland Rating (December 17, 2010-update)

2010-10-05T03:05:00.000-07:00

LONDON (October 5th, 2010) —Moody's Investors Service said Tuesday it may cut Ireland's debt rating again, citing the increased cost to the government of repairing the stricken banking system, weak economic growth and rising borrowing costs.

A further downgrade could push Ireland's borrowing costs higher and make it more difficult for the government to meet its debt repayments without seeking help from the European Union's European Financial Stability Fund.

(Click for whole article)
Moody's downgrades Ireland rating (december 17th 2010)

Dutch article:
PARIJS - Moody's verlaagt de beoordeling van de kredietwaardigheid van Ierland, zo maakte de kredietbeoordelaar vrijdag bekend.
Aanleiding zijn de toenemende onzekerheden over de economie en de begroting van het door schulden geplaagde land.
De beoordeling gaat vijf niveaus omlaag, van Aa2 naar Baa1. De verlaging is volgens Moody's gebaseerd op het risico dat de financiële kracht van de overheid verder kan afnemen als de economische groei tegenvalt of als de kosten van het stabiliseren van het banksysteem hoger zijn dan voorspeld.

The information also applies to some extent to Country ratings.

1. Country Risk
Country risk refers to the risk of investing in a country, dependent on changes in the business environment that may adversely affect operating profits or the value of assets in a specific country.

For example, financial factors such as currency controls, devaluation or regulatory changes, or stability factors such as mass riots, civil war and other potential events contribute to companies' operational risks. This term is also sometimes referred to as political risk, however country risk is a more general term, which generally only refers to risks affecting all companies operating within a particular country.

Political RiskPolitical risk analysis providers and credit rating agencies use different methodologies to assess and rate countries' comparative risk exposure. Credit rating agencies tend to use quantitative econometric models and focus on financial analysis, whereas political risk providers tend to use qualitative methods, focusing on political analysis. However, there is no consensus on methodology in assessing credit and political risks.

(source: wikipedia)

2. Country Risk

A.M. Best defines country risk as the risk that country-specific factors could adversely affect an insurer's ability to meet its financial obligations. Country risk is evaluated and factored into all A.M. Best ratings. As part of evaluating country risk, A.M. Best identifies the various factors within a country that may directly or indirectly affect an insurance company

Countries are placed into one of five tiers, ranging from Country Risk Tier 1 (CRT-1), denoting a stable environment with the least amount of risk, to Country Risk Tier 5 (CRT-5) for countries that pose the most risk and, therefore, the greatest challenge to an insurer's financial stability, strength and performance.

Source: Ratings & Analysis Center < -- click for site

The site contains free downloadable! Country reports on their financial (political) risk.

3.
Sovereign Risk (Wiki)
Sovereign risk is the risk of a government becoming unwilling or unable to meet its loan obligations, or reneging on loans it guarantees. The existence of sovereign risk means that creditors should take a two-stage decision process when deciding to lend to a firm based in a foreign country. Firstly one should consider the sovereign risk quality of the country and then consider the firm's credit quality.

Five macroeconomic variables that affect the probability of sovereign debt rescheduling are:

Debt service ratio
Import ratio
Investment ratio
Variance of export revenue
Domestic money supply growth

The probability of rescheduling is an increasing function of debt service ratio, import ratio, variance of export revenue and domestic money supply growth. Frenkel, Karmann and Scholtens also argue that the likelihood of rescheduling is a decreasing function of investment ratio due to future economic productivity gains. Saunders argues that rescheduling can become more likely if the investment ratio rises as the foreign country could become less dependent on its external creditors and so be less concerned about receiving credit from these countries/investors.

Global Financial Stability Report (IMF) (Last Updated: December 01, 2010) http://www.imf.org/external/data.htm
http://www.imf.org/external/pubs/ft/gfsr/2010/02/pdf/text.pdf (the report)

Monthly bulletin of statistics (open accessible)
Government bonds - monthly 2007 - 2010, selected countries Belgium, France, Germany, Ireland, Iceland, Italy, Netherlands and Spain.
The rates are an indication of financial stability of a country. In the case of Ireland you can expect a rise in rate.
2010AUG 5.30
2010JUL 5.32
2010JUN 5.31
2010MAY 4.86
2010APR 4.76
2010MAR 4.54

Blog Items Databaser
Stock rating, debt rating and stock ranking : < click
Databases with Ratings data in our library < click

Standard and Poor's Souvereign ratings and T&C assessment < click

This site explains the S&P strategy in rating souvereigns and their transfer and convertibility (T&C). (most recent 23/Aug/2010) The information is copyrighted, so I will sustain with a link to their site.

More information on the subject can be found at Mark Bruyneel's blog click here
This blog will also explain how to find RISK data in Datastream.

Return, expected return and abnormal return

2010-07-22T04:23:00.000-07:00

In finance, an abnormal return is the difference between the expected return of a security and the actual return. Abnormal returns are sometimes triggered by "events." Events can include mergers, dividend announcements, company earning announcements, interest rate increases, lawsuits, etc. all which can contribute to an abnormal return. Events in finance can typically be classified as occurrences or information that has not already been priced into the market.

In stock market trading, abnormal returns are the differences between a single stock or portfolio's performance in regard to the average market performance over a set period of time.[1] Usually a broad index, such as the S&P 500 or a national index like the Nikkei 225, is used as a reference for the average market performance.
For example if a stock increased by 5% because of some news which affected the stock price, but the average market only increased by 3%, then the abnormal return was 2% (5% - 3% = 2%). If the market average performs better than the individual stock then the abnormal return will be negative.

Abnormal return = Actual return - normal return

Definition: Used in the context of stock returns; abnormal returns means the return to a portfolio in excess of the return to a market portfolio. Contrast excess returns which means something else. Note that abnormal returns can be negative.
Example: Suppose average market return to a stock was 10% for some calendar year, meaning stocks overall were 10% higher at the end of the year than at the beginning, and suppose that stock S had risen 12% in that period. Then stock S's abnormal return was 2%.

In contrast, excess returns are returns above the risk-free rate. What is used in the CAPM is the expected excess return instead of excess return itself. Expected excess return is the so called risk premium. The measure of these excess returns is expressed by the measure alpha.

Where can we find it and how do we find it?

In Datastream.
You'll have to calculate the abnormal return yourself.

Ri = Total Return (datatype RI) on Datastream is cumulative as it adds any changes on to the previous day's value. Return index – datatype (RI) A return index (RI) is available for individual equities and unit trusts. This shows a theoretical growth in value of a share holding over a specified period, assuming that dividends are re-invested to purchase additional units of an equity or unit trust at the closing price applicable on the ex-dividend date. For unit trusts, the closing bid price is used.For all countries except the USA and Canada detailed dividend payment data is only available on Datastream from 1988 onwards. Up to this time the RI is constructed using an annualised dividend yield.

Expected returnHow do you calculate the average of a probability distribution? As denoted by the above formula, simply take the probability of each possible return outcome and multiply it by the return outcome itself. For example, if you knew a given investment had a 50% chance of earning a 10% return, a 25% chance of earning 20% and a 25% chance of earning -10%, the expected return would be equal to 7.5%:
The forecast return on a stock, Return on Equity (ROE)
Abundant in Datastream. Noteworthy imho are Return on Equity (Datastream Worldscope DWRE andthe IBES section forecast for 12 month period:
filter on return on equity
12 Month Forward Return on Equity ROEF1FD12 (frequency yearly)

Relative changes in return can be preset in the datatypes rule or in the item rule:
Via formula PCH#(X, 1D) or PCH#(item,1D) *
it's possible to extract per stock the % change in return.
This can also be applied to weekly, monthly or yearly.
Example: Koninklijke Ahold: PCH#(H:AH,1D)

*) This formula also works for index Return Index: PCH#(mnemonic(RI),1M)
e.g. S&P 500: PCH#(S&PCOMP(RI),1M) (put this in the item rule)
When working with the constituent LS&PCOMP it'd be
PCH#(X(RI), 1M)

Cumulative abnormal return (CAR)
Cumulative abnormal return, or CAR, is the sum of all abnormal returns up to time . If no event occurs then CAR equals zero.

Market value:
International Valuation Standards defines market value as "the estimated amount for which a property should exchange on the date of valuation between a willing buyer and a willing seller in an arm’s-length transaction after proper marketing wherein the parties had each acted knowledgeably, prudently, and without compulsion.

The following table shows a Datastream output on a yearly search on H:AH (AHOLD) with datatypes RI and formulas PCH#(X,1D) = % change; Return of Equity ROE (DWRE) and PCH#(X(LI), 1D) = % change compared to LI (Local Market Index = AEX); plus the % change with 7 decimals and lastly the abnormal return. The latter was provided by the Thomson Financial helpdesk. REGR = regression, PCH = % change
As far as I understand you can calculate abnormal return G column yourself by using columns E and F (or column C, for that matter)

Mark's Blog provides more information and a formula
http://financialresearch.blog2blog.nl/Datastream/
For those that don't want to read the explanation, here is the formula:
REGR#(PCH#(X(LI),1D),PCH#(X*1.0000000,1D))

Disclaimer: I am still working out how this exactly works, and I cannot claim this to be correct. I am open for all suggestions/ corrections: r.vermunt@ubvu.vu.nl

Sources:
Wikipedia
http://en.wikipedia.org/wiki/Abnormal_return
Investopedia
http://www.investopedia.com/terms/a/abnormalreturn.asp

Zephyr: country coverage

2010-04-15T06:25:00.000-07:00

Zephyr does not cover all countries in the world:

http://www.delphion.com/promo/vendors/derwent/derwent_valadd#coverage

Alternative to Derwent: Patent Lens

2010-04-15T06:20:00.000-07:00

Patent Lens is a worldwide, open-access, free full-text patent informatics resource.

http://www.patentlens.net/

Online Manual Compustat - news update

2010-04-13T01:39:00.000-07:00

It looks like it that link to the online Manual Compustat has been removed from the New Wharton Interface.

How to get there, then?
Using the Old Wharton Interface.

Start the Database.
Either select Compustat Global or Compustat North America, open Fundamentals Annual / fundamentals quarterly, and choose link Data Manuals > Compustat Online Manual.

As soon as I can find the route to the on line manual from the new interface, I will inform you.

Datastream: request table using Excel

2010-03-16T03:36:00.000-07:00

In Datastream's Time Series menu it's only possible to generate one single Time Series.
Datastream features ' Request Table ' , to circumvent that and create multiple Time Series.
This blog explains with a simple example what to do.

Example workset:

(copy, CTRL-C)

Time Series menu:

After starting Request Table, paste the workset into the proper fields. It's is also possible to press the Navigator button and create a table from scratch.

Next: adjust all fields to the required preferences (like the time series)
Click the buttons above the columns to select.

Update Yes/NO
Request Type: Static (S), Time series (TS), Time series list (TSL), Company accounts (CAF)
Format: (output) things like headers, row titles etc.

Datatypes, functions, CA (for CAF)
Now, here is the trick: for each item it is possible to create its own time series
(included start date and end date)
Frequency: Daily, weekly, monthy, quarterly and yearly
Example: (note the different Time series)

Next it is time to set the Data Destination.
This demands some rather complicated actions.
Just follow the instructions.

First: creating a new Work sheet (the destination of your search)
Right Mouse click > insert new work sheet
(might differ per Excel version)

Now, press this button:

Another screen pops up:

Click the new work sheet TAB and click the first top cell

Press OK

Repeat untill all funds are done:

Finally, press this button:

All data is transported to the new work sheet.
Result:

Functions and expressions in Datastream and Extranet

2010-03-15T08:08:00.001-07:00

Datastream contains lots of functions and expressions to change datatypes to your own liking/preference. For those that can use the Datastream Extranet, it provides a vast list of possible functions/expressions.

Some general expressions:
- Trading days and non –trading days (e.g. New year’s day & bank holidays)
P en P#S, Weekends don’t apply
- Currency conversion (Datastream uses World Market Reuters series) Useful when comparing equities from different currency markets. (Exchange rate is Reuters)
X(P)~E of X(P)~U$
(ALT190= ¥, ALT156 = £, ALT159=ƒ)
E.g.: ING Groep sector: life insurance (NY, London & Amsterdam)
But you can also use the button in the Datastream menu
- Log-function
Two options: Log (logaritm) and LN (base value mathematic constant e).
E.g. data type RI (total return): LOG#(X(RI))
- Cumulative sum
CMA#(X(RI)) (Calendar Monthly Average)
CSUM#(CMA#(X(RI))) cumulative sum

By replacing the X for a mnemonic or or a DS code and use it in the series/lists field of the menu it is possible to run the function. But that can be a drag, since it has to be done for each single item (note all the brackets)
Tip: It saves loads of work to use the functions / expressions in the datatypes field and have them apply to your entire work set in one single go.

Datastream Extranet: (Datastream customers only)
Extranet provides in depth information on databases published by Thomson Financial.

Functions and Expressions in Extranet:
Select from the menu (Quicklinks) on the right hand side of the page under header
DOWNLOADS, DOCUMENTS AND TRAINING, and click the link Using Datastream Hints and tips
On the next page it is possible to select on subject:
Finding series in Datastream
Functions and expressions
Datastream charting
Datastream Advance for Office (AFO)
User created indices
Each of these subjects opens another page, with in the center of the page downloads and on the right hand side a menu called Useful links (often the same as the former page), but always with the link Functions and definitions. This opens a searchable database.
E.g; Cumulative sum:
CSUM#(Expression, Start Date)
Where Start Date defines the start date for the calculation. This must be earlier than, or the same as, the display period end date. Actual dates are typed in using either the standard DD/MM/YY format or the displacement date formats.
CSUM#(Expression)
If you do not specify a start date, the default is the start date on the input screen.

Constituent list in Compustat

2010-03-09T02:45:00.001-08:00

Translating exported worksets from Datastream into compustat can be tedious when it involves American companies, since it is not possible to upload ISIN lists in Compustat North America.
Fortunately Compustat contains it own consituent section.
In that section you can make retrieve (historic) compositions of e.g. S&P indexes.

Example S&P 500

Here are the necessary steps:

Select the desired identifier:

Look up the codes for S&P 500

Type in the name and memorise the identifier(s)

For the S&P 500 and S&P 1500 historical indexes the codes are:
S&P 500 (S&P 500 Comp-Ltd) codes: GVKEYX: 000003, TIC: I0003
S&P 1500 (S&P 1500 Super Composite) codes: GVKEYX: 031855, TIC: I0020

Now it is time to select variables desired,

Index information such as CONM (indexname) and TIC (ticker)
Company information like CONM (company name, mind the section), TIC (Ticker), CUSIP, SIC (Standard Industry Code) and NAICS.

Finally the output format.
It's a good idea to export the output as a TAB delimited TEXT file as it is ridded of all hidden coding, which might influence the presentation of the data when opening in a specific program (Excel, for instance).

Submit your query.
Check this page how to work with your output: click here

End result.

Also noteworthy: Mark B. has a nice contribution to this subject (click link )

Databases offering Debt/credit ratings

2010-02-12T05:23:00.000-08:00

The Vrije Universiteit gives access to several databases offering data on Debt ratings.

Compustat (North America, Ratings section)
SPSDRM -- S&P Subordinated Debt Rating
This item represents the issuer subordinated debt rating that has been assigned to a company by Standard & Poor's. (prior to 1.09.1998)
SPLTICRM -- S&P Domestic Long Term Issuer Credit Rating
The Standard & Poor's Issuer Credit Rating (ICR) is a current opinion of an issuer's overall creditworthiness, apart from its ability to repay individual obligations
SPSTICRM -- S&P Domestic Short Term Issuer Credit Rating
The Standard & Poor's Issuer Credit Rating (ICR) is a current opinion of an issuer's overall creditworthiness
SPCSRC -- S&P Quality Ranking - Current
This item is an appraisal of past performance of a stock's earnings and dividends and the stock's relative standing as of a company's current fiscal year-end.

Datastream:
Only Bonds and Convertibles, the latest rating of Moody's and of S&P.
ISPRT- Current rating of a bond from S&P
MRT- Moody’s rating for Bond
BSPF- S&P Current Foreign Credit Rating (Borrower Rating)
BSPL- S&P Current Local Credit Rating (Borrower Rating)
BSPHF - Historic rating

SDC Mergers and Acquisitions

Executed result
What to do: (example)

Criteria: date announced 2005 - 2008; target nation Netherlands, related debt ratings (search in all by typing).
After adding your criteria, go to top bar select dropdown menu reports and select CUSTOM report. A menu pops up where your own criteria can be added. Go to the All TAB and type in: debt rating.
Add those:

The output result is:

Note: Searches can result in large work sets, so it could be a good idea to 'cut up' yourt search into parts and add several extra limiters.

Important NB on content (31st March 2011): I have noticed that SDC 3.0 does not provide credit ratings in M&A.
SDC (link: explains how to do a basic tear sheet search)

SDC GNI (Global New Issues)

(Issued new stocks. Ratings may influence buyers in buying new stock)

Report Output with ratings: (use Report button in the Top Bar)

Stock ranking, credit/debt rating

Stock rating, debt rating and stock ranking

2010-02-11T06:47:00.000-08:00

Definition Stock ratingAn evaluation of a stock's expected performance and/or its risk level as judged by a rating agency such as Standard and Poor's. A stock rating will usually tell the investor how well a stock's market value relates to what analysts believe is a fair value for the stock, based on an objective evaluation of the company. The greater the amount by which the fair value exceeds the market value, the more highly recommended a buy the stock is. Conversely, if the market value of the stock exceeds the fair value of the stock, then analysts recommend that the stock be sold. Most stock rating systems give stocks one to five stars, with five being best. While some investors use a particular analyst's stock ratings as guidance, it is important to evaluate the criteria which they use to determine fair value, since the techniques they use are diverse and not all analysts are equally competent.

You could also interpret the rating as a ranking system in which the highest ranking means very credit worthy.

Debt rating
In investment, the bond credit rating assesses the credit worthiness of a corporation's debt issues. It is analogous to credit ratings for individuals and countries. The credit rating is a financial indicator to potential investors of debt securities such as bonds.
These are assigned by credit rating agencies such as Moody's, Standard & Poor's, and Fitch to have letter designations (such as AAA, B, CC) which represent the quality of a bond. Bond ratings below BBB/Baa are called junk bonds

Credit rating agencies
In short: credit rating agencies (CRAs) are companies specializing in investigating credit worthiness (abilty to pay back their loans) of companies and governments. In general concerning certain types of debt obligations like bonds and the debt instruments themselves. Practice is that companies use credit ratings to investigate whether investing in that company would be a good idea or not. But the last couple of years CRAs are primarily known for their ratings of governments and nations, think of Ireland, Greece, Italy and Spain.

In most cases, the issuers of securities are companies, special purpose entities, state and local governments, non-profit organizations, or national governments issuing debt-like securities (i.e., bonds) that can be traded on a secondary market.
Many people are unaware of what the actual debt ratings mean. The Moody's rating is based on statistical calculations of a company's likelihood of default. Most explanations give verbal descriptions for the ratings, such as Aaa = "Highest Rating Available" or Ba = "Low Grade", the first level of "Below Investment Grade" or the first level of "Junk Bonds". These descriptions seem useless without a way to determine what it all means in terms of the likelihood of default on the terms of repayment. To me, what is important is the chance that the company is not going to live up to its obligations, and that means the percentage of companies that default with a given rating. Moody's calculates this information, but it is hard to find a place that shows it.

Ratings by Moody's, Standard and Poors and Fitch

Rating examples:
(highest)

(lowest)

(source wikipedia)

Criticism on the rating system
Untill the 1970 the ratings were done entirely indepentdantly. But in the early 1970s, (S&P, Moody's, and Fitch) began to receive payment for their work by the securities issuers for whom they issue those ratings. Obviously the question arose on the impartiality of the three. One can image that securities issuing companies have an interest in good ratings, and the intention of companies shopping for good ratings, until they get one may be clear. In fact, it is not imaginative that getting ratings this way may have been one of the causes for the credit crunch, given the high ratings CDOs (collateralized debt obligations) and MBSs (mortgage backed securities) got. (High rating: buy, buy buy!)

Interview with John Kiff , Senior Financial Sector Expert Global Financial Stability Division in the Monetary and Capital Markets Department (IMF) on the debt/credit rating system. (Global Financial Stability Report)http://www.imf.org/external/mmedia/view.aspx?vid=619701770001

Sites:
Definition Wikipedia

Standard & Poors
Moody's

See also Databases offering debt/ credit ratings (hyperlink)
IMF Direct Blog (John Kiff) http://blog-imfdirect.imf.org/
. End the credit rating addiction < blog item

Preferred stocks in Datastream, Compustat and SDC

2010-02-11T03:45:00.000-08:00

Preferred stock, also called preferred shares, preference shares, or preferreds, is a special type of equity security that has chracteristics of both an equity and a debt instrument.
Preferreds are senior (i.e. higher ranking) to common stock, but are lower ranking than bonds.
Bonds > preferred stock > common stock
Preferred stocks usually carry no voting rights, but may carry priority over common stock in the payment of dividends and upon liquidation. Preferred stock may carry a dividend that is paid out prior to any dividends being paid to common stock holders. Preferred stock may have a feature to have them converted into common stock. Terms of the preferred stock are stated in a "Certificate of Designation".
Similar to bonds, preferred stocks are rated by the major credit rating companies. The rating for preferreds is generally lower since preferred dividends do not carry the same guarantees as interest payments from bonds and they are junior to all creditors.
Resumating:
The following features are usually associated with preferred stock
Preference to dividends.
Preference to assets in the event of liquidation.
Convertible into common stock.
Callable at the option of the corporation.
Nonvoting.

Preferred stock may or may not have a fixed liquidation value, or par value, associated with it. This represents the amount of capital that was contributed to the corporation when the shares were first issued

Preferred stock has a claim on liquidation proceeds of a stock corporation, equivalent to its par or liquidation value unless otherwise negotiated. This claim is senior to that of common stock, which has only a residual claim

Almost all preferred shares have a negotiated fixed dividend amount. The dividend is usually specified as a percentage of the par value or as a fixed amount.

Some preferred shares have special voting rights to approve certain extraordinary events (such as the issuance of new shares or the approval of the acquisition of the company) or to elect directors, but most preferred shares provide no voting rights associated with them. Some preferred shares only gain voting rights when the preferred dividends are in arrears for a
substantial time.

Types of preferred stocks:

Prior Preferred Stock – Many companies have different issues of preferred stock outstanding at the same time and one of them is usually designated to be the one with the highest priority. If the company has only enough money to meet the dividend schedule on one of the preferred issues, it makes the dividend payments on the prior preferred. Therefore, prior preferred have less credit risk than the other preferred stocks but it usually offers a lower yield than the others.

Preference Preferred Stock – Ranked behind the company's prior preferred stock (on a seniority basis), are the company's preference preferred issues. These issues receive preference over all other classes of the company's preferred except for the prior preferred. If the company issues more than one issue of preference preferred, then the various issues are ranked by their relative seniority. One issue is designated first preference, the next senior issue is the second and so on.

Convertible Preferred Stock – These are preferred issues that the holders can exchange for a predetermined number of the company's common stock. This exchange can occur at any time the investor chooses regardless of the current market price of the common stock. It is a one way deal so one cannot convert the common stock back to preferred stock.

Cumulative preferred stock – If the dividend is not paid, it will accumulate for future payment.

Exchangeable preferred stock – This type of preferred stock carries the option to be exchanged for some other security upon certain conditions.

Participating preferred stock – These preferred issues offer the holders the opportunity to receive extra dividends if the company achieves some predetermined financial goals. The investors who purchased these stocks receive their regular dividend regardless of how well or how poorly the company performs, assuming the company does well enough to make the annual dividend payments. If the company achieves predetermined sales, earnings or profitability goals, the investors receive an additional dividend.

Perpetual preferred stock – This type of preferred stock has no fixed date on which invested capital will be returned to the shareholder, although there will always be redemption privileges held by the corporation. Most preferred stock is issued without a set redemption date.

Putable preferred stock – These issues have a "put" privilege whereby the holder may, upon certain conditions, force the issuer to redeem shares.

Monthly income preferred stock – A combination of preferred stock and subordinated debt.

Non-cumulative preferred stock – Dividend for this type of preferred stock will not accumulate if it is unpaid.

Source: Wikipedia

Databases offering preferred stock:
Datastream
Equity Active (29)
<><>

COMPANY NAME	ISIN CODE	SEDOL CODE
MET.MGE.& SECS.CO.INCO.	US59219P6025	2844626
SOCA.ELEC.& GAS CO.	US8370048527	2414900
PREMIER BANCORP.INCO.	US74046J2087	2960544
VESTA INSURANCE GP.INCO.	US925391AC84
SUMMIT SECURITIES INCO.	US8662612097	2981738
MILLS CORP.	US6011482089	2020590
FANNIE MAE
FANNIE MAE	US3135868699	2165907
MILLS CORP.	US6011483079	2218500
CHS INC	US12542R2094	2332756
FANNIE MAE	US3135868517	2468189
INTERST.PWR.& LT.CO.	US4610708723	2681306
FIRST COASTAL BCSH.	US31969P2074	2432902
LORAL SPA.& COMMS.INCO.	BMG564621495	2599647
MIRANT CORP.
CBL & ASSOCS.PROPS.INCO.	US1248304073	2169954
CBL & ASSOCS.PROPS.INCO.	US1248305062	2346133
CINCINNATI BELL INCO.	US1718713043	2585141
CARRAMERICA REALTY CORP.	US1444187041	2667362
ROYCE VALUE TST.INCO.	US7809104026	2012326
KILROY REALTY CORP.	US49427F4054	2027869
FPL GP.CAP.TST.I	US30257V2079	2460475
STRATS TRUST AT&T CORP.	US86310P2074	B00FRG9
KEYSTONE PR.TST.CORP.	US4935964076	B010FL4
BEDFORD PR.INVRS.INCO.	US0764466084	B00XRB8
CAP.AUTV.	US1397333071	B013YC7
SAVANNAH ELEC.& PWR.CO.	US8047878025	B01C242
TARRAGON CORP.	US8762872021	B0168J1
SIMON PR.GP.INCO.	US8288068021	B02TS77
PLM EQU.GW.FD.	US6934131060	2535102
PONIARD PHARMS.INCO.	US7324492021	2582904
WESTPORT RES.CORP.	US9614182098	2276780
BEAR STEARNS COS.INCO.	US0739028761	2756583
FIRST BANC.	US3186722019	2757865
LASALLE RE HDG.LTD.	BMG5383Q1197	B01D6F4
SPANISH BCAST.SY.INCO.	US8464258586	2064628
MARCHEX INCO.	US56624R3066	B065Y17
HUNTSMAN CORP.	US4470112065	B066NM4
SPANISH BCAST.SY.INCO.	US8464258412	B05L353
DIGITAL REALTY TST.INCO.	US2538683010	B0FGLP4
CITIGROUP FUNDING INCO.	US17308C8745	B0LKXS7
LORAL SPA.& COMMS.INCO.	US5438854045	2112970
TOP 10 SPLIT TRUST	CA8905201171	B0T4MG2
SUPERTEL HOSPLTY.INCO.	US8685262037	B0W1VZ2
MEXICO EQ.& INC.FD.INCO.	US5928342048	B0THTC6
FREDDIE MAC
AMB PROPERTY CORP.
ZIONS BANCORPORATION	US98973A1043	B1L0NK6
GEN.ELEC.CAP.CORP.	US3696227178	2459321
NORAM CAPITAL HDG.INCO.	US4136483041
HYUNDAI MER.MAR.CO.LTD.	KR7011201001	B1GRRB1
ISHARES TRUST	US4642886877	B1FLNF0
ITO EN LIMITED	JP3143000101	B23PWV0
THORNBURG MORTGAGE INCO.
FANNIE MAE	US3135867865	B282QS1
FANNIE MAE	US3135867782	B287ZD4
FANNIE MAE	US3135867600	B29HJQ4
SY BANCORP INCO.	US7850592053	B3KQQF5
HC SECURITIES & INV.	EGS692D1P027	B3BM771
ALKAN HOLDING	EGS690T1P021	B3DQCS7
SPDR SERIES TRUST	US78464A2924	B4MTQL5

Datatypes: (worldscope) WC03451 is the only datatype that should give data

Compustat / Wharton
Compustat North America:
This database contains many more entrees into peferred stocks and data related to preferred stock. A shortlist follows below.

DCPSTK , Convertible Debt and Preferred Stock
This item represents the balance sheet amount of outstanding convertible debt and the carrying value of convertible preferred stock that is reported as being convertible into common stock. This item is not available for banks.DVPA, Preferred Dividends in Arrears
This item represents the dollar amount of preferred dividends on cumulative preferred stock which have not been paid and are considered to be in arrears. This item is not available for banks or utility companies.DVP -- Dividends - Preferred/Preference
ESOPR -- Preferred ESOP Obligation - Redeemable
ESOPT -- Preferred ESOP Obligation - Total

PSTK -- Preferred/Preference Stock (Capital) - Total
U.S. and Canadian GAAP Definition
This item represents the net number of preferred shares at year-end multiplied by the par or stated value per share as presented in the company's Balance Sheet.PSTKC -- Preferred Stock ConvertiblePSTKL -- Preferred Stock Liquidating ValuePSTKN -- Preferred/Preference Stock - Nonredeemable
PSTKR -- Preferred/Preference Stock - RedeemablePSTKRV -- Preferred Stock Redemption Value
UPMPF -- Premium on Preferred Stock
UPMPFS -- Premium on Preference StockUPMSUBP -- Premium on Subsidiary Preferred StockUPSTK -- Preferred Stock at Carrying ValueUPSTKC -- Preference Stock at Carrying ValueUPSTKSF -- Preferred/Preference Stock Sinking Fund Requi
PDVC -- Cash Dividends on Preferred/Preference Stock
This item represents the total amount of cash declared for preferred/preference stock shareholders.

SDCSection GNU (Globl New Issues, US companies)
Unfold option US en select Preferred Stock.

Next data is available in the search screen.

Set Lo Hi values as desired.
Execute and make a report, a tear sheet.

Result example:

In this case, US Public preferred stock, year 2008 and % yield 1 - 10, resuklts 1 hit.

Documentary DSB bank

2010-02-10T03:41:00.000-08:00

Date broadcasted: 07-02-2010
Language: Dutch

In a series of interviews with those closely inviolved this documentary tries to reconstruct the rise and fall of DSB bank, Dirk Scheringa's bank. Afairs Things were always stirring and rumbling in this bank. For years. So, the bigest bank bust in Dutch history didn't come unexpected. At least, not to the incrowd.
The Dutch national bank was (well) informed, but decided not to intervene.
New facts on a long lasting permissive policy.

Documentary click to view

VPRO Documentary: Tegenlicht: Quants: the alchemists of Wall Street

2010-02-10T03:16:00.000-08:00

Date broadcasted: 25-01-2010

Last year the world verged on the brink of the abyss, during a quants induced collapse of the financial markets. Quants are the mathmaticians and programmers in the machine room of our global financial system. The documentary speaks with the smartest 'kids' on Wall Street.
Inside stories how bad mortgage loans were cut into profitable financial derivatives.
How our financial markets became more and more dependant of models and algorythms, while boards of directors blissfully ignorant were lead by those whizkids.
Unnerving is how things were back to business as usual within a couple of months.

Documentary Quants click to view

Loading Zephyr data into Amadeus * New format *

2010-02-05T01:31:00.001-08:00

When also company overview(s) is/are desired, after creating a deal set from Zephyr, it is possible to retrieve these data from Amadeus. Although it demands some steps.

This blog thread describes how it is done in the New Amadeus format.
After exporting the Zephyr search (i.e. a deal list) to a dedicated directory:

1. Open Excel
2. Load the export file
3. Look up the BvDep identifiers that were retrieved from the format change option in Xephyr, Targets or Acquirors.
Identifiers like ISIN and Ticker symbol won't work.
4. Remove empty cells by using the autofilter (TAB data > filter > auto filter)

Open Amadeus new interface:

Move cursor over the link 'Identification Number'
and select the desired identifier, e.g. ISIN, BvDEP number (Bureau van Dijk), Ticker symbol or SEDOL

1.

2.

3.

Even if ISIN are only a part of a work set it is possible to retrieve them without the need to first copy paste into a .txt file, like with Amadeus old format:

Press OK :

Press OK (2x)

Here are the uploaded ISIN

Press button 'View list of results'

Which results in this list.

Back to Making Zephyr work sets readable in Amadeus (press link)

Loading Zephyr data into Amadeus * Old format *

2010-02-04T05:33:00.000-08:00

When a company overview is desired also, after creating a deal set from Zephyr, it is possible to retrieve these data from Amadeus. Although it demands some steps.

This blog thread describes how it is done in the old Amadeus format.
After exporting the Zephyr search (i.e. a deal list) to a dedicated directory:

1. Open Excel
2. Load the export file
3. Look up the BvDep identifiers that were retrieved from the format change option in Xephyr, Targets or Acquirors.
Identifiers like ISIN and Ticker symbol won't work.
4. Remove empty cells by using the autofilter (TAB data > filter > auto filter)

Select all files in the column with CTRL-SHIFT ARROW Down
CRTL-C (copy), open Note Pad, paste CTRL-V and save as a text file.

!! Go to the save directory and change extension into .bvd !!

Start Amadeus (Bureau van Dijk)
Note: the Wharton portal may give problems uploading the file(s)

Select ' Load company file' from the menu at the left hand side of the screen.

Next, Amadeus will show a list of companies it couldn't find (memorize these or write them down, for manual search)

Next result

Finally, click TABS List and Company Reports (a page for every file in the work set):

Back to Making Zephyr files readable in Amadeus < click link

Making Zephyr work sets readable in Amadeus

2010-02-04T03:51:00.000-08:00

Databases are not always compatible with each other. Even between databases Zephyr and Amadeus (same publisher) this is the case (particularly old format Amadeus).
This cook book will show a work around.

Open Zephyr and make the following deal selection:

Quoted stocks

Acquiror, Target and Vendor > all Netherlands

Time period: 1/01/2000 - 31/12/2005

Result:

Now press Button List of deals in the top Bar (second left)

Sort the deals by deal value: (clickable link)

Adjust format of the presented data.
1.

2.

3.

With the drop down option in the top it's possible to select on deal type, company roles, stock data etc. The latter is important for selection Identifiers like ISIN and Ticker Symbol.

4.

Result List of deals:

Export the deal set (top bar button on the right):

In this example we export the 50 largest deals (hence the sort by)

From this point there are two options:
First: uploading into Amadeus Old format (press link)

Second: uploading into Amadeus New Format 2010 (press link)

Date of incorporation

2010-01-28T04:18:00.000-08:00

The date the company was founded.
Not all databases at Vrije Universiteit do actually provide this item, although it seems like they do. For instance Datastream datatype WC182173

Databases that do provide data are
Amadeus or
Lexis/ Nexis (company information)

Switch to English (if desired) by pressing the flag symbol on the right

Press the TAB Company Information

Fill in the required/ desired fields

Result: when just search by key word dat of incorporation and e.g. "North/South America Company Information Sources" (scroll down) results in many records (+ 3000). A fine feature is that the dates of all subsidiaries are listed as well:

Open a record:

Means of saving keeping (top right of screen)

Amadeus (through Wharton or Bureau van Dijk)

Select

A screen pops up, fill in desired date range

The left menu priovides many selection criteria.
E.g., do the same for region (country and city)

The selection in three steps:

Show the actual records by pressing one of the options in the top bar.

List: list of all companies from the selection.
Company report, a report of all companies in the selection (893) etc.
(Peer analysis is limited to 500 companies.)
Example:

Amadeus in Wharton Portal

Choose: Bureau van Dijk (drop down selection)

Open link financial (left hand menu) and search for "incorporation" (CTRL-F)

As for submiting the query (save as TAB-delimited txt file) and opening the results, I refer to this section <- click the link

SDC: Gobal New Issues, Common stock, private equity and creating reports

2010-01-05T03:45:00.000-08:00

The VU license to SDC consists of 3 databases: Venture Expert, Global New Issues and Mergers & Acquisitions.
This topic deals with a search in Global New Issues (GNI)

1. Select the proper database

In this case we are searching within the US.

Press OK

2. Select Categorie:

Select categories by double clicking.

In this case Common equity and Private Equity.

3. Determine date range:

Press OK.

Start searching the desired items. The search menu pops up, or you can open the menu by using the button in the Top Bar:

4. Search Items.

Double click selection (Close Price at close offer/first trade)

Next, determine the volume range of the Close Price.

or where information is available (thise case)

Do the same with IPO

Type: IPO, select Original IPO Flag (Y/N)

A pop up asks to include all original IPOs or exclude them.

Press OK

5. Close and press Button Execute in the Top Bar.

The next screen appears:

Press OK

6. Making a report

Go to the Top Bar and Select the TAB Report

Select New Custom Report.

This enables selecting your own criteria.

Now select the criteria you want or search them by typing in TAB 'All Items':

Press OK, and in the next screen press OK

Think of a make shift save file:

Press SAVE

Next: making up the report

7. Finally: Execute the Report (button Top Bar)
Result:

8. It's possible to make a Tear Sheet for each item in the report.

Select the desired compoany by clicking the row.

Press the Button Tear Sheet.

Result:

Enrolling to Data Center on Blackboard VU

2009-11-06T02:04:00.000-08:00

Black Board Data Center offers in depth information, tutorials, downloads and back ground information on the databases the library offers to the economics students, lecturors and researchers.

In order to get to this Black Board it's necessary to enrol.

Logon with VU-net-id,
select TAB Courses in the top bar,
select Faculteit Economische Wetenschappen,
type in search term Data Center and submit.

Next, enroll (two or three clicks) Data Center Blackboard

Datastream: Balance Sheet Data World Scope

2009-11-06T01:04:00.001-08:00

Introduction:

World Scope provides 120 different balance sheet data for all traded companies. Text information from annual reports should be retrieved from other databases or from company websites.
Datastream makes it possible to compare balance sheet data in time series.
The principles are basically the same as any search in Datastream.

Data types:
. Go to Time Series Search (menu)
. Go to data types

If you are interested in balance sheet data from companies, make certain your are working from equities datatypes.

(It's always a good idea to press the reset button to remove all previous search actions.)
For an overview of all Worldscope datatypes, simply click on Worldscope (see oval)

The total number of datatypes is :

Most of those datatypes don't provide data.

Obviously it saves a lot of work if you can preselect the datatypes that do provide data.
All you have to do is click the Key WS Item Radio Button in the Top Bar:

likewise change the Datatype selection in from 'All' into 'Time Series' and press Filter Search
160 datatypes remain.
Still, not all datatypes provide data.

For 'Vrije Universiteit Amsterdam' students it is possible to download a list a WS datatypes that do provide data from the university Data Center pages on Blackboard: http://bb.vu.nl/
Enrolling to Data Center on Blackboard is explained on this page: click here

Now select the Company Info TAB and next select 'Datastream'

Within this subject select ' Supplementary Files and download Key World Scope Datatypes.
This document is ordered by industrial branch (banks, industrials, insurance, other financials. The only exception are ADRs, which do not provide any data at all.

Datastream: User created Indices (UCI)

2009-10-19T05:21:00.000-07:00

UCI (User Created Indices) are a powerful tool for evaluating different strategies.
First of all create a list of your own liking. For instance: search for Index AEX

and then move your cursor over the > symbol next to AEX

Choose "Display Constituent Equities"
The result is a list of the equities that are currently in AEX.
Next Export you data to an Excel file sheet by using this button at the right top of the screen:

In our example we'll limit ourselves to exporting Mnemonics only (follow instructions)

Export your data and close all screens.
Now open your saved file sheet and make a selection of the mnemonics.

Copy this selection to your clipboard (CTRL-C), open the navigator again and select from the TOP bar the UCI option UCI Create/modify

The next screen show an overview of precreated Indices.
Now Click on Create Indices at the top right of the screen

Paste your selection into the rectangle field on the screen:

Press the next button:

Now, make up your own META data

The result looks like this:

For more information on weighted indexes I refer to Wikipedia < click link

Datastream: official abbreviations indexes and constituent

2009-09-01T02:34:00.000-07:00

Dow Jones Industrials
DJINDUS
LDJINDUS
NASDAQ COMPOSITE
NASCOMP
LNASCOMP
S&P 500
S&PCOMP
LS&PCOMP
S&P 100
S&P100I
LS&P100I
FTSE All Share
FTALLSH
LFTALLSH

BETAs in Datastream

2009-09-01T02:08:00.000-07:00

Only the the latest calculated BETA can be retrieved from Datastream as a number for active equities.
Static Request is needed.
Historic BETAs can be calculated through the following formula:

REGB#(LN#(X/LAG#(X,1M)),LN#(Y/LAG#(Y,1M)),60M)
X = market index (e.g. TOTMKUK or FTALLSH) Y = equity (e.g. MKS)

Condition for your research is that the BETAs you calculated are from equities that are part of the same market Index, or else they can't be compared.

Datastream: Total Shareholder Return (TSR), average return

2009-09-01T02:01:00.000-07:00

This is the change in price of a stock added with dividend on yearly basis.
Return index – datatype (RI)
A return index (RI) is available for individual equities and unit trusts. This shows a theoretical growth in value of a share holding over a specified period, assuming that dividends are re-invested to purchase additional units of an equity or unit trust at the closing price applicable on the ex-dividend date.
Total Return (datatype RI) on Datastream is cumulative as it adds any changes on to the previous day's value.

Average return, there are a few options here.
If you want to get the average between two specific dates then you would run a static request with the company codes in the series field and the expression as per below:
AVG#(X(RI),01/01/00,31/12/00)
Another option is to use a calendar average such as calendar monthly average in a time series request as per below:
CMA#(X(RI))
You would need to set the frequency to monthly and it will then provide an average of the company's RI for each complete calendar month in your time period (specified using the start and end date fields).
Alternatively, if you want to calculate the cumulative sum of all of the calendar monthly average datapoints between two dates, you can use the following:
CSUM#(CMA#(X(RI)))

. . .

For unit trusts, the closing bid price is used. For all countries except the USA and Canada detailed dividend payment data is only available on Datastream from 1988 onwards. Up to this time the RI is constructed using an annualised dividend yield.
From 1988 onwards (and from 1973 for US and Canadian stocks), the availability of detailed dividend payment data enables a more realistic method to be used in which the discrete quantity of dividend paid is added to the price on the ex-date of the payment.
Gross dividends are used where available and the calculation ignores tax and re-investment charges.
Adjusted closing prices are used throughout to determine price index and hence return index. At this point the RI is calculated back to the base date.

See also: functions and expressions in Datastream (link)

SDC Mergers & acquisitions: a League table

2009-08-21T02:35:00.000-07:00

This cook book shows how to create a League table. Keep in mind that SDC offers many more possibilities and options.

1. Starting:
Setting target nations: US target + Non-US target

> Set the date range: 2000 - 2008 >

2.
Search Items Screen: search for "target nation"

3. Search for the Netherlands

4. Next search for sector

5. Search sector telecommunications

Press OK
6. Search items: Deal value (millions

Close and Execute
7. Creating a League table

Search Financial Advisors:

Press OK > and save somewhere

Ignore the next screen and press Execute

Result: league table financial advisors, ranked by value

You can save the result (top bar left, save as....) as either Excel (*.xls) or Text (*.txt)

SDC - Mergers & acquisitions: a tear sheet of Corus and Tata

2009-08-21T02:00:00.001-07:00

This cook book shows how to create a tear sheet report of a merger.
Keep in mind that SDC offers many more possibilities and options.

1. Setting targets.

2. Setting date range

3. Select specific deal

4. Target: Corus
> Type name, click search . . . >

Acquiror = Tata Steel (see arrow)

> Click OK

Ignore the next screen and click Execute or press Close and click Execute >

The result is:

5. Making a report

6. Tear Sheet:

SDC, what is?

2009-08-21T01:57:00.001-07:00

SDC Platinum contains databases with world wide information on financial transactions.
This blog focusses on:

Mergers and Acquisitions
This database contains mergers and acquisitions of companies from USA from1979 and from other countries, starting 1985. Thomson Financial aims at a coverage of all companies in which 5% of the ownership of a company is involved in any transaction of $ 1 million or more. Next to that the database contains data on many other transactions.
Global New Issues (equities and bonds)
This database contains info on introductions to stock exchanges by american companies from 1970 and on and from other countries starting 1983.
Venture Capital (VentureXpert)
Information on company sectors (SIC, NAICS etc.) equities, companies, portfolios and individuals; statistics on industry sectors, (results, statistics and info about investments, IPOs, mergers and acquisitions.

Menu screen SDC:

M&A: a tear sheet example (link)

M&A: make a league table report (link)

Financial Times on Hedge Funds

2009-07-29T01:59:00.000-07:00

... those rascals from the stock exchanges . .
what are their prospects? What is their look out? Is there a future for hedge funds, or are they doomed as governments are trying to get a hold on them?
Find out yourself: click here

What are hedge funds, anyway?
And this is their "crime"
As the name implies, hedge funds often seek to offset potential losses in the principal markets they invest in by hedging their investments using a variety of methods, most notably short selling. However, the term "hedge fund" has come to be applied to many funds that do not actually hedge their investments, and in particular to funds using short selling and other "hedging" methods to increase rather than reduce risk, with the expectation of increasing return.

Short selling (link, full explanation), in short: The short seller hopes to profit from a decline in the value of the assets, when he will pay less to repurchase the assets than he received on selling them.

(image wikipedia)

Hedging (link, full explanation): Hedging is a strategy designed to minimize exposure to such business risks as a sharp contraction in demand for one's inventory, while still allowing the business to profit from producing and maintaining that inventory.

Exporting data from Datastream into Compustat

2009-07-20T06:03:00.000-07:00

World scope provides the annual reports data for Datastream. 120 balance sheet data.
Many data are therefore not available in Datastream.
Another option for annual reports infor is Compustat.

Solution: (example S&P 500)

Start up Datastream > Select Static Search > search for S & P 500 (as given)

Select S & P 500 composite

Select underlying constituent list(mouse over)

Go to equity Datatypes: and filter on static and cusip

Static request > name, cusip > Russel Cusip key (Cusip8)

Back to menu:

SUBMIT:

Removing the 'U' in front of the Cusip codes in Excel.
Excel: Click the cell next to the first cell with data

Click: ƒx

Fill in: 'right'

Select the desired cell > TEXT > Give total number of characters: 8

Steps: Click block > Click top cel > Click block > fill in: number 8 (number of characters)
Click in the right bottom corner of the cell ( a plus + character shows up)

Double click the '+' and all cells are adjusted

Copy this and Select Paste Special ('Values') into Notepad
Save as a TAB-delimited TXT file

Close, and open as See this file (click)
Make a block of the cusip codes column and copy to NOTEPAD

Remove all empty rows
Save as: . . . .txt

Adding a character to make the codes readable in Compustat (Cusip9)
A special CUSIP tool on VU blackboard: http://bb.vu.nl/
(FAQ > Cusip converter > Cusip download file > open with ZIP > RUN >)
(you must install this tool every time)

Mark Bruyneel gives a good explanation on what defines a CUSIP on his blog and how to convert them between databases.
Quote:
"The CUSIP (Committee on Uniform Security Identification Procedures) identifier is a fairly good identifier for finding information in financial databases because it is a unique code for companies. Example:
Company: Teleconnect

CUSIP 6: 879292

CUSIP 8: 87929210

CUSIP 9: 879292100

CUSIP 6 is a company identifier. The CUSIP 8 is a stock/security identifier. CUSIP 9 has the extra check digit (which can also be calculated) which is needed to be able to check whether the CUSIP format is correct. If you have the 6-digit CUSIP you can calculate the 9-digit version based on the 8-digit CUSIP. The seventh and eighth digit indicate the type of security."

A direct link to the CUSIP calculator tool can be found here

Open the TXT file > ! save immediately as a new file !

Importing into Compustat:

Start up Compustat > Compustat North America > Fundamentals annual (menu on the left hand side of the screen)

Choose search > Cusip > select the file you just saved

Select any desired variables not available in Datastream, Name, ticker, cusip9 etc.

NEW INTERFACE WHARTON
Mark's Financial blog gives insight in the new Wharton interface
The past year a lot of work has been done by the University of Pennsylvania to create a new interface with improvements. These improvements will make searching in databases through Wharton even better. One of the improvements is that it has become easier to get additional help on elements of the search screen. Below you can see a an example of the new interface.CLICK FOR MORE . . .

Transfering data from Amadeus (van Dijk) into Datastream

2009-07-17T04:40:00.000-07:00

Letting one database communicate with the other can be quite a complicated process.
This cook book will show how to transfer data from Amadeus to Datastream.

Select Netherlands

Datastream works with Excel...
Now you can close Amadeus and start Excel.
With the Datatsream Add-in you can start working with List Wizard or Create Table.

Opening your Compustat save

2009-07-16T03:25:00.001-07:00

Open your save file in Excel.

1. Click Next/ volgende

2. Next screen, click Next/ volgende

3. Select the first column en scroll to the ultimate right, click-shift.

Important: Make sure you select the "text box", this prevents Excel from removing any zeros at the beginning of the end of your data.

Press Finish/ voltooien

WRDS search tool

2009-07-15T05:55:00.000-07:00

Searching Whartonisn't always easy if you don't know any tickers/ identifiers.

Looking for company codes like CUSIP, GVKEY and other identifiers can easily be done in the WRDS tool.

You can sustain with (part of) company name:

This searches the entire Wharton data portal.

Just select the part of WRDS you are using, for instance CRSP or Compustat.

Example Compustat: 1 match.

NEW INTERFACE

Mark's Financial blog gives insight in the new Wharton interface

The past year a lot of work has been done by the University of Pennsylvania to create a new interface with improvements. These improvements will make searching in databases through Wharton even better. One of the improvements is that it has become easier to get additional help on elements of the search screen. Below you can see a an example of the new interface.
CLICK FOR MORE . . .

Compustat searching, example annual reports

2009-07-14T01:44:00.000-07:00

Compustat is available through the Wharton portal.
Select the desired part of Compustat:

Mouse over provides some information on the database
Select North America (click)
The next screen gives some news on the changes in the database. On the left hand side of the screen are three sections making up the database: quarterly updates, annual updates and FTP (legacy) version (= historic data)
Select Fundamentals Annual

For explantions and more insight in the database:

Documentation provides explanation on the necessary codes.
Data manuals: handy for deeper insight in the database.
The steps needed to create your datalist.

Step 1, date range

Step 2, SEARCH

Search by: TIC, GVKEY,CIK, NAICS, SIC, CUSIP. (explanation online manual)

CIK: The Central Index Key (CIK) is used on the SEC's computer systems to identify corporations and individual people who have filed disclosure with the SEC.
NAICS: The North American Industry Classification System (NAICS) is a 6-digit coding system for classifying establishments on a production/process-oriented basis. This item contains the NAICS code assigned to the company. The NAICS can consist of diffrent number combinations
Important note: in a search it is necessary to involve all code combinations in your seartch. You can do this by putting them in your search field divided by SPACE or as a text file, which each code on a new line
SIC: This item represents the code identifying the line of business best representative of the company as a whole after the corporate action. It consists of a four-digit numeric code. Standard Industrial Classification codes are published by the United States' Office of Management and Budget in their 1987 edition of the Standard Industrial Classification Manual.
CUSIP: This item is a unique identifier for each company on COMPUSTAT®. It is a nine-digit code: the first six digits identify the issuer, the seventh and eighth digit identify the issue, and the ninth digit is the check digit. (For further information, contact the CUSIP Service Bureau, 25 Broadway, New York, NY 10004)
TIC: This item identifies the symbol associated with trading of a particular issue on an exchange.
GVKEY: This item identifies the unique identifier and primary key for the company after the corporate action, i.e., the GVKEY of the acquiring company
Important Note:
It can be hard to find the proper code. WRDS has got a tool (press link) in its top bar in which you can find the codes by e.g. company name.

In this example we'll use the SaraLee Corp. GVKEY: 009411

Next, set your preferences. When uncertain, you can press the links for an explantion.
Method three searches the entire database (later)

(industry format determines which sector you want to search, industrials or financials)

Conditional statements can only be used when searching the entire database (not this example)

Step 3, variables

There are a lot of selectable variables. You can select them with CTRL-clik. If you don't know what they mean you can use the online Manual or Documentation. (can only bereached from campus or with your own digital access code)

Step 4, output

Important: it's recommended to set your output format to TAB-delimited. Explanation follows.

Press submit request

Wait untill the request is done and provides a clickable TXT.
Right-click the link and save the file (to desktop). Do not open it.

The next link (click) will explain how to handle your save file.

NEW INTERFACE
Mark's Financial blog gives insight in the new Wharton interface
The past year a lot of work has been done by the University of Pennsylvania to create a new interface with improvements. These improvements will make searching in databases through Wharton even better. One of the improvements is that it has become easier to get additional help on elements of the search screen. Below you can see a an example of the new interface.CLICK FOR MORE . . .

Compustat Introduction

2009-07-14T01:31:00.000-07:00

Standard & Poor's Compustat is a database of financial, statistical and market information on active and inactive companies throughout the world. The service began in 1962.
This database provides a broad range of information products directed at institutional investors, bankers, advisors, analysts, and asset/portfolio managers in corporate, M&A, private capital, equity, and fixed income markets. The database covers 75,000 global securities, covering 90% of the world's total market capitalization, and provides company data history back nearly 40 years (depending when that company was added to the database)
The following information are provided:
The Global Industry Classification Standard (GICS)

Key market identifiers, includings CUSIP, NAICS, ISIN, SEDOL
Monthly and daily pricing data
Standard & Poor's and other leading Index Data
Earnings data from Thomson I/B/E/S
Corporate actions and company business descriptions
Insider and Institutional holdings
Standard & Poor's Stock Reports, Industry Surveys, and ratings

Links:

Searching in compustat - annuals

WRDS Search Tool

Handling your Compustat save file

Datastream: Commonly used dataypes (Time series equities)

2009-07-09T06:45:00.000-07:00

Key datatypes
Dividend Yield (DY), Earnings per share (EPS), Market value (MV), Price (adjusted) (P), Price index (PI), Total return Index (RI) , Turnover By Volume (VO)
Datastream dataypes
Dividend Yield (DY), EPS (EPS), Free float number of shares (NOSHFF), Market to book value (MTBV), Market value (MV), Number of shares (NOSH), Price (adjusted) (P), Price index (PI), Strategic number of shares (NOSHST), Total assets (DWTA), Total return Index (RI), Turnover by Value (VA), Turnover By Volume (VO)

Explanations:
DY: The dividend yield expresses the dividend per share as a percentage of the share price. The underlying dividend is calculated according to the same principles as datatype DPSC (Dividend per share, current rate) in that it is based on an anticipated annual dividend and excludes special or once-off dividends. For some countries the dividend used is a forecast.
EPS This is the latest annualised rate that may reflect the last financial year or be derived from an aggregation of interim period earnings. Data is either provided by local sources or Worldscope.
MV Market value on Datastream is the share price multiplied by the number of ordinary shares in issue. The amount in issue is updated whenever new tranches of stock are issued or after a capital change.
P Datatype (P) represents the official closing price. This is the default datatype for all equities.
PI The price index expresses the price of an equity as a percentage of its value on the base date, adjusted for capital changes.
RI This shows a theoretical growth in value of a share holding over a specified period, assuming that dividends are re-invested to purchase additional units of an equity or unit trust at the closing price applicable on the ex-dividend date. For unit trusts, the closing bid price is used.
NOSH This is the total number of ordinary shares that represent the capital of the company.
NOSHST The percentage of total shares in issue held strategically and not available to ordinary shareholders.
NOSHFF The percentage of total shares in issue available to ordinary investors. The total number of shares less the strategic holdings.
MTBV This is defined as the market value of the ordinary (common) equity divided by the balance sheet value of the ordinary (common) equity in the company
VA This shows the value of all trades for a stock on a particular day. The figure is expressed in thousands. Daily figures are adjusted for capital changes; non-daily figures are not adjusted.
VO This shows the number of shares traded for a stock on a particular day.
PE This is the price divided by the earnings rate per share at the required date. For full details of the price and earnings figures used in any particular case, see the price and Earnings per share topics.

Datastream: Quick charted overview (equity example)

2009-07-09T06:24:00.000-07:00

In the Retrieve Results screen press: (left of the equity name)

Result:

This also applies to indices, bonds & convertibles, economics, exchange rates, interest rates and commodities.

Datastream: Banks listed at Euronext Amsterdam

2009-07-09T05:44:00.000-07:00

Selection screen Equities

Go to exchange selection:

Select: Euronext

Select Sector

Banks

Selection result:

Now start your search (press search button)

Where are ING, ABN AMRO and Fortis Bank?

Answer:
ING 's main activity is Life Insurance (sector selection)
ABN AMRO has become a dead fund after the Dutch government took control of the bank, check the DEAD box next to Status
Fortis (with a merger with AMEV) has changed its main activity into Life Insurance

Datastream : Time Series MENU - excel Interface

2009-07-09T05:17:00.000-07:00

All selections in the Time Series search are made from this menu.

Pressing the navigator button opens the navigator (see navigator)

. Start date : notation dd-mm-yyyy; by default start date is -2y

. By default End Date: runs op latest trading day

. Frequency by pressing drop down

. Transposing data topples the table 90º

Datastream - Excel Interface

2009-07-09T04:41:00.000-07:00

Excel Interface Datastream

From this screen you can make your first selection(s):

. Static (don't change overtime), handy when creating lists

. Time series

. CA: annual reports data, 120 different balance sheet data, provided by World Scope

. Lists Wizard, to construct a working set from scratch or from a selection

. Request table, handy when working with different (short) Time series, e.g. around events

Datastream - NIKKEI real estate

2009-07-09T03:46:00.000-07:00

DS Time series:
Selection screan (Series selection)
Indices:
Searching "equity indices" > filter on Source : Nikkei (click the three dots button and select from the A-Z list) fill in search word 'real'
Press search
Result: 4 hits (indices)

Datatypes > Equity Indices.
Which funds compose the indices?
Mouse over the bold arrow next to the Index name.
Click 'Display constituent equities'
This presdent a list of all equities making up the index.
Admitted, those are only traded equities.

As for real Estate Prices:
Time series:
Selection screen (Series selection)
> select economics (mouse) over

Select in Source (the three dots . . .', A-Z list, press 'R' and select Real Estate Econ. Institute Co., Ltd, Japan
Click search:
12 items about Real estate prices
For the datatypes: > mouse over Economics

Best is to do the following:
Mouse over and select e.g.
AVE. NEW CONDOMINIUMS PRICE PER HOUSEHOLD - KINKI AREA >
and click Report.
This provides an overview of real estate prices

In a Time series:
the only available datypes is DEFT (default)