topic Re: computing buys and sells in a portfolio in SAS Programming

computing buys and sells in a portfolio

Ramani — Thu, 01 Jun 2017 02:58:19 GMT

Hi,

I'm trying to wrap my head around how to compute buys and sells by a mutual fund in a particular quarter.

My data looks like this:

Date Fund Stock Shares-held

Jun-90 Fund A Apple 5000
Microsoft 5000
Sep-90 Fund A Apple 6000
Microsoft 4000
Oracle 2000
Dec-90 Fund A Apple 6000
Oracle 2000
Facebook 2000

I'm in need of an output data set something like this

buy/Sell Change in shares
Jun-90 Apple 5000
Microsoft 5000
Sep-90 Apple 1000
Microsoft -1000
Oracle 2000
Dec-90 Microsoft -4000
Facebook 2000

I tried using some combinations of sorting, if statements and lag statements and cannot seem to get all the values I want.

Thank you

-R

Re: computing buys and sells in a portfolio

ChrisNZ — Thu, 01 Jun 2017 03:31:33 GMT

Likes this (I'll let you handle the dates properly) ?


data HAVE(index=(A=(FUND STOCK DATE )));
input DATE $ FUND $ STOCK $10. SHARES_HELD;
cards;
Apr-90 FundA Apple            5000
Apr-90 FundA Microsoft        5000
Jun-90 FundA Apple            6000
Jun-90 FundA Microsoft        4000
Jun-90 FundA Oracle           2000
Oct-90 FundA Apple            6000
Oct-90 FundA Oracle           2000
Oct-90 FundA Facebook         2000
run;

proc sql;
  create table CARTESIAN as 
  select * from (select unique DATE  from HAVE)
               ,(select unique FUND  from HAVE)
               ,(select unique STOCK from HAVE)
  order by FUND, STOCK, DATE;
quit;

data CHANGES(index=(A=(FUND DATE STOCK))); 
  merge HAVE CARTESIAN;
  by FUND STOCK DATE;
  if SHARES_HELD=. then SHARES_HELD=0;
  CHANGE=ifn(first.STOCK,SHARES_HELD,SHARES_HELD-lag(SHARES_HELD));
run;
      
data WANT; 
  set CHANGES;
  by FUND DATE STOCK;
run;  

proc print noobs;
  var DATE STOCK CHANGE;
  where CHANGE;
run;

DATE	STOCK	CHANGE
Apr-90	Apple	5000
Apr-90	Microsoft	5000
Jun-90	Apple	1000
Jun-90	Microsoft	-1000
Jun-90	Oracle	2000
Oct-90	Facebook	2000
Oct-90	Microsoft	-4000

Re: computing buys and sells in a portfolio

ChrisNZ — Thu, 01 Jun 2017 03:31:51 GMT

Slightly more compact:

data CHANGES(index=(A=(FUND DATE STOCK))); 
  merge HAVE CARTESIAN;
  by FUND STOCK DATE;
  CHANGE=sum(0,SHARES_HELD,-lag(SHARES_HELD)*^first.STOCK);
run;

Re: computing buys and sells in a portfolio

art297 — Thu, 01 Jun 2017 03:32:55 GMT

data want (drop=_:);
  set have;
  retain _date _fund;
  if not missing(date) then _date=date;
  else date=_date;
  if not missing(fund) then _fund=fund;
  else fund=_fund;
run;

proc sort data=want;
  by stock date;
run;

data want (drop=_:);
  set want;
  by stock;
  _shares_held=ifn(first.stock,0,lag(Shares_held));
  change=Shares_held-_Shares_held;
run;

proc sort data=want;
  by date fund stock;
run;

Art, CEO, AnalystFinder.com

Re: computing buys and sells in a portfolio

Ramani — Thu, 01 Jun 2017 03:56:06 GMT

Thank art297 and ChrisNZ, both of your solutions work..

Re: computing buys and sells in a portfolio

Ramani — Thu, 01 Jun 2017 16:42:02 GMT

Art297, This solution does not work when I extend the data.
1990-04-30 FundA Apple 5000
1990-04-30 FundA Microsoft 5000
1990-06-30 FundA Apple 6000
1990-06-30 FundA Microsoft 4000
1990-06-30 FundA Oracle 2000
1990-09-30 FundA Apple 6000
1990-09-30 FundA Oracle 2000
1990-09-30 FundA Facebook 2000
1990-12-30 FundA Microsoft 1000
1990-12-30 FundA Apple 6000

Re: computing buys and sells in a portfolio

art297 — Thu, 01 Jun 2017 16:56:29 GMT

What doesn't work? You'll have to be more specific. Your data format and values have changed quite a bit from your original question, thus I removed the data clean-up section of my code. Unless I'm missing something, it appears to follow your specs:

data have;
  informat date yymmdd10.;
  informat fund $8.;
  informat stock $9.;
  input Date    Fund &      Stock          Shares_held;
  cards;
1990-04-30  FundA  Apple  5000
1990-04-30  FundA  Microsoft  5000
1990-06-30  FundA  Apple  6000
1990-06-30  FundA  Microsoft  4000
1990-06-30  FundA  Oracle  2000
1990-09-30  FundA  Apple  6000
1990-09-30  FundA  Oracle  2000
1990-09-30  FundA  Facebook  2000
1990-12-30  FundA  Microsoft  1000
1990-12-30  FundA  Apple  6000
;

proc sort data=have out=want;
  by stock date;
run;

data want (drop=_:);
  set want;
  by stock;
  _shares_held=ifn(first.stock,0,lag(Shares_held));
  change=Shares_held-_Shares_held;
run;

proc sort data=want;
  by date fund stock;
run;

Art, CEO, AnalystFinder.com

Re: computing buys and sells in a portfolio

Ramani — Fri, 02 Jun 2017 18:09:47 GMT

I apologise for not being specific earlier,

Your code captures the difference in shares from one quarter to another. but it does not capture when all the shares of a stock have been sold.
E.g. All of Microsoft is sold in September and hence I need a value where shares_change = -4000 or microsoft.
Microsoft is added back to the portfolio in December, but the value wrongly shows as -3000.
Oracle is also sold in December and hence it is not being captured in December.

Re: computing buys and sells in a portfolio

art297 — Fri, 02 Jun 2017 18:40:16 GMT

Got it! I didn't understand that missing entries meant that all shares had been sold. As such, yes, I'd stick with the code suggested by @ChrisNZ

Art, CEO, AnalystFinder.com