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)
University of Manchester researchers now have access to additional databases on WRDS (Wharton Research Data Services) following a recent review of the data needed to support current and planned research. These changes are not always easy to see in the menu of “Current Subscriptions” on the left side when you login with a WRDS username and password.
University of Manchester (UoM) main WRDS subscriptions (we don’t subscribe to everything) are:
- Bureau van Dijk – Amadeus (European companies) and Bankscope (banks worldwide)
- COMPUSTAT (North America) – “S&P Capital IQ’s Compustat North America is a database of U.S. and Canadian fundamental and market information on active and inactive publicly held companies”.
- COMPUSTAT Execucomp – Executive Compensation for North American companies
- CRSP – US Stocks and Indices (annual update) – “The Center for Research in Security Prices (CRSP) maintains the most comprehensive collection of security price, return, and volume data for the NYSE, AMEX and NASDAQ stock markets.”
- CRSP/Compustat Merged – CCM – “The gold standard in academic research, The CRSP/COMPUSTAT Merged Database allows for concurrent database access to CRSP’s stock data and Compustat’s fundamental data.”
- CSMAR – “The China Stock Market & Accounting Research Database is designed and developed by GTA Information Technology – one of major providers of China data. The CSMAR Databases offer data on the China stock markets and the financial statements of China’s listed companies. “
- EVENTUS – “Eventus performs event studies using data read directly from CRSP stock databases or pre-extracted from any source.”
- IBES - I/B/E/S from Thomson Reuters – “I/B/E/S International Inc. created their Academic Research Program over 30 years ago to provide both summary and individual analyst forecasts of company earnings, cash flows, and other important financial items, as well as buy-sell-hold recommendations.”
- LSPD – “The London Share Price Database (LSPD) is a unique, comprehensive database of UK stock returns covering over 9,000 UK shares from 1955 to date.”
- Mergent FISD - The Mergent Fixed Income Securities Database (FISD) is a comprehensive database of publicly-offered U.S. bonds.
- Option Metrics - “Ivy DB OptionMetrics is a comprehensive source of historical price and implied volatility data for the US equity and index options markets”
- Risk Metrics – “RiskMetrics (through ISS Governance Services) is a leader in corporate governance data. IRRC was initially the data provider for WRDS. IRRC, however, was acquired by ISS in 2005.”
- TAQ -”The Trade and Quote (TAQ) database contains intraday transactions data (trades and quotes) for all securities listed on the New York Stock Exchange (NYSE) and American Stock Exchange (AMEX), as well as Nasdaq National Market System (NMS) and SmallCap issues.”
- Thomson Reuters ownership ( 13F, Insiders, Mutual Funds ) – “Thomson-Reuters Institutional Holdings (13F) Database provides Institutional Common Stock Holdings and Transactions, as reported on Form 13F filed with the SEC.”
- Thomson Reuters Dealscan or WRDS-Thomson Reuters LPC DealScan – “WRDS-Thomson Reuters LPC DealScan is the world’s pre-eminent source for extensive and reliable information on the global syndicated bank loan market. It provides users with access to Thomson Reuters LPC’s robust database of detailed terms and conditions on over 240,000 loan transactions. These transactions finance M&A activity, working capital needs and other general corporate purposes for loan participants world-wide.”
The WRDS current subscriptions list also include databases that are free to all WRDS subscribers. A list of these is available on the “What is WRDS?” page. More detailed information on WRDS datasets is available to all on their Our Datasets page (no subscription required)
Most of our WRDS subscriptions are US (North America) only – exceptions include Amadeus (European companies), Bankscope (banks worldwide), CSMAR (China), LSPD (UK) and IBES. (IBES is split into a US file (the default) and an International file.)
WRDS subscriptions are complicated as many providers have multiple databases and the University of Manchester may only subscribe to some. For example, for CRSP we subscribe to US stocks and indices, CRSP/Compustat merged, treasuries and mutual funds, but not to the CRSP Ziman REIT database.
- CRSP Treasuries – “The CRSP US Treasury and Inflation Series contain returns and index levels on the US Government Bond Fixed Term Index Series, and the Risk Free Rates File.”
- CRSP Mutual Funds – “As the provider of the only complete database of both active and inactive mutual funds, CRSP leads the way in mutual fund research.”
WRDS does provide some online help for selecting appropriate databases for different types of data, but this help does not take account of our subscriptions. An alternative view of UoM subscriptions is given on the MyWRDS tab and Products page.
US company data on WRDS (posted March 2012)
S&P 500 (Standard and Poor’s 500 Index) (posted December 2011)
WRDS – Wharton Research Data Services (posted March 2011)
Wharton Research Data Services (WRDS). (2014) WRDS Database overview desciptions. [Online]. Available at: WRDS http://wrds-web.wharton.upenn.edu/wrds/ (Accessed: 21 March 2014)
[This reference is adequate since it is probably enough for readers to get access to the information quoted in this post. It does not include the specific page for each quote or cope well with different pages which WRDS being accessed at different dates. However, the alternative of having a whole list of references (WRDS, 2014a), (WRDS, 2014b), (WRDS, 2014c) ... looks a worse choice using far more words to give the reader only a little more information.]
Thomson ONE.com is only certified to work with Internet Explorer 7, 8 and 9. It is not full compatible with Internet Explorer 10 or 11.
Using Internet Explorer 10 or 11
If you have IE 10 or 11 then you can run these in compatibility mode.
Just after the URL for Thomson ONE.com you will see an icon like a piece of paper torn in half. It is coloured grey if the compatibility mode is turned off.
Click in the icon and it will turn blue when IE is in compability mode.
You also need to disable to pop-up blocker or to specifically allow pop-ups from *.thomsonone.com
You can check this using the Thomson ONE.com Diagnostic page.
Additional online information for IE10 and IE11:
- Thomson One & Browser Compatibility: The Case of IE10 (Datapoints blog, posted October 2103) – University of Manchester registered students and staff should use the link on the Thomson ONE.com page.
- ThomsonOne & Internet Explorer 10 (EDSC tips & hints blog, posted August 2013) -
- Using Thomson ONE with IE 10 – Baker Library, Harvard Business School
- Compatibility View information IE10 and Compatibility View information IE11 – this was recommended by Thomson Reuters in response to Thomson ONE.com and IE10/IE11 compatibility problems reported by UK Business’ Librarians Assocaition (BLA) members.
Using Chrome or Firefox
Colleagues at the Lippincott library have reported some success in using the IE Tab Extension to run Thomson ONE.com in Chrome.
Thomson One & Browser Compatibility: Using Chrome and Firefox (Datapoints blog, posted March 2014)
Other Thomson Reuters products
Browser compatibility problems with Thomson Reuters products are not new – see Thomson Research only works fully with IE (posted May 2010)
Researchers often need to find data on companies that are no longer listed (i.e. dead or inactive companies). Often searching by name will be sufficient, but occasionally further investigation is needed.
Searching for RBS Citizens Financial Group onThomson Reuters Datastream gives some results but none of them equities. Knowing that I am looking for a company acquired by RBS (Royal Bank of Scotland Group) perhaps RBS Citizens Financial Group is the name used after the acquisition.
Searching for citizens financial group gives an equity, Datastream code (DScode) 923677 now dead, as expected, with the last valid price data for O3 Jan 1989.
Now Datastream does not contain information on deals (mergers, acquisitions, IPOs, etc.) but limited information is often available in the “Capital Issues and Changes” report. For 923677 this gives “Takeover (effective) by cash … offer made by a subsidiary of the Royal Bank of Scotland” on 16 Dec 1988.
This may be enough to confirm that I have found the DScode needed to get price or return data for the acquired company before the deal. However, to double-check we look for the deal information in Thomson ONE.com.
Using Screening & Analysis -> Deals & League Tables -> Advanced Search and selecting All Mergers & Acquisitions.
Now the RBS group is large, and the acquiror in the database might be a subsidiary so my first few searches fail.
I change and find that lots of companies have names starting “citizens financial”. I select all the ones that are US companies as the potential target and the deal effective date as December 1988.
I get just one result and the predefined Deal Basics Report gives quick access to the deal tearsheet. “Royal Bank of Scotland acquires Citizens Financial Group,RI for US$440M“
- Date announced – March 18 1988
- Date effective – December 16 1988
- Deal value – 440 Million US$
- (SDC) Deal Number – 23691020
The fact that Datastream had price data until Jan 3 1989 though the deal was effective Dec 16 1988 is due to the details of how the shares were removed from the exchange. There would be minimal price change, and probably minimal trading volume, between these dates.
Previous posts featuring hard to find companies
Finding inactive/dead companies on Datastream (Feb 2012) – Vodafone takeover of Mannesman
Datastream – coping with name changes (Oct 2012) - Carlton and Granada merge becoming ITV
The variety of information available on Bloomberg Professional continues to surprise. The Bloomberg function WETR (Global Weather Database) provides a large amount of worldwide historical weather data.
The screenshot below shows that weekly mean temperature for London between February 1 2009 and February 23 2014.
Weather information is of great interest to commodities traders (oil, gas, wheat, etc.) so perhaps I should have expected it to be available on Bloomberg. In addition to WETR, there are a range of other weather functions including BMAP (Hurrican Mapping), SNOW (US Snow Monitor) , USDM (US Drought Monitor) and EUMM (EU Weather & Utility Models).
“EUMM consolidates Bloomberg’s proprietary supply and demand modeling for European gas and power markets, as well as the high-speed, market-moving weather data which drive them, so you can make more accurate trading decisions.” (Bloomberg, 2014)
Sources of free weather data (historical)
You might think that historical weather data would be free and easily accessible on the web, but my searching suggests otherwise.
- http://www.metoffice.gov.uk/education/teachers/past-weather-data – UK Met Office provides a limited amount for free, more than that and you have to pay
- http://blog.worldweatheronline.com/2012/03/05/how-to-get-past-or-historical-weather-forecast/ – World Weather Online provides historical forecasts but only one day at a time (no time series)
- www.wunderground.com sounds promising but has no historical data for London.
- http://www.weatheranalytics.com/industry-solutions/universities-and-labs/ – promotes fee-based data to university researchers implying that free data is not easy find
- Quandl – a search engine for time-series datasets – did not offer anything promising
Bloomberg. (2014) “EUMM help”, Bloomberg Professional. [Online]. Available at: Bloomberg Subscription Service (Accessed: 5 March 2014)