Okay, I sort of see what you mean, but I don't have the entire picture
in view, so here is what I suggest. Put together a small file of
exactly the incoming data layout for two or three stocks (and cash, if
there is any incoming cash data). Then give a blow by blow account of
the rules to compute the output, and what the output should be. I know
that's a lot of work, but once I see all that, I'll be able to show you
how to do it in SAS, possibly in a small number of steps.
I recommend that you send all this to the SAS-L list, too. So far
you've given the pieces in kind of disconnected fashion, and so there's
no guarantee the answers to the pieces will fit together. Once they
see exactly what you want, they'll be able to give it to you. And
you're likely to get an even "better" solution than what I give you.
Here's the blow by blow:
After you read everything in:
Step 1. calculate cost market value of each security you purchased
roll forward to next month.
Step 2: adjust starting shares for any splits. Calculate the dollar
dividends you receive for each security.
Step 3. buy and sell trades for month as indicated and update ending
share values along with market value and cost value for each stock
Step 4. continue in this loop till you've gone through all the days.
Step 5. summary the data for purchases, sales and dividends. So in
addition to having these numbers for each ticker when we sum we have
these values for the portfolio for each date
Step 6 merge this data back into the ticker data
Step 7 recalculate the starting and ending values for cash
Step 8 summary the portfolio. You're done.
This was not the logic I wanted to use. I kept saying that I should
not have to merge the summary data back into the dataset. It was
wasteful and inefficient. No one would suggest any other way to handle
the problem. The q2 spreadsheet matches the sas program and is a
correct reflection of what's going on. The data tabs and trades tab
are the data and the port1 tab is basically the sas code in excel.
I would like to make the initial cash position more explicit and I
think that perhaps the right way here is to exclude cash from the
initial data set but I'm not sure. Please see my post: the right way
to write sas code by rss.