7,612
edits
Changes
Jump to navigation
Jump to search
==Paper Discussion==
Variables & Where Also, good to Find Themknow:{| class="wikitable sortable" style="border: 1px solid darkgray; bgcolor: #f9f9f9"| '''Variable''' || '''Location'''|Best Identifier to Use in RiskMetrics Directors-Changes to Identifier between Legacy and Current Files| Actual Return || CRSP| 1. The primary identifier in the current dataset is year-| Abnormal Return || CRSP|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. | Average Return || CRSP 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.| Weighted Average Return || CRSP|-| Cumulative Ab 4. Return || CRSP|Each time IRRC -| Boom || SDC|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.| Competition || Compustat 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. |-| Distance || SDC + google maps|-| IT Fixed Effect || SDC|-| Idiosyncratic Volatility || CRSP 6. Be very careful when combining the legacy data (Calculatedthrough 2006)|-| Industry Fixed Effect || SDC|-| Momentum || CRSP and the current data (Calculatedfrom 2007)|-| 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|}They were separated because the collection methods and meaning of some variables changed.
Boom - Created==Paper Discussion==
Competition - We need to determine how many other competitors (in the same industry, given by first 3 digits of NAICS code) the acquirer had in the year of the acquisition. To do so, create a table with 3 digit naics codes, the year, and the count of the naics codes in the year minus one then group by naics and year from crspfinal. Then join back to main table on naics code.
IT Fixed Effect -
Winner's Curse in Acquisitions (Academic Paper) (view source)
Revision as of 18:14, 22 March 2017
, 18:14, 22 March 2017no edit summary
{{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
}}
==Summary==
This paper finds evidence consistent with a Winner's Curse in the acquisitions of privately-held firms. This Winner's Curse is aggravated when there are more potential bidders and/or greater information asymmetries.
The paper was accepted for publication in the Quarterly Review of Economics and Finance in February of 2017.
==Dbase Notes==
--SDC_TABLE.sql --START AT NEW CODE
SDC --Raw data in
SDCBase --No dups
SDCV2 --Code and restrict 40216
SDCNo --Build AcqNo
SDCA1 --Add previous acqs
SDCA2 --Add bin for IT (from SDCwIT)
SDCA3 --Add bin for pub/priv (from SDC_test)
SDCA4 --Add indu FE (40216)
--CRSP_TABLE.sql
CRSPFinal --Processed Raw
CRSPMain --w/ cusip6
CRSPRelNoInc --Add in the DayNo and lose irrelevant CUSIP
EstimationBaseNoDups
Estimation1-5 5 --output tables
EstimationTotal --THIS IS LIKELY DEPRECATED
Competition
EventBase --Only SDC relevant CUSIP -3 to +3
...
Flatten1 -- Rn3 to Rp3 and VRn3 to VRp3
Flatter -- Collapse 32109
EstimationOutput --Load it back up 30950
AbnormalReturn1 --Join back to Flatter
ABR2 --Abnormal returns (plus momentum)
AbRet --CARs 30950
-------------
Turnover --NEEDS REWORKING
tobinq --
CPI
Below that is supersceeded
--GOV_Table and GOVERNANCE_NOTES.txt
--Malmendier_tate.sql
==List of Variables==
Variables & Where to Find Them:
{| class="wikitable sortable" style="border: 1px solid darkgray; bgcolor: #f9f9f9"
| '''Variable''' || '''Location''' || '''Table'''
|-
| Actual Return || CRSP || A1
|-
| Abnormal Return || CRSP || A1
|-
| Average Return || CRSP || STATA
|-
| Weighted Average Return || CRSP || STATA
|-
| Cumulative Ab. Return || CRSP || A1
|-
| Boom || Compustat || tobinxcompxsdcboom
|-
| Competition || Compustat || tobinxcompxsdcboom
|-
| Distance || SDC + google maps || D.N.E.
|-
| IT Fixed Effect || SDC || SDCA4
|-
| Idiosyncratic Volatility || CRSP (Calculated) || A1
|-
| Industry Fixed Effect || SDC || SDCA4
|-
| Momentum || CRSP (Calculated) || A1
|-
| Private Financial Data || CRSP(SDC) || SDCA4
|-
| Percent Cash || SDC || SDCA4
|-
| Previous Acquisitions || SDC || SDCA4
|-
| Sales || Compustat || tobinxcompxsdcboom
|-
| 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.
|-
| CEO Overconfidence || Execucomp || malmendier_Tate
|}
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 [https://wrds-web.wharton.upenn.edu/wrds/query_forms/variable_documentation.cfm?vendorCode=CRSP&libraryCode=crspa&fileCode=ccmfunda&id=bspr]. 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==
*Data dictionary for SDC MA: http://www.edegan.com/repository/DD-ThomsonSDCMA.txt
==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 inGOV_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\SQL\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\GovernanceVARSSQL\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.
==Importing the CRSP data into the `winner' database (Amir) ==
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==
*[[E-mail Chain on Winner's Curse]]
*[[Malmendier & Tate CEO Overconfidence]]
*[[Overconfidence Papers]]
==Resources==
*[[SDC Platinum]]
*[[Venture Expert]]
