DATA Step, Macro, Functions and more

Help in calculating 5-year compounded return on monthly data

Reply
Occasional Contributor
Posts: 17

Help in calculating 5-year compounded return on monthly data

Hi, I have a large data of monthly stock price from 1985 Aug-1990 July. And I am required to calculate the 5 year compounded return for each stock. How do i go about it? An example would as below, from table A to B. The tricky thing not all stocks have full monthly data from 1985 Aug to 1990 July and there are blanks data too.

table A

CusipMonthsprice
1111111985 Aug2.05
111111
1111111990 Jul5.36
1000001985 Aug5.8
100000
1000001990 Jul8.6

table b

CusipMonthsprice5 years return
1111111985 Aug2.05xxx
111111
1111111990 Jul5.36
1000001985 Aug5.8xxx
100000
1000001990 Jul8.6
Super User
Posts: 17,819

Re: Help in calculating 5-year compounded return on monthly data

What's your formula?

Have you looked into the Financial formula's?

 

Can you post a worked example with data and expected output?

Valued Guide
Posts: 858

Re: Help in calculating 5-year compounded return on monthly data

If you are subtracting beginning price from end price here you go:

 

data have;
infile cards dsd;
input Cusip$ Months$ price;
cards;
111111,1985 Aug,2.05
111111,,,
111111,1990 Jul,5.36
100000,1985 Aug,5.8
100000,,
100000,1990 Jul,8.6
;

data prep;
set have;
if not missing(months) then do;
_month = month(input(put(cats('01',scan(months,2,' '),2015),$9.),date9.));
end;
_year = scan(months,1,' ');
run;

proc sort data=prep out=prep2;by cusip _year _month;where not missing(months);

data want;
set prep2;
retain _begin;
by cusip _year _month;
if first.cusip then _begin = price;
if last.cusip then _end = price;
return = _end-_begin;
drop _:;
run;

Ask a Question
Discussion stats
  • 2 replies
  • 149 views
  • 0 likes
  • 3 in conversation