computing buys and sells in a portfolio

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

computing buys and sells in a portfolio

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


Accepted Solutions
Solution
‎05-31-2017 11:53 PM
Trusted Advisor
Posts: 1,510

Re: computing buys and sells in a portfolio

[ Edited ]

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

View solution in original post


All Replies
Solution
‎05-31-2017 11:53 PM
Trusted Advisor
Posts: 1,510

Re: computing buys and sells in a portfolio

[ Edited ]

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
Trusted Advisor
Posts: 1,510

Re: computing buys and sells in a portfolio

[ Edited ]

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;

 

Esteemed Advisor
Posts: 7,296

Re: computing buys and sells in a portfolio

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

 

Occasional Contributor
Posts: 6

Re: computing buys and sells in a portfolio

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

Occasional Contributor
Posts: 6

Re: computing buys and sells in a portfolio

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
Esteemed Advisor
Posts: 7,296

Re: computing buys and sells in a portfolio

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

Occasional Contributor
Posts: 6

Re: computing buys and sells in a portfolio

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.

 

Esteemed Advisor
Posts: 7,296

Re: computing buys and sells in a portfolio

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

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 170 views
  • 1 like
  • 3 in conversation