## LIFO logic for calculating return from shares held

Solved
Occasional Contributor
Posts: 9

# 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 Date Flag Shares Price 1-Jul-15 Buy 100 505 1-Jul-15 Buy 25 520 1-Jul-15 Sell -125 530 2-Jul-15 Buy 50 480 6-Jul-15 Buy 25 492 8-Jul-15 Sell -25 475 10-Jul-15 Buy 50 503 15-Jul-15 Sell -50 515 21-Jul-15 Sell -50 540

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 Date Flag Shares Price LIFO based Matching Price 1-Jul-15 Buy 100 505 1-Jul-15 Buy 25 520 1-Jul-15 Sell -125 530 (25*520+100*505)/125 2-Jul-15 Buy 50 480 6-Jul-15 Buy 25 492 8-Jul-15 Sell -25 475 25*492/25 10-Jul-15 Buy 50 503 15-Jul-15 Sell -50 515 50*503/50 21-Jul-15 Sell -50 540 50*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: 2,370

## Re: LIFO logic for calculating return from shares held

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    Sell   -50    530

A 1-Jul-15    Sell   -75    530

A 8-Jul-15    Sell   -25    475

A 15-Jul-15   Sell   -50    515

A 21-Jul-15   Sell   -50    540

B 1-Jul-15    Sell   -50    530

B 1-Jul-15    Sell   -75    530

B 8-Jul-15    Sell   -25    475

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;

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;

 DATE FLAG VOLUME PRICE LIFO_PRICE 1-Jul-15 Buy 100 505 . 1-Jul-15 Buy 25 520 . 1-Jul-15 Sell -50 530 512.5 1-Jul-15 Sell -75 530 505 2-Jul-15 Buy 50 480 . 6-Jul-15 Buy 25 492 . 8-Jul-15 Sell -25 475 492 10-Jul-15 Buy 50 503 . 15-Jul-15 Sell -50 515 503 21-Jul-15 Sell -50 540 480

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

All Replies
Super User
Posts: 10,784

## Re: LIFO logic for calculating return from shares held

What if you can't find a perfect one ?

 Trade Date Flag Shares Price 1-Jul-15 Buy 100 505 1-Jul-15 Buy 25 520 1-Jul-15 Sell -50 530
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: 2,370

## Re: LIFO logic for calculating return from shares held

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    Sell   -50    530

A 1-Jul-15    Sell   -75    530

A 8-Jul-15    Sell   -25    475

A 15-Jul-15   Sell   -50    515

A 21-Jul-15   Sell   -50    540

B 1-Jul-15    Sell   -50    530

B 1-Jul-15    Sell   -75    530

B 8-Jul-15    Sell   -25    475

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;

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;

 DATE FLAG VOLUME PRICE LIFO_PRICE 1-Jul-15 Buy 100 505 . 1-Jul-15 Buy 25 520 . 1-Jul-15 Sell -50 530 512.5 1-Jul-15 Sell -75 530 505 2-Jul-15 Buy 50 480 . 6-Jul-15 Buy 25 492 . 8-Jul-15 Sell -25 475 492 10-Jul-15 Buy 50 503 . 15-Jul-15 Sell -50 515 503 21-Jul-15 Sell -50 540 480

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

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..!!

Hates off to you Sir..    Big Thanks..

It will really help me.

Thanks & Regards,

Vishal Kapasi

PROC Star
Posts: 2,370

## Re: LIFO logic for calculating return from shares held

```Hates off to you Sir..
```

I hope not !

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,784

## Re: LIFO logic for calculating return from shares held

```data have;
input id \$ DATE anydtdte.  FLAG \$ share price;
format DATE date9.;
cards;
A 1-Jul-15    Sell   -50    530
A 1-Jul-15    Sell   -75    530
A 8-Jul-15    Sell   -25    475
A 15-Jul-15   Sell   -50    515
A 21-Jul-15   Sell   -50    540
B 1-Jul-15    Sell   -50    530
B 1-Jul-15    Sell   -75    530
B 8-Jul-15    Sell   -25    475
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: 2,370

## 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    Sell -50    530

A 1-Jul-15    Sell -75    530

A 8-Jul-15    Sell -25    475

A 15-Jul-15   Sell   -50 515

A 21-Jul-15   Sell   -50 540

B 1-Jul-15    Sell -50    530

B 1-Jul-15    Sell -75    530

B 8-Jul-15    Sell -25    475

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

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;

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;

end;

else do;                                            %* price calculation can be completed;

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

end;

end;

end;

drop VOLUME_TALLY RC SUM N _: ;

run;

Super User
Posts: 10,784

## 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.