Using Merge to select results in an event study

There are times when the database query doesn’t match the data that you are trying to obtain. For example, you have a list of X companies and for each company the date of an event E (e.g. a merger) but the database query has to be in terms of a list of X companies and a date range Ya-Yb.

WRDS is a typical example – you have a list of 100 gvkey – year pairs where you want some accounting data from Compustat NA (North America). The company identifier gvkey is used to identify companies in Compustat so you can use it in WRDS Compustat queries.

Option 1 – Multiple WRDS requests

Sort your list by the year and create a company list for each year, say N lists. You then make N WRDS-Compustat requests using the relevant company list and year. Finally you combine the N results.

This option is good if you have to make a few requests (i.e N is small)

Option 2 – One WRDS request and select the results you want

Calculate the range of years that you need. You then make one WRDS-Compustat request covering all these years.

If you have 1990-2010 you could now have a result set with 2100 observations (21 years times 100 companies) and you need to select the 100 relevant ones.

You can do this selection using the Merge command in a Statistics package (the following uses Stata as an example)

1. Decide on the variable names in your WRDS results that are going to identify the observations of interest (for example gvkey and fyear)
2. If necessary, Convert the WRDS results into a Stata dataset and save this.
3. Create a Stata dataset of your 100 gvkey-year pairs using the same variable names as in your WRDS results dataset (e.g. gvkey and fyear).
   (If necessary you may need to note and eliminate duplicates from this dataset – Stata commands duplicates list and drop duplicates)
4. Merge your gvkey-year dataset with your WRDS results:
   merge gvkey fyear using “P:\Projects\BMAN70162\acqwrds.dta”, unique sort
   (Note ”P:\Projects\BMAN70162\acqwrds.dta” is the file saved at step 2)
5. You should have one match for each observation in your gvkey-year dataset. This is indicated by the new Stata variable _merge = 3.
   Observations with _merge = 1 indicate there was no matching entry in the WRDS result dataset – this might require further investigation.
6. Observations with _merge = 2 are all the extra results you want to eliminate.

Stata Data Browser viewing merge results (click to expand)

This technique does not depend on Stata – other Statistics packages provide similar merge functions but the detail of the commands will vary. (Indeed, it is probably possible to do this in Excel.)

Referencing – new citation guide

There is a new version of the University of Manchester Harvard Citation Guide.

Follow the Guide to Referencing at the University of Manchester link on the Business and Management Referencing tab.

At the Guide to Referencing at the University of Manchester pages select:

Referencing styles -> Harvard

You can download the guide as a pdf file, or read the advice online.

The Guide to Referencing at the University of Manchester (now renamed Referencing guide at the University of Manchester) includes tabs on referencing software: EndNote, EndNote Web, Reference Manager, Zotero and Mendeley.

UK loses AAA rating (sovereign debt ratings)

The decision of the credit rating agency Moody’s to downgrade the United Kingdom from AAA to Aa1 has been widely reported in the news recently.

• UK loses top AAA credit rating for the first time since 1978 (BBC News 23 Feb 2013)
• Rating Action: Moody’s downgrades UK’s government bond rating to Aa1 from Aaa (Moody’s 22 Feb 2013) – Moody’s ratings definitions
• UK Becomes Next Major Economy to have Credit Rating Downgraded (Euromonitor blog 25 Feb 2013)

Bankscope gives access to the current sovereign debt ratings from Moody’s, Standard and Poor’s (S&P) and  Fitch.

At the Bankscope home page, select Help and Country ratings list:

Bankscope country ratings (sovereign debt ratings) – (click to expand)

 Bloomberg Professional also has sovereign debt ratings – use function CSDR (Sovereign Ratings)

Selecting a country will launch CRPR (Credit Rating Profile) for more detail.
Selecting a rating will give the historical changes in a pop-up window.

Global Insight (from IHS) provides its own Sovereign Risk Ratings and how these compare with Fitch, S&P, and Moody’s.

For example on 27 Feb 2013:

• France is among the top rating “0 AAA Highest Quality” but given a “Higher Risk” by Fitch, S&P and Moody’s.
• Australia is among the rating “5 AA Very High Quality” but given a ”Lower Risk” by Fitch, S&P and Moody’s.

IHS Global Insight covers 204 countries including many that are not covered by Fitch, S&P and Moody’s. For example, Zimbabwe “85 C Poorest Quality” and Somalia “95 D Poorest Quality“.

World Equity Indices (benchmark / key indices)

Datastream Equity Indices (click to expand)

The Datastream Navigator gives quick access to the key equity indices worldwide.

Select Explore – Equity Indices – Benchmarks at a Glance

The top results are worldwide and regional and then there are key indices for specific countries.

This list of benchmark equity indices can be downloaded into Excel using the icon at the top right.

Bloomberg Professional also gives easy access to key indices through functions WEI (World Equity Indices) and EMEQ (Emerging markets equity indices).

Online resources (for if you don’t have access to Datastream or Bloomberg Professional)

• World Stock Indexes – Bloomberg.com
• Market Indices – Reuters.com
• Major World Indices – Yahoo! Finance
• World Stock Market Indices world-stock-exchanges.net

One observation is that a benchmark index may change over time. The benchmark for Italy used to be the Milan MIB 30 Index but this has been replaced by FTSE MIB Index, and for Malaysia the KLSE Composite Index has been replaced by FTSE Bursa Malaysia Klci.

GMID now Passport

Global Market Information Database (GMID) from Euromonitor has now been renamed Passport.

At the moment you access Passport (formerly GMID) as before, e.g. from the University of Manchester Library business and management databases page or the databases A to Z list.

Passport from Eurominitor welcome page (click to expand)

When you login you will get this welcome page.
(after confirming acceptance of the terms of use)

Passport is an excellent source of consumer market research reports and data, industry report and general macro economic data.

The most recent enquiry was for the “Retailing in Morocco Euromonitor 2012 report”.

For a quick search you can use the Enter keywords search box at the top right – “retail morocco” gave the results below.

Selecting the title Retailing in Morocco gives direct access to this June 2012 Euromonitor industry overview report – you can convert to a PDF file (25 pages) to download.

For more Passport (formerly GMID) tips:

• post tagged Passport-GMID on the Ohio University Business Blog
• other posts tagged GMID

Passport from Euromonitor – search results (click to expand)

Datasets in Quandl

Reblogged from researchfinancial:

Quandl is a new portal that has indexed millions of numerical datasets on the Internet. More information on the background and features is available through their FAQ pages. When you click on a particular dataset listed in the Quandl index, Quandl goes to the original source of that data, extracts the most recent version of that data, cleans it up, and gives it to you in the format of your choice.

Read more… 30 more words

Recommended on a couple of Business Library blogs - http://piersonrevesz.wordpress.com/2013/02/01/quandl/ and researchfinancial above.

News and deals (Dell and Virgin media)

There were two stories on the BBC Business News recently that provided a reminder of how deals generate news because they result from companies’ strategic decisions.

TOB Deals example (click to expand)

Dell computers to be bought back by founder Michael Dell

This news story covers a company going from Public to Private, and therefore delisting from a stock exchange, the reverse of an IPO (Initial Public Offering).

This illustrates why databases like Thomson One Banker and Bloomberg Professional track deals from when they are rumoured or announced (not just when they become effective). In Dell’s case there has been significant news coverage about the company’s strategic options now that PCs are seen to be in decline.

Liberty Global to buy Virgin Media for $23.3bn

This news story covers an acquisition. The surprise for me was that this is one US company taking over another. Although when I checked on Bloomberg Professional Virgin Media has 100% of its turnover in the UK, it has chosen to be a US company Virgin Media Inc, listed on the NASDAQ exchange.

Using the FAME database of UK companies you can check that Virgin Media Limited (registered no 02591237) is a subsidiary of Virgin Media Inc.

Bloomberg‘s Mergers & Acquistions (MA) function highlights the current largest deals – on 20 Feb 2013 these are HJ Heinz Co, Virgin Media Inc, and Dell Inc. Bloomberg Industries (BI) can be used to investigate industry trends including the expected impact of large deals, e.g. BI CATVE (Cable and Satellite Europe) has the Feb 05 news item “Liberty Fortifying in Europe May Boost Broadband, TV Competition” and a range of related industry data.

Related posts:

• How Business News can enhance your research (June 2011)
• Mergers and Acquisitions databases (Nov 2011)

From website to company – e.g. ClickandBuy

You want to find the company behind a website. Sometimes there will be an “About us” page – often there will be a footer at the bottom of the pages – and any ‘terms and conditions’ can also be useful.

What you are looking for is the official name of the company, and ideally a company registration number.

For our example www.clickandbuy.com the “About us” page includes a footer that mentions “ClickandBuy International Limited is authorised by the Financial Services Authority” and the terms and conditions include

the Service, is operated by ClickandBuy International Ltd (“Us” / “We” / “Our“), a company registered in England with company number 5661160

ClickandBuy E-Money Account Terms of Use from http://www.clickandbuy.com/WW_en/about-us/terms-and-conditions.html (retrieved 25 January 2013)

This means that we can check a UK company database:

• How to Research UK Company Data (posted June 2102)

For a non-UK company there is Orbis:

• Fame and Orbis for company identification (posted June 2012)

For quoted (public) companies there are additional options:

• Researching a global company using Bloomberg (posted Nov 2012)
• Researching a UK company – reports and data (posted Oct 2012)
• Company Financial Analysis (global companies) (posted Oct 2011)

For going from company to website, databases Fame and Orbis include a website variable.

You can try using the website name as a search variable in Fame, but the legal structure of companies can be complex. For example, search for “pizzaexpress.com” will give several results. The global ultimate owner (GUO) is GONDOLA INVESTMENTS LIMITED PARTNERSHIP INCORPORATED is whose subsidiaries PIZZAEXPRESS HOLDINGS LIMITED is the direct owner of PIZZAEXPRESS (FRANCHISES) LIMITED  and PIZZAEXPRESS LIMITED is the direct owner of PIZZAEXPRESS (RESTAURANTS) LIMITED. [Fame database accesses 26 March 2013]

Risk Free Rate and Fama French factors

Throughout 2012 Risk Free Rate for US and US has consistently been one of our top posts, and the related  FAQ answers have also been among most accessed on Manchester Business Answers 24/7.

For a US risk free rate, Ken French’s site provides downloadable files of Fama/French factors that include this.

Fama French factors from Kenneth R. French Data Library (click to expand)

On the data library page:

Scroll down to U.S. Research Returns Data (Downloadable Files)

The Fama/French Factor downloadable files are available in default/monthly, weekly or daily format.

The risk free rate in these files is based on the returns for a 1 month (4 week) Treasury Bill, and these are expressed in terms of monthly, weekly or daily returns.

For example, the monthly risk free return values include:

• 200701  -  0.44
• …
• 200801  -  0.21

For January 2007 the risk free monthly return is 0.44%, and for January 2008 0.21%

If rather than Ken French’s site you go to the FRED Economic Data (Federal Reserve Bank of St. Louis Economic Data) and download the 4-Week Treasury Bill: Secondary Market Rate (Series id TB4WK) from Board of Governors of the Federal Reserve System H15 Selected Interest Rates, they you will get a file expressing the annualised yield on a T-Bill.

For example, the monthly yield figures for TB4WK include:

• 2007-01-01 – 4.84
• …
• 2008-01-01 – 2.68

For January 2007 the annual yield on a 4-week T-Bill was 4.84% and for January 2008 2.68%

Converting annual yield to monthly return

To make an annual yield, downloaded from FRED Economic Data or Datastream or Bloomberg etc.,  comparable with the monthly return from the downloadable Fama/French factors we need to convert. We could use an Excel formula

4.84% annually – POWER( (1 + 4.84/100), 1/12 ) = 1.003947 – monthly return 0.39%

2.68% annually – POWER((1 + 2.68/100), 1/12 ) = 1.002206 – monthly return 0.22%

[Converting to weekly would be POWER( (1+r/100), 1/52 ) where r is the annual yield
- converting to daily POWER( (1+r/100)/1/250 ) assuming 250 trading days per year]

Key Tip

There are two important steps to finding a good proxy for the risk free rate:

1. Finding a series that is as close to risk free as possible for the analysis you are doing, and where you have data available for the relevant dates.
2. Comparing the data you retrieve with the data format you want and converting between an annual yield and monthly/weekly/daily return as appropriate

In academic work, we recomend that you what you have chosen for your risk free rate and any data conversion that you have applied.

For further tips on potential sources, see earlier posts Risk Free Rate for US and US and Risk Free Interest Rate.

HP acquisition of Autonomy and a $8.8billion write off

On 20 Nov 2012 Hewlett Packard (HP) announced that it was taking a write off of $8.8 billion of the $11.1 billion that it paid to acquire Autonomy in  October 2010. At the time Automony was a member of the FTSE 100 and seen as a UK technology success story.

There has been lots in the UK financial press since then as HP’s key argument is that they overpaid for Autonomy because of accounting manipulation. This is strongly denied by Automony founder Mike Lynch.

News stories:

• Hewlett-Packard accountants sued over Autonomy purchase (BBC 29 Nov 2012)
• US to probe Autonomy sale to Hewlett-Packard (BBC 27 Dec 2012)
• Autonomy’s Lynch defends record as HP confirms Federal probe (Reuters 28 Dec 2012)

Breaking down a 8.8$ million write off – from Musings on Markets blog (click to expand)

Aswath Damodaran’s Musings on Markets blog makes a strong case that even if HP is right this only accounts for $2.45 billion of the $8.8 billion write off – see HP’s Deal from Hell: The mark-it-up and write-it-down two-step.

The write off raises issues about the acquisition in terms of corporate strategy, leadership, corporate governance as well as the accuracy of Autonomy’s accounts.

Resources for more information:

Thomson One Banker - a deals module search will provide details of the deal: announced 18 Aug 2011, effective 3 Oct 2011, “UK – Hewlett-Packard Vision BV of the Netherlands, a unit of by Hewlett-Packard Co (HP),  completed the tender offer to acquire the entire share capital of Autonomy Corp PLC (Autonomy), a
Cambridge-based developer of infrastructure and information management software, for GBP 25.5 (USD 42.09)” [SDC deal number 2337968040]

Thomson Research - analysts reports - For example, on 23 Aug 2011 Marc Geall of Deutsche Bank published a report on Autonomy Corp plc. This reported a change of recommendation to sell, since the shares were GDP 24.86 almost fully valuing the HP offer, and substantially higher that Deutsche Bank’s fundamental target price of GBP 17.50.

Business Source Premier (EBSCO): - several trade journals with articles on the HP aquisition of Autonomy. For example,

Forbes.com (2012) ‘With Autonomy, H-P Bought An Old-Fashioned Accounting Scandal. Here’s How It Worked’ Forbes.Com, (November 20, 2012):p. 39, Business Source Premier, EBSCOhost, viewed 11 January 2013.

Manchester Business Answers 24/7 FAQ:

Where do I find info on Mergers and Acquisitions? and

mergers_and_aquistions tag.