Difference between revisions of "Winner's Curse in Acquisitions (Academic Paper)"
| Line 36: | Line 36: | ||
| Distance || SDC + google maps || D.N.E. | | Distance || SDC + google maps || D.N.E. | ||
|- | |- | ||
| − | | IT Fixed Effect || SDC || | + | | IT Fixed Effect || SDC || SDCA3 |
|- | |- | ||
| Idiosyncratic Volatility || CRSP (Calculated) || A1 | | Idiosyncratic Volatility || CRSP (Calculated) || A1 | ||
| Line 44: | Line 44: | ||
| Momentum || CRSP (Calculated) || A1 | | Momentum || CRSP (Calculated) || A1 | ||
|- | |- | ||
| − | | Private Financial Data || CRSP(SDC) || | + | | Private Financial Data || CRSP(SDC) || SDCA3 |
|- | |- | ||
| − | | Percent Cash || SDC || | + | | Percent Cash || SDC || SDCA3 |
|- | |- | ||
| − | | Previous Acquisitions || SDC || | + | | Previous Acquisitions || SDC || SDCA3 |
|- | |- | ||
| Sales || Compustat || SDCv2 | | Sales || Compustat || SDCv2 | ||
Revision as of 15:54, 11 November 2016
| Academic Paper | |
|---|---|
| Title | |
| © edegan.com, 2016 | |
Contents
WHAT MATTERS
Ed - Review code for SDCv2 & distance
Amir - Literature review of Governance measures (I'll work on this on Friday around 2:45 -Amir)
Will - Clarify which variable to use on Malmendier & Tate and join your part of the database
Jake - Combining SDC databases to one database
List of Variables
Variables & Where to Find Them:
| Variable | Location | Table |
| Actual Return | CRSP | A1 |
| Abnormal Return | CRSP | A1 |
| Average Return | CRSP | STATA Later |
| Weighted Average Return | CRSP | STATA Later |
| Cumulative Ab. Return | CRSP | A1 |
| Boom | SDC | tobinxcompxsdcboom |
| Competition | Compustat | tobinxcompxsdcboom |
| Distance | SDC + google maps | D.N.E. |
| IT Fixed Effect | SDC | SDCA3 |
| Idiosyncratic Volatility | CRSP (Calculated) | A1 |
| Industry Fixed Effect | SDC | In progress |
| Momentum | CRSP (Calculated) | A1 |
| Private Financial Data | CRSP(SDC) | SDCA3 |
| Percent Cash | SDC | SDCA3 |
| Previous Acquisitions | SDC | SDCA3 |
| Sales | Compustat | SDCv2 |
| Tobin's Q | Compustat | tobinxcompxsdcboom |
| Turnover | CRSP | A1 |
| VC Fixed Effect | SDC & VentureXpert | D.N.E. |
| Governance | Compustat | D.N.E. |
| Friendly vs. Hostile | SDC | D.N.E. |
Boom - Created. Code in E:\McNair\Projects\Winner's Curse\Data\SQL\SDC_TABLE. Final variable in table SDCno_boom.
Competition - Created. Code in E:\McNair\Projects\Winner's Curse\Data\SQL\CRSP_TABLE. Final variable in table competition final.
IT Fixed Effect - Create an indicator variable taking the value one if the target has a NAICS code listed on page 31 of the article.
Idiosyncratic Volatility - RMSE, it should already be up on the database.
Industry Fixed Effect - Created. Jake, please update this.
Momentum - To control for systematic variations in return over time. The summation of the NYSE-Nasdaq-AMEX composite market return (as calculated by CRSP) over the event window
Private Financial Data - Look at [1]. The variables takes value 1 when the balance sheet is not publicly available.
Percent Cash - Possibly this is going to be the PCT_CASH variable in SDC. This should be the PC_CASH field in the SDC table.
Previous Acquisitions - The number of previous acquisitions (from 1985 on) of privately-held firms completed by the acquirer prior to its announcement of the acquisition.
Tobin's Q: The ratio of market-to-book value of assets. This would be MKVALT/ AT in the SDC table.
Turnover: AVG( # shares traded each month / # shares outstanding.
VC Fixed Effect: Is the target VC backed or not? Where could we find the VC data.
Three Big Pulls
SDC Mergers & Acquisitions
Add the following (A=L+E) ---- DONE
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 ---- DONE
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 ---- DONE
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)
DROP TABLE compustatpull; CREATE TABLE compustatpull ( gvkey int, datadate date, Fyear int, industry_format varchar(5), consol varchar(20), popsrc varchar(20), datafmt varchar(5), tic varchar(20), cusip varchar(20), conm varchar(50), curcd varchar(3), curncd varchar(3), fyr int, act float, ceq varchar(20), ebitda float, intan float, revt float, sale float, exchg varchar(20), costat varchar(5), naicsh float, sich varchar(20), mkvalt varchar(20), prcc_f varchar(20), county varchar(50), naics varchar(10), sic int, states varchar(2), ipodate date);
\COPY compustatpull FROM 'CSTAT-10Oct-ff1feb81b9f166ad.txt' WITH DELIMITER AS E'\t' HEADER NULL AS CSV --464894
All loaded up
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:
- See page 9 of the paper for data processing notes.
- Data dictionary for SDC MA: http://www.edegan.com/repository/DD-ThomsonSDCMA.txt
Governance Variables
A list of governance variables can be found in: E:\McNair\Projects\Winner's Curse\Data\GovernanceVARS
Paper Discussion
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:
- Activate the Komodo license.
- Open Komodo IDE 9, there should be a shortcut on your desktop
- Drag and drop NormalizeFixedWidth.pl in Komdo
- Click on the start or continue debugging
- 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.) ##########################################################
The output is now stored in the same directory: E:\McNair\Projects\Winner's Curse\Data\SDC_MA_Oct10v4-normal.txt
To import the data into winner database, I'll copy the above file to the /bulk directory.
The code to create the SDC table is stored in \McNair\Projects\Winner's Curse\Data\SQL\SDC_TABLE.
Create Table SDC( Date_Announced date, Date_Effective date, Tgt_Name varchar(80), Tgt_Industry varchar(100), Tgt_Nation varchar(100), Acq_Name varchar(80), Acq_Ind varchar(100), Acq_Nation varchar(100), Pc_Shares_Acq varchar(100), Pc_Owned_After_Transaction varchar(100), Value_Transaction varchar(100), Enterprise_Value varchar(100), Equity_Value varchar(100), Acq_CUSIP varchar(100), Acq_prnt_CUSIP varchar(100), Acq_Ultimate_CUSIP varchar(100), Tgt_prnt_CUSIP varchar(100), Tgt_Ultimate_CUSIP varchar(100), Tgt_CUSIP varchar(100), Acq_Primary_Ticker varchar(100), Acq_Ultimate_Ticker varchar(100), Tgt_Primary_Ticker varchar(100), Tgt_Ultimate_Ticker varchar(100), Acq_NAIC varchar(100), Acq_Ultimate_NAIC varchar(100), Tgt_Primary_NAIC varchar(100), Tgt_Ultimate_NAIC varchar(100), NBid varchar(100), Status varchar(100), Value_Est varchar(100), Value_Amended_Flag varchar(100), Date_Announced_Estimated varchar(100), Consideration_Structure varchar(100), NConsid_off varchar(100), NConsid_Sought varchar(100), Consid_Structure varchar(100), Pc_Cash varchar(100), Pc_Other varchar(100), Pc_Stock varchar(100), Pc_Unknown varchar(100), Acq_FullName varchar(100), Tgt_Name2 varchar(100), Acq_City varchar(100), Tgt_City varchar(100), Acq_Zip varchar(100), Tgt_Zip varchar(100), Acq_St varchar(100), Tgt_St varchar(100), Currency varchar(100), Tgt_Bankrupt varchar(100), Chlng_Deal varchar(100), Debt_Restructuring varchar(100), RecapDefense varchar(100), Government_Owned_Involvement_Flag varchar(100), Joint_Venture varchar(100), Restructuring varchar(100), Acq_LBO_firm varchar(100), LBO varchar(100), IPO_Flag varchar(100), Tgt_LBO_firm varchar(100), Merger_Equals varchar(100), Reverse_Takeover varchar(100), Deal_was_Rumor varchar(100), Acq_is_a_White_Knight varchar(100), White_Knight_Defense varchar(100), Back_End_Defense varchar(100), FlipOver_Defense varchar(100), Voting_Plan_Defense varchar(100), Poison_Pill varchar(100), Creeping_Acquisition varchar(100), Repurchase_Defense varchar(100), Pacman_Defense varchar(100), Lockup_FlagY_N varchar(100), Tgt_Lockup varchar(100), Greenmail varchar(100), Asset_Lockup varchar(100), Scorched_Earth_Defense varchar(100), SelfTender_Defense varchar(100), Stock_Lockup varchar(100), Defense varchar(100), White_Squire varchar(100), Acq_Includes_Employees varchar(100), Acq_Includes_an_ESOP varchar(100), Sig_Family_Ownership_Tgt varchar(100), Acq_Lockup varchar(100), Financial_Acq varchar(100), Step_Spinoff varchar(100), Acq_Includes_Mgmt varchar(100), Open_Market_Purchases varchar(100), Proxy_Fight varchar(100), Purpose_Code varchar(100), Related_Deals varchar(100), Foreign_Provider_Funds varchar(100), Stock_Swap varchar(100), Asset_Swap_Flag varchar(100), Sweep_the_Street varchar(100), SelfTender varchar(100), Tender_offer varchar(100), Unsolicited varchar(100), Acq_TermFee varchar(100), Tgt_TermFee varchar(100), Forced_vote_provisions_FlagY_N varchar(100), Walkaway varchar(100), Acq_Ultimate_Add1 varchar(100), Acq_Ultimate_Add2 varchar(100), Tgt_Ultimate_Add1 varchar(100), Tgt_Ultimate_Add2 varchar(100), Acq_stockEx varchar(100), Tgt_stockEx varchar(100), Tgt_Net_Sales varchar(100), Tgt_Net_Assets varchar(100), Tgt_Total_Assets varchar(100), Tgt_Common_Equity varchar(100), Tgt_BVpersh_LTMUS varchar(100), Tgt_IntangibleA varchar(100), Tgt_RD varchar(100), Tgt_Tot_liab varchar(100) );
The column tgt_name seemed to have repeated twice in our data. To save time, I've just added 2 at the end of the column name for any such occurrence.
--COPY 52746
SDC Table with pairs of unique CUSIP and Announcement dates(Jake)
acq_cusip | acq_prnt_cusip | acq_ultimate_cusip date_announced
Determine which of the cusips has the best coverage by checking for null values. All cusip variables have equal coverage. Picking acq_cusip.
Create a table with unique cusip (only first 6 digits) and announcement date values.
SELECT LEFT(acq_cusip, 6) AS acq_cusip, date_announced INTO cusip_announcedate FROM sdc GROUP BY acq_cusip, date_announced;
SELECT 49766
Creating compound variables from variable list and combining them (Jake)
DROP TABLE tobinq; CREATE TABLE tobinq AS SELECT conm, gvkey, cusip, datadate, extract(year from datadate) AS year, LEFT(naics,3) AS naics3, revt, CASE WHEN (CAST(ceq AS real)) = 0 THEN NULL ELSE (CAST(mkvalt AS real))/(CAST(ceq AS real)) END AS tobinq FROM compustatpull; --SELECT 464894
DROP TABLE tobinqxcompetition; CREATE TABLE tobinqxcompetition AS SELECT conm, gvkey, cusip, datadate, tobinq.year, tobinq.naics3, revt, LAG(revt) OVER (PARTITION BY gvkey ORDER BY tobinq.year) AS revtlag, tobinq, LAG(tobinq) OVER (PARTITION BY gvkey ORDER BY tobinq.year) AS tobinlag, sumsharesq, countgvkey FROM tobinq LEFT JOIN competitionfinal ON tobinq.year = competitionfinal.year AND tobinq.naics3 = competitionfinal.naics3; --SELECT 464894
DROP TABLE CPI; CREATE TABLE CPI( year int, avg real, twentytenbase real);
\COPY CPI FROM 'CPI Data.txt' WITH DELIMITER AS E'\t' HEADER NULL AS CSV;
DROP TABLE test; CREATE TABLE test AS SELECT tobinqxcompetition.*, acq_cusip, pc_cash, CASE WHEN (Tgt_stockEx = 'NYSE' OR Tgt_stockEx = 'AMEX' OR Tgt_stockEx = 'NASDAQ') THEN 1::int ELSE 0::int END AS Tgt_Public, date_announced, date_effective FROM tobinqxcompetition LEFT JOIN sdc ON LEFT(tobinqxcompetition.cusip, 6) = sdc.acq_cusip AND EXTRACT (year from tobinqxcompetition.datadate) = EXTRACT(year from sdc.date_effective);
STATA Work
All Stata work found in the following folder:
E:\McNair\Projects\Winner's Curse\Stata
Code for most recent regression:
set more off
insheet using "Estimation.txt"
log using "Estimation.log", replace
gen rmse=0
gen ressd=0
gen alpha=0
gen beta=0
tabstat acqkey, stats(min max) save
matrix acqnos=r(StatTotal)
local min=acqnos[1,1]
local max=acqnos[2,1]
forvalues acqnumber = `min'(1)`max' {
capture reg ret vret if acqkey==`acqnumber'
capture matrix betas=e(b)
capture local beta=betas[1,1]
capture local alpha=betas[1,2]
capture local rmse=e(rmse)
capture predict res,r
capture tabstat res, stats(sd) save
capture matrix resmat=r(StatTotal)
capture local ressd=resmat[1,1]
capture replace rmse=`rmse' if acqkey==`acqnumber'
capture replace ressd=`ressd' if acqkey==`acqnumber'
capture replace alpha=`alpha' if acqkey==`acqnumber'
capture replace beta=`beta' if acqkey==`acqnumber'
capture drop res
display `acqnumber'
}
/*
reg ret vret if acqkey==2
matrix betas=e(b)
local beta=betas[1,1]
local alpha=betas[1,2]
local rmse=e(rmse)
predict res,r
tabstat res, stats(sd) save
matrix resmat=r(StatTotal)
local ressd=resmat[1,1]
replace rmse=`rmse' if acqkey==2
replace ressd=`ressd' if acqkey==2
replace alpha=`alpha' if acqkey==2
replace beta=`beta' if acqkey==2
drop res
*/
drop if reldayno !=-30
outsheet using "Estimation-processed.txt", replace
Previous Papers
From the old wiki
