Dallman Ross wrote:
I chart stock performance and keep extensive tables to do with that.
Often I sell a different number of shares than I buy, which makes
my tables fill up with multiple lines to do with the same date of
sale.

By way of example, here is some sample data from a table of
realized gains/losses (row numbers down the left margin):

r B C D ... F G H ... J K
o ----- --- --------- ----- -------- -------- ----- --------
w Symbl # Date Bot Bot @ Cost Date Sld Sld @ Proceeds
-- ----- --- --------- ----- -------- -------- ----- --------
21 CSCO 200 25-Jul-05 19.03 3,806.00 5-Jan-06 17.99 3,597.89
22 CSCO 400 11-Aug-05 18.03 7,212.00 5-Jan-06 17.99 7,195.77
23 CSCO 200 11-Aug-05 18.03 3,606.00 9-Jan-06 18.97 3,793.88
24 CSCO 400 12-Oct-05 17.03 6,812.00 9-Jan-06 18.97 7,587.77
25 CSCO 200 12-Oct-05 17.03 3,406.00 8-Feb-06 19.25 3,849.88
26 CSCO 100 3-Feb-06 18.03 1,803.00 8-Feb-06 19.25 1,924.94
27 CSCO 300 3-Feb-06 18.03 5,409.00 8-Feb-06 19.16 5,747.82
28 CSCO 200 3-Feb-06 18.03 3,606.00 8-Feb-06 19.48 3,895.88

By the way, an ancillary problem is that the 3-item limitation to
Excel's sort routine means that my desire to sort by Symbol, then
Date Sold, then Date Bought, then (ascending) Share Price on Sale
doesn't always work out. For example, I want Row 27 in the sample
to come before Row 26, because J27 is smaller than J26. But I
can't get Excel to do that.

But that's not what my main thrust is here with this question,
although I would love to hear an answer to that, as well.
My main question has to do with my desire to have, given the above
data, a chart with five "events" rather than eight. The combined
sale per date, per price, should be used. That is, on Jan. 5th
there would be one event charted: the sale of 200 + 400 = 600
shares @ 17.99, bought for an average price of 19.53
(=AVERAGE(F21:F22)). On Jan. 9th, likewise one event would be
charted, for the sale of a combined 600 shares @ 18.97. On
Feb. 8th, *three* events should be charted: the sale of a
combined 300 shares @ 19.25, 300 @ 19.16, and 200 @ 19.48.
I have no idea how to do that.

I wouldn't mind having the different combined odd lots demarcated
as a stacked area chart, but that is a secondary desire.

Any ideas?

Dallman Ross


Hi Dallman,

I had...

symbol in column B
number in column C
date bought in column D
bought at in column E
cost in column F
date sold in column G
sold at in column H
proceeds in column I

The formula I used in column J to give the one date for combined events
was...

=IF(AND(G2=G3,H2=H3),"",G2)
The table will have to have the 'date sold' column sorted for this
formula to give the correct result.

The formula I used in column K to give the total number for each
combined event was...

=IF(J2="","",SUMPRODUCT(($G$2:$G$9=J2)*($H$2:$H$9= H2)*($C$2:$C$9)))

The formula I used in column L to give the average bought price was...

=IF(K2="","",SUMPRODUCT(($G$2:$G$9=J2)*($H$2:$H$9= H2)*($E$2:$E$9))/SUMPRODUCT(($G$2:$G$9=G2)*($H$2:$H$9=H2)))

I also used this formula in column M to give the weighted average of
the bought prices ( I know you didn't ask for it, I just thought it
would be a more meaningful measure of average bought price)...

=IF(K2="","",SUMPRODUCT(($G$2:$G$9=G2)*($H$2:$H$9= H2)*($C$2:$C$9*$E$2:$E$9))/K2)

Also, if you first sort the table using Share Price on Sale as the sort
key then sort by Symbol, then Date Sold, then Date Bought, I think you
will find that the desired sort order is achieved. There really is no
limit to the number of ways you can sort your data, it's just that you
can only do three at one time, it's just a matter of sorting with
respect to the least significant fields first.

Ken Johnson