Stock return calculation

Reply
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:

Date   Buy/Sell       Price       ShrsHeld

   1        Buy              10                 2

   2        Buy              20                 5

   3        Sell              30                 4       

   4        Sell              40                -2       

   5        Sell              50                -4       

   6        Buy              60                -1      

    


Desired ouput:

Date   Buy/Sell          Price        ShrsHeld   Return

   1        Buy                 10                 2

   2        Buy                 20                 5

   3        Sell                 30                 4             20

   4        Sell                 40                -2             90

   5        Sell                 50                -4      

   6        Buy                 60                -1            -50

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.

I have trouble to code it. Please help!! Thank you for your kind assistance.

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;

1 Buy 10 2

2 Buy 20 5

3 Sell 30 4

4 Sell 40 -2

5 Sell 50 -4

6 Buy 60 -1

;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;

                      /* positionSmiley Tongueush-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;

                      /* positionSmiley Tongueush-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;

                      /*returnSmiley Tongueush-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;

                      /*returnSmiley Tongueush-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);

                      /*returnSmiley Tongueush-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;

消息编辑者为:Chuan-Bin Huang

Occasional Contributor
Posts: 15

Re: Stock return calculation

Thank you so much, Bill.

N/A
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.

Kindly need your help.

Thank you for your kind assistance.

Thanks and regards,

Ricky

SAS Employee
Posts: 340

Re: Stock return calculation

Super User
Posts: 9,681

Re: Stock return calculation

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

Xia Keshan

Respected Advisor
Posts: 3,124

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;

     BS_ind=ifn(Buy_Sell='Buy',-1,1); /*This is to digitalize Sell/Buy*/

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

     cards;

1        Buy              10                 2

2        Buy              20                 5

3        Sell              30                 4       

4        Sell              40                -2       

5        Sell              50                -4       

6        Buy              60                -1 

7        Buy              40                 1

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;

Ask a Question
Discussion stats
  • 6 replies
  • 1850 views
  • 0 likes
  • 6 in conversation