Author Archive

Beta values from Datastream

There is a datatype BETA available in Thomson Reuters Datastream  but often this is not what researchers are looking for when they are looking for “company betas”.

The Datastream datatype BETA is static – only the current value is available – and it is also adjusts the raw beta value to make it a forecast beta.

There is a formula that you can use to get a time series of historic beta values: REGB#(LN#(X/LAG#(X,1M)),LN#(Y/LAG#(Y,1M)),60M). This uses the regression beta function with three parameters.

  1. The return of the market over one month – LN#(X/LAG#(X,1M)
  2. The return of the equity over one month – LN#(Y/LAG#(Y,1M)
  3. The time period – number of observations in the regression – 60M

The screenshot below shows the historic beta values for the June 2004 FTSE 100 constituents yearly from 31 December 2000 to 31 December 2013. The market return is the FTSE All Share – LN#(FTALLSH/LAG#(FTALLSH,1M). The equity return is LN#(X/LAG#(X,1M) – X will be each of the list constituents in turn, and the time period is 60 months.

Datastream - historical beta values for ftse 100 (click to expand)

Datastream – historical beta values for ftse 100 (click to expand)

The results in the screenshot come from two entries in a Datastream request table

  • Static request for constituent list LFTSE1000604 and datatypes NAME,ESTAT,TIME,WC09802,BETA
  • Time series request for LFTSE1000604 and the beta formula REGB#(LN#(FTALLSH/LAG#(FTALLSH,1M)),LN#(X/LAG#(X,1M)),60M)

The results show that care is required for dead companies. The formula continues to generate results long after the last valid price data (shown by TIME) so for dead companies the beta returned is always zero once the company has been dead 5 years (60 months).

The Worldscope beta datatype (WC09802) just gives the latest value (see Worldscope manual for details). If you use this in a time series request then you just get the same value repeated. In contrast the static datatype BETA gives an error if used in a time series request.

You can use the local index datatype (LI) rather than explicitly choosing the index in the beta formula.

  • REGB#(LN#(X(LI)/LAG#(X(LI),1M)),LN#(X/LAG#(X,1M)),60M)

This will give the same values as displayed above as X(LI) is FTALLSH for all these LSE listed shares.

Related Posts

Beta values on Thomson (April 2014)

Beta values for companies (March 2010)

Beta and choosing the market (researchfinancial blog,  May 2014) – includes a useful Excel sheet for constructing a Datastream beta formula

Categories: company information Tags:

US multinationals

A query about finding the top 30 US multinational companies from 1975 onwards has turned out to be very difficult.

Since we are looking at US companies WRDS is the place to start. CRSP Compustat Merged (CCM) provides both the stock price data (from CRSP) and the accounting data (from Compustat) and provides excellent historical data.

However, after some investigation we could find no easy way of finding multinational companies using WRDS-CCM or WRDS-Compustat. We were looking to be able to rank the companies on foreign/international sales and then select the top 30 for each year.

Thsomson Reuters Datastream does provide a foreign/international sales dataype (WC07101). As a test of large US companies we select a historical S&P 500 constituent list (LSP500I0901 – S&P 500 constituents September 2001) and get sales, international sales and foreign/international sales % of total sales for 31 December 2001.

US cos International Sales 2001 (click to expand)

US cos International Sales 2001 (click to expand)

This screenshot shows the results ordered by international sales and certainly has companies that we would recognise as large US multinationals. However, scanning the results include many companies where there are no sales figures and some where only (total) sales available.

Further tests for international sale and total sales for the September 2001 S&P 500 constituents (LSP500I0901) shows that Datastream has no data before 1980. Further checking has shown that there is another better S&P 500 constituents list with 12 additional years of historical data (LS&PCOMP0989 – September 1989, LS&PCOMP0901 – September 2001) see notes below. (However, the results in terms of sales and international sales data are the same.)

In summary,  Datastream has some useful data but it is incomplete, and only goes back to 1980.

Returning to CRSP Compustat Merged on WRDS (CCM) and selecting a small number of companies by hand we can check that CCM does have total sales from 1975 onwards

CCM (WRDS) historical sales (click to expand)

CCM (WRDS) historical sales (click to expand)

By now we have realised that something that sounds easy, finding large quoted multinational companies, is not. If US companies only have to report total sales in their accounts then databases such as Compustat and Worldscope/Datastream will only have foreign/international sales if the companies choose to report this. We can check this by getting the annual reports for individual companies.

If something is harder then expected it is also worth looking at related work to see exactly how they have identified US multinational companies.

As a check I had a look at Capital IQ. This was similar to Compustat in terms of the datatypes (variables) available, which is not surprising as they are both products from Standard and Poors (S&P), but more limited than Compustat in terms of the historical data available.

Bloomberg – I have still to check – however I don’t expect it to be better than Datastream in terms of historical data or in terms of the not available data.

S&P 500 Constituents

While preparing this post, I have discovered that there is more that one S&P 500 constituent list on Datastream.

  • LS&PCOMP from Standard and Poor’s, LS&PCOMP is the current constituents, LS&PCOMP0989 is the September 1989 constituents (oldest available), LS&PCOMP0901 is September 2001, and LS&PCOMP0714 is July 2014 (newest on 4 August 2014)
  • LSP500I (no source provided), date is unclear since several members are acquired, merged or delisted. LSP500I0901 looks to be a correct constituents list for Sept 2001.

Datastream changes: S&P Historical Indexes (posted June 2010 Financial Databases and Research) confirms that LS&PCOMP is the best constituent list for the S&P 500.

Compustat – North America – Index Constituents on WRDS will also give the S&P 500 constituents – use GVKEYX 000003 or TIC  I0003. Historical data is available from March 1964. See Constituent list in Compustat (posted September 2010 Databaser blog) for more details.


Categories: company information Tags: ,

All French listed companies in 2002 (Datastream)

In fuller response to a comment on Finding inactive/dead companies on Datastream (posted Feb 2012)

Although it is good for worldwide historic company data, Thomson Reuters Datastream does not give a quick way of getting a historic list for all companies from a specific country.

DSworldscopeFranceFor all French companies listed in 2002 we can use the Worldscope French company list (WSCOPEFR) since worldscope will cover French companies by 2002.

For WSCOPEFR we do a static request including:

  • ESTAT – company status
  • BDATE – base date
  • TIME – latest valid price data
  • EXDSCD – exchange code
  • MAJOR – major security for company
  • ISINID – primary quote

Using the results we filter to get companies listed in 2002

1837 – original Worldscope France list (WSCOPEFR)

1503 – removing companies that died before 2002, TIME < 01 Jan 2002

1015 – removing companies that listed after 2002, BDATE > 31 Dec 2002

You might want to further trim the list: only companies listed on the Paris exchange (EXDSDC = PR), only equities (TYPE = EQ), or only major securities (MAJOR = Y).

reviewing French companies 2002 (click to expand)

reviewing French companies 2002 (click to expand)

Rather that using the Worldscope list as a starting point you could use the Datastream lists FFRA (Active French companies) and DEADFR (Dead French companies), or do an explicit criteria search in Datastream Navigator.

All UK listed companies (part 3) (posted June 2013) gives details of the different approaches for the UK market. The first question about “all French companies” is – all companies with their primary listing on the Paris exchange, or all companies headquartered/registered in France whereever they are listed, or all French registered companies with their primary listing  in Paris.

Categories: company information Tags:

Ownership data from Capital IQ

From the research enquiries of students gathering data for their dissertations we know that data on company ownership can be difficult. Recent investigations suggest that Capital IQ from Standard and Poors (S&P), though not perfect, may be a good choice for many research projects.

The usual way to use Capital IQ is to use a screen to select the companies of interest, then add some additional variables, and finally download the results as an Excel file.

The screenshot below shows a company screen with two criteria:

  1. Companies with their primary listing on the London Stock Exchange (LSE)
  2. Companies with a market capitalisation (aka market value) greater than 1,000 million UK £s on 31 December 2013
Capital IQ company screening (click to expand)

Capital IQ company screening (click to expand)

This gives a total of 268 companies – since this is a test we want to be well within the Capital IQ download limit.

Next use the Customize Display Columns tab to add extra information to the results.

There is a good selection of variables that can be selected from the ownership section. For this test we choose:

  • CEO – Number of shares owned [Latest Quarter - 2]
  • CEO – Value owned [Latest Quarter - 2]

The only options for historical data are in terms of latest quarter – the oldest being latest quarter – 40. Selecting the CEO value owned column the results can be reordered to show which CEO’s of large LSE listed companies have the most wealth in their company’s shares.

Capital IQ ownership results (click to expand)

Capital IQ ownership results (click to expand)

Downloading the results is just a question of selecting Excel and go in the export section just below the screening results header, and waiting while Capital IQ formats the results and returns a download link.

The selection of Latest Quarter – 2 was an attempt to choose the Q4 2013. However, advice from the Capital IQ customer help desk suggest that this will give Q3 2013. The ownership data is updated when the company files the relevant data, usually 4-6 weeks after the end of a quarter. On 16 July we are just in Q3 but the Q2 data will not be available yet. This makes the Latest Quarter Q1 2014, and so Latest Quarter – 2 is Q3 2013.


There is company ownership information available in other databases, but they do not handle the combination of a list of companies and historical data as easily as Capital IQ

  • Thomson Reuters 13F data from WRDS (only data for  US companies)
  • Bloomberg – good for the ownership of one company
  • Thomson –  detailed ownership for worldwide companies but with restrictions on the number of companies that can be investigated at one time, and results often need significant reformatting.
  • Fame, for UK and Irish companies, and Amadeus, for large Eur0pean companies, – can work with lists of companies but variables restricted to the owners holding the most shares.

Company ownership information may only be available for the last few years, or if historical information is available then it may be restricted to the largest shareholders.

Acknowledgements are due to Phil Reed and Xia Hong who explained how to use Capital IQ and noticed to availability of ownership data.

Categories: company information Tags:

Global Financial Database for historical data

June 30, 2014 2 comments

Global Financial Database (GFD or Global Financial Data) provides access to financial and economic time-series data with more than 6500 series covering 150 countries worldwide. Two key characteristics of GFD:

  1. Long historical time-series data – e.g. for most countries GFD provides GDP data going back 100+ years. (screenshot below)
  2. Ease of access – available on any University of Manchester PC (and off-campus using VPN)

Using the GFD Filter Search is one way of getting an overview of the data available. A GFD Filter Search – GFDatabase: Country-UNITED KINGDOM and Series Group-Economic –  gives results including

  • United Kingdom Retail Price Index – Years 1209 – 2014
  • United Kingdom Consumer Price Index – Years 1800 – 2014
  • Great Britain Unemployment Rate – Years 1855 – 2014

The Screenshot below used a combination of a keyword search (Name contains “dividend”) and filter search (GFDatabase: Country-G-20)

GFD search for dividend yield for G20 countries (click to expand)

GFD search for dividend yield for G20 countries (click to expand)

Just select the linked name or a download button to get the page for downloading data.

The UK FT-Actuaries Dividend Yield is the FTSE All Share series that GFD have extended to give a time series that starts in 1926. (For full details see the description.)

Analysis of long-term data is a main market for GFD as illustrated in their White PapersGlobal History of Currencies and GFD blog.

GFD does not (with the University of Manchester subscription) provide data on individual stocks, but it does provide a wide range of series covering bond and equity market indices, commodity prices, exchange rates, interest rates and macro economic series.


Thanks  to the research student, looking for historical UK dividend yield, who mentioned that a paper had got data for UK aggregate stock market from “Global Financial Data”.

This is essentially an edit and re-post of Global Financial Database (posted June 2011) following a check that the GFD interface had not changed significantly in the last 3 years, and that the UK aggregate dividend yield was part of the University of Manchester subscription.

Indices and datatypes in Datastream

June 27, 2014 1 comment

Thomson ReutersThomson Reuters Datastream is possibly the largest database in terms of the numerical, historical data available. However, it can sometimes surprise with data not being available. Recently this has been highlighted with research students looking for dividend yield data for several international indices.

Equity indices are provided by a number of different companies and this means that the datatypes available in Datastream can vary from index to index. In addition, when Datastream says that it has 35 years of data for an index, this is for the index level (datatype Price Index – PI), and other datatypes may well have less historical data.

There is a quick way to check datatype availability using the Datastream Navigator.

  1. Find the index – search the Equity Indices category, or use Explore – Equity Indices
  2. Select the name so that the index metadata is displayed
  3. Find and select the “more” at the end of the Headline coverage and you get a window displaying datatypes and dates.

The following screenshot is for the Nikkei 225 (JAPDOWA) – it shows Price Index (PI) available from April 1950, Total Return Index (RI) from January 2002, and Dividend Yield (DY) is not available at all.

DS Navigator Index datatypes (click to expand)

DS Navigator Index datatypes (click to expand)

If the datatype that you want is not available, or does not have sufficient historic data, there are two options.

First, check if there is an alternative datatype – for some indices there is a DSDY (Datastream calculated dividend yield) or MSDY (MSCI Dividend Yield) [See Equity Indices and datatypes (Datastream) posted July 2010)

Second, choose a different index where DY, or MSDY or DSDY, is available. For Japan the MSCI Country Index MSJPANL has MSDY available from December 1969, and Japan Datastream-Market TOTMKJP had DY available from January 1973.

To get more historic data then we need to look something like the aggregate Japan/Tokyo stock exchange dividend yield from Global Financial Database (GFD) – See Global Financial Database for historical data (posted June 2014)


Although this post has been about equity indices, this approach works for finding the historic availability of datatypes for other Datastream series, for example the commodity crude oil series OILBREN.

If you use the Datastream Navigator Criteria Search then the “more” to display datatype details is at the end of the Datatypes column. (When searching make sure to select the correct Data category.)


Linking Compustat and Mergent FISD on WRDS

June 25, 2014 1 comment

“How can we get details of corporate bonds issued by S&P 500 companies?” This enquiry has prompted this post that concentrates on the issues of linking databases, an essential skill in many research projects.

WRDS-iconMergent FISD (Fixed Income Securities Database) is a comprehensive database of publicly-offered U.S. bonds available on WRDS. It is a relatively new addition to the University of Manchester research database portfolio, but the WRDS web interface follows the same general style for all databases. The key to getting your data is having a list of appropriate identifiers.

For this query an brief review of Mergent FSID on WRDS and “Issuer CUSIP” is a valid identifier.

Step 1 – Getting CUSIPs for S&P 500 companies

This is quite easy in WRDS as Compustat has an Index Constituents query (Select Compustat – North America – Index Constituents). The i0003 identifier for the S&P500 index can be found using the code lookup facility.

You select your date range, index identifier, and check that you include CUSIP, from company information, as one of the variables to include in the results.

The results can be significantly more than 500 companies, depending on your selected date range, as the constituents of the S&P 500 change over time, and some companies have had several CUSIPs over time.

Step 2 – Greeting CUSIPs into format for Mergent FISD

In the ideal world the CUSIPs from Compustat would be exactly those required by Mergent FISD. In fact a little work is needed as the CUSIPs generated are 9 digits (for the stock issue that is in the S&P 500) and FISD requires the 6 digit issues CUSIP.

Therefore we used Excel, or another program, to trim the final 3 digits from the Compustat generated CUSIPs -

931142103 -> 931142 , 00206R102 -> 00206R

This can produce some duplicates that can be eliminated.

Step 3 – Using Issuer CUSIPs to query Mergent FISD

For a WRDS query you need to copy your identifiers into a plain text (.txt) file – one identifier per line. When indicating the variable (id) “to search by” we select the Issuer CUSIP option, and then the Upload a file containing company codes.

Selecting the FISD Bond Issues Query –  choosing some test dates and test variables for the results gave 2315 observations – some included in the screenshot below.


Mergent FISD sample results (click to expand)

Mergent FISD sample results (click to expand)

One of the strengths of WRDS is that once you have got your file of identifiers it is easy to submit another request if you want to vary the time period, or the variables in the results. You can also choose the format of the results.

Related posts

CUSIP – 6-digit cusip to 9-digit cusip (posted June 2010) – has more detail on CUSIPs

S&P 500 (Standard and Poor’s 500 Index) (posted December 2011) – has more on the S&P 500 on WRDS

Additional comment on S&P 500 code

When using the “Index Constituents Code Lookup” facility on WRDS there are two potential candidates for the S&P 500 – S&P 500 Comp – Ltd, with Ticker I0003, and S&P 500 Comp – Wed, with Ticker I0010.

A little investigation on the WRDS online help reveals the difference.

“The S&P 500 LTD (last trading day) has the ticker I0003 and S&P 500 WED (last Wednesday of the month) has ticker I0010. Two indices were created because some investors felt that the “last trading day” was too volatile, and preferred the last “Wednesday” methodology. Most people use I0003 (“LTD”)” (WRDS, no date)

The monthly value of an index, or equity, is by convention taken as the last trading date of the month. However, these end of month last trading day values can be more volatile for indices where there is high volumes of options traded on their expiry date. In our query to get the index constituents the choice of index will make no difference.


WRDS. (no date) WRDS (Wharton Research Data Services) Knowledge Base with FAQs – WRDS: S&P 500 Data. [Online]. Available at: WRDS (Accessed: 25 June 2014)

Categories: company information Tags: ,

Get every new post delivered to your Inbox.

Join 1,035 other followers