Combining data for charting

[Dallman Ross]

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

 

[Ken Johnson]

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

 

[Ken Johnson]

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

Hi Dallman,

Since the data you supplied is only a part of a larger table containing
symbols other than CSCO, the formulae will need extending to separate
the different symbols (I'm guessing).

Also, I've found an alternative formula for column J to give the one
date for combined events...

=IF(SUMPRODUCT(($B2:$B$9=B2)*($G2:$G$9=G2)*($H2:$H$9=H2))<>1,"",G2)
This formula does not require the 'date sold' column be sorted and
includes 'symbol' as a criterion.

The other formulae that include 'symbol' as a criterion are...

=IF(J2="","",SUMPRODUCT(($B$2:$B$9=B2)*($G$2:$G$9=J2)*($H$2:$H$9=H2)*($C$2:$C$9)))
in column K for total number for each combined event.

=IF(K2="","",SUMPRODUCT(($B$2:$B$9=B2)*($G$2:$G$9=J2)*($H$2:$H$9=H2)*($E$2:$E$9))/SUMPRODUCT(($B$2:$B$9=B2)*($G$2:$G$9=G2)*($H$2:$H$9=H2)))
in column L for the average bought price.

=IF(K2="","",SUMPRODUCT(($B$2:$B$9=B2)*($G$2:$G$9=G2)*($H$2:$H$9=H2)*($C$2:$C$9*$E$2:$E$9))/K2)
in column M for weighted average of the bought prices.

Ken Johnson

 

[Ken Johnson]

Hi Dallman,

After re-examining your post and noticing that you columns E and I are
either hidden or contain irrelevant data, it might make it easier for
you if I supply the new equations based on the inclusion of columns E
and I...

Column L....

Heading is "DATE (sale per date per price)"
Formula in row 2 is
=IF(SUMPRODUCT(($B2:$B$9=B2)*($H2:$H$9=H2)*($J2:$J$9=J2))<>1,"",H2)

You will have to change all the "$9"s in the formula so that the whole
table is included. They could be changed to some convenient large
number that you will never exceed eg $1000 or $65536. The only things
you need to keep in mind are 1) they must all be changed to the same
number and 2) the bigger the number the longer the sheet will take to
calculate.

Column M...

Heading is "NUMBER (sale per date per price)"
Formula in row 2 is
=IF(L2="","",SUMPRODUCT(($B$2:$B$9=B2)*($H$2:$H$9=L2)*($J$2:$J$9=J2)*($C$2:$C$9)))

Change the "$9"s

Column N...

Heading is "AVERAGE BOUGHT @ (sale per date per price)"
Formula in row 2 is
=IF(M2="","",SUMPRODUCT(($B$2:$B$9=B2)*($H$2:$H$9=L2)*($J$2:$J$9=J2)*($F$2:$F$9))/SUMPRODUCT(($B$2:$B$9=B2)*($H$2:$H$9=H2)*($J$2:$J$9=J2)))

Change the "$9"s

Column O...

Heading is WEIGHTED AVERAGE BOUGHT @ (sale per date per price)
Formula in row 2 is
=IF(M2="","",SUMPRODUCT(($B$2:$B$9=B2)*($H$2:$H$9=H2)*($J$2:$J$9=J2)*($C$2:$C$9*$F$2:$F$9))/M2)

Change the "$9"s

Filldown all formulae to the bottom of the table.

Ken Johnson

 

[Dallman Ross]

Hi Dallman,

After re-examining your post and noticing that you columns E and I are
either hidden or contain irrelevant data, it might make it easier for
you if I supply the new equations based on the inclusion of columns E
and I...

Yup. Thanks for noticing.

I didn't yet reply to your other posts because I was conducting some
experiments on your efforts and because I was busy with some other
tasks. I really appreciate your yeomen work helping me solve my
complex problem.

I had adjusted your equations for columns -- by copying my sheet
to a dummy sheet and deleting the columns I had not published here,
creating your suggested columns, then inserting the columns I'd
deleted in order to shift the ranges in your equations. I also
did figure out I needed to extend the rows. thanks. I do very much
appreciate the new offerings, in any case. Much better to all be
working from the same sheet of "music," as they say.

Column L....

Heading is "DATE (sale per date per price)"
Formula in row 2 is
=IF(SUMPRODUCT(($B2:$B$9=B2)*($H2:$H$9=H2)*($J2:$J$9=J2))<>1,"",H2)

Now for a bit of bad this isn't working entirely. I have
only a vague theory as to why. But I think the algorithm will need
revision. :-(

Let's just look at this snippet:

r B C D ... F G H ... J K
o
w Symbl # Date Bot Bot @ Cost Date Sld Sld @ Proceeds
-- ----- --- --------- ----- -------- -------- ----- --------
24 CSCO 200 3-Feb-06 18.03 3,606.00 8-Feb-06 19.48 3,895.88
25 CSCO 250 8-Jun-06 19.52 4,880.00 9-Aug-06 19.40 4,849.84
26 CSCO 240 11-Jul-06 18.76 4,502.40 9-Aug-06 19.40 4,655.85
27 CSCO 20 11-Jul-06 18.76 375.20 9-Aug-06 19.40 387.99
28 CSCO 50 13-Jul-06 18.03 901.50 9-Aug-06 19.40 969.97
29 CSCO 220 13-Jul-06 18.03 3,966.60 14-Aug-06 19.99 4,397.66
30 CSCO 40 3-Aug-06 17.26 690.40 14-Aug-06 19.99 799.58
31 CSCO 250 3-Aug-06 17.26 4,315.00 15-Aug-06 20.74 5,184.84

Applying your formula for New Column L, every single date is replicated.
There ought to be gaps for L25:L27 and for L29.

I propose using COUNTIF from the current row down to the bottom.
If the result is > 1, then something lower repeats the date in
the columns we apply the test to; leave this one blank. So far I
haven't gotten this to work, and I'm also not sure about how we'd
add up the data we're accruing for new synthetic totals for Shares
Sold and Weighted Average Price per Share.

(You were right that I wanted a weighted average of share price
paid. I erred in asking for a simple average. Thank you for
catching that.)

Your tip on sorting in two or more steps as needed also works well.
Thank you for that, also!

Obtw: as long as we're making the proposed formulas match my
actual columns -- in the actual sheet, I fill out columns now
all the way to Q. (And it's a work in progress.) What you
show as L, I have as R.

Thanks for all the sweat-effort expended, Ken!

Dallman Ross

 

[Dallman Ross]

In <[email protected]>, Ken
Johnson <[email protected]> spake thusly:

Now for a bit of bad this isn't working entirely. I have
only a vague theory as to why. But I think the algorithm will need
revision. :-(

I have to retract all that. I erred. My Column J is a calculated
column, and there are significant digits that do not show up in the
simple data I reproduced or, indeed that I normally see. Hidden
Column I contains (links to a data in a hidden worksheet with) the
actual downloaded data from the broker. When I substitute Column I
for J in your formula, it works as you expected. Hooray!

The reason I have a calculated column, and the reason I display
it whilst hiding the base data from the broker, is so that I
can do data validation. The broker's data is sometimes flawed!
I have conditional formatting set to compare the calculated field
with the broker's field, to allow for rounding, and to apply
a color pattern to the cell if there is a discrepancy! If I
don't do some kind of data validation like this, I end up
accepting the broker's errors and even sending them to my tax
man! Anyway, I should have seen this earlier. I should have
applied your suggested formula to Column I instead of J.

Thanks, Ken!

Oh: for general info, here is the conditional-formatting formula
to check the calculated cell against the broker's data:

=AND(ROUND(ABS(J608-I608),3)>0.005,ISERROR(NOT(FIND("@",$A608))))

(It's so complicated because the way that options are
represented is anomalous and doesn't meet the pricing strictures
we are used to for normal stocks. (A quantity of 1 for options
represents 100 shares of stock. A 50-cent option costs $50 to
buy 1 contract of.) So I have to make sure the row of data
isn't to do with options if I find a validation "error"; and I
do this with the FIND operation, since the "@" sign only shows
up in those entries.)

Dallman Ross

 

[Ken Johnson]

Hi Dallman,

Now for a bit of bad this isn't working entirely. I have
only a vague theory as to why. But I think the algorithm will need
revision. :-(

Applying your formula for New Column L, every single date is replicated.
There ought to be gaps for L25:L27 and for L29.

I pasted in your new snippet and the results in column L were...

24-->8-Feb-06
25-->blank
26-->blank
27-->blank
28-->9-Aug-06
29-->blank
30-->14-Aug-06
31-->15-Aug-06

ie the required result.
I then inserted extra columns to get the formula out to column R and it
still works.
So, something is going wrong your end, the end that matters!

If you can't figure out what's wrong you could always email me a bit of
your workbook for me to checkout. (kencjohnson gmail account).

Ken Johnson

 

[Ken Johnson]

Hi Dallman,

It's nice to hear you've got it working.

Ken Johnson

 

[Dallman Ross]

Hi Dallman,

It's nice to hear you've got it working.

Yes, indeed, it's working well. Thank you again!

Just for further information:
I forgot to mention the other reason I need Column J, a calculated
column that is compared to the raw numbers from the broker that are
in Column I: when I sell because an option I've sold (covered call)
got executed, the price I get is not what the broker shows.

For example, I sell a covered call on Cisco Systems @ $22.50,
and it's executed (the price is above that on option expiration,
or the purchaser of my call option decides anyway to execute
it before then). The broker shows the sales prices as $22.50
per share. But he adds the receipts I got from selling the
option to the total receipts from the sale. So, supposing I
sold the call for 50 cents for a contract (which equates to
100 shares), and supposing we're speaking of 100 shares or
1 contract, well, I received $2,300 for the sale. My Column
J shows $23.00 per share for that transaction, not the $22.50
that the broker recorded.

Okay, now I'm off to see if I can make my charts look good with
the new columns you've assisted me in creating!

Dallman Ross