Author Archive

Converting Company Identifiers for Quoted Companies

24 April 2017 Leave a comment

Recently, we helped a PhD student use a company ID converter tool to find the GVKEY codes (one of the company IDs assigned by S&P) for a list of US companies which had been downloaded from Thomson Reuters DealScan that doesn’t provide GVKEYs. GVKEYs are required in order for her to download the necessary data variables from Compustat North America …

This isn’t a one off case. Researchers in business and finance normally require a range of company data variables which need to extracted from more than one databases. For different reasons, companies can be assigned more than one company IDs: IDs assigned by stock exchanges, such as tickers, SEDOLs; IDs assigned by databases, such as DS Code (Datastream), BVD Code (Bureau van Dijk), PERMNO (CRSP) , CUSIP (Standard & Poors), or ISIN.

Unfortunately, there isn’t one universal company ID system which is adopted by all databases. The most commonly used quoted company identifiers are ISINs (International Security Identification Numbers) and CUSIPs. Some databases may provide more than one IDs. For example, Datastream provides ISIN, CUSIP, Ticker etc. apart from its own proprietary Datastream Codes. If using Datastream, one can download a list of companies with DS Codes, ISIN or CUSIP as required. Other databases, for example, CRSP provides PERMCO, Ticker, and CUSIP.

To help users overcome this problem, some database suppliers may provide tools for users to convert one set of IDs to another. In CRSP, for example, one of the CRSP tools can help convert CUSIP to PERMNO or vice versa.

The best tool that I have come across so far is S&P Capital IQ’s company ID conversion tool: SPCIQ Identifier Converter. Embedded in Capital IQ’s Excel Plugin tools, Capital IQ’s Identifier Converter tool can provide ISIN, CUSIP, Ticker etc. if a company name is provided. In other words, you only need a list of company names and it could provide the required IDs for you if the companies are within Capital IQ’s coverage.

Below are the steps of how to use Capital IQ Identifier Converter after the Capital IQ Excel Plugins is installed.

In Microsoft Excel, with the CIQ plugin installed, from Templates, select and download SPCIQ Identifier Converter.xls:


Then run it from Templates – SPCIQ Plugin Tools – SPCIQ Identifier Converter:

picture 2


The caption below shows a list of sample companies with default company ID – Ticker with the embedded formula. Please note in the command line ticker field name is `IQ_Company_Ticker’.

If a different type of ID is needed, for example, GVKEY, then slight editing of the formula should do the job.


If you do not know whether Capital IQ provides the required company ID, run Formula Builder to confirm: eg: Click on Formula Builder, Search for GVKEY, GVKEY’s field name is `IQ_GVKEY’:


Then input the names (or paste in, from cell A12) – a list of companies for which you require GVKEY and click on `Search’ at the right top. Replace ‘IQ_Company_Ticker’ with ‘IQ_GVKEY’ in the command line and we get the list of companies with GVKEYs below:

Capture new 4

A related post: Company Identifiers in Datastream




Categories: Uncategorized

Corporate Credit Ratings Data

3 March 2016 Leave a comment

Corporate credit ratingCorporate credit ratings data is one of the criteria used in assessing a firm’s ability to service its financial obligations. They can address a specific company’s financial instrument i.e. debt security such as a bond or assess a company’s overall creditworthiness. Through corporate credit ratings, one can determine how a firm is performing and its financial situation.  Well, that is the theory anyway…

Currently, three major rating agencies Moody’s, Standard &Poor’s and Fitch Ratings control approximately 95% of the credit ratings business. While Moody’s and Standard & Poor’s have approximately 40% each, Fitch has around 15%.

The three are commonly known as the “Big Three credit rating agencies”

Types of rating data

Issue vs. issuer ratings

Credit ratings agencies’ ratings can be assigned to a specific issue of debt by a company such as a loan, a bond etc. There are also ratings that are called issuer ratings. These ratings are assigned to the corporate who issues debt securities.

Long term vs.  short term issuer ratings

Long term ratings are opinions of the rating agencies of a company’s capacity and willingness to meet its financial commitments as its debt becomes due in 12 months or beyond

Short term ratings asses the repayment capabilities of a company for its issued debts matured within 12 months.

Local Currency  vs.  Foreign Currency Ratings

When assigning issuer credit ratings, rating agencies make a distinction between foreign currency ratings and local currency ratings. An issuer’s foreign currency rating will differ from its local currency rating when the company has a different capacity to meet its obligations denominated in its local currency, vs. obligations denominated in a foreign currency.

For details of rating scales, please  check out Corporate credite ratings on Wikipedia.

The University of Manchester Library subscribes to a few databases which provide corporate credit ratings data:

Databases which provide credit rating data

Database Data coverage Access Note
Bankscope Current and historical Fitch ratings for banks Via web, WRDS Database licence does not permit download of data
Bloomberg Current and historical ratings data Via Bloomberg terminal only Only one company at a time
Compustat S & P ratings only Via WRDS
Datastream Current and historical 10 occurrences Via Datastream terminal only Using static datatype only
Mergent FISD US current and historical bond ratings data Via WRDS Ratings are also available for bond issuers Current and historical ratings data Via web (Internet Explorer) – one company at a time Using Thomson Reuters Spreadsheet Link Excel add-in for historical data

Please also read our blog post on `Obtaining historical credit ratings data’.

Obtaining historical corporate credit ratings data from

2 February 2016 1 comment

Corporate credit ratings data are useful to assess the financial capabilities of companies to pay back their debt obligations.

Current ratings market is dominated by three rating agencies, the so called ‘big three’. They are Standard & Poor’s (S&P, circa 40%), Moody’s (circa 40%), and Fitch Group (circa 15%).

Ratings could be assigned to debt obligations due within one year (short term rating), or beyond one year (long term rating). They could also be assigned to debt securities that are in local currency or in foreign currencies.

The University of Manchester Library subscribes to a few databases that provide current and historical corporate credit ratings data. One of them is

1.     From portal, a company’s current and historical ratings data can be accessed via the company’s Debt Overview page.

This shows Pfizer’s corporate ratings by the big three agencies since 1972. Data could also be downloaded into Microsoft Excel:

(1) for one company's credit rating

Corporate ratings on for one company at a time

However, this works well only if one needs data for one or a small number of companies. In finance research, we normally need data for hundreds if not more companies over a period of time.  This is when Excel add-in becomes very useful.

2.’s Excel add-in is efficient when it comes to extract multiple years of data for a portfolio of companies.

The Excel add-in for, called Thomson Reuters Spreadsheet Link (TRSL), allows for more complex data gathering requests. Below is an example of getting S&P’s Long term issuer ratings for FTSE100 companies from 1996-2015

List of companies

Step 1: Upload FTSE100 companies with their respective Thomson Tickers (identifiers such as ISIN, CUSIP, and Datastream Code also work), with years 1996-2015 across, with the TRSL add-in


Step 2: Select Items look up, and type in ‘Rating’ at the search box. The first item S&P Long Term Issuer Rating will appear. Highlight this item


Step 3: Input Date and Identifier with cell referencing. So that date cell is C$1, and identifiers cell is $B3. Click on Export


Step 4: Getting the data: At export option box, select ‘Excludes Headings and Dates’, and press OK




Copied across… and under…

Where to find Thomson Reuters Spreadsheet Link

TRSL is available in the Library Finance Zone (currently Dover Street Building G.010). Please see information signs in the rooms for how to set it up.

Alternative sources of credit rating data

Good sources of historical corporate credit ratings data include Bankscope, Bloomberg (through the terminal only), Mergent FISD and Compustat. Datastream only provides current data only.

Company Status Data in Datastream

9 August 2012 1 comment

There is much confusion in understanding Datastream’s company status information, especially when a company is dead or delisted. Generally, you may find the following 3 types of  non active status data:

Dead: In Datastream, a dead company is a company or corporation that is formally dissolved but has not yet completed distributing its assets. Therefore, you will find `dead – delisted’ meaning dead and also delisted; `dead – EXCHANGE INTO’ meaning company merged with another company and is using a different company name/id; `dead – T/O’ meaning dead and taken over by another company.

Delisted: This means that a company’s stocks are no longer listed on a stock exchange. There are various reasons for the removal, including: a. the security no longer exists, the company is bankrupt; b. the public distribution of the security has dropped to an unacceptably low level; c. the company has failed to comply with the terms of its listing agreement.

Suspended: This referrs to the status of a listed security of a company, who’s trading privileges have been revoked by the Exchange. All securities of the company remain suspended until trading privileges have been reinstated, or the company is delisted.

Categories: Business Databases Tags:

Lessons from the Financial Crisis

21 March 2011 Leave a comment
Cover image for product 0470561777
Lessons from the Financial Crisis – Causes, Consequences, and Our Economic Future by Robert W. Kolb (editor) is one of 16 publications in the KOLB SERIES IN FINANCE which the library has recently added to the EDL book collection.

With so much information saturating the market about the recent financial crisis, trying to understand why the economic crisis happened and what needs to be done to fix it can be daunting. There is a real need, and demand, from both the financial professionals and scholars to obtain answers as to what really happened and why.

Lessons from the Financial Crisis brings together the leading minds in the worlds of finance, government and academia to dissect the crisis. Divided into three broad themes – a. The Subprime Crisis; b. The Global Financial Crisis; and c. Law, Regulation, the Financial Crisis, and The Future –  this book puts the events that have transpired in perspective, and details why it began and how it developed as well as its effect on people, implication for our economy, and the broader ramifications for our society.

There are altogether 11 chapters written by experienced contributors, each with his or her own unique insights into this crisis. Topics touched upon include:

  • Particular institutional problems that were revealed in the crisis
  • The role of borrowers, especially subprime borrowers, in formenting the crisis
  • The failures of modern risk analysis and management, and how these essential disciplines can be improved
  • The securitization process and how a new way of originating mortgages affected the mortgage market and the broader financial crisis
  • Regulatory, systemic, and other solutions that can b e implemented to avert future problems


`The Robert W. Kolb Series in Finance provides a comprehensive view of the field of finance in all its variety and complexity. The series is projected to include approximately 65 volumes covering all major topics and ecializations in finance ranging from investments, to corporate finance, to financial institutions.’

Currently, the MBS Library Service has purchased 10 of the published books which are now available in the Eddie Davies Library. For more information about the Robert W. Kolb Series in Finance, click on the link below:

Categories: New Books

Text searching non US company annual reports

1 December 2010 2 comments

Recently, we have been offered a trial by Perfect Information on their PI Navigator database. This trial will end June 2011.

PI Navigator offers 2 unique features which are not present in other database subscriptions that we have: 1. Text searching company annual reports (text searching  US company annual reports is also available in Thomson Research), 2. Corporate action varibles – over 600 items are indexed and searchable.

The ability to text search company annual reports has always been a sought after feature by academic researchers. It allows researchers to cluster certain companies in an efficient way based on certain critiera present in company’s own annual reports. For example, one can use this feature to identify all public listed UK companies which have adopted and clearly inluded corporate social responsibilities policies in their annual reports.

As a rule of thumb, this feature only applies to annual reports that have been issued since 2002.

Perfect Information is listed in the Library’s website (requires central username and password):

Please contact if you need any further help with the database.