Average Trading volume 30 days stock

[jesse brandon]

Hi all,

I want to calculate the average trading volume (30 days) for each trading day per stock. I have the variables date, ticker, volume (per day per company). The problem is that i cant use rolling, because i have multiple dates that are the same due to the fact that each company was trading that day of course. I hope someone can help me with my problem. below you will find a sneak peak of how my data looks like. Logical would be that the first 29 days dont have an average, because those are the days that the first average volume is being calculated with.

I also tried this, but this gives the same value for all the observations:
sort TICKER date
by TICKER: gen avg_volume = VOL[_n - 29] / 30 if _n >= 30
by TICKER: replace avg_volume = sum(VOL[_n - 29])/30 if _n >= 30

[Nick Cox]

rangestat from SSC can help here.

rangestat VOL, by(TICKER) int(date -29 0)

would give you the average over the previous 29 days and the present day.

It takes your date variable literally and so doesn't do anything to adjust for weekends or other non-trading days.

You might want to monitor e.g. the range of dates in each window at the same time.

rangestat is a key word to search the forum for other examples.

[jesse brandon]

Thank Nick, I think you helped me further with my problem

[jesse brandon]

rangestat from SSC can help here.

rangestat VOL, by(TICKER) int(date -29 0)

would give you the average over the previous 29 days and the present day.

It takes your date variable literally and so doesn't do anything to adjust for weekends or other non-trading days.

You might want to monitor e.g. the range of dates in each window at the same time.

rangestat is a key word to search the forum for other examples.

How would you write it in stata to not use the first 29 trading days of one ticker? because your code just averages as well even tho there are not enough observations

[Nick Cox]

I wouldn't do that. As said, I would just monitor the range of dates included in each calculation.

Somewhere in the forum is code to calculate the number of distinct dates in each window.

I can't illustrate with your data because you showed a screenshot.

[Nick Cox]

Here is an example simple enough to invent but I hope realistic enough to show the main idea.

I have created a dataset without weekends, but without adjusting for public holidays anywhere.

rangerun is just a cousin of rangestat on SSC.

By construction here the number of distinct daily dates is exactly the same as the number of observations in a window, but that wouldn't be true if there were multiple observations for any stock on any date.

It is open to any user to ignore windows without sufficient observations according to some criterion but easier to do that after the fact. If you try to specify if you make observations unavailable to more calculations than you want to make. (Try it to see this!)

clear
set obs 81 
gen date = mdy(12,31,2023) + _n 
format date %td 
keep if inrange(dow(date), 1, 5)
gen ticker = 1 

gen y = _n 

rangestat (count) count=date (min) min=date (max) max=date (mean) y, by(ticker) int(date -29 0)

program my_distinct 
    gen ndistinct = sum(date != date[_n-1])
    replace ndistinct = ndistinct[_N]
end 

rangerun my_distinct, use(date) by(ticker) int(date -29 0)

list in 1/40 

     +---------------------------------------------------------------------+
     |      date   ticker    y   count     min     max   y_mean   ndisti~t |
     |---------------------------------------------------------------------|
  1. | 01jan2024        1    1       1   23376   23376        1          1 |
  2. | 02jan2024        1    2       2   23376   23377      1.5          2 |
  3. | 03jan2024        1    3       3   23376   23378        2          3 |
  4. | 04jan2024        1    4       4   23376   23379      2.5          4 |
  5. | 05jan2024        1    5       5   23376   23380        3          5 |
     |---------------------------------------------------------------------|
  6. | 08jan2024        1    6       6   23376   23383      3.5          6 |
  7. | 09jan2024        1    7       7   23376   23384        4          7 |
  8. | 10jan2024        1    8       8   23376   23385      4.5          8 |
  9. | 11jan2024        1    9       9   23376   23386        5          9 |
 10. | 12jan2024        1   10      10   23376   23387      5.5         10 |
     |---------------------------------------------------------------------|
 11. | 15jan2024        1   11      11   23376   23390        6         11 |
 12. | 16jan2024        1   12      12   23376   23391      6.5         12 |
 13. | 17jan2024        1   13      13   23376   23392        7         13 |
 14. | 18jan2024        1   14      14   23376   23393      7.5         14 |
 15. | 19jan2024        1   15      15   23376   23394        8         15 |
     |---------------------------------------------------------------------|
 16. | 22jan2024        1   16      16   23376   23397      8.5         16 |
 17. | 23jan2024        1   17      17   23376   23398        9         17 |
 18. | 24jan2024        1   18      18   23376   23399      9.5         18 |
 19. | 25jan2024        1   19      19   23376   23400       10         19 |
 20. | 26jan2024        1   20      20   23376   23401     10.5         20 |
     |---------------------------------------------------------------------|
 21. | 29jan2024        1   21      21   23376   23404       11         21 |
 22. | 30jan2024        1   22      22   23376   23405     11.5         22 |
 23. | 31jan2024        1   23      22   23377   23406     12.5         22 |
 24. | 01feb2024        1   24      22   23378   23407     13.5         22 |
 25. | 02feb2024        1   25      22   23379   23408     14.5         22 |
     |---------------------------------------------------------------------|
 26. | 05feb2024        1   26      21   23383   23411       16         21 |
 27. | 06feb2024        1   27      22   23383   23412     16.5         22 |
 28. | 07feb2024        1   28      22   23384   23413     17.5         22 |
 29. | 08feb2024        1   29      22   23385   23414     18.5         22 |
 30. | 09feb2024        1   30      22   23386   23415     19.5         22 |
     |---------------------------------------------------------------------|
 31. | 12feb2024        1   31      21   23390   23418       21         21 |
 32. | 13feb2024        1   32      22   23390   23419     21.5         22 |
 33. | 14feb2024        1   33      22   23391   23420     22.5         22 |
 34. | 15feb2024        1   34      22   23392   23421     23.5         22 |
 35. | 16feb2024        1   35      22   23393   23422     24.5         22 |
     |---------------------------------------------------------------------|
 36. | 19feb2024        1   36      21   23397   23425       26         21 |
 37. | 20feb2024        1   37      22   23397   23426     26.5         22 |
 38. | 21feb2024        1   38      22   23398   23427     27.5         22 |
 39. | 22feb2024        1   39      22   23399   23428     28.5         22 |
 40. | 23feb2024        1   40      22   23400   23429     29.5         22 |
     +---------------------------------------------------------------------+

[jesse brandon]

Here is an example simple enough to invent but I hope realistic enough to show the main idea.

I have created a dataset without weekends, but without adjusting for public holidays anywhere.

rangerun is just a cousin of rangestat on SSC.

By construction here the number of distinct daily dates is exactly the same as the number of observations in a window, but that wouldn't be true if there were multiple observations for any stock on any date.

It is open to any user to ignore windows without sufficient observations according to some criterion but easier to do that after the fact. If you try to specify if you make observations unavailable to more calculations than you want to make. (Try it to see this!)

clear
set obs 81
gen date = mdy(12,31,2023) + _n
format date %td
keep if inrange(dow(date), 1, 5)
gen ticker = 1

gen y = _n

rangestat (count) count=date (min) min=date (max) max=date (mean) y, by(ticker) int(date -29 0)

program my_distinct
gen ndistinct = sum(date != date[_n-1])
replace ndistinct = ndistinct[_N]
end

rangerun my_distinct, use(date) by(ticker) int(date -29 0)

list in 1/40

+---------------------------------------------------------------------+
| date ticker y count min max y_mean ndisti~t |
|---------------------------------------------------------------------|
1. | 01jan2024 1 1 1 23376 23376 1 1 |
2. | 02jan2024 1 2 2 23376 23377 1.5 2 |
3. | 03jan2024 1 3 3 23376 23378 2 3 |
4. | 04jan2024 1 4 4 23376 23379 2.5 4 |
5. | 05jan2024 1 5 5 23376 23380 3 5 |
|---------------------------------------------------------------------|
6. | 08jan2024 1 6 6 23376 23383 3.5 6 |
7. | 09jan2024 1 7 7 23376 23384 4 7 |
8. | 10jan2024 1 8 8 23376 23385 4.5 8 |
9. | 11jan2024 1 9 9 23376 23386 5 9 |
10. | 12jan2024 1 10 10 23376 23387 5.5 10 |
|---------------------------------------------------------------------|
11. | 15jan2024 1 11 11 23376 23390 6 11 |
12. | 16jan2024 1 12 12 23376 23391 6.5 12 |
13. | 17jan2024 1 13 13 23376 23392 7 13 |
14. | 18jan2024 1 14 14 23376 23393 7.5 14 |
15. | 19jan2024 1 15 15 23376 23394 8 15 |
|---------------------------------------------------------------------|
16. | 22jan2024 1 16 16 23376 23397 8.5 16 |
17. | 23jan2024 1 17 17 23376 23398 9 17 |
18. | 24jan2024 1 18 18 23376 23399 9.5 18 |
19. | 25jan2024 1 19 19 23376 23400 10 19 |
20. | 26jan2024 1 20 20 23376 23401 10.5 20 |
|---------------------------------------------------------------------|
21. | 29jan2024 1 21 21 23376 23404 11 21 |
22. | 30jan2024 1 22 22 23376 23405 11.5 22 |
23. | 31jan2024 1 23 22 23377 23406 12.5 22 |
24. | 01feb2024 1 24 22 23378 23407 13.5 22 |
25. | 02feb2024 1 25 22 23379 23408 14.5 22 |
|---------------------------------------------------------------------|
26. | 05feb2024 1 26 21 23383 23411 16 21 |
27. | 06feb2024 1 27 22 23383 23412 16.5 22 |
28. | 07feb2024 1 28 22 23384 23413 17.5 22 |
29. | 08feb2024 1 29 22 23385 23414 18.5 22 |
30. | 09feb2024 1 30 22 23386 23415 19.5 22 |
|---------------------------------------------------------------------|
31. | 12feb2024 1 31 21 23390 23418 21 21 |
32. | 13feb2024 1 32 22 23390 23419 21.5 22 |
33. | 14feb2024 1 33 22 23391 23420 22.5 22 |
34. | 15feb2024 1 34 22 23392 23421 23.5 22 |
35. | 16feb2024 1 35 22 23393 23422 24.5 22 |
|---------------------------------------------------------------------|
36. | 19feb2024 1 36 21 23397 23425 26 21 |
37. | 20feb2024 1 37 22 23397 23426 26.5 22 |
38. | 21feb2024 1 38 22 23398 23427 27.5 22 |
39. | 22feb2024 1 39 22 23399 23428 28.5 22 |
40. | 23feb2024 1 40 22 23400 23429 29.5 22 |
+---------------------------------------------------------------------+

Thanks Nick I figured it out!

[Bruno Paisani]

Here is an example simple enough to invent but I hope realistic enough to show the main idea.

I have created a dataset without weekends, but without adjusting for public holidays anywhere.

rangerun is just a cousin of rangestat on SSC.

By construction here the number of distinct daily dates is exactly the same as the number of observations in a window, but that wouldn't be true if there were multiple observations for any stock on any date.

It is open to any user to ignore windows without sufficient observations according to some criterion but easier to do that after the fact. If you try to specify if you make observations unavailable to more calculations than you want to make. (Try it to see this!)

clear
set obs 81
gen date = mdy(12,31,2023) + _n
format date %td
keep if inrange(dow(date), 1, 5)
gen ticker = 1

gen y = _n

rangestat (count) count=date (min) min=date (max) max=date (mean) y, by(ticker) int(date -29 0)

program my_distinct
gen ndistinct = sum(date != date[_n-1])
replace ndistinct = ndistinct[_N]
end

rangerun my_distinct, use(date) by(ticker) int(date -29 0)

list in 1/40

+---------------------------------------------------------------------+
| date ticker y count min max y_mean ndisti~t |
|---------------------------------------------------------------------|
1. | 01jan2024 1 1 1 23376 23376 1 1 |
2. | 02jan2024 1 2 2 23376 23377 1.5 2 |
3. | 03jan2024 1 3 3 23376 23378 2 3 |
4. | 04jan2024 1 4 4 23376 23379 2.5 4 |
5. | 05jan2024 1 5 5 23376 23380 3 5 |
|---------------------------------------------------------------------|
6. | 08jan2024 1 6 6 23376 23383 3.5 6 |
7. | 09jan2024 1 7 7 23376 23384 4 7 |
8. | 10jan2024 1 8 8 23376 23385 4.5 8 |
9. | 11jan2024 1 9 9 23376 23386 5 9 |
10. | 12jan2024 1 10 10 23376 23387 5.5 10 |
|---------------------------------------------------------------------|
11. | 15jan2024 1 11 11 23376 23390 6 11 |
12. | 16jan2024 1 12 12 23376 23391 6.5 12 |
13. | 17jan2024 1 13 13 23376 23392 7 13 |
14. | 18jan2024 1 14 14 23376 23393 7.5 14 |
15. | 19jan2024 1 15 15 23376 23394 8 15 |
|---------------------------------------------------------------------|
16. | 22jan2024 1 16 16 23376 23397 8.5 16 |
17. | 23jan2024 1 17 17 23376 23398 9 17 |
18. | 24jan2024 1 18 18 23376 23399 9.5 18 |
19. | 25jan2024 1 19 19 23376 23400 10 19 |
20. | 26jan2024 1 20 20 23376 23401 10.5 20 |
|---------------------------------------------------------------------|
21. | 29jan2024 1 21 21 23376 23404 11 21 |
22. | 30jan2024 1 22 22 23376 23405 11.5 22 |
23. | 31jan2024 1 23 22 23377 23406 12.5 22 |
24. | 01feb2024 1 24 22 23378 23407 13.5 22 |
25. | 02feb2024 1 25 22 23379 23408 14.5 22 |
|---------------------------------------------------------------------|
26. | 05feb2024 1 26 21 23383 23411 16 21 |
27. | 06feb2024 1 27 22 23383 23412 16.5 22 |
28. | 07feb2024 1 28 22 23384 23413 17.5 22 |
29. | 08feb2024 1 29 22 23385 23414 18.5 22 |
30. | 09feb2024 1 30 22 23386 23415 19.5 22 |
|---------------------------------------------------------------------|
31. | 12feb2024 1 31 21 23390 23418 21 21 |
32. | 13feb2024 1 32 22 23390 23419 21.5 22 |
33. | 14feb2024 1 33 22 23391 23420 22.5 22 |
34. | 15feb2024 1 34 22 23392 23421 23.5 22 |
35. | 16feb2024 1 35 22 23393 23422 24.5 22 |
|---------------------------------------------------------------------|
36. | 19feb2024 1 36 21 23397 23425 26 21 |
37. | 20feb2024 1 37 22 23397 23426 26.5 22 |
38. | 21feb2024 1 38 22 23398 23427 27.5 22 |
39. | 22feb2024 1 39 22 23399 23428 28.5 22 |
40. | 23feb2024 1 40 22 23400 23429 29.5 22 |
+---------------------------------------------------------------------+

Hi Nick, could you plese help me out related to the discussion of -rangerun command?

I recently ran the command Clyde once provided in another post (https://www.statalist.org/forums/for...ith-panel-data), the example using -xtreg, fe. However, I'm puzzled as to why all the coefficients are the same across companies and years. I attempted to include a "by(company)" command after "interval(year -5 -1)", but then it fails to run (doesn't calculate "b" or "se"). Any thoughts on this? Thanks in advance.

The command was:

webuse grunfeld, clear

capture program drop one_regression
program define one_regression
xtset company year
xtreg mvalue kstock, fe
gen b = _b[kstock]
gen se = _se[kstock]
exit
end

rangerun one_regression, interval(year -5 -1)

[Nick Cox]

#8 is a repeat of a question now answered at https://www.statalist.org/forums/for...ith-panel-data

Please don't repeat the same question in different threads, certainly not without cross-referencing.