Counting Distinct Observations: number of new investments of specific VCs in an industry 5 years prior to the focal observation

[Nicky Joosse]

Dear All,

I am using Stata 15.0

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. 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 if inrange(InvestmentDate[`i'], InvestmentDate-1825, InvestmentDate-0) & pid != `i' & Computer_Related ==1 & NEW ==1
replace test_Computer_Related = r(N) in `i'
}

That code ran and should be right, except that it does not take into account that only the investments of the specific VC of the focal investment should be counted. I wrote code for that

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

Stata gives, however, invalid syntax r(198). I do not understand why, and neither how to rewrite my code so it does what I need.

I have also tried another line of code, following the advice of Cox (https://www.stata.com/statalist/arch.../msg00424.html)

gen count_CR =.
local N = _N
qui forval i = 1/`N'{
count if FirmName==Firmname[`i'] & _n != `i' &
inrange(InvestmentDate[`i'], InvestmentDate-1825, InvestmentDate-0) & Computer_Related ==1 & NEW ==1
replace count_CR = r(N) in `i'
}

And again, I get the syntax error (invalid syntaxr(198); )

Could somebody help me and tell me what I do wrong and how I should write this code?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str59 FirmName float Computer_Related int InvestmentDate float(NEW pid)
"10X Venture Partners LLC" 1 19508 1  1
"10X Venture Partners LLC" 1 19400 1  2
"10X Venture Partners LLC" 0 19260 1  3
"10X Venture Partners LLC" 1 19575 0  4
"10X Venture Partners LLC" 1 19508 1  5
"180 Capital Fund LLC"     0 16215 1  1
"180 Degree Capital Corp"  1 19156 0  1
"180 Degree Capital Corp"  0 18918 0  2
"180 Degree Capital Corp"  0 16798 0  3
"180 Degree Capital Corp"  0 19478 0  4
"180 Degree Capital Corp"  0 18787 0  5
"180 Degree Capital Corp"  0 18179 0  6
"180 Degree Capital Corp"  0 16576 1  7
"180 Degree Capital Corp"  0 19712 0  8
"180 Degree Capital Corp"  0 19183 0  9
"180 Degree Capital Corp"  1 15697 1 10
"180 Degree Capital Corp"  0 15858 1 11
"180 Degree Capital Corp"  0 19492 0 12
"180 Degree Capital Corp"  0 18744 1 13
"180 Degree Capital Corp"  1 19270 0 14
"180 Degree Capital Corp"  0 18851 1 15
"180 Degree Capital Corp"  0 16425 1 16
"180 Degree Capital Corp"  0 18805 1 17
"180 Degree Capital Corp"  0 17232 1 18
"180 Degree Capital Corp"  1 14670 0 19
"180 Degree Capital Corp"  0 18204 1 20
"180 Degree Capital Corp"  0 15953 0 21
"180 Degree Capital Corp"  0 13696 1 22
"180 Degree Capital Corp"  0 15426 1 23
"180 Degree Capital Corp"  1 16937 1 24
"180 Degree Capital Corp"  0 16582 1 25
"180 Degree Capital Corp"  0 16699 0 26
"180 Degree Capital Corp"  0 13180 1 27
"180 Degree Capital Corp"  0 19457 1 28
"180 Degree Capital Corp"  0 19004 1 29
"180 Degree Capital Corp"  0 19562 0 30
"180 Degree Capital Corp"  0 13605 0 31
"180 Degree Capital Corp"  0 18827 0 32
"180 Degree Capital Corp"  1 19666 1 33
"180 Degree Capital Corp"  0 17805 0 34
"180 Degree Capital Corp"  0 19053 1 35
"180 Degree Capital Corp"  0 16161 0 36
"180 Degree Capital Corp"  0 16819 0 37
"180 Degree Capital Corp"  0 19250 0 38
"180 Degree Capital Corp"  0 15830 1 39
"180 Degree Capital Corp"  0 18533 1 40
"180 Degree Capital Corp"  0 18326 0 41
"180 Degree Capital Corp"  0 17881 1 42
"180 Degree Capital Corp"  0 18091 0 43
"180 Degree Capital Corp"  0 14914 1 44
"180 Degree Capital Corp"  0 18682 1 45
"180 Degree Capital Corp"  0 18792 0 46
"180 Degree Capital Corp"  0 18610 1 47
"180 Degree Capital Corp"  0 16472 1 48
"180 Degree Capital Corp"  0 14264 0 49
"180 Degree Capital Corp"  0 18441 0 50
"180 Degree Capital Corp"  0 16204 1 51
"180 Degree Capital Corp"  0 16875 0 52
"180 Degree Capital Corp"  0 19485 0 53
"180 Degree Capital Corp"  0 16614 0 54
"180 Degree Capital Corp"  1 14090 1 55
"180 Degree Capital Corp"  1 18445 0 56
"180 Degree Capital Corp"  0 17297 0 57
"180 Degree Capital Corp"  0 17087 0 58
"180 Degree Capital Corp"  0 19267 0 59
"180 Degree Capital Corp"  0 17374 0 60
"180 Degree Capital Corp"  1 13270 1 61
"180 Degree Capital Corp"  0 13787 0 62
"180 Degree Capital Corp"  0 16743 1 63
"180 Degree Capital Corp"  0 18779 0 64
"180 Degree Capital Corp"  0 17381 0 65
"180 Degree Capital Corp"  0 18221 0 66
"180 Degree Capital Corp"  0 18702 0 67
"180 Degree Capital Corp"  1 14473 1 68
"180 Degree Capital Corp"  0 17164 0 69
"180 Degree Capital Corp"  0 13936 0 70
"180 Degree Capital Corp"  0 16499 0 71
"180 Degree Capital Corp"  0 15658 1 72
"180 Degree Capital Corp"  0 17986 0 73
"180 Degree Capital Corp"  0 18868 0 74
"180 Degree Capital Corp"  1 17573 1 75
"180 Degree Capital Corp"  0 16496 0 76
"180 Degree Capital Corp"  0 13301 1 77
"180 Degree Capital Corp"  0 18206 0 78
"180 Degree Capital Corp"  0 18053 0 79
"180 Degree Capital Corp"  0 13410 1 80
"180 Degree Capital Corp"  0 17073 0 81
"180 Degree Capital Corp"  0 16392 0 82
"180 Degree Capital Corp"  0 16693 0 83
"180 Degree Capital Corp"  0 17345 1 84
"180 Degree Capital Corp"  0 19241 0 85
"180 Degree Capital Corp"  0 18435 0 86
"180 Degree Capital Corp"  0 19402 0 87
"180 Degree Capital Corp"  0 17485 1 88
"180 Degree Capital Corp"  0 17475 0 89
"180 Degree Capital Corp"  0 17866 1 90
"180 Degree Capital Corp"  0 16342 1 91
"180 Degree Capital Corp"  0 16869 1 92
"180 Degree Capital Corp"  0 15497 1 93
"180 Degree Capital Corp"  0 15918 0 94
end
format %td InvestmentDate

Best,

Nicky Joosse

[Nick Cox]

Pleased to think that my posts from 2011 are of interest but over the last few years my posts on similar problems have mostly focused on rangestat (SSC; first author Robert Picard). With your data example my guess is that

gen wanted = Computer_Related == 1 & NEW == 1
rangestat (sum) wanted, excludeself by(FirmName) interval(InvestmentDate -1825 0)

is what you want. Note the construction of a new indicator which I think simplifies the programming and reduces the need for clean-up afterwards. Summing indicators (adding zeros and ones) is naturally equivalent to counting ones.

Your syntax looks good to me so long as you don't split a command

gen count_CR =.
qui forval i = 1/`=_N' {      
    count if FirmName==Firmname[`i'] & _n != `i' & inrange(InvestmentDate[`i'], InvestmentDate-1825, InvestmentDate-0) & Computer_Related ==1 & NEW ==1    

    replace count_CR = r(N) in `i'
}

but rangestat automates the loop and does the calculations much, much faster.

[Nicky Joosse]

Thanks for your reply! This works