Changes

{{Project|Has project output=Data|Has sponsor=McNair ProjectsCenter

|Has owner=Jake SilbermanHira Farooqi,

|Has project status=TabledActive|Does subsume=Hubs Analysis 2017,

'''Important Notice: The Hubs Research Project is a full-length academic paper analyzing last update to the effectiveness of "hubs", a component of the entrepreneurship ecosystem, in the advancement data was done manually by Ed and growth of entrepreneurial success is in a metropolitan areaE:\projects\MeasuringHGHTEcosystems\HubsData-RevisedSimplified. xlsx'''

===Primary Data Set===The Hubs data setResearch Project is a full-length academic paper analyzing the effectiveness of "hubs", from SDC Platinuma component of the entrepreneurship ecosystem, has been constructed in the server: Data files are advancement and growth of entrepreneurial success in 128.42.44a metropolitan area.181/bulk/Hubs All files are It focuses on cities in 128the United States as the primary unit of analysis.42.44.182/bulk/Projects/Ecosystem/Hubs psql Hubs

The Information on initial data set has now been uploaded work done prior to the database server, named Summer 2017 can be found at [[HubsSummer 2016]].There are 4 tables: *Rounds: Rounddate, coname, state, roundno, stage1, etc.*CombinedRounds: Coname, rounddate, discamount, fundname*Companies: LastInv, FirstInv, coname, MSA, MSACode, Address, state, datefounded, totalknownfunding, industry(major)*Funds: fundname, closingdate, lastinv, firstinv, msa, msacode, avinv, nocoinv, totalknowninv, address

Used variables'''Note on joining:''' The city-state-year ID from VC data is used as the master ID for joining datasets. Each table (e.g. income, nih, nsf, sbir, compustat) is first joined with the VC data on city-state-year ID and then the resulting tables are all joined together in the final table.

===Data by zip code===*Population data, 2000-2016 - US Census Bureau (E:\McNair\Hubs\summer 2017)https://www2.census.gov/programs-surveys/popest/datasets/*Income data, 1998-2014 - The Internal Revenue Service (E:\McNair\Hubs\summer 2017)https://www.irs.gov/uac/about-irs*DCI index, to assess the economic well-being of communitieshttp://eig.org/dci/interactive-maps/u-s-zip-codes*R&D Expenses, 1980-2016 - Wharton Research Data Services (E:\McNair\Hubs\summer 2017)*Zipcode look-up table obtained from https://www.unitedstateszipcodes.org/zip-code-database/. It's available in (E:\McNair\Hubs\summer 2017). == Data by MSA == We have principle cities of MSAs from the census:https://www.census.gov/geographies/reference-files/time-series/demo/metro-micro/delineation-files.html We might be able to go City -> FIPS place code -> MSA? Cities and their FIPS codes (which don't perfectly correspond) are available from https://www.census.gov/geo/reference/codes/place.html The Census claims to provide city to MSA here: https://www.census.gov/geo/maps-data/data/ua_rel_download.htmlHowever, there is only CBSA! This might do it: https://www2.census.gov/geo/pdfs/maps-data/data/rel/explanation_ua_cbsa_rel_10.pdfWe can maybe track city to principal city to MSA ==COMPUSTAT Data==The data set includes information on publicly traded firms in the US. It was obtained from the Wharton Research Data Services (https://wrds-web.wharton.upenn.edu/wrds/index.cfm?).   Raw Data is in: FundsE:\McNair\Projects\Hubs\Summer 2017 Z:\Hubs\2017 The source file is RandDExpenditures.txt. It contains:*Date from 1980-2017 (July). *427799 records*Fields include: fundname**R&D Expenditure**Address (inc. city, msacodezip, state)**Revenue of firms MSALookupTableDatabase is '''cities''' SQL script is: COMPUSTAT.sql Output file is COMPUSTATSummary.txt. It contains: MSACode*Variables: City, year, No.public firms, sum R&D, MSASuper sum Sales, sum total assets*1979-2016*4440 cities It is located in Z:\Hubs\2017\Output_Files ==NSF Data==Data is in: E:\McNair\Projects\Hubs\Summer 2017 Z:\Hubs\2017 Database is '''cities''' SQL script is: nsf_2017.sql The source files are: nsf2017.txt, copied from table '''nsf''', and nsf_institution copied from table '''nsf_grants_institution''' from the biotech db. They contain:*Award ID*Award Institution*Award Effective date*Institution city*Award Value*Organization state codeFrom 1900 -> 2017 Output file is nsfSummary.txt. It contains: FundInfo*Variables: fundnameCity, msacodeState code year, nsf_nogrants, nsf_valuegrant *1900-2017 ===Joined NSF table===The joined nsf table with the VC table is found in db '''cities'''. The table is named '''merged_nsf'''.All the values of nogrants and valuegrant with missing values for years 1990-2017 are set equal to 0.The sql script is in Z:\HUbs\2017\sql scripts ==NIH Data==Data is in: Z:\Hubs E:\McNair\Projects\Hubs\Summer 2017 Database is '''cities'''SQL script is: nih2017.sqlThe source files are: *nih_1986_2001.csv*nih_2002_2012.txt*nih_2013_2015located in E:\McNair\Projects\Federal Grant Data\NIH   The script that cleans NIH data and generates the summary table is titled '''nihSummary'''. It is located here:  Z:\Hubs\2017\sql scripts This table includes*year*city*state *country*nogrants (completenumber of grants)*valuegrant*city_state  *Date from 1986-2015 ===Joined NIH table===The joined NIH table with the VC table is found in db '''cities'''. The table is named '''merged_nih'''.All the values of nih_valuegrant and nih_nogrants with missing values for years 1986-2015 are set equal to 0.The sql script is in Z:\HUbs\2017\sql scripts ==Clinical Trials Data==Data is in: Z:\Hubs E:\McNair\Projects\Hubs\Summer 2017 Database is '''cities'''SQL script is: ctrials.sqlThe source file is:  *medclinical.txt located in Z:\Hubs\2017 *Date from 1999-2017 ===Joined clinical trials table=== The file which contains the number of trials in each city and year is located in: Z:\Hubs\2017 The file is in: Z:\Hubs\2017\clean dataThe name of the file is: ctrialsSummary.txt It contains:*city*year*city_state_year*noctrials - number of trials The ctrials is joined with VC table. The joined SQL script is: '''new_ctrials.sql''' and it is located in Z:\Hubs\2017\sql scripts The name of the joined table is '''new_merged_ctrials'''. It contains:*city*state*city_state_id*city_state_year*year*noctrials*seedamtm*earlyamtm*lateramtm*selamtm*numseeds*numearly*numlater*numsel

Rounds: coname, rounddate, stagecode, roundno CombinedRounds: coname, rounddate, discamount, fundname All the values of noctrials with missing values for years 1999-> RoundInfoSuper: coname, rounddate, '''nofunds''', discamount -> RoundInfo: Coname, roundyear, fundname, estamount (complete)2017 are set equal to 0.

Then take: RoundInfo: Coname, roundyear, fundname, estamount CompanyInfo: Coname, MSASuper, InduCode, state==Population Data== FundInfoData is in: fundname, msacode, state -> SuperRoundInfoZ: Coname, CoMSASuper, CoInduCode, CoState, FundName, FundMSASuper, FundState, RoundYear, RoundEstAmount\Hubs -> MSAPortCosE: Count(CoName) As NoPortCosFunded, CoMSASuper, RoundYear ...\McNair\Projects\Hubs\Summer 2017

Database is '''Notes on Creation of Primary Data Setcities'''

Raw tablesSQL script is: '''population.sql'''* companies (last investment, first investment, company name, MSA, MSA code, address, state, date founded, known funding, industry) * funds (fund closing date, last investment, first investment, fund name, address, MSA, MSA code, Average investment, number companies invested (NoCos), known investment) * rounds (round date, company name, state, round number, stage 1, stage 2, stage 3) * combined rounds (company name, round date, disclosed amount, investor) The source files are: * msalist (changes MSAs to CMSAs— combined MSAs)pop2000_2009.xlsx*industry list (changes 6 industry categories to 4— ICT, Life Sciences, Semiconductors, Other) pop2010_2016.xlsx

ProcessThey contain:*cleaned tables to eliminate duplications, undisclosed variablesState*changed all original characters to include CMSA and Industry Codes (companyinfo3, fundinfocleanfinal, roundinfoclean) City name *matched funds to avoid any issues with names (i.e. Fund ABC L.P./Fund ABC LP/Fund ABC) Year *matched roundinfoclean investors to fundinfocleanfinal investors (roundinfo.txt >> cleanfundfinal.txt)*join by round and company conames*bridge years (1990-2016), stage, and cmsa* populate data with count of companies (Deal flow) and estimated amount ($)** data set in 181 hubs folder under summarycmsa.txt (38394)Population Estimates

Key decisions:*Threw out undisclosed co throughDate from 2000-out as no address*Count is done by joining round and company*Anything fund related must be disclosed fund*Near and far, and total invested, and fund counts, etc., are all done using disclosed funds that match only2016

'''Glossary of Tables''' cleanco — used to remove duplicates from companies cleanedcompanies — clean set of companies with no duplicates cmsafunds- cmsas— list of all CMSAs in final data set (for merging) cmsastats- statistics not including empty years (pre-merge) cmsastats2 - statistics separated by year-MSA cmsastats3— statistics separated by year-MSA-stage cmsastats4 cmsayears— empty merged table between year and cmsa cmsayearstage — empty merged table between cmsa/years and stage combinedrounds— raw sdc data for combined rounds combinedroundswamt— used to join rounds and combined rounds for roundinfo2 companies- raw SDC company data companyinfo — cleaned companies joined with state and CMSA information companyinfo2— companyinfo1 with original industry categories companyinfo3— companyinfo2 with updated industry categories and codes companyinfo4-- clean version of companyinfo3 companyround- combined company information with round information companyround2- combined company information with round information, cleaned up from companyround2 companyround3- combined company information with round information, cleaned up from companyround3 '''finaldataset'''- final statistics by CMSA-year, see section Final Primary Data Set for more information fundinfo— funds joined with CMSA info fundinfo2 - clean version of fundinfo1 fundinfoclean - used in process to clean fundinfo2 fundinfoclean2- used is in process to clean fundinfo2: fundinfocleanfinal- used in process to Z:\Hubs\2017\clean fundinfo2 fundinfocleannodups- final clean set of fundinfo funds - raw SDC fund data Houston - analysis for Houston ecosystem team Houston2- analysis for Houston ecosystem teamThe file names are houston31_population.txt - analysis for Houston ecosystem team industry — new industry codes (4)— used for all future contains data sets industrylist— lookup table for new industry codes (went on population estimates from 6 to 4) joined1- used for matching process joined2- used for matching process matchfund2- used for matching process matchfunds- used for matching process matchroundfund - used for matching process matchroundfund2- used for matching process msalist — lookup table for MSA to CMSA (used for all future data sets) nearfar12000-- beginning set before adding nearfar/stage variables 2009 nearfar2 2_population.txt -- added binomial variables for near/far and for each of the stages, used to build final dataset roundfund— not used— joined round to fund; drop/ignore roundinfo— round info cleaned up to include number of investors in a syndicate and estimated investment per member of syndicate roundinfo2— roundinfo1 including name of investors/funds roundinfo3— clean version of roundinfo2 roundinfoclean — final clean version of roundinfo3 (final roundinfo table) rounds — raw SDC round contains data stages — table for merging stage-year-CMSA superinfo — ignore/drop temp - used for matching process years — table for merging stage-yearon population estimates from 2010-CMSA2016

The Hubs candidate data set Database is '''cities'''SQL script is a list of potential hubs found in MSAs throughout the country: '''new_population. Researchers are currently pulling qualitative and quantitative information from the candidatesql'''s websites, located in an attempt to categorize what can be identified as a hub. This is a difficult data set to pull, as there is little to no quantitative information available for this category of institution, and is dependent on accessibility of information to the public on the internet. Z:\Hubs\2017\sql scripts

Characteristics/Variables*Year Founded*Square footage*LinkedIN self-identifiers (what the organization classifies itself The population table is joined on its LinkedIN profile) *Activeness on Twitter (binomial)*Member Directory available online (binomial)*Number of conference rooms*Price ($/month) for Flex desk *Offers Reserved desk (binomial)*Offers office space for rent (binomial) *Offers community membership-- not for coworking but for community events, etcVC table. The table is called '''new_merged_population'''. (binomial)*Number of events offered per month (estimate)*Offers code academy*Mission Statement/Vision (for qualitative or key-word analysis)

These characteristics/variables will be used They contain:*City*State*city_state_id to determine whether a candidate is or is not likely uniquely identify each city*city_state_year to be a Hub. uniquely identify each city in each year*Population estimates*Year*Code from the state code and Fips code*State full name

'''Where to find''': The Hubs Raw data set can be found in the Ecosystem>>Hubs>>dataset folderwas obtained from Census data, American Communities Survey. It is not currently in the database due to a UTF8 issue

===Supplementary Raw Data Sets===is in: '''Patent data''' E: to be pulled from USPTO or SDC Platinum\McNair\Projects\Hubs\Summer 2017\MSA Income_raw. zip

'''Per Capita Income''' and '''Employment Data''' (US Census Bureau): *"Per Capita Personal Income by MSA 2000-2012.xlsx" in datasets folder (Ecosystem>>Hubs>>Datasets>>Data Date from Yael)*"Wages and Salaries by MSA 20002005-2012.xlsx" in datasets folder (Ecosystem>>Hubs>>datasets>>Data from Yael)**not uploaded to server or matched yet to CMSA code2015

The master list with MSAs and principal cities is titled '''Firm Birthslist2.xls''' (BDS)*in server 181, under table name "BDS"*includes birth, death, net(birth-death) and rate(death rate) for years 1990-2013 for every msa*includes code for CMSA but . It is not aggregated by CMSAlocated at:** i.e. BDS statistics are still separate for all the smaller MSAs in New York's CMSA (code=1) Z:\Hubs\2017

===Resources===This master list includes:* Yael Hochberg and Fehder (2015), located in dropboxMSA code*MSA name* Use this paper as a guideline on how to conduct the analysisPrincipal City*US Census Bureau data on employment by MSA: http://factfinder.census.gov/faces/tableservices/jsf/pages/productview.xhtml?pid=ACS_14_5YR_B23027&prodType=tableState*USPTO utility patents by MSA: http://www.uspto.gov/web/offices/ac/ido/oeip/taf/cls_cbsa/allcbsa_gd.htmPlace code (city code)*MSA level trends: http://www.metrotrends.org/data.cfState Code

Example datasetThey contain: *MSA code*MSA*Year SeedVCInv SeedEarlyVCInv LaterVCInv NoDeals FundsInvested DistinctInvestors .... ---------------------------------------------------------------------------------------------------------------------------- 1234 2001 1000000 20000000 30000000 4 7 7*Total Household Income

Note The SQL file that merges income data from ACS (by MSA - Year) with the unit of observation is MSA-YearCity file is titled '''income.sql'''. It is located here: Z:\Hubs\2017\sql scripts The final income table is in db '''cities''' titled '''merged_income'''.

Variables to be computed at the MSA levelIt includes:*HubActive (binary)*NoHubsActive (Count)*HubSqFt*Other Hub Vars (build list!!!)*'''SeedVCInv''' (Seed/Start-up)*'''EarlyVCInv''' (Early Stage)*'''LaterStageVC''' (Later)*'''OtherStageVC''' (Buyout/Acq, Other)*'''NoDeals''' (done by local VCs?)**'''NoDealsNear'''**'''NoDealsFar'''*NoPortCosFunded*'''FundsInv''' (in an MSA)**'''FundsInvFromNear''' (within MSA?)**'''FundsInvFromFar''' (outside MSA?)*DistinctInvestors (?)**DistinctInvestorsNear (within MSA?)**DistinctInvestorsFar (outside MSA?)*PatentCount*NoSTEMGrads*FirmBirths (BDS data)City*UniRandDSpendState*PerCapitaIncomeYear*EmploymentTotal Household Income

There may be a second dataset that has Hub-Industry-Year (where industry Data is semiconductor/nonin: Z:\Hubs\clean dataThe file names are: INC_05.txt -semiconductor?)INC_15.txt

Table nameThey contain: finaldataset cmsa*City year*State totalamountinv--total amount invested nearamountinv--amount invested from local funds faramountinv-- amount invested from funds outside CMSA *city_state_id to uniquely identify each city earlyinv--amount invested in early stage companies *Income laterinv--amount invested in later stage companies *Year startupseedinv--amount invested in seed or startup stage companies otherstageinv--amount invested in Acquisition/Buy-outs/Other stage companies investingfund--distinct funds that are investing in that CMSA-year investingfundnear--distinct funds *Code from that CMSA that invested in that CMSA-year investingfundfar--distinct funds from outside that CMSA that invested in that CMSA-year deals--number of deals neardeals--number of deals inside a CMSA fardeals--number of deals from outside a CMSA --some of these deals might count in both categories, because of syndicate members being both inside the state code and outside the CMSA earlystagedeals--deals with earlystage companies laterstagedeals--deals with later stage companies startupseeddeals--deals with startup/seed companies otherstagedeals--deals with companies in other stages newportcosfunded--number of portfolio companies to receive their first investment in that yearFips code

===Employment Data by zip code===*Population dataData on employment was obtained from American Communities Survey, 2000-2016 - US Census Bureau (. Raw Data is in: E:\McNair\Projects\Hubs\summer Summer 2017\Employment Data by MSACleaned files are in Z:\Hubs\2017\clean data They contain:*MSA code*MSA*Year *Employment rate of individuals 16 years or older*Unemployment rate of individuals 16 years or older Date from 2005-2015 The SQL file that merges employment data from ACS (by MSA - Year)with the MSA-City file is titled '''Employment.sql'''. The file is located in:https Z://www2\Hubs\2017 The final table is in db '''cities''' titled '''merged_employment'''.census It includes:*MSA*City*Year*Employment rate*Unemployment rate ===Joined employment table=== Data is in: Z:\Hubs\clean data The file names are: EMP_05.gov/programstxt -surveys/popest/datasets/EMP_15.txt  Database is '''cities'''SQL script is: '''new_employment.sql''' and it is located in Z:\Hubs\2017\sql scripts The final table which is joined on VC is in db cities titled '''new_merged_employment'''. They contain:*City*State*Code from the state code and Fips code*city_state_id to uniquely identify each city*Income datacity_state_year to uniquely identify each city in each year*Employment rates of individuals of 16 years or older*Unemployment rates of individuals of 16 years or older*Year ==Schooling Data== Data on schooling was obtained from American Communities Survey, 1998-2014 - The Internal Revenue Service (US Census Bureau. Raw Data is in: E:\McNair\Projects\Hubs\summer Summer 2017\School Enrollment Data by MSACleaned files are in Z:\Hubs\2017\clean data They contain:*MSA code*MSA*Year *Total number of population 3 years and over enrolled in school*Percent of population 3 years and over enrolled in public school*Percent of population 3 years and over enrolled in private school  Date from 2005-2015 The SQL file that merges schooling data from ACS (by MSA - Year)with the MSA-City file is titled '''schooling.sql'''. httpsThe file is located in://www Z:\Hubs\2017 The final table is in db '''cities''' titled '''merged_schooling'''.irs It includes:*MSA*City*Year*Total*Percent_public_schooling*Percent_private_schooling ===Joined schooling table=== Data is in: Z:\Hubs\clean dataThe file names are: SCH_05.gov/uac/abouttxt -irsSCH_15.txt  Database is '''cities'''SQL script which joins this table with VC table is: '''new_merged_schooling.sql'''The final table is in db '''cities''' titled '''new_merged_schooling'''. It contains:*City*State*DCI index, city_state_id to uniquely identify each city*city_state_year to assess uniquely identify each city in each year*Total number of school enrollment*Percentage enrolled in public schools*Percentage enrolled in private schools*Year*Code from the economic wellstate code and Fips code ==VC Data==   Raw Data is in: Z:\VentureCapitalData\SDCVCData\vcdb2 The file name is roundleveloutput2.txt It contains:*city*state*year*seedamtm -being seed, amount in millions*earlyamtm - early, amount in millions*lateramtm - late, amount in millions*selamtm - seed early late, amount in millions*numseeds - number of communitiesseeds*numearly *numlater*numsel*numdeals*numalive  Date from 1948-2017  httpThe table is in db '''cities''' titled '''new_vc'''. It includes://eig*city*state*city_state_id*city_state_year*seedamtm*earlyamtm*lateramtm*selamtm*numseeds*numearly*numlater*numsel*numdeals*numalive*year ==Final Joined Data set ==  The final data set is in file '''final.org/dci/interactivetxt''' and is located here: Z:\Hubs\2017 It includes:*city*state*city_state_year - (ID that data is merged on)*year*seedamtm - Seed Amount*earlyamtm -maps/uEarly Investment Amount*lateramtm -sLate Investment Amount*selamtm -zipSeed early or late amount*numseeds -codesNumber of seed investments *numearly - Number of early investments*numlater - Number of late investments*numsel *numdeals - Number of deals (first contracts)*numalive - Number of start ups alive*income - Income per capita in each city-year*sbir_nogrants - Number of SBIR grants*sbir_valuegrant - Value of SBIR grants*emp - Employment stats of each city-year*unemp - Rate of unemployment*popestimate - Population estimate of each city-year*private - Enrollment in private schools*public - Enrollment in public schools*total - *numfirms - Number of publicly traded firms*randd - R&D expenditure of publicly traded firms*revenue - Revenue of PTF*totalassets *nsf_nogrants - Number of NSF grants*valuegrant - Value of NSF grants*nih_nogrants - Number of NIH grants*nih_valuegrant - Value of NIH grants*noctrials - NUmber of clinical trials == Defining Hubs == '''Summer 2016''' - Last year a master list of 125 "potential" hubs was used. A scorecard was developed which filtered these 125 candidate hubs to determine which of these should be included in the study sample. This method resulted in a sample size of ~ 30. The master list and the final hubs list is titled '''Hubs Data v2_'16'''. It is located here: Z:\Hubs\2017\hubs_data '''Summer 2017''' - In order to obtain a more statistically significant sample of hubs, we developed 5 criteria which produce a more relaxed definition of hubs than last year. These include *Availability of co-working space*Coding classes or tech events*Some focus on the tech sector (this is important as our dependent variable is VC funding)*Presence of an accelerator*Availability of mentorship for members. We will review the 125 candidate hubs and select those which satisfy a subset or all of these characteristics.   [[category:Internal]]