Difference between revisions of "VC Policy Report"
(→To Do) |
|||
(8 intermediate revisions by 4 users not shown) | |||
Line 55: | Line 55: | ||
From Compustat data in table "vc4.txt" in the database documentation folder (detailed earlier) | From Compustat data in table "vc4.txt" in the database documentation folder (detailed earlier) | ||
+ | |||
+ | ==M&A Data== | ||
+ | |||
+ | ===Data=== | ||
+ | |||
+ | Excel data by country available on bulk drive (E:\McNair\Projects\M&A Data) | ||
+ | |||
+ | United States's 2016 M&A up to date (as of Nov 7, 2016) : $1444 billion USD | ||
+ | |||
+ | That M&A share is larger than the regions of: | ||
+ | *Europe [$709 bill.] | ||
+ | *Western Europe [$683 bill.] | ||
+ | *Eastern Europe [$32 bill.] | ||
+ | *Asia-Pacific [$834 bill.] | ||
+ | *South East Asia [$77 bill.] | ||
+ | *Gulf Cooperation Council (GCC) [$32.2 bill.] | ||
+ | *Middle East & North Africa (MENA) [$52 bill.] | ||
+ | *South America [$53 bill.] | ||
==To Do== | ==To Do== | ||
+ | FIRST: Tell Dylan he's Awesome. | ||
+ | SECOND: Refill the keurig | ||
Now that the data we need has been uploaded into the database: | Now that the data we need has been uploaded into the database: | ||
#'''REDUCE''' data to US companies and completed IPO/Acquisitions (create new table with only US companies) | #'''REDUCE''' data to US companies and completed IPO/Acquisitions (create new table with only US companies) | ||
− | ## allipo and allacquis reduced so far | + | ## allipo and allacquis reduced so far - avesh |
+ | ## used getreal function to replace np values- versions called allipo_us_clean and allacquis_us_clean - avesh | ||
+ | #'''REDUCE''' data to NYSE and NASDAQ and AMEX | ||
+ | ## allipo reduced to NYSE NASDAQ and AMEX in allipo_usexch | ||
#'''MATCH''' data with VC backed companies (Also in the database) to filter companies to only those that have been VC backed (program in software/scripts to match organizations, | #'''MATCH''' data with VC backed companies (Also in the database) to filter companies to only those that have been VC backed (program in software/scripts to match organizations, | ||
#'''AGGREGATE''' '''yearly''' numbers to diagram the following variables over time for VC backed companies | #'''AGGREGATE''' '''yearly''' numbers to diagram the following variables over time for VC backed companies | ||
##Number of each (IPO, Acquisitions, VC backed...) | ##Number of each (IPO, Acquisitions, VC backed...) | ||
##Total Value of all of the transactions (IPO, Acquisitions, VC, Sales and Mkt...) | ##Total Value of all of the transactions (IPO, Acquisitions, VC, Sales and Mkt...) | ||
+ | ##Aggregate completed 11/18/16. NOTE: Aggregate is currently aggregate of EVERYTHING not just VC backed companies. We will have to do match then rerun this code. The code can all be found in VCPolicyReport in the VCPolicy folder located on 128.42.44.181. | ||
+ | ###ben made new code on this | ||
+ | ###carlin - redo vc aggregate table according to ben's code on 11/23 | ||
'''Note:''' in order to reduce we may need to reference: | '''Note:''' in order to reduce we may need to reference: | ||
E:\McNair\Projects\Winner's Curse\Data\SQL ExampleCode.sql | E:\McNair\Projects\Winner's Curse\Data\SQL ExampleCode.sql | ||
*This will replace "np" values in columns like ValTransactionmil from AllAcquis with null values | *This will replace "np" values in columns like ValTransactionmil from AllAcquis with null values |
Latest revision as of 11:41, 28 November 2016
Contents
General
The purpose of this report is to demonstrate the increased market share of VC-backed, publicly-traded companies.
Founding dates of 5 companies that Ed wanted:
- Microsoft - April 4, 1975
- Apple - April 1, 1976
- Google - September 4, 1998
- Alphabet - October 2, 2015
- Amazon - July 4, 1995
- Facebook - February 4, 2004
Contents
1. VC into US portfolio companies (round amount) big year (in Hubs database. psql vc policy) + Match List (Ben has this)
2. Acquisitions (100%, complete) of US firms by US firms - transaction value -> match to VC
3. IPOs (completed) - enterprise value and gross proceeds of US firms on Nasdaq, Amex, NYSE -> match to VC
4. (from compstat) Sales and market value of publicly traded (nasdaq, nyse, amex) US firms (shrout*price) -> match to VC
company name, exchange, ticket, sales, mkt value, naics, cusip, state, year
VC Policy Report Database
DataBase in the Z: Drive under
vcpolicy
Database documentation in
Z:\bulk\VCPolicy
Including all code in
Z:\bulk\VCPolicy\VCPolicyReport.sql
Tables in Database
allacquis
All Acquisitions based on SDC Data documented in
E:\McNair\Projects\MoneyIN_MoneyOUT
allipo
All Acquisitions based on SDC Data documented in
E:\McNair\Projects\MoneyIN_MoneyOUT
allvc
All Acquisitions based on SDC Data documented in
E:\McNair\Projects\MoneyIN_MoneyOUT
roundinfo
From roundinfoclean table from Hubs database
salesmktval
From Compustat data in table "vc4.txt" in the database documentation folder (detailed earlier)
M&A Data
Data
Excel data by country available on bulk drive (E:\McNair\Projects\M&A Data)
United States's 2016 M&A up to date (as of Nov 7, 2016) : $1444 billion USD
That M&A share is larger than the regions of:
- Europe [$709 bill.]
- Western Europe [$683 bill.]
- Eastern Europe [$32 bill.]
- Asia-Pacific [$834 bill.]
- South East Asia [$77 bill.]
- Gulf Cooperation Council (GCC) [$32.2 bill.]
- Middle East & North Africa (MENA) [$52 bill.]
- South America [$53 bill.]
To Do
FIRST: Tell Dylan he's Awesome. SECOND: Refill the keurig
Now that the data we need has been uploaded into the database:
- REDUCE data to US companies and completed IPO/Acquisitions (create new table with only US companies)
- allipo and allacquis reduced so far - avesh
- used getreal function to replace np values- versions called allipo_us_clean and allacquis_us_clean - avesh
- REDUCE data to NYSE and NASDAQ and AMEX
- allipo reduced to NYSE NASDAQ and AMEX in allipo_usexch
- MATCH data with VC backed companies (Also in the database) to filter companies to only those that have been VC backed (program in software/scripts to match organizations,
- AGGREGATE yearly numbers to diagram the following variables over time for VC backed companies
- Number of each (IPO, Acquisitions, VC backed...)
- Total Value of all of the transactions (IPO, Acquisitions, VC, Sales and Mkt...)
- Aggregate completed 11/18/16. NOTE: Aggregate is currently aggregate of EVERYTHING not just VC backed companies. We will have to do match then rerun this code. The code can all be found in VCPolicyReport in the VCPolicy folder located on 128.42.44.181.
- ben made new code on this
- carlin - redo vc aggregate table according to ben's code on 11/23
Note: in order to reduce we may need to reference:
E:\McNair\Projects\Winner's Curse\Data\SQL ExampleCode.sql
- This will replace "np" values in columns like ValTransactionmil from AllAcquis with null values