Compustat North America Fundamentals on WRDS (Wharton Research Data Services) offers such a large number of data items that it can be tricky to find the ones you require.
The following tips for finding data items can be used whether you are Compustat Fundamantals directly, or through CRSP/Compustat Merged (CCM) Fundamentals.
There is a WRDS Compustat North America request - Simplified Financial Statement Extract – that gives only the most commonly used items – 25 rather than 382 from the balance sheet, 14 rather than 328 from the income statement etc.
Selecting the Variable Descriptions link at the top of the request online form gives an alphabetic list of these variable (item) names and their descriptions (see screenshot). As on other WRDS pages you can use “Ctrl-F” to search for specific word.
There is also the Complete Financial Statements form under Compustat Tools. This gives the most common items in a financial statement order.
On occasions you may be looking for Compustat data items that have been mentioned in academic research papers.
If you see a paper mentioning Compustat numbers, e.g. “6 – Total Assets”, then the paper is referring to the older method of identifying data items. In 2007-2008 Compustat changed from a legacy FTP format to the current Xpressfeed (XPF) format. The CRSP/Compustat Merged Database Guide Annual Data -Industrial provides a link between the older item numbers and the newer item names.
The WRDS Support tab provides Vendor Manuals – Compustat vendor as one of the frequently visited links. This gives a quick way of reaching the WRDS Support – Compustat Manuals and Overview page.
This page has links to the detailed Compustat manuals, including two describing the format change “Comparison of the Legacy FTP format to Xpressfeed” and “Variable Translations – Compann Compqtr and PDE”.
- Worldscope meets Compustat: A Comparison of Financial Databases by Niels Ulbricht and Christian Weiner may be useful if you are looking to find comparable Worldscope and Compustat data items.
Thanks to Praj Desai for the advice on searching Compustat, especially when papers refer to older compustat numbers, included in this post.
Do University of Manchester researchers have access to company credit ratings? Yes, but the data is limited so may not be sufficient for some researchers.
There are no company credit rating datatypes (variables) in Datastream (including Worldscope), but they do exist in the deals module of Thomson ONE.com.
For example, let us search for bond issues:
- TONE.com – Screening and Analysis – Deals Advanced Search – All Bonds
- Issue Date – 1 Jan 2000 to 1 Jan 2010
- Issuer/Borrower Nation – United Kingdom
- Issuer Public Status – Public
Then in the results report include the fields:
- SP - Ratings: S&P Debt/Bank Loan Rating
- MDY – Ratings: Moody’s Debt/Bank Loan Rating
- Issuer Name (I), 6-digit CUSIP (CU),
In this case there are 2625 results that can be exported to Excel. (The export to Excel can timeout for large numbers of results – if this happens vary the issue date criteria a to reduce the number of results from a single request.)
These results do include many observations for the some companies, especially financial companies such as the Royal Bank of Scotland, and the rating can be NR (Not Rated) or – (unknown).
Further sorting of the results reveals the observations cover 329 distinct companies, but some will be not rated. (The Nov 17 2009 issue for Segro PLC in screenshot above was NR, and that is the only observation for that company in these results.)
US Company Credit Ratings
For US companies there is data in Compustat on WRDS – see Company Credit Ratings ( posted March 2011)
On 28 January 2014, the UK Office for National Statistics (ONS) released its Gross Domestic Product: Preliminary Estimate, Q4 2013. The headline figure that appears in the news is the 0.7% increase (Q4 2013 compared with Q3 2013).
In Bloomberg, type “UK GDP” in the command line and the predictive search will offer the most requested series or “UK GDP <HELP/F1> will take you straight to the headline figure.
UKGRABMI Index – This is the figure just released by the ONS. The general notes clearly explains that this is real GDP (by expenditure) and therefore adjusted for inflation. The corresponding year on year percentage change, and quarter on quarter, can be found from the related indicators tab.
UKGRYBHA Index – This is the nominal GDP (by expenditure) figure, not adjusted for inflation. The corresponding percentage change figures are on the related indicators tab.
UKGRYBGB Index – The GDP deflator (also known as implicit price deflator).
In Datastream, selecting the economics category in the Datastream Navigator and searching for “UK GDP” gives series from the ONS as the top results.
UKGDP…D – GDP AT MARKET PRICES (CVM) – This real GDP figure is the one just released by the ONS: 387691 Millions GBP (£ UK).
UKGDP…B – GROSS DOMESTIC PRODUCT (MARKET PRICES) – This is the ONS nominal GDP figure – the latest value is still Q3 2013
UKGDPIDPE – IPD OF GDP MARKET PRICES – This is the Implicit Price Deflator (IPD)
There are several factors that make searching for GDP figures on Bloomberg and Datastream a little tricky.
There are many potential sources. The main distinction is between national sources, such as the ONS in the UK, and international sources, such as the OECD, IMF, World Bank. International sources may be more appropriate for international studies as they apply a consistent methodology across different countries.
The names can be confusing. The Datastream series UKGDP…D does not use the term “real GDP” in its title. The preferred term to indicate real GDP is “constant prices”, with 2010 = 100 (see screenshot above). For nominal GDP the preferred term is “current prices”.
There are series for different components of GDP, series for different methods of calculating GDP, and series for GDP forecasts as well as the more common GDP historic values.
Robert J. Shiller was one of the three people awarded The Sveriges Riksbank Prize in Economic Sciences in Memory of Alfred Nobel 2013 “for their empirical analysis of asset prices”. The other two recipients of the Nobel Prize for economics 2013 were Eugene F. Fama and Lars Peter Hansen. Since reading about this award I keep noticing Shiller’s research.
Shiller, R.J. (2005) Irrational Exuberence (2nd edition) Princeton University Press is an international bestseller. It draws out the psychological origins of volatility in financial markets – the first edition concentrating on the technology bubble of the late 1990′s and the second adding the US housing market boom that precipitated the recent recession.
The Yale university site has “Online Data Robert Shiller” that gives access to data on investor attitudes, US stock market data from 1871, and historic housing market data. Robert Shiller and Karl Case are the original developers of the S&P/Case-Shiller Home Price Indices that are now available from Standard & Poors (S&P).
Recently the Financial Times (FT) produced a list of business and management Moocs (Massive open online courses) that includes in its “finance and accounting” section -
Yale University, with Bob Shiller [Coursera]
(Adam Palin, Mooc matters, FT, 16 December 2013)
Finally, Robert Shiller is mentioned in the blog post A good year ends, but what’s next for stocks? (Musings on Markets, 2 January 2014) that describes a variety of approaches to estimating whether the US stock market is over valued.
Thomson ONE.com (or T1.com) is Thomson Reuters replacement product for Thomson One Banker. It provides relatively easy access to analysts’ reports on public (quoted) companies through its Research tab (in Company Views).
You can also search for analysts’ reports using Screening & Analysis -> Research -> Research Search. This search screen is similar to one available through the older product Thomson Research.
Select your company using the boxes at the top left and then the Research tab. Do not worry that the heading says Company Research – Embargoed – the University of Manchester subscription does include these reports. When you see the price given as Subscription this is because our subscription for personal non-commercial use is a fixed fee – others pay per page for these reports.
If you get nothing the most common problem is browser compatability – see details below.
The number of analysts’ reports will vary significantly from company to company. For the year to 22 Jan 2014, Apple (AAPL-US) had 877 reports, Tesco (TSCO-LN) had 203, and Castings (CGS-LN) only 15 reports.
There are two types of reports:
1) Investment bank analysts’ reports (broker’s reports) from JPMORGAN, DEUTSCHE BANK RESEARCH, BARCLAYS etc. These are typically short reports with the analysts’ latest update of their Target Price or Earnings Per Share (EPS) forecast and explanatory background information.
2) Company analysts’ reports from MARKETLINE (DATAMONITOR), GLOBALDATA, ‘MORNINGSTAR, INC’ etc. These are longer reports with more information about the company, for example GLOBALDATA usually includes a SWOT analysis, but no specific target price or EPS forecast.
You can use the search options on the number of pages, contributor and date to refine your search. If you need more options there is an Advanced Research Page link at the top right – (similar to the older Thomson Research search page)
There is a Thomson ONE guide in the databases section of the business and management guides page (LibGuides)
Browser problems with Thomson ONE.com
Thomson ONE.com (or T1.com) is sensitive to your browser. Like other Thomson Reuters products it only works with Internet Explorer (IE). Further it only works with IE versions 7,8, and 9. For more recent versions you have to run IE in compatability mode. Once you have a compatable browser you have to make sure that it allows popups from amr.thomsonone.com – without this it will fail when you try to download a report forcing you to restart your search. For more detail see Thomson ONE.com – browser compatibility (posted March 2014)
Most posts tagged Thomson Research will also be relevant to research reports on Thomnson ONE.com
Revised information below in the replacement for the ABS journal quality guide due out later in 2014. (Update 12 March 2014)
The SJR SCImago Journal and Country Rank – Journal Ranking, based on Scopus data, now have 2012 as their latest year.
The SJR indicator, developed by SCImago, is not as well know as the JCR (Journal Citation Reports), including JIF (Journal Impact Factor), from ISI Web of Knowledge. However the metrics are based on Scopus rather than ISI Web of Knowledge (Web of Science) so more business and management journals are covered, and more business and management subject categories.
The screenshot below shows results for the subject category Accounting from the subject area Business, Management and Accounting. There is also a category Marketing in this subject area (see Journal ranking – marketing posted August 2012). The subject category Finance is in the separate subject area Economics, Econometrics and Finance.
Another major advantage is that the SJR SCImago Journal and Country Rank – Journal Ranking portal is available online – there is no requirement for a Scopus subscription.
Note the number format in the portal – the standard UK format would have the SJR value for the Journal of Finance written as 14.465 (fourteen point …) and the total references as 2,957 (two thousand, nine hundred …)
The CWTS Journal Indicators are also based on Scopus data, and 2012 is now the latest available. CWTS provide the SNIP indicator (source normalized impact per paper) that corrects for differences in citation practices between different scientific fields.
Other journal rankings
Journal ranking – 2012 JCR – (posted June 2103) the ISI Web of Knowledge (Web of Science) Journal Citation Reports – latest year 2012.
Google Scholar Metrics – 2013 version see Journal ranking – August 2013 update
ABS Journal Quality Guide - The Association of Business Schools (ABS) have created the EAJG to produce the International Guide to Academic Journal Quality, building on the success of the earlier ABS Journal Quality Guide. The new EAJG (ABS) journal quality guide 2014 is expected later this year – latest progress report.
A newspaper article mentioned that the FTSE 100 index recently celebrated its 30th birthday (Stevenson, 2014).
It is relatively easy to produce a graph of the FTSE 100 over the last 30 years, but more difficult to identify how the constituents have changed over the years.
The screenshot below is from Thomson Reuters Datastream (2014) and Datastream also includes FTSE 100 constituent lists from January 1996. (See Historical Index constituents (e.g. FTSE 100) posted June 2011) It is easy to do a similar graph with Bloomberg but it only records the constituents from 2 January 2001.
Rebecca Clancy (2014) identifies 30 current FTSE 100 companies that were there at the start and 19 of these have always been included in the index. A Financial Times article from February 14 1984 suggests that the new FTSE 100 index did not immediately replace the existing FT 30. It also records the first index change. “The only change so far has been the removal of Eagle Star on its takeover by BAT Industries, and its replacement by the next on the list, which happened to be Charterhouse J Rothschild.” (Riley, 1984).
I have a file FTSE 100 original constituents that is consistent with the FT article (Riley, 1984) including Eagle Star and BAT Industries but not Charterhouse J Rothschild. Unfortunately I have no record of the provenance of this file, but I think it was related to the University of Manchester Library involvement in the SCoRe annual reports – SCoRe (search company reports) website closing (posted March 2013)
Clancy, R. (2014) “FTSE 100 celebrates 30th birthday”, The Telegraph, 2 January [Online]. Available at: http://www.telegraph.co.uk/finance/markets/ftse100/10544527/FTSE-100-celebrates-30th-birthday.html (Accessed: 7 January 2014).
Datastream. (2014) Thomson Reuters Datastream. [Online]. Available at: Subscription Service (Accessed: January 2014)
Riley, B. (1984) “Financial Times-Stock Exchange 100 Index Goes Live”, Financial Times, 14 February 1984, p. 8, Edition 29,246. Available at: Financial Times Historical Archive 1888 – 2009 (Gale Group) Gale Document Number:HS2304487684 (Accessed: 6 January 2014).
Stevenson, T. (2014) “Why I can’t stop watching the Footsie”, The Telegraph, 4 January [Online]. Available at: http://www.telegraph.co.uk/finance/personalfinance/investing/shares/10549939/A-memorial-to-our-industrial-past-rather-thank-UK-plc-today.html (Accessed: 7 January 2014).
In 2013 Business Research Plus has reached two significant milestones: over 100,000 views for the year and over 250,000 views since it was started in September 2009.
We are taking a break. There will be no new posts until the week beginning 6th January 2014, and limited checking for comments.
This feels like an appropriate time to metaphorically step back and reflect a little. To try and view Business Research Plus objectively, one angle is to review some other blogs of interest to business and management researchers.
There are some blogs based on “online tips from the library”
- Aberconway Library (Cardiff) – good for Datastream tips
- Datapoints from Lippincott Library (Wharton, UPENN) - excellent for Bloomberg tips
- Judge Business Info (Cambridge) - great for social media tips
- Ohio University Business Blog - lots on Passport GMID and a wide range of useful videos
Some of these are specifically on specialist financial databases (Bloomberg, Datastream, Thomson One, WRDS)
Others aim to promote online resources available through the library
There are library blogs about general information literacy and academic libraries in general
Finally, there are blogs authored by academics that have a specific topic
- Corporate Law and Governance
- Leaders We Deserve
- Musings on Markets - thoughts about valuation, corporate finance and the news of the day!
If we look at the most popular posts on Business Research Plus (BRP): risk free rates and CUSIPs would be specialist financial databases, journal ranking is not subject specific (though the BRP posts are), and referencing/EndNote is similar. The posts “How to …” are firmly in the category of online tips based on Manchester resources.
Blogs evolve. We should review the Business Research Plus About page (Broadhurst, 2010) and, if necessary, update it to reflect what we plan to blog about in 2014. Perhaps a resolution for the new year could be to reorganise our categories and improve our tagging. On the other hand, most readers may not use the categories and tags so our effort would be better spent on new posts, or new revisions of old posts.
If you (as a Business Research Plus) reader have any ideas, comments or suggestions, please let us know (just remember to wait until January if you want a prompt response).
Broadhurst, D. (2010) About [Business Research Plus]. Available at: http://bizlib247.wordpress.com/about-2/ (Accessed 23 December 2013)
University of Manchester Library, (2102) ‘Manchester University Library – restructure heralds new library strategy’ SCONUL Focus, 56, p. 62. Available at: http://www.sconul.ac.uk/sites/default/files/documents/20_6.pdf (Accessed 23 December 2013)
A recent student enquiry asked for clarification on Thomson ONE.com.
The simplest approach is a screenshot and explanation following our guidance in the Referencing Guide at the University of Manchester – Harvard Referencing Style.
Often it is appropriate for the figure caption to be relatively short and the text referring to the figure to give the details of the source of the data. It should be clear whether the figure is directly provided by the financial database as in this screenshot (Thomson ONE.com, 2013), or the figure is of your own design using data obtained.
The reference year (in this case 2013) will normally be the same as the year accessed. Even though the company accounts information in figure 1 is only till 2010, the web page was dynamically generated in response to a request made in 2013 and therefore is “published in 2013″.
The reference year can differ from the year accessed when referring to the document that has its own date. For example the AstraZeneca annual report for the financial year ending 31 December 2010 is published in 2011 and available through Thomson ONE.com (AstraZeneca, 2011).
The URL in the reference (see below) is a compromise. Thomson ONE.com is a subscription service so the URL is of limited value to readers who do not have access. On the other hand the URL does provide additional confirmation to readers who have Thomson ONE.com access.
AstraZeneca. (2011) AstraZeneca Annual Report and Form 20-F Information 2010 [Online]. Available at: http://www.thomsonone.com/ (Accessed: 20 December 2013)
Thomson ONE.com. (2013) Thomson ONE.com. Thomson Reuters. [Online]. Available at: http://www.thomsonone.com/ (Accessed: 20 December 2013)
Appendix – related posts
Referencing databases (November 2012)
Referencing – new citation guide (February 2013)
From the Lippincott Datapoints blog – an excellent source of Bloomberg tips
Originally posted on Datapoints: A blog from the Lippincott Library of the Wharton School of Business:
Bloomberg’s Trade Flow function (ECTR) has recently been updated. It provides an easy way to produce tables and graphs for world trade including data on total trade, surplus/deficit, exports/imports and net exports. Time series go back to 1980 and include yearly, quarterly, and monthly data. Geographic detail includes geographic regions, 251 countries, and geopolitical regions such as BRICS and the EU.
Bloomberg uses import data provided by the IMF. Bloomberg then determines the figures for exports by imputing them from imports. For example, country A’s exports to Country B are given as Country B’s imports from country A. This calculation is done to make world total imports and exports equal. Reported export figures of countries tend to be lower, and are assumed to be less accurate, than import figures. For example, the IMF reports Total World exports in 2012 as 18,097.2 billion U.S. dollars and Total World imports as 18.267.0…
View original 164 more words