Hi all,
First of all, thank you for help in advance. I'm trying to calculate the churn rate (CR) and investor turnover (IT) portfolio of my panel data (xtset in latest version of Stata). I'm struggling to do this, as i don't know how to work with 2 identifiers (Management ID and company CUSIP) and how i can approach this with the 'lag' in my formula. I'm working with panel data of 15 years (per quarter) with multiple investment managers (just 1 in sample below) and public firms. The Churn rate is calculated as such: Array

Where Qjt represent the set of firms held by investor j in quarter t. Subsequently, P and N denote the Shareprice and the Sharesheld, respectively, of company held by institutional investor at quarter.Subsequently, the churn rates are averaged with the preceding three quarters for each investor to create a measure of investor turnover at firm level: Array

Where W represents the weight of investor j in the total fraction held by institutional investors in firm i at quarter t, and S represents the set of investors in firm i at quarter t . To finish, to match the quarterly t with firm-year observations, IT is added from the final quarter of the book year, which holds the average of quarterly churn rates for that year.

Anyone who can help me with this? Thanks!



Code:
FileDate_2    ManagerName    ManagerNumber    Cusip    SHARESHELD    StockName    SHAREPRICE    sharesoutstanding
31mar2003    A R ASSET MANAGEMENT, INC.    110    11012210    90684    BRISTOL MYERS SQUIBB CO    21.13    1.94e+09
31mar2003    A R ASSET MANAGEMENT, INC.    110    47816010    36244    JOHNSON & JOHNSON    57.87    2.97e+09
31mar2003    A R ASSET MANAGEMENT, INC.    110    19121610    1108050    COCA COLA CO    40.48    2.47e+09
31mar2003    A R ASSET MANAGEMENT, INC.    110    45814010    79070    INTEL CORP    16.28    6.54e+09
31mar2003    A R ASSET MANAGEMENT, INC.    110    34537086    70712    FORD MTR CO DEL    7.52    1.76e+09
31mar2003    A R ASSET MANAGEMENT, INC.    110    93114210    15500    WAL MART STORES INC    52.03    4.41e+09
31mar2003    A R ASSET MANAGEMENT, INC.    110    71344810    51600    PEPSICO INC    40    1.72e+09
31mar2003    A R ASSET MANAGEMENT, INC.    110    98956P10    9016    ZIMMER HLDGS INC    48.63    1.96e+08
31mar2003    A R ASSET MANAGEMENT, INC.    110    45920010    86608    INTERNATIONAL BUSINESS MACHS    78.43    1.81e+09
31mar2003    A R ASSET MANAGEMENT, INC.    110    59491810    26180    MICROSOFT CORP    24.21    1.07e+10
30jun2003    A R ASSET MANAGEMENT, INC.    110    45920010    85508    INTERNATIONAL BUSINESS MACHS    82.5    1.81e+09
30jun2003    A R ASSET MANAGEMENT, INC.    110    53245710    3000    LILLY ELI & CO    68.97    1.12e+09
30jun2003    A R ASSET MANAGEMENT, INC.    110    71344810    41600    PEPSICO INC    44.5    1.72e+09
30jun2003    A R ASSET MANAGEMENT, INC.    110    93114210    15500    WAL MART STORES INC    53.67    4.38e+09
30jun2003    A R ASSET MANAGEMENT, INC.    110    34537086    10227    FORD MTR CO DEL    10.99    1.76e+09
30jun2003    A R ASSET MANAGEMENT, INC.    110    19121610    1104670    COCA COLA CO    46.41    2.46e+09
30jun2003    A R ASSET MANAGEMENT, INC.    110    98956P10    9016    ZIMMER HLDGS INC    45.05    1.97e+08
30jun2003    A R ASSET MANAGEMENT, INC.    110    47816010    36244    JOHNSON & JOHNSON    51.7    2.97e+09
30jun2003    A R ASSET MANAGEMENT, INC.    110    45814010    79950    INTEL CORP    20.81    6.53e+09
30sep2003    A R ASSET MANAGEMENT, INC.    110    45814010    79950    INTEL CORP    27.52    6.51e+09