Dylan's Filter Method

Purpose of Method

If you have a large set of data, and need to answer a specific question with it, this filter method can work. It involves a substantial amount of hard-coding, which is a no-no in Excel but works. The method will be updated as Dylan learns the "right way" to filter data in this manner without hard-coding. As an example of the types of questions that can be answered through this method, starting with all VC data at the round level, you can answer the question: "How much SEL venture capital investment and how many deals did Houston receive between 1996 and 2016?"

Step 1 - PreWork

Take the raw data and process it either through TextPad or SQL until it can be easily imported to an Excel spreadsheet. Research the city that you are trying to analyze and generate a list of significant suburbs and smaller cities in its MSA.

Step 2 - Filtration

Use the "Filter" tool in Excel to filter multiple columns:

• the date column to only the dates you are analyzing
• the city column to only the city you are considering and other cities in its MSA
• the Round Stage 3 column to only "SEL" investment, which is Early, Late, Seed, and VC Partnership

Step 3 - Table Creation

Take the filtered sheet and copy all of the data. Open up a second worksheet and paste only the values. This can be done by right clicking and selecting "Paste Values" rather than using ctrl-V. Use the filter tool in Excel to ensure that this new sheet only contains records for the dates, cities, and stages that you are analyzing.

Step 4 - Pivot Baby Pivot

Select all data in this newly created datasheet. Then click Pivot Table from the Insert Tab of the Excel ribbon. Place Pivot table on a new sheet named "Summary." This Pivot table will help you answer your questions. Place "year" in the column quadrant of the pivot table, and place "Sum of Round Amount disclosed" in the value quadrant. You now have the sum of the SEL dollars invested for this city across the years that you specified. In order to do deals, simply change the "Sum of Round Amount Disclosed" in the the value quadrant to "Sum of Deal Flags," or Build a second pivot table.

Step 5 - Graph

Copy the data that you want to graph from the pivot table and build a hard-coded table by pasting values only in some adjacent cells. Then select that data and build the type of graph you would prefer.