Changes

Jump to navigation Jump to search
no edit summary
*This page [[Hubs (Academic Paper)]] is the main page for the Hubs project!
*The old work done by Rachael is on the [[Hubs]] page
*There is also [[Old Completed Work on Hubs]]
*For a high-level overview of the variables for the scorecard go to [[Hubs Scorecard (Academic Paper)]]. This summarizes:
**Current work in progress for building the Hubs scorecard: [[Hubs: Hubs Scorecard]]
==Abstract==
The Hubs Research Project is a full-length academic paper analyzing the effectiveness of "hubs", a component of the entrepreneurship ecosystem, in the advancement and growth of entrepreneurial success in a metropolitan area.
==Current Work== ===General Overview=== 
Currently there are '''3''' major tasks being performed (list to be updated):
#'''Creation of VC data table''': '''UPDATE: Complete''' (see completed work section below)
#'''Hazard Rate Model''': '''UPDATE: (7/11) Spoke to Xun Tang, econometrics professor in Rice's Economics Department, and now looking for appropriate proportional rate hazard models with time varying covariates.''' In order to perform our diff-diff model, we need to match MSAs. In order to do so, we will be using a hazard rate model to produce a probability that a MSA gets a Hub and compare MSAs that do and don't have hubs with similar probabilities.
==Work In Progress==
 
 
=Completed Work=
==Venture Capital Data General Overview==
The main goal of the data set is to aggregate company, fund, and round level data to be analyzed at a combined MSA and year level. The data set is compromised of two major parts: a granular company/fund/round and an aggregated CMSA-Year. The data includes all United States Venture Capital transactions (moneytree) from the twenty-five year period of 1990 through 2015.
 
The Hubs data set, from SDC Platinum, has been constructed in the server:
Data files are in 128.42.44.181/bulk/Hubs
All files are in 128.42.44.182/bulk/Projects/Hubs
psql Hubs2
 
Sql files:
:<code>E:\McNair\Projects\Hubs\Data Script v10.txt</code>
Note: We need to check that everything in '''Data Script v9 Ariel.txt''' has been incorporated into v10
 
Table Header Rows + 5 lines:
:<code>E:\McNair\Projects\Hubs\Data Table List v2.txt</code>
Note: This was generated by '''Data Script v10.txt'''
 
===Procedure - Granular Table===
#Start with separate raw datasets for Companies, Funds, and Rounds - '''Locate Raw Datasets and Determine Pedigree'''
#Add Data to Each Individual dataset (e.g. add MSA code)
#Clean and standardize names (e.g. company or fund name) for each dataset
#Join the Datasets (here we need to exclude undisclosed companies)
 
===Procedure - CMSA-Year Table===
#Create a consistent CMSA-Year table to be used later
#Using the tables from the granular table, parse out the right data
#Join the parsed out data with the CMSA-Year Table
#Join these Tables
 
==VC Specific Tables and Procedure==
===Raw data tables===
#'''Funds''': fund name, first investment date, last investment date, fund closing date, address, known investment, average investment, number of companies invested, MSA, MSA code.
#'''Rounds''': round date, company name, state, round number, stage 1, stage 2, stage 3
#'''Combined Rounds''': company name, round date, disclosed amount, investor
#'''Companies''': company name, first investment, last investment, MSA, MSA code, address, state, date founded, known funding, industry
#'''MSA List''': MSA, MSA code, CMSA, CMSA code
#'''Industry List''': changes 6 industry categories to 4— ICT, Life Sciences, Semiconductors, Other
 
 
===Granular Table (Fund-Round-Company)===
The final table here contains all venture capital transactions by disclosed funds and portfolio companies, together with their CMSAs.
To get the table, we processed the raw data sets in the following steps:
#Clean '''Company''' data
##Import raw data companies
##Add variable 'CMSA' from data set MSA list, update variable 'industry' by joining data set industry list
##Remove duplicates and remove undisclosed companies
#Clean '''Fund''' data
##Import raw data funds
##Add variable 'CMSA'
##Remove duplicates and remove undisclosed funds
##Match fund names with itself using [[The Matcher (Tool) |The Matcher]] to get the standard fund names
#Clean '''Round''' data
##Import raw data rounds and combined rounds
##Add variables 'number of investment', 'estimated investment' and 'year'
##Remove duplicates and remove undisclosed funds
#'''Combine''' '''Companies''' and '''Rounds'''
##Combine cleaned companies and rounds data table on company names
##Add variable 'round number' and 'stage'
##Remove duplicates
#'''Combine''' '''Funds''' and '''rounds-companies'''
##Match fund names in rounds data table with standard fund names using [[The Matcher (Tool) |The Matcher]] to standardize fund names in rounds data table
##Join standard fund names to rounds-companies table
##Join cleaned funds table to rounds-companies table on standard fund names
 
Note: This was done by Ariel and then edited by Todd.
 
===CMSA-Year Aggregated Table===
 
The original MSA to CMSA was done by Rachel and used here. '''LOCATE THE FILE!!!'''
 
The final table contains number of companies and amount of investment, categorized by distance and stages, of each CMSA.
 
We processed data as follows:
#Create the '''CMSA-Year''' Table
##Create single variable tables: Distinct CMSA, year, stage, found year of fund and found year of company.
##Create the cross production tables: CMSA-year, CMSA-year-fund year founded and CMSA-year-company year founded
#Draw data from cleaned companies, funds and rounds tables
##Create a table with 'CMSA', 'number of companies' and 'year Founded' from cleaned companies table and join it to CMSA -year founded
##Create a table with 'Company CMSA', 'round year', 'disclosed amount' from rounds-companies combined table, and add stage binary variables. Join it to CMSA-year-company year founded
##Create a table with 'CMSA', 'fund year', 'number of investors' from cleaned funds table and join it to CMSA-year-fund year founded
#Create '''near-far''' and stages table
##Add fund data to rounds-companies
##Create near-far and stages binary variable
##Count investment and deals by CMSA and year, categorized by near-far and stages
#Combine all tables by CMSA and round-year
 
==Supplementary Data Sets==
 
Supplementary data sets are cleaned and joined back to CMSAyear table on CMSA and year:
 
#Number of STEM graduate student, by university and year(2005 to 2014).
#University R&D spending, by university and year(2004 to 2014).
#Income per capital, by MSA and year(2000 to 2012)
#Wages and salaries, by MSA and year(2000 to 2012)
 
All of these files were created originally by Rachel. Some were cleaned in Excel. No new data was added (some extra cols, no extra rows).
 
The datasets can respectively be found at:
E:\McNair\Projects\Hubs\STEM grads for upload v2.xls
--Contains: university zipcode newmsacode msa msacode cmsa cmsacode year nostudents
--CMSA code inside sheet seems to be ours. Check with Ariel.
E:\McNair\Projects\Hubs\NSF spending for upload.xls
--Contains: Institution MSA CMSA code Year Spending
--We think the CMSA Code is ours. Check with Ariel.
E:\McNair\Projects\Hubs\Income per capita upload.xls
--Contains: Fips Area Year Income
--Lookup to CMSA was done using VLOOKUPs in Excel. See Matcher Helper vTR.xls, and other Matcher Helper ???.xls files
E:\McNair\Projects\Hubs\Wage for upload v2.xls
--Contains: Fips MSA Year Wage
--Lookup to CMSA was done using VLOOKUPs in Excel. See Matcher Helper vTR.xls, and other Matcher Helper ???.xls files
==Resources==
===Additional Resources===
76

edits

Navigation menu