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
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
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!
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 |
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
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
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!
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
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
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;
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;
"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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.