BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MAC1430
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

MAC1430
Pyrite | Level 9

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 

ballardw
Super User

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.

MAC1430
Pyrite | Level 9
Thanks for your reply, I will edit the post and will fix the errors in it.
MAC1430
Pyrite | Level 9
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. 

 

Ksharp
Super User

Sorry I don't understand what you mean.

MAC1430
Pyrite | Level 9

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;

 

Ksharp
Super User

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;
MAC1430
Pyrite | Level 9
Thanks a lot Ksharp 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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