7,613
edits
Changes
Jump to navigation
Jump to search
This can be done with SQL faster than in ExcelFollow a similar procedure for the IPOs. Be aware There are 3134 IPOs, of which 2551 are all good (3 out of 3), 495 have bad dates or states, and 87 are left. For these 87, almost all are pairs (presumably the ones that aren't had a bad date) where there are two issue dates and we should take the join back must use statecode to deal with: Mobile Technologies LLC Mobile Technologies PA 4/28/2011 8/12/2013 Mobile Technology Incminimum issue date. Mobile Technology Inc CA 12/1/1985 6/28/1990
AlsoThe result will be (with portcokey unique and valid):*MACleanNoDups (8344) - PortCoKey, there is an issue with multiples not showing up as multiple matchesconamestd, targetnamestd, eMA Info..g.: ARCA BIOPHARMA Inc ARCA biopharma Inc Hall ARCA BIOPHARMA Inc ARCA BIOPHARMA Inc CO 3/1/2006 ARCA biopharma Inc ARCA biopharma Inc CO 9/25/2008 1 1 1 3 0 1 ARCA BIOPHARMA Inc ARCA biopharma Inc Hall ARCA BIOPHARMA Inc ARCA Biopharma Inc CO 2/3/2003 ARCA biopharma Inc ARCA biopharma Inc CO 9/25/2008 1 1 1 3 0 1*IPOCleanNoDups (2600) - PortCoKey, conamestd, issuernamestd, IPO Info...
This is because there aren't multiple variants - just multiples of the same variant. It happens when there are two records for one company with the same state but different datefirstinv dates. One solution is to build an exclusion list of portco keys (those with the coname, statecode, and min(datefirstinv)) and exclude those keys everywhere applicable. Instead we went back to the original portco table and fixed it properly so that companybasecore doesn't have this issue.===Exits===
3134 IPOs, of which 2551 Next we need to determine precedence if a PortCo has both an IPO and an M&A record. There are all good 173 such records. This is done in the query '''ExitKeys'''. PortCoExit (3 out of 349136)is then built. This version fixes an error from before, 495 have bad dates or stateswhere announceddate and not effectivedate was being used as exitdate for M&As. Note that exitvaluem is left as null for non-exits. PortCoAliveDead still uses a 5 year interval, but the date truncation date is updated to 9/22/2019. exitvaluem is then updated to 0 for those that are deemed dead without exit and 87 left as null for non-exits. Exitdate and year are left. For these 87as is, almost all are pairs as new fields (presumably the ones that aren't had a bad datedeaddate, deadyear) where there are two issue dates and we should take the minimum issue datecreated appropriately.
→Matching
perl .\Matcher.pl -mode=2 -file1="IPODistinctIssuer.txt" -file2="IPODistinctIssuer.txt"
Then match clean up the PortCo table by removing any duplicate firms with different datefirstinv records. Following this construct portcokeysstd (to be used to restrict keys through out) and companybasecore (49136). Match between and reviewMAs and IPOs:
perl .\Matcher.pl -mode=2 -file1="PortCoMatchInput.txt" -file2="MAMatchInput.txt"
*Of the remaining (463), take the min date announced.
