Hi PGStats, Thank you for your codes. Unfortunately, it does not give the return of each trade. Instead, it gives me cumulative sell volume buy price in perfect order! Your codes give me the following output; return SEDOL trade_date Buy_volume Sell_volume Buy_price Sell_price 0 2426299 15/11/2006 706000 0 28.32 0 9912000 2426299 24/11/2006 0 350000 0 28.24 9912000 2426299 20/12/2006 300000 0 28.89 0 9912000 2426299 2/01/2007 260000 0 29.17 0 18408000 2426299 26/01/2007 0 300000 0 29.57 28660920 2426299 8/02/2007 0 356000 0 30.04 32453020 2426299 2/03/2007 0 130000 0 29.51 36245120 2426299 5/03/2007 0 130000 0 28.91 36245120 2426299 15/03/2012 133000 0 22.7 0 39264220 2426299 29/03/2012 0 133000 0 22.07 0 2426300 16/11/2006 500000 0 5.5 0 825000 2426300 20/11/2006 0 150000 0 5.9 825000 2426300 12/12/2006 175000 0 5.7 0 2062500 2426300 5/01/2007 0 225000 0 6.1 3177500 2426300 16/01/2007 0 200000 0 6.12 3177500 2426300 18/02/2007 160000 0 6.07 0 3519500 2426300 2/03/2007 0 60000 0 6.11 4111700 2426300 15/03/2007 0 100000 0 6.13 4111700 2426300 25/03/2012 120000 0 6.11 0 5451900 2426300 29/03/2012 0 220000 0 6.21 However, using your codes i have managed to calculate what i needed. Since i am new in coding i have done it very novice way. Here is what i have done; data want;
array p{999};
array v{999};
bCount = 0;
cum_svol_buy_value = 0;
do until(last.sedol);
set have; by sedol;
if Buy_volume > 0 then do;
bCount = bCount + 1;
p{bCount} = Buy_price;
v{bCount} = Buy_volume;
end;
sv = Sell_volume;
do i = 1 to dim(v) while (sv > 0);
if missing(v{i}) then do;
leave;
end;
rs = min(sv, v{i});
cum_svol_buy_value = cum_svol_buy_value + rs*p{i};
v{i} = v{i} - rs;
sv = sv - rs;
end;
svol_sell_value = Sell_volume*sell_price;
output;
end;
keep SEDOL trade_date Buy_volume Sell_volume Buy_price Sell_price cum_svol_buy_value svol_sell_value;
run;
data want;
set want;
svol_buy_value = cum_svol_buy_value-lag1(cum_svol_buy_value);
run;
data want;
set want;
by SEDOL;
if first.SEDOL then svol_buy_value = Sell_volume*sell_price;
run;
data want;
set want; drop cum_svol_buy_value; return = (svol_sell_value-svol_buy_value)/svol_buy_value; run; These codes give me the following results; SEDOL trade_date Buy_volume Sell_volume Buy_price Sell_price svol_sell_value svol_buy_value return 2426299 15/11/2006 706000 0 28.32 0 0 0 . 2426299 24/11/2006 0 350000 0 28.24 9884000 9912000 -0.00282 2426299 20/12/2006 300000 0 28.89 0 0 0 . 2426299 2/01/2007 260000 0 29.17 0 0 0 . 2426299 26/01/2007 0 300000 0 29.57 8871000 8496000 0.04414 2426299 8/02/2007 0 356000 0 30.04 10694240 10252920 0.04304 2426299 2/03/2007 0 130000 0 29.51 3836300 3792100 0.01166 2426299 5/03/2007 0 130000 0 28.91 3758300 3792100 -0.00891 2426299 15/03/2012 133000 0 22.7 0 0 0 . 2426299 29/03/2012 0 133000 0 22.07 2935310 3019100 -0.02775 2426300 16/11/2006 500000 0 5.5 0 0 0 . 2426300 20/11/2006 0 150000 0 5.9 885000 825000 0.07273 2426300 12/12/2006 175000 0 5.7 0 0 0 . 2426300 5/01/2007 0 225000 0 6.1 1372500 1237500 0.10909 2426300 16/01/2007 0 200000 0 6.12 1224000 1115000 0.09776 2426300 18/02/2007 160000 0 6.07 0 0 0 . 2426300 2/03/2007 0 60000 0 6.11 366600 342000 0.07193 2426300 15/03/2007 0 100000 0 6.13 613000 592200 0.03512 2426300 25/03/2012 120000 0 6.11 0 0 0 . 2426300 29/03/2012 0 220000 0 6.21 1366200 1340200 0.0194 Thanks.
... View more
