Calculating the number of new investments of a VC in a specific industry in 5 years prior to the focal investment (observation)

[Nicky Joosse]

I am using Stata 15.0

I hope somebody can help me, given that I have been struggling for a lot of time now.

I need to calculate how often a VC firm X (variable name: FirmName e.g. 10X Venture Partners LLC) has invested in the industry Computer_Related (1== Computer_Related Industry). Only new Investments (NEW ==1) by that VC firm X should be taken into account and only the investments that were made 5 years prior to the focal investment of VC firm X. The focal transaction should be excluded from the counting.

I have a database including all transactions from VCs between 1995 and 2013 which I grouped based on FirmName. Within each group, I have tried to make a loop.

pid = unique identifier of that transaction by that VC

gen test_Computer_Related = 0

forvalues i = 1/`r(max)' {
count (pid != `i' & Computer_Related ==1 & NEW ==1 & TransactionDate `i' - InvestmentDate <= 1825), by(FirmName)
replace test_Computer_Related = r(N) in `i'
}

Stata runs the loop, but the outcome is for each observation '0' while this should obviously not be the case.
E.g. for 10X Venture Partners LLC (pid = 3) --> there are 2 new investments in the Computer_Related industry that have been made <5 years prior to that investment

My question is: how should the loop look like to get for each focal investment of investment firm X the number of new investments that VC firm X has made before the focal investment of VC firm X.

I am aware of the rangestat plugin and therefore I also created a variable InvestmentDate_minus_5years. I, however, have not been able to apply that.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str60 CompanyName float Computer_Related str59 FirmName float pid int InvestmentDate float(InvestmentDate_minus_5years TransactionDate NEW)
"1Mind Inc"                          1 "10X Venture Partners LLC"  1 19508 17683 19508 1
"Mosaic Storage Systems Inc"         1 "10X Venture Partners LLC"  2 19400 17575 19400 1
"MYVBO L L C"                        0 "10X Venture Partners LLC"  3 19260 17435 19260 1
"1Mind Inc"                          1 "10X Venture Partners LLC"  4 19575 17750 19575 0
"Dodge Brothers, The"                1 "10X Venture Partners LLC"  5 19508 17683 19508 1
"H & W Distributors Inc"             0 "180 Capital Fund LLC"      1 16215 14390 16215 1
"D-Wave Systems Inc"                 1 "180 Degree Capital Corp"   1 19156 17331 19156 0
"Geo Semiconductor Inc"              0 "180 Degree Capital Corp"   2 18918 17093 18918 0
"Chlorogen, Inc."                    0 "180 Degree Capital Corp"   3 16798 14973 16798 0
"Laser Light Engines Inc"            0 "180 Degree Capital Corp"   4 19478 17653 19478 0
"Kovio Inc"                          0 "180 Degree Capital Corp"   5 18787 16962 18787 0
"InnovaLight, Inc."                  0 "180 Degree Capital Corp"   6 18179 16354 18179 0
"Kereos Inc"                         0 "180 Degree Capital Corp"   7 16576 14751 16576 1
"Laser Light Engines Inc"            0 "180 Degree Capital Corp"   8 19712 17887 19712 0
"Enumeral Biomedical Corp"           0 "180 Degree Capital Corp"   9 19183 17358 19183 0
"Planprescriber Inc"                 1 "180 Degree Capital Corp"  10 15697 13872 15697 1
"Nanosys, Inc."                      0 "180 Degree Capital Corp"  11 15858 14033 15858 1
"Enumeral Biomedical Corp"           0 "180 Degree Capital Corp"  12 19492 17667 19492 0
"Enumeral Biomedical Corp"           0 "180 Degree Capital Corp"  13 18744 16919 18744 1
"D-Wave Systems Inc"                 1 "180 Degree Capital Corp"  14 19270 17445 19270 0
"Hzo Inc"                            0 "180 Degree Capital Corp"  15 18851 17026 18851 1
"Nanomix Inc"                        0 "180 Degree Capital Corp"  16 16425 14600 16425 1
"ABS Materials Inc"                  0 "180 Degree Capital Corp"  17 18805 16980 18805 1
"Xradia, Inc."                       0 "180 Degree Capital Corp"  18 17232 15407 17232 1
"Genomica Corporation"               1 "180 Degree Capital Corp"  19 14670 12845 14670 0
"Cambrios Technologies Corp"         0 "180 Degree Capital Corp"  20 18204 16379 18204 1
"NanoOpto Corporation"               0 "180 Degree Capital Corp"  21 15953 14128 15953 0
"Gel Sciences, Inc."                 0 "180 Degree Capital Corp"  22 13696 11871 13696 1
"NanoOpto Corporation"               0 "180 Degree Capital Corp"  23 15426 13601 15426 1
"D-Wave Systems Inc"                 1 "180 Degree Capital Corp"  24 16937 15112 16937 1
"Bridgelux Inc"                      0 "180 Degree Capital Corp"  25 16582 14757 16582 1
"Kereos Inc"                         0 "180 Degree Capital Corp"  26 16699 14874 16699 0
"Micracor"                           0 "180 Degree Capital Corp"  27 13180 11355 13180 1
"AgBiome LLC"                        0 "180 Degree Capital Corp"  28 19457 17632 19457 1
"Senova Systems Inc"                 0 "180 Degree Capital Corp"  29 19004 17179 19004 1
"Laser Light Engines Inc"            0 "180 Degree Capital Corp"  30 19562 17737 19562 0
"Biosupplynet Inc"                   0 "180 Degree Capital Corp"  31 13605 11780 13605 0
"InnovaLight, Inc."                  0 "180 Degree Capital Corp"  32 18827 17002 18827 0
"EchoPixel Inc"                      1 "180 Degree Capital Corp"  33 19666 17841 19666 1
"Nextreme Thermal Solutions Inc"     0 "180 Degree Capital Corp"  34 17805 15980 17805 0
"OpGen Inc"                          0 "180 Degree Capital Corp"  35 19053 17228 19053 1
"Continuum Photonics Inc"            0 "180 Degree Capital Corp"  36 16161 14336 16161 0
"NeoPhotonics Corp"                  0 "180 Degree Capital Corp"  37 16819 14994 16819 0
"Laser Light Engines Inc"            0 "180 Degree Capital Corp"  38 19250 17425 19250 0
"Chlorogen, Inc."                    0 "180 Degree Capital Corp"  39 15830 14005 15830 1
"Geo Semiconductor Inc"              0 "180 Degree Capital Corp"  40 18533 16708 18533 1
"Bridgelux Inc"                      0 "180 Degree Capital Corp"  41 18326 16501 18326 0
"Contour Energy Systems Inc"         0 "180 Degree Capital Corp"  42 17881 16056 17881 1
"Kovio Inc"                          0 "180 Degree Capital Corp"  43 18091 16266 18091 0
"NeoPhotonics Corp"                  0 "180 Degree Capital Corp"  44 14914 13089 14914 1
"Nano Terra Inc"                     0 "180 Degree Capital Corp"  45 18682 16857 18682 1
"Contour Energy Systems Inc"         0 "180 Degree Capital Corp"  46 18792 16967 18792 0
"Ultora Inc"                         0 "180 Degree Capital Corp"  47 18610 16785 18610 1
"Nextreme Thermal Solutions Inc"     0 "180 Degree Capital Corp"  48 16472 14647 16472 1
"NEUROMetrix, Inc."                  0 "180 Degree Capital Corp"  49 14264 12439 14264 0
"Adesto Technologies Corp"           0 "180 Degree Capital Corp"  50 18441 16616 18441 0
"Starfire Systems Inc"               0 "180 Degree Capital Corp"  51 16204 14379 16204 1
"Cswitch Corporation"                0 "180 Degree Capital Corp"  52 16875 15050 16875 0
"Hzo Inc"                            0 "180 Degree Capital Corp"  53 19485 17660 19485 0
"NeoPhotonics Corp"                  0 "180 Degree Capital Corp"  54 16614 14789 16614 0
"SciQuest Inc"                       1 "180 Degree Capital Corp"  55 14090 12265 14090 1
"D-Wave Systems Inc"                 1 "180 Degree Capital Corp"  56 18445 16620 18445 0
"Chlorogen, Inc."                    0 "180 Degree Capital Corp"  57 17297 15472 17297 0
"Cswitch Corporation"                0 "180 Degree Capital Corp"  58 17087 15262 17087 0
"Adesto Technologies Corp"           0 "180 Degree Capital Corp"  59 19267 17442 19267 0
"Bridgelux Inc"                      0 "180 Degree Capital Corp"  60 17374 15549 17374 0
"PureSpeech, Inc."                   1 "180 Degree Capital Corp"  61 13270 11445 13270 1
"NBX Corporation"                    0 "180 Degree Capital Corp"  62 13787 11962 13787 0
"Kovio Inc"                          0 "180 Degree Capital Corp"  63 16743 14918 16743 1
"Bridgelux Inc"                      0 "180 Degree Capital Corp"  64 18779 16954 18779 0
"Kovio Inc"                          0 "180 Degree Capital Corp"  65 17381 15556 17381 0
"Laser Light Engines Inc"            0 "180 Degree Capital Corp"  66 18221 16396 18221 0
"InnovaLight, Inc."                  0 "180 Degree Capital Corp"  67 18702 16877 18702 0
"Sun Dial Market Place Corporation"  1 "180 Degree Capital Corp"  68 14473 12648 14473 1
"SiOnyx Inc"                         0 "180 Degree Capital Corp"  69 17164 15339 17164 0
"NEUROMetrix, Inc."                  0 "180 Degree Capital Corp"  70 13936 12111 13936 0
"Nanomix Inc"                        0 "180 Degree Capital Corp"  71 16499 14674 16499 0
"Agile Materials & Technologies Inc" 0 "180 Degree Capital Corp"  72 15658 13833 15658 1
"BioVex Group, Inc."                 0 "180 Degree Capital Corp"  73 17986 16161 17986 0
"Metabolon Inc"                      0 "180 Degree Capital Corp"  74 18868 17043 18868 0
"PolyRemedy Inc"                     1 "180 Degree Capital Corp"  75 17573 15748 17573 1
"Nantero Inc"                        0 "180 Degree Capital Corp"  76 16496 14671 16496 0
"NEUROMetrix, Inc."                  0 "180 Degree Capital Corp"  77 13301 11476 13301 1
"Metabolon Inc"                      0 "180 Degree Capital Corp"  78 18206 16381 18206 0
"Terravia Holdings Inc"              0 "180 Degree Capital Corp"  79 18053 16228 18053 0
"NBX Corporation"                    0 "180 Degree Capital Corp"  80 13410 11585 13410 1
"SiOnyx Inc"                         0 "180 Degree Capital Corp"  81 17073 15248 17073 0
"Agile Materials & Technologies Inc" 0 "180 Degree Capital Corp"  82 16392 14567 16392 0
"Molecular Imprints Inc"             0 "180 Degree Capital Corp"  83 16693 14868 16693 0
"LifCo Inc"                          0 "180 Degree Capital Corp"  84 17345 15520 17345 1
"Contour Energy Systems Inc"         0 "180 Degree Capital Corp"  85 19241 17416 19241 0
"SiOnyx Inc"                         0 "180 Degree Capital Corp"  86 18435 16610 18435 0
"Adesto Technologies Corp"           0 "180 Degree Capital Corp"  87 19402 17577 19402 0
"BioVex Group, Inc."                 0 "180 Degree Capital Corp"  88 17485 15660 17485 1
"NanoGram Corporation"               0 "180 Degree Capital Corp"  89 17475 15650 17475 0
"Tetravitae Bioscience Inc"          0 "180 Degree Capital Corp"  90 17866 16041 17866 1
"Crystal IS, Inc."                   0 "180 Degree Capital Corp"  91 16342 14517 16342 1
"Evolved Nanomaterial Sciences Inc"  0 "180 Degree Capital Corp"  92 16869 15044 16869 1
"Optiva Inc"                         0 "180 Degree Capital Corp"  93 15497 13672 15497 1
"Nantero Inc"                        0 "180 Degree Capital Corp"  94 15918 14093 15918 0
end
format %td InvestmentDate
format %td TransactionDate

[/CODE]

[Nick Cox]

I don't understand your code. count doesn't support options.

TransactionDate `i'

is not legal either. So, I suggest that you show us code that actually runs. rangestat (SSC; not a plugin, as it's all Stata/Mata code) is indeed pertinent here. I don't know why you are not able to apply that.

[Nicky Joosse]

Thanks for your swift reply, Nick.

I have adjusted the loop, and the following code ran once, I wanted to re-run it and it did not work (Invalid Syntax, r(198). I Have, however still one issue: I need to run it per group, so by FirmName. How can I do that? Thanks again for your advice.

gen test_Computer_Related = 0
forvalues i = 1/`r(max)' {
count if inrange(InvestmentDate[`i'], InvestmentDate-1825, InvestmentDate-0) & pid != `i' & Computer_Related ==1 & NEW ==1
replace test_Computer_Related = r(N) in `i'
}