Changes

Jump to navigation Jump to search
2,214 bytes added ,  17:44, 5 October 2020
no edit summary
{{Project
|Has project output=Content
|Has sponsor=McNair Center
|Has title=LBO Data Description
|Has owner=James Chen,Brian Ayash,Ed Egan
|Has project status=Complete
}}
 
 
This page is included in the project [[Leveraged Buyout Innovation (Academic Paper)]]
==Work Scripts==
===SQL===
*<code>E:\McNair\Projects\LBO\LBO academic paper patent data script pre compustat</code>*<code>E:\McNair\Projects\LBO\LBO academic paper script post compustat</code>
===Stata===
*<code>E:\McNair\Projects\LBO\STATAdatasetup2</code>
*<code>E:\McNair\Projects\LBO\STATAanalysis2</code>
==Work Description==
===LBO Data and Patent Data===
See script for exact work.: <code>E:\McNair\Projects\LBO\patent data script</code>
*Get the source file for the LBOs
*Load the LBO data into a dbase
*match Match the LBO data to the patent data
:<code>Z:\allpatentsprocessed\LBO Patent matched</code>
*Note - all data in '''allpatentsprocessed ''' database
:access it by logging on to <code>researcher@McNair DBServ:/bulk/allpatentsprocessed</code>
*Create Table firstjoin_cleaned: Join patent data to assignee data, creating firstjoin_cleaned. firstjoin_cleaned shows organization names of assignees from the patent data and their patent numbers *Create Table secondjoin_cleaned: join firstjoin_cleaned to matchassignees data, creating secondjoin_cleaned. secondjoin_cleaned shows standardized organization names, organization names, and patent numbers  OLD PROCESS (NOT USED ANYMORE) join secondjoin_cleaned to lbopatentmatch data, creating thirdjoin_cleaned. thirdjoin_cleaned shows LBO's names, standardized organization names, and their patent numbers in order to show lbo's that had patents a text file of the final copy can be found here <code>Z:\allpatentsprocessed\LBO Patent Data Joined</code> create table lbopatentinfo showing lbo names, patent numbers, and grant dates of the patents use lbopatentinfo to create table lbopatentsummary to show lbo names, amount of patents they have, minimum grant date years, maximum grant date years, and average grant date years import lbo data matched against itself into table(lbodistinct), then use that table and lbopatentsummary to create to create lbofinal. lbofinal then shows distinct lbonames, number of patents, minimum grant year, maximum grant year, and average grant year use lbofinal to create lboreallyfinal. lboreallyfinal is the same as lbofinal but with null space in number of patents replaced with 0 FINAL DATA IN THE FOLLOWING FOLDER <code>E:again\McNair\Projects\LBO</code> #After getting lboreallyfinal, create a table with all of its columns in addition to the lbo deal numbers, just look inside and whether it was public to private or private to private in the following steps #import LBOcleaneddata.txt into table dealnumbxcleannames. it will have the deal numbers and cleaned original lbo names (duplicates not removed) #import Originallbomatch.txt into table lbonamesxcleannames. it will have the matched standardized lbo names and the cleaned original lbo names #join those two tables together to reach the standardized lbo names with the deal numbers in table lbonamesxdealnumb, and create a column that says whether the deal was public to private or private to private in the table lboxpriv #join lbo_list_and_data and lbopatentmatch together using lboxpriv as a go between. this provides a reference in case we want to join using stdorgname to the patent database #join lbo_list_and_data and lboreallyfinal together using lboxpriv as a go between. this is the final table it can be found in the following folder under the name lbopatentsreallyfinal :<code>E:\McNair\Projects\LBO\Data Sets</code> ===Introduction of Compustat and Preparation for Stata===See script for exact work: <code>E:\McNair\Projects\LBO\LBO academic paper script post compustat</code>
*join firstjoin_cleaned to matchassignees data, creating secondjoin_cleaned. secondjoin_cleaned shows standardized organization names, organization names, and patent numbersCreate tables as follows:
*join secondjoin_cleaned to lbopatentmatch data, creating thirdjoin_cleaned. thirdjoin_cleaned shows LBO's names, standardized organization names, and their patent numbers in order to show lbo's that had patents:a text file of 'SDC''' with SDC data imported from the final copy can be found here::<code>Z:\allpatentsprocessed\initial LBO Patent Data Joined</code>data.
*create table lbopatentinfo showing lbo names, patent numbers, and grant dates of the patents'''LBOCOMPUSTAT4''' with Compustat data imported.
*use lbopatentinfo to create '''dealnumbxgvkey''' selecting deal numbers and gvkeys from table lbopatentsummary to show lbo names, amount of patents they have, minimum grant date years, maximum grant date years, and average grant date yearssdc.
*import lbo data '''compustatnamematch''' with compustat names matched against itself into table(lbodistinct), then use that table and lbopatentsummary to create to create lbofinal. lbofinal then shows distinct lbonames, number of patents, minimum grant year, maximum grant year, and average grant yearthemselves imported
*use lbofinal to create lboreallyfinal. lboreallyfinal is the same as lbofinal but '''stdcompnamestdorgnamematched''' with null space in number of patents replaced with 0standardized compustat names matched against stdorgname
*FINAL DATA IN THE FOLLOWING FOLDER:<code>E:\McNair\Projects\LBO</code>'''matchedlbocompnames''' with standardized original lbo names matched to standardized compustat names
*After getting lboreallyfinal'''compnamematchlbocompnames''' with standardized compustat names, create a table with all of its columns in addition to the lbo deal numberscompustatnames, and whether it was public to private or private to private in the following steps#import LBOcleaneddata.txt into table dealnumbxcleannames. it will have the deal numbers and cleaned original lbo names (duplicates not removed)#import Originallbomatch.txt into table lbonamesxcleannames. it will have the matched standardized lbo names and the cleaned original lbo names#join those two tables together to reach the standardized lbo names with the deal numbers in table lbonamesxdealnumb, and create a column that says whether the deal was public to private or private to private in the table lboxpriv#join lbo_list_and_data and lbopatentmatch together using lboxpriv as a go between. this provides a reference in case we want to join using stdorgname to the patent database#join lbo_list_and_data and lboreallyfinal together using lboxpriv as a go between. this is the final table it can be found in the following folder under the name lbopatentsreallyfinal:<code>E:\McNair\Projects\LBO\Data Sets</code>
===Introduction of Compustat *'''stdcompnamepatentjoin''' with variables standardized compustat names and Preparation for Stata===See script for exact work.patent numbers
*Create table sdc '''compustatpatentinfo''' with SDC data imported from the initial LBO data.variables standardized compustat names, patent numbers, and grant dates
*Create table LBOCOMPUSTAT4 '''stdcompnamexgvkeycleaned''' with Compustat data imported.**Note: There were 3 prior Compustat pulls. The 1st is used to create several cross reference tables that are not made obsolete by the subsequent pulls.variables standardized compustat names and gvkeys
*Create table dealnumbxgvkey selecting deal numbers and '''ALLGVKey''' with all distinct gvkeys from table sdc.stdcompnamexgvkeycleaned
*Create table compustatnamematch '''AllYears''' with compustat names matched against themselves importedall years from 1970 to 2016
*Create table stdcompnamestdorgnamematched '''KeysXYears''' with standardized compustat names matched against stdorgnamevariables gvkey and year. Each gvkey corresponds to 47 years.
*Create table stdcompnamepatentjoin '''keysxyearsxstdcompname''' with variables gvkeys, years, and standardized compustat names and patent numbers
*Create table compustatpatentinfo '''compupatentyearsummary''' with variables standardized compustat names, patent numbersgvkeys, years, and grant datesnumber of patents per year
*Create table stdcompnamexgvkeycleaned '''dealnumbxgvkeyxlboyeartemp''' with variables standardized compustat names deal numbers, gvkeys, LBO years, and gvkeysexit years
*Create table ALLGVKey '''dealnumbxgvkeyxlboyear''' with all distinct variables deal numbers, gvkeys from stdcompnamexgvkeycleaned, lbo years, exit years, entry year 1, entry year 2, exit year 1, and exit year 2
*Create table AllYears '''compupatentlboyearsummary''' with all variables standard compustat names, gvkeys, years from 1970 to 2016, number of patents per year, and lbo entries/exits in each year
*Create table KeysXYears '''statastaging''' with all variables gvkey and year. Each gvkey corresponds from compupatentlboyearsummary in addition to 47 years.all the pulled compustat variables
*Create table keysxyearsxstdcompname '''sicnaicsconversion''' with variables gvkeys, years, sic codes and standardized compustat namesnaics codes as a conversion table for adding naics that correspond to the sic codes in blank rows. Table from https://www.census.gov/eos/www/naics/concordances/concordances.html
*Create table compupatentyearsummary '''statastagingnew''' with variables standardized compustat namestwo extra columns, gvkeysconcordsic and concordnaics, years, and number of patents per yearthen export to stata
*Create table dealnumbxgvkeyxlboyeartemp with variables deal numbers, gvkeys, ===Stata Work===See script for exact work: <code>E:\McNair\Projects\LBO years, and exit years\STATAdatasetup2</code>
====Setup====*Create table dealnumbxgvkeyxlboyear with Label Compustat/patent variables deal imported from SQL *Generate compound variables and label them*Set the data as panel data and a time series*Set the data for survival analysis*Generate variable of first two numbersin naics code/lagged variable of first two numbers*Generate log of non-ratio variables*Generate lagged variables*Find first fiscal year in which data appears for each company*Find last fiscal year in which data appears for each company, gvkeysor set it to 2016*Recode the industry (based on NAICS2) to Manufacturing, lbo yearsRetail, exit years, entry year 1, entry year 2, exit year 1Prof. Services, and exit year 2other*Generate additional lagged variables
====Analysis====See script for exact work: <code>E:\McNair\Projects\LBO\STATAanalysis2</code>*Create table compupatentlboyearsummary with Summary of variables standard compustat namesintended for use in hazard model (mean, gvkeys, years, number count)*Run Cox Proportional Hazard Model multiple times to determine statistically significant variables*Regress on variables that are questionable to determine which may be correlated*Run Cox Hazard again with final list of patents per year, variables*Run diff-and lbo entries/exits in each year-diff
*Create table statastaging with all variables from compupatentlboyearsummary in addition to all the pulled compustat variables and export to Stata==Regressions & Tables==
===Stata Work===The following is a summary of our variables intended for use in a Cox Proportional Hazards Regression as well as one regression. *<code>E:\McNair\Projects\LBO\Saved STATA Tables.txt</code>
==Compustat Variables==
RE -- Retained Earnings
==ReferencesSQL Fixes==[[(Moved here from deleted page: LBO Lit Review]]Fix List) *Improve SIC to NAICS fill in further**Manually add in conversions from three digit SIC (e.g., 1110) to three digit NAICS (e.g., 134)**Re-import into STATA
==Resources==
WRDS USER: mcnair
WRDS PASS: 9Mil2015
http[[category://www.axial.net/forum/how-private-equity-screens-lbo-candidates/ - Variables that private equity firms examine to decide whether or not to lboInternal]]

Navigation menu