04-12-2015 01:04 AM
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.
|Cusip||Months||price||5 years return|
09-11-2015 11:00 AM
What's your formula?
Have you looked into the Financial formula's?
Can you post a worked example with data and expected output?
09-11-2015 01:20 PM
If you are subtracting beginning price from end price here you go:
infile cards dsd;
input Cusip$ Months$ price;
if not missing(months) then do;
_month = month(input(put(cats('01',scan(months,2,' '),2015),$9.),date9.));
_year = scan(months,1,' ');
proc sort data=prep out=prep2;by cusip _year _month;where not missing(months);
by cusip _year _month;
if first.cusip then _begin = price;
if last.cusip then _end = price;
return = _end-_begin;