DATA Step, Macro, Functions and more

Calculate Compounding Monthly Returns using daily returns

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

Calculate Compounding Monthly Returns using daily returns

Hi everyone,

 

Can you please help me to calculate compounding monthly returns using daily returns? The first step, if the number of non-missing daily returns or daily return with a value equal to -66 or -99 in a month are15 or above 15 then the non-missing daily return or daily return with a value equal to -66 or -99 is set equal to market returns (mkt_ret). The second step is to calculate monthly compounding returns from daily returns.

However, If the number of non-missing daily returns or daily return with a value equal to -66 or -99 is less than 15 then monthly return is set equal to -99. Please find the data below. I guess the correct answer will be the monthly return of 0.05085.

data have;
infile cards expandtabs truncover;
input stock date : yymmdd10. daily_ret mkt_ret ;
format date yymmdd10.;
cards;
10006	19600104	-99	0.01
10006	19600104	0.0387	0.011
10006	19600105	0.0223	0.012
10006	19600106	-66	0.013
10006	19600107	-0.0146	0.014
10006	19600108	-0.0074	0.015
10006	19600111	-0.0174	0.016
10006	19600112	-0.0051	0.017
10006	19600113	-0.0051	0.018
10006	19600114	0.0306	0.019
10006	19600115	0.0099	0.02
10006	19600118	-0.0049	0.021
10006	19600119		0.022
10006	19600120	-0.0172	0.023
10006	19600121	0.0025	0.024
10006	19600122	0.01	0.025
10006	19600125	-0.0025	0.026
10006	19600126	-0.0124	0.027
10006	19600127	-0.0101	0.028
10006	19600128	0.0076	0.029
10006	19600129	-0.0176	0.03
;
run;

Thanks for your help.

 

Best,

 

Cheema


Accepted Solutions
Solution
‎04-28-2017 05:35 PM
Super User
Posts: 9,681

Re: Calculate Compounding Monthly Returns using daily returns

OK. This much more clear .

 

data have;
infile cards expandtabs truncover;
input stock date : yymmdd10. daily_ret mkt_ret ;
format date yymmdd10.;
cards;
10006	19600104	.	0.01
10006	19600104	0.0387	0.011
10006	19600105	0.0223	0.012
10006	19600106	.	0.013
10006	19600107	-0.0146	0.014
10006	19600221	0.0025	0.024
10006	19600222	0.01	0.025
10006	19600225	-0.0025	0.026
10006	19600226	.	0.027
10006	19600227	-0.0101	0.028
10006	19600228	0.0076	0.029
10006	19600229	-0.0176	0.03
;
run;
data have;
 set have;
 monyy=put(date,monyy7.);
run;
data want;
n=0;comp_ret=1;
do until(last.monyy);
 set have;
 by stock monyy notsorted;
 if not missing(daily_ret) then n+1;
end;

do until(last.monyy);
 set have;
 by stock monyy notsorted;
 if n ge 5 then do;
  comp_ret=comp_ret*(1+coalesce(daily_ret,mkt_ret));
 end;
 else comp_ret=-99;
end;
output;
drop n daily_ret mkt_ret;
run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: Calculate Compounding Monthly Returns using daily returns

The search bar near the top of the page is specifically designed to find similar questions.  By typing in your thread title to this search bar I found 15 discussions on this matter, 3 of which had correct answers:

https://communities.sas.com/t5/forums/searchpage/tab/message?q=Compounding+monthly+returns

Contributor
Posts: 57

Re: Calculate Compounding Monthly Returns using daily returns

Thaks, I checked those posts earlier but it seems a bit different than what I am looking for. I want additional filters while estimating monthly compounding returns using daily returns. For example, I want to estimate monthly returns when there are at least 15 non-missing daily returns available; otherwise, the monthly return will be set equal to -99. Furthermore, I want to set daily returns equal to market returns when the daily return is missing if at least 15 non-missing daily returns are available. Please correct me if I missed something.

 

Best,

 

Cheema 

Super User
Posts: 10,500

Re: Calculate Compounding Monthly Returns using daily returns

[ Edited ]

Show as a WANT data set some of the records with the results calculated by hand. You don't need to include every day of month but enough data so we can see what you are doing. Also since you are doing something for "month" it would likely help as well to have some records from two months to see what the boundary may entail.

You may also have to define exactly what that "daily return" means. "Return" implies to me that it may be calculated from multiple values not shown and how it is created could well be important for any combined calculation. For instance if it is a actually a rate calculated with numerator/denominator then averaging, adding or almost anything on the result is pretty difficult to interpret (at best).

 

I have no clue how you would get 0.05085 from your daily_ret values.

 

BTW your data step is likely incorrect as it returns a value of 0.022 for daily_ret on 19600119. For inline example data place a . to indicate missing OR read by fixed columns.

Contributor
Posts: 57

Re: Calculate Compounding Monthly Returns using daily returns

Thanks for your reply, I will edit the post and will fix the errors in it.
Contributor
Posts: 57

Re: Calculate Compounding Monthly Returns using daily returns

data have;
input stock date : yymmdd10. daily_ret mkt_ret ;
format date yymmdd10.;
cards;
10006	19600104	.	0.01
10006	19600104	0.0387	0.011
10006	19600105	0.0223	0.012
10006	19600106	.	0.013
10006	19600107	-0.0146	0.014
10006	19600221	0.0025	0.024
10006	19600222	0.01	0.025
10006	19600225	-0.0025	0.026
10006	19600226	.	0.027
10006	19600227	-0.0101	0.028
10006	19600228	0.0076	0.029
10006	19600229	-0.0176	0.03
;
run;

Please find the new data. First, I want to find the number of non-missing daily_ret in a month for each stock. If the number of non-missing daily_ret is at least 5, then I want to replace any missing values in that month using market_ret. Finally, I want to estimate compound or cumulative returns in a month using daily_ret. If the number of non-missing daily_ret in a month for a stock is less than 5, then the monthly return for that stock will be set equal to -99.

For example, in the data provided, the monthly return for January 1960 will be set equal to -99 since there are only 3 non-missing values. However, the cumulative (compound) monthly return for the month of Feburary will be 0.016382. First, we will set the missing daily_ret value for 19600226 (26 February 1960) equal to market return of 0.027 and then estimate compound returns as ((1+0.0025)*(1+0.01)*(1-0.0025)*(1+0.027)*(1-0.0101)*(1+0.0076)*(1-0.0176)-1).

 

Thanks in advance for your help. 

 

Super User
Posts: 9,681

Re: Calculate Compounding Monthly Returns using daily returns

Sorry I don't understand what you mean.

Contributor
Posts: 57

Re: Calculate Compounding Monthly Returns using daily returns

Hi Ksharp,

 

Sorry for not making it clear. Please find the new one with the example and data at the end.

Please find the new data. First, I want to find the number of non-missing daily_ret in a month for each stock. If the number of non-missing daily_ret is at least 5, then I want to replace any missing values in that month using market_ret. Finally, I want to estimate compound or cumulative returns in a month using daily_ret. If the number of non-missing daily_ret in a month for a stock is less than 5, then the monthly return for that stock will be set equal to -99.

 

For example, in the data provided, the monthly return for January 1960 will be set equal to -99 since there are only 3 non-missing values. However, the cumulative (compound) monthly return for the month of February will be 0.016382. First, we will set the missing daily_ret value for 19600226 (26 February 1960) equal to market return of 0.027 and then estimate compound returns as ((1+0.0025)*(1+0.01)*(1-0.0025)*(1+0.027)*(1-0.0101)*(1+0.0076)*(1-0.0176)-1).

 

Thanks in advance for your help.

 

data have;
input stock date : yymmdd10. daily_ret mkt_ret ;
format date yymmdd10.;
cards;
10006	19600104	.	0.01
10006	19600104	0.0387	0.011
10006	19600105	0.0223	0.012
10006	19600106	.	0.013
10006	19600107	-0.0146	0.014
10006	19600221	0.0025	0.024
10006	19600222	0.01	0.025
10006	19600225	-0.0025	0.026
10006	19600226	.	0.027
10006	19600227	-0.0101	0.028
10006	19600228	0.0076	0.029
10006	19600229	-0.0176	0.03
;
run;

 

Solution
‎04-28-2017 05:35 PM
Super User
Posts: 9,681

Re: Calculate Compounding Monthly Returns using daily returns

OK. This much more clear .

 

data have;
infile cards expandtabs truncover;
input stock date : yymmdd10. daily_ret mkt_ret ;
format date yymmdd10.;
cards;
10006	19600104	.	0.01
10006	19600104	0.0387	0.011
10006	19600105	0.0223	0.012
10006	19600106	.	0.013
10006	19600107	-0.0146	0.014
10006	19600221	0.0025	0.024
10006	19600222	0.01	0.025
10006	19600225	-0.0025	0.026
10006	19600226	.	0.027
10006	19600227	-0.0101	0.028
10006	19600228	0.0076	0.029
10006	19600229	-0.0176	0.03
;
run;
data have;
 set have;
 monyy=put(date,monyy7.);
run;
data want;
n=0;comp_ret=1;
do until(last.monyy);
 set have;
 by stock monyy notsorted;
 if not missing(daily_ret) then n+1;
end;

do until(last.monyy);
 set have;
 by stock monyy notsorted;
 if n ge 5 then do;
  comp_ret=comp_ret*(1+coalesce(daily_ret,mkt_ret));
 end;
 else comp_ret=-99;
end;
output;
drop n daily_ret mkt_ret;
run;
Contributor
Posts: 57

Re: Calculate Compounding Monthly Returns using daily returns

Thanks a lot Ksharp Smiley Happy
☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 218 views
  • 2 likes
  • 4 in conversation