monthly variable OVER the current/previous fiscal year period

[Max he]

Hi Statalisters,

I have the following problem in my thesis, which has been stumping me for weeks and I just can't get my head around it and all my attempts at solutions produce nonsense.

First of all, here is the variable definition:
"Detrended turnover is the average monthly share turnover over the current fiscal-year period minus the average monthly share turnover over the previous fiscal-year period, where monthly share turnover is calculated as the monthly trading volume divided by the total number of shares outstanding during the month"


My problem is, that i cant figure out how to calculate the "the average monthly share turnover over the current fiscal-year period minus the average monthly share turnover over the previous fiscal-year period"

What I have done so far:
My starting position are daily stock price data.
First I generate my monthly variable (monthly_date) and calculate the first part of the variable:

"where monthly share turnover is calculated as the monthly trading volume divided by the total number of shares outstanding during the month"

*Monthly variable  
gen monthly_date = mofd(ndate)
format monthly_date %tm

*Monthly SHROUT

bys PERMCO monthly_date: egen total_month_SHROUT = total(SHROUT) 

*Monthly VOL 

bys PERMCO monthly_date: egen total_month_VOL = total(VOL) 

*Monthly Turnover 
 
by PERMCO monthly_date: gen total_month_turnover = total_month_VOL / total_month_SHROUT

In the next step i calculate the yearly turnover from the monthly turnover:

sort PERMCO fyear
bys PERMCO fyear: egen year_total_turnover = mean(total_month_turnover)

in my code, i am now starting to calculate many more variables on a weekly basis and to break the data set down to a weekly basis as well.

Then I come back to the DTURN variable when I want to bring the data set to a yearly basis and do the following:

*Panelstructure to fyear basis 

sort PERMCO fyear 
quietly by PERMCO fyear:  gen dup = cond(_N==1,0,_n)
drop if dup > 1
drop dup

duplicates tag PERMCO fyear, gen(isdup3)


tsset PERMCO fyear 
sort PERMCO fyear 

gen DTURN =  year_total_turnover - l1.year_total_turnover

My supervisor gave me the following tipp: "DTURN is the average monthly share turnover OVER the current fiscal-year period minus the average monthly share turnover OVER the previous fiscal-year period, where monthly share turnover is calculated as the TOTAL monthly trading volume divided by the TOTAL number of shares outstanding during the month"


I have no idea how to bring a month variable (which I created above) OVER a fiscal year. It may even be so simple that the answer will be very embarrassing for me. Which would explain why I haven't found anything about it.

The goal of the variable is that at the end it is also a year variable, which means that my entire dataset amounts to having only year variables in order to do regressions etc. with them.
All the other variables are already formed and look great in my regressions and summary statistics, but this one just produces garbage.

In other papers the variable has approximately these statistics:

DTURN	mean	median	std. dev	25 pct	75 pct
Paper	0.000	−0.001	0.147	−0.024	0.022
My	-0,259	-0,069	23,229	-1,616	1,346

I would be very happy to receive help!

i have given you a small dataex excerpt, but i don't know how helpful it is for you, because its daily obs from one company. Please let me know if you need anything else:

* Example generated by -dataex-. For more info, type help dataex
clear
input long PERMCO int(ndate fyear) long(SHROUT VOL)
7 14248 1999 136417  8609040
7 14249 1999 136417 12848150
7 14250 1999 136417 12130355
7 14251 1999 136417 13100998
7 14252 1999 136417  6112367
7 14255 1999 136417  5746493
7 14256 1999 136417  7359291
7 14257 1999 136417  9456051
7 14258 1999 136417 15470629
7 14259 1999 136417  9029496
7 14263 1999 136417  4906867
7 14264 1999 136417  6988940
7 14265 1999 136417  5395374
7 14266 1999 136417  3144092
7 14269 1999 136417  3510150
7 14270 1999 136417  5026812
7 14271 1999 136417  3277342
7 14272 1999 136417  3074963
7 14273 1999 136417  2236727
7 14276 1999 136417  2523407
7 14277 1999 136417  2781568
7 14278 1999 136417  3046553
7 14279 1999 136417  4174222
7 14280 1999 136417  6981017
7 14283 1999 136417  4202275
7 14284 1999 136417  6471637
7 14285 1999 136417  5058010
7 14286 1999 136417  5058341
7 14287 1999 136417  3861449
7 14291 1999 136417  2719680
7 14292 1999 136417  2660281
7 14293 1999 136417  4479763
7 14294 1999 136417  3247528
7 14297 1999 136417  2691077
7 14298 1999 136417  2898386
7 14299 1999 136417  1954202
7 14300 1999 136417  2391228
7 14301 1999 136417  5992166
7 14304 1999 136417  4375858
7 14305 1999 136417  6124295
7 14306 1999 136417  2636912
7 14307 1999 136417  3299416
7 14308 1999 136417  4226999
7 14311 1999 136417  4934420
7 14312 1999 136417  3434356
7 14313 1999 136417  4919140
7 14314 1999 136417  4288920
7 14315 1999 136417  2448554
7 14318 1999 136417  3162048
7 14319 1999 136417  3585561
7 14320 1999 136417  3329757
7 14321 1999 136417  2044962
7 14322 1999 136417  4898723
7 14325 1999 136417  5319341
7 14326 1999 136417  3726468
7 14327 1999 136417  3590602
7 14328 1999 136417  3583978
7 14329 1999 136417  2290304
7 14332 1999 136417  5095627
7 14333 1999 136417  4970661
7 14334 1999 136417  3819204
7 14335 1999 136417  2349939
7 14339 1999 136417  4132131
7 14340 1999 136417  5650433
7 14341 1999 136417  3711833
7 14342 1999 136417  2687835
7 14343 1999 136417  2410584
7 14346 1999 136417  3572590
7 14347 1999 136417  3700722
7 14348 1999 136417  6344851
7 14349 1999 136417 15657774
7 14350 1999 136417  4514779
7 14353 1999 136417  8258383
7 14354 1999 136417  4700656
7 14355 1999 136417  3168984
7 14356 1999 136417  6625871
7 14357 1999 136417  9345339
7 14360 1999 136417  8331329
7 14361 1999 136417 18953596
7 14362 1999 136417  8579367
7 14363 1999 136417  8190039
7 14364 1999 136417 12162763
7 14367 1999 136417 13257218
7 14368 1999 136417  7301197
7 14369 1999 136417  5216705
7 14370 1999 136417  3899170
7 14371 1999 136417  3962573
7 14374 1999 136417  3526181
7 14375 1999 136417  4111597
7 14376 1999 136417  3551756
7 14377 1999 136417  2653275
7 14378 1999 136417  2047481
7 14381 1999 136417  1892459
7 14382 1999 136417  3754769
7 14383 1999 136417  2680097
7 14384 1999 136417  3757595
7 14385 1999 136417  4209878
7 14388 1999 136417  2353038
7 14389 1999 136417  3351723
7 14390 1999 136417  3998498
end
format %tdDD.NN.CCYY ndate

[George Ford]

a thought. rather than dup, which here is deleting everything but one observation:

collapse (first) total_month_turnover, by(PERMCO monthly_date)

would get you the monthly figures (they are means and all the same by month, so first will do). you'd need to xtset and then compute the lags.

[Max he]

a thought. rather than dup, which here is deleting everything but one observation:

collapse (first) total_month_turnover, by(PERMCO monthly_date)

would get you the monthly figures (they are means and all the same by month, so first will do). you'd need to xtset and then compute the lags.

Hi George, thanks for your help! I'm afraid I don't quite understand the difference to my approach.
It is true that the values of my variables (total_month... and total_year....) are on a monthly or yearly basis and are therefore the same for all observations for each PERMCO and month/year (since I still have daily observations at this point).

However, if I change the data set to a yearly basis using my dup drop or your collapse approach, this does not change the calculation itself, does it?

Because on a fyear basis, I bring the data set into a panel structure and then no longer have any duplicate values whether I use your collapse or my dup approach is not soooo relevant. The only important thing is that one observation per company per year remains. And that happens, because otherwise I couldn't use tsset and calculate the lag.

I have the feeling that I have not understood exactly what you mean with your help and I think that I have a huge mistake in my calculation, how I read and calculate the variable.

[Max he]

After a long search, I was able to find the banal error in my procedure. I looked at the variable definition at the data provider again and it says in the small print that it is specified in thousands. Consequently, I only had to adjust it by the factor and was now able to achieve the desired results.

[George Ford]

Make sure the lags work as intended. Stata is literal in its interpretation. l1.x is on day lag, not one-year lag.