DATA Step, Macro, Functions and more

how to calculate holding period of stocks?

Reply
Occasional Contributor
Posts: 5

how to calculate holding period of stocks?

Hi All,

I want to calculate holding period of stocks to be held by a particular customer.

For example look data in this format:

TRANS_DATESTOCKSHRSBuy/Sell
1/30/2009X100B
2/27/2009X100B
3/31/2009X400B
5/29/2009X100B
11/16/2009X150S
12/31/2009X190S
1/11/2010X260S

Now ,150 units of 'X' were sold on 11/16/2009;out of which holding period of 100 is (11/16/2009-1/30/2009) and holding period of remaining 50 is (11/16/2009-2/27/2009).

So I want output in this format:

TRANS_DATESTOCKSHRSBuy/SellPurchase date
1/30/2009X100B1/30/2009
2/27/2009X100B2/27/2009
3/31/2009X400B3/31/2009
5/29/2009X100B5/29/2009
11/16/2009X100S1/30/2009
11/16/2009X50S2/27/2009
12/31/2009X50S2/27/2009
12/31/2009X140S3/31/2009
1/11/2010X260S3/31/2009

Can anyone help me to build this logic in SAS?

I guess we need to use Hash tables to store the data.

Thanks & regards,

Super Contributor
Posts: 644

Re: how to calculate holding period of stocks?

To answer this question we have to have some way of knowing which shares were sold.  Shares do not have to be sold in the order they were bought.  We need to have a parcel identifier in the data.

Richard in Oz

Super User
Posts: 9,682

Re: how to calculate holding period of stocks?

Hi.

data have;
infile cards expandtabs;
input TRANS_DATE : mmddyy10.     STOCK $ SHRS     Buy_Sell $;
format      TRANS_DATE      mmddyy10.;
cards;
1/30/2009     X     100     B
2/27/2009     X     100     B
3/31/2009     X     400     B
5/29/2009     X     100     B
11/16/2009     X     150     S
12/31/2009     X     190     S
1/11/2010     X     260     S
;
run;
proc sort data=have;by Buy_Sell  TRANS_DATE;run;
data b;
 set have(where=(Buy_Sell='B'));
 do i=1 to SHRS;
  output;
 end;
 keep TRANS_DATE;
run;
data s;
 set have(where=(Buy_Sell='S'));
  do i=1 to SHRS;
   output;
 end;
 drop SHRS i; 
run;
data temp;
 merge s b(rename=(TRANS_DATE=_TRANS_DATE));
 retain s 1;
run;
data temp;
 set temp;
  if TRANS_DATE ne lag(TRANS_DATE) or _TRANS_DATE ne lag(_TRANS_DATE) then group+1;
run;
data temp1;
 set temp;
 by group;
 SHRS+s;
 if last.group then do;output;SHRS=0;end;
 drop s group;
run;
data want;
 set  have(where=(Buy_Sell='B')) temp1;
run;

PROC Star
Posts: 7,363

Re: how to calculate holding period of stocks?

Another way to do it could be as follows:

data have;

  informat TRANS_DATE mmddyy10.;

  format TRANS_DATE mmddyy10.;

  input TRANS_DATE STOCK $ SHRS Buy_Sell $;

  cards;

1/30/2009 X 100 B

2/27/2009 X 100 B

3/31/2009 X 400 B

5/29/2009 X 100 B

11/16/2009 X 150 S

12/31/2009 X 190 S

1/11/2010 X 260 S

;

proc sort data=have;

  by stock trans_date buy_sell;

run;

data want (drop=keepit_: first: counter amount);

  set have;

  by stock;

  format purchase_date mmddyy10.;

  array keepit_d(999);

  array keepit_s(999);

  retain keepit_:;

  if first.stock then do;

    call missing(of keepit_d(*));

    call missing(of keepit_s(*));

    first_counter=1;

    counter=0;

  end;

  if Buy_Sell eq 'B' then do;

    counter+1;

    keepit_d(counter)=TRANS_DATE;

    keepit_s(counter)=SHRS;

    output;

  end;

  else do;

    amount=SHRS;

    do until (amount eq 0);

      purchase_date=keepit_d(first_counter);

      shrs=min(amount,keepit_s(first_counter));

      amount=amount-min(amount,keepit_s(first_counter));

      keepit_s(first_counter)=keepit_s(first_counter)-

         shrs;

      output;

      if keepit_s(first_counter) eq 0 then first_counter+1;

    end;

  end;

run;

Super User
Posts: 9,682

Re: how to calculate holding period of stocks?

ArthurT,

Congratulations. Yours are better than mine.

Occasional Contributor
Posts: 5

Re: how to calculate holding period of stocks?

Thanks for your inputs.The logic is working perfectly for buy and sell of one customer for multiple stocks.

However,if more than one customers are buying/selling same stock on same date do we need to change the 'by' clause in code?

Also is length of array a problem while dealing with huge sets of data?

Thanks & Regards,

Shivangi

Occasional Contributor
Posts: 5

Re: how to calculate holding period of stocks?

I have modified 'by' clause and got the desired results.

Also increase in array size solved the issue large data.

Thanks..

Occasional Contributor
Posts: 5

Re: how to calculate holding period of stocks?

Thanks ArthurT & Ksharp for your valuable inputs.Those are very much helpful..

Regards,

Shivangi naik

Respected Advisor
Posts: 3,124

Re: how to calculate holding period of stocks?

Here is one-step Hash() approach. Although 1-step, but it is 1-really-mouthful-step. To get more general solution, raw data has been modified to reflect multiple stocks. Results are matching Art's, but not Ksharp's.

data have;

infile cards expandtabs;

input TRANS_DATE : mmddyy10.     STOCK $ SHRS     Buy_Sell $;

format      TRANS_DATE      mmddyy10.;

cards;

1/30/2009     X     100     B

2/27/2009     X     100     B

3/31/2009     X     400     B

5/29/2009     X     100     B

11/16/2009    X     150     S

12/31/2009    X     190     S

1/11/2010     X     260     S

1/30/2009     Y     100     B

2/27/2009     Y     100     B

3/31/2009     Y     400     B

5/29/2009     Y     100     B

11/16/2009    Y     150     S

12/31/2009    Y     190     S

;

data want;

retain _rc;

  if _n_=1 then do;

    if 0 then set have(rename=(TRANS_DATE=_TRANS_DATE SHRS=_SHRS stock=_stock buy_sell=_buy_sell));

         declare hash h(dataset:'have(rename=(TRANS_DATE=_TRANS_DATE SHRS=_SHRS stock=_stock buy_sell=_buy_sell) where=(_Buy_Sell="B"))', multidata:'y', ordered:'a');

         h.definekey('_stock','_Buy_Sell','_TRANS_DATE');

         h.definedata(all:'y');

         h.definedone();

         declare hiter hi('h');

  end;

do until (last.buy_sell);

  set have;

  by STOCK buy_sell notsorted;

  format Purchase_date mmddyy10.;

  if first.stock then _rc=hi.setcur(key:stock, key:buy_sell,key:TRANS_DATE);      

  if Buy_Sell='B' then do; Purchase_date=TRANS_DATE ;output;end;

  else if Buy_Sell='S' then do; 

     _diff=shrs-_shrs;    

     do while (_diff>0 and _rc=0 and stock=_stock);

                Purchase_date=_TRANS_DATE;

                shrs=_shrs;

                output;

        _rc=hi.next();

        if _diff <= _shrs then do; __shrs=_shrs; _shrs=_diff; _diff=_diff-__shrs;  end;

        else _diff=_diff-_shrs;

     end;

     Purchase_date=_TRANS_DATE;

         shrs=_shrs;

     output;

         _shrs=-_diff;

  end;

end;

drop _:;

run;

Haikuo

Super User
Posts: 9,682

Re: how to calculate holding period of stocks?

HaiKuo,

Your result is unlike Art's,But mine is. I tested it with the data below.

data have;
infile cards expandtabs;
input TRANS_DATE : mmddyy10.     STOCK $ SHRS     Buy_Sell $;
format      TRANS_DATE      mmddyy10.;
cards;
1/30/2009     X     100     B
2/27/2009     X     100     B
3/31/2009     X     40     B
5/29/2009     X     10     B
11/16/2009     X     150     S
12/31/2009     X     190     S
1/11/2010     X     260     S
;
run;

Respected Advisor
Posts: 3,124

Re: how to calculate holding period of stocks?

Keshan,

No offense, but your dummy data is really dummy. OP is interested in the holding period of certain stocks, which I believe that first you need to have it (buy it) before even talking about holding it. So how could you sell what you don't have (selling quantity is more than buying quantity)? It should not occur in real life, and if it does, then an error should be flagged. That being said, if to mimic yours and art's results, a minor tweak to my code will do:

data have;

infile cards expandtabs;

input TRANS_DATE : mmddyy10.     STOCK $ SHRS     Buy_Sell $;

format      TRANS_DATE      mmddyy10.;

cards;

1/30/2009     X 100     B

2/27/2009     X 100     B

3/31/2009     X 40     B

5/29/2009     X 10     B

11/16/2009     X 150     S

12/31/2009     X 190     S

1/11/2010     X 260     S

;

run;

data want;

retain _rc;

  if _n_=1 then do;

    if 0 then set have(rename=(TRANS_DATE=_TRANS_DATE SHRS=_SHRS stock=_stock buy_sell=_buy_sell));

         declare hash h(dataset:'have(rename=(TRANS_DATE=_TRANS_DATE SHRS=_SHRS stock=_stock buy_sell=_buy_sell) where=(_Buy_Sell="B"))', multidata:'y', ordered:'a');

         h.definekey('_stock','_Buy_Sell','_TRANS_DATE');

         h.definedata(all:'y');

         h.definedone();

         declare hiter hi('h');

  end;

do until (last.buy_sell);

  set have;

  by STOCK buy_sell notsorted;

  format Purchase_date mmddyy10.;

  if first.stock then _rc=hi.setcur(key:stock, key:buy_sell,key:TRANS_DATE);      

  if Buy_Sell='B' then do; Purchase_date=. ;output;end;

  else if Buy_Sell='S' then do

     _diff=shrs-_shrs;    

     do while (_diff>0 and _rc=0 and stock=_stock);

                Purchase_date=_TRANS_DATE;

                shrs=_shrs;

                output;

        _rc=hi.next();

        if _rc=0 then do; if _diff <= _shrs then do; __shrs=_shrs; _shrs=_diff; _diff=_diff-__shrs; end;

                          else _diff=_diff-_shrs;

            end;

     end;

       if _rc=0 then do;

         Purchase_date=_TRANS_DATE;

         shrs=_shrs;

         output;

         _shrs=-_diff;

     end;

     else do; _shrs=0;shrs=_diff;Purchase_date=.;output;end;

  end;

end;

drop _:;

run;

Thanks for all these intellectual exchange!

Haikuo

Super User
Posts: 9,682

Re: how to calculate holding period of stocks?

HaiKuo,

Don't forget to notice the date.

1/30/2009     X     100     B

2/27/2009     X     100     B

3/31/2009     X     40     B

5/29/2009     X     10     B

11/16/2009     X     150     S

12/31/2009     X     190     S

1/11/2010     X     260     S

we sell 150 at 11/16/2009 , until then we already have 100+100+40+10 stocks, We surely can sell it now.

Respected Advisor
Posts: 3,124

Re: how to calculate holding period of stocks?

Hi Keshan,

I deleted my previous mumble jumble post. You are right about the date. I agree that even though there is still something left to desired (isn't always like this?), you got yourself reasonable approach. I hope that this wouldn't happen in real life, and if it(selling > buying) does,, the data owner should be warned.

Haikuo

Super User
Posts: 9,682

Re: how to calculate holding period of stocks?

Hi Bian Hai Kuo,

Agree. In the real life , it could not happen. but sometime,somewhere,someone maybe give us such messy data , You aren't able to prevent it occurring ,are you? We just make sure our code as strong as it is possible.

Frequent Contributor
Posts: 78

Re: how to calculate holding period of stocks?

You can sell stock you don't own, it's called "short selling" and it's done all the time.  You have to locate and borrow the shares first (your broker does this).  The goal is always "buy low and sell high", but not necessarily in that order.

Ask a Question
Discussion stats
  • 19 replies
  • 991 views
  • 7 likes
  • 7 in conversation