Difference between revisions of "VentureXpert Data"

From edegan.com
Jump to navigation Jump to search
Line 347: Line 347:
  
 
==Instructions on Matching PortCos to Issuers and M&As From Ed==
 
==Instructions on Matching PortCos to Issuers and M&As From Ed==
 +
===Company Standardizing===
  
 
Get portco keys
 
Get portco keys
Line 396: Line 397:
  
  
−
Now prepare to repeat that process for M&A's and IPOs:
+
===MA Cleaning and Matching===
−
*For M&As your keys (for now) will be targetname, statecode, dateannounced
+
First remove all of the duplicates in the MA data. Do this by running aggregate queries on every column except for the primary key:
−
*For IPOs your keys (for now) will be issuername, statecode, issuedate
+
DROP TABLE MANoDups;
−
*FIRST CLEAN EACH DATASET. The easiest way to remove duplicates (if you have lots of them) is to use an aggregate query:
+
CREATE TABLE MANoDups AS
 +
SELECT targetname, targetstate, announceddate, min(effectivedate) AS effectivedate, MIN(acquirorname) as acquirorname, MIN(acquirorstate) as acquirorstate, MAX(transactionamt) as
 +
transactionamt, MAX(enterpriseval) as enterpriseval, MIN(acquirorstatus) as acquirorstatus
 +
FROM mas
 +
GROUP BY targetname, targetstate, announceddate ORDER BY targetname, targetstate, announceddate;
 +
--119374
  
−
  DROP TABLE IPOCoreNoDups;
+
SELECT COUNT(*) FROM(SELECT DISTINCT targetname, targetstate, announceddate FROM manodups)a;
−
  CREATE TABLE IPOCoreNoDups as
+
--119374
−
  SELECT issuername, statecode, issuedate, max(var1) as var1, avg(var2) as var2, ...
+
 
−
  FROM IPOCore GROUP BY issuername, statecode, issuedate ORDER BY issuername, statecode, issuedate;
+
Since these counts are equivalent, the data set is clean. Then get all the primary keys from the table and copy the distinct target names into a text file.
−
   
+
 
−
  Note that you need all vars to be inside aggregates and that you should choose the aggregate function sensibly by looking at the data. Generally use MAX for amounts and MIN for dates. You can also use MAX or MIN on text strings.
+
DROP TABLE makey;
 +
CREATE TABLE makey AS
 +
SELECT targetname, targetstate, announceddate
 +
FROM manodups;
 +
--119374
 +
 
 +
\COPY (SELECT DISTINCT targetname FROM makey) TO 'DistinctTargetName.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV;
 +
--117212
 +
 
 +
After running this list of distinct target names through the matcher, put the standardized MA list into the data base.
 +
 
 +
DROP TABLE MaStd;
 +
CREATE TABLE MaStd (
 +
  targetnamestd varchar(255),
 +
  targetname varchar(255),
 +
  norm varchar(100),
 +
  x1 varchar(255),
 +
  x2 varchar(255)
 +
);
 +
 
 +
\COPY mastd FROM 'DistinctTargetName.txt-DistinctTargetName.txt.matched' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
 +
--117212
 +
 
 +
Then match the list of standardized names back to the makey table to get a table with standardized keys and primary keys. This will be your input for matching against port cos.
 +
 
 +
  DROP TABLE makeysstd;
 +
  CREATE TABLE makeysstd AS
 +
SELECT B.targetnamestd, A.*
 +
FROM makey AS A
 +
JOIN mastd AS B ON A.targetname=B.targetname;
 +
--119374
 +
 
 +
  \COPY makeysstd TO 'MAMatchInput.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
 +
  --119374
 +
 
 +
===IPO Cleaning and Matching===
 +
The process is the same for IPOs.
 +
 
 +
DROP TABLE iponodups;
 +
CREATE TABLE iponodups
 +
  AS SELECT issuer, statecode, issuedate, MAX(principalamt) AS principalamt, MAX(proceedsamt) AS proceedsamt, MIN(naiccode) as naicode, MIN(zipcode) AS zipcode, MIN(status) AS status,  
 +
MIN(foundeddate) AS foundeddate
 +
  FROM ipos GROUP BY issuer, statecode, issuedate ORDER BY issuer, statecode, issuedate;  
 +
  --11149
 +
 
 +
SELECT COUNT(*) FROM(SELECT DISTINCT issuer, statecode, issuedate FROM iponodups)a;
 +
  --11149
  
−
And now build the same stacks as before but to create Issuerkeystd and TargetKeystd (or whatever you call them). Make sure that issuerstd (and targetnamestd) is in the first column.
+
DROP TABLE ipokeys;
 +
CREATE TABLE ipokeys AS
 +
SELECT issuer, statecode, issuedate
 +
FROM iponodups;
 +
--11149
  
−
Now match Portcokeystd to Issuerkeystd, and match Portcokeystd to Targetkeystd
+
\COPY (SELECT DISTINCT issuer FROM ipokeys) TO 'IPODistinctIssuer.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
−
*Move the files into the input director as before
+
--10803
−
*Run the matcher script but WITHOUT mode 2:
 
  
−
  perl Matcher.pl -file1="PortCoMatchInput.txt" -file2="IssuerMatchInput.txt"
+
  DROP TABLE ipokeysstd;
−
  perl Matcher.pl -file1="PortCoMatchInput.txt" -file2="TargetMatchInput.txt"
+
  CREATE TABLE ipokeysstd (
 +
    issuerstd varchar(255),
 +
    issuer varchar(255),
 +
    norm varchar(100),
 +
    x1 varchar(255),
 +
    x2 varchar(255)
 +
  );
 
   
 
   
−
Open each of these files in excel and mark good matches with 1s and bad matches with 0s by adding columns to compare dates, states, etc, and filtering.
+
\COPY ipokeysstd FROM 'IPODistinctIssuer.txt-IPODistinctIssuer.txt.matched' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
 +
--10803
 +
 
 +
DROP TABLE ipostd;
 +
CREATE TABLE ipostd AS
 +
SELECT B.issuerstd, A.*
 +
FROM ipokeys AS A
 +
JOIN ipokeysstd AS B ON A.issuer=B.issuer;
 +
--11149
 +
 
 +
\COPY ipostd TO 'IPOMatchInput.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
 +
--11149
  
−
When you are done:
+
I generally use MAX for amounts and MIN for dates. I also chose to use MIN on text strings.
−
*Build a new sheet of just good matches.
 
−
*Save the excel files
 
−
*Copy each of your match sheets to a text file
 
−
*CREATE TABLE to reflect the data you are going to load (include std names and keys)
 
−
*\COPY the data (using the exact copy command above but changing the table and file names) into the table
 
−
*Celebrate!
 
−
*Next we'll deal with any firms that have an IPO and an M&A and decide which we'll keep
 
−
*And then we'll join in the chosen IPO and M&A data and move on!
 
  
 
==Cleaning IPO and MA Data==
 
==Cleaning IPO and MA Data==

Revision as of 10:21, 7 August 2018


McNair Project
VentureXpert Data
Project logo 02.png
Project Information
Project Title VentureXpert Data
Owner Augi Liebster
Start Date June 20, 2018
Deadline
Primary Billing
Notes
Has project status Active
Copyright © 2016 edegan.com. All Rights Reserved.


Augi Liebster (Work Log)

Relevant Former Projects

  1. Venture Capital (Data)
  2. Retrieving US VC Data From SDC
  3. VC Database Rebuild

Location

My scripts for SDC pulls are located in the E drive in the location: 

E:\VentureXpertDatabase\ScriptsForSDCExtract

My successfully pulled and normalized files are stored in the location: 

E:\VentureXpertDatabase\ExtractedDataQ2

My script for loading data is in one big text file in the location: 

E:\VentureXpertDatabase\vcdb3\LoadingScripts

There are a variety of SQL files in there with self explanatory names. The file that has all of the loading scripts is called LoadingScriptsV1. The folder vcdb2 is there for reference to see what people before had done. ExtractedData is there because I pulled data before July 1st, and Ed asked me to repull the data.

Goal

I will be looking to redesign the VentureXpert Database in a way that is more intuitively built than the previous one. I will also update the database with current data.

Initial Stages

The first step of the project was to figure out what primary keys to use for each major table that I create. I looked at the primary keys used in the creation of the VC Database Rebuild and found primary keys that are decent. I have updated them and list them below:

  1. CompanyBaseCore- coname, statecode, datefirstinv
  2. IPOCore- issuer, issuedate, statecode
  3. MACore- target name, target state code, announceddate
  4. Geo - city, statecode, coname, datefirst, year
  5. DeadDate - conname, statecode, datefirst, rounddate (tentative could still change)
  6. RoundCore- conname, statecode, datefirst, rounddate
  7. FirmBaseCore - firmname
  8. FundBaseCore - fund name (firstinvedate doesn't work because not every row has an entry)

These are my initial listings and I will come back to update them if needed.

The second part of the initial stage has been to pull data from the SDC Platinum platform. I did it in July to ensure that I had two full quarters of data.


SDC Pull

When pulling data from SDC, it is a good idea to look for previously made rpt files that have the names of the pulls you will need to do. They have already been created and will save you a lot of work. The rpt files that I used are in the folder VentureXpertDB/ScriptsForSDCExtract. The files will come in pairs with one being saved as an ssh file and one as a rpt file. To update the dates to make them recent, go into the ssh file of the pair and change the date of last investment. When you open SDC, you will be given a variety of choices for which database to pull from. For each type of file chose the following:

  1. VentureXpert - PortCo, PortCoLong, USVC, Firms, BranchOffices, Funds, Rounds, VCFirmLong
  2. Mergres & Acquisition - MAs
  3. Global New Issues Databases - IPOs

Help on pulling data from SDC is on the SDC Platinum (Wiki) page.

VCFund Pull Problem

When pulling the VCFund1980-Present, I encountered two problems. One, is that SDC is not able to sort through the funds that are US only with the built in filters. Two, there are multiple rpt files that specify different variables for the fund pull. I pulled from both to be safe, but in the VC Database Rebuild page there is a section on the fund pull where Ed specifies which rpt file he used to pull data from SDC. Regardless I have both saved in the ExtractedData folder. After speaking with Ed, he told me to use the VCFund1980-present.rpt file to extract the data. Had various problems extracting data including freezing of SDC program or getting error Out of Memory. Check the SDC Platinum (Wiki) page to fix these issues.


Loading Tables

When I describe errors I encountered, I will not describe them using line numbers. This is because as soon as any data is added, the line numbers will become useless. Instead I recommend that you copy the normalized file you are working with into an excel file and using the filter feature. This way you can find the line number in your specific file that is causing errors and fix it in the file itself. The line numbers that PuTTY errors display are often wrong, so I relied on excel to discover the error fastest. If my instructions are not enough for you to find the error, my advice would be to find key words in the line that PuTTY is telling you is causing errors and filter through excel. 


DROP TABLE roundbase;
CREATE TABLE roundbase (
  coname varchar(255),
  rounddate date,
  updateddate date,
  foundingdate date,
  datelastinv date,
  datefirstinv date,
  investedk real,
  city varchar(100),
  description varchar(5000),
  msa varchar(100),
  msacode varchar(10),
  nationcode varchar(10),
  statecode varchar(10),
  addr1 varchar(100),
  addr2 varchar(100),
  indclass varchar(100),
  indsubgroup3 varchar(100),
  indminor varchar(100),
  url varchar(5000),
  zip varchar(10),
  stage1 varchar(100),
  stage3 varchar(100),
  rndamtdisck real,
  rndamtestk real,
  roundnum integer,
  numinvestors integer
);

\COPY roundbase FROM 'USVC1980-2018q2-Good.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--151549

The only error I encountered here was with Cardtronic Technology Inc. Here there was a problem with a mixture of quotation marks which cause errors in loading. Find this using the excel trick and remove it manually. 

DROP TABLE ipos;
CREATE TABLE ipos (
  issuedate date,
  issuer varchar(255),
  statecode varchar(10), 
  principalamt money, --million
  proceedsamt money, --sum of all markets in million
  naiccode varchar(255), --primary NAIC code
  zipcode varchar(10),
  status varchar (20),
  foundeddate date
);

\COPY ipos FROM 'IPO1980-2018q2-NoFoot-normal.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--12107

I encountered no errors while loading this data. 


DROP TABLE branchoffices;
CREATE TABLE branchoffices (
  firmname varchar(255),
  bocity varchar(100),
  bostate varchar(2),
  bocountrycode varchar(2),
  bonation varchar(100),
  bozip varchar(10),
  boaddr1 varchar(100),
  boaddr2 varchar(100)

);

\COPY branchoffices FROM 'USVCFirmBranchOffices1980-2018q2-NoFoot-normal.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--10353

I encountered no errors while loading this data. 

DROP TABLE roundline;
CREATE TABLE roundline (
  coname varchar(255),
  statecode varchar(2),
  datelastinv date,
  datefirstinv date,
  rounddate date,
  disclosedamt money,
  investor varchar(255)
);

\COPY roundline FROM 'USVCRound1980-2018q2-NoFoot-normal-normal.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--403189

I encountered no errors while loading this data. 

DROP TABLE fundbase;
CREATE TABLE fundbase (
  fundname varchar(255),
  closedate date, --mm-dd-yyyy
  lastinvdate date, --mm-dd-yyyy
  firstinvdate date, --mm-dd-yyyy
  numportcos integer,
  investedk real,
  city varchar(100),
  fundyear varchar(4), --yyyy
  zip varchar(10),
  statecode varchar(2),
  fundsizem real,
  fundstage varchar(100),
  firmname varchar(255),
  dateinfoupdate date,
  invtype varchar(100),
  msacode varchar(10),
  nationcode varchar(10),
  raisestatus varchar(100),
  seqnum integer,
  targetsizefund real,
  fundtype varchar(100)
);

\COPY fundbase FROM 'VCFund1980-2018q2-NoFoot-normal.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--29397

There is a Ukranian fund that has stray quotation marks in its name. It is called something along the lines of "VAT "ZNVKIF "Skhidno-Evropeis'lyi investytsiynyi Fond". If this does not help, you can filter in excel using Kiev as the keyword in the city column and find the line where you are getting errors. Then manually remove the commas in the actual text file. After that, the table should load correctly. 

DROP TABLE firmbase;
CREATE TABLE firmbase(
  firmname varchar(255),
  foundingdate date, --mm-dd-yyyy
  datefirstinv date, --mm-dd-yyyy  
  datelastinv date, --mm-dd-yyyy
  addr1 varchar(100),
  addr2 varchar(100),
  location varchar(100),
  city varchar(100),
  zip varchar(10),
  areacode integer,
  county varchar(100),
  state varchar(2),
  nationcode varchar(10),
  nation varchar(100),
  worldregion varchar(100),
  numportcos integer,
  numrounds integer,
  investedk money,
  capitalundermgmt money,  
  invstatus varchar(100),
  msacode varchar(10),
  rolepref varchar(100),
  geogpref varchar(100),
  indpref varchar(100),
  stagepref varchar(100),
  type varchar(100)
);



\COPY firmbase FROM 'USVCFirms1980-2018q2-NoFoot-normal.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--15899

The normalization for this file was wrong when I tried to load the data. To fix this go to the file where you have removed the footer and find the column header titled Firm Capital under Mgmt{0Mil}. Delete the {0mil} and renormalize the file. Then everything should be ok. A good way to check this is to copy and paste the normalized file into an excel sheet and see whether the entries line up with their column header correctly. The second error I found was with the Kerala Ventures firm. Here the address has the word l"opera in it. This quotation will cause errors so find the line number using excel and remove it manually. The third error is in an area code where 1-8 is written. This hyphen causes errors. Interestingly, the line number given by PuTTY was correct, and I found it in my text file and deleted it manually. These were the only errors I encountered while loading this table. 


DROP TABLE mas;
CREATE TABLE mas (
  announceddate date,
  effectivedate date,
  targetname varchar(255),
  targetstate varchar(100),
  acquirorname varchar(255),
  acquirorstate varchar(100),
  transactionamt money,
  enterpriseval varchar(255),
  acquirorstatus varchar(150)
);
\COPY mas FROM 'MAUSTargetComp100pc1985-July2018-normal.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--119432

I encountered no problems loading in this data. 

DROP TABLE longdescription;
CREATE TABLE longdescription(
  varchar(255),
  statecode varchar(10),
  fundingdate date, --date co received first inv
  codescription varchar(10000) --long description
);

\COPY longdescription FROM 'PortCoLongDesc-Ready-normal-fixed.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--48037

I encountered no problems loading this data.

Cleaning Companybase, Fundbase, Firmbase, and BranchOffice

Cleaning Company

The primary key for port cos will be coname, datefirstinv, and statecode. Before checking whether this is a valid primary key, remove the undisclosed companies. I will explain the second part of the query concerning New York Digital Health later. 

DROP TABLE companybasecore;
CREATE TABLE companybasecore AS
SELECT * 
FROM Companybase WHERE nationcode = 'US' AND coname != 'Undisclosed Company' 
AND NOT (coname='New York Digital Health LLC' AND statecode='NY' AND datefirstinv='2015-08-13' AND updateddate='2015-10-20');
--48001

SELECT COUNT(*) FROM (SELECT DISTINCT coname, statecode, datefirstinv FROM companybasecore) AS T;
--48001

Since the count of the table and the count of the distinct primary key is equivalent, you know that the primary key is valid. In the initial cleaning of the table, I first sorted out only the undisclosed companies. This table had 48002 rows. I then ran the DISTINCT query above and found that there are 48001 distinct rows with the coname, datefirstinv, statecode primary key. Thus there must two rows that share a primary key. I found this key using the following query: 

SELECT * FROM (SELECT coname, datefirstinv, statecode FROM companybase) as key GROUP BY coname, datefirstinv, statecode HAVING COUNT(key) > 1;

The company named 'New York Digital Health LLC' came up as the company that is causing the problems. I queried to find the two rows that list this company name in companybase and chose to keep the row that had the earlier updated date. It is a good practice to avoid deleting rows from tables when possible, so I added the filter as a WHERE clause to exclude one of the New York Digital listings.

Cleaning Fundbase

The primary key for funds will be only the fundname. First get rid of all of the undisclosed funds. 

DROP TABLE fundbasenound;
CREATE TABLE fundbasenound AS 
SELECT DISTINCT * FROM fundbase WHERE fundname NOT LIKE '%Undisclosed Fund%';
--28886

SELECT COUNT(*) FROM (SELECT DISTINCT fundname FROM fundbasenound)a;
--28833

As you can see, fundbase still has rows that share fundnames. If you are wondering why the DISTINCT in the first query did not eliminate these, it is because this DISTINCT applies to the whole row not individual fundnames. Thus, only completely duplicate rows will be eliminated in the first query. I chose to keep the funds that have the earlier last investment date. 

DROP TABLE fundups;
CREATE TABLE fundups AS SELECT
fundname, max(lastinvdate) AS lastinvdate FROM fundbasenound GROUP BY fundname HAVING COUNT(*)>1;
--53

DROP TABLE fundbasecore;
CREATE TABLE fundbasecore AS
SELECT A.* FROM fundbasenound AS A LEFT JOIN fundups AS B ON A.fundname=B.fundname AND A.lastinvdate=B.lastinvdate WHERE B.fundname IS NULL AND B.lastinvdate IS NULL;
--28833

Since the count of fundbasecore is the same as the number of distinct fund names, we know that the fundbasecore table is clean. In the first query I am finding duplicate rows and choosing the row that has the greater last investment date. I then match this table back to fundbasenound but choose all the rows from fundbasecore for which there is no corresponding fund in fundups based on fund name and date of last investment. This allows the funds with the earlier date of last investment to be chosen.

Cleaning Firmbase

The primary key for firms will be firm name. First I got rid of all undisclosed firms. I also filtered out two firms that have identical firm names and founding dates. The reason for this is because I use founding dates to filter out duplicate firm names. If there are two rows that have the same firm name and founding date, they will not be filtered out by the third query below. Thus, I chose to filter those out completely. 

DROP TABLE firmbasenound;
CREATE TABLE firmbasenound AS 
SELECT DISTINCT * FROM firmbase WHERE firmname NOT LIKE '%Undisclosed Firm%' AND firmname NOT LIKE '%Amundi%' AND firmname NOT LIKE '%Schroder Adveq Management%';
--15452

SELECT COUNT(*) FROM(SELECT DISTINCT firmname FROM firmbasenound)a;
--15437

Since these counts are not equal we will have to clean the table further. We will use the same method from before. 

DROP TABLE firmdups;
CREATE TABLE firmdups AS SELECT
firmname, max(foundingdate) as foundingdate FROM firmbasenound GROUP BY firmname HAVING COUNT(*)>1;
--15

DROP TABLE firmbasecore;
CREATE TABLE firmbasecore AS
SELECT A.* FROM firmbasenound AS A LEFT JOIN firmdups AS B ON A.firmname=B.firmname AND A.foundingdate=B.foundingdate WHERE B.firmname IS NULL AND B.foundingdate IS NULL;
--15437

Since the count of firmbasecore and the DISTINCT query are the same, the firm table is now clean.

Cleaning Branch Offices

When cleaning the branch offices, I had to remove all duplicates in the table. This is because the table is so sparse that often the only data in a row would be the fund name the branch was associated with. Thus, I couldn't filter based on dates as I had been doing previously for firms and funds. The primary key is firm name. 

DROP TABLE bonound;
CREATE TABLE bonound AS
SELECT *, CASE WHEN firmname LIKE '%Undisclosed Firm%' THEN 1::int ELSE 0::int END AS undisclosedflag
FROM branchoffices;
--10353

SELECT COUNT(*) FROM(SELECT DISTINCT firmname FROM bonound)a;
--10042

Since these counts aren't the same, we will have to work a little more to clean the table. As stated above, I did this by excluding the firm names that were duplicated. 

DROP TABLE branchofficecore;
CREATE TABLE branchofficecore AS
SELECT A.* FROM bonound AS A JOIN (
		SELECT bonound.firmname, COUNT(*) FROM bonound GROUP BY firmname
		HAVING COUNT(*) =1
		) AS B
ON A.firmname=B.firmname WHERE undisclosedflag=0;
--10032

SELECT COUNT(*) FROM (SELECT DISTINCT firmname FROM branchofficecore)a;
--10032

Since these counts are the same, we are good to go. The count is 10 lower because we completely removed 10 firmnames from the listing by throwing out the duplicates.

Instructions on Matching PortCos to Issuers and M&As From Ed

Company Standardizing

Get portco keys 

DROP TABLE portcokeys;
CREATE TABLE portcokey AS
SELECT coname, statecode, datefirst
FROM portcocore;
--CHECK COUNT IS SAME AS portcocore OR THESE KEYS ARE VALID AND FIX THAT FIRST

Get distinct coname and put it in a file 

\COPY (SELECT DISTINCT coname FROM portcokeys) TO 'DistinctConame.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV

Match that to itself 

Move DistinctConame.txt to E:\McNair\Software\Scripts\Matcher\Input
Open powershell and change directory to E:\McNair\Software\Scripts\Matcher
Run the matcher in mode2:
 perl Matcher.pl -file1="DistinctConame.txt" -file2="DistinctConame.txt" -mode=2
Pick up the output file from E:\McNair\Software\Scripts\Matcher\Output (it is probably called DistinctConame.txt-DistinctConame.txt.matched) and move it to your Z drive directory

Load the matches into the dbase 

DROP TABLE PortcoStd;
CREATE TABLE PortcoStd (
   conamestd  varchar(255),
   coname   varchar(255),
   norm  varchar(100),
   x1  varchar(255),
   x2  varchar(255)
);

\COPY CohortCoStd FROM 'DistinctConame.txt-DistinctConame.txt.matched' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--YOUR COUNT

Join the Conamestd back to the portcokeys table to create your matching table 

DROP TABLE portcokeysstd;
CREATE TABLE portcokeysstd AS
SELECT B.conamestd, A.*
FROM portcokey AS A
JOIN PortcoStd AS B ON A.coname=B.coname
--CHECK COUNT IS SAME AS portcokey OR YOU LOST SOME NAMES OR INFLATED THE DATA

Put that in a file for matching (conamestd is in first column by construction) 

 \COPY portcokeysstd TO 'PortCoMatchInput.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
 --YOUR COUNT


MA Cleaning and Matching

First remove all of the duplicates in the MA data. Do this by running aggregate queries on every column except for the primary key: 

DROP TABLE MANoDups;
CREATE TABLE MANoDups AS
SELECT targetname, targetstate, announceddate, min(effectivedate) AS effectivedate, MIN(acquirorname) as acquirorname, MIN(acquirorstate) as acquirorstate, MAX(transactionamt) as 
transactionamt, MAX(enterpriseval) as enterpriseval, MIN(acquirorstatus) as acquirorstatus
FROM mas 
GROUP BY targetname, targetstate, announceddate ORDER BY targetname, targetstate, announceddate;
--119374

SELECT COUNT(*) FROM(SELECT DISTINCT targetname, targetstate, announceddate FROM manodups)a;
--119374

Since these counts are equivalent, the data set is clean. Then get all the primary keys from the table and copy the distinct target names into a text file. 

DROP TABLE makey;
CREATE TABLE makey AS
SELECT targetname, targetstate, announceddate
FROM manodups;
--119374

\COPY (SELECT DISTINCT targetname FROM makey) TO 'DistinctTargetName.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV;
--117212

After running this list of distinct target names through the matcher, put the standardized MA list into the data base. 

DROP TABLE MaStd;
CREATE TABLE MaStd (
  targetnamestd varchar(255),
  targetname varchar(255),
  norm varchar(100),
  x1 varchar(255),
  x2 varchar(255)
);

\COPY mastd FROM 'DistinctTargetName.txt-DistinctTargetName.txt.matched' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--117212

Then match the list of standardized names back to the makey table to get a table with standardized keys and primary keys. This will be your input for matching against port cos. 

DROP TABLE makeysstd;
CREATE TABLE makeysstd AS
SELECT B.targetnamestd, A.*
FROM makey AS A
JOIN mastd AS B ON A.targetname=B.targetname;
--119374

 \COPY makeysstd TO 'MAMatchInput.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
 --119374

IPO Cleaning and Matching

The process is the same for IPOs. 

DROP TABLE iponodups;
CREATE TABLE iponodups
AS SELECT issuer, statecode, issuedate, MAX(principalamt) AS principalamt, MAX(proceedsamt) AS proceedsamt, MIN(naiccode) as naicode, MIN(zipcode) AS zipcode, MIN(status) AS status, 
MIN(foundeddate) AS foundeddate
FROM ipos GROUP BY issuer, statecode, issuedate ORDER BY issuer, statecode, issuedate; 
--11149

SELECT COUNT(*) FROM(SELECT DISTINCT issuer, statecode, issuedate FROM iponodups)a;
--11149

DROP TABLE ipokeys;
CREATE TABLE ipokeys AS
SELECT issuer, statecode, issuedate
FROM iponodups;
--11149

\COPY (SELECT DISTINCT issuer FROM ipokeys) TO 'IPODistinctIssuer.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--10803

DROP TABLE ipokeysstd;
CREATE TABLE ipokeysstd (
   issuerstd varchar(255),
   issuer varchar(255),
   norm varchar(100),
   x1 varchar(255),
   x2 varchar(255)
  );

\COPY ipokeysstd FROM 'IPODistinctIssuer.txt-IPODistinctIssuer.txt.matched' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--10803

DROP TABLE ipostd;
CREATE TABLE ipostd AS
SELECT B.issuerstd, A.*
FROM ipokeys AS A
JOIN ipokeysstd AS B ON A.issuer=B.issuer;
--11149

\COPY ipostd TO 'IPOMatchInput.txt' WITH DELIMITER AS E'\t' HEADER NULL AS  CSV
--11149

I generally use MAX for amounts and MIN for dates. I also chose to use MIN on text strings.

Cleaning IPO and MA Data

It is important to follow Ed's direction of cleaning the data using aggregate function before putting the data into excel. This will keep you from a lot of manual checking that is unnecessary. When ready, paste the data you have into an excel file. In that excel file, I made three columns: one to check whether state codes were equivalent, one checking whether the date of first investment was 3 years before the MA or IPO, and one checking whether both of these conditions were satisfied for each company. I did this using simple if statements. This process is manual checking and filtering to see whether matches are correct or not and are thus extremely subjective and tedious. First, I went through and checked the companies that did not have equivalent state codes. If the company was one that I knew or the name was unique to the point that I did not believe the same name would appear in another state, I marked the state codes as equivalent. I did the same for the date of first investment vs MA/IPO date. Then I removed all duplicates that had the marking Warning Multiple Matches, and the data sheets were clean.


Process For Creating the PortCoExits Table

Even if you manually checked the excel sheet for Warning Multiple Matches with the Hall warning, there still may be duplicates. To get rid of these specify that a query where you join a table of only singular primary keys to the original table. That way you will have unique primary keys by construction.

There are two companies that have the name Masspower in the MAClean file. One is written in all caps and will thus not be caught by an aggregate function. I will select only the companies where the primary keys occurs once and join this to MAClean. I will then select needed info from MANoDps. 

DROP TABLE MACleanNoDups;
CREATE TABLE MACleanNoDups AS
SELECT A.*, effectivedate, transactionamt, enterpriseval, acquirorstatus
FROM MAClean AS A
JOIN (
	SELECT targetname, targetstate, announceddate, COUNT(*) FROM MAClean
	GROUP BY targetname, targetstate, announceddate HAVING COUNT(*)=1
	) AS B
ON A.targetname=B.targetname AND A.targetstate=B.targetstate AND A.announceddate=B.announceddate
LEFT JOIN MANoDups AS C ON A.targetname=C.targetname AND A.targetstate=C.targetstate AND A.announceddate=C.announceddate;

--7171 

SELECT COUNT(*) FROM(SELECT DISTINCT coname, statecode, datefirstinv FROM MACleanNoDups)a;
--7171

Thus the portco primary key is unique in the table. We will use this later. Now do the same for the IPOs. 


DROP TABLE IPOCleanNoDups;
CREATE TABLE IPOCleanNoDups AS
SELECT A.*, principalamt, proceedsamt, naicode as naics, zipcode, status, foundeddate
FROM IPOClean AS A
JOIN (
	SELECT issuername, issuerstate, issuedate, COUNT(*) FROM IPOClean
	GROUP BY issuername, issuerstate, issuedate HAVING COUNT(*)=1
	) AS B
ON A.issuername=B.issuername AND A.issuerstate=B.issuerstate AND A.issuedate=B.issuedate
LEFT JOIN IPONoDups AS C ON A.issuername=C.issuer AND A.issuerstate=C.statecode AND A.issuedate=C.issuedate;
--2136



SELECT COUNT(*) FROM(SELECT DISTINCT coname, statecode, datefirstinv FROM IPOCleanNoDups)a;
--2136

Now the duplicates are out of the MAClean and IPOClean data and we can start to construct the ExitKeysClean table.

Creating ExitKeysClean

First I looked for the PortCos that were in both the MAs and the IPOs. I did this using: 

DROP TABLE IPOMAForReview;
CREATE TABLE IPOMAForReview
SELECT A.*, B.targetname, B.targetstate, B.announcedate
FROM IPOClean AS A
JOIN MAClean AS B ON A.coname=B.coname AND A.statecode=B.statecode AND A.datefirstinv=B.datefirstinv;
--92

I then pulled out the IPOs that were only IPOs and MAs that were only MAs. I also added in a column that indicated whether a company underwent an IPO or a MA. 

DROP TABLE IPONoConflict;
CREATE TABLE IPONoConflict AS
SELECT A.*, 1::int as IPOvsMA
FROM IPOCleanNoDups AS A 
LEFT JOIN MACleanNoDups AS B 
ON A.coname=B.coname AND A.statecode=B.statecode AND A.datefirstinv=B.datefirstinv 
WHERE B.statecode IS NULL AND B.coname IS NULL AND B.datefirstinv IS NULL;
--2044

DROP TABLE MANoConflict;
CREATE TABLE MANoConflict AS
SELECT A.*, 0::int as IPOvsMA
FROM MACleanNoDups AS A
LEFT JOIN IPOCleanNoDups AS B 
ON A.coname=B.Coname AND A.statecode=B.statecode AND A.datefirstinv=B.datefirstinv
WHERE B.statecode IS NULL AND B.coname IS NULL AND B.datefirstinv IS NULL;
--7079

Since 2141-92=2049 and 7188-92=7096, we know that the duplicate companies were extracted successfully.

I then created a column that identifies whether a company underwent an MA or an IPO. A 0 indicates an MA and a 1 indicates an IPO. 

DROP TABLE ExitKeysCleanMA;
CREATE TABLE ExitKeysCleanMA AS
SELECT *,
CASE 
 WHEN issuername IS NULL AND issuerstate IS NULL AND issuedate IS NULL THEN 0 ELSE NULL END AS MAvsIPO
FROM exitkeyscleanmanoexit;
--7096

DROP TABLE ExitKeysCleanIPO;
CREATE TABLE ExitKeysCleanIPO AS
SELECT *,
CASE 
 WHEN targetname IS NULL AND targetstate IS NULL AND announceddate IS NULL THEN 1 ELSE NULL END AS MAvsIPO
FROM exitkeyscleaniponoexit as A;
--2049

I then wrote a query to check which date was lower and used that to indicate whether I chose the company to have undergone an MA or an IPO in the column MSvsIPO(I chose based on which process came first). A 0 in the column represented an MA being chosen and a 1 represented an IPO being chosen. 

DROP TABLE IPOMASelected; 
CREATE TABLE IPOMASelected AS 
SELECT *,
CASE 
 WHEN issuedate < announceddate THEN 1 ELSE 0 END AS MAvsIPO
FROM IPOMAForReview;
--92


Then out of this table I extracted the MAs and IPOs using the the created MAvsIPO flag: 

DROP TABLE MASelected;
CREATE TABLE MASelected AS
SELECT A.coname, A.statecode, A.datefirstinv, B.targetname, B.targetstate, B.announceddate, B.issuername, B.issuerstate, B.issuedate, B.mavsipo
FROM IPOMASelected AS B LEFT JOIN companybasecore AS A ON A.coname=B.coname AND A.statecode=B.statecode AND A.datefirstinv=B.datefirstinv WHERE mavsipo=0;
--25

DROP TABLE IPOSelected;
CREATE TABLE IPOSelected AS
SELECT A.coname, A.statecode, A.datefirstinv, B.targetname, B.targetstate, B.announceddate, B.issuername, B.issuerstate, B.issuedate, B.mavsipo
FROM IPOMASelected AS B LEFT JOIN companybasecore AS A ON A.coname=B.coname AND A.statecode=B.statecode AND A.datefirstinv=B.datefirstinv WHERE mavsipo=1;
--67

I then put together all of the IPOs that I selected into one table and all of the MAs I selected into another table. I did this using UNION statements. I did this because I didn't want to duplicate any IPOs and UNION acts as a SELECT DISTINCT statement. Thus if the number of the two tables added together equaled the final count of the table, I did not have any duplicate rows. 

DROP TABLE SelectedIPOSAll;
CREATE TABLE SelectedIPOSALL AS
SELECT A.coname, A.statecode, A.datefirstinv, A.targetname, A.targetstate, A.announceddate, A.issuername, A.issuerstate, A.issuedate, A.mavsipo FROM
ExitKeysCleanIPO AS A
UNION
SELECT IPOSelected.* FROM
IPOSelected;
--2116
--Makes sense because 2049+67=2116

DROP TABLE SelectedMASAll;
CREATE TABLE SelectedMASALL AS
SELECT A.coname, A.statecode, A.datefirstinv, A.targetname, A.targetstate, A.announceddate, A.issuername, A.issuerstate, A.issuedate, A.mavsipo FROM
ExitKeysCleanMA AS A
UNION
SELECT MASelected.* FROM
MASelected;
--7121
--Makes sense because 25+7096=7121

I then checked both of these files to make sure that their primary keys were still distinct and thus valid. 

SELECT COUNT(*) FROM(SELECT DISTINCT targetname, targetstate, announceddate FROM SelectedMasAll)a;
--7121

SELECT COUNT(*) FROM(SELECT targetname, targetstate, announceddate FROM SelectedMasAll)a;
--7121

SELECT COUNT(*) FROM(SELECT issuername, issuerstate, issuedate FROM SelectedIPOMA)a;
--2116

SELECT COUNT(*) FROM(SELECT DISTINCT issuername, issuerstate, issuedate FROM SelectedIPOMA)a;
--2116

I combined the two tables and checked the result to make sure it Unioned correctly. 

DROP TABLE SelectedIPOMA;
CREATE TABLE SelectedIPOMA AS
SELECT A.*
FROM SelectedMASALL AS A
UNION
SELECT B.*
FROM SelectedIPOSALL AS B;
--9237

SELECT COUNT(*) FROM SelectedIPOMA WHERE mavsipo=0;
--7121

SELECT COUNT(*) FROM SelectedIPOMA WHERE mavsipo=1;
--2116

SELECT COUNT(*) FROM SelectedIPOMA WHERE targetname IS NOT NULL;
--7188

SELECT COUNT(*) FROM SelectedIPOMA WHERE issuername IS NOT NULL;
--2141

Everything seems to check out, so we can move on to joining the SelectedIPOMA table to the companybasecore table to create the ExitKeysClean table. 

DROP TABLE ExitKeysClean;
CREATE TABLE ExitKeysClean AS
SELECT A.coname, A.statecode, A.datefirstinv, B.targetname, B.targetstate, B.announceddate, B.issuername, B.issuerstate, B.issuedate, B.mavsipo
FROM companybasecore AS A 
LEFT JOIN SelectedIPOMA AS B ON A.coname=B.coname AND A.statecode=B.statecode AND A.datefirstinv=B.datefirstinv;
--48000

Since 48000 is the same number of rows in the companybasecore, we know that the join was successful and no rows were added that we don't want.

POTENTIAL CAUSE OF ERROR: I am missing 5 entries from the SelectedIPOMA WHERE mavsipo=0. The answer should be 7121 but instead it is 7116. Not sure why this is happening.

UPDATE: I have found the 5 missing entires but am unsure as to why thy are not being included in the ExitKeysClean table. 

       coname       | statecode | datefirstinv |    targetname     | targetstate | announceddate | issuername | issuerstate | issuedate | mavsipo | equivalence
--------------------+-----------+--------------+-------------------+-------------+---------------+------------+-------------+-----------+---------+-------------
 Corbel & Company   | FL        | 1985-04-01   | Corbel & Co Inc   | FL          | 1993-03-29    |            |             |           |       0 |           0
 Deltak Corporation | IL        | 1971-08-01   | Deltak Corp       | MN          | 1993-12-28    |            |             |           |       0 |           0
 Wine.com           | CA        | 1995-07-01   | Wine.com          | CA          | 2001-04-27    |            |             |           |       0 |           1
 CHF Solutions Inc  | MN        | 1999-06-30   | CHF Solutions Inc | MN          | 2010-01-20    |            |             |           |       0 |           1
 Packet Design Inc  | CA        | 2000-06-13   | Packet Design LLC | CA          | 2013-03-19    |            |             |           |       0 |           0

Create the PortCoExit Table

From consulting with Ed and the VC Database Rebuild wiki, I decided to make the PortCoExit table with an mavsipo, an exitdate, an exited, and an exitvalue column. DROP TABLE PortCoExit; 

CREATE TABLE PortCoExit AS
SELECT A.coname, A.statecode, A.datefirstinv,
CASE WHEN a.mavsipo=0 THEN 0::int WHEN a.mavsipo=1 THEN 1::int ELSE NULL::int END AS mavsipo,
CASE WHEN a.mavsipo=0 THEN B.announceddate WHEN a.mavsipo=1 THEN C.issuedate ELSE NULL::date END AS exitdate,
CASE WHEN a.mavsipo=0 OR a.mavsipo=1 THEN 1::int ELSE 0::int END AS exited,
CASE WHEN a.mavsipo=0 THEN B.transactionamt WHEN a.mavsipo=1 THEN C.proceedsamt ELSE NULL::money END AS exitvalue
 FROM ExitKeysClean AS A
LEFT JOIN MANoDups AS B ON A.targetname=B.targetname AND A.targetstate=B.targetstate AND A.announceddate=B.announceddate
LEFT JOIN IPONoDups AS C ON A.issuername=C.issuer AND A.issuerstate=C.statecode AND A.issuedate=C.issuedate;
--48000

The issue that I am currently having is that the exitvalue column for MAs is always Null, and there are some weird numbers in the table. I will continue to look for these. I have been continuously checking for duplicates and validity of the primary keys, so I do not believe these problems to be due to duplicates

Retrieved from "http://www.edegan.com/mediawiki/index.php?title=VentureXpert_Data&oldid=23950"