Archive

Author Archive

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.

 

Number of Analysts from IBES

June 3, 2014 1 comment

The premier research database for historical numerical analyst forecast data is IBES (or I/B/E/S ) from Thomson Reuters.

If you want the number of analysts who are covering a company the best estimate is the number of analysts who provide an earnings per share estimate (EPS) for the next (to be announced) financial year (FY1). This is the variable estimated by most analysts.

At the University of Manchester we have access to IBES through WRDS. For the number of analysts we can use the IBES summary data rather than the detailed data at the individual analyst level.

IBES number of analysts estimating EPS FY1 (click to expand)

IBES number of analysts estimating EPS FY1 (click to expand)

Selecting IBES – Summary History – Summary Statistics

Select dates and the company codes

Measures – EPS (Earnings Per Share)

Forecast Period Indicator – Fiscal Year 1

Identifying Information – <as required>

Other Variables – Number of Estimates

The results will be similar to these.

INTEL CP (ticker INTC, cusip 45814010) in January 2010 had 43 analysts giving an estimate for EPS of next forecast period (fpi 1). The next forecast period was the company fiscal year ending 31 December 2010 (fpedats 20101231) and the number of analysts was calculated on 14 January 20101  (statpers 20100114).

Note that the number of analysts (numest) varies monthly. IBES considers that an analysts estimate is only valid for a certain period of time. If analysts do not update or confirm their estimate within this period then it is removed from the number of estimates.

The IBES summary data is also available through Datastream.

The Datastream variable EPS1NE – Earnings Per Share Total Number of Estimates in the Mean FY1 – is the same variable that was obtained using the WRDS IBES Summary Statistics query described above.

Datastream makes it easy to retrieve EPS1NET – EPS Total Number of Estimates (including those excluded from the Mean) FY1 – in January 2010 this was 44, rather than 43 for EPS1NE.

This expands on the July 2010 post No of Analysts covering a company

Categories: company information Tags: , ,

Checking database coverage (companies)

May 30, 2014 1 comment

As research students begin to scope their dissertation projects, they often ask about the availability of company data for a specific market (country). Two basic questions are how many companies are covered by a database, and how much historical data is available.

This post will take South Africa and Datastream as a concrete example, but it can be adapted to other databases.

Using Thomson Reuters Datastream Navigator Critera Search we can search for :-

  • Market equals South Africa
  • Major Security is Yes and Primary Quote is Yes (to ensure that there is only one equity series for a company)
  • Status is All (to include both active and dead/inactive companies)

This search returned 1295 results, and on the Base Date header we can select the results to be oldest to newest

Datastream Navigator South Africa equities search (click to expand)

Datastream Navigator South Africa equities search (click to expand)

A total of 1295 is a reasonable number of companies, and the results show that data is available from January 1 1973.

This list can be downloaded for further analysis using the Excel icon at the top right.

One of the columns in the Datastream Navigator output, WS, indicates whether the company is in Worldscope, and therefore whether company accounts information is available.

The summary statistics for WS and Equity Status are

in Worldscope Not in Worldscope
Active 322 88
Dead 533 347
Suspended 4 1

The Datastream Base Date (BDATE) variable gives the first date for which price data is available for a company (equity series). There is a Worldscope variable WC11516, Date added to product, but when a company is added this can include historical accounts data. For example, the company AECI (dscode 930060) has a value for  WC11516 of 19920609 indicating it was added to Worldscope in June 1992. However a time series request for net sales (WC01001) shows that data is available at least from 1980.

Creating a Datastream list and then doing a few test requests is the quickest way to check the availability of specific data items. See our guide Datastream Part 2 (List Creation) for more details.

Quick Summary

Datastream covers approximately 1300 South African companies, about 400 of which are active (on May 28 2014), and price data goes back to 1973. Company accounts data is available for approx 850 of these 1300 companies, and data pre-1990 looks limited.

A quick equity screen on Thomson ONE.com has 324 active South African companies (386 if active/inactive is selected). (This is what we would expect – dead companies are removed from Thomson ONE.com as they are no longer of interest to professional investors.)

Evaluating Thomson ONE.com for research

Any research that requires gathering company, financial or economic data will involve choosing the appropriate databases. Three key aspects to this decision are :-

  1. data content – which databases have the data required by the research
  2. access – how is the data accessed (e.g. web interface, specialist PC in library, …)
  3. learning curve – how easy is it to learn

Of these the first “data content” is the most specific to a research topic and the obvious place to start.

ThomsonONEfundamentalsThomson ONE.com (or T1.com), Thomson Reuters replacement product for Thomson One Banker, covers a wide range of financial information for listed (public) companies and provides a good example of many common data content issues that researchers need to consider.

Company Accounts (Company Financials)

A significant limitation of Thomson ONE.com from a research perspective is that it does not cover inactive (dead) companies. Research often deals with a significant number of companies and only selecting companies that are still active (listed) leads to survivorship bias. Alternatives giving fuller coverage include Datastream, also from Thomson Reuters, and Compustat for US companies.

Thomson ONE.com also has less historical coverage than more research oriented databases such as Datastream and Compustat.

Financial Markets, Prices, Returns

Thomson ONE.com does provide price and index data. As with company accounts there is the limitation to active companies only. In addition, the web interface is not a good choice if you need to download a significant amount of data, e.g. daily price data for a large list of companies. The database with the best research reputation and longest historical coverage is CRSP, available through WRDS, but this is US data only. Similarly LSPD, also available through WRDS, has the longest historical coverage for UK prices and returns. For global coverage there is Datastream.

Analysts’ Reports

T1researchAnalysts’ reports (brokers’ reports) is an area where Thomson ONE.com has the best data available (to University of Manchester subscribers). See Analysts’ reports on Thomson ONE.com (posted January 2014) for more detail.

The web interface is designed for searching by company name and downloading a small number of these text reports. There is no quick method for downloading many reports for multiple companies.

Company Filings

These are documents that a company files with its regulator. In the US they are often referred to by the name of the SEC form – 10-K for annual reports, 10-Q for quarterly reports, …

The downloading issues for analysts’ reports apply to filings too. Indeed with the over 100 pages and lots of images the annual reports for larger companies can be 10-20 MB and therefore not quick to download. For recent company annual reports you may find it quickest to use google, or another search engine, and download direct from each company’s website.

PI Navigator, from Perfect Information,  is a specialist database for companies regulatory filings of companies with flexible search options.

Deals – Mergers and Acquisitions, New Issues, IPOs

SDC Platinum is a well known database for company deals data, and it is also well regarded by academic researchers. SDC Platinum is now part of the Thomson Reuters portfolio and you can access the data through Thomson ONE.com.

TONEdealsUse the Screening & Analysis tab to access the advanced deals search screen. There are four broad classifications: mergers and acquisitions, equity (inital issues/IPOs and secondary issues), bonds, and syndicated loans.

When researching deals it is important to remember that the information can be sparse – for many variables there is no recorded value. When a public company takes over a private company it is not obliged to reveal all the financial details of the deal. (A deal between private companies may have only the minimum of  information disclosed.)

Some researchers prefer using the older SDC Platinum database, especially when downloading large amounts of data, even though this requires coming to the library to use of of the PCs with SDC installed.

Summary

Thomson ONE.com is a popular choice when researching an individual company, or a small group of companies. When researching a large number of companies, over the last 5/10/20 years, a different more research oriented database (or databases) can be better. However, the choice of which other databases does depend on the detail of the research data required.

Follow

Get every new post delivered to your Inbox.

Join 1,016 other followers