Changes

{{McNair ProjectsAcademicPaper|Project TitleHas title=Winner's Curse in Acquistions Acquisitions (Academic Paper),|Topic AreaHas author=Economic and Business TrendsEd Egan,Jim Brander|OwnerHas RAs=Ed EganAmir Kazempour, Will Cleland, Amir K, Jake Silberman,|Start Term=Fall 2016,|Status=Active,|Deliverable=Academic Paper,|AudienceHas paper status=Academics,|Primary Billing=AccMcNair01,Published

Add the following (A=L+E):---- DONE

Also nice:---- DONE

Added just in case: ---- DONE

==Governance VariablesGovernance_Final Table (Amir)== A list of governance variables UPDATE Nov 22, 3:30 pm: All null entries are now stored as 0. Modified SQL codes can be found in GOV_Table- NULL AS ZERO.sql  Table Name: governance_final Database: winner  Data: ISS (formerly RiskMetrics) - QUERY saved under 'RiskMetrics' in WRDS Coverage:2007-2015 Summary: SELECT COUNT(*) FROM governance_final; count ------- 40005 (1 row)  SELECT COUNT(DISTINCT CUSIP) FROM governance_final; count ------- 2209 (1 row)  SELECT COUNT(DISTINCT(CUSIP, year)) FROM governance_final; count ------- 13332 (1 row)  Text file is saved in: E:\McNair\Projects\Winner's Curse\Governance\RiskMetrics-0b578b37fbb59afa.txt Variable Description is saved in E:\McNair\Projects\Winner's Curse\Governance\Var-Gov-Desc.pdf SQL code file for the table is saved in E:\McNair\Projects\Winner's Curse\Data\GovernanceVARSSQL\GOV_Table.sql The variables are coded as follows: 0 NO 1 YES NULL NULLotherwise see the following file: E:\McNair\Projects\Winner's Curse\Data\SQL\GOVERNANCE_NOTES.txt or here: BLANKCHECK NuLL/YES/NO CARVE_OUT NULL/YES/NO CBOARD NULL/YES/NO CONAME CONFVOTE NULL/YES/NO CUMVOTE NULL/YES/NO CUSIP DUALCLASS NULL/YES/NO FAIRPRICE NULL/YES/NO GPARACHUTE NULL/YES/NO LABYLW NULL/YES/NO LACHTR NULL/YES/NO LEGACYPPS_ID NO ENTRY ---> DROPPED LSPMT NULL/YES/NO LWCNST NULL/YES/NO/UNANIMOUS= 2 MAJ_VOTE_REQUIRED NULL/CAST=1/OUTSTANDING=2 MAJ_VOTE_REQUIREMENT NULL/NO=0/BY-LAWS=1/POLICY=2/CHARTER=3 MAJOR_VOTE_COMM USELESS ---> DROP PPILL NULL/YES/NO RESIGN_REQUIRE NULL/NO=0/ POLICY=1/ BYLAWS-CHARTER=2 SIC varchar(4) TICKER varchar(5) TIER DROPthis UNEQVOTE NULL =0, ELSE =1 DEPOSITORY R/TIME-PHASED/GOLDEN SHARES/FIXED VOTING RI/GOLDEN SHARE/PRIORITY SHA/PRIORITY SHARES/FIXED VOTING/YES/TIME-PHASED VOT/DEPOSITORY RECE OO_BUSCOMP NULL/YES/NO OO_CASHOUT_PA NULL/YES/NO OO_CSA NULL/YES/NO OO_DUTIES NULL/YES/NO OO_FAIRPRICE NULL/YES/NO OO_PP NULL/YES/NO OO_RPROFITS NULL/YES/NO OO_STAKEHOLDER NULL/YES/NO YEAR int RT_ID int MEETINGDATE date COMPANY_ID int STATE LEAVE AS VARCHAR CHARTER_AMEND_VOTEPCNT real FYEND int LAW_AMEND_VOTEPCNT real MTGMONTH int PRICE real SPINDEX LEAVE AS VARCHAR SPL_MEET_VOTEPCNT real SUPERMAJOR_PCNT real WRITTEN_CONSENT_VOTEPCNT real Merging with CRSP: Q) Is CUSIP in RiskMetrics current or historical? How can I use it to merge with CRSP? A) The Legacy files have the current CUSIP. With each update, WRDS updated the historical data with the latest value. Starting with the new Director and Governance files, we no longer modify the historical data. To merge any of these datasets with CRSP, you can join on the NCUSIP field. This will match a single Permco (company identifier), but you may still need to select the primary Permno because some companies issue more than 1 security. Merging EXECUCOMP AND IRRC: Q) What is the best way to merge Execucomp and IRRC (director and governance) databases? I understand they both have CUSIP, but that matching with CUSIPs isn't always ideal since the CUSIP can change over time for the same firm. A) All Execucomp (CUSIP), IRRC Governance (CN6), and IRRC Directors (CUSIP) CUSIPs are header cusips, in that they reflect the most recent CUSIP that the company has had. There are however 2 main differences between Execucomp's CUSIP and IRRC's CUSIP and CN6: 1. While Execucomp uses 8-digit CUSIP, both IRRC datasets have 6-digit CUSIPs. Therefore, you need to use a SAS function like "substr" to take the first 6-digit of Execucomp CUSIP before merging it to IRRC data. ex: cusip6 = substr(CUSIP,1,6); 2. While header convention reflect "most recent" value of an identifier variables, it is dependent on the date of update of each dataset. Therefore, for CUSIPs that change in between the time that both databases are updated, you might find some discrepancy. To overcome this issue, we recommend that you make use of the CRSP name files (ex: stocknames), that contains all historical CUSIPs that a company has had.   Also, good to know: Best Identifier to Use in RiskMetrics Directors- Changes to Identifier between Legacy and Current Files 1. The primary identifier in the current dataset is year-ticker-director_detail_id. Ticker is better populated than cusip in earlier years, but should be about the same in the latest years. For the legacy directors, director_detail_id was in use for some years and legacy_director_id for others. 2. Cusips in the legacy file are "header". With each update, WRDS modified all previous cusips for a company to match the latest. Although it may help in some cases, it may hurt in others and it is not quite the way the data was delivered. Starting with the new directors file, Cusips appear in the file as they were delivered (effectively making them historical going forward). Cusips in the legacy data were also uniformly truncated to 6 digits for consistency. The new file reports them as they are delivered, typically (but not always) 9 digits. 3. Tickers were not changed over time and appear as they were delivered. One example is legacy company id (legacy_pps_id) 25 has tickers ASN and IKN. 4. Each time IRRC - ISS - Riskmetrics was acquired, new identification methods were introduced. This is why you see legacy company and director IDs. Neither is more correct, we recommend choosing the most populated one for the years in which you are working. 5. Constructing a complete time series in the directors data is not easy. There is no single variable that is populated for all companies and all years. 6. Be very careful when combining the legacy data (through 2006) and the current data (from 2007). They were separated because the collection methods and meaning of some variables changed.

Create Table SDC(

Tgt_Industry varchar(50100), Tgt_Nation varchar(10100),

Acq_Ind varchar(10100), Acq_Nation varchar(10100), Pc_Shares_Acq floatvarchar(100), Pc_Owned_After_Transaction floatvarchar(100), Value_Transaction floatvarchar(100), Enterprise_Value floatvarchar(100), Equity_Value floatvarchar(100), Acq_CUSIP varchar(10100), Acq_prnt_CUSIP varchar(10100), Acq_Ultimate_CUSIP varchar(10100), Tgt_prnt_CUSIP varchar(10100), Tgt_Ultimate_CUSIP varchar(10100), Tgt_CUSIP varchar(10100), Acq_Primary_Ticker varchar(10100), Acq_Ultimate_Ticker varchar(10100), Tgt_Primary_Ticker varchar(10100), Tgt_Ultimate_Ticker varchar(10), Tgt_Ultimate_Ticker2 varchar(10100), Acq_NAIC varchar(10100), Acq_Ultimate_NAIC varchar(10100), Tgt_Primary_NAIC varchar(10100), Tgt_Ultimate_NAIC varchar(10100), NBid varchar(10100), Status varchar(10100), Value_Est varchar(10100), Value_Amended_Flag varchar(10100), Date_Announced_Estimated varchar(10100), Consideration_Structure varchar(10100), NConsid_off varchar(10100), NConsid_Sought varchar(10100), Consid_Structure varchar(10100), Pc_Cash varchar(10100), Pc_Other varchar(10100), Pc_Stock varchar(10100), Pc_Unknown varchar(10100), Acq_FullName varchar(10100), Tgt_Name2 varchar(30100), Acq_City varchar(30100), Tgt_City varchar(30100), Acq_Zip varchar(10100), Tgt_Zip varchar(10100), Acq_St varchar(10100), Tgt_St varchar(10100), Currency varchar(10100), Tgt_Bankrupt varchar(10100), Chlng_Deal varchar(10100), Debt_Restructuring varchar(10100), RecapDefense varchar(10100), Government_Owned_Involvement_Flag varchar(10100), Joint_Venture varchar(10100), Restructuring varchar(10100), Acq_LBO_firm varchar(10100), LBO varchar(10100), IPO_Flag varchar(10100), Tgt_LBO_firm varchar(10100), Merger_Equals varchar(10100), Reverse_Takeover varchar(10100), Deal_was_Rumor varchar(10100), Acq_is_a_White_Knight varchar(10100), White_Knight_Defense varchar(10100), Back_End_Defense varchar(10100), FlipOver_Defense varchar(10100), Voting_Plan_Defense varchar(10100), Poison_Pill varchar(10100), Creeping_Acquisition varchar(10100), Repurchase_Defense varchar(10100), Pacman_Defense varchar(10100), Lockup_FlagY_N varchar(10100), Tgt_Lockup varchar(10100), Greenmail varchar(10100), Asset_Lockup varchar(10100), Scorched_Earth_Defense varchar(10100), SelfTender_Defense varchar(10100), Stock_Lockup varchar(10100), Defense varchar(10100), White_Squire varchar(10100), Acq_Includes_Employees varchar(10100), Acq_Includes_an_ESOP varchar(10100), Sig_Family_Ownership_Tgt varchar(10100), Acq_Lockup varchar(10100), Financial_Acq varchar(10100), Step_Spinoff varchar(10100), Acq_Includes_Mgmt varchar(10100), Open_Market_Purchases varchar(10100), Proxy_Fight varchar(10100), Purpose_Code varchar(10100), Related_Deals varchar(10100), Foreign_Provider_Funds varchar(10100), Stock_Swap varchar(10100), Asset_Swap_Flag varchar(10100), Sweep_the_Street varchar(10100), SelfTender varchar(10100), Tender_offer varchar(10100), Unsolicited varchar(10100), Acq_TermFee varchar(10100), Tgt_TermFee varchar(10100), Forced_vote_provisions_FlagY_N varchar(10100), Walkaway varchar(10100), Acq_Ultimate_Add1 varchar(50100), Acq_Ultimate_Add2 varchar(50100), Tgt_Ultimate_Add1 varchar(50100), Tgt_Ultimate_Add2 varchar(50100), Acq_stockEx varchar(10100), Tgt_stockEx varchar(10100), Tgt_Net_Sales varchar(10100), Tgt_Net_Assets varchar(10100), Tgt_Total_Assets varchar(10100), Tgt_Common_Equity varchar(10100), Tgt_BVpersh_LTMUS varchar(10100), Tgt_IntangibleA varchar(10100), Tgt_RD varchar(10100), Tgt_Tot_liab varchar(10100)

Few columns seemed to have repeated twice ==STATA Work==All Stata work found in our datathe following folder: E:\McNair\Projects\Winner's Curse\Stata Code for most recent regression:  set more off insheet using "Estimation.txt" log using "Estimation. To log", replace gen rmse=0 gen ressd=0 gen alpha=0 gen beta=0 tabstat acqkey, stats(min max) save time matrix acqnos=r(StatTotal) local min=acqnos[1,1] local max=acqnos[2, I1] forvalues acqnumber = `min'(1)`max' { capture reg ret vret if acqkey==`acqnumber've just added capture matrix betas=e(b) capture local beta=betas[1,1] capture local alpha=betas[1,2 at the end of the column name for any such occurrence] 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