topic LIFO logic for calculating return from shares held in SAS Programming

LIFO logic for calculating return from shares held

VISHALKAPASI — Wed, 29 Jul 2015 07:20:35 GMT

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	(*25520+100505)/125*
2-Jul-15	Buy	50	480
6-Jul-15	Buy	25	492
8-Jul-15	Sell	-25	475	*25492/25**
10-Jul-15	Buy	50	503
15-Jul-15	Sell	-50	515	*50503/50**
21-Jul-15	Sell	-50	540	*50480/50**

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

Thanks & Regards,

Vishal

Re: LIFO logic for calculating return from shares held

Ksharp — Wed, 29 Jul 2015 14:35:36 GMT

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

Re: LIFO logic for calculating return from shares held

VISHALKAPASI — Wed, 29 Jul 2015 15:25:17 GMT

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

Re: LIFO logic for calculating return from shares held

ChrisNZ — Wed, 29 Jul 2015 22:39:43 GMT

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;

%* 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!

Re: LIFO logic for calculating return from shares held

VISHALKAPASI — Thu, 30 Jul 2015 06:01:43 GMT

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

Re: LIFO logic for calculating return from shares held

ChrisNZ — Fri, 31 Jul 2015 01:49:08 GMT

Hates off to you Sir..

I hope not !

Re: LIFO logic for calculating return from shares held

Ksharp — Fri, 31 Jul 2015 02:39:53 GMT

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;

Re: LIFO logic for calculating return from shares held

ChrisNZ — Fri, 31 Jul 2015 04:51:35 GMT

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;

drop VOLUME_TALLY RC SUM N _: ;

run;

Re: LIFO logic for calculating return from shares held

Ksharp — Fri, 31 Jul 2015 05:22:03 GMT

"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

Re: LIFO logic for calculating return from shares held

VISHALKAPASI — Fri, 31 Jul 2015 05:47:33 GMT

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