Stock return calculation

Occasional Contributor
Posts: 15

Stock return calculation

Hello,

I am very new to SAS and have a research project which requires to calculate stock return. My data allows short sale. So shares held (inventory) may be negative. I need to calculate return at each point of time by following First In First Out (FIFO) rule. FIFO means where the oldest entry is processed first.

Original data:

3        Sell              30                 4

4        Sell              40                -2

5        Sell              50                -4

Desired ouput:

3        Sell                 30                 4             20

4        Sell                 40                -2             90

5        Sell                 50                -4

The logic is:

On date 3, shares sold is the difference between ShrsHeld and lag(ShrsHeld). So actual shares sold on date 3 is 1. Then return is 1*(30-10)=20.

On date 4, return is 1*(40-10)+3*(40-20)=90.

On date 6, return is -2*(60-40)-1*(60-50)=-50.

Best,

Tammy

Occasional Contributor
Posts: 17

Re: Stock return calculation

Hello,

I have tried this one, although far more complex and inefficient as one can expect by using

c language. There are many advanced techniques that cannot be explained at once.

You can check out the online documentation. Hope this helps.

best regards,

Bill

/*======================== worked example ============================*\

| # description: FIFO stock return calculation                         |

| # note: a damn hard problem on SAS community...                      |

| Essence: Because only a few data are processed at a time, one can    |

|         simply put them into macro variables (by call symputx) and   |

|         then exploit %if and %do loops to deal with compound logic   |

\*====================================================================*/

data raw;

input Date BS \$ Price held;

format date 8. BS \$4. Price 12.5 held 8.;

cards;

3 Sell 30 4

4 Sell 40 -2

5 Sell 50 -4

;run;

/*====================================================================*\

|                    1.prepare data                                    |

\*====================================================================*/

/* note: if there are many stocks present, simply sort by stock & date

/*       and replace "_N_=1" by "BY stock;" + "first.stock=1" */

data action(keep=date price amount);

set raw;

lagheld=lag(held);

if _N_=1 then amount=held;

else amount=held-lagheld;

run;

/* create datasets with zero obs, to be appended */

data position;

format date 8. Price 12.5 amount 8.;

stop;

run;

data return;

format date 8. amount 8. return 12.5;

stop;

run;

/*====================================================================*\

|                    2.iterative macro                                 |

\*====================================================================*/

%macro FIFO;

/* get the max number of action */

data _null_;

dsid=open("action"); /*dataset handle*/

/*put NOBS (a numeric attribute) of action into i_max*/

call symputx("i_max", attrn(dsid,"nobs"), "G");

x=close(dsid);/*close dataset handle*/

run;

/* iterate on all obs of dataset action */

%do i=1 %to &i_max;

data _null_;

/* store the ith obs of dataset action into macro variables */

set action(firstobs=&i obs=&i);

call symputx("DATE", date, "G");

call symputx("PRICE", price, "G");

call symputx("AMOUNT", amount, "G");

/* put NOBS of position into a macro variable */

dsid=open("position");

call symputx("N_pos", attrn(dsid,"nobs"), "G");

x=close(dsid);

run;

/* loop until &AMOUNT is consumed */

%do %while ("&AMOUNT"^="0");

/*******************************

* case 1: if position is empty *

*******************************/

%if &N_pos=0 %then %do;

/* positionush-back, return:no action*/

data tmp_position;

date=&DATE;

price=&PRICE;

amount=&AMOUNT;

run;

proc datasets lib=work nolist;

append base=position data=tmp_position;

delete tmp:;

quit;

/* consume all AMOUNT */

%let AMOUNT=0;

%end; /*end case 1*/

%else %do; /*case 2-5*/

/* position is not empty -> put the 1st obs into macro variables */

data _null_;

set position(firstobs=1 obs=1);

call symputx("PRICE_P", price, "G");

call symputx("AMOUNT_P", amount, "G");

run;

/*******************************************************

* case 2: the sign of &AMOUNT is the same as &AMOUNT_P *

*******************************************************/

%if %eval(&AMOUNT*&AMOUNT_P)>0 %then %do;

/* positionush-back, return:no action*/

data tmp_position;

date=&DATE;

price=&PRICE;

amount=&AMOUNT;

run;

proc datasets lib=work nolist;

append base=position data=tmp_position;

delete tmp:;

quit;

/* consume all AMOUNT */

%let AMOUNT=0;

%end; /*end case 2*/

%else %do; /*case 3-5*/

/******************************************************

* case 3: the sign of &AMOUNT differs from &AMOUNT_P  *

* and |&AMOUNT|<|&AMOUNT_P|                           *

******************************************************/

%if %sysfunc(abs(&AMOUNT))<%sysfunc(abs(&AMOUNT_P)) %then %do;

/* position:update 1st obs*/

data position;

modify position;

if _N_=1 then amount=amount+&AMOUNT;

run;

/*returnush-back*/

data tmp_return;

date=&DATE;

amount=&AMOUNT;

return=-(&Price-&Price_P)*&AMOUNT;

run;

proc datasets lib=work nolist;

append base=return data=tmp_return;

delete tmp:;

quit;

/* consume all AMOUNT */

%let AMOUNT=0;

%end; /*end case 3*/

/******************************************************

* case 4: the sign of &AMOUNT differs from &AMOUNT_P  *

* and |&AMOUNT|=|&AMOUNT_P|                           *

******************************************************/

%else %if %sysfunc(abs(&AMOUNT))=%sysfunc(abs(&AMOUNT_P)) %then %do;

/* position:drop the 1st obs*/

data position;

set position(firstobs=2);

run;

/*returnush-back*/

data tmp_return;

date=&DATE;

amount=-&AMOUNT_P;

return=(&Price-&Price_P)*&AMOUNT_P;

run;

proc datasets lib=work nolist;

append base=return data=tmp_return;

delete tmp:;

quit;

/* consume all AMOUNT */

%let AMOUNT=0;

%end; /*end case 4*/

/******************************************************

* case 5: the sign of &AMOUNT differs from &AMOUNT_P  *

* and |&AMOUNT|>|&AMOUNT_P|                           *

******************************************************/

%else %do;

/* position:drop the 1st obs*/

data position;

set position(firstobs=2);

run;

/* update N_pos */

%let N_pos=%eval(&N_Pos-1);

/*returnush-back*/

data tmp_return;

date=&DATE;

amount=-&AMOUNT_P;

return=(&Price-&Price_P)*&AMOUNT_P;

run;

proc datasets lib=work nolist;

append base=return data=tmp_return;

delete tmp:;

quit;

/* consume AMOUNT by AMOUNT_P */

%let AMOUNT=%eval(&AMOUNT+&AMOUNT_P);

%end; /*end case 5*/

%end; /*end case 3-5*/

%end; /*end case 2-5*/

%end;/*end do-while amount^=0 */

%end; /*end obs i<=N_pos*/

%mend;

/* excecute */

%FIFO;

/*====================================================================*\

|                    3.organize results                                |

\*====================================================================*/

proc sql;

/* sum-up daily returns */

create table return_summed as

select distinct date, sum(return) as return, sum(amount) as amount

from return

group by date

order by date;

/* merge to the original dataset*/

create table final as

select * from raw

left join return_summed

on raw.date=return_summed.date

order by date;

quit;

Occasional Contributor
Posts: 15

Re: Stock return calculation

Thank you so much, Bill.

Not applicable
Posts: 1

Re: Stock return calculation

Hi Bill...

It works very well !

I have similar case with two additional fields (Customer Name & Stock Code).

The database has multiple customer and stock code.

Thank you for your kind assistance.

Thanks and regards,

Ricky

SAS Employee
Posts: 340

Super User
Posts: 10,770

Re: Stock return calculation

Post your sample , and the output you need . That would be better than explaining your question.

Xia Keshan

Posts: 3,167

Re: Stock return calculation

The key to implement FIFO logic is to have a workshop where you can move your pointer freely and update the data element as you will. Hash object is a place where you can do exact that, and shouldn't be " far more complex and inefficient as one can expect by using c language."

The follow can also be easily tweaked to adapt to multi-user/multi-stock scenario by adding DOW or first/last technique.  The code has not been rigorously tested.

data have;

input Date   Buy_Sell :\$4.      Price       ShrsHeld;

Shr_Brk=abs(sum(shrsheld, -lag(shrsheld))); /*This is to break down shares per price level*/

cards;

3        Sell              30                 4

4        Sell              40                -2

5        Sell              50                -4

8        Sell             20                 -2

;

data want;

if _n_=1 then

do;

dcl hash h(ordered:'a');

h.definekey('_date');

h.definedata('_date', '_price''_Shr_Brk', '_bs_ind');

h.definedone();

dcl hiter hi('h');

call missing (_date, _bs_ind,_price, _Shr_Brk);

end;

set have;

call missing (Retrn);

/*here is to show where there is a stock In/Out, it is also where to calculate the return*/

if ((lag(shrsheld) > 0 and buy_sell='Sell') or (lag(shrsheld) < 0 and buy_sell='Buy')) and (not missing (lag(shrsheld))) then

do;

do rc=hi.first() by 0 while (rc=0);

Retrn + min(shr_brk,_shr_brk)*(bs_ind*price + _bs_ind*_price);

if shr_brk = _shr_brk then

do;

_Rmkey = _date;

rc=hi.next();

rc=h.remove(key:_rmkey);

return;

end;

else if shr_brk < _shr_brk then

do;

_shr_brk = _shr_brk - shr_brk;

rc=h.replace();

return;

end;

else if shr_brk > _shr_brk then

do;

_Rmkey = _date;

shr_brk=shr_brk - _shr_brk;

rc=hi.next();

rc=h.remove(key:_rmkey);

rc=hi.first();

end;

end;

end;

_date=date;

_price=price;

_shr_brk=shr_brk;

_bs_ind=bs_ind;

rc=h.replace();

keep date Buy_Sell Price ShrsHeld retrn;

run;

Discussion stats
• 6 replies
• 2264 views
• 0 likes
• 6 in conversation