I now eve see what you want is a dataset with (usually) one observation per purchased lot of shares. The obs will have the CUSIP, then all the buy transaction variables (B_data=trade_data, B_time=execution_time, etc). If that lot is sold it will also have an analogous set of sell transaction variables (s_date, s_price, s_quantity ...).
If all the shares from a lot is sold, it will have a quantity_available=0. If no shares from a lot is sold, then all the S_ vars will be missing, and that lot will have quantify_available=b_quantity. If a lot is partially sold, than it will have two records. The first will record the sold portion (and will have quantity_available=0), and the second will have the same B_ variables, and all missing S_ variables, and quantity_available=the unsold portion of the lot.
Even through I've written papers using hash-of-hashes for FIFO queues, I think you might be better off maintaining a single hash with both "b_" and "s_" vars for each stock lot, and a second hash with one data item per stock, with the current status (i.e. like total shares currently held, number of buys and sales to date).
In that case, each "buy" establishes a new stock LOT (i.e. a new data item in hash object LOTS). I.e. 3 buys of stock AAA generates three lots, identified by stock/date/price/quantity/source/ (the "b_" vars - b_date, b_price, b_quantity, b_source,...).
But when a subsequent "sell" for the same stock is encountered you want to match it with the oldest LOT not yet sold off - that's the FIFO part. So in addition to the buy-related vars noted above, the lot should also have the same set of variables (date/price/...) from the sell transactions (the "s_" vars).
As a rule:
- When a "buy" is encountered add a new lot, with populated "b_" vars, and missing "s_" vars.
- When a sell is encountered, find the oldest lot with available_shares>0.
- If the sell is for exactly the number in available shares, simply update the lot with the "b_" vars and set available shares to 0.
- If the sell if for more than available shares, do the same as the above, but then proceed to subsequent lots to sell of the unsatisfied portion of the original sell transaction. Update that second lot
- But If the sell is for
leave less than available shares then you have to create a new second "sub-lot" with the unsold portion of the lot, while updating the sold portion setting available shares to 0.
You might have chosen hash-of-hashes so that you can easily find the oldest lot for a given stock. Since each stock has its own hash and corresponding hash iterator, then just using the first method of the iterator gets you to the oldest lot for that stock. If you have a single hash lots sorted by cusip/b_num (b_num is the sequence of the buy transaction), then instead of first method, you can use the setcur method, with arguments cusip=cusip, anb b_num=1.
Here is a working program:
proc datasets library=work noprint;
delete _: ;
quit;
Data have;
Input Cusip $ Trade_Date mmddyy10. Execution_Time time9. Execution_Price Quantity Buy_Sell $1. Account_Number2 Source Trade_ID In14;
format trade_date yymmddn8. execution_time time8.0;
datalines;
A62 05/06/2013 11:46:58 100.1 100 B 602 1 135 1
A62 05/06/2013 12:23:10 100.1 250 B 352 3 320 1
A62 05/06/2013 15:09:02 100.1 150 B 602 1 616 1
A62 06/07/2013 14:48:28 99.854 500 S 176 3 065 0
A70 05/06/2013 11:47:53 100.1 150 B 602 1 287 1
A70 05/06/2013 12:23:50 100.1 250 B 352 3 326 1
A70 05/06/2013 15:09:58 100.1 100 B 602 1 626 1
A70 05/14/2013 11:58:52 100.028 715 B 161 3 251 1
A70 05/20/2013 12:18:25 100.738 1215 S 176 3 628 0
A88 05/06/2013 11:48:44 100.1 150 B 602 1 290 1
A88 05/06/2013 12:24:29 100.1 250 B 352 3 345 1
A88 05/06/2013 15:10:38 100.1 100 B 602 1 643 1
A88 05/20/2013 12:54:53 100.699 500 S 176 3 165 0
A96 05/06/2013 11:49:21 100.1 135 B 602 1 291 1
A96 05/06/2013 12:25:01 100.1 140 B 352 3 348 1
A96 06/18/2013 16:04:55 98.257 275 S 176 3 466 0
B20 05/06/2013 11:51:16 100.1 150 B 602 1 292 1
B20 05/06/2013 15:51:57 100.1 80 B 602 1 268 1
B20 06/27/2013 09:48:19 93.6 230 S 176 3 181 0
B38 05/03/2013 15:50:05 100.1 100 B 549 3 921 1
B38 05/06/2013 11:52:02 100.1 110 B 602 1 899 1
B38 05/20/2013 16:55:36 100.375 210 S 176 3 868 0
L55 03/07/2014 11:24:32 100.05 500 B 398 2 424 1
L55 03/07/2014 13:00:45 100 500 B 793 3 953 1
L55 03/11/2014 12:07:02 100.239 75 S 176 3 716 0
run;
data _null_;
set have end=end_of_have;
length lot_cusip $8 /* The CUSIP var for use in the LOTS hash object */
shrs_held N_buys N_sells /* Vars for the stock_profile hash */
lot_sold /* Actual lot sales for this Sales transaction */
lot_avail /* Current share available in a LOT after sale */
8;
array trdata {*} trade_date execution_time quantity execution_price account_number2 source trade_id in14;
array b_data {*} b_date b_time b_quantity b_price b_acct b_source b_tid b_in14;
array s_data {*} s_date s_time s_quantity s_price s_acct s_source s_tid s_in14;
format b_date: s_date: yymmddn8. b_time s_time time8.0;
if _n_=1 then do;
declare hash stock_profile();
stock_profile.definekey('cusip');
stock_profile.definedata('cusip','shrs_held','N_buys','N_sells'); /*Number of Buys, Sells*/
stock_profile.definedone();
declare hash lots(ordered:'A');
lots.definekey('lot_cusip','b_num');
lots.definedata('lot_cusip','b_num','s_num');
lots.definedata('b_date','b_time','b_quantity','b_price','b_acct','b_source','b_tid','b_in14');
lots.definedata('s_date','s_time','s_quantity','s_price','s_acct','s_source','s_tid','s_in14');
lots.definedata('lot_sold','lot_avail');
lots.definedone();
declare hiter ilots('lots');
end;
rc=stock_profile.find(); /*Get current status of the incoming CUSIP */
if (rc^=0 or shrs_held in (.,0)) and buy_sell='S' then do;
if end_of_have=0 then delete; /*Avoid premature sales*/
else goto eoh;
end;
else if buy_sell='B' then do;
N_buys=sum(N_buys,1);
shrs_held=sum(shrs_held,quantity);
rc=stock_profile.replace();
lot_cusip=cusip;
b_num=n_buys;
lot_sold=0;
lot_avail=quantity;
do v=1 to dim(trdata); b_data{v}=trdata{v}; end;
rc=lots.add();
end;
else if buy_sell='S' then do;
N_sells=sum(N_sells,1);
total_shrs_to_sell=min(quantity,shrs_held);
/* Now step through the lots looking for shares availble to sell */
do rc=ilots.setcur(key:cusip,key:1) by 0 until(total_shrs_to_sell=0 or lot_cusip^=cusip or rc^=0);
if lot_avail>0 then do;
orig_lot_avail=lot_avail;
s_num=n_sells;
do v=1 to dim(trdata); s_data{v}=trdata{v}; end;
lot_sold=min(lot_avail,total_shrs_to_sell);
lot_avail=0;
rc=lots.replace();
total_shrs_to_sell=total_shrs_to_sell-lot_sold; /*Update the sales "order" */
shrs_held=shrs_held-lot_sold; /*Update current holdings */
if lot_sold<orig_lot_avail then do; /*If the LOT was not emptied by this sale ...*/
b_num=b_num+.01; /*Create a "sublot" (=lot+.01) with remaining avaiable shrs*/
lot_avail=orig_lot_avail-lot_sold;
lot_sold=0;
call missing(of s_:);
rc=lots.add();
end;
end;
rc=ilots.next();
end;
rc=stock_profile.replace(); /*Store updated holdings*/
end;
if end_of_have ;
eoh:
rc=stock_profile.output(dataset:'stock_profile');
rc=lots.output(dataset:'want');
run;
notes:
- Your data has to be sorted by TRADE_DATE, but it doesn't have to be grouped by CUSIP
- Note that premature sales (i.e. first transaction is an "S", or the shares_held is already at zero), that record is entirely ignored.
- If, say, lot 2 for cusip AAA starts with 200 shares, of which only 50 shares are sold, then there will be two records:
- Record with B_NUM=2.0 and lot_available=0, and lot_sold=50
- Record with B_NUM=2.01 and lot_available=150 and lot_sold=0
I suspect this will be just as flexible as the hash-of-hashes approach, and probably takes somewhat less memory. Plus it allows you to easily do a bulk output of all the records for all the cusips.
Click image to register for webinar
