Combining complex data

[Dallman Ross]

Hi, folks,

Given a sheet of data about stock sales, I want to combine the lots
that were sold on the same day for the same price for the same
stock symbols.

For example, say I have:

A B C D E F G H I
1 Symbol Shares Date Bot PPrice Cost Date Sold SPrice Proceeds G/L
2 TSRA 100 3/1/2007 38.56 3,856.00 3/21/2007 41.05 4,104.93 248.93
3 TSRA 100 3/5/2007 37.66 3,766.00 3/21/2007 41.64 4,163.93 397.93
4 TSRA 30 3/2/2007 38.56 1,156.80 3/21/2007 41.05 1,231.48 74.68
5 TSRA 30 3/5/2007 37.66 1,129.80 4/5/2007 42.03 1,260.88 131.08
6 XRX 10 3/2/2007 16.86 168.60 4/18/2007 18.12 181.20 12.60
7 XRX 290 3/2/2007 16.86 4,889.40 4/20/2007 18.68 5,417.02 527.62
8 XRX 290 2/27/2007 17.36 5,034.40 4/18/2007 18.12 5,254.71 220.31

I will be writing lines to a new worksheet and combining the lots.
So lines 2 and 4 as well as lines 6 and 8 will have their data
combined.

Column B on the new sheet for that sale will say, for lines 2 & 4,
130 shares. For lines 6 & 8, 300 shares. I'll average the date
bought, weighted based on the dollar amount of the sale. Ditto
purchase price. Cost ought to still be the sum of the combined
cost lines. Date Sold doesn't change -- it's one of the bases for
combining data. Ditto Sale Price. Proceeds will be a simple
sum, as will gain/loss. Any ideas would be much appreciated.

 

[Stephane Quenson]

dman,

I would use the pivot table function located in menu Data > Pivottable and
pivotchart. This allows you to make sum, average, count, etc. for groups of
columns with same values.

Stephane.

 

[Dallman Ross]

Folks, I really could use some help here. I've spent hours
over the last few days flailing around without quite getting what
I need. I can do most of the work myself once I just get a
kick-start from somebody.

Maybe I should just ask one question at a time. Okay,
given a table like the one below, I need a formula -- I guess
an array formula -- to tell me the total shares sold per
day ("Date Sold") per specific selling price ("SPrice") for each symbol.

Thanks for any tips!

 

[Max]

One venture ..

Assuming your source table as posted is in sheet: X, data within A2:I8

Put in J2, normal ENTER:
=SUMPRODUCT((A$2:A$8=A2)*(F$2:F$8=F2)*(G$2:G$8=G2)*B$2:B$8)
Copy down to J8

Then in another sheet,
you have the symbols and dates sold listed in A1:B2, eg:
TSRA 3/21/2007
XRX 4/18/2007

Put in C1, array-enter (CSE):
=IF(COUNTA(A1:B1)<2,"",INDEX(X!J$2:J$8,MATCH(1,(X!A$2:A$8=A1)*(X!F$2:F$8=B1),0)))
Copy C1 down to return required results, viz 130 (TSRA) and 300 (XRX).

 

[Dallman Ross]

Assuming your source table as posted is in sheet: X, data within A2:I8

Put in J2, normal ENTER:
=SUMPRODUCT((A$2:A$8=A2)*(F$2:F$8=F2)*(G$2:G$8=G2)*B$2:B$8)
Copy down to J8

That works! Thank you very much, Max.

It also helped me figure out what about my multi-hour flailing
wasn't working. I had set up named ranges, but had inadvertently
gotten some things wrong that were hard to pinpoint without ever
having had a working starting point to test from.

Then in another sheet,
you have the symbols and dates sold listed in A1:B2, eg:
TSRA 3/21/2007
XRX 4/18/2007

Put in C1, array-enter (CSE):
=IF(COUNTA(A1:B1)<2,"",INDEX(X!J$2:J$8,MATCH(1,(X!A$2:A$8=A1)*(X!F$2:F$8=B1),0)))
Copy C1 down to return required results, viz 130 (TSRA) and 300 (XRX).

Okay, I managed to get this part to return values that look sort of
within expectations, but are slightly different. I'm confused about what
we are trying to achieve with this part of the suggestion. Can you
clarify further why we're doing this second formula on the other sheet?

Here are representative side-by-side results of the values I get
in Column J from your initial formula and the values in Column C
on the new sheet. Notice where some of the figures (shares sold)
shift around and don't match up:

330 330
100 100
100 360
100 100
100 360
100 100
100 100
350 350
100 100


That's for C23-C31 on the new sheet and the corresponding J on the
original sheet. I don't really follow what we're doing here, so
don't know whether those divergences are "planned."

So far, this is a big help in any case. Thanks again!

---

 

[Dallman Ross]

That works! Thank you very much, Max.

I should add that I have succeeded at setting up named ranges for
this now. E.g.:

csvColDesc set to: =OFFSET('Cur Realized - imported'!$A$1,1,0,csvRows-1)
csvRows is set to: =COUNTA('Cur Realized - imported'!$L:$L)

(I use Col. L for the row counter because there is some extra
garbage [totals, etc.] below the data in earlier columns. This
comes from my broker on downloading in CSV format.)

So yur formula works as follows for me now (per cell J2):

=SUMPRODUCT((csvColDesc=A2)*(csvColDateSold=F2)*(csvcolSalePrice=G2)*csvColShares)

Thanks again!

 

[Max]

welcome, Dallman. glad you managed to extract the cores out of the response
<g>

 

[Dallman Ross]

=SUMPRODUCT((A$2:A$8=A2)*(F$2:F$8=F2)*(G$2:G$8=G2)*B$2:B$8)

I have a question about this now. It seems to me in theory
that this, with relative refs, might work and be more efficient:

=SUMPRODUCT((A2:A$8=A2)*(F2:F$8=F2)*(G2:G$8=G2)*B2:B$8)

But I'm not sure.

I came to this thinking about how to work out copying only the
lines with merged quantities (in B:B) to a new worksheet.

I haven't worked it through yet. I might have to go do doing
this combining in VBA so I can copy the results to the new
summary sheet.

If you have any more thoughts about this, they would be
appreciated.

Dallman

 

[Max]

Here's a revised set-up which I think should deliver your original post's
intents

Illustrated in this sample at:
http://www.flypicture.com/download/NTIyMw==
Combine lots sold on same day f same price f same stock.xls

In source sheet: X,

In J2:
=IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2)*(G$2:G2=G2)*B$2:B2))

In K2:
=IF(A2="","",IF(SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2)*(G$2:G2=G2))>1,ROWS($1:1),""))
Leave K1 blank. Select J2:K2, copy down to cover the max expected extent of
source data. Col K serves to flag the lines to be copied over to the summary
sheet

In sheet: Summary,
A1:J1 carries the same col headers as in X

In A2:
=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL(X!$K:$K,ROWS($1:1))))
Copy A2 across to J2, fill down to cover the same extent as filled in X's
cols J and K

 

[Dallman Ross]

Here's a revised set-up which I think should deliver your
original post's intents

Illustrated in this sample at:
http://www.flypicture.com/download/NTIyMw==

Max, that's really super. I thank you. I didn't answer right
away because it was an extremely busy work day for me.
But I downloaded the sample workbook with formulas and
explanations and am working through it. It looks promising,
though I'm still trying to understand parts of it.

The part that's on the summary sheet you included is what
I so far can't quite fathom completely. It seems not to
be putting all the unique data in place from the other sheet,
but I probably don't understand it well enough yet.

Combine lots sold on same day f same price f same stock.xls

Now that we've gone this far, I'm realizing more and more that
I'm going to want to go with some VBA code to do this task
and deposit the result as values in the new second sheet, though.

If you or anyone could help with that, it would be fantastic.

The task, for review purposes here, was: take a sheet full
of transaction information for stock sales and copy the
data to a new sheet while combining some lots. (I'll want
to do other calculation on some of the fields too, but I
think I can handle that later, either on my own or with
more help.) Lots (which means the number of shares reported
sold in the transaction on any given row) need to be combined
whenever the sale date and sales price for different rows match.

People who can see the earlier articles in this thread should
find some sample data; or I can supply again if need be.

All the best to your tremendous helpful effort up to this point,
Dallman

==============

 

[Max]

Sorry, think there was an error in my earlier offering

In sheet: Summary,
In A2:
=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL(X!$K:$K,ROWS($1:1))))

In A2 should be:
=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL(X!$K:$K,ROWS($1:1))+1))

.. It seems not to be putting all the unique data in place from the other
sheet,

Ok, going by the comment above, I've re-read your orig posting. I had
earlier assumed that you wanted only the combined shares lines to be
extracted over. I see now that you want all the uniques, ie inclusive of
those "single" lines in the source which doesn't require combining.

Here's the revised sample which should now accomplish it:
http://www.flypicture.com/download/NjE3Mg==
Combine lots sold on same day f same price f same stock_v2.xls

The revised construct
--------------------------
In source sheet: X,

In J2:
=IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2)*(G$2:G2=G2)*B$2:B2))

In K2:
=IF(A2="","",IF(OR(SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2)*(G$2:G2=G2))>1,SUMPRODUCT((A$2:A$20=A2)*(F$2:F$20=F2)*(G$2:G$20=G2))=1),ROWS($1:1),""))
Leave K1 blank. Select J2:K2, copy down to cover the max expected extent of
source data. Col K serves to flag the all the "unique" lines to be copied
over to the summary sheet. Note that you need to change the ranges within
the 2nd sumproduct (A$2:A$20, F$2:F$20, etc) to suit the actual expected
extent of the source data. I used row20 as the extent arbitrarily.

In sheet: Summary,
A1:I1 carries the same col headers as in X

In A2:
=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL(X!$K:$K,ROWS($1:1))+1))

In B2:
=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!J:J,SMALL(X!$K:$K,ROWS($1:1))+1))

In C2:
=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!C:C,SMALL(X!$K:$K,ROWS($1:1))+1))

In D2:
=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!D,SMALL(X!$K:$K,ROWS($1:1))+1))

In E2:
=IF(B2="","",B2*D2)

In F2:
=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!F:F,SMALL(X!$K:$K,ROWS($1:1))+1))

In G2:
=IF(ROWS($1:1)>COUNT(X!$K:$K),"",INDEX(X!F:F,SMALL(X!$K:$K,ROWS($1:1))+1))

In H2:
=IF(B2="","",G2*B2)

In I2:
=IF(B2="","",H2-E2)

Select A2:I2, fill down to cover the same extent as filled in X's cols J and
K.

As for your comment:

I'm going to want to go with some VBA code to do this task
and deposit the result as values in the new second sheet, though.

I'm not vba conversant enough to offer you this route. But perhaps if the
expected source data extent is something that is quite fixed, think you
could try recording a macro when you perform the construct steps outlined,
inclusive of a couple of additional steps for an entire sheet copy n paste
special as values to freeze values in Summary. Alternatively, I would
suggest you try a fresh posting in .programming for insights from responders
versed in vba. Since this thread is quite dated and deep, I'm not sure
whether there are other responders still tracking developments here <g>.
All the best ..

 

[Dallman Ross]

I've re-read your orig posting. I had earlier assumed that you
wanted only the combined shares lines to be extracted over. I see
now that you want all the uniques, ie inclusive of those "single"
lines in the source which doesn't require combining.

Correct. Sorry for not responding sooner. My business is
stocks, and as you probably noticed, it was rather a hellish week.

Here's the revised sample which should now accomplish it:
http://www.flypicture.com/download/NjE3Mg==
Combine lots sold on same day f same price f same stock_v2.xls

Really neat stuff, Max. Great work! Thank you very much.

Regarding my VBA ideas, you're right -- I'll re-write my goals
and post to the programming group for that if I still think I
need to go that route.

Muchas gracias!
Dallman

====================================================

 

[Max]

welcome, Dallman. Thanks for posting back.