Search Results

Keyword: ‘capital iq’

Extending your Excel worksheet formulas via Python, for Capital IQ

17 March 2017 3 comments

Downloading bulk data from financial databases can be a tiring process. You may need panel data structured in a very specific way for analysis in a tool like Stata, and the data provider provides data in a different and limited way; perhaps for one company or one year at a time, leaving you to build it into the shape you need. Each product, like Bloomberg, Datastream, ThomsonONE.com and Capital IQ, provide a different Excel interface. Sometimes you can build a formula for each data item for each year for each company, then fill or copy it across or down to save time. This extended blog post will show you how to do this for the Capital IQ Office Plug-in, and how to save time extending it for panel data using a short Python script.

What kind of data are we looking at?

First, let’s consider the types of variables in turn. In no particular order:

  1. Entities: That is, Companies, Funds, Equities, Indices, Persons…
  2. Time: Usually one of Years, Quarters, Months, Days…
  3. Data items/types: Time series or line items such as Total assets, Turnover, Closing price…

If we consider any one variable or dimension at a time, this is easy; it’s just a list of identifiers or numbers.

  1. AAPL, MSFT, GOOG
  2. 1995, 1996, 1997
  3. TOTAL_ASSETS, NUM_EMPLOYEES, CREDIT_RATING

We can easily create a table to list any two of these dimensions simply.

For example: (fictional) figures for one company:

Item\Year 2001 2002 2003
Total assets ($m) 6.3 7.2 8.9
Net profit ($m) 2.3 4.7 6.1

Or, (fictional) figures for Year 2004:

Company\Item Employees (thousands) Credit rating
AAPL 50 AAA
MSFT 65 AA
GOOG 14 AAA

But if we want to look at three at once, it gets more complicated. More on that later.

Getting data from Capital IQ Office Plug-in

This post assumes you are familiar with using Capital IQ and its Excel add-in. If not, search for other posts on Capital IQ or read the hand-out guides we have written before proceeding.

In short, you can use an Excel formula to pull data from the Capital IQ servers to your spreadsheet. These can be created in the Formula Bulider menu, or typed out manually. For example, to get total assets, the formula will be =CIQ(“IQ18671”, “IQ_TOTAL_ASSETS”, “FY2012”) if the company identifier is IQ18671 and the selected date is financial year 2012. [Note that the double-quote characters appear ‘curved’ here in WordPress and you’ll need to retype them clean in Excel.]

Tip: The data retrieved will only load on a PC with the Capital IQ Excel add-in set up; you will need to copy then paste-as-values, or save as CSV to keep the data for use elsewhere.

Using the fill down/across command

The formula includes several arguments — the options separated by commas — which are “hard coded” – the values for which company, date or item are included as text strings.

Basic Capital IQ formula

The CIQ formula in the yellow cell is shown shown in the formula editor. It references the company in A2, the item “IQ_TOTAL_ASSETS” and the date “FY2012”.

You can replace these arguments with cell references, to a cell that contains the same text, or part of it.

Replacing the item with a cell reference

The yellow cell’s formula now includes a cell reference B$1 for the item type. Note the dollar sign locks the row number when we fill the formula down.

This will allow you to use the Excel feature fill down or fill across, to extend the formula down or right.

Fill the formula down and right

The cell reference for date follows the text “FY” then an ampersand, as the cell B2 contains just the year number. Note the dollar sign in B$2, and now also in the company cell reference $A3, so that the formula fills right correctly.

It will work for any argument, including item or date, although you need to take more care with date. In the example above we use year, and Capital IQ expects the year number to follow the letters “FY” for Financial Year (or “CY” for Calendar Year). We must therefore use the ampersand ‘&’ character to join the strings.

Adding the third dimension

So far we have looked tables to represent two of our three dimensions. There are six permutations for this:

  • Companies down and dates across, for one item
  • Companies down and items across, for one date
  • Dates down and items across, for one company
  • and each of those transposed the other way around.

So if we want to show all three, we have to essentially stack more tables across or down. There are many more ways to arrange the data like this, such as:

  • Companies down, items across, dates across
  • Companies down, date across, items across

It might be easier to show these in a dummy table:

Company ID Item1-2001 Item2-2001 Item1-2002 Item2-2002 Item1-2003 Item2-2003
comp1  a  b  c  d  e  f
comp2  g  h  i  j  k  l

Or

Company ID Item1-2001 Item1-2002 Item1-2003 Item2-2001 Item2-2002 Item2-2003
comp1  a  c  e  b  d  f
comp2  g  i  k  h  j l

You might prefer to stack into panels as below (this is like reshaping from wide to long in Stata). We have to repeat the first column (company ID) for each date… do not be tempted to use merged cells here! If you did, you won’t be able to use data filters, Excel tables or correctly export to other programs.

Company ID Year Item1 Item2
comp1 2001  a  b
comp1 2002  c  d
comp1 2003  e  f
comp2 2001  g  h
comp2 2002  i  j
comp2 2003  k  l

Often we start with a list of thousands of companies. It would be very time consuming to manually insert extra lines for each year after each company! This is where the script comes in…

Extending the process with a Python script

In order to perform a predictable and repetitive task, it can be a good idea to write a short script instead. The method below uses a combination of Excel and an external programming language, Python, and some human intervention. (I assume you have Python installed – the University PCs tend to have it, sometimes as part of SPSS.)

  1. Start with a column of the company IDs in Excel, and save this as a text file. You can do this by copying the column and pasting it into Notepad, remembering to delete any header rows. (You can instead paste it to a new workbook in Excel and save as Text (Tab delimited) (*.txt).) The file must be named ids.txt for the script to work.

    List of company IDs

    Save the list of IDs to a text file ids.txt

  2. Now for the Python script, which is shown at the end of the post for you to copy. Copy and paste this into a text editor like Notepad, although Notepad++ is included on University of Manchester PCs and is much better. If the list of years {1995, 1996, 1997} is not right for you, change it here! Save this as script01.py or similar – note the file extension is .py and not .txt – this is a Python script. Save it to the same directory as the ID file.

    Python code, showing whitespace

    Notepad++ showing Python code (you can copy-paste it from the end of this blog post), showing whitespace/tabs in orange. Python scripts must have consistent line indents, tabs or spaces.

  3. Run the script. To do this, open Windows Explorer, navigate to the folder you have your two new files in and hold Shift + Right-click then choose “Open command window here”. A black command window will open, type “python script01.py” (no quotes) and press enter. Hopefully no error message will show, you’ll get no message if it was successful.

    Run the script

    Shift+Right-click to get “Open command window here”, type “python script01.py” and press Enter. File template.txt is created.

  4. Find the newly created file, template.txt, and open it in Excel. There are several ways to open tab-delimited text files in Excel, any should do.
    Open .txt file in Excel, part 1

    Excel, open file, select type “Text Files (*.prn; *.txt; *.csv)” and choose template.txt. Accept any warning messages.

    Open .txt file in Excel - part 2

    This is a delimited text file, with Tab delimiters only.

  5. You can type the item names in C1, D1, E1 etc., such as “IQ_FINISHED_INV” and “IQ_TOTAL_ASSETS” (no quotes). You can type the template Capital IQ formula in C2, remembering to replace the literal arguments with cell references, and remembering to lock some of the references with dollar signs. For example, =CIQ($A2, C$1, “FY”&$B2)

    Fill the template columns and first formula

    Complete the green cell column headers (item codes). Fill in the yellow cell formula with correct cell references and locks, here it is =CIQ($A2, C$1, “FY”&$B2)

  6. Save the workbook as regular Excel file (*.xlsx). Now select the cell, hover the mouse over the bottom-right corner for the little black square icon, and drag the icon to the right to select that row in all the columns you need. The formula will copy across, and hopefully correctly. You may need to edit it and try again if the dollar sign locks went wrong. Now fill the formula down (just a few rows at first to test, then all the way down by double-clicking the black square icon).

    Final fill out

    Save before you start! Fill the formula right, then down a few rows, then further. Don’t just fill all the way to the bottom if there are thousands of rows, this might overload Capital IQ.

The Python code, which should have indents on the lines in each for loop:

fin = open('ids.txt')
fout = open('template.txt', 'w')
years = {"1995", "1996", "1997"}
# header
fout.write("ID\tYear\n")
#body
for line in fin:
   id = line.rstrip()
   for year in years:
       lineout = id + "\t" + year + "\n"
fout.write(lineout)
fin.close()
fout.close()

Moving on

  • The script is not the best example of Python, but it works! For example, the list of years should really be done better.
  • You could extend the script to include the items and Excel formulas, but for a one-off script, it is probably quicker to do some of the work in Excel by hand (using the fill function).
  • You could chose any other programming language over Python, including VBA which is built into Excel.
  • You could apply this technique to other databases with an Excel interface such as Bloomberg.

Related posts

From ISIN codes to Compustat (via Capital IQ using GVKEY, CUSIP and Name)

17 December 2015 Leave a comment

Given a list of North American company ISIN codes from the Markit credit default swap database (via a lookup database), you may want to join in quarterly company fundamentals from Compustat in WRDS. Compustat does not accept ISIN codes; you have to use an intermediate source such as Capital IQ and one or more intermediate data types (GVKEY, CUSIP and Company Name).

Note, you must be using a PC with the Capital IQ Excel add-in set up. This is in the Library Finance Zone.

Method

  1. Use Capital IQ Office Plug-in (CIQ) with a column of ISIN codes in Excel. Put these in column A.
  2. In the next column, B, get the values of column A with a prefix of “I_” so CIQ can interpret them as ISINs. Cell B2 contains =”I_”&A2
  3. Next get the GVKEY code in column C with a CIQ formula. Cell C2 contains =CIQ(B2, “IQ_GVKEY”) — the output will need cleaning up to remove the “GV_” prefix and any multiple matches.
  4. Then the CUSIP codes in column D. Cell D2 contains =CIQ(B2, “IQ_CUSIP”) — this may not work well, I used the Compustat helpdesk here to get these.
  5. Finally from CIQ, the company names. Column E. Cell E2 contains =CIQ(B2, “IQ_COMPANY_NAME”)
  6. Create a new plain text document with the values of column C (GVKEY) and upload this to Compustat in the usual way.
  7. For the gaps, repeat with the values of column D (CUSIP).
  8. For any final gaps, use the Manually enter company codes > Code Lookup tool in Compustat, typing in the first part of the names to obtain any extra GVKEY codes one by one and repeat step 6.
  9. Stitch together the various Compustat output files and check that all the companies are present without duplicates.

Consider that CUSIP codes can change with time, so use them with caution. Remember to keep any column of CUSIP codes with Text format, never General, Number or Scientific.

Ownership data from Capital IQ

From the research enquiries of students gathering data for their dissertations we know that data on company ownership can be difficult. Recent investigations suggest that Capital IQ from Standard and Poors (S&P), though not perfect, may be a good choice for many research projects.

The usual way to use Capital IQ is to use a screen to select the companies of interest, then add some additional variables, and finally download the results as an Excel file.

The screenshot below shows a company screen with two criteria:

  1. Companies with their primary listing on the London Stock Exchange (LSE)
  2. Companies with a market capitalisation (aka market value) greater than 1,000 million UK £s on 31 December 2013
Capital IQ company screening (click to expand)

Capital IQ company screening (click to expand)

This gives a total of 268 companies – since this is a test we want to be well within the Capital IQ download limit.

Next use the Customize Display Columns tab to add extra information to the results.

There is a good selection of variables that can be selected from the ownership section. For this test we choose:

  • CEO – Number of shares owned [Latest Quarter – 2]
  • CEO – Value owned [Latest Quarter – 2]

The only options for historical data are in terms of latest quarter – the oldest being latest quarter – 40. Selecting the CEO value owned column the results can be reordered to show which CEO’s of large LSE listed companies have the most wealth in their company’s shares.

Capital IQ ownership results (click to expand)

Capital IQ ownership results (click to expand)

Downloading the results is just a question of selecting Excel and go in the export section just below the screening results header, and waiting while Capital IQ formats the results and returns a download link.

The selection of Latest Quarter – 2 was an attempt to choose the Q4 2013. However, advice from the Capital IQ customer help desk suggest that this will give Q3 2013. The ownership data is updated when the company files the relevant data, usually 4-6 weeks after the end of a quarter. On 16 July we are just in Q3 but the Q2 data will not be available yet. This makes the Latest Quarter Q1 2014, and so Latest Quarter – 2 is Q3 2013.

 Alternatives:

There is company ownership information available in other databases, but they do not handle the combination of a list of companies and historical data as easily as Capital IQ

  • Thomson Reuters 13F data from WRDS (only data for  US companies)
  • Bloomberg – good for the ownership of one company
  • Thomson One.com –  detailed ownership for worldwide companies but with restrictions on the number of companies that can be investigated at one time, and results often need significant reformatting.
  • Fame, for UK and Irish companies, and Amadeus, for large Eur0pean companies, – can work with lists of companies but variables restricted to the owners holding the most shares.

Company ownership information may only be available for the last few years, or if historical information is available then it may be restricted to the largest shareholders.

Acknowledgements are due to Phil Reed and Xia Hong who explained how to use Capital IQ and noticed to availability of ownership data.

Categories: company information Tags:

Capital IQ Common pitfalls and webinars

19 June 2013 Leave a comment

Capital IQ webinars listS&P Capital IQ provides regular training webinars which are free to join. Simply log into Capital IQ in the usual manner and go to the Help link at the top-right.

There is also a video archive of all the past topics you can choose from. These include:

  • 5 Common Pitfalls to Avoid in Screening
  • Assessing Counterparty Credit Risk
  • Corporate Valuations Series
  • New Portfolio Templates
  • Value Investing Using S&P Capital IQ
  • Excel Trilogy: Basic/Intermediate/Advanced

Capital IQ webinar Java warningYou will need to have Java installed, and to allow it to run, and to accept the warning messages.

It should work on Windows and Mac OS in any modern web browser, but please leave a comment below if you find otherwise.

Please enter your University email address and your name when accessing these resources. Remember we only have an extended trial subscription for Capital IQ here at The University of Manchester Library so only one person can use it at a time.

The most recent webinar was titled “5 Common Pitfalls to Avoid in Screening” and we shall look at the first example from it.

Linking criteria and identifying a company as public on a given date in history

Capital IQ screening (linking)

In this example we want to screen for a list of public materials companies that closed an M&A deal in the last 12 months with total revenue greater than US$1,000,000,000 one day prior to the closure of the deal.

  1. Begin by choosing the Screening tab in the menu bar and clicking on Companies.
  2. The first criteria must return no more than 1,000,000 results. From the first column, choose Company Details > Industry Classifications and click on Materials, then Add Criteria.
  3. From the last column, choose M&A Details > Dates then in the list choose Closed Date, with the Time Frame: Last 12 Months. This metric refers to a transaction, not to a company, but we can still use it for screening. Click Add Criteria.
  4. We now want to identify the company as a public company. It might be tempting to go for Company Details > Company Type: Public Company, but that would apply to the status today, not on the date of the transaction. To get the status on the date of the transaction, you need a metric relating to the Transaction (about the target company).So again from the last column, choose M&A Details > Valuations and in the list choose Pre-Deal Share Performance with the Data Point: Target Market Cap 1-Day Prior  and the Value: Greater Than 0. This means the company was publicly listed. Ensure that Target/Issuer is displayed in the box below, and choose Type: AND from the radio buttons at the bottom. Click Add Criteria.Why Type: AND instead of Type: New (the default)? Because this criteria is part of a Transaction detail, and we are in Company Screening, we need to combine all the Transactions criteria together, using logical AND. It is a bit confusing, as all the criteria are combined with AND rather than OR, but it is essential when linking these types of screenings.
  5. Finally, to limit to target companies with Total Revenue greater than $1 billion at the time of the deal and not at the present day, choose from the last column M&A Details > Valuations then from the list Target/Issuer LTM Financials (at Announcement) with the Data Point: Total Revenue, Value: Greater Than 1000 (the units are millions of US Dollars). Again, ensure Target/Issuer is displayed below this and choose Type: AND to link it to the Transaction criteria. Click Add Criteria, then View Results.

The most important thing to take away from this is to consider using Pre-Deal Share Performance with the Data Point: Target Market Cap 1-Day Prior and the Value: Greater Than 0 to identify a company as public from a transaction, using the Type: AND operator after choosing a deal date.

Converting company IDs 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:

picture1

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.

Capture3

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’:

picture4

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

 

 

 

 

Categories: Uncategorized

Searching for Market Research Content

Locating Market Research content is less straightforward than company financial data – many databases provide this coverage (Thomson ONE.com, Bloomberg Professional, Capital IQ, Fame, Amadeus, Datastream – to see posts which include content on these, scroll down and click on the ‘Tag Cloud’ links on the right hand menu).

However, students often aren’t even aware specific databases exist to provide Market Research content, such as company/industry reports, analyst evaluations and market news.

With a systematic approach, searching in all Market Research databases which may have relevant content, is worthwhile. This is because the way research has been framed (i.e. what it looks at: online sales, but not sales from physical stores, for example) means it may not be an exact match to the enquiry (student assignment: essay or project), but nevertheless can be applied to it in some way, which still proves useful. In essence, many sources provide numerous small pieces of the puzzle, which come together to build a strong argument, to answer the enquiry.

Even if a student does locate a report which exactly matches their enquiry, the notion that a single report or source is sufficient should be dispelled. This would typically be spelt out in assessment criteria or marking scheme, employed by educational establishments. For example, ‘wide background reading’ [suggesting multiple sources]; ‘appropriate examples’ [plural: i.e. not just one large example/case study]; ‘good support and justification’ [material obtained through research provides the foundation from which analysis can be undertaken, to produce a successful piece of work]. It would be more appropriate to view such a report as the starting point – like a plant’s roots beginning to grow and branching out in different directions.

A number of databases are available which can provide useful Market Research content, such as:

Thomson Research (International focus: analyst reports at Company and Industry level).

Mintel (UK focus: with reports, news, analyst insights).

Frost & Sullivan (International focus: market insights, technical insights reports, industry research reports).

Passport (International focus: category briefings, statistics [company/brand market shares]).

Freedonia (International focus: reports – browse by industry [US] / country/region).

Factiva (International newspapers and trade journals [a publication linked to a particular industry – so different to an academic journal]. Can also be useful in providing background coverage for Mergers and Acquisitions deals (a deal is announced, how it progresses over time and finally completion).

Perhaps the best in terms of reports, due to the number and depth of coverage is Thomson Research, which is examined below.

 

Thomson Research

 

Coverage: International market content, providing Analyst Reports at Company and Industry level.

Access: This database is designed to work with the Internet Explorer browser. In addition it is advisable to select Tools (the small cog icon at the top right of the screen) – Compatibility View Settings, then click on the ‘Add’ and ‘Close’ buttons, so all search features work as they should. Note: If other browsers, such as Chrome or Firefox are used, search features will not work as expected.

Searching – Analyst Reports – Company Level.

These reports provide an assessment of the factors affecting companies and industries, which may cover financial performance data, and some or all of the PESTLE (Political, Economic, Social, Technological, Legal and Environmental) factors which could impact on current and future performance. This is perfect for student projects, in that they represent the same process the student is going through. That is, to research relevant content and provide an analysis based on this, to complete their assignment.

This is the default search screen [Research – Full Search]. All search and display options are contained on a single page.

 

Analyst Reports - Search Screen

Analyst Reports – Search Screen

 

Searching Tip #1:  Although there is a dedicated ‘Company’ field, it is often more effective to enter the company name in the ‘Title’ field, which searches within report titles. This is because a company’s official name can be different to that in common usage, meaning if this ‘common usage’ version is entered in the ‘Company’ field, no results are returned. For example, Royal Bank of Scotland, often abbreviated to RBS, has the full name of The Royal Bank of Scotland Group PLC.

Searching Tip #2:  Sort Results by ‘Report Date – Descending’, is preferred, as this will show the most recent reports at the top of the list. This option can be selected from the drop down menu (bottom left of screen).

The other search options are straightforward. For this search: Collection = Investment Research, Report Type = Company, Report Date = Last Year, Title = Tesco PLC, then click on ‘Search’.

This search resulted in 183 reports, which is perhaps too many. Fortunately, it is quite quick to change the search by clicking on the ‘Edit Search’ button. Making use of a phrase (two or more search terms enclosed in quotation marks) in the Text field (i.e. content from the main body of reports) can be very effective. In the modified search, “online sales” have been added in the Text field.

 

Phrase search to refine results.

Phrase search to refine results.

 

This reduced the number of results to 5 reports. Click on ‘Tesco PLC’ report title (highlighted).

 

Results List

Search Results – Reports

 

Click on ‘View’.

 

View report content

View content

 

Click on ‘Submit’ to proceed:

 

Submit request to display report

Submit

 

The full text is then displayed in PDF format. For a large report, the PDF format is helpful, with the option to search for particular terms inside, using the ‘Find’ search box (Control – ‘F’ key), which appears at the top right of the screen.

 

Tesco Analyst Report

Tesco Report

 

The same search principles apply to ‘Industry’ level reports. The obvious difference being that Report Type is now ‘Industry’.

 

Choice of Search Terms

 

The value of being able to think of alternative search terms is that it is then possible to obtain new results. For example, a search which is too general will give many results. For an Industry level search, using just ‘Grocery’ in the Title field produces over 100 reports. By adding the phrase, “Tesco Sales”, in the Text field, this gave one result (‘UK Food Retail: Brexit Impacts Grocery Volumes Negatively’). Using an alternative Title search (UK Grocery) produced 10 reports, which is a reasonable number.

Additionally, alternative search terms can be obtained by looking at a dictionary definition of a particular term or a thesaurus. Sometimes search terms can only be encountered by looking within Market Research reports. For example, when the relative contribution of Internet and physical store sales is considered, certain terminology tends to prevail. So, ‘Online’ rather than Internet, and ‘Bricks and Mortar’, rather than physical stores.

 

Summary

 

Awareness of Market Research databases is a necessary first step to making use of these valuable resources. The databases listed above can provide useful content, to enable effective analysis by students, contributing to a successful piece of work.

 

Related post:  Company and Industry Comparisons   [Date: 10 July 2015].

 

The above databases are available to current students and staff of The University of Manchester.

Categories: Business Databases

Historical Index Constituents in Bloomberg

12 April 2016 2 comments

This post follows on from Historical Index Constituents (e.g. S&P 500) written in October 2014.

It is easy to find the current constituents for an equity index such as the FTSE 100 or S&P 500 but finding the historical members for a given date or range of dates requires more work. This blog post shows briefly how to use four screens in Bloomberg.

Bloomberg screen SECF

Use Security Finder SECF to find the code for the Equity Index. Choose Category: Index/Stats and Sub-Category: Equity Index. You can type part of the name or description to further filter the list.

In the Bloomberg search bar, type ‘SECF<GO>’ (that is, SECF then the Enter key) to use the Security Finder to find the Equity Index you need. You could just type the name of the Index in the search box and see if the correct Index is suggested. In this example, click on SPX Index (S&P 500 Index). A menu will appear, choose Member Weightings MEMB.

Bloomberg screen MEMB

For an Index, the MEMB screen shows constituent members (and weightings) for a given date. You can add extra Fields such as ISIN or CUSIP identifiers to use later.

You can get straight to the Member Weightings screen if you know the code of the index. Type, for example, ‘SPX <Index> MEMB <GO>’ (that is, SPX then the F10 key then MEMB then Enter). The date can be changed in the ‘As of’ box at the top-right and extra columns can be added. From here, on the red menu, click Output > Excel to export the data to a new Excel document (requires the Excel add-in to be configured, see desktop icon “Install Bloomberg Office Add-ins” at Manchester).

Bloomberg Export MEMB to Excel

Open the output file, accept a warning message about file types.

If you requested ISIN or CUSIP identifier numbers, these can be used in other databases such as Datastream, Compustat or Capital IQ.

Getting constituents for a range of dates

Getting the membership for the start date and the end date in your range will obviously not consider any company that joined and left in the intervening time. It is common to collect the membership at month end for each month in the range, paste the results into one long list in Excel then use the Data > Remove Duplicates function to get a tidy list of each company once only.

Bloomberg output without duplicates

(1) Export each month’s constituents. (2) Copy and paste the Tickers into one long column for all months (without copying the heading ‘Ticker’ each time). (3) Select that column and choose Data > Remove Duplicates.

You can do this and keep all the columns (name, weight, ISIN etc.) but the image above just keeps Ticker. You can get all the other data again using the Bloomberg Excel add-in if you wish (with formulas such as =BDP(“AAPL US Equity”, “ID_ISIN”) where the first parameter is a reference to the cell with the Ticker).

Is there a way to do this more easily in Bloomberg? Well, yes and no. There is a screen in Bloomberg called Historical Index Member Changes CHNG.

Bloomberg screen CHNG

Historical Index Member Changes for a given Index and date range. But only works for some indices and the report cannot be exported.

For the FTSE 100, type ‘UKX <Index> CHNG <GO>’. Choose the date range and click the top ‘Enter 1<GO> to generate report’. A report will be generated and it will tell you the number of the report. Go to the Retrieve Reports screen RPT.

Bloomberg screen RPT

Retrieve reports generated in Bloomberg. View these on screen or save one page at a time to an image or printer.

You will see a line in the report for each member’s entry and exit to the index, with more detail. But you cannot export the list of member tickers to Excel or do anything else systematic like that. It will be better to use the MEMB screen and export each month.

Do you know a better way? Please let us know by commenting on this post.

See also Historical FTSE100 Index Constituents on Datastream, dated July 2012.

Categories: Business Databases Tags:

Company Employee Data Using Different Databases

24 February 2016 1 comment

How many employees work for a company?

This would appear to be a simple question. But perhaps not…

Although the company in question will have a more exacting answer as to values at any particular point in time, this would be considered commercially sensitive information. Therefore, values are typically made available at a single point in time, such as the end of the company’s reporting period. This is then accessible via a number of business databases.

Unfortunately, if you look in different databases, you tend to get different answers – for example, looking at Tesco PLC, which is a publicly quoted UK company, a number of values are specified depending on the database consulted:

 

Bloomberg Professional

This shows 386,086 employees and helpfully specifies a date point of 28 February 2015, but displayed in US date format [02/28/15]. This reflects Tesco’s reporting period dates – essentially the start of March to the end of February.

Bb Tesco EE

 

Thomson ONE.com

This gives a different value [506,984] but specifies the same date – the end of the reporting period.

T1 Tesco EE PDF

 

Datastream (Thomson Reuters)

The datatype is ‘WC07011 – Employees’ and the definition notes: Employees represents the number of both full time and part time employees of the company. It excludes: Seasonal employees, Emergency employees. Footnotes:  D. Average employees.  This gives a value of 506,984.

Datastream Tesco EE

 

Fame

This specifies a value of 386,086.

Fame Tesco EE

 

Capital IQ

When searching for Tesco PLC, the ‘Number of Employees’ is stated as: 517,802.

Capital IQ Tesco EE

 

Company Annual Report

Perhaps one way to obtain clarity is to look in the ‘Tesco Annual Report and Financial Statements 2015’, accessible through Thomson Research or PI Navigator databases, for example.

Page 20, shows the following breakdown, for a total of 517,802 (220,257 + 297,545).

ARS 2015 Tesco EE p20

If you were to stop at this point in the Annual Report, you would miss further details. Hence, on page 100, there are additional details:

 

ARS 2015 Tesco EE p100

This helps to show which values the various databases have selected. Thus, Capital IQ has used the combined values [220,257 + 297,545 = 517,802] from page 20 of the Annual Report, in contrast to the other databases.

Thomson ONE.com/Datastream have used the ‘Average Number of Employees’ measure [506,984] and Bloomberg/Fame have used the ‘Average Number of Full-Time Equivalents’ [386,086] for 2015 – that is, the end of the reporting period, specifically 28 February 2015.

 

The lesson to be drawn when using data from a particular database – always quote the source, to ensure clarity and remove uncertainty. In the case of Employee data, the company’s Annual Report is the ultimate source.

However, the difference in total values between page 20 [517,802 (220,257 + 297,545)] and those quoted on page 100 [506,984 and 386,086] is a mystery. Specifying a page number in your reference is therefore recommended!

 

Bloomberg Professional, Datastream, Thomson ONE.com, Thomson Research, Fame and Capital IQ are databases available to current students and staff of The University of Manchester.

 

Compustat CUSIP Converter – limited use tool and some tips

16 December 2015 Leave a comment
Compustat CUSIP Converter

Converts CUSIP codes to CUSIP codes… it’s (somewhat) more useful than it sounds.

Most of our users who need North American company financial information need to link it to another data source, often with a different type of company identifier. A tool has appeared within Compustat via WRDS (which may have been there for some time) allowing you to convert either 8 or 9-digit CUSIP codes into 6, 8 or 9-digit ones. This is a bit of a surprise to me, as Compustat works best with GVKEY codes, but the different length of CUSIP codes help it to interface with other databases as follows.

  • Bloomberg (9-digit CUSIP)
  • Capital IQ (GVKEY has better coverage)
  • CRSP (8-digit CUSIP)
  • Datastream (via Local Code)
  • Eventus (8-digit CUSIP)
  • SDC Platinum (6-digit CUSIP)
  • ThomsonONE.com (9-digit CUSIP when using the Excel add-in)

The databases you join with may not have the full coverage of CUSIP codes, so take care. There will almost always be gaps when going from one database to another.

Take scientific care of CUSIPs in Excel

A final tip when using Excel. Some CUSIP codes can be interpretted as numbers in scientific format (when the code contains numerals and the letter ‘E’, such as Genuity Inc 37248E202 read as 3.72 x 10206). Some CUSIP codes begin with leading zeroes (Apple Inc is ‘037833AL4‘) which can get lost and change meaning.

You must always make sure that a column of CUSIP codes is formatted as Text not General before you paste them in. When opening from a CSV file, consider using the Data > Get External Data > From Text menu command and explicitly set the CUSIP column as Text format.

See also our earlier post on the CRSP converter tool:

Using business databases with Microsoft Office 2016 for Windows

13 October 2015 Leave a comment

Last month saw the release of the latest version of Microsoft’s ubiquitous Office suite for Windows, named version 2016. It brings new features and a design more in line with that of versions of Office released for iOS and Android. Many students beginning their studies this month will arrive at university with this version of Excel on their computers.

This is not a concern for users of the most specialist financial databases at The University of Manchester Library as the databases are installed on cluster PCs which will remain on Windows 7 with Office 2010; it may become a matter of concern for those at other institutions who are responsible for PC updates in the coming months.

I have run a few tests installing Thomson Reuters Datastream Advance for Office, Capital IQ Office Plug-in and Thomson Reuters Spreadsheet Link on Windows 7 and Windows 10 systems with Office 2016, specifically to check if their Excel add-ins work correctly. (I was not able to test Bloomberg due to its more limited restrictions.)

Datastream in Excel 2016 for Windows 7

Datastream in Excel 2016 for Windows 7

Datastream

Datastream in Excel 2010: The version of Datastream that is most used at The University of Manchester is Datastream Advance for Office (DAO), officially supported by Office 2007 and 2010 32-bit versions and no newer. (There is a version for 64-bit Office which I have not tested, called Datastream for Office (DFO), and a Datastream charting add-in, also not tested.)

Datastream for Excel 2013 in Windows 10Datastream in Excel 2013: Although Office 2013 is not officially supported for DAO, I managed to install it on a Windows 10 32-bit tablet and got a Request Table to work. (Tip: save the Request Table document the first time it asks you to, otherwise you will get into trouble with macro-enabled filetypes later.) I have seen the previous version of DAO, designed for Office 2003, working with Office 2013 32-bit on Windows 8.1 64-bit (sorry, screen shot not available).

Datastream install fail: AFOHelpDatastream in Excel 2016: Although Office 2016 is also not officially supported for DAO, I managed to install it on a Windows 7 32-bit PC and got a Request Table to work. I could not install it on a Windows 10 64-bit PC, using Office 2010 or Office 2016; it gave error messages such as “Compile error in hidden module: AFOHelp”.

In summary: Datastream works for me in Office 2016 with Windows 7 32-bit and possibly in some other situations but more testing is required.

Capital IQ

CIQ Excel 2016 data failI had a mixed but unsuccessful experience using the Capital IQ Office Plug-in. On a Windows 10 PC where I had already installed the software to use with Office 2010, running the Excel add-in on Excel 2016 would let me load the Capital IQ ribbon tab, access the template library, but it would not download any real data. I tried the usual troubleshooting methods but could not get the data to work.

CIQ Office 2016 install failWhen I tried to install Capital IQ Office Plug-in on Windows 7 where Office 2016 was installed, I got an error message that I could not work around: “Unsupported Office Version”. I guess Office 2016 is too far ahead of Office 2003 for it to recognise, even though I had Office 2010 installed as well.

I have not tried to contact their help desk yet; perhaps there will be a newer version released to address this. I am hopeful for this with Capital IQ; there will not be an update for either of these Thomson Reuters products tested today.

Thomson Reuters Spreadsheet Link

TRSL Office2010 Windows 10 failUnfortunately I was unable to run Thomson Reuters Spreadsheet Link (TRSL) in Office 2016. It is not officially supported in anything newer than Office 2010 so I was not expecting it to start working again in Office 2016, but running on the Windows 10 PC Office 2010 showed part of the Excel add-in loading; perhaps it had worked on that same PC a few months earlier.

TRSL in Office 2016 Windows 7 install failWhen I tried to install TRSL on a Windows 7 PC with Office 2016 installed, I got a message like with Capital IQ, stopping the installation from taking place at all. In short, it doesn’t work.

Summary

Many of the archive specialist database platforms that researchers use are dependent on old and ageing pieces of software. It is asking a lot to expect software written over five years ago to continue working in successive releases of the Windows operating system, and an even harder challenge to make it work within multiple Office environments on each of those platforms. Sometimes these old applications would require major redevelopment to work, sometimes just a tiny tweak. If that tiny tweak requires a disbanded team and development environment to be resurrected, it’s just as unlikely to be fixed as a major job. Sadly, the Thomson Reuters Excel add-ins fall into one of these categories. We will have to wait until Eikon is ready for wider use in education, hopefully before the Windows 7 operating system reaches the end of its life for receiving security updates in January 2020.

See also: Using business databases with Microsoft Edge on Windows 10