Welcome to Business Research Plus

July 27, 2011 1 comment

Bloomberg Suite

From specialist databases to business literature, Business Research Plus provides advice and tips based on the MBS library service resources and expertise.  See our About page for more details.

Exploring our resources – try the Business and Management Resources page (LibGuides),  scroll down to category or tag cloud in the right-hand column, search this blog (top right) or search Manchester Business Answers 24/7 (FAQ). For latest news see @MBSLibrary 

In 2013 we had 64 new posts, just over 110,000 views, and All UK listed companies (part 1) was the most commented post. Of the top 5 posts Journal Ranking – marketing was from 2012, three were from 2011, and one from 2010. Please read Reflecting on Business Research Plus and comment with your suggestions for improvement. [22 January 2014]

(MBS Library Service is part of The University of Manchester Library. )

FAQ: CUSIPs in WRDS output

July 25, 2014 Leave a comment

Phil Reed:

Using CUSIP codes can cause headaches in Excel. They can be confused with numbers expressed in scientific format or leading zeroes can be lost. The blog post below, written by EDSC, offers some assistance on the subject.

Originally posted on EDSC tips & hints:

When you download data from WRDS to an Excel spreadsheet and include CUSIPs in your file, you often have CUSIPs that that look weird. See this example from CompuStat North America Index Constituents, the column Company Cusip should contain 9-digit CUSIPs:

cusips with E

The E’s in the CUSIPs of Dun & Bradstreet and Dr Pepper are recognized by Excel as a mathematical constant (2,718) and the CUSIPs of Amazon.com and Apple are incomplete (they start with zero’s, but these are not visible). This can very unhandy if you use these codes as input in other databases.

You can solve this by downloading the data from WRDS in another output format (comma-delimited text or tab-delimited text) and then:

  • Open a new Excel sheet
  • Open the ribbon Data
  • Choose From Text
  • Browse to the CSV or txt file and click Import
  • In the Text Import Wizard, mark Delimited and click Next
  • In step 2…

View original 98 more words

Categories: Business Databases 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.

 Alternatives:

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 One.com –  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.

Acknowledgement

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)

Notes

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

“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.

References

WRDS. (no date) WRDS (Wharton Research Data Services) Knowledge Base with FAQs – WRDS: S&P 500 Data. [Online]. Available at: WRDS http://wrds-web.wharton.upenn.edu/wrds/ (Accessed: 25 June 2014)

Categories: company information Tags: ,

Corporate Governance data

Here at Manchester students are busy gathering data for their Masters dissertations – one area causing a few queries is corporate governance so this post will review sources. In addition, we will focus on corporate governance data for Brazil and an example emerging market.

The commercial database providers highlight environmental, social and governance (ESG) data as their customers (professional investors) often have an interest in corporate governance within this wider context. We therefore build on the July 2013 post Environmental, Social and Governance (ESG) data.

Bloomberg - Financial Analysis - Governance data (click to expand)

Bloomberg – Financial Analysis – Governance data (click to expand)

Bloomberg Professional provides data through the ESG tab in FA (Financial Analysis) – e.g. BRFS3 BS Equity FA GO – this has sub-tabs (ESG) overview, environmental, social, governance, ESG ratios and CDP (carbon disclosure project). The screenshot shows the governance sub-tab for the Brazilian firm BRF SA.

Bloomberg provides data on company directors, including pay, through the MGMT (Management) function.

Bloomberg industries has a global corporate governance section (BI CGOV) that highlights investment trends, for example how  to create an exclusion list of companies involved in the arms trade.

Bloomberg also provides information about their environmental, social and governance data through the function ESG. This includes detail of the geographic coverage of ESG data. On 30 April 2014 this was 20.8% globally (based on 10,852 companies), with 25.9% (128 companies) for Brazil.

Thomson Reuters Datastream corporate governance variables appear under the heading ESG – ASSET4 in the Datastream Navigator. Within Asset4  the main sections are corporate governance (Datastream datatypes start CG) social (SO), environmental (EN), and economic (EC).

Datastream ASSET4 coverage for Brazil (click to expand)

Datastream ASSET4 coverage for Brazil (click to expand)

For information about the ESG coverage in Datastream you can download the “ASSET4 on Datastream – Company Level template” – just select the Sample Sheets option on the Datastream Excel add-in and to download this template. You can also access this useful Datastream sample sheet from the Thomson Reuters Datastream Extranet (see  post Environmental, Social and Governance (ESG) data for more details)

The tab for ASSET4 Universe indicates a total over 4400 companies covered, with following for the BRIC emerging markets -

  • Brazil 84
  • Russia 34
  • India 80
  • China 82

This confirms that there is limited ESG data available for emerging markets. Further investigation is needed to identify the level of corporate governance data available. Since most of this data is optional rather than mandatory it will be influenced by reports companies have published that can vary between countries.

Checking historical data availability

A quick check – a Datastream time-series request of the (current)  Brazilian Bovespa index (LBRBOVES) for datatype (variable) Board Size (CGBSDP060) yearly 2004 to 2013. This gives 5 errors, indicating 5 of the 71 companies in the Bovespa index are not in ASSET4. Only 2 (Petrobras PN and Pertobras ON) have data for all 10 years – most have data for 2010, 2011 and 2012 but only about half for 2009 decreasing to 2 in 2006.

The Brazil Bovespa is the benchmark equity index for Brazil so the availability of corporate governance data for Brazillian companies as a whole will be no better, and probably significantly worse.

Double-checking with Bloomberg Professional Excel add-in for all (current) Bovespa index companies, Board Size, 2004 to 2013 confirms the limited amount of historical data available.

An initial investigation strongly suggests that there is limited corporate governance data available for Brazil, particularly in terms of historical data. This suggests that there is not a big demand among professional investors for historic corporate governance data on emerging market companies.

 Specialist sources

WRDS-iconOn WRDS (Wharton Research Data Services) there is Compustat Execucomp for executive compensation, and Risk Metrics for corporate governance data. However, these will only cover US companies.

BoardEx – is an excellent database for searching current board memberships, and browsing links between directors. It is however more limited in its options for searching and downloading historical board data.

Robert Goddard has a Corporate Law and Governance blog that also includes links to a wide variety of related material from UK Corporate Governance reports to selected journals and research publications.

 

Follow

Get every new post delivered to your Inbox.

Join 999 other followers