Welcome to Business Research Plus

27 July 2011 1 comment

Business Research Plus header 2015

From specialist databases to business literature, Business Research Plus provides advice and tips based on The University of Manchester Library Business Data Service resources and expertise.  See our About page for more details.

  • Library Research Plus provides expert insight from The University of Manchester Library’s Research Services for researchers in all disciplines including business.
  • My Learning Essentials, the Library’s award-winning skills progamme includes online resources with a wealth of useful tips on searching, referencing, writing to support personal and professional development.

Exploring our resources – try the Business and Management Resources page (Subject Guides),  scroll down to category or tag cloud in the right-hand column or search this blog (top right). For latest news see @UML_BDS 

Advertisements

Getting a linking table from Compustat via WRDS Cloud using SSH and SAS

27 June 2017 Leave a comment

Researchers commonly need to combine data from multiple sources. Even when the sources are offered through the same platform such as Wharton Research Data Service (WRDS), this can still be a difficult process with many choices. Essentially, you need to find a common entity identifier in both sources; if none exists, find a reference table or other third source.

The help provided by WRDS is often sparse in detail and tailored for their more advanced users. I have previously written in detail about using WRDS Cloud and SAS to gain access to resources not available on the WRDS website, in the blog post Getting S&P 500 constituents from CRSP via WRDS Cloud using SSH and SAS.

In this post, I will suggest a way to link I/B/E/S data with Compustat North America data. I will summarise the points that are addressed in the aforementioned blog post.

Get a linking table from Compustat North America

You will need the following to begin.

  1. AUTHENTICATION. Your WRDS username and password, specifically an individual PGR or Academic login.
  2. SENDING COMMANDS. Windows users: the Putty program. Mac/Linux users: the Terminal app or command line.
  3. FILE TRANSFER. Windows users: WinSCP or FileZilla. Mac/Linux users: CyberDuck or FileZilla or command line.
  4. FILE EDITING: Notepad, Notepad++ or another text editor. Excel or other spreadsheet application.

The method is very similar to getting the S&P 500 constituents. Essentially, Compustat has a dataset called securities which lists all the identifiers for all the securities. It is in SAS format. It cannot be accessed directly by us, but we can write a SAS script to read its entire contents to a plain text file, then copy the file to our own PCs. This file is kept at location /wrds/comp/sasdata/d_na/security/security.sas7bdat for North American data and is updated often.

Your script to read this file should look like this:

options nosource nodate nocenter nonumber ps=max ls = max;
title ' ';
options source;
proc print data=comp.security noobs width=UNIFORM;

This is different from last time in a few ways.

  1. The ‘ls’ or ‘line size’ option has been changed from ’72’ to ‘max’. This is because there are more columns for Compustat securities than for S&P 500 constituents.
  2. The data table we are reading from is ‘data=comp.security’.
  3. The ‘labels’ option has been removed so we get the clean variable names as the header.
  4. The ‘width’ option is set to ‘UNIFORM’ so the columns are the same number of characters wide on each ‘page’.

The method is as follows; remember to refer to the aforementioned blog post.

  1. Save the SAS script to a new text file on your computer, compsecurity.sas or similar.
  2. Use WinSCP or equivalent to copy this file to your own space on WRDS Cloud.
  3. Use Putty or equivalent to run the script, log in and type qsas compsecurity.sas and press Enter.
  4. Wait 10 seconds and use WinSCP to download the generated files compsecurity.log and compsecurity.lst to your computer. The ‘lst’ file contains the data, if the program was successful (read the ‘log’ file to check).
  5. Use Notepad++ or equivalent to remove the blank lines from the top of compsecurity.lst and save it as compsecurity.txt – then check every 32,764 lines for a few more blank lines and repeat of the headers to be removed (these are ‘page breaks’).
  6. In Excel, open compsecurity.txt (via Data > Get External Data from Text) and use fixed column width option, carefully setting the locations for the column breaks, remembering to set most columns as Text format instead of General (particularly GVKEY and CUSIP). Save as CSV or Excel format.

Compustat securities via WRDS Cloud

Using the linking table

You will need the columns GVKEY and IBTIC (IBES Ticker).

  • If your Compustat and I/B/E/S data are in Stata format, import this table and consider joining the tables.
  • If your Compustat and I/B/E/S data are in Excel format, use VLOOKUP formulas (some more processing may be required).

Still to do: there is not necessarily a one-to-one match between these two identifiers. The same GVKEY will appear on more than one row, but will those rows contain more than one unique ticker? And is the data frequency the same in each source? You will need to find the answers to these questions out for yourself.

Company Screening in ‘Capital IQ’

A request for information from an academic (or student) can appear straightforward, but may be challenging, when all the resources available are not known, or fully understood.

Consequently, on receiving a request for a list of Chinese companies, in particular industries/sectors, with an employee count of between 100 and 500, a number of potential options present themselves. Namely, Thomson ONE.com, Bloomberg Professional and Capital IQ. With the primary emphasis on publicly quoted companies for the first two databases, that leaves Capital IQ, which also covers private companies.

 

Clear Navigation

 

The drop down menus across the top of the search screen reveal options quickly and simply. The option for searching – or screening – companies, to create a list according to the criteria specified, can be accessed by hovering over ‘Screening’, towards the top left.

 

Company Searching

Capital IQ – Company Screening

 

Criteria Selections

 

Once ‘Screening – Companies’ is selected, a number of standard options are presented, within ‘Most Used’ and ‘Company Details’. Certain options would appear to cover broadly similar areas. For example, ‘Geographical Locations’ and ‘Country of Incorporation’. Also ‘Industry Classifications’ and ‘Business Descriptions’. These options can be inserted into separate searches to obtain different results, to gauge which most closely matches the requirements of a research project.

Geographical Locations.

 

Location Searching

Screening: Geographical Locations

 

Searching for the country required: in this case ‘China’, then select from the options presented and click on ‘Add Criteria’, to begin the search process. This produces 375,994 results.

 

Country Search and Select (China)

 

Next, Business Descriptions:  search for ‘Engineering’, then click on ‘Add Criteria’.

 

Engineering

Business Description (Engineering)

 

This greatly reduces the number of results as both criteria have to be true (the ADD operator in Boolean searching), giving 5,889 companies.

 

Country and Industry

Results (China and Engineering)

 

Where a required data item does not appear within the standard list, it is possible to search for possible alternatives. In this case, employees. Type ‘Employees’ in the search box, hover over the required selection (‘Total Employees’) and then move along the line to select ‘Open Criterion’.

 

 

Search for Employees

Employee Search

 

The specific details can then be entered. That is, a range of between 100 and 500 employees working for companies, in the Last Quarter. Click on ‘Add Criteria’ to search.

 

Number of Employees

Employee Search

 

This shows 42 results.

 

42 Companies

Results (42)

 

Results

 

The culmination of Geographic Locations (China), Business Descriptions (Engineering) and Total Employees (100 to 500) combine to give 42 results – companies.

 

List of Companies

Results List

 

Click on the ‘View Results’ button to list the companies. Clicking on the company name provides additional details. For export, click on the ‘Excel’ button and ‘Go’.

 

List to Excel

Export to Excel

 

The Excel export option is straightforward, providing tabs for search results (Screening).

 

Results screen in Excel

Excel – Screening tab

 

The criteria for the search are set out in the ‘Screen Criteria’ tab.

 

Search Criteria (Excel)

Excel – Screen Criteria

 

Summary

 

The Capital IQ Screening feature is easy to use and flexible. In terms of results, these are quick to display on screen or alternatively export to Excel.

 

Related posts for Company Screening include:

Advanced Company Screening in Bloomberg Professional   [31 March 2017]

Top 10 Companies   [16 December 2016]

 

S&P Capital IQ, Bloomberg Professional and Thomson ONE.com databases are available for use by current students and staff of The University of Manchester.

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:

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

A related post: Company Identifiers in Datastream

 

 

 

Categories: Uncategorized

Advanced Company Screening in Bloomberg Professional

Students often enquire about creating a list of companies in a particular area…sector of the economy, or industry.

 

Equity Screening

 

The Equity Screening function [EQS <GO>] in Bloomberg is an obvious starting point. This allows a number of criteria to be specified, to build a search, step by step.

 

When ‘Country of Domicile’ is selected, the expanding menus on the left hand side of the screen give a breakdown from geographical regions to individual countries. However, it is actually faster to type in the country name in the search field, in this case, ‘United States’.

Click ‘Update’. This gives 12,455 companies.

 

Repeat for ‘Sectors’. Here, the expanding menus can come in useful, as you may be uncertain of the category names to enable a keyword search. A recent student enquiry requested US ‘Medical Equipment’ companies. This was located by expanding the options, starting with ‘Health Care’ – and then selected. Click ‘Update’ to incorporate ‘Medical Equipment’ into the search. This gives 202 companies.

 

Results

EQS Results Summary

 

Click on ‘See Results’ to list the companies:

 

List

EQS Company List

 

Whilst there is an icon at the top right of the screen to Export the results to another application, it can be more effective to save the list of companies through the ‘Actions – Save’  drop down menu. Enter a file name (‘US Medical Device Companies’ specified here), press the Enter/GO key to activate the ‘1) Save’ button, and then click on ‘1) Save’.

 

Save

EQS: Actions – Save

 

Excel Add-In

 

To gain access to the results from the EQS search, for additional research, the Excel Add-In interface for Bloomberg provides a number of dedicated search options. The ‘Import Data’ feature is particularly useful. This gives a number of Wizard-type options, with standard screens allowing search selections to be entered. For example:

Import Data  –  Real-Time/Historical  >  Historical End of Day

 

Step 1: Create a List of Securities.

 

Import Data

Import Data search screen.

 

The ‘Select securities’ section allows the source to be specified from a drop menu of options. This includes Indices and EQS – Equity Screening. Once EQS is selected, the field below (Saved Screens) gives access to file names saved – in this case ‘US Medical Device Companies’ – normally at the bottom of the list. This loads the full list of companies to the ‘Available securities’ window. From here, the complete list can be selected (‘Add all’) and copied into the ‘Selected securities’ window, or a subset, then ‘Add’ – as is the case in the screen shot below.

 

List

Step 1: Security List

 

Click on the ‘Next’ button to proceed:

 

Step 2: Create List of Fields.

Whilst there are a series of expanding menus [New Fields, Analysis, Corporate Actions, Descriptive, Earnings Estimates, Fundamentals, Market Activity, Ratings] to reveal the data items available, it is actually more effective to search by keyword(s). To illustrate, ’employees’ has been used as a search term, to locate the ‘Number of Employees’ field. Once selected, a definition is provided below. Click ‘Add’ to copy into the ‘Selected fields’ window.

 

Field

Step 2: Field Selection

 

Click ‘Next’, for Date and Frequency options: Yearly, back to 2010 specified.

 

Step 3: Select Perodicity and Time Frame.

 

Dates

Step 3: Dates / Frequency

 

The next two steps (Currency, Pricing Defaults) are not relevant to this search, so click ‘Next’ to proceed to

Step 6: Layout Options.

 

Layout

Layout Options

 

Click on ‘Finish’ to display results – it may be necessary to widen columns.

 

Results

Results in Excel

 

Although a simple example, this could include many additional data items for each company, for which Excel is well suited.

 

Bloomberg Professional is available to current students and staff of The University of Manchester.

Comparing Company Share Price Performance

Datastream is useful to be able to search for multiple companies (Series) and datatypes, in terms of quantitative data.

For example, Tesco PLC, J Sainsbury PLC and Marks & Spencer Group PLC are UK supermaket groups, each within the FTSE100 Price Index. One measure of company performance is Share Price. This quantitative data can be represented in chart form.

 

Search Procedure

 

Data Category is the starting point. For company related data, this would be ‘Equities’.

For Analysis, this would be ‘Time Series Data’.

The Series can be identified by using the Navigator function, activated by clicking on the ‘Find Series’ button. The code displayed in the Navigator search, when selected, gets copied into the search screen. For example, TSCO for Tesco PLC.

The Datatype, for Share Price, searchable via the Navigator (Datatypes button) is ‘Price (Adjusted – Default)’, represented by the code: ‘P’.

Time Period can be selected from set date ranges, such as ‘-10Y’, which represents the last 10 years. Alternatively, a specific date range can be entered by clicking on the ‘Time Period’ button, giving the option to enter a Start and End date using the format:  DD/MM/YYYY.

The frequency of data can be selected by clicking on the Settings button. This allows choices of ‘Daily, Weekly, Monthly, Quarterly and Yearly’, with a default of ‘Daily’. Daily is normally used for Share Price data.

Finally, click on the Run Now! button to execute the search.

The results of this search for Tesco PLC Share Price data, in Datastream 5.1, gives the following output:

 

Datastream Search.

Datastream 5.1 Search

 

It is then possible to add this data into a chart view, by selecting Multiple Series/Flexible Chart, then Comparison Line Chart then Rebased.

By clicking on the plus [ + ] symbol, the Series is copied into the chart summary box on the left of the screen.

Go back to ‘Time Series Data’ Analysis option, run the search for the next company’s Share Price and repeat the process, to include all those required in the chart summary box:

 

Analysis - Charts

Datastream – Analysis: Comparison Line Chart (Rebased)

 

Execute the search by clicking on the ‘Run Now!‘ button at the top right of the screen.

 

Rebased Chart

Datastream: Comparison Line Chart (Rebased)

 

By virtue of specifying ‘Rebased’, this represents the different series as an index, beginning at 100. This allows a relative view of performance. Hence, a movement from 100 to 95 represents a 5 per cent decrease in the share price of a company. All three companies are showing a value of about 40 (as at 24/3/2017), indicating they have lost approximately 60 per cent of their value, as expressed by Share Price, over the last 10 years.

 

Price Index Comparison

 

By comparing a number of companies within a particular Price Index, it is possible to get an appreciation of how the companies have performed relative to the whole index. Tesco PLC, J Sainsbury PLC and Marks & Spencer Group PLC are all part of the FTSE100 Price Index.

Using the Analysis option ‘Comparison Line Chart – Rebased to First’ gives a scale relating to the first series (FTSE100). To do this, run a search for the FTSE100 data first and subsequently the companies also part of the index. Changes to the search above include:

Data Category: ‘Equity Indices’, Datatype: ‘Price Index’ and run the search for data.

Next, change back to the settings for Companies (Data Category: Equities, Datatype: Price (Adjusted Default), run the searches for data and add to the chart summary box [ + ].

 

Chart summary box.

Datastream: Chart summary box.

 

Next, click on the ‘Run Now’ button to execute the search.

 

Datastream Chart.

Datastream: Price Index vs Companies (Line Chart Rebased to First).

 

This chart gives a clear graphical representation of the relative performance of company Share Prices and the FTSE100 Price Index. The competition from low cost retailers such as Aldi and Lidl is a factor in the poor recent performance of Tesco PLC and J Sainsbury PLC in particular, reflected in the above chart.

 

Datastream is available to current students and staff of The University of Manchester.

 

 

Categories: Business Databases Tags:

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

Company Financial Statements: Comparisons in Different Databases

Students are often confused when viewing company financial statements (Income Statement, Balance Sheet, Cash Flow Statement) in different databases. The reason being, that the values displayed, for the same company, often don’t match.

 

Commercial vs Educational Emphasis

This confusion is understandable. Surely values for the same company, for the same statement, should be identical? In explaining this apparent anomaly, the primary purpose of specialised financial databases needs to be appreciated. That is, to provide data/information to allow analysis of many types of securities – different classes of assets, such as Equities – or companies.

The key user groups are within business and commerce. However, there is sufficient useful content, to also be of value to educational establishments, and therefore students – but business users have priority.

For example, when seeking to gauge the relative performance of two or more companies, from different countries, with different accounting standards, there is a clear logic in being able to compare on a like-with-like basis. Therefore, when looking at a value in an Income Statement, for example, it would be clear which company had superior performance. To compare companies otherwise, the so-called apples with oranges analogy, lacks transparency and is ineffective.

Hence, the evolution of common methods for calculating accounting values in different databases, to ease comparisons between companies. Bloomberg Professional and Thomson ONE.com databases both reflect this need, but use different terminology. A look at a company example is instructive.

 

Bloomberg Professional – Financial Analysis [FA]

The Income Statement (‘I/S’) for Tesco PLC, a UK supermarket group, is set out below, using the Financial Analysis function, within Bloomberg Professional.

 

Income Statement - Adjusted

Bloomberg – Financial Analysis (Income Statement – Adjusted)

 

Note that the tab towards the top left: ‘Adjusted’, is highlighted in blue. This is the default view, meaning this is how data is normally displayed for financial statements in Bloomberg Professional. Therefore, companies from different countries can be compared on a like-with-like basis, when the same currency is selected (top right of screen). In the example above, this is set to GBP, or Great Britain Pounds.

Another option is also highlighted – the ‘As Reported’ tab. This selection would display values from the company’s official annual report and accounts.

 

Thomson ONE.com – Reuters Fundamentals

 

The Income Statement for Tesco PLC is displayed again, using Thomson ONE.com database: drop down menu selection:

Company Overviews – Fundamentals – Reuters Fundamentals

 

T1 Income Statement

Thomson ONE.com – Income Statement (Standardised)

 

The ‘Report Format’ option on the left, gives the choice of ‘Company Specific’ (official values – annual report and accounts) and ‘Standardised’.  When the ‘Standardised’ view has been selected and ‘Update View’ clicked on,  the drop down menu to choose a common currency become available, thus allowing like-with-like comparisons, between different companies.

 

Conclusions

The ability to quickly search for companies within databases such as Bloomberg Professional or Thomson ONE.com is an effective means to locate company financial statements and take advantage of like-with-like comparisons.

When viewing financial statements within databases, the realisation that a company’s annual report and accounts is the ultimate (official) source, represented by ‘As Reported’ in Bloomberg Professional and ‘Company Specific’ in Thomson ONE.com, can help to avoid confusion for students, in conducting company analysis.

 

Bloomberg Professional and Thomson ONE.com are databases available to current students and staff of The University of Manchester.