topic Re: How to calculate buy-and-hold return based on daily return data in SAS Programming

How to calculate buy-and-hold return based on daily return data

Ariel — Thu, 15 Oct 2015 06:09:01 GMT

Hello,

Currently I have multiple daily data for multiple firms of the 5 day, 1 month(21 trading days), 3 months(63 trading days), 6 months(126 trading days), 9 months(189 trading days) and 12 months(252 trading days) stock buy-and-hold returns.

MY data is as below: (252 may not be the last data of a firm)

Firm_ID	date	daye	rtn
1101	2009/8/26	1	-0.0173
1101	2009/8/27	2	0.00147
…
1101	2010/8/25	252	0.03
1101		253
1101		…
1102	2011/3/23	1	0.0014
1102	2011/3/24	2	-0.007
…
1102	2011/9/9	252	0.045
1102		…
1205	2013/2/4	1	0.065
…

If I calculate it by hand then:

(1) 5 days: Buy and hold return=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day5)-1

(2) 1 month: BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day21)-1

(3) 3 months: BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day63)-1

....

(6) 12 months:BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day252)-1

However, I don't know how to write a sas program to do this...

Please give me some suggestions. Thank you!

Re: How to calculate buy-and-hold return based on daily return data

user24feb — Thu, 15 Oct 2015 08:07:44 GMT

A brute force approach would be (maybe proc timedata is worth looking):

%Let Span=5; *!;

Data A;
Do Firm=1 To 3;
    Do i=0 To 20;
      Date=IntNX('month',"01JAN2000"d,i,'same');
   Rtn=RanNor(1)/10;
   Output;
End;
End;
Drop i;
Format Date Date9.;
Run;

%Macro L;
%Do i=1 %To &Span.;
%If &i.=1 %Then %Do;
Data B;
    Set A;
    A_Rtn1=(1+Rtn);
Run;
%End;
%Else %Do;
Data B;
    Set B;
    By Firm;
    A_Rtn&i.=Lag(A_Rtn%Eval(&i.-1));
    If First.Firm Then Call Missing (A_Rtn&i.);
Run;
%End;
%End;
%Mend;
%L;

Data B (Drop=A_:);
Set B;
Array A A_:;
Total_Rtn_&Span.=1;
Do over A;
Total_Rtn_&Span.=Total_Rtn_&Span.*A;
End;
Total_Rtn_&Span.=Total_Rtn_&Span.-1;
Run;

Re: How to calculate buy-and-hold return based on daily return data

user24feb — Thu, 15 Oct 2015 08:30:14 GMT

However, this would give the same result:

%Let Span=5; *!;

Data A;
Do Firm=1 To 3;
    Do i=0 To 20;
      Date=IntNX('month',"01JAN2000"d,i,'same');
     Rtn=RanNor(1)/10;
   Output;
End;
End;
Drop i;
Format Date Date9.;
Run;

Data B (Keep=Firm Date Rtn Total_Rtn_&Span.);
Retain dummy ;
Set A;
By Firm;
If First.Firm Then Do;
    count=0;
dummy=1;
End;
count+1;
Drop_Value=Lag&Span.(Rtn);
If count gt &Span. Then Do;
    dummy=dummy*(1+Rtn)/(1+Drop_Value);
End;
Else Do;
    dummy=dummy*(1+Rtn);
End;
Total_Rtn_&Span.=dummy-1;
Run;

Re: How to calculate buy-and-hold return based on daily return data

Ariel — Tue, 27 Oct 2015 00:47:06 GMT

Thanks for your help! 🙂 🙂