DATA Step, Macro, Functions and more

LIFO logic for calculating return from shares held

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

LIFO logic for calculating return from shares held

Dear All,

Greetings for the day...

Well, I want to pair my Sell transactions with Buy transactions considering LIFO logic. This means, whenever I sell shares I want to know its corresponding BUY, who were last bought prior to my Sell.

For example, On 1 st of July, I bought 100, and 25 shares, and sold all 125 shares, So prior to selling 125 shares, my last bought 125 shares were also on 1st of July. So matching price will consider its weighted buy price in desired output. Please find my Input Data in below table.

Trade DateFlagSharesPrice
1-Jul-15Buy100505
1-Jul-15Buy25520
1-Jul-15Sell-125530
2-Jul-15Buy50480
6-Jul-15Buy25492
8-Jul-15Sell-25475
10-Jul-15Buy50503
15-Jul-15Sell-50515
21-Jul-15Sell-50540

Please find below table for Desired output.

Here, in case of 8July Sell, I had bought prior to 8July is  50 shares on 2nd and 25 shares on 6July. Since my last 25 shares bought is for 6July, it will be matched considering LIFO logic and its price will be reflected in my matching price output.

Trade DateFlagSharesPriceLIFO based Matching Price
1-Jul-15Buy100505
1-Jul-15Buy25520
1-Jul-15Sell-125530(25*520+100*505)/125
2-Jul-15Buy50480
6-Jul-15Buy25492
8-Jul-15Sell-2547525*492/25
10-Jul-15Buy50503
15-Jul-15Sell-5051550*503/50
21-Jul-15Sell-5054050*480/50

Requesting for sharing appropriate code considering this LIFO logic where I can get my desired output.

Thanks & Regards,

Vishal



Accepted Solutions
Solution
‎07-29-2015 06:39 PM
PROC Star
Posts: 1,760

Re: LIFO logic for calculating return from shares held

Posted in reply to VISHALKAPASI

Nothing like a brain teaser to start the day.

data T;

input SHARE $ DATE anydtdte.  FLAG $ VOLUME PRICE;

format DATE date9.;

cards;

A 1-Jul-15    Buy    100    505

A 1-Jul-15    Buy    25     520

A 1-Jul-15    Sell   -50    530

A 1-Jul-15    Sell   -75    530

A 2-Jul-15    Buy    50     480

A 6-Jul-15    Buy    25     492

A 8-Jul-15    Sell   -25    475

A 10-Jul-15   Buy    50     503

A 15-Jul-15   Sell   -50    515

A 21-Jul-15   Sell   -50    540

B 1-Jul-15    Buy    100    505

B 1-Jul-15    Buy    25     520

B 1-Jul-15    Sell   -50    530

B 1-Jul-15    Sell   -75    530

B 2-Jul-15    Buy    50     480

B 6-Jul-15    Buy    25     492

B 8-Jul-15    Sell   -25    475

B 10-Jul-15   Buy    50     503

B 15-Jul-15   Sell   -50    515

B 21-Jul-15   Sell   -50    540

run;

data S;

  array VOLUMES [10] _temporary_;

  array PRICES  [10] _temporary_;

  array LIFO    [10] _temporary_;

  %* populate arrays;

  do until (last.SHARE);                        

    set T ; 

    by SHARE;

    TRANSACTION+1;

    VOLUMES[TRANSACTION]=VOLUME;

    PRICES [TRANSACTION]=PRICE;

  end;

  %* process array for this share;

  do FWD=1 to TRANSACTION;

    if VOLUMES[FWD] < 0 then do ;                   %* we have a sell;

      VOLUME=-VOLUMES[FWD];                       %* store sell volume;

      PRICE =0;                                   %* store cumulated price;

      do BACK=FWD-1 to 1 by -1 until (LIFO[FWD]);     %* look back to find previous buys;

        if VOLUMES[BACK]<= 0 then continue;           %* not a buy ;      

        if VOLUMES[BACK] < -VOLUMES[FWD] then do;     %* volume too small to complete price calculation;

          PRICE+PRICES[BACK]*VOLUMES[BACK];       %* update buy price with this volume;         

          VOLUMES[FWD]+VOLUMES[BACK];             %* reset tally for sell volume;

          VOLUMES[BACK]=0;                        %* reset tally for buy volume;

        end;     

        if VOLUMES[BACK] >= -VOLUMES[FWD] then do;%* price calculation can be completed;

          PRICE+PRICES[BACK]*-VOLUMES[FWD];       %* update buy price;        

          VOLUMES[BACK]+VOLUMES[FWD];             %* reset tally for buy volume;

          LIFO[FWD]=PRICE/VOLUME;                 %* calculate lifo price;

        end;

      end;

    end

  end;

  %* save LIFO prices;

  do I=1 to TRANSACTION;

    set T;                                 

    LIFO_PRICE=LIFO;

    output;

  end;

  call missing (TRANSACTION, of VOLUMES

  • , of PRICES
  • , of LIFO
  • );
  •   keep SHARE DATE FLAG VOLUME PRICE LIFO_PRICE;

    run;

    DATEFLAGVOLUMEPRICELIFO_PRICE
    1-Jul-15Buy100505.
    1-Jul-15Buy25520.
    1-Jul-15Sell-50530512.5
    1-Jul-15Sell-75530505
    2-Jul-15Buy50480.
    6-Jul-15Buy25492.
    8-Jul-15Sell-25475492
    10-Jul-15Buy50503.
    15-Jul-15Sell-50515503
    21-Jul-15Sell-50540480

    Now do this in SQL or using drag'n'drop.. Ha!  Smiley Happy

    View solution in original post


    All Replies
    Super User
    Posts: 10,046

    Re: LIFO logic for calculating return from shares held

    Posted in reply to VISHALKAPASI

    What if you can't find a perfect one ?

    Trade DateFlagSharesPrice
    1-Jul-15Buy100505
    1-Jul-15Buy25520
    1-Jul-15Sell-50530
    Occasional Contributor
    Posts: 9

    Re: LIFO logic for calculating return from shares held

    In the above case where perfect qty not found, LIFO logic will consider last bought 50 shares prior to sell. So matching price will be calculated as (25*520+25*505)/50.

    Thanks for the revert.

    Regards,

    Vishal

    Solution
    ‎07-29-2015 06:39 PM
    PROC Star
    Posts: 1,760

    Re: LIFO logic for calculating return from shares held

    Posted in reply to VISHALKAPASI

    Nothing like a brain teaser to start the day.

    data T;

    input SHARE $ DATE anydtdte.  FLAG $ VOLUME PRICE;

    format DATE date9.;

    cards;

    A 1-Jul-15    Buy    100    505

    A 1-Jul-15    Buy    25     520

    A 1-Jul-15    Sell   -50    530

    A 1-Jul-15    Sell   -75    530

    A 2-Jul-15    Buy    50     480

    A 6-Jul-15    Buy    25     492

    A 8-Jul-15    Sell   -25    475

    A 10-Jul-15   Buy    50     503

    A 15-Jul-15   Sell   -50    515

    A 21-Jul-15   Sell   -50    540

    B 1-Jul-15    Buy    100    505

    B 1-Jul-15    Buy    25     520

    B 1-Jul-15    Sell   -50    530

    B 1-Jul-15    Sell   -75    530

    B 2-Jul-15    Buy    50     480

    B 6-Jul-15    Buy    25     492

    B 8-Jul-15    Sell   -25    475

    B 10-Jul-15   Buy    50     503

    B 15-Jul-15   Sell   -50    515

    B 21-Jul-15   Sell   -50    540

    run;

    data S;

      array VOLUMES [10] _temporary_;

      array PRICES  [10] _temporary_;

      array LIFO    [10] _temporary_;

      %* populate arrays;

      do until (last.SHARE);                        

        set T ; 

        by SHARE;

        TRANSACTION+1;

        VOLUMES[TRANSACTION]=VOLUME;

        PRICES [TRANSACTION]=PRICE;

      end;

      %* process array for this share;

      do FWD=1 to TRANSACTION;

        if VOLUMES[FWD] < 0 then do ;                   %* we have a sell;

          VOLUME=-VOLUMES[FWD];                       %* store sell volume;

          PRICE =0;                                   %* store cumulated price;

          do BACK=FWD-1 to 1 by -1 until (LIFO[FWD]);     %* look back to find previous buys;

            if VOLUMES[BACK]<= 0 then continue;           %* not a buy ;      

            if VOLUMES[BACK] < -VOLUMES[FWD] then do;     %* volume too small to complete price calculation;

              PRICE+PRICES[BACK]*VOLUMES[BACK];       %* update buy price with this volume;         

              VOLUMES[FWD]+VOLUMES[BACK];             %* reset tally for sell volume;

              VOLUMES[BACK]=0;                        %* reset tally for buy volume;

            end;     

            if VOLUMES[BACK] >= -VOLUMES[FWD] then do;%* price calculation can be completed;

              PRICE+PRICES[BACK]*-VOLUMES[FWD];       %* update buy price;        

              VOLUMES[BACK]+VOLUMES[FWD];             %* reset tally for buy volume;

              LIFO[FWD]=PRICE/VOLUME;                 %* calculate lifo price;

            end;

          end;

        end

      end;

      %* save LIFO prices;

      do I=1 to TRANSACTION;

        set T;                                 

        LIFO_PRICE=LIFO;

        output;

      end;

      call missing (TRANSACTION, of VOLUMES

  • , of PRICES
  • , of LIFO
  • );
  •   keep SHARE DATE FLAG VOLUME PRICE LIFO_PRICE;

    run;

    DATEFLAGVOLUMEPRICELIFO_PRICE
    1-Jul-15Buy100505.
    1-Jul-15Buy25520.
    1-Jul-15Sell-50530512.5
    1-Jul-15Sell-75530505
    2-Jul-15Buy50480.
    6-Jul-15Buy25492.
    8-Jul-15Sell-25475492
    10-Jul-15Buy50503.
    15-Jul-15Sell-50515503
    21-Jul-15Sell-50540480

    Now do this in SQL or using drag'n'drop.. Ha!  Smiley Happy

    Occasional Contributor
    Posts: 9

    Re: LIFO logic for calculating return from shares held

    Dear Chris,

    Great.!!  You have created LIFO logic code in a Flash.. Amazing..!! Smiley Happy

    Hates off to you Sir..    Big Thanks..

    It will really help me.

    Thanks & Regards,

    Vishal Kapasi

    PROC Star
    Posts: 1,760

    Re: LIFO logic for calculating return from shares held

    Posted in reply to VISHALKAPASI

    Hates off to you Sir..

    I hope not !  Smiley Wink

    Occasional Contributor
    Posts: 9

    Re: LIFO logic for calculating return from shares held

    Dear Chris,

    I meant Hats off to you Sir, I am amazed with your prompt n perfect reply.

    Pls forgive me for d spelling mistake.

    Thanks &Regards,

    Vishal

    Super User
    Posts: 10,046

    Re: LIFO logic for calculating return from shares held

    Posted in reply to VISHALKAPASI
    data have;
    input id $ DATE anydtdte.  FLAG $ share price;
    format DATE date9.;
    cards;
    A 1-Jul-15    Buy    100    505
    A 1-Jul-15    Buy    25     520
    A 1-Jul-15    Sell   -50    530
    A 1-Jul-15    Sell   -75    530
    A 2-Jul-15    Buy    50     480
    A 6-Jul-15    Buy    25     492
    A 8-Jul-15    Sell   -25    475
    A 10-Jul-15   Buy    50     503
    A 15-Jul-15   Sell   -50    515
    A 21-Jul-15   Sell   -50    540
    B 1-Jul-15    Buy    100    505
    B 1-Jul-15    Buy    25     520
    B 1-Jul-15    Sell   -50    530
    B 1-Jul-15    Sell   -75    530
    B 2-Jul-15    Buy    50     480
    B 6-Jul-15    Buy    25     492
    B 8-Jul-15    Sell   -25    475
    B 10-Jul-15   Buy    50     503
    B 15-Jul-15   Sell   -50    515
    B 21-Jul-15   Sell   -50    540
    ;
    run;
    
    
    data x;
     set have;
     k+1;
     rename share=_s price=_p;
     keep k share price;
    run;
    data want;
     if _n_ eq 1 then do;
      if 0 then set x;
      declare hash ha(dataset:'x');
      ha.definekey('k');
      ha.definedata('_s','_p');
      ha.definedone();
     end;
    set have nobs=nobs;
    k=_n_; _share=share;sum=0;
    if share lt 0 then do;
     ha.remove();
     do i=1 to nobs;
       k=k-1;
       rc=ha.find(); 
       if rc=0 then do;
         _share=_share+_s;
         if _share lt 0 then do; sum+_s*_p;     ha.remove();end;
           else do;
                 dif=_s-_share;
                 sum+dif*_p;
                 _s=_share;
                 ha.replace(); 
               end;
         *put k= _share= _s= _p= sum= dif=;    
       end;
            if _share ge 0 then leave;
     end;
    LIFO=divide(sum,abs(share));
    end;
    drop k i _s _p sum _share dif rc ;
    run;
    
    
    
    PROC Star
    Posts: 1,760

    Re: LIFO logic for calculating return from shares held

    Yes, you can use hash table, which alleviates the need to size the arrays, but they are harder to understand.

    No need to store the sell operations in the hash table.

    Note that at no point do we check the buy volumes are sufficient to cover the sell volumes.

    Why the heck to spaces get wrecked when pasting and ruin the alignment?

    data HAVE;

    input SHARE $ DATE anydtdte.  FLAG $ VOLUME PRICE;

    format DATE date9.;

    N=_N_;

    cards;

    A 1-Jul-15    Buy 100    505

    A 1-Jul-15    Buy 25     520

    A 1-Jul-15    Sell -50    530

    A 1-Jul-15    Sell -75    530

    A 2-Jul-15    Buy 50     480

    A 6-Jul-15    Buy 25     492

    A 8-Jul-15    Sell -25    475

    A 10-Jul-15   Buy    50 503

    A 15-Jul-15   Sell   -50 515

    A 21-Jul-15   Sell   -50 540

    B 1-Jul-15    Buy 100    505

    B 1-Jul-15    Buy 25     520

    B 1-Jul-15    Sell -50    530

    B 1-Jul-15    Sell -75    530

    B 2-Jul-15    Buy 50     480

    B 6-Jul-15    Buy 25     492

    B 8-Jul-15    Sell -25    475

    B 10-Jul-15   Buy    50 503

    B 15-Jul-15   Sell   -50 515

    B 21-Jul-15   Sell   -50 540

    run;

    data WANT;

      if _N_ eq 1 then do;

        declare hash BUYS(dataset:'HAVE(keep   = N VOLUME PRICE

                                      rename = (VOLUME=_V PRICE=_P)

                                      where  = (_V>0))');

      BUYS.definekey('N');

      BUYS.definedata('_V','_P');

      BUYS.definedone();

        call missing( _V, _P);

      end;

      set HAVE ;

      SUM =0;                                             %* store cumulated price;

      VOLUME_TALLY=VOLUME;                                %* store sell volume;

      if VOLUME < 0 then do N=_N_-1 to 1 by -1 until (LIFO); %* we have a sell;

      RC=BUYS.find();                                     %* look back to find previous buys;

        if RC=0 then do;                                    %* found ;

           if _V < -VOLUME_TALLY then do;                    %* volume too small to complete price calculation;

              SUM+_P*_V  ;                                %* update buy price with this volume;        

              VOLUME_TALLY+_V;                            %* reset tally for sell volume;

              BUYS.remove();                              %* remove buy volume;       

            end;

            else do;                                            %* price calculation can be completed;

              SUM+_P*-VOLUME_TALLY;                          %* update buy price;       

              _V+VOLUME_TALLY;   BUYS.replace();             %* reset  buy volume;

              LIFO=SUM/VOLUME;                               %* calculate lifo price;       

            end;

         end;

      end;

      drop VOLUME_TALLY RC SUM N _: ;

    run;

    Super User
    Posts: 10,046

    Re: LIFO logic for calculating return from shares held

    "they are harder to understand"

    But are easier to use ( no need to change the size of array ), right ?

    Write once, Use anytime anywhere.

    Xia Keshan

    🔒 This topic is solved and locked.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 9 replies
    • 664 views
    • 1 like
    • 3 in conversation