Specific period Buy and Hold returns for every day

[Jakob Stoll]

Hello everyone,

I have daily panel data with prices and returns for several companies.
My question is, how do I calculate Buy and Hold returns for every company and every day in the sample.
E.g. 1-year holding period returns for ARCHER-DANIELS-MIDLAND on 15th March 2020 (until 14th March 2021), 16th March 2020 (until 15th March 2021), ...

* Example generated by -dataex-. For more info, type help dataex
clear
input double PERMNO str58 CONM long caldt double prccd float RETURN
10516 "ARCHER-DANIELS-MIDLAND CO" 17136 34.83             .
10516 "ARCHER-DANIELS-MIDLAND CO" 17139 34.85   .0005741212
10516 "ARCHER-DANIELS-MIDLAND CO" 17140 34.58   -.007747393
10516 "ARCHER-DANIELS-MIDLAND CO" 17141  34.3   -.008097241
10516 "ARCHER-DANIELS-MIDLAND CO" 17142 33.76   -.015743468
10516 "ARCHER-DANIELS-MIDLAND CO" 17143 34.07    .009182505
10516 "ARCHER-DANIELS-MIDLAND CO" 17146 33.69    -.01115354
10516 "ARCHER-DANIELS-MIDLAND CO" 17147 33.47   -.006530051
10516 "ARCHER-DANIELS-MIDLAND CO" 17148 32.76    -.02121311
10516 "ARCHER-DANIELS-MIDLAND CO" 17149 32.62   -.004273486
10516 "ARCHER-DANIELS-MIDLAND CO" 17150 32.72    .003065674
10516 "ARCHER-DANIELS-MIDLAND CO" 17153 32.06   -.020171143
end
format %td caldt

Please keep in mind that in the data only trading days are included.

I have looked for a solution for several hours but haven't found anything that exactly matches my problem. I think using asrol and generating an identifying variable could work out. Indeed for the first day of the year and a yearly variable this works out, however, already for the second trading day of the year, one observation is missing. In addition, I thought about generating an identifying dummy variable for every date, however, this is probably not the best solution, since my dataset is really large (c. 15 million observations).

gen fyear1 = year(caldt)
bysort PERMNO fyear1 : asrol RETURN, stat(product) add(1)

Best regards,
Jakob Stoll

[Clyde Schechter]

For those of us who do not work in finance, what is the convention for defining the 1-yr buy and hold return when the 1-yr anniversary is not a trading day? Do you use the nearest preceding, or nearest following trading day?

Also, if you are not using the current version of Stata (17), please indicate in your response which version you are using.

[Jakob Stoll]

Dear Clyde,

To be honest I am not entirely sure about the convention in finance since a) daily returns are rarely used for BHAR scenarios and b) I am still an undergraduate student, and, thus not familiar with all academic conventions. However, for my use, I think using the nearest following trading day should be sufficient.

In addition, I use Stata 17.

Best,
Jakob

[Clyde Schechter]

OK. Your example data doesn't extend as long as a year (and it would be difficult to post one that did), so the following code begins by creating a toy data set that illustrates the code. The actual solution code starts from the CALCULATE 1 YEAR BUY AND HOLD RETURN comment.

clear*

//  CREATE AN EXAMPLE DATA SET
set obs `=td(31dec2020)-td(1jan2015) + 1'
gen int caldt = td(31dec2014) + _n
format caldt %td
expand 10
by caldt, sort: gen PERMNO = _n

xtset PERMNO caldt

set seed 1234
gen double return = rnormal(1, 0.01)
by PERMNO (caldt): gen prccd = rnormal(100, 5) if _n == 1
by PERMNO (caldt): replace prccd = L1.prccd*L1.return if _n > 1
//  REMOVE SATURDAYS AND SUNDAYS AND CORRECT THE RETURNS
drop if inlist(dow(caldt), 6, 0)
by PERMNO (caldt): replace return = prccd/prccd[_n-1] if caldt[_n-1] != caldt - 1
format prccd %3.2f

//  CALCULATE 1 YEAR BUY AND HOLD RETURN


//      WHEN END OF HOLDING PERIOD caldt IS MISSING, USE NEAREST SUBSESQUENT caldt
gen anniversary = birthday(caldt, year(caldt)+1) - 1
format anniversary %td
rangestat (min) ref_caldt = caldt, by(PERMNO) interval(caldt anniversary .)
format ref_caldt %td

//      FIND prccd ON THAT caldt
rangestat (first) ref_prccd = prccd, by(PERMNO) interval(caldt ref_caldt ref_caldt)
format ref_prccd %3.2f

//      CALCULATE BUY AND HOLD 1 YR RETURN
gen buy_hold_1_yr_return = (ref_prccd/prccd)-1

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

Note that the code does not calculate a running product of returns. To do so over a period of a year with (nearly) daily returns is to set up a trap from accumulating rounding/truncation errors. The return can be directly calculated from the price on the final date and the initial price directly. In fact, with the price data, you don't even need the variable RETURN--notice that the code makes no use of it.

[Jakob Stoll]

Dear Clyde,

thank you very much. This works out exactly as I intended.

Best,
Jakob