Fluorite | Level 6

Monthly stock returns from monthly prices

hi there, How do i compute monthly stock returns using monthly end prices in sas. The data i have is for 500 different stocks stacked together.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Monthly stock returns from monthly prices

proc sort data=sashelp.stocks out=stocks;

by stock date;

run;

data returns;

set stocks;

by stock;

prev_open=lag(open);

return=log(open/prev_open);

if first.stock then return=.;

run;

10 REPLIES 10
PROC Star

Re: Monthly stock returns from monthly prices

If not, what are your plans to get it into a SAS data set?

If it is already a SAS data set, what are the names of the variables?  Which ones are character and which ones are numeric?

Is the data complete (no months skipped in the middle)?

Do you want month over month returns, or some other returns?

Fluorite | Level 6

Re: Monthly stock returns from monthly prices

Yes its already in Sas. The Variable names are DATE TICKER  PRICE  .....All of them are Character. All months are available. And i want month over month returns

Super User

Re: Monthly stock returns from monthly prices

How are you defining RETURN?

Fluorite | Level 6

Re: Monthly stock returns from monthly prices

Return = Ln(Price t/ Price t-1)...where price t is today's price and price t-1 is previous month price of a given ticker.

Super User

Re: Monthly stock returns from monthly prices

proc sort data=sashelp.stocks out=stocks;

by stock date;

run;

data returns;

set stocks;

by stock;

prev_open=lag(open);

return=log(open/prev_open);

if first.stock then return=.;

run;

Fluorite | Level 6

Re: Monthly stock returns from monthly prices

Thanks a bunch. It worked out well.

Quartz | Level 8

Re: Monthly stock returns from monthly prices

Hi, I wondering you are having problems in how to frame or ask a question by explaining the right set of details. Let the folks know,

1. what does your input dataset have

2. variable names and type

4. a Convert business logic or formula, something like a defined business requirement that is meant to be used in the input to get your wanted output.

5. size of the dataset, long or wide and how big?

I think had a link or a page that explains how to ask a question.  You really need to let us the folks know as much details(simulated) as possible to get the best possible help.

Super User

Re: Monthly stock returns from monthly prices

Fluorite | Level 6

Re: Monthly stock returns from monthly prices

Just another step, how do i compute the trailing/rolling standard deviation for the returns i have generated for the different Tickers/stocks

Obsidian | Level 7

Re: Monthly stock returns from monthly prices

``````proc sort data=input; by tic date; run;
proc expand data=input out=output method = none ;
by tic;
convert ret= ret; convert ret=RETVOL / transformout=(MOVSTD 4); * 4 = number of 4 past/trailing obs to calculate std;
label RETVOL =rollingSTDofRET;
run;``````
Discussion stats
• 10 replies
• 3614 views
• 3 likes
• 5 in conversation