Ranking US Cities by Venture Capital

From edegan.com
Revision as of 14:43, 17 March 2019 by Ed (talk | contribs)
Jump to navigation Jump to search


McNair Project
Ranking US Cities by Venture Capital
Project logo 02.png
Project Information
Project Title Ranking US Cities by Venture Capital
Owner Ed Egan, Anne Dayton, Diana Carranza
Start Date Summer 2017
Deadline
Primary Billing
Notes
Has project status Active
Subsumes: Top Cities for VC Backed Companies
Copyright © 2016 edegan.com. All Rights Reserved.


Project Description

This project was initially undertaken in the summer of 2017 and resulted in a full report on 2016.

The data was then updated for 2017 and 2018 (Q1 and Q2), which resulted in a ranking spreadsheet and document, but not a full report.

Last code rebuild

The constraint that a city had to have raised $100m in SEL VC over the last 34 years was too onerous for some of the very new ecosystems and was removed from the last build.

The build file is:

E:\project\vcdb3\Ranking.sql

This file replaces both RoundRanking.sql and RoundRankingState.sql, and fixes some minor issues with the latter having overwritten tables from the former.

The output files are:

  • Ranking2016WMove.txt
  • Ranking2017WMove.txt
  • Ranking2018Q12WMove.txt
  • Ranking2016WMoveState.txt
  • Ranking2017WMoveState.txt
  • Ranking2018Q12WMoveState.txt
  • USSelamnt.txt

They are produced in Z:/vcdb3

2017 and 2018 Q1/2 updates

The final files are in

E:\mcnair\Projects\VentureXpert Database

They include:

  • US and TX SEL Amount - Done.xlsx
  • Master Tables Sheet.docx
  • Top 15 States (Based on All Time SELAmnt) SEL vs Year - Done.xlsx
  • TX Cities Graphs and Data - Done.xlsx
  • Top 100 Cities 2017 - Done.xlsx
  • All States 2018 (Q1 and Q2) - Done.xlsx
  • All States 2017 - Done.xlsx
  • Top 100 Cities 2018 (Q1 And 2) - Done.xlsx

The database is vcdb3 and the SQL scripts are in

E:\mcnair\Projects\VentureXpert Database\vcdb3\LoadingScripts

The database build is documented in VentureXpert Data#Ranking Tables and Graphs

2016 Report

Project Location

E:\McNair\Projects\Ecosystem\Ranking

The report is available from that directory as well https://www.bakerinstitute.org/research/top-100-us-startup-cities-2016/.

Main SQL file location

Ranking.sql

This file works on the database cities.

However, the cities database takes vc data from vcdb2. See VC Database Rebuild for information on the build.

The output from vcdb2 is roundleveloutput2, which is exported as roundleveloutput2.txt

roundleveloutput2.txt is imported into cities as roundleveloutput2, and then left joined with populationtablecorrected.

Last Population SQL file location

Z:\Hubs\2017\sql scripts
Z:\Hubs\2017\clean data\Population

One issue is that cities without populations in populationtablecorrected but in roundleveloutput2 get dropped later. For example, roundleveloutput2 contains Brooklyn, NY but populationtablecorrected doesn't (its New York count is therefore suspect too).

populationtablecorrected is loaded from files 'population1.txt' and 'population2.txt'. Dups are removed with a max.

Their construction notes are on Hubs#Population_Data