computing buys and sells in a portfolio

Solved
Occasional Contributor
Posts: 6

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

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

Jun-90                   Apple 5000
Microsoft 5000
Sep-90                  Apple 1000
Microsoft -1000
Oracle 2000
Dec-90                  Microsoft -4000

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
PROC Star
Posts: 2,319

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
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 Microsoft -4000

All Replies
Solution
‎05-31-2017 11:53 PM
PROC Star
Posts: 2,319

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
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 Microsoft -4000
PROC Star
Posts: 2,319

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;``````

PROC Star
Posts: 8,149

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-12-30 FundA Microsoft 1000
1990-12-30 FundA Apple 6000
PROC Star
Posts: 8,149

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-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.

PROC Star
Posts: 8,149

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.