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.
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
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
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|
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.
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.)
Any research that requires gathering company, financial or economic data will involve choosing the appropriate databases. Three key aspects to this decision are :-
- data content – which databases have the data required by the research
- access – how is the data accessed (e.g. web interface, specialist PC in library, …)
- 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.
Thomson 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 (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.
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.
Use 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.
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.
A merger or acquisition is a combination of two companies where one corporation is completely absorbed by another corporation. The lesser company loses its identity and becomes part of the larger corporation, which retains its identity and assumes all the rights, privileges, and liabilities of the merged company.
The research of mergers and acquisitions forms a key part of the MBA programme and a greater understanding of the issues involved is also of interest to the wider research community. The proposed merger between pharmaceutical companies Pfizer and AstraZeneca is much reported in the business news from a variety of perspectives: jobs, innovation, research and development, takeover policy, and tax policy.
Bloomberg Professional is great for researching mergers involving public (quoted) companies. The MA (Merger and Acquisition) function gives an overview of the M&A market and the ability to drill down to details of specific deals. In addition Bloomberg is excellent for researching companies and business news.
You will see that Bloomberg uses colour coding to highlight whether a deal is proposed, pending, completed or withdrawn. You can use the actions option to refine your deal list to select a specific country or industry, or select an individual deal to access the details.
Thomson ONE.com also offers a range of M&A information:
- Market Views – Deal Activity – M&A Market Overview gives a summary of the number and value of deals
- Screening & Analysis – Deals & League Tables – M&A – gives various options for searching the deals module
- Selecting a company and then Company Views – Deals gives the recent deals for a specific company
For qualitative reports on mergers and acquisitions, both proposed and completed, investigate the analyst reports from Thomson ONE.com or the older Thomson Research. With leading contributors such as Merrill Lynch, Credit Suisse and Morgan Stanley, Thomson ONE.com provides analysis reports for over 60,000 companies. It affords authoritative insights into sales, forecasts and strategy evaluation.
Market research databases, for example Keynote/Mintel/Passport GMID, provide information on business market trends that influence companies M&A strategies. These databases allow for the study of market size, segmentation, trends, forecasts and competitor and buying behaviour. They focus on the market analysis of UK and International Industrial/Consumer markets.
Business news is often an essential part of M&A research. If you are not using Bloomberg then Factiva (from Dow Jones) is a key source. Covering over 10,000 international, national and regional newspapers, news feeds and trade publications, Factiva provides access to the latest targeted company and sector developments. Searches can be made via company name, publication, industry, region or type of news.
For quantitative data to study trends and features of historic mergers and acquisitions you will want to get your candidate deal list from Bloomberg Professional MA function search, or a search in Thomson ONE.com screening and analysis, or a search in SDC Platinum. SDC Platinum is an older product from Thomson Reuters but still widely used because of its reputation among commercial and academic customers.
Comparing the performance of one or more companies with “the market” is a common task. Researchers need to choose a time-series that provides a good estimate of the market, and then check that the data they want is available for the relevant time period. (In this post we use Thomson Reuters Datastream as the data source)
For the UK market the FTSE All Share is a common choice. This is a broad based equity index that represents the UK market. The FTSE All Share and the FTSE 100 are the two equity indices that Datastream lists as benchmark indices for the UK.
- FTALLSH datatype RI – the total return, including reinvestment of dividends, for the FTSE All Share is avaliable from 1986.
There are a number of companies that provide a large number of indices and one of these might be the best choice if you are looking across multiple countries or looking for a market index and a more specific sector index.
- TOTMKUK datatype RI – the total return for the Total UK Market from Datastream is available from 1966.
- SBBUKD£ datatype RI – the total return for the Standard and Poor’s Broad UK market index in local currency (£) only has data from 1990.
- MSUTDKL datatype MSRI – the total return for the MSCI UK Country Index in local currency (£) is available from 1970.
Here you have to remember that the MSCI indices have slightly different dataypes, e.g. MSRI rather than RI.
The general formula used is DPL#(PCH#(X(RI),1Y),4) – the percentage change in the series (X), datatype RI, over 1 year calculated to four decimal places. This can easily be changed to give monthly, weekly or daily data, or you can get the index value from Datastream and perform the calculation in Excel.
For the China market things are not so straightforward.
Two China market indices in Datastream “Benchmarks at a Glance” equity indices list are Shanghai SE A Share and Shenzhen SE B Share.
- CHSASHR – Shanghai does not offer the total return RI datatype, only the price index PI is available. PI is available from 1993.
- CHZBSHR – Shenzhen does not offer the total return RI, only price return PI – again available from 1993.
- TOTMKCA datatype RI – the total return for the Total China Market from Datastream is available from 1995.
- SBBCHNL datatype RI – the total return for the Standard and Poor’s Broad UK market index in local currency (CH) only has data from 2007.
- MSICHFL datatype MSRI – the total return for the MSCI China Country Index in local currency (CH) is available from 1995.
All of these indices are in the Chinese Yuan Renminbi currency except for Shenzhen SE B Share, which is in Hong Kong Dollar.
The yearly return values given by these different broad market indices for China vary more widely than those for the UK. Each index provider selects their rules for including stocks that considered investable. In an emerging market such as China there is more difference between what providers choose to include in their country index, and therefore in the observed returns for those indices.
Datastream Request Table for market return results above
This post has provided examples using Thomson Reuters Datastream as the source of equity indices. The same issues have to be considered when choosing a market index from Bloomberg Professional, S&P Capital IQ, or any other source.
Total shareholder return (share price return) (July 2011) gives more detail on calculating total return
S&P 500 (Standard and Poor’s 500 Index) (December 2011) gives sources for the S&P 500 a common choice for the US market
Returns in 2012 (January 2013) illustrates the difference between total return (reinvesting dividends) and price return
A couple of queries recently have been about where to get “CEO compensation” data, and this turns out to be a good example where the answer depends on refining the initial question.
At the University of Manchester we have two specialist financial databases that cover executive pay, and therefore CEO (Chief Executive Officer) compensation.
- BoardEx – this is good for searching current board memberships, but one query was looking for 10 years of historical CEO compensation data for a list of companies
- Execucomp (Compustat Execucomp) – this is available on WRDS, would cope with historical data and a list of companies, but only if they are US companies.
Next I re-read the May 2012 post Executive Pay – this gives Thomson One Banker and Bloomberg as options.
Thomson ONE.com is similar to Thomson One Banker – you need to look up the executives, find the CEO on the list and then select to get the detailed information. (It is even harder if you want a previous CEO)
Bloomberg has a function MGMT that allows also allows you to browse company executives. However, it also has two variables for the total salary and the total compensation of the company CEO (or the executive Bloomberg judge as equivalent to the CEO). I used these in the Bloomberg Excel add-in after loading list of companies.
A little more skill with the Bloomberg Excel add-in and these results could have been presented in a neater fashion.
Datastream is usually good if you want historical numerical data, but searching using “compensation”, “pay”, “salary”, “CEO” all yield no evidence of a suitable time-series datatype.
So as I am looking for historical CEO compensation, including non-US companies, and can get to the library Bloomberg looks to be the best option.
A recent comment asked about creating a list on UK companies listed between 2000 and 2013 and with a market capitalisation of more that $10 million using Datastream. The basic technique is to use a Datastream static request to get data about UK companies, and filter to create the required list.
There are a number of key Datastream datatypes that commonly used
- DSCD – Datastream code
- EXDSCD – Exchange Datastream code (as a check)
- BDATE – Base date
- TIME – latest/final data of price data
- MV – market value (market cap), MV~U$ will give this is US dollars.
If you want companies listed between 2000 and 2013, is this companies that are listed continually from 01 Jan 2000 to 31 Dec 2013, or as is more common companies that are listed for some time between 01 Jan 2000 and 31 Dec 2013.
If you are looking for companies with a market capitalisation of more than say £100 million, is that companies whose market cap is always over £100 million, or those whose market cap is over £100 million at some time. The spreadsheet below illustrates one approach.
The spreadsheet above shows the results of a static request for BDATE and TIME and the market value in US dollars (MV~U$) on the 31 December for each year 2000 to 2013.
For the market value, an alternative is to make a time series request for the market value in US dollars, for example for each quarter 2000 to 2013, and combine this with the static request.
If we take the maximum of all the MV~U$ values in the spreadsheet above then we have the maximum market value at each year end. The maximum of the MV~U$ values for the quarterly time series gives the maximum at each quarter end for the relevant time period.
The Datastream predefined list WSCOPEUK is a reasonable choice for an initial list. (See earlier post All UK listed companies (part 2) for details) We can use Excel to sort and filter to give the required set of companies.
- 5189 – WSCOPEUK – UK companies
- 4535 of these – have a market cap in US dollars over 10 for at least 1 quarter end 2000 – 2013
- 3848 of these – have TIME > 01 Jan 2000, so were listed for at least part of the relevant time period
- These 3848 do include 8 companies that were only listed in December 2013 (BDATE > 30/11/2013) and 12 companies that were delisted in January 2000 (TIME < 01/02/2000)
Happy with this list of 3848 UK companies, then select the column of Datastream codes (DSCD) and use the Create List(from Range) option in the Datastream tab to create your list that can be used in future Datastream queries.
Betas represent the volatility of a company with respect to a market index.
Beta. The sensitivity of the share to market moves. A share with a beta of 1.0 tends to perform in line with the index; one with a beta of 1.2 tends to change by 1.2 percent for each 1 percent move in the index. (London Business School, 2012)
Beta values are used in many financial market models and therefore researchers often ask about which databases include betas. Thomson ONE.com has a relatively easy way of getting historical betas (and current betas if yesterday is current enough).
- Goto Thomson ONE.com – remembering to use IE (Internet explorer) – select the Company Views tab
- Lookup your company in the usual way and select Price Chart
- Select Beta Coefficient in one of the LOWER PANE SERIES drop down boxes (screenshot below)
You can use the View Data button to see the data values that have been charted, or the Excel icon to export the results.
Other sources of beta values include Bloomberg, Datastream, and LSPD for UK companies.
The basic formula for calculating a beta value is fixed but the details can vary so that different sources may give slightly different values. For example, for a UK company Bloomberg uses the FTSE 100 as the default market index and weekly data while Datastream uses the FTSE All Share index and monthly data.
London Business School (2012) “Guide to Company Tables”, Risk Measurement Service, 34(7), p. 9.
Just added the LSE Library Blog to the blogroll widget on the right side.
The LSE (London School of Economics and Political Science) Library Blog does a regular feature that highlights online resources of interest. The most recent Social Science sites of the week from LSE library (28 March 2014) includes sections on women on boards, engaging with massive online courses, research ethics guide book, and the Australian Government Web Archive (AGWA).
The blog also has an excellent post about the recent UK budget with lots of links to additional online resources – UK Budget: get the facts with LSE Library’s recommended academic resources (19 March 2014)
In addition, you have to like a blog that includes a post on the economics of chocolate – Celebrate Valentines day with our economics of chocolate special (14 February 2014)
If you are interested in the UK budget, there is a “TweetMap” and commentary from Anita Greenhill of Manchester Business School – The social media reaction to #Budget2014
For more on women on boards try the Corporate Law and Governance blog board diversity posts.
<HELP> X 2 – press the help key twice – to start a live instant message (IM) session with the Bloomberg helpdesk. This has been such a core part of the help available on Bloomberg Professional that it is a surprise to see it change.
<HELP> X 2 – now gives a screen that describes various ways of exploring Bloomberg’s online help and, at the foot, a link to “submit a question to the Bloomberg Help Desk and receive a response in one business day”
A number of academic Bloomberg subscribers have reported this change. However, there is no mention of it as a recent update so may not apply to all Bloomberg users. (Further evidence for this is that there are frequent references to the live help on Bloomberg.)
Following the Bloomberg Help Desk link you get a form that is very similar to the old IM form for live help. The differences include the email address field and the clear statement that the email response will be “by the next business day”.
When you submit you get a reference number.
The email query facility works. I submitted a question around 10:00am and got a response about 3 hours later.
The email reply does contain a paragraph that suggests this new procedure for contacting the Bloomberg help desk has not been thought through.
This response from the Bloomberg Help Desk has been forwarded to your external email address at your request. Please do not respond to this email. For a full log of this and other inquiries please login to the Bloomberg and run HDSK<GO> or for further assistance press the <HELP> key twice and reference H#47…
To get clarification of the reply to my question I follow this – as described above when I press the <HELP> key twice I have to email my query to the Bloomberg Help Desk (and get another reference number).
Posts in praise of the old live help service:
Help! Help Help! and CHEAT: Getting Assistance on Bloomberg (datapoints blog, October 2013)
Bloomberg… real time information on just about everything (Judge Business School Library blog, March 2014)