topic Re: calculate stock cost based on FIFO in New SAS User

calculate stock cost based on FIFO

Xiyuan — Sun, 26 Dec 2021 14:46:59 GMT

Hi all , here's the problem:

I want to calculate the purchase cost of stock in my account following FIFO (first in first out), actually this can be a large panel dataset because I can have many stocks and adjust their position persistently. The figure below is an example with only 1 stock:

date 1: I buy 100 shares, so holding 100 shares, the price is 20, so the cost is 20, too

date 2: I continue to buy 100 shares, so holding 200 shares, the price is 23.3, cost is the average

date 3: I sell 120 shares, 100 of 120 sold is bought on date1, 20 of 120 sold is bought on date2, so the remaining 80 also belongs to date2, the cost is 23.3

date 4: I buy 20 shares, now i hold 100, 20 of 100 is newly bought with price 23, 80 of 100 is remained on date3 and bought on date2 with price 23.3, so the cost is weighted average 23.16

date 5: I sell 90, the remaining 10 is bought on date4, so cost is 24, the same with price on date4

It seems a little wordy, but I also want to say that the actual time range is longer and there are more stocks. I hope there is a general solution. thank you! happy Christmas!

Date	Trade	Holding	Price	Cost
0	+100 (initial buy)	100	20	(100∗20)/100=20
1	+100	200	23.2	(100∗20+100∗23.2)/200=21.6
2	-120	80	22	(80∗23.2)/80=23.2
3	+20	100	23	(20∗23+80∗23.2)/100=23.16
4	-90	10	24	(10∗23)/10=23

Re: calculate stock cost based on FIFO

kelxxx — Sun, 26 Dec 2021 21:07:15 GMT

I hope this can help you. My idea is create a new table and insert line by line.

Sorry, i dont know how to explain all in english. and I hope I understand your question correctly

*-------------------------------;
* - 1 - Préparation				;
*-------------------------------;
data have						;
input date trade holding price	;
cards							;
0 100 100 20
1 100 200 23.2
2 120 -80 22
3 20 100 23
4 -90 10 24
;
run								;

*---------------------------------------------------------------;
* - 2 - Macro 													;
*---------------------------------------------------------------;
%macro stock(date,trade,price)									;
%if &trade > 0 %then %do										; 	/* si c'est l'achat*/
	data tem_last_transaction									; 
	set stock end=eof; if eof then do							;	/* récupérer la dernière ligne de la table stock pour les infos suivantes*/	
	call symputx('lhold',holding); call symputx('lcost',cost)	;	/* &lhold: quantité dans le stock, &lcost: prix moyen */
	output; end; run											;	
	%let holding=%sysevalf(&lhold+&trade)						;	/* calculer holding*/
	%let cost=%sysevalf((&lcost*&lhold+&trade*&price)/&holding)	;	/* calculer cost*/
	%put &holding &cost											;
	proc sql;
	insert into suivi values(&date,&trade,&holding,&price,&cost);	/* insérer les résultats dans la table suivi*/
	insert into stock values(&date,&trade,&holding,&price,&cost);	/* et stock*/
	quit														;
%end															;

%else %do														;	/* si c'est vente */	
	data stock													;
	set stock(where=(holding>abs(&trade)))						;	/* récuperer seulement les lignes ou holding > quantité vente(abs(trade)), parceque si holding < trade >>> il sera soldé*/
	holding=holding+&trade										;	/* màj holding*/	
	cost1=lag(cost)												;	/* cost de la ligne n-1*/
	holding1=lag(holding)										;	/* holding de la ligne n-1*/
	if _n_=1 then do											;	/* si c'est la première ligne _n_=1*/
	trade=holding												;	/* >>> trade= holding*/
	cost=price													;	/* >>> code= price*/
	end															;	
	else do														;	/* si _n_ > 1, les autres lignes */
	cost=(cost1*holding1+trade*price)/holding; end				;	/* >>> cost=(cost1*holding1+trade*price)/holding */
	drop cost1 holding1											;	/* supprimer les varibles intermédiaires*/
	run															;
	data tem_last_transaction									;	
	set stock end=eof											;	/* comme achat, récuperer les infos nécessaires pour calculer holding et cost*/
	if eof then do												;
	call symputx('lhold',holding)								;
	call symputx('lcost',cost)									;
	output														;
	end															;
	run															;
	%let holding=&lhold											;	/* cette fois ci, holding = &lhosd */
	%let cost=&lcost											;	/* cost = &lcost */
	proc sql													;
	insert into suivi values(&date,&trade,&holding,&price,&cost);	/* inserer valeur dans la table suivi, pas dans stock, car c'est fait au dessus et c'est une vente*/
	quit														;
%end															;
proc datasets nolist lib=work; delete tem: ; quit				;   /* supprimer les tables temporaires */
%mend;

%macro fifo(table=have)					;
data suivi stock;	set &table(obs=1)	;
cost=price								;	/* préparation des tables intermédiaires*/
run										;
data _null_; set have					;
key=compress(catx(",",date,trade,price));
call symputx(compress("key"||_N_),key)	;	/* récuperer des valeurs pour utiliser %stock*/
run										;
%let nbobs= &sysnobs.					;
%do i=2 %to &nbobs.						;
%stock(&&key&i)							;	/* %stock(&key2)=%stock(1,100,23.2)	;*/
%end									;
data want; set suivi; run				;	/* Table resultat: WANT*/
%mend									;

*-------------------;
* - 3 - RUN			;
*-------------------;
%fifo(table=have)	;
*%stock(1,100,23.2)	;	/* exemple d'utilisation macro %stock*/
*%stock(2,-120,22)	;
*%stock(3,20,23)	;
*%stock(4,-90,24)	;

Please give me a like if you find it useful.

Re: calculate stock cost based on FIFO

kelxxx — Sun, 26 Dec 2021 21:18:28 GMT

*-------------------------------;
* - 1 - Préparation				;
*-------------------------------;
data have						;
input date trade holding price	;
cards							;
0 100 100 20
1 100 200 23.2
2 -120 80 22 
3 20 100 23
4 -90 10 24
;
run								;

SORRY, ERROR IN TABLE HAVE,

This is corrected version

Re: calculate stock cost based on FIFO

kelxxx — Sun, 26 Dec 2021 21:22:12 GMT

Another solution if you don't have table have before

*---------------------------------------;
* - 1 - Préparation						;
*---------------------------------------;
data suivi;
input date trade holding price cost		;
cards									;
0 100 100 20 20
;
run										;
data stock; set suivi;	run				;

*---------------------------------------------------------------;
* - 2 - Macro 													;
*---------------------------------------------------------------;
%macro stock(date,trade,price)									;
%if &trade > 0 %then %do										; 	/* si c'est l'achat*/
	data tem_last_transaction									; 
	set stock end=eof; if eof then do							;	/* récupérer la dernière ligne de la table stock pour les infos suivantes*/	
	call symputx('lhold',holding); call symputx('lcost',cost)	;	/* &lhold: quantité dans le stock, &lcost: prix moyen */
	output; end; run											;	
	%let holding=%sysevalf(&lhold+&trade)						;	/* calculer holding*/
	%let cost=%sysevalf((&lcost*&lhold+&trade*&price)/&holding)	;	/* calculer cost*/
	%put &holding &cost											;
	proc sql;
	insert into suivi values(&date,&trade,&holding,&price,&cost);	/* insérer les résultats dans la table suivi*/
	insert into stock values(&date,&trade,&holding,&price,&cost);	/* et stock*/
	quit														;
%end															;

%else %do														;	/* si c'est vente */	
	data stock													;
	set stock(where=(holding>abs(&trade)))						;	/* récuperer seulement les lignes ou holding > quantité vente(abs(trade)), parceque si holding < trade >>> il sera soldé*/
	holding=holding+&trade										;	/* màj holding*/	
	cost1=lag(cost)												;	/* cost de la ligne n-1*/
	holding1=lag(holding)										;	/* holding de la ligne n-1*/
	if _n_=1 then do											;	/* si c'est la première ligne _n_=1*/
	trade=holding												;	/* >>> trade= holding*/
	cost=price													;	/* >>> code= price*/
	end															;	
	else do														;	/* si _n_ > 1, les autres lignes */
	cost=(cost1*holding1+trade*price)/holding; end				;	/* >>> cost=(cost1*holding1+trade*price)/holding */
	drop cost1 holding1											;	/* supprimer les varibles intermédiaires*/
	run															;
	data tem_last_transaction									;	
	set stock end=eof											;	/* comme achat, récuperer les infos nécessaires pour calculer holding et cost*/
	if eof then do												;
	call symputx('lhold',holding)								;
	call symputx('lcost',cost)									;
	output														;
	end															;
	run															;
	%let holding=&lhold											;	/* cette fois ci, holding = &lhosd */
	%let cost=&lcost											;	/* cost = &lcost */
	proc sql													;
	insert into suivi values(&date,&trade,&holding,&price,&cost);	/* inserer valeur dans la table suivi, pas dans stock, car c'est fait au dessus et c'est une vente*/
	quit														;
%end															;
proc datasets nolist lib=work; delete tem: ; quit				;   /* supprimer les tables temporaires */
%mend;

*-------------------;
* - 3 - RUN			;
*-------------------;
%stock(1,100,23.2)	;
%stock(2,-120,22)	;
%stock(3,20,23)		;
%stock(4,-90,24)	;

Please give me a like if you find it useful.Thanks!

Re: calculate stock cost based on FIFO

Xiyuan — Mon, 27 Dec 2021 04:55:53 GMT

thanks kel ! That works well, but i still need your help because i don't know how to identify each stock, each transaction in this context, just like this, if i have more than 1 stock and the initial day on which i hold them can be different, btw, the data is so large that i can't input one by one actually

Date	Stock	Trade	Holding	Price	Reference Price
2020-01-01	A	100	100	20	(100∗20)/100=20
2020-02-01	A	100	200	23.2	(100∗20+100∗23.2)/200=21.6
2020-03-01	A	-120	80	22	(80∗23.2)/80=23.2
2020-04-01	A	20	100	23	(20∗23+80∗23.2)/100=23.16
2020-05-01	A	-90	10	24	(10∗23)/10=23
2020-03-01	B	200	200	10	(200∗10)/200=10
2020-04-01	B	-100	100	15	(100∗10)/100=10
2020-05-01	B	50	150	12	(100∗10+50∗12)/150=10.67

Re: calculate stock cost based on FIFO

Xiyuan — Mon, 27 Dec 2021 07:33:21 GMT

also, i'm still thinking how to write your third step into a circular format. First, i want to traverse all dates, next i want to traverse both all stocks and all dates

i'm not clear how to run this: %macro stock(date,trade,price) if i want to loop every observation

Re: calculate stock cost based on FIFO

Xiyuan — Mon, 27 Dec 2021 07:44:33 GMT

let me give you a better version, we cannot input the observations one by one because there are so much time. thanks !

Date	Stock	Trade	Holding	Price	Reference Price	BS
2020-01-01	A	100	100	20	(100*20)/100=20	Buy
2020-02-01	A	100	200	23.2	(10020+10023.2)/200=21.6	Buy
2020-03-01	A	-120	80	22	(80*23.2)/80=23.2	Sell
2020-04-01	A	20	100	23	(2023+8023.2)/100=23.16	Buy
2020-05-01	A	-90	10	24	(10*23)/10=23	Sell
2020-05-02	A	20	30	20	(2020+1023)/30=21	Buy
2020-03-01	B	200	200	10	(200∗10)/200=10	Buy
2020-04-01	B	-100	100	15	(100∗10)/100=10	Sell
2020-05-01	B	50	150	12	(100∗10+50∗12)/150=10.67	Buy

Re: calculate stock cost based on FIFO

Ksharp — Mon, 27 Dec 2021 11:39:09 GMT

I like this question.

data have;
input stock date trade holding price;
cards;
1 0 100 100 20
1 1 100 200 23.2
1 2 -120 80 22
1 3 20 100 23
1 4 -90 10 24
2 0 100 100 20
2 1 100 200 23.2
2 2 -120 80 22
2 3 20 100 23
2 4 -90 10 24
;
run ;

data want;
array positive{999999} _temporary_;
i=1;j=1;call missing(of positive{*});

do until(last.stock);
  set have;
  by stock;
/*hold the positive share*/
  if trade>0 then do; 
    do i=i to i+trade-1 ;
      positive{i}= price ; 
 end;
  end;
/*eliminate the sell share - FIFO*/
  if trade<0 then do;
    do j=j to j+abs(trade)-1 ;
      positive{j}= . ;
 end;
  end;    
/*calcualte cost*/
cost=sum(of positive{*})/n(of positive{*});

output;
end;
drop i j;
run;

Re: calculate stock cost based on FIFO

andreas_lds — Mon, 27 Dec 2021 13:05:48 GMT

Please use a data step to post your data, making it so much easier to start helping.

data work.have;
  infile datalines dsd truncover;
  input Date:MMDDYY10. Stock:$1. Trade:BEST. Holding:BEST. Price:BEST.;
  format Date MMDDYY10.;
datalines4;
01/01/2020,A,100,100,20
02/01/2020,A,100,200,23.2
03/01/2020,A,-120,80,22
04/01/2020,A,20,100,23
05/01/2020,A,-90,10,24
05/02/2020,A,20,30,20
03/01/2020,B,200,200,10
04/01/2020,B,-100,100,15
05/01/2020,B,50,150,12
;;;;

/* if you are not 100% sure, that have is properly sorted by "stock", add proc sort here */

data want;
   set have;
   by stock;
   
   length RefPrice 8;
   
   retain _holding _price;
   
   if first.stock then do;
      _holding = 0;
      _price = 0;
   end;
   
   if trade > 0 then do;
      RefPrice = round((trade * price + _holding * _price) / holding, 0.01);
      _price = price;
   end;
   else do;
      RefPrice = _price;
   end;
   
   _holding = holding;
   
   drop _:;
run;

I am not sure whether the result match your expectation if Trade is negative several times in a row.

Re: calculate stock cost based on FIFO

kelxxx — Mon, 27 Dec 2021 16:08:41 GMT

It's awesome!!!!!!!

Re: calculate stock cost based on FIFO

kelxxx — Mon, 27 Dec 2021 16:14:19 GMT

It's awesome!!!!!!!

Re: calculate stock cost based on FIFO

Xiyuan — Tue, 28 Dec 2021 05:20:43 GMT

Hi andreas, this works well but there is something wrong when i extend more samples, i'm sorry i cannot find out what the specific problem is， for example, when i add 2020/06/01,B,-20,130,14
i expect the reference price is 10.77, but it is not here

Re: calculate stock cost based on FIFO

Xiyuan — Tue, 28 Dec 2021 05:23:18 GMT

Thanks Ksharp! this code works well and robust enough even I extend more
learned a lot, i'm going to try this on my large dateset

Re: calculate stock cost based on FIFO

andreas_lds — Tue, 28 Dec 2021 06:25:01 GMT

Why 10.77?

The calculation should be: (holding * last_price) / holding => (130 * 12) / 130, which can be optimized to 12.

Re: calculate stock cost based on FIFO

Xiyuan — Tue, 28 Dec 2021 06:39:09 GMT

not the last price, if we sell 20 shares, the remaining is 130,
following FIFO, 80 of 130 should come from the initial buy on 2020-03-01 with price 10,
while 50 of 130 should come from 2020-05-01 with price 12,
thus cost should be (80*10+50*12)/130=10.77

Re: calculate stock cost based on FIFO

andreas_lds — Tue, 28 Dec 2021 06:42:55 GMT

Interesting, so the program i posted returns the expected results only randomly 🙂

You should extend your examples to contain that case.

Re: calculate stock cost based on FIFO

kelxxx — Tue, 28 Dec 2021 11:47:57 GMT

my program returns this result

Re: calculate stock cost based on FIFO

kelxxx — Tue, 28 Dec 2021 11:49:20 GMT

you don't need to input one by ont, because the macro will do that.

Re: calculate stock cost based on FIFO

andreas_lds — Tue, 28 Dec 2021 16:53:00 GMT

Another try using a hash-object:

data want;
   set work.have;
   by Stock;
   
   format Date date9.;
   
   length _Amount 8;
   
   if _n_ = 1 then do;
      declare hash fifo(ordered: 'yes');
      fifo.defineKey('Date');
      fifo.defineData('Date', '_Amount', 'Price');
      fifo.defineDone();
      declare hiter fi('fifo');
   end;
   
   if Trade > 0 then do;
      _Amount = Trade;
      fifo.add();
      _Total = 0;
      rc = fi.first();
      
      do while (rc = 0);
         _Total = _Total + (_Amount * Price);
         rc = fi.next();
      end;
   end;
   else do;
      _Remaining = abs(Trade);
      _Date = Date;
      _Price = Price;
      
      rc = fi.first();
      _Total = 0;
      
      do while (rc = 0);
      
         if _Remaining > 0 then do;
            _Taken = min(_Amount, _Remaining);
            _Amount = _Amount - _Taken;
            _Remaining = _Remaining - _Taken;
             
            fifo.replace();
         end;
        
         _Total = _Total + _Amount * price;
                  
         rc = fi.next();
      end;
            
      Date = _Date;
      Price = _Price;
   end;
   
   RefPrice = round(_Total / Holding, 0.01);
  
   if last.Stock then do;
      rc = fifo.clear();
   end;

   drop rc _:;
run;

The paper "From Stocks to Flows: Using SAS® HASH objects for FIFO, LIFO, and other FO’s" by Mark Keintz (@mkeintz ??) was a very useful inspiration.

Re: calculate stock cost based on FIFO

Ksharp — Wed, 29 Dec 2021 11:41:40 GMT

If you have big table, try this one .

data work.have;
  infile datalines dsd truncover;
  input Date:MMDDYY10. Stock:$1. Trade:BEST. Holding:BEST. Price:BEST.;
  format Date MMDDYY10.;
datalines4;
01/01/2020,A,100,100,20
02/01/2020,A,100,200,23.2
03/01/2020,A,-200,80,22
04/01/2020,A,90,100,23
05/01/2020,A,-60,10,24
05/02/2020,A,20,30,20
03/01/2020,B,200,200,10
04/01/2020,B,-100,100,15
05/01/2020,B,-100,150,12
;;;;


data want2;
array _trade{9999} _temporary_;
array _price{9999} _temporary_;

i=0;j=1;call missing(of _trade{*} _price{*});

do until(last.stock);
  set have;
  by stock;
/*hold the positive share*/
  if trade>0 then do; 
    i+1;
 _trade{i}= trade ;
    _price{i}= price ; 
  end;
/*eliminate the sell share - FIFO*/
  if trade<0 then do;
    balance_trade=trade ;
    do j=j to i ;
      balance_trade=sum(balance_trade,_trade{j}) ;
   if balance_trade<=0 then do;_trade{j}=.;_price{j}=.; end;
    else do;_trade{j}=balance_trade;leave;end;
    end;
  end;    
/*calcualte cost*/ 
a=0;b=0;
do k=j to i;
 a+_trade{k}*_price{k};
 b+_trade{k};
end;
cost=divide(a,b);

output;
end;
drop i j k balance_trade a b;
run;