DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

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

[ Edited ]

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!


Accepted Solutions
Solution
‎10-26-2015 08:46 PM
Super Contributor
Posts: 340

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

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_Smiley Happy;
  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;

View solution in original post


All Replies
Solution
‎10-26-2015 08:46 PM
Super Contributor
Posts: 340

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

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_Smiley Happy;
  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;

Super Contributor
Posts: 340

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

Posted in reply to user24feb

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;

 

Occasional Contributor
Posts: 18

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

Posted in reply to user24feb
Thanks for your help! Smiley Happy Smiley Happy
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 2308 views
  • 0 likes
  • 2 in conversation