Calculate Average of Lagged Variables by Group (excluding own values)

[Serena Chan]

Hi Folks,


I am new to the forum and I am now working on my thesis regarding equity analyst forecast errors on companies based on the data from I/B/E/S.

The following codes are a small clip of my data sorted by company id(CUSIP), analyst (ANALYS), quarter of the year (YEAR_Q) and forecast errors (FE).

Basically what I want to calculate now is the average of an analyst's forecast errors on the other companies he/she followed in the previous quarter.
For example, for analyst #380 who covered company 1416 in 2021q3, I want to generate a variable that captures his/her average forecast errors on company 494 and 2234 (not including company 1416) in 2021q2.
So the answer in this case would be -0.003 i.e. (-0.006+0)/2

If I am looking for the analyst's average forecast errors on other companies he/she covers in the current quarter, I could do it like the following discussion proposed:
https://www.stata.com/statalist/arch.../msg00462.html

However I am trying to do this for the previous quarter and codes I tried like the following didn't work (sorry I am pretty new to stata - errors I made may look silly)

bysort ANALYS YEAR_Q CUSIP: gen sum_FE_own_prev = sum(FE_own[_n-1])

or

bysort ANALYS YEAR_Q CUSIP: gen sum_FE_own_prev = sum(L1.FE_own)


I know I might need tsset for that but my YEAR_Q are not unique so errors of repeated time values in sample would occur.


Any help much appreciated!

P.S. using stata 17

input float a_CUSIP double ANALYS float(YEAR_Q FE_own)
1174  70 2021q2  -.0004757255
  32  70 2021q3   -.001127759
 512  70 2021q3             0
1565  70 2021q3  .00011461318
2245  70 2021q3   -.014095536
 427  70 2022q2  -.0015798463
1089  70 2022q2   -.001376351
1565  70 2022q2  -.0003412652
2245  70 2022q2   .0004115791
2366  70 2022q2  -.0006048471
1565  70 2022q4 -.00007655502
 486 380 2020q2   -.006934307
 662 380 2020q2   -.004965243
1629 380 2020q2   -.006343568
2621 380 2020q2    -.00754976
 497 380 2020q2   -.002758794
 976 380 2020q3   -.001270971
 494 380 2021q2         -.006
1416 380 2021q2   -.005897698
2234 380 2021q2             0
1416 380 2021q3   -.003161797
2234 380 2021q3  -.0015360983
2302 380 2021q4     .03836735
 976 380 2022q2  -.0007688365

Thank you very much!


Best,
Serena

[Serena Chan]

I tried the following code but didn't seem to be working. Anything wrong around the interval (-1 0)? Many thanks

rangestat (mean) FE_own_other=FE_own, by(ANALYS YEAR_Q) excludeself interval(YEAR_Q -1 0)

[Clyde Schechter]

I'm not entirely sure what you want to do, but I think it's this:

* Example generated by -dataex-. For more info, type help dataex
clear
input int(a_cusip analys) float fe_own int year_q
1174  70  -.0004757255 245
  32  70   -.001127759 246
 512  70             0 246
1565  70  .00011461318 246
2245  70   -.014095536 246
 427  70  -.0015798463 249
1089  70   -.001376351 249
1565  70  -.0003412652 249
2245  70   .0004115791 249
2366  70  -.0006048471 249
1565  70 -.00007655502 251
 486 380   -.006934307 241
 662 380   -.004965243 241
1629 380   -.006343568 241
2621 380    -.00754976 241
 497 380   -.002758794 241
 976 380   -.001270971 242
 494 380         -.006 245
1416 380   -.005897698 245
2234 380             0 245
1416 380   -.003161797 246
2234 380  -.0015360983 246
2302 380     .03836735 247
 976 380  -.0007688365 249
end
format %tq year_q

tempfile copy
save `copy'

rangejoin year_q -1 -1 using `copy', by(analys)
drop if a_cusip == a_cusip_U
collapse (mean) fe_own_U, by(a_cusip analys fe_own year_q)

The variable fe_own_U is what, I believe, you are trying to compute.

-rangejoin- is written by Robert Picard and is available from SSC. To use -rangejoin- you must also have installed -rangestat-, which, based on what you wrote in #2, I suppose you have already done.

One of the things I find unclear in your post is whether you want the mean to be taken over cusips in both the current and preceding quarter, or just the preceding quarter. The code shown above assumes the latter. But if you want both current and preceding, change the second -1 in the -rangejoin- command to 0.

Finally, the example data you show in #1 does not work because you declare YEAR_Q as a float, but you show it as a string. It is not real -dataex- output. Either you took real -dataex- output and edited it in a way that made it unusable, or you took list output and stuck an incorrect -input- command in front of it. Or maybe you did it some other way. But whatever you did, please don't do it again. Use real -dataex- output to show example data--never modify it in any way. It is the only way to show example data that is guaranteed to be usable by those who want to help you and faithful to your data.

[Serena Chan]

Clyde Schechter You are right that I only want to do it for the preceding quarter. Thanks for the help - the code works very well. Also thanks for the reminder - won't modify the data in any way again.


Here comes my another question: I now want to generate a variable called PeerFE_prev (peer analysts' forecast errors), which calculates other peer analysts' average forecast error (in quarter t-1) on the other firms that analyst i follows in quarter t.

May sound complicated but what I'm trying to say is that, for example, if analyst i follows firms j, k, and l in quarter t, PeerFE_prev is calculated as other peer analysts’ average forecast error on firms k and l during quarter t‒1.


An explanation with data may sound easier to understand:

So for example, for ANALYS #78782 who covered company #1174 in 2020q3 (t), I want to generate a variable that calculate the average forecast errors of his/her peer analysts who covered company #427 and #512 in 2020q2 (t-1).

* Example generated by -dataex-. For more info, type help dataex
clear
input float a_CUSIP double ANALYS float(YEAR_Q FE_own)
1174     70 245  -.0004757255
 512     70 246             0
 427     70 249  -.0015798463
1174   2739 245 -.00050076365
 512   9700 241             0
1174   9700 241   -.006176837
1174   9700 242  -.0005642372
 512   9700 246   .0012755102
 427   9700 251 -.00058193103
 427  24192 245  -.0016429804
 427  24192 246    -.00275303
 427  24192 249   -.002903501
 427  24192 251  -.0005334368
 427  30139 241  -.0030612755
1174  30139 245   .0002729162
 512  30139 246 -.00009920635
 512  30139 249    -.00338763
 427  49483 241    -.00523508
1174  49483 241   -.013449565
 427  53035 241  -.0031310765
1174  53035 242  -.0005642372
 512  53035 245  -.0001686625
 427  53035 246  -.0023209574
 512  53035 246  -.0014880953
 427  53035 249  -.0029590095
 512  53035 249  .00011803588
1174  53035 249   -.001895612
 427  53035 251 -.00024247127
1174  57524 242  -.0007359615
 512  57524 245    .005059875
1174  57524 245   .0018778638
 427  57524 246  -.0022177189
 512  57524 246   .0009920635
1174  57524 247 -.00011457116
1174  57524 249  -.0022618098
1174  71787 245  -.0001752673
 427  71787 249   -.003415884
 427  71787 251 -.00029096552
1174  74192 247   .0007103412
 512  75101 245   -.001686625
 512  75101 246  .00056689343
 427  75101 247   .0004582951
 427  75101 249  -.0039282665
 427  78782 241   -.004636785
 512  78782 241             0
1174  78782 241   -.013150685
1174  78782 242   -.000932218
 427  78782 245  -.0012733098
 427  78782 246   -.001797117
1174  78782 247 -.00009165693
 512  78782 249             0
 427  78782 251   -.000387954
 427  82682 241   -.003041332
 512  82682 241   .0021708792
 427  82682 245 -.00008214902
 512  82682 245    .006409175
 512  82682 246  -.0004251701
1174  82682 249  -.0021971867
1174  89984 241   -.010510585
 427  89984 245  -.0011090117
 427  89984 246   -.001797117
1174 106368 241   -.005753424
1174 106368 242  -.0007359615
 427 106368 249   -.005038429
 512 106368 249   -.003186969
1174 108054 241   -.005952678
1174 108054 242  -.0006868974
 512 108054 245   .0045538875
 427 108054 246  -.0017588804
 512 112207 241   -.013025275
1174 112207 242  -.0003679808
 427 112207 249   -.001622545
 512 112207 249 -.00023607176
1174 112207 249  -.0015294143
1174 112415 241    -.00622665
 512 112415 245   .0045538875
 427 112415 249   -.003885568
1174 122937 247  -.0002291423
 427 122937 249   -.003330487
 427 130592 241    .001545595
1174 130592 242  -.0007114295
 427 130592 245  -.0006982667
 512 130592 245    .003879238
1174 157521 249  -.0021541046
1174 163969 242  -.0006868974
 427 164367 246  -.0022177189
 427 186081 247    .002635197
 512 186081 249   -.008498584
1174 186081 249  -.0016586606
 512 193740 249   -.003068933
 427 196999 249   -.002860803
1174 196999 249  -.0019817762
end
format %tq YEAR_Q

And we could know from the above data that there are other 5 analysts followed company #427 and 3 followed company 512 in the previous quarter 2022q2. So the answer I'm looking for (i.e. PeerFE_prev for ANALYS #78782 who covered company #1174 in 2020q3) should be -0.0029722 (below is just a small snapshot of my previous code for better explanation purpose.)

* Example generated by -dataex-. For more info, type help dataex
clear
input float a_CUSIP double ANALYS float(YEAR_Q FE_own)
427  82682 241  -.003041332
427  49483 241   -.00523508
427  53035 241 -.0031310765
427 130592 241   .001545595
427  30139 241 -.0030612755
512 112207 241  -.013025275
512  82682 241  .0021708792
512   9700 241            0
end
format %tq YEAR_Q

Seems like I need to use a combination of rangejoin and loop to achieve that. Unfortunately I am still stuck with it after trying different codes. Any help will be much appreciated! Thank you again.

[Serena Chan]

Clyde Schechter I am so sorry - please ignore what I typed in #4, something doesn't make sense.

Seems I can't edit my post now so please see the following:


Basically I now want to generate a variable called PeerFE_prev (peer analysts' forecast errors), which calculates other peer analysts' average forecast error (in quarter t-1) on the other firms that analyst i follows in quarter t.

May sound complicated but what I'm trying to say is that, for example, if analyst i follows firms j, k, and l in quarter t, PeerFE_prev is calculated as other peer analysts’ average forecast error on firms k and l during quarter t‒1.


An explanation with data may sound easier to understand:

So for example, for ANALYS #57524 who covered company #427 in 2021q3 (t), I want to generate a variable that calculate the average forecast errors of his/her peer analysts who covered company #512 (as #512 is the other company that our focal analyst #57524 followed in t) in 2021q2 (t-1).

* Example generated by -dataex-. For more info, type help dataex
clear
input float a_CUSIP double ANALYS float(YEAR_Q FE_own)
1174     70 245  -.0004757255
 512     70 246             0
 427     70 249  -.0015798463
1174   2739 245 -.00050076365
 512   9700 241             0
1174   9700 241   -.006176837
1174   9700 242  -.0005642372
 512   9700 246   .0012755102
 427   9700 251 -.00058193103
 427  24192 245  -.0016429804
 427  24192 246    -.00275303
 427  24192 249   -.002903501
 427  24192 251  -.0005334368
 427  30139 241  -.0030612755
1174  30139 245   .0002729162
 512  30139 246 -.00009920635
 512  30139 249    -.00338763
 427  49483 241    -.00523508
1174  49483 241   -.013449565
 427  53035 241  -.0031310765
1174  53035 242  -.0005642372
 512  53035 245  -.0001686625
 427  53035 246  -.0023209574
 512  53035 246  -.0014880953
 427  53035 249  -.0029590095
 512  53035 249  .00011803588
1174  53035 249   -.001895612
 427  53035 251 -.00024247127
1174  57524 242  -.0007359615
 512  57524 245    .005059875
1174  57524 245   .0018778638
 427  57524 246  -.0022177189
 512  57524 246   .0009920635
1174  57524 247 -.00011457116
1174  57524 249  -.0022618098
1174  71787 245  -.0001752673
 427  71787 249   -.003415884
 427  71787 251 -.00029096552
1174  74192 247   .0007103412
 512  75101 245   -.001686625
 512  75101 246  .00056689343
 427  75101 247   .0004582951
 427  75101 249  -.0039282665
 427  78782 241   -.004636785
 512  78782 241             0
1174  78782 241   -.013150685
1174  78782 242   -.000932218
 427  78782 245  -.0012733098
 427  78782 246   -.001797117
1174  78782 247 -.00009165693
 512  78782 249             0
 427  78782 251   -.000387954
 427  82682 241   -.003041332
 512  82682 241   .0021708792
 427  82682 245 -.00008214902
 512  82682 245    .006409175
 512  82682 246  -.0004251701
1174  82682 249  -.0021971867
1174  89984 241   -.010510585
 427  89984 245  -.0011090117
 427  89984 246   -.001797117
1174 106368 241   -.005753424
1174 106368 242  -.0007359615
 427 106368 249   -.005038429
 512 106368 249   -.003186969
1174 108054 241   -.005952678
1174 108054 242  -.0006868974
 512 108054 245   .0045538875
 427 108054 246  -.0017588804
 512 112207 241   -.013025275
1174 112207 242  -.0003679808
 427 112207 249   -.001622545
 512 112207 249 -.00023607176
1174 112207 249  -.0015294143
1174 112415 241    -.00622665
 512 112415 245   .0045538875
 427 112415 249   -.003885568
1174 122937 247  -.0002291423
 427 122937 249   -.003330487
 427 130592 241    .001545595
1174 130592 242  -.0007114295
 427 130592 245  -.0006982667
 512 130592 245    .003879238
1174 157521 249  -.0021541046
1174 163969 242  -.0006868974
 427 164367 246  -.0022177189
 427 186081 247    .002635197
 512 186081 249   -.008498584
1174 186081 249  -.0016586606
 512 193740 249   -.003068933
 427 196999 249   -.002860803
1174 196999 249  -.0019817762
end
format %tq YEAR_Q

And we could know from the above data that there are other 6 analysts who followed company #512 in the previous quarter i.e. 2021q2. So the answer I'm looking for (i.e. PeerFE_prev for ANALYS #57524 who covered company #427 in 2021q3) should be 0.0029235 (below is just a small snapshot of my previous code for better explanation purpose.)

* Example generated by -dataex-. For more info, type help dataex
clear
input float a_CUSIP double ANALYS float(YEAR_Q FE_own)
512  53035 245 -.0001686625
512  75101 245  -.001686625
512  82682 245   .006409175
512 108054 245  .0045538875
512 112415 245  .0045538875
512 130592 245   .003879238
end
format %tq YEAR_Q

Seems like I need to use a combination of rangejoin and loop to achieve that. Tried different codes but unfortunately still stuck with it.

Again, any help much appreciated!

[Clyde Schechter]

So it sounds like this (starting over from the original data):

* Example generated by -dataex-. For more info, type help dataex
clear
input float a_cusip double analys float(year_q fe_own)
1174     70 245  -.0004757255
 512     70 246             0
 427     70 249  -.0015798463
1174   2739 245 -.00050076365
 512   9700 241             0
1174   9700 241   -.006176837
1174   9700 242  -.0005642372
 512   9700 246   .0012755102
 427   9700 251 -.00058193103
 427  24192 245  -.0016429804
 427  24192 246    -.00275303
 427  24192 249   -.002903501
 427  24192 251  -.0005334368
 427  30139 241  -.0030612755
1174  30139 245   .0002729162
 512  30139 246 -.00009920635
 512  30139 249    -.00338763
 427  49483 241    -.00523508
1174  49483 241   -.013449565
 427  53035 241  -.0031310765
1174  53035 242  -.0005642372
 512  53035 245  -.0001686625
 427  53035 246  -.0023209574
 512  53035 246  -.0014880953
 427  53035 249  -.0029590095
 512  53035 249  .00011803588
1174  53035 249   -.001895612
 427  53035 251 -.00024247127
1174  57524 242  -.0007359615
 512  57524 245    .005059875
1174  57524 245   .0018778638
 427  57524 246  -.0022177189
 512  57524 246   .0009920635
1174  57524 247 -.00011457116
1174  57524 249  -.0022618098
1174  71787 245  -.0001752673
 427  71787 249   -.003415884
 427  71787 251 -.00029096552
1174  74192 247   .0007103412
 512  75101 245   -.001686625
 512  75101 246  .00056689343
 427  75101 247   .0004582951
 427  75101 249  -.0039282665
 427  78782 241   -.004636785
 512  78782 241             0
1174  78782 241   -.013150685
1174  78782 242   -.000932218
 427  78782 245  -.0012733098
 427  78782 246   -.001797117
1174  78782 247 -.00009165693
 512  78782 249             0
 427  78782 251   -.000387954
 427  82682 241   -.003041332
 512  82682 241   .0021708792
 427  82682 245 -.00008214902
 512  82682 245    .006409175
 512  82682 246  -.0004251701
1174  82682 249  -.0021971867
1174  89984 241   -.010510585
 427  89984 245  -.0011090117
 427  89984 246   -.001797117
1174 106368 241   -.005753424
1174 106368 242  -.0007359615
 427 106368 249   -.005038429
 512 106368 249   -.003186969
1174 108054 241   -.005952678
1174 108054 242  -.0006868974
 512 108054 245   .0045538875
 427 108054 246  -.0017588804
 512 112207 241   -.013025275
1174 112207 242  -.0003679808
 427 112207 249   -.001622545
 512 112207 249 -.00023607176
1174 112207 249  -.0015294143
1174 112415 241    -.00622665
 512 112415 245   .0045538875
 427 112415 249   -.003885568
1174 122937 247  -.0002291423
 427 122937 249   -.003330487
 427 130592 241    .001545595
1174 130592 242  -.0007114295
 427 130592 245  -.0006982667
 512 130592 245    .003879238
1174 157521 249  -.0021541046
1174 163969 242  -.0006868974
 427 164367 246  -.0022177189
 427 186081 247    .002635197
 512 186081 249   -.008498584
1174 186081 249  -.0016586606
 512 193740 249   -.003068933
 427 196999 249   -.002860803
1174 196999 249  -.0019817762
end
format %tq year_q

tempfile copy
save `copy'

rangejoin year_q -1 -1 using `copy', by(analys)
drop if a_cusip == a_cusip_U
collapse (mean) fe_own_U, by(a_cusip analys fe_own year_q)
tempfile own_results
save `own_results'

use `copy'
rangejoin year_q -1 -1 using `copy', by(a_cusip)
drop if analys == analys_U
collapse (mean) fe_other_U = fe_own_U, by(a_cusip analys fe_own year_q)
merge 1:1 a_cusip analys fe_own year_q using `own_results', nogenerate

The variable fe_own_U is the same result as earlier in this thread. The variable fe_other_U is the mean forecasting error of other analysts on the same cusip in the same (preceding) quarter.

Notice, by the way, that no explicit loops are involved in this code. While there are things that can only be done with loops, Stata has numerous commands that greatly reduce the need for explicit loops compared to other programming languages. So before you start to try writing loops, particularly loops over the values of a variable, or loops over individual observations, ask yourself if the iteration cannot be accomplished better with a -by:- prefix, or some of the specialized user-written programs such as -rangestat-, -rangejoin-, -rangerun-, or -runby-. Another area where explicit looping can be avoided is the use of wildcard abbreviations of lists of variables which need to be processed in some way. Many commands allow you to submit the entire list that way and will process each of the referenced variables sequentially. Perhaps most amazing of all of these is the -rename- command. See -help rename group- for details on that.

[Serena Chan]

Clyde Schechter Thanks again for your reply.

The variable fe_other_U is the mean forecasting error of other analysts on the same cusip in the same (preceding) quarter.

Unfortunately it is not the same firm that I'm looking for. It is the peer analysts' average forecast error (in preceding quarter) on the other firms that analyst i follows in quarter t.

So what I'm trying to say is that, for example, if analyst i follows firms j, k, and l in quarter t, fe_other_U is calculated as other peer analysts’ average forecast error on firms k and l (not j the focal firm) during quarter t‒1.

(I know it may sound a bit weird but this is the result I aim at as I am asked to replicate a paper)

In #5 I used an example and very happy to clarify if it's not clear.

Thanks again. Much appreciated, truly

[Clyde Schechter]

OK, sorry. I think I have it now. The code I gave in #3 destroyed some of the information needed to do this additional request. So we have to start back at the original data and proceed a little differently.

* Example generated by -dataex-. For more info, type help dataex
clear
input float a_cusip double analys float(year_q fe_own)
1174     70 245  -.0004757255
 512     70 246             0
 427     70 249  -.0015798463
1174   2739 245 -.00050076365
 512   9700 241             0
1174   9700 241   -.006176837
1174   9700 242  -.0005642372
 512   9700 246   .0012755102
 427   9700 251 -.00058193103
 427  24192 245  -.0016429804
 427  24192 246    -.00275303
 427  24192 249   -.002903501
 427  24192 251  -.0005334368
 427  30139 241  -.0030612755
1174  30139 245   .0002729162
 512  30139 246 -.00009920635
 512  30139 249    -.00338763
 427  49483 241    -.00523508
1174  49483 241   -.013449565
 427  53035 241  -.0031310765
1174  53035 242  -.0005642372
 512  53035 245  -.0001686625
 427  53035 246  -.0023209574
 512  53035 246  -.0014880953
 427  53035 249  -.0029590095
 512  53035 249  .00011803588
1174  53035 249   -.001895612
 427  53035 251 -.00024247127
1174  57524 242  -.0007359615
 512  57524 245    .005059875
1174  57524 245   .0018778638
 427  57524 246  -.0022177189
 512  57524 246   .0009920635
1174  57524 247 -.00011457116
1174  57524 249  -.0022618098
1174  71787 245  -.0001752673
 427  71787 249   -.003415884
 427  71787 251 -.00029096552
1174  74192 247   .0007103412
 512  75101 245   -.001686625
 512  75101 246  .00056689343
 427  75101 247   .0004582951
 427  75101 249  -.0039282665
 427  78782 241   -.004636785
 512  78782 241             0
1174  78782 241   -.013150685
1174  78782 242   -.000932218
 427  78782 245  -.0012733098
 427  78782 246   -.001797117
1174  78782 247 -.00009165693
 512  78782 249             0
 427  78782 251   -.000387954
 427  82682 241   -.003041332
 512  82682 241   .0021708792
 427  82682 245 -.00008214902
 512  82682 245    .006409175
 512  82682 246  -.0004251701
1174  82682 249  -.0021971867
1174  89984 241   -.010510585
 427  89984 245  -.0011090117
 427  89984 246   -.001797117
1174 106368 241   -.005753424
1174 106368 242  -.0007359615
 427 106368 249   -.005038429
 512 106368 249   -.003186969
1174 108054 241   -.005952678
1174 108054 242  -.0006868974
 512 108054 245   .0045538875
 427 108054 246  -.0017588804
 512 112207 241   -.013025275
1174 112207 242  -.0003679808
 427 112207 249   -.001622545
 512 112207 249 -.00023607176
1174 112207 249  -.0015294143
1174 112415 241    -.00622665
 512 112415 245   .0045538875
 427 112415 249   -.003885568
1174 122937 247  -.0002291423
 427 122937 249   -.003330487
 427 130592 241    .001545595
1174 130592 242  -.0007114295
 427 130592 245  -.0006982667
 512 130592 245    .003879238
1174 157521 249  -.0021541046
1174 163969 242  -.0006868974
 427 164367 246  -.0022177189
 427 186081 247    .002635197
 512 186081 249   -.008498584
1174 186081 249  -.0016586606
 512 193740 249   -.003068933
 427 196999 249   -.002860803
1174 196999 249  -.0019817762
end
format %tq year_q

tempfile copy
save `copy'

rangejoin year_q -1 -1 using `copy', by(analys)
drop if a_cusip == a_cusip_U
by a_cusip analys year_q, sort: egen wanted1 = mean(fe_own_U)

preserve
keep a_cusip analys year_q fe_own
rename (a_cusip) =_U
duplicates drop
save `copy', replace
restore

rangejoin year_q -1 -1 using `copy', by(a_cusip_U) suffix("_V")
drop if analys_V == analys
by a_cusip analys year_q, sort: egen wanted2 = mean(fe_own_V)
keep a_cusip analys year_q fe_own wanted1 wanted2
duplicates drop

[Serena Chan]

Clyde Schechter Thanks for the code - looks good.

But there's still something we need to fine tune for that: when calculating the peer analysts' average forecast errors, in the current code we included the company(/-ies) covered by analyst i in the preceding quarter (but in fact analyst i does not cover that in the current quarter).

Below is the final results generated. easier to illustrate.

For example, in the 2nd row for analys #70, wanted2 should be a missing value as analys #70 does not cover #1174 in the current quarter. In other words, as analys #70 does not cover any company other than #521 in the current quarter, wanted2 should contain no value.

As again, if analyst i follows firms j, k, and l in quarter t, wanted2 is calculated as other peer analysts’ average forecast error on firms k and l (not j the focal firm) during quarter t‒1.

* Example generated by -dataex-. For more info, type help dataex
clear
input float a_cusip double analys float(year_q fe_own wanted1 wanted2)
1174     70 245  -.0004757255             .             .
 512     70 246             0  -.0004757255   .0003686873
 427     70 249  -.0015798463             .             .
1174   2739 245 -.00050076365             .             .
 512   9700 241             0             .             .
1174   9700 241   -.006176837             .             .
1174   9700 242  -.0005642372             0   -.003618132
 512   9700 246   .0012755102             .             .
 427   9700 251 -.00058193103             .             .
 427  24192 245  -.0016429804             .             .
 427  24192 249   -.002903501             .             .
 427  24192 251  -.0005334368             .             .
 427  30139 241  -.0030612755             .             .
1174  30139 245   .0002729162             .             .
 512  30139 246 -.00009920635   .0002729162  .00018152683
 512  30139 249    -.00338763             .             .
 427  49483 241    -.00523508             .             .
1174  49483 241   -.013449565             .             .
 427  53035 241  -.0031310765             .             .
1174  53035 242  -.0005642372  -.0031310765  -.0028857754
 512  53035 245  -.0001686625             .             .
 427  53035 246  -.0023209574  -.0001686625   .0037949064
 427  53035 249  -.0029590095             .             .
 512  53035 249  .00011803588             .             .
1174  53035 249   -.001895612             .             .
 427  53035 251 -.00024247127             .             .
1174  57524 242  -.0007359615             .             .
 512  57524 245    .005059875             .             .
1174  57524 245   .0018778638             .             .
 427  57524 246  -.0022177189   .0034688695    .001666206
 512  57524 246   .0009920635   .0018778638 -.00021971007
1174  57524 247 -.00011457116  -.0006128277  -.0007923461
1174  57524 249  -.0022618098             .             .
1174  71787 245  -.0001752673             .             .
 427  71787 249   -.003415884             .             .
 427  71787 251 -.00029096552             .             .
1174  74192 247   .0007103412             .             .
 512  75101 245   -.001686625             .             .
 427  75101 247   .0004582951  .00056689343   .0003486395
 427  75101 249  -.0039282665             .             .
 427  78782 241   -.004636785             .             .
 512  78782 241             0             .             .
1174  78782 241   -.013150685             .             .
1174  78782 242   -.000932218  -.0023183925  -.0029721956
 427  78782 245  -.0012733098             .             .
1174  78782 247 -.00009165693   -.001797117   -.002128819
 512  78782 249             0             .             .
 427  78782 251   -.000387954             .             .
 427  82682 241   -.003041332             .             .
 512  82682 241   .0021708792             .             .
 427  82682 245 -.00008214902             .             .
 512  82682 245    .006409175             .             .
 512  82682 246  -.0004251701 -.00008214902   -.001180892
1174  82682 249  -.0021971867             .             .
1174  89984 241   -.010510585             .             .
 427  89984 245  -.0011090117             .             .
1174 106368 241   -.005753424             .             .
 427 106368 249   -.005038429             .             .
 512 106368 249   -.003186969             .             .
1174 108054 241   -.005952678             .             .
 512 108054 245   .0045538875             .             .
 427 108054 246  -.0017588804   .0045538875    .003007815
 512 112207 241   -.013025275             .             .
1174 112207 242  -.0003679808   -.013025275   .0007236264
 427 112207 249   -.001622545             .             .
 512 112207 249 -.00023607176             .             .
1174 112207 249  -.0015294143             .             .
1174 112415 241    -.00622665             .             .
 512 112415 245   .0045538875             .             .
 427 112415 249   -.003885568             .             .
1174 122937 247  -.0002291423             .             .
 427 122937 249   -.003330487             .             .
 427 130592 241    .001545595             .             .
1174 130592 242  -.0007114295    .001545595    -.00382111
 427 130592 245  -.0006982667             .             .
 512 130592 245    .003879238             .             .
1174 157521 249  -.0021541046             .             .
1174 163969 242  -.0006868974             .             .
 427 164367 246  -.0022177189             .             .
 427 186081 247    .002635197             .             .
 512 186081 249   -.008498584             .             .
1174 186081 249  -.0016586606             .             .
 512 193740 249   -.003068933             .             .
 427 196999 249   -.002860803             .             .
1174 196999 249  -.0019817762             .             .
end
format %tq year_q

I think the code needs some changes before we rangejoin again for the second time (or even before that, I am still figuring it out - more complicated than I expected) in order to left out the company that analyst i does not cover in the current quarter but did so in the preceding quarter. Otherwise the last part of rangejoin will only build up on that.

Thanks again for your help. grateful for that

[Clyde Schechter]

OK, I'm sorry I've misunderstood so many times. But perhaps I have it right this time. It's a short addition to the code:

* Example generated by -dataex-. For more info, type help dataex
* Example generated by -dataex-. For more info, type help dataex
clear
input float a_cusip double analys float(year_q fe_own)
1174     70 245  -.0004757255
 512     70 246             0
 427     70 249  -.0015798463
1174   2739 245 -.00050076365
 512   9700 241             0
1174   9700 241   -.006176837
1174   9700 242  -.0005642372
 512   9700 246   .0012755102
 427   9700 251 -.00058193103
 427  24192 245  -.0016429804
 427  24192 246    -.00275303
 427  24192 249   -.002903501
 427  24192 251  -.0005334368
 427  30139 241  -.0030612755
1174  30139 245   .0002729162
 512  30139 246 -.00009920635
 512  30139 249    -.00338763
 427  49483 241    -.00523508
1174  49483 241   -.013449565
 427  53035 241  -.0031310765
1174  53035 242  -.0005642372
 512  53035 245  -.0001686625
 427  53035 246  -.0023209574
 512  53035 246  -.0014880953
 427  53035 249  -.0029590095
 512  53035 249  .00011803588
1174  53035 249   -.001895612
 427  53035 251 -.00024247127
1174  57524 242  -.0007359615
 512  57524 245    .005059875
1174  57524 245   .0018778638
 427  57524 246  -.0022177189
 512  57524 246   .0009920635
1174  57524 247 -.00011457116
1174  57524 249  -.0022618098
1174  71787 245  -.0001752673
 427  71787 249   -.003415884
 427  71787 251 -.00029096552
1174  74192 247   .0007103412
 512  75101 245   -.001686625
 512  75101 246  .00056689343
 427  75101 247   .0004582951
 427  75101 249  -.0039282665
 427  78782 241   -.004636785
 512  78782 241             0
1174  78782 241   -.013150685
1174  78782 242   -.000932218
 427  78782 245  -.0012733098
 427  78782 246   -.001797117
1174  78782 247 -.00009165693
 512  78782 249             0
 427  78782 251   -.000387954
 427  82682 241   -.003041332
 512  82682 241   .0021708792
 427  82682 245 -.00008214902
 512  82682 245    .006409175
 512  82682 246  -.0004251701
1174  82682 249  -.0021971867
1174  89984 241   -.010510585
 427  89984 245  -.0011090117
 427  89984 246   -.001797117
1174 106368 241   -.005753424
1174 106368 242  -.0007359615
 427 106368 249   -.005038429
 512 106368 249   -.003186969
1174 108054 241   -.005952678
1174 108054 242  -.0006868974
 512 108054 245   .0045538875
 427 108054 246  -.0017588804
 512 112207 241   -.013025275
1174 112207 242  -.0003679808
 427 112207 249   -.001622545
 512 112207 249 -.00023607176
1174 112207 249  -.0015294143
1174 112415 241    -.00622665
 512 112415 245   .0045538875
 427 112415 249   -.003885568
1174 122937 247  -.0002291423
 427 122937 249   -.003330487
 427 130592 241    .001545595
1174 130592 242  -.0007114295
 427 130592 245  -.0006982667
 512 130592 245    .003879238
1174 157521 249  -.0021541046
1174 163969 242  -.0006868974
 427 164367 246  -.0022177189
 427 186081 247    .002635197
 512 186081 249   -.008498584
1174 186081 249  -.0016586606
 512 193740 249   -.003068933
 427 196999 249   -.002860803
1174 196999 249  -.0019817762
end
format %tq year_q

tempfile copy
save `copy'

rangejoin year_q -1 -1 using `copy', by(analys)
drop if a_cusip == a_cusip_U
rangestat (count) fe_own, by(analys year_q) interval(a_cusip a_cusip_U a_cusip_U)
drop if missing(fe_own_count)
by a_cusip analys year_q, sort: egen wanted1 = mean(fe_own_U)

preserve
keep a_cusip analys year_q fe_own
rename (a_cusip) =_U
duplicates drop
save `copy', replace
restore

rangejoin year_q -1 -1 using `copy', by(a_cusip_U) suffix("_V")
drop if analys_V == analys

by a_cusip analys year_q, sort: egen wanted2 = mean(fe_own_V)
keep a_cusip analys year_q fe_own wanted1 wanted2
duplicates drop

The added bold face commands check the output of -rangejoin- and remove from them any a_cusip's that the analyst did not cover in the current quarter, even though they did in the preceding one.

[Serena Chan]

Clyde Schechter Thanks again for your help

I guess there are two things we need to further work on:

1)

rangestat (count) fe_own, by(analys year_q) interval(a_cusip a_cusip_U a_cusip_U)

Unfortunately I did anonymize the a_cusip. The original one is a string that includes both numbers and characters so the interval() won't work out.

2)
Notice that I probably created some confusion in my wordings re "leaving out" the company that the analyst did not cover in the current quarter, even though they did in the preceding one.

I intended to make the peer analysts' average forecast for that as a missing value (but not dropping that observation out)

This may better explain what I aim to do:

For example, in the 2nd row for analys #70, wanted2 should be a missing value as analys #70 does not cover #1174 in the current quarter. In other words, as analys #70 does not cover any company other than #521 in the current quarter, wanted2 should contain no value.

The final result should look like this (I did the wanted2 manually just for better illustration):

* Example generated by -dataex-. For more info, type help dataex
clear
input float a_cusip double analys float(year_q fe_own wanted2)
1174     70 245  -.0004757255        .
 512     70 246             0        .
 427     70 249  -.0015798463        .
1174   2739 245 -.00050076365        .
 512   9700 241             0        .
1174   9700 241   -.006176837        .
1174   9700 242  -.0005642372        .
 512   9700 246   .0012755102        .
 427   9700 251 -.00058193103        .
 427  24192 245  -.0016429804        .
 427  24192 246    -.00275303        .
 427  24192 249   -.002903501        .
 427  24192 251  -.0005334368        .
 427  30139 241  -.0030612755        .
1174  30139 245   .0002729162        .
 512  30139 246 -.00009920635        .
 512  30139 249    -.00338763        .
 427  49483 241    -.00523508        .
1174  49483 241   -.013449565        .
 427  53035 241  -.0031310765        .
1174  53035 242  -.0005642372        .
 512  53035 245  -.0001686625        .
 427  53035 246  -.0023209574 .0037949
 512  53035 246  -.0014880953        .
 427  53035 249  -.0029590095        .
 512  53035 249  .00011803588        .
1174  53035 249   -.001895612        .
 427  53035 251 -.00024247127        .
1174  57524 242  -.0007359615        .
 512  57524 245    .005059875        .
1174  57524 245   .0018778638        .
 427  57524 246  -.0022177189 .0029235
 512  57524 246   .0009920635        .
1174  57524 247 -.00011457116        .
1174  57524 249  -.0022618098        .
1174  71787 245  -.0001752673        .
 427  71787 249   -.003415884        .
 427  71787 251 -.00029096552        .
1174  74192 247   .0007103412        .
 512  75101 245   -.001686625        .
 512  75101 246  .00056689343        .
 427  75101 247   .0004582951        .
 427  75101 249  -.0039282665        .
 427  78782 241   -.004636785        .
 512  78782 241             0        .
1174  78782 241   -.013150685        .
1174  78782 242   -.000932218        .
 427  78782 245  -.0012733098        .
 427  78782 246   -.001797117        .
1174  78782 247 -.00009165693        .
 512  78782 249             0        .
 427  78782 251   -.000387954        .
 427  82682 241   -.003041332        .
 512  82682 241   .0021708792        .
 427  82682 245 -.00008214902        .
 512  82682 245    .006409175        .
 512  82682 246  -.0004251701        .
1174  82682 249  -.0021971867        .
1174  89984 241   -.010510585        .
 427  89984 245  -.0011090117        .
 427  89984 246   -.001797117        .
1174 106368 241   -.005753424        .
1174 106368 242  -.0007359615        .
 427 106368 249   -.005038429        .
 512 106368 249   -.003186969        .
1174 108054 241   -.005952678        .
1174 108054 242  -.0006868974        .
 512 108054 245   .0045538875        .
 427 108054 246  -.0017588804        .
 512 112207 241   -.013025275        .
1174 112207 242  -.0003679808        .
 427 112207 249   -.001622545        .
 512 112207 249 -.00023607176        .
1174 112207 249  -.0015294143        .
1174 112415 241    -.00622665        .
 512 112415 245   .0045538875        .
 427 112415 249   -.003885568        .
1174 122937 247  -.0002291423        .
 427 122937 249   -.003330487        .
 427 130592 241    .001545595        .
1174 130592 242  -.0007114295        .
 427 130592 245  -.0006982667        .
 512 130592 245    .003879238        .
1174 157521 249  -.0021541046        .
1174 163969 242  -.0006868974        .
 427 164367 246  -.0022177189        .
 427 186081 247    .002635197        .
 512 186081 249   -.008498584        .
1174 186081 249  -.0016586606        .
 512 193740 249   -.003068933        .
 427 196999 249   -.002860803        .
1174 196999 249  -.0019817762        .
end
format %tq year_q

Almost there. Many thanks

[Serena Chan]

My bad again - the final result should look like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input float a_cusip double analys float(year_q fe_own wanted2)
1174     70 245  -.0004757255        .
 512     70 246             0        .
 427     70 249  -.0015798463        .
1174   2739 245 -.00050076365        .
 512   9700 241             0        .
1174   9700 241   -.006176837        .
1174   9700 242  -.0005642372        .
 512   9700 246   .0012755102        .
 427   9700 251 -.00058193103        .
 427  24192 245  -.0016429804        .
 427  24192 246    -.00275303        .
 427  24192 249   -.002903501        .
 427  24192 251  -.0005334368        .
 427  30139 241  -.0030612755        .
1174  30139 245   .0002729162        .
 512  30139 246 -.00009920635        .
 512  30139 249    -.00338763        .
 427  49483 241    -.00523508        .
1174  49483 241   -.013449565        .
 427  53035 241  -.0031310765        .
1174  53035 242  -.0005642372        .
 512  53035 245  -.0001686625        .
 427  53035 246  -.0023209574 .0037949
 512  53035 246  -.0014880953 -0.00096
 427  53035 249  -.0029590095        .
 512  53035 249  .00011803588        .
1174  53035 249   -.001895612        .
 427  53035 251 -.00024247127        .
1174  57524 242  -.0007359615        .
 512  57524 245    .005059875        .
1174  57524 245   .0018778638        .
 427  57524 246  -.0022177189 .0029235
 512  57524 246   .0009920635 -0.00096
1174  57524 247 -.00011457116        .
1174  57524 249  -.0022618098        .
1174  71787 245  -.0001752673        .
 427  71787 249   -.003415884        .
 427  71787 251 -.00029096552        .
1174  74192 247   .0007103412        .
 512  75101 245   -.001686625        .
 512  75101 246  .00056689343        .
 427  75101 247   .0004582951        .
 427  75101 249  -.0039282665        .
 427  78782 241   -.004636785        .
 512  78782 241             0        .
1174  78782 241   -.013150685        .
1174  78782 242   -.000932218        .
 427  78782 245  -.0012733098        .
 427  78782 246   -.001797117        .
1174  78782 247 -.00009165693        .
 512  78782 249             0        .
 427  78782 251   -.000387954        .
 427  82682 241   -.003041332        .
 512  82682 241   .0021708792        .
 427  82682 245 -.00008214902        .
 512  82682 245    .006409175        .
 512  82682 246  -.0004251701        .
1174  82682 249  -.0021971867        .
1174  89984 241   -.010510585        .
 427  89984 245  -.0011090117        .
 427  89984 246   -.001797117        .
1174 106368 241   -.005753424        .
1174 106368 242  -.0007359615        .
 427 106368 249   -.005038429        .
 512 106368 249   -.003186969        .
1174 108054 241   -.005952678        .
1174 108054 242  -.0006868974        .
 512 108054 245   .0045538875        .
 427 108054 246  -.0017588804        .
 512 112207 241   -.013025275        .
1174 112207 242  -.0003679808        .
 427 112207 249   -.001622545        .
 512 112207 249 -.00023607176        .
1174 112207 249  -.0015294143        .
1174 112415 241    -.00622665        .
 512 112415 245   .0045538875        .
 427 112415 249   -.003885568        .
1174 122937 247  -.0002291423        .
 427 122937 249   -.003330487        .
 427 130592 241    .001545595        .
1174 130592 242  -.0007114295        .
 427 130592 245  -.0006982667        .
 512 130592 245    .003879238        .
1174 157521 249  -.0021541046        .
1174 163969 242  -.0006868974        .
 427 164367 246  -.0022177189        .
 427 186081 247    .002635197        .
 512 186081 249   -.008498584        .
1174 186081 249  -.0016586606        .
 512 193740 249   -.003068933        .
 427 196999 249   -.002860803        .
1174 196999 249  -.0019817762        .
end
format %tq year_q

[Clyde Schechter]

I'm sorry, I'm now totally confused again. I really have no understanding of what you want for either wanted1 or wanted2. I do not understand how you arrived at the results you are showing.

[Serena Chan]

Clyde Schechter Sorry for the confusion - I'll try to explain clearly this time. So let's take a step back and ignore what I typed in #9, #11 and #12.


What I am now trying to figure out is to generate a variable called fe_other (or whatever we name it), which is the peer analysts' average forecast error (in preceding quarter) on the other firms that a given analyst follows in current quarter.

So again if analyst i follows firms j, k, and l in quarter t, fe_other is calculated as peer analysts’ average forecast error on firms k and l (not j the focal firm) during quarter t‒1.


Let's stick to the following data set as usual and let's take a few examples.

For the first row - analys #70 who follows company #1174 in 2021q2. Apparently, this analyst does not follow other company in the same quarter. So there is no peer average forecast error (in t-1) on other firm that this analyst follows in current quarter (as he only follows one firm in current quarter!). fe_other for this observation will therefore be a missing value (i.e. '' . '')

For the second row - analys #70 who follows company #512 in 2021q3. Again, this analyst only follows one firm this quarter. fe_other will be a missing value.

For the third row - analys #70 who follows company #427 in 2022q2. Again, this analyst does not follow firm other than #427 in current quarter. fe_other will be a missing value.

Yes - sounds like lots of missing values. But it will be fine with my complete data set which has > 1 million observations.

Let's keep going with this data set:

Finally when we move to row 23 - analys #53035 who follows company #427 in 2021q3. We could see that this analyst also follows another company #512 in the same quarter. Great, that's what we love to see. Now, we have to figure out peer analysts' (aka analysts other than #53035) average forecast error on company #512 in 2021q2, i.e. previous quarter. With my own manual calculation, the fe_other for this would be 0.003795.

Next, when we move to row 24 - analys #53035 who follows company #512 in 2021q3. Apparently from the above we know this analyst follows #427 in 2021q3 as well. We can then also calculate fe_other for this observation.

and the process goes on for the remaining observations.

* Example generated by -dataex-. For more info, type help dataex
clear
input float a_cusip double analys float(year_q fe_own)
1174     70 245  -.0004757255
 512     70 246             0
 427     70 249  -.0015798463
1174   2739 245 -.00050076365
 512   9700 241             0
1174   9700 241   -.006176837
1174   9700 242  -.0005642372
 512   9700 246   .0012755102
 427   9700 251 -.00058193103
 427  24192 245  -.0016429804
 427  24192 246    -.00275303
 427  24192 249   -.002903501
 427  24192 251  -.0005334368
 427  30139 241  -.0030612755
1174  30139 245   .0002729162
 512  30139 246 -.00009920635
 512  30139 249    -.00338763
 427  49483 241    -.00523508
1174  49483 241   -.013449565
 427  53035 241  -.0031310765
1174  53035 242  -.0005642372
 512  53035 245  -.0001686625
 427  53035 246  -.0023209574
 512  53035 246  -.0014880953
 427  53035 249  -.0029590095
 512  53035 249  .00011803588
1174  53035 249   -.001895612
 427  53035 251 -.00024247127
1174  57524 242  -.0007359615
 512  57524 245    .005059875
1174  57524 245   .0018778638
 427  57524 246  -.0022177189
 512  57524 246   .0009920635
1174  57524 247 -.00011457116
1174  57524 249  -.0022618098
1174  71787 245  -.0001752673
 427  71787 249   -.003415884
 427  71787 251 -.00029096552
1174  74192 247   .0007103412
 512  75101 245   -.001686625
 512  75101 246  .00056689343
 427  75101 247   .0004582951
 427  75101 249  -.0039282665
 427  78782 241   -.004636785
 512  78782 241             0
1174  78782 241   -.013150685
1174  78782 242   -.000932218
 427  78782 245  -.0012733098
 427  78782 246   -.001797117
1174  78782 247 -.00009165693
 512  78782 249             0
 427  78782 251   -.000387954
 427  82682 241   -.003041332
 512  82682 241   .0021708792
 427  82682 245 -.00008214902
 512  82682 245    .006409175
 512  82682 246  -.0004251701
1174  82682 249  -.0021971867
1174  89984 241   -.010510585
 427  89984 245  -.0011090117
 427  89984 246   -.001797117
1174 106368 241   -.005753424
1174 106368 242  -.0007359615
 427 106368 249   -.005038429
 512 106368 249   -.003186969
1174 108054 241   -.005952678
1174 108054 242  -.0006868974
 512 108054 245   .0045538875
 427 108054 246  -.0017588804
 512 112207 241   -.013025275
1174 112207 242  -.0003679808
 427 112207 249   -.001622545
 512 112207 249 -.00023607176
1174 112207 249  -.0015294143
1174 112415 241    -.00622665
 512 112415 245   .0045538875
 427 112415 249   -.003885568
1174 122937 247  -.0002291423
 427 122937 249   -.003330487
 427 130592 241    .001545595
1174 130592 242  -.0007114295
 427 130592 245  -.0006982667
 512 130592 245    .003879238
1174 157521 249  -.0021541046
1174 163969 242  -.0006868974
 427 164367 246  -.0022177189
 427 186081 247    .002635197
 512 186081 249   -.008498584
1174 186081 249  -.0016586606
 512 193740 249   -.003068933
 427 196999 249   -.002860803
1174 196999 249  -.0019817762
end
format %tq year_q

The code you last typed on #10 looks great:

rangejoin year_q -1 -1 using `copy', by(analys)
drop if a_cusip == a_cusip_U
rangestat (count) fe_own, by(analys year_q) interval(a_cusip a_cusip_U a_cusip_U)
drop if missing(fe_own_count)
by a_cusip analys year_q, sort: egen wanted1 = mean(fe_own_U)

preserve
keep a_cusip analys year_q fe_own
rename (a_cusip) =_U
duplicates drop
save `copy', replace
restore

rangejoin year_q -1 -1 using `copy', by(a_cusip_U) suffix("_V")
drop if analys_V == analys

by a_cusip analys year_q, sort: egen wanted2 = mean(fe_own_V)
keep a_cusip analys year_q fe_own wanted1 wanted2
duplicates drop

But the thing is that, firstly, I have been anonymizing a_cusip (by using the egen group() function from https://www.statalist.org/forums/for...-for-statalist)
The original a_cusip in my data is a string that includes both numbers and characters
So for the code rangestat (count) fe_own, by(analys year_q) interval(a_cusip a_cusip_U a_cusip_U) , the interval() part won't work out as neither a_cusip nor a_cusip_U is a numeric value.

Second, for the code drop if missing(fe_own_count) , it will drop the observations while we should not. ( I mean if they are like duplicate values surely we should drop them, but in this step they are not)


I attached an excel file which shows how I wish my results look like - it by no means is the input or code that we should base on, it's like the final product if our code works perfectly. I eyeballed the data and calculated the fe_other manually. Again, it has nothing to deal with the code we have and it is just like the solution that I could refer to when I am checking on my codes.

To give you a better understanding on why I'd hope (or need) to work on this code is that my assigned topic is to examine whether analyst forecasts are also influenced by peers who follow other firms in the coverage portfolio of the analyst. Just like if I am an equity analyst who does earning forecast for Apple, Facebook and Netflix earnings, I may also want to know other analysts' forecast errors on Facebook and Netflix in the previous quarter so I may learn from their mistakes and adjust my forecast for apple in current quarter accordingly which may result in a better forecast accuracy.

Long story short. I am sorry that I created confusion at some point. The code you helped with is really great just I guess we need to fine tune for a final bit. Thanks again for your patience and help.

[Clyde Schechter]

For the first row - analys #70 who follows company #1174 in 2021q2. Apparently, this analyst does not follow other company in the same quarter. So there is no peer average forecast error (in t-1) on other firm that this analyst follows in current quarter (as he only follows one firm in current quarter!). fe_other for this observation will therefore be a missing value (i.e. '' . '')

When you say this analyst does not follow other companies in the same quarter, which quarter are you referring to? 2021q2? 2021q1 (which would be t-1)?

For the second row - analys #70 who follows company #512 in 2021q3. Again, this analyst only follows one firm this quarter. fe_other will be a missing value.

Yes, but in t-1, q2021q2, he/she does follow another firm, namely 1174. So why isn't the result the value of fe_own in 2021q2, namely -.0004757?

For the third row - analys #70 who follows company #427 in 2022q2. Again, this analyst does not follow firm other than #427 in current quarter. fe_other will be a missing value.

This is different because now t-1 is 2022q1, and analys #70 has followed no firms at all in 2022q1. So I understand why fe_other should be missing here. But I don't get it for the others.

Finally when we move to row 23 - analys #53035 who follows company #427 in 2021q3. We could see that this analyst also follows another company #512 in the same quarter. Great, that's what we love to see. Now, we have to figure out peer analysts' (aka analysts other than #53035) average forecast error on company #512 in 2021q2, i.e. previous quarter. With my own manual calculation, the fe_other for this would be 0.003795.

Apparently it is important that this analyst follows more than one company in 2021q3. But I don't understand why that is relevant for that observation: what I thought should matter is which companies the analyst followed in t-1, i.e., in 2021q2.

The original a_cusip in my data is a string that includes both numbers and characters
So for the code rangestat (count) fe_own, by(analys year_q) interval(a_cusip a_cusip_U a_cusip_U) , the interval() part won't work out as neither a_cusip nor a_cusip_U is a numeric value.

Well, this part has an easy fix. The fix, actually, is precisely your anonymization. Instead of replacing the real cusip with the anonymized version, keep both of them in the data set, and wherever the code says a_cusip (or a_cusip_*) replace a_cusip by the anonymized variable, which is numeric. Since the anonymized cusip is in 1:1 correspondence with the real one, and since what is being tested in the -interval()- option involving it is just equality, it will work just fine.

Second, for the code drop if missing(fe_own_count) , it will drop the observations while we should not. ( I mean if they are like duplicate values surely we should drop them, but in this step they are not)

If all that is wrong is that this is causing observations to drop that need to be retained, then you can just replace the two lines

drop if missing(fe_own_count)
by a_cusip analys year_q, sort: egen wanted1 = mean(fe_own_U)

with just the following one line:

by a_cusip analys year_q, sort: egen wanted1 = mean(cond(!missing(fe_own_count), fe_own_U, .))

That will retain those observations but exclude them from the calculation of the mean. But this change does not seem to address any of the other issues, and when I tried it, it did not replicate the results you showed in #12.

Note: I have a standing policy, not just on Statalist, of never downloading files from people I do not know. So I have not looked at your attachment in #14. If this modification of the code and the fix for the cusip being non-numeric, do produce the results you want, then I am happy that is the case, although, in all honesty, I don't quite get how they do. If so, it is one of those situations where it is better to be lucky than smart.