Difference between revisions of "Winner's Curse in Acquisitions (Academic Paper)"

From edegan.com
Jump to navigation Jump to search
Line 451: Line 451:
  
 
=== Normalizing SDC data ===
 
=== Normalizing SDC data ===
 +
Make sure that the first line of the data file starts with the column names and column name are not preceded by empty lines. Also, remove the summary/report lines at the bottom of the text file.
  
 
A copy of the normalizer is save here
 
A copy of the normalizer is save here
Line 460: Line 461:
 
# Drag and drop NormalizeFixedWidth.pl in Komdo
 
# Drag and drop NormalizeFixedWidth.pl in Komdo
 
# Click on the start or continue debugging
 
# Click on the start or continue debugging
−
# Type : '''-file=SDC_MA_Oct10v3.txt''
+
# Type : -file=SDC_MA_Oct10v4.txt in Script Arguments textbox.
 +
# Click on OK
 +
# Enter the number of last line of the header and press return. This should be the line immediately followed by the first row of data.
 +
 
 +
########################################################## 
 +
#                  NormalizeFixedWidth.pl                #
 +
#                          v0.01                        #
 +
#                    (c)Ed Egan, 2010                  #
 +
##########################################################
 +
 +
Starting the program... [Done]
 +
Your run options are:
 +
File to read from... [SDC_MA_Oct10v4.txt]
 +
File to write to... [SDC_MA_Oct10v4-normal.txt]
 +
Opening the input file to read... [Done]
 +
 +
##########################################################
 +
Choose the last line of the header (inc. blanks):
 +
0   Date        Date      Target Name           
 +
1 Announced    Effective                           
 +
2                                                  
 +
3                                                  
 +
4                                                  
 +
5                                                  
 +
6 01/01/85    01/01/85    Gladieux Corp         
 +
7 01/02/85    01/02/85    STG Electrosystems Inc 
 +
8 01/03/85    01/03/85    American Title Co     
 +
9 01/04/85    01/04/85    Bee Chemical Co       
 +
 +
Your Choice: 5
 +
 +
 +
Thank you - Proceeding now...
 +
##########################################################
 +
Determining markers... [Done]
 +
Processing the header... [Done]
 +
Processing the body... [Done]
 +
Removing dead columns... [Done]
 +
##########################################################
 +
Choose any columns you want repeated down (e.g. 0,1,2):
 +
0 Date Announced
 +
1 Date Effective
 +
2 Target Name 
 +
3 Target Industry Sector 
 +
4 Target Nation
 +
5 Acquiror Name 
 +
6 Acquiror Industry Sector 
 +
7 Acquiror Nation
 +
8 Pc of Shares Acq
 +
9 Pc Owned After Transaction
 +
10 Value of Transaction mil
 +
11 Enterprise Value mil
 +
12 Equity Value mil
 +
13 Acquiror CUSIP
 +
14 Acquiror Immediate Parent CUSIP
 +
15 Acquiror Ultimate Parent CUSIP
 +
16 Target Immediate Parent CUSIP
 +
17 Target Ultimate Parent CUSIP
 +
18 Target CUSIP
 +
19 Acquiror Primary Ticker Symbol
 +
20 Acquiror Ultimate Parent Primary Ticker Symbol
 +
21 Target Primary Ticker Symbol
 +
22 Target Ultimate Parent Primary Ticker Symbol
 +
23 Acquiror Primary NAIC Code
 +
24 Acquiror Ultimate Parent Primary NAIC Code
 +
25 Target Primary NAIC Code
 +
26 Target s Ultimate Parent Primary NAIC Code
 +
27 Number of Bidders
 +
28 Status 
 +
29 Value Est 
 +
30 Value Amended Flag
 +
31 Date Announced is Estimated
 +
32 Consideration Structure
 +
33 Number of Considerations Offered
 +
34 Number of Considerations Sought
 +
35 Consid Structure Description
 +
36 Pc of Cash
 +
37 Pc of Other
 +
38 Pc of Stock
 +
39 Pc of Unknown
 +
40 Acquiror Full Name 
 +
41 Target Name 
 +
42 Acquiror City 
 +
43 Target City 
 +
44 Acquiror Zip Code
 +
45 Target Zip Code
 +
46 Acquiror State
 +
47 Target State
 +
48 Deal Currency Code
 +
49 Target Bankrupt
 +
50 Challenged Deal
 +
51 Debt Restructuring
 +
52 RecapDefense 
 +
53 Government Owned Involvement Flag
 +
54 Joint Venture
 +
55 Restructuring
 +
56 Acquiror is a Leveraged Buyout Firm
 +
57 LBO 
 +
58 IPO FlagY N 
 +
59 Target is a Leveraged Buyout Firm
 +
60 Merger of Equals
 +
61 Reverse Takeover
 +
62 Deal Began as a Rumor
 +
63 Acquiror is a White Knight
 +
64 White Knight Defense
 +
65 Back End Defense
 +
66 FlipOver Defense
 +
67 Voting Plan Defense
 +
68 Poison Pill
 +
69 Creeping Acquisition
 +
70 Repurchase Defense
 +
71 Pacman Defense
 +
72 Lockup FlagY N 
 +
73 Target Lockup
 +
74 Greenmail 
 +
75 Asset Lockup
 +
76 Scorched Earth Defense
 +
77 SelfTender Defense
 +
78 Stock Lockup
 +
79 Defense 
 +
80 White Squire
 +
81 Acquiror Includes Employees
 +
82 Acquiror Includes an ESOP
 +
83 Significant Family Ownership of Target
 +
84 Acquiror Lockup
 +
85 Financial Acquiror
 +
86 2 Step Spinoff 
 +
87 Acquiror Includes Mgmt
 +
88 Open Market Purchases
 +
89 Proxy Fight
 +
90 Purpose Code
 +
91 Related Deals
 +
92 Foreign Provider of Funds
 +
93 Stock Swap
 +
94 Asset Swap Flag
 +
95 Sweep the Street
 +
96 SelfTender
 +
97 Tender Offer
 +
98 Unsolicited 
 +
99 Acquiror TermFee 
 +
100 Target TermFee 
 +
101 Forced vote provisions FlagY N 
 +
102 Walkaway
 +
103 Acquiror Ultimate Parent Street Address 1
 +
104 Acquiror Ultimate Parent Street Address 2
 +
105 Target Ultimate Parent Street Address 1
 +
106 Target Ultimate Parent Street Address 2
 +
107 Acquiror Primary Stock ExchangeName
 +
108 Target Primary Stock ExchangeName
 +
109 Target Net Sales LTM mil
 +
110 Target Net Assets mil
 +
111 Target Total Assets mil
 +
112 Target Common Equity mil
 +
113 Target Book Value Per Share LTMUS 
 +
114 Target Intangible Assets mil
 +
115 Target R D One Year Priormil
 +
116 Target Total Liabilities mil
 +
 +
Your Choice:
 +
 +
 +
Thank you - Proceeding now...
 +
##########################################################
 +
 +
Writing the output file... [Done]
 +
 +
##########################################################
 +
Thank you for processing data with NormalizeFixedWidth
 +
Please cite any Ed Egan corporate finance paper in your
 +
references or otherwise acknowledge this help to your work.
 +
(c)Ed Egan, 2010. Enjoy. (US dollars preferred.)
 +
##########################################################
  
 
==Previous Papers==
 
==Previous Papers==

Revision as of 11:36, 14 October 2016


McNair Project
Winner's Curse in Acquisitions (Academic Paper)
Project logo 02.png
Project Information
Project Title
Start Date
Deadline
Primary Billing
Notes
Has project status
Copyright © 2016 edegan.com. All Rights Reserved.


Three Big Pulls

SDC Mergers & Acquisitions

Add the following (A=L+E): 

TargetTotalAssets	real, TASS: Taget Financials: Total Assets
TargetTotalLiabilities	real,  TLIA: Total Liabilities Last Twelve Months ($ Mil) 
TargetCommonEquity	real, COMEQ: Target Financials: Common Equity

Also nice: 

TargetNetSales	real,  SALES: Net Sales Last Twelve Months ($ Mil)
TargetNetSales2	real, ?
TargetBookValuePerShare	real, BV: Book Value per Share Last Twelve Months ($) 
TargetIntangibles	real, IASS: Intangible Assets Last Twelve Months 
TargetRAndD	real, RND1: Research & Development Expense 1 Year Prior ($ Mil)

Added just in case: 

Sales
Net Assets
Book Value (Perhaps needed for Tobin's Q?)

Session Details
---------------
Request   Hits    Request Description
   0        -     DATABASES: Domestic Mergers, 1979-Present (MA, OMA)
   1        -     Date Announced: 1/1/1985 to 10/11/2016 (Custom) (Calendar)
   2    300351    Target Nation : US
   3    257921    Acquiror Nation : US
   4    127852    Acquiror Public Status : P
   5     98102    Target Public Status : V, P
   6     63725    Deal Status : C
   7     52739    Percent of Shares Owned after Transaction: 100 to 100
   8              Custom Report: SDC_MA_Oct10v2 (Columnar) - Save As:
                  E:\McNair\Projects\Winner's Curse\Data\SDC_MA_Oct10v2.txt

  Billing Ref # : 1960756
  Capture File  : riceuniv.1960756
  Session Name  : SDC_MA_Oct10v2.ssh

Variables Pulled:
-----------------
Date Announced
Date Effective
Target Name 
Target Industry Sector 
Target Nation
Acquiror Name 
Acquiror Industry Sector 
Acquiror Nation
Pc of Shares Acq
Pc Owned After Transaction
Value of Transaction mil
Enterprise Value mil
Equity Value mil
Acquiror CUSIP
Acquiror Immediate Parent CUSIP
Acquiror Ultimate Parent CUSIP
Target Immediate Parent CUSIP
Target Ultimate Parent CUSIP
Target CUSIP
Acquiror Primary Ticker Symbol
Acquiror Ultimate Parent Primary Ticker Symbol
Target Primary Ticker Symbol
Target Ultimate Parent Primary Ticker Symbol
Target Ultimate Parent Ticker Symbol
Acquiror Primary NAIC Code
Acquiror Ultimate Parent Primary NAIC Code
Target Primary NAIC Code
Target s Ultimate Parent Primary NAIC Code
Number of Bidders
Status 
Value Est 
Value Amended Flag
Date Announced is Estimated
Consideration Structure
Number of Considerations Offered
Number of Considerations Sought
Consid Structure Description
Pc of Cash
Pc of Other
Pc of Stock
Pc of Unknown
Acquiror Full Name 
Target Name 
Acquiror City 
Target City 
Acquiror Zip Code
Target Zip Code
Acquiror State
Target State
Deal Currency Code
Target Bankrupt
Challenged Deal
Debt Restructuring
RecapDefense  
Government Owned Involvement Flag
Joint Venture
Restructuring
Acquiror is a Leveraged Buyout Firm
LBO 
IPO FlagY N 
Target is a Leveraged Buyout Firm
Merger of Equals
Reverse Takeover
Deal Began as a Rumor
Acquiror is a White Knight
White Knight Defense
Back End Defense
FlipOver Defense
Voting Plan Defense
Poison Pill
Creeping Acquisition
Repurchase Defense
Pacman Defense
Lockup FlagY N 
Target Lockup
Greenmail
Asset Lockup
Scorched Earth Defense
SelfTender Defense
Stock Lockup
Defense
White Squire
Acquiror Includes Employees
Acquiror Includes an ESOP
Significant Family Ownership of Target
Acquiror Lockup
Financial Acquiror
2 Step Spinoff
Acquiror Includes Mgmt
Open Market Purchases
Proxy Fight
Purpose Code
Related Deals
Foreign Provider of Funds
Stock Swap
Asset Swap Flag
Sweep the Street
SelfTender
Tender Offer
Unsolicited
Acquiror TermFee 
Target TermFee 
Forced vote provisions FlagY N
Walkaway
Acquiror Ultimate Parent Street Address 1
Acquiror Ultimate Parent Street Address 2
Target Ultimate Parent Street Address 1
Target Ultimate Parent Street Address 2
Acquiror Primary Stock ExchangeName
Target Primary Stock ExchangeName

COMPUSTAT

Use Compustat Industrial Annual (annual data) 

Criteria:
-----------------
1970-1 to 2015-11
GVKEY, Entire Dbase
tab delimited, date in ISO8661

Variables:
----------
GVKEY
CONM
TIC
CUSIP
EXCHG
FYR
CURNCD
FYEAR
AT
CEQ
INTAN
EBITDA
REVT
SALE
NAICSH
SICH
MKVALT
PRCC_F
C
INDL
FS
STD

Note: TobinsQ is Market over book so MKVALT/AT (also pulled common stock and price)

CRSP

Use CRSP Annual, Daily Stock File 

Criteria:
-----------------
PERMNO, Entire Dbase
1980-01-01 to 2015-12-31
tab delimited, date in ISO8661

Variables:
----------
PERMNO
CUSIP
NCUSIP
COMNAM
TICKER
PERMCO
EXCHCD
SICCD
NAICS
PRIMEXCH
PRC
VOL
NUMTRD
RET
SHROUT
VWRETD
EWRETD


Governance Measures

Projects --> Winner's Curse --> Data

Open the GovernanceVARS.txt

These are

  • staggered boards
  • limits to shareholder bylaw amendments
  • limits to shareholder charter amendments
  • supermajority requirements for mergers
  • poison pills
  • golden parachutes

To do list

Processing the SDC data:

  • Find SIC codes concordance to get NAICS for old data
  • Acquirer publicly traded ( must be AMEX, NASDAQ, or NYSE)
  • Magic with CUSIPs : CUSIP is a nine digit number (possibly ten, ) Seven issuer, two issue, one check

CRSP processing notes:

  • Estimation window 250 days ending 30 days before the Acq. must be at least 50 continuous days before the 30 days window.
  • Returns and prices may contain codes. Import as var_char and reprocess?

Other notes:

Governance Variables

A list of governance variables can be found in: E:\McNair\Projects\Winner's Curse\Data\GovernanceVARS

Paper Discussion

Variables & Where to Find Them:

Variable Location
Actual Return CRSP
Abnormal Return CRSP
Average Return CRSP
Weighted Average Return CRSP
Cumulative Ab. Return CRSP
Boom SDC
Competition Compustat
Distance SDC + google maps
IT Fixed Effect SDC
Idiosyncratic Volatility CRSP (Calculated)
Industry Fixed Effect SDC
Momentum CRSP (Calculated)
Private Financial Data CRSP
Percent Cash SDC
Previous Acquisitions SDC
Sales Compustat
Tobin's Q Compustat
Turnover CRSP
VC Fixed Event SDC & VentureXpert
Governance Compustat
Friendly vs. Hostile SDC

Importing the CRSP data into the `winner' database (Amir)

Two important files:
Project --> Winner's Curse --> Data --> SQL --> CRSP_NOTES
Project --> Winner's Curse --> Data --> SQL --> CRSP_CODES

Data file is located in: E:\McNair\Projects\Winner's Curse\Data\CRSP-Oct10-c30cc89e52b70545-1.txt

Issue 1: The text file is 8 GB large and there's no way to open it in Textpad. I'll try to read the file in LogExpert which seems to do a neat job in in opening large files. Otherwise, I'll try to read the first few lines in the terminal. Update: I managed to open the file in LogExpert. However, extremely inefficient to scroll over the file.

I copied the file to Z:\WinnersCurse\CRSP-oct10

Entered the following command in the terminal to read the header: 

head -1 CRSP-oct10.txt

CREATE TABLE CRSP (
 PermNo int,
 date date,
 EXcode int,
 SIC int,
 NCUSIP varchar(10),
 Ticker varchar(10), 
 Name varchar(100),
 NAICS int,
 PrimEx varchar(1),
 PERMCO int, 
 CUSIP varchar(12),
 Price real,
 VOL int,
 RET real,
 ShOut int, 
 trade int, 
 EQRET real,
 VRET real
);

We have unexpected data types in few columns. We'll first start by copying data into a table named CRSPtmp with varchar type for most of the fields. 

CREATE TABLE CRSPtmp (
 PermNo varchar(10),
 date date,
 EXcode varchar(10),
 SIC varchar(10),
 NCUSIP varchar(10),
 Ticker varchar(10), 
 Name varchar(100),
 NAICS varchar(10),
 PrimEx varchar(1),
 PERMCO varchar(10), 
 CUSIP varchar(12),
 Price varchar(15),
 VOL varchar(10),
 RET varchar(10),
 ShOut varchar(10), 
 trade varchar(10), 
 EQRET varchar(10),
 VRET varchar(10)
);
 
\COPY crsptmp FROM '/bulk/WinnersCurse/CRSP-oct10.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
-- COPY 64614281

Next, to create an index on CUSIP: 

CREATE INDEX CUSIPIndex
ON crspfinal(CUSIP);

CRSP2 Variable Issues (Jake)

permno-

date-

excode -

sic - letter "Z", value 711 (most are 4 digits),

ncusip -

ticker -

name -

naics -

primex -

permco -

cusip -

Price - Null values, negative numbers,

vol - null values

ret - null values, letter "B" as value multiple times, letter "C",

shout - null values

trade - null values

eqret -

vret -

Importing SDC Data into Winner Database

Session Details
---------------
Request   Hits    Request Description
  0        -     DATABASES: Domestic Mergers, 1979-Present (MA, OMA)
  1        -     Date Announced: 1/1/1985 to 10/11/2016 (Custom) (Calendar)
  2    300400    Target Nation : US
  3    257952    Acquiror Nation : US
  4    127865    Acquiror Public Status : P
  5     98108    Target Public Status : V, P
  6     63732    Deal Status : C
  7     52746    Percent of Shares Owned after Transaction: 100 to 100
  8              Custom Report: SDC_MA_Oct10v4 (Columnar) - Save As:
                 E:\McNair\Projects\Winner's Curse\Data\SDC_MA_Oct10v4.txt
 Billing Ref # : 1962461
 Capture File  : riceuniv.1962461
 Session Name  : SDC_MA_Oct10v4.ssh

We encountered an issue with the variable Purpose Code in our most recent SDC pull. Purpose Code can carry multiple values, so we had extra lines with blanks for every value other than Purpose Code. Rather than doing another SDC pull, we used the following regular expressions to solve: 

Find : ^[\s}.* 
Replace :

Directly below is the original SDC file prior to our fix 

E:\McNair\Projects\Winner's Curse\Data\SDC_MA_Oct10v4 -raw

Directly below is the SDC file post-fix 

E:\McNair\Projects\Winner's Curse\Data\SDC_MA_Oct10v4



11332 rows were removed as a result of our regular expression use.

Normalizing SDC data

Make sure that the first line of the data file starts with the column names and column name are not preceded by empty lines. Also, remove the summary/report lines at the bottom of the text file.

A copy of the normalizer is save here 

E:\McNair\Projects\Winner's Curse\Data\NormalizeFixedWidth.pl

Follow these steps:

  1. Activate the Komodo license.
  2. Open Komodo IDE 9, there should be a shortcut on your desktop
  3. Drag and drop NormalizeFixedWidth.pl in Komdo
  4. Click on the start or continue debugging
  5. Type : -file=SDC_MA_Oct10v4.txt in Script Arguments textbox.
  6. Click on OK
  7. Enter the number of last line of the header and press return. This should be the line immediately followed by the first row of data.
##########################################################  
#                  NormalizeFixedWidth.pl                #
#                           v0.01                        #
#                     (c)Ed Egan, 2010                   #
##########################################################

Starting the program...				[Done]
Your run options are:
	File to read from... 			[SDC_MA_Oct10v4.txt]
	File to write to... 			[SDC_MA_Oct10v4-normal.txt]
Opening the input file to read...		[Done]

##########################################################
Choose the last line of the header (inc. blanks):
0	  Date         Date       Target Name             
1	Announced    Effective                            
2	                                                  
3	                                                  
4	                                                  
5	                                                  
6	01/01/85     01/01/85     Gladieux Corp           
7	01/02/85     01/02/85     STG Electrosystems Inc  
8	01/03/85     01/03/85     American Title Co       
9	01/04/85     01/04/85     Bee Chemical Co         

Your Choice: 5


Thank you - Proceeding now...
##########################################################
Determining markers...				[Done]
Processing the header...			[Done]
Processing the body...				[Done]
Removing dead columns...			[Done]
##########################################################
Choose any columns you want repeated down (e.g. 0,1,2):
0	Date Announced 
1	Date Effective 
2	Target Name  
3	Target Industry Sector  
4	Target Nation 
5	Acquiror Name  
6	Acquiror Industry Sector  
7	Acquiror Nation 
8	Pc of Shares Acq 
9	Pc Owned After Transaction
10	Value of Transaction mil
11	Enterprise Value mil
12	Equity Value mil 
13	Acquiror CUSIP 
14	Acquiror Immediate Parent CUSIP
15	Acquiror Ultimate Parent CUSIP
16	Target Immediate Parent CUSIP
17	Target Ultimate Parent CUSIP
18	Target CUSIP 
19	Acquiror Primary Ticker Symbol
20	Acquiror Ultimate Parent Primary Ticker Symbol
21	Target Primary Ticker Symbol
22	Target Ultimate Parent Primary Ticker Symbol
23	Acquiror Primary NAIC Code
24	Acquiror Ultimate Parent Primary NAIC Code
25	Target Primary NAIC Code
26	Target s Ultimate Parent Primary NAIC Code
27	Number of Bidders 
28	Status  
29	Value Est  
30	Value Amended Flag
31	Date Announced is Estimated
32	Consideration Structure 
33	Number of Considerations Offered
34	Number of Considerations Sought
35	Consid Structure Description
36	Pc of Cash 
37	Pc of Other 
38	Pc of Stock 
39	Pc of Unknown 
40	Acquiror Full Name  
41	Target Name  
42	Acquiror City  
43	Target City  
44	Acquiror Zip Code 
45	Target Zip Code
46	Acquiror State 
47	Target State 
48	Deal Currency Code
49	Target Bankrupt 
50	Challenged Deal 
51	Debt Restructuring 
52	RecapDefense   
53	Government Owned Involvement Flag
54	Joint Venture 
55	Restructuring 
56	Acquiror is a Leveraged Buyout Firm
57	LBO  
58	IPO FlagY N  
59	Target is a Leveraged Buyout Firm
60	Merger of Equals
61	Reverse Takeover 
62	Deal Began as a Rumor
63	Acquiror is a White Knight
64	White Knight Defense
65	Back End Defense 
66	FlipOver Defense
67	Voting Plan Defense
68	Poison Pill 
69	Creeping Acquisition 
70	Repurchase Defense 
71	Pacman Defense 
72	Lockup FlagY N   
73	Target Lockup 
74	Greenmail  
75	Asset Lockup 
76	Scorched Earth Defense
77	SelfTender Defense
78	Stock Lockup 
79	Defense  
80	White Squire 
81	Acquiror Includes Employees
82	Acquiror Includes an ESOP
83	Significant Family Ownership of Target
84	Acquiror Lockup 
85	Financial Acquiror 
86	2 Step Spinoff  
87	Acquiror Includes Mgmt
88	Open Market Purchases
89	Proxy Fight 
90	Purpose Code 
91	Related Deals 
92	Foreign Provider of Funds
93	Stock Swap 
94	Asset Swap Flag
95	Sweep the Street
96	SelfTender 
97	Tender Offer 
98	Unsolicited  
99	Acquiror TermFee   
100	Target TermFee   
101	Forced vote provisions FlagY N  
102	Walkaway 
103	Acquiror Ultimate Parent Street Address 1 
104	Acquiror Ultimate Parent Street Address 2 
105	Target Ultimate Parent Street Address 1 
106	Target Ultimate Parent Street Address 2 
107	Acquiror Primary Stock ExchangeName 
108	Target Primary Stock ExchangeName 
109	Target Net Sales LTM mil
110	Target Net Assets mil
111	Target Total Assets mil
112	Target Common Equity mil
113	Target Book Value Per Share LTMUS  
114	Target Intangible Assets mil
115	Target R D One Year Priormil
116	Target Total Liabilities mil

Your Choice:


Thank you - Proceeding now...
##########################################################

Writing the output file...			[Done]

##########################################################
Thank you for processing data with NormalizeFixedWidth
Please cite any Ed Egan corporate finance paper in your 
references or otherwise acknowledge this help to your work.
(c)Ed Egan, 2010. Enjoy. (US dollars preferred.)
##########################################################

Previous Papers

From the old wiki

Related Pages

Resources

Retrieved from "http://www.edegan.com/mediawiki/index.php?title=Winner%27s_Curse_in_Acquisitions_(Academic_Paper)&oldid=10176"