Datastream searching for book to market

A query this morning uncovered a situation where some care is needed when searching in Thomson Reuters Datastream.

We were looking for the “book to market” value for a company. It turns out that Datastream does not have this datatype. It does have Market To Book Value (MTBV), but even this can be difficult to find.

• If you search for name contains “book” you get 618 results – rather a lot to scroll through
• If you search for name contains “market” you get 205 results – still a lot to scroll through
• If you search for name contains “book market” you get 0 (zero) results -
• Using both search boxes for name contains “book” AND name contains “market” will give you Market To Book Value (MTBV)

DS Navigator search (click to expand)

After finding Market To Book Value (MTBV) you can get “book to market”:

• the inverse – book to market = 1/MTBV
• use constituents and calculate – book to market = WC03501/MV (ordinary (common) equity / market value)
• use constituents and Datastream expression

Related tips:

Datastream, Worldscope and Units (August 2011, updated April 2013)

Worldscope accounting data – finding data tips (July 2011)

Worldscope – coverage and data definitions

Worldscope is the global company accounts “database” from Thomson Reuters and thus a key research resource for company information. It is accessed using either Datastream (active and inactive companies) or Thomson One Banker (active companies only).

Worldscope is designed to allow comparison of companies that report under different accounting rules worldwide. In addition, it records company information using four templates: banks, industrial companies, insurance companies and other financial companies. This means that many of Worldscope’s datatypes only have values for a subset of the companies covered (e.g. US companies).

For full details of the Worldscope methodology and the definitions of the datatypes you can consult the Worldscope Data Definitions Guide (Issue 14) available from the Datastream Extranet.

The Worldscope Coverage Report, also available from the Datastream Extranet, for 28th Feb 2013 includes the following coverage information:

• Argentina – total companies 144, active 105, inactive 39
• Australia – total companies 2903, active 1957, inactive 946
• …
• United Kingdom – total companies 5093, active 1890, inactive 3203
• United States – total companies 21130, active 9175, inactive 11955
• All countries – total companies 74757, active 46704, inactive 28053

Worldscope Data Definitions Guide from Datastream Extranet (click to expand)

The Worldscope Data Definitions Guide (Issue 14) and Worldscope Coverage Report are also available in the folder Database Manuals\Worldscope when using the database PCs in the Eddie Davies Library

Related Worldscope posts:

• Worldscope – company accounts data definitions (April 2011)
• Datastream, Worldscope and Units (August 2011)
• Worldscope accounting data – finding data tips (July 2011)

Worldscope is not the only database for company accounts (financials) – for example Compustat, available through WRDS,  is very popular with researchers studying US companies and Bloomberg’s Financial Analysis (FA) function gives company account information.

• Where can I find company financials, e.g., balance sheets etc.?

The term “company financials” is often used rather than “company accounts”.

Currency conversion in Datastream

The default for Datastream is to give data, price (P), market value (MV), total assets (WC02999) etc., in local currency. However, when doing regional or global comparisons it can often be useful to convert to a standard currency, such as US dollars.

You can convert a Datastream datatype to a different currency by using the “$” button on the request, or selecting the target currency from the drop-down list.

P is the datatype for adjusted price

X(P)~U$ is the adjusted price converted to US dollars

X(P)~E is the adjusted price converted to Euros

This currency conversion is using one of the simplest expressions available in Datastream. For further examples see:

• Functions and Expressions in Datastream and Extranet (Databaser blog 15 Mar 2010)
• Total shareholder return (posted 29 July 2011)
• Beta values – for companies (posted 5 March 2010)

World Equity Indices (benchmark / key indices)

Datastream Equity Indices (click to expand)

The Datastream Navigator gives quick access to the key equity indices worldwide.

Select Explore – Equity Indices – Benchmarks at a Glance

The top results are worldwide and regional and then there are key indices for specific countries.

This list of benchmark equity indices can be downloaded into Excel using the icon at the top right.

Bloomberg Professional also gives easy access to key indices through functions WEI (World Equity Indices) and EMEQ (Emerging markets equity indices).

Online resources (for if you don’t have access to Datastream or Bloomberg Professional)

• World Stock Indexes – Bloomberg.com
• Market Indices – Reuters.com
• Major World Indices – Yahoo! Finance
• World Stock Market Indices world-stock-exchanges.net

One observation is that a benchmark index may change over time. The benchmark for Italy used to be the Milan MIB 30 Index but this has been replaced by FTSE MIB Index, and for Malaysia the KLSE Composite Index has been replaced by FTSE Bursa Malaysia Klci.

Returns in 2012

When looking at the performance of a share (stock) or index for the year 2012 you will often see a graph that has the basic return and a higher total/cumulative return, which includes the reinvestment of dividends.

FTSE 100 return in 2012 (click to expand)

Illustrating with the FTSE 100 and data from Thomson Reuters Datastream:

• FTSE100 on 31/12/2011 – Price Index (PI) is 5572.28 – Return Index (RI) is 3770.37
• FTSE100 on 31/12/2012 – Price Index (PI) is 5897.81 – Return Index (RI) is 4146.4
• FTSE100 percentage return for 2012 – basic return is 5.84% – total return is 9.97%

Return Formula: Ret_it = ( RI_it – RI_it-1 ) / RI_it-1  (multiply by 100 for percentage return)

For Total Return FTSE 100 in 2012 :  (4146.4 – 3770.37)/3770.37 = 0.099733 [ 9.97% to two significant digits]

You can get Datastream to calculate this directly using the formula PCH#(FTSE100(RI),1Y) for 31/12/2012 – see previous post Total shareholder return (July 2011)

Sainsburys Total Return Analysis on Bloomberg (click to expand)

Illustrating with the Sainsbury (J) and data from Thomson Reuters Datastream (screenshot from Bloomberg Total Return Analysis (TRA) function):

• SBRY on 31/12/2011 – Price adjusted (P) is 302.9 – Return Index (RI) is 11651.27
• SBRY on 31/12/2012 – Price adjusted (P) is 345.1 – Return Index (RI) is 13989.18
• Sainsbury (J) percentage return for 2012 – basic return is 13.93% – total return is 20.07%

For Total Return  Sainsburys in 2012 :  (13989.18 – 11651.27)/11651.27 = 0.200657 [ 20.07% to two significant digits]

You can get Datastream to calculate this directly using the formula PCH#(SBRY(RI),1Y) for 31/12/2012 – see previous post Total shareholder return (July 2011)

Bloomberg Professional‘s Total Return Analysis (TRA) function provides a number of additional features – for example whether the share dividends are reinvested in Sainsbury’s shares or reinvested for a fixed percentage return.

Datastream – coping with name changes

In Datastream you can search for companies (equities*) by name using the Datastream Navigator. However, you can only search by the current company name so care is required if there has been a name change.

FT archive 31 Oct, 2012 (click to enlarge)

For example, you find an FT article on the Carlton Granada merger, “Champagne Flowed as Carlton and Granada Won Approval to Merge. Now ITV Must Make the Deal Work.” (Financial Times[London, England] 11 Oct. 2003) and are looking to find the relevant share prices and returns from Datastream.

Nothing from a “granada” search seems to match.

Datastream Navigator “granada search” (click to enlarge)

The reason for this is that the equity Granada on Datastream was renamed ITV on 2 Feb 2004.

• Before 2 Feb 2004 the Datastream code 931524 represents the Granada (now renamed ITV).
• Upto 30 Jan 2004 the Datastream code 901604 represents Carlton (full Datastream name “Carlton Comms.”)
• From 2 Feb 2004 the Datastream code 931524 represents the merged company ITV.

Although there are three company names involved in this merger, it is recorded in Datastream as if Granada tookover Carlton Comms. and changed its name to ITV at the same time.

Another example is the demerger of GUS (formerly Great Universal Stores) into Home Retail Group and Experian. (Thanks to John Shenton for pointing this out.)

• Upto 10 Oct 2006 the Datastream code 901199 represents GUS (now renamed Home Retail Group)
• From 6 Oct 2006 the Datastream code 410124 represents Experian.
• From 10 Oct 2006 the Datastream code 901199 represents Home Retail Group.

While Datastream only allows you to search for an equity by its current name, you can find its previous name (DS datatype PNAME) and the date of last name change (DS datatype DNMC).

Related posts:

• Finding UK listed companies – try LSPD – if checking the previous name doesn’t identify the equity you want you can try LSPD for UK companies
• Finding inactive/dead companies on Datastream – make sure you are not searching for active companies only (includes another name change example)

*Strictly in Datastream you are searching for the equity that is primary quote of the major security of the company. Major security because some companies issue more that one type of equity and you want the most traded: e.g. Facebook and Google have A and B shares. Primary quote because many larger companies choose to list on more than one market/exchange. UK companies may have a primary quote in London and a secondary quote in the US.

The Financial Times image above is from the Financial Times Historical Archive (Gale).

Datastream Workbook

Datastream (Thomson Reuters) is a Specialist Financial Database.     

Within the Eddie Davies and Precinct libraries there are copies of a ‘Datastream Workbook’, designed to assist students using Datastream.

This has been updated with an additional section under ‘Company Information – Quick Reference’, entitled:  ‘Importing Companies from External Database Search’.

When undertaking research for a dissertation, large numbers of companies can be generated from a database search. Often further analysis is required using another database, such as Datastream. A common company identifier available from database searches is the ISIN (International Security Identification Number) code. For example, GB0008847096 represents Tesco PLC.

Using the Datastream Advance For office (AFO) Excel Add-In, results [ISIN codes] from an external database search can be imported into an Excel sheet (copy and paste) and then the ‘Create List (From Range)’ function can be used to create a List for further analysis. This List can be stored locally or uploaded to the Thomson Reuters Mainframe for access and use in a ‘Static’ or ‘Time Series’ request (search).

The updated Datastream Workbook is available at the Eddie Davies Library and Precinct Library.

Datastream is available to use by current students and staff of the University of Manchester at the Eddie Davies and Precinct libraries.

Datastream AFO problems

Thank you to the students who reported problems with the Datastream AFO Excel add-in. Update Aug 17 2012 – Thanks to Andrew of MBS IT we have a workaround – for details see comment below.

 

Datastream AFO VB error

Investigation this morning has identified the problem. Microsoft recently issued a critical update which has affected external Visual Basic applications including the Datastream AFO Excel add-in. Thomson Reuters are aware of this but they do not yet have a solution. For details see their Microsoft Update note.

MBS IT advise that as the Microsoft update is critical it is not possible to uninstall it. (There have been increased problems with virus and malware software on campus recently.)

Friday Aug 17, MBS IT have come up with a workaround until Thomson Reuters or Microsoft issue a fix (details in comment below). This should be applied to all our PCs with Datastream on reboot. If you get an error at the first attempt to use the DS AFO Excel add-in then close Excel and try again.

If DS AFO Excel add-in problems persist the alternatives that we can suggest are:

1. Use the Datastream Application Interface and export resulting data to Excel.
2. Use an alternative database – see earlier Researching Financial Markets post

We realise that these are not ideal if you are in the midst of collecting your dissertation data from Datastream. Please get in touch if you need further help or advice.

In addition, please continue to let us know if you are still having problems with Datastream.

Manchester United IPO on NYSE

The recent (Aug 10 2012) Manchester United IPO (Initial Public Offering) illustrates the distinction between a company, as a legal entity, and its listing(s) on one or more stock exchanges.

• Companies do not always list on their “home” market – Manchester United have chosen to list in New York (NYSE)
• Companies occasionally have distinct classes of shares – for Manchester United there are Class A and Class B shares, both listed on NYSE on Aug 10 2012.
• Companies can have a listing history – Manchester United was a PLC listed on the London Stock Exchange from Jun 7 1991 to 2005, then a private company, and now an NYSE-listed one.

The history of Manchester United shares is illustrated by searching on Datastream.

Datastream Navigator – Manchester United (click to expand)

The first listing for Manchester United (952540) was on Jun 7 1991. This was followed by secondary listings (Primary Quote = No) on exchanges Frankfurt in 1997 and XETRA in 2000.  These three de-listed on June 22 2005 when the company went private. After a period as a private company there were two listings on the NYSE on Aug 10 2010: Manchester United CL.A (87312Q) (Major Security = Yes) and Manchester United CL.B (87407C) (Major Security = No).

Need More Information

Bloomberg Professional is excellent giving access to lots of information on the company and its IPO. A predictive search for “manchester united” gives easy access to the current NYSE listing and previous LSE listing. Bloomberg also provide lots of business news on the IPO.

Datastream does not give access to information on the IPO. You can get the IPO prospectus, and any other filings documents, from PI Navigator, and financial information on the IPO from Thomson One Banker.

For balance – Manchester City Football Club is a private company, a subsidiary of Manchester City Limited, wholly owned by HH SHEIKH MANSOUR BIN ZAYED AL NAHYAN. [From FAME database for all UK companies, public and private]

Company Status Data in Datastream

There is much confusion in understanding Datastream’s company status information, especially when a company is dead or delisted. Generally, you may find the following 3 types of  non active status data:

Dead: In Datastream, a dead company is a company or corporation that is formally dissolved but has not yet completed distributing its assets. Therefore, you will find `dead – delisted’ meaning dead and also delisted; `dead – EXCHANGE INTO’ meaning company merged with another company and is using a different company name/id; `dead – T/O’ meaning dead and taken over by another company.

Delisted: This means that a company’s stocks are no longer listed on a stock exchange. There are various reasons for the removal, including: a. the security no longer exists, the company is bankrupt; b. the public distribution of the security has dropped to an unacceptably low level; c. the company has failed to comply with the terms of its listing agreement.

Suspended: This referrs to the status of a listed security of a company, who’s trading privileges have been revoked by the Exchange. All securities of the company remain suspended until trading privileges have been reinstated, or the company is delisted.