VC Database Rebuild - Revision history

Ed: /* Creating portcoexitmaster */

2018-01-30T21:31:05Z

Creating portcoexitmaster

Ed: /* Creating portcoexitmaster */

2018-01-30T21:25:42Z

Creating portcoexitmaster

Ed: /* Creating portcoexitmaster */

2017-12-16T02:52:14Z

Creating portcoexitmaster

Ed: /* Creating portcoexitmaster */

2017-12-15T21:58:51Z

Creating portcoexitmaster

Meghanagaur: /* Cleaning firmbase */

2017-10-24T21:16:15Z

Cleaning firmbase

Meghanagaur: /* Cleaning branchoffices */

2017-10-24T21:03:49Z

Cleaning branchoffices

Meghanagaur: /* Cleaning branchoffices */

2017-10-24T21:01:20Z

Cleaning branchoffices

Meghanagaur: /* Cleaning branchoffices */

2017-10-24T20:50:57Z

Cleaning branchoffices

Meghanagaur: /* Cleaning branchoffices */

2017-10-24T20:50:32Z

Cleaning branchoffices

Meghanagaur: /* Cleaning branchoffices */

2017-10-24T20:47:10Z

Cleaning branchoffices

@@ Line 1,292: / Line 1,292: @@
 ==Creating portcoexitmaster==
-Portcoexitmaster contains the portcokey with an exitflag, ipoflag and maflag and an exit value. It is built off the companybaseipomasmaster table so be sure you've built this first. Note that the amounts are actually in b in the input. We need to times by 1000 to get to m.
+Portcoexitmaster contains the portcokey with an exitflag, ipoflag and maflag and an exit value. It is built off the companybaseipomasmaster table so be sure you've built this first. Note that the amounts are actually in m (not k) in the input.
 ALTER TABLE portcoexitbuild RENAME TO portcoexitbuildbak;
@@ Line 1,308: / Line 1,308: @@
   CASE WHEN masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS maflag,
   CASE WHEN ipoissuedate IS NOT NULL OR masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS exitflag,
-  CASE WHEN ipoissuedate IS NOT NULL THEN ipoamt::numeric::float8*1000 ELSE maamt::numeric::float8*1000 END AS
+  CASE WHEN ipoissuedate IS NOT NULL THEN ipoamt::numeric::float8 ELSE maamt::numeric::float8 END AS
   exitvaluem
   FROM portcoexitbuild;

@@ Line 1,292: / Line 1,292: @@
 ==Creating portcoexitmaster==
-Portcoexitmaster contains the portcokey with an exitflag, ipoflag and maflag and an exit value. It is built off the companybaseipomasmaster table so be sure you've built this first. Note that the amounts are actually in m not k.
+Portcoexitmaster contains the portcokey with an exitflag, ipoflag and maflag and an exit value. It is built off the companybaseipomasmaster table so be sure you've built this first. Note that the amounts are actually in b in the input. We need to times by 1000 to get to m.
 ALTER TABLE portcoexitbuild RENAME TO portcoexitbuildbak;
@@ Line 1,308: / Line 1,308: @@
   CASE WHEN masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS maflag,
   CASE WHEN ipoissuedate IS NOT NULL OR masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS exitflag,
-  CASE WHEN ipoissuedate IS NOT NULL THEN ipoamt::numeric::float8 ELSE maamt::numeric::float8 END AS
+  CASE WHEN ipoissuedate IS NOT NULL THEN ipoamt::numeric::float8*1000 ELSE maamt::numeric::float8*1000 END AS
   exitvaluem
   FROM portcoexitbuild;

@@ Line 1,293: / Line 1,293: @@
 ==Creating portcoexitmaster==
 Portcoexitmaster contains the portcokey with an exitflag, ipoflag and maflag and an exit value. It is built off the companybaseipomasmaster table so be sure you've built this first. Note that the amounts are actually in m not k.
   CREATE TABLE portcoexitbuild AS
-  SELECT coname, statecode, datefirstinv, ipoissuedate, masannounceddate, ipoprincipalamtk, mastransactionamtk
+  SELECT coname, statecode, datefirstinv, ipoissuedate, masannounceddate, ipoamt, maamt, investedk
   FROM companybaseipomasmaster;
   --44740
@@ Line 1,301: / Line 1,303: @@
   DROP TABLE portcoexitmaster;
   CREATE TABLE portcoexitmaster AS
-  SELECT coname, statecode, datefirstinv,
+  SELECT coname, statecode, datefirstinv, investedk,
   CASE WHEN ipoissuedate IS NOT NULL THEN ipoissuedate WHEN masannounceddate IS NOT NULL THEN masannounceddate ELSE NULL END AS exitdate,
   CASE WHEN ipoissuedate IS NOT NULL THEN 1::int ELSE 0::int END AS ipoflag,
   CASE WHEN masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS maflag,
   CASE WHEN ipoissuedate IS NOT NULL OR masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS exitflag,
-  CASE WHEN ipoissuedate IS NOT NULL THEN ipoprincipalamtk::numeric::float8 ELSE mastransactionamtk::numeric::float8 END AS
+  CASE WHEN ipoissuedate IS NOT NULL THEN ipoamt::numeric::float8 ELSE maamt::numeric::float8 END AS
   exitvaluem
   FROM portcoexitbuild;

@@ Line 1,292: / Line 1,292: @@
 ==Creating portcoexitmaster==
-Portcoexitmaster contains the portcokey with an exitflag, ipoflag and maflag and an exit value. It is built off the companybaseipomasmaster table so be sure you've built this first.
+Portcoexitmaster contains the portcokey with an exitflag, ipoflag and maflag and an exit value. It is built off the companybaseipomasmaster table so be sure you've built this first. Note that the amounts are actually in m not k.
   CREATE TABLE portcoexitbuild AS
   SELECT coname, statecode, datefirstinv, ipoissuedate, masannounceddate, ipoprincipalamtk, mastransactionamtk
@@ Line 1,301: / Line 1,302: @@
   CREATE TABLE portcoexitmaster AS
   SELECT coname, statecode, datefirstinv,
   CASE WHEN ipoissuedate IS NOT NULL THEN 1::int ELSE 0::int END AS ipoflag,
   CASE WHEN masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS maflag,
   CASE WHEN ipoissuedate IS NOT NULL OR masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS exitflag,
-  CASE WHEN ipoissuedate IS NOT NULL THEN ipoprincipalamtk::numeric::float8/1000 ELSE mastransactionamtk::numeric::float8/1000 END AS
+  CASE WHEN ipoissuedate IS NOT NULL THEN ipoprincipalamtk::numeric::float8 ELSE mastransactionamtk::numeric::float8 END AS
   exitvaluem
   FROM portcoexitbuild;

@@ Line 1,092: / Line 1,092: @@
   --6
-  DROP TABLE firmbasecore;
+  DROP TABLE firmbasec;
-  CREATE TABLE firmbasecore AS
+  CREATE TABLE firmbasec AS
   SELECT l.*
   FROM firmbase1 AS l
   LEFT JOIN firmbaseinclude AS r ON r.firmname = l.firmname AND r.foundingdate = l.foundingdate
   WHERE r.firmname IS NULL AND undisclosedflag = 0;
   --9446
   SELECT COUNT(DISTINCT firmname) FROM firmbasecore;
-  --9446
+  --9445
 ==Cleaning branchoffices==

@@ Line 1,126: / Line 1,126: @@
   --9450
-  SELECT COUNT(*) FROM (SELECT DISTINCT firmname, bozip FROM branchofficecore WHERE bocity IS NOT NULL) AS T;
+  SELECT COUNT(*) FROM (SELECT DISTINCT firmname FROM bocore WHERE bocity IS NOT NULL) AS T;
-  --1964
+  --1962
 There are no duplicates in firmbasecore. In firmbase, 'Amundi Private Equity Funds SA' is the only duplicate, but we dropped the one with the earlier founding date when we cleaned firmbase. Amundi has 2 branch offices in the same city (Bucharest) but different zip codes, which is why there are different counts when we use firmname/firmname,bocity vs firmname, bozip as our key.

@@ Line 1,120: / Line 1,120: @@
   SELECT * FROM branchofficesbase WHERE undisclosedflag = 0;
   --9452
-−
   SELECT COUNT(*) FROM (SELECT DISTINCT firmname, bozip FROM branchofficecore WHERE bocity IS NOT NULL) AS T;
   --1964
-  SELECT COUNT(*) FROM (SELECT DISTINCT firmname, bocity FROM branchofficecore WHERE bocity IS NOT NULL) AS T;
+  SELECT COUNT(*) FROM (SELECT DISTINCT firmname FROM branchofficecore WHERE bocity IS NOT NULL) AS T;
   --1963
-There are no duplicates in firmbasecore. In firmbase, 'Amundi Private Equity Funds SA' was the only duplicate, but we dropped the one with the earlier founding date when we cleaned firmbase. Amundi has 2 branch offices in the same city (Bucharest) but different zip codes, which is why there are different counts when we use firmname/firmname,bocity vs firmname, bozip as our key.
+There are no duplicates in firmbasecore. In firmbase, 'Amundi Private Equity Funds SA' is the only duplicate, but we dropped the one with the earlier founding date when we cleaned firmbase. Amundi has 2 branch offices in the same city (Bucharest) but different zip codes, which is why there are different counts when we use firmname/firmname,bocity vs firmname, bozip as our key.
-We might drop "Amundi" from firmbasecore, as well as branchofficecore.
+NOTE: We have dropped "Amundi" from firmbasecore, as well as branchofficecore, in order to use firmname as a valid key for bocore.
 ==Cleaning fundbase==

@@ Line 1,127: / Line 1,127: @@
   --1963
-There are no duplicates in firmbasecore, aside from 'Amundi', which has 2 branch offices in the same city (Bucharest) but different zip codes, which is why there are different counts when we use firmname/firmname,bocity as a key vs firmname, bozip.
+There are no duplicates in firmbasecore. In firmbase,'Amundi Private Equity Funds SA' was the only duplicate, but we dropped the one with the earlier founding date when we cleaned firmbase. Amundi has 2 branch offices in the same city (Bucharest) but different zip codes, which is why there are different counts when we use firmname/firmname,bocity vs firmname, bozip as our key.
 ==Cleaning fundbase==

@@ Line 1,115: / Line 1,115: @@
   SELECT COUNT(*) FROM branchofficesbase WHERE undisclosedflag = 0;
   --9452
   DROP TABLE branchofficecore;
@@ Line 1,123: / Line 1,120: @@
   SELECT * FROM branchofficesbase WHERE undisclosedflag = 0;
   --9452
 ==Cleaning fundbase==