Need help in return calculation for multiple accounts with multiple transactions

[bd_user_10]

Hi Everyone,

I need to calculate the returns for following transactions on a FIFO (first in first out) basis:

SEDOL	trade_date	Buy_volume	Sell_volume	Buy_price	Sell_price
2426299	15/11/2006	706000	0	28.32	0
2426299	24/11/2006	0	350000	0	28.24
2426299	20/12/2006	300000	0	28.89	0
2426299	2/01/2007	260000	0	29.17	0
2426299	26/01/2007	0	300000	0	29.57
2426299	8/02/2007	0	356000	0	30.04
2426299	2/03/2007	0	130000	0	29.51
2426299	5/03/2007	0	130000	0	28.91
2426299	15/03/2012	133000	0	22.7	0
2426299	29/03/2012	0	133000	0	22.07
2426300	16/11/2006	500000	0	5.5	0
2426300	20/11/2006	0	150000	0	5.9
2426300	12/12/2006	175000	0	5.7	0
2426300	5/01/2007	0	225000	0	6.1
2426300	16/01/2007	0	200000	0	6.12
2426300	18/02/2007	160000	0	6.07	0
2426300	2/03/2007	0	60000	0	6.11
2426300	15/03/2007	0	100000	0	6.13
2426300	25/03/2012	120000	0	6.11	0
2426300	29/03/2012	0	220000	0	6.21

Can someone please help with the SAS code? Thanks in advance for your help.

[ChrisNZ]

1. Provide you data in a SAS program, so we don't have to do this for you

See

How to convert datasets to data steps
The macro for direct download

2. Provide the expected output and the logic

[PGStats]

Using arrays:

data have;
input SEDOL trade_date :ddmmyy10. Buy_volume Sell_volume Buy_price Sell_price;
format trade_date yymmdd10.;
datalines;
2426299	15/11/2006	706000	0	28.32	0
2426299	24/11/2006	0	250000	0	28.24
2426299	20/12/2006	300000	0	28.89	0
2426299	2/01/2007	260000	0	29.17	0
2426299	26/01/2007	0	300000	0	29.57
2426299	8/02/2007	0	356000	0	30.04
2426299	2/03/2007	0	130000	0	29.51
2426299	5/03/2007	0	130000	0	28.91
2426299	15/03/2012	133000	0	22.7	0
2426299	29/03/2012	0	133000	0	22.07
2426300	16/11/2006	500000	0	5.5	0
2426300	20/11/2006	0	150000	0	5.9
2426300	12/12/2006	175000	0	5.7	0
2426300	5/01/2007	0	225000	0	6.1
2426300	16/01/2007	0	200000	0	6.12
2426300	18/02/2007	160000	0	6.07	0
2426300	2/03/2007	0	60000	0	6.11
2426300	15/03/2007	0	100000	0	6.13
2426300	25/03/2012	120000	0	6.11	0
2426300	29/03/2012	0	220000	0	6.21
;

data want;
array p{999};
array v{999};
bCount = 0;
return = 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});
        return = return + rs*p{i};
        v{i} = v{i} - rs;
        sv = sv - rs;
        end;
    output;
    end;
keep SEDOL trade_date Buy_volume Sell_volume Buy_price Sell_price return;
run;

[bd_user_10]

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.

[PGStats · Accepted solution]

I get slightly different results with:

data want;
array p{999};
array v{999};
bCount = 0;
do until(last.sedol);
    set have; by sedol;
    originalBuyValue = 0;
    sellValue = 0;
    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;
            put "Note: Short selling of " SEDOL " on " trade_date;
            leave;
            end;
        rs = min(sv, v{i});
        originalBuyValue = originalBuyValue + rs*p{i};
        sellValue = sellValue + rs*Sell_price;
        v{i} = v{i} - rs;
        sv = sv - rs;
        end;
    if originalBuyValue > 0 then 
        return = (sellValue - originalBuyValue) / originalBuyValue;
    else call missing(return);
    output;
    end;
keep SEDOL trade_date Buy_volume Sell_volume Buy_price 
    Sell_price return sellValue originalBuyValue;
run;

                        Buy_    Sell_   Buy_  Sell_  original      sell
   SEDOL   trade_date  volume  volume  price  price  BuyValue     Value   return

  2426299  2006-11-15  706000       0  28.32   0.00         0         0    .
  2426299  2006-11-24       0  250000   0.00  28.24   7080000   7060000  -0.00282
  2426299  2006-12-20  300000       0  28.89   0.00         0         0    .
  2426299  2007-01-02  260000       0  29.17   0.00         0         0    .
  2426299  2007-01-26       0  300000   0.00  29.57   8496000   8871000   0.04414
  2426299  2007-02-08       0  356000   0.00  30.04  10195920  10694240   0.04887
  2426299  2007-03-02       0  130000   0.00  29.51   3764100   3836300   0.01918
  2426299  2007-03-05       0  130000   0.00  28.91   3792100   3758300  -0.00891
  2426299  2012-03-15  133000       0  22.70   0.00         0         0    .
  2426299  2012-03-29       0  133000   0.00  22.07   3666100   2935310  -0.19934
  2426300  2006-11-16  500000       0   5.50   0.00         0         0    .
  2426300  2006-11-20       0  150000   0.00   5.90    825000    885000   0.07273
  2426300  2006-12-12  175000       0   5.70   0.00         0         0    .
  2426300  2007-01-05       0  225000   0.00   6.10   1237500   1372500   0.10909
  2426300  2007-01-16       0  200000   0.00   6.12   1115000   1224000   0.09776
  2426300  2007-02-18  160000       0   6.07   0.00         0         0    .
  2426300  2007-03-02       0   60000   0.00   6.11    342000    366600   0.07193
  2426300  2007-03-15       0  100000   0.00   6.13    592200    613000   0.03512
  2426300  2012-03-25  120000       0   6.11   0.00         0         0    .
  2426300  2012-03-29       0  220000   0.00   6.21   1340200   1366200   0.01940

[bd_user_10]

These codes work fantastically!

[Ksharp]

You didn't post the output yet .

data have;
input SEDOL trade_date :ddmmyy10. Buy_volume Sell_volume Buy_price Sell_price;

buy=Buy_volume * Buy_price ;
sell=Sell_volume * Sell_price ;

format trade_date yymmdd10.;
datalines;
2426299	15/11/2006	706000	0	28.32	0
2426299	24/11/2006	0	250000	0	28.24
2426299	20/12/2006	300000	0	28.89	0
2426299	2/01/2007	260000	0	29.17	0
2426299	26/01/2007	0	300000	0	29.57
2426299	8/02/2007	0	356000	0	30.04
2426299	2/03/2007	0	130000	0	29.51
2426299	5/03/2007	0	130000	0	28.91
2426299	15/03/2012	133000	0	22.7	0
2426299	29/03/2012	0	133000	0	22.07
2426300	16/11/2006	500000	0	5.5	0
2426300	20/11/2006	0	150000	0	5.9
2426300	12/12/2006	175000	0	5.7	0
2426300	5/01/2007	0	225000	0	6.1
2426300	16/01/2007	0	200000	0	6.12
2426300	18/02/2007	160000	0	6.07	0
2426300	2/03/2007	0	60000	0	6.11
2426300	15/03/2007	0	100000	0	6.13
2426300	25/03/2012	120000	0	6.11	0
2426300	29/03/2012	0	220000	0	6.21
;

data want;
 set have;
 by SEDOL;
 if first.SEDOL then call missing(cum_sell,cum_buy);
 cum_buy+buy;
 cum_sell+sell;
 return=cum_sell-cum_buy;
run;