Calculations of quarterly stock holding data by shareholder

[Cong Gu]

Dear Statalists,

I am dealing with an unbalanced panel data for calculating the share holdings by a particular investing company. As shown from the table below, I would like to calculate the quarterly holding changes by investing company (id=1001) for each portfolio firm (e.g., a, b, c, d, e), and then use the difference times the stock price of each portfolio firm in the incumbent quarter.

year	quarter	investment company	portfolio firm	number of shares	stock price of each holding firm
1996	1	1001	a	1000	3.33
1996	1	1001	b	1200	3.33
1996	1	1001	c	1300	3.33
1996	1	1001	d	1100	3.33
1996	1	1001	e	1050	3.33
1996	2	1001	a	1100	4.44
1996	2	1001	b	1100	4.44
1996	2	1001	c	1200	4.44
1996	2	1001	d	1400	4.44
1996	2	1001	e	0	4.44
1996	3	1001	a	900	5.55
1996	3	1001	b	1300	5.55
1996	3	1001	c	1200	5.55
1996	3	1001	d	1400	5.55
1996	4	1001	a	1200	6.66
1996	4	1001	b	1030	6.66
1996	4	1001	c	1000	6.66
1996	4	1001	d	1409	6.66
1997	1	1001	a	2000	7.77
1997	1	1001	b	1700	7.77
1997	1	1001	c	1344	7.77
1997	1	1001	d	1278	7.77
1997	2	1001	a	1900	8.88
1997	2	1001	b	2000	8.88
1997	2	1001	c	1300	8.88
1997	2	1001	d	700	8.88

For example, for portfolio firm a, firstly, I need to calculate the difference in holding shares by investing firm 1001 between the first and second quarter in 1996, which is 1100-1000=100, secondly, I will use such difference times the stock price of portfolio firm a in the second quarter of 1996, which is 100*4.44. This is just for one firm, while the same process should be performed for other portfolio firms. Similarly, for portfolio firm b, the equation should be (1100-1200)*4.4...

In general, the calculation equation could be:

(N_{j, i, t} - N_{j, i, t-1})* P_j,t, where N_j,i,t is the number of shares holding by investing firm i in portfolio firm j in quarter t, P_{j, t} is the stock price for portfolio firm j in quarter t.

It would be much appreciated if some one can show me the stata codes in dealing with such issue.

Best,
Cong

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year byte quarter int investment_company str1 portfolio_firm int n_shares float stock_price
1996 1 1001 "a" 1000 3.33
1996 1 1001 "b" 1200 3.33
1996 1 1001 "c" 1300 3.33
1996 1 1001 "d" 1100 3.33
1996 1 1001 "e" 1050 3.33
1996 2 1001 "a" 1100 4.44
1996 2 1001 "b" 1100 4.44
1996 2 1001 "c" 1200 4.44
1996 2 1001 "d" 1400 4.44
1996 2 1001 "e"    0 4.44
1996 3 1001 "a"  900 5.55
1996 3 1001 "b" 1300 5.55
1996 3 1001 "c" 1200 5.55
1996 3 1001 "d" 1400 5.55
1996 4 1001 "a" 1200 6.66
1996 4 1001 "b" 1030 6.66
1996 4 1001 "c" 1000 6.66
1996 4 1001 "d" 1409 6.66
1997 1 1001 "a" 2000 7.77
1997 1 1001 "b" 1700 7.77
1997 1 1001 "c" 1344 7.77
1997 1 1001 "d" 1278 7.77
1997 2 1001 "a" 1900 8.88
1997 2 1001 "b" 2000 8.88
1997 2 1001 "c" 1300 8.88
1997 2 1001 "d"  700 8.88
end

gen qdate = yq(year, quarter)
format qdate %tq

egen pair = group(investment_company portfolio_firm)
xtset pair qdate, quarterly

by pair (qdate), sort: gen wanted = (n_shares - L1.n_shares)*stock_price

Note: your data example is not from a real Stata data set because your column headers are not legal variable names in Stata. So I used the numbers in the tableau to create a Stata data set and then extracted it with the -dataex- command to include here in the code.

In the future, when showing data examples, please use the -dataex- command to do so, as I have in this response. If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Cong Gu]

Dear Clyde, many thanks for that and sorry for the late reply.