Help Automatically Sorting and Summing 3

[mcongdon]

Hi Everyone,
I'm not sure if I should be posting here or in the VBA forum, but I think that starting here would be good.

I have a spreadsheet with 38,000 lines in it. They contain data from stock trades. When buying or selling a stock, the shares don't always necessarily get bought or sold in one big piece. They go off in lots of 100 or 500 shares. I want to write a procedure to take (for example) the following data and combine "multi executions":

EXDATE    ACCT   SYMBOL    SHARES   PRICE   RCHRG   CCHRG

Add a new line where SHARES are summed, RCHRG is summed, and CCHRG is summed keeping EXDATE the same, ACCT the same, and SYMBOL the same

Basically looking for trades that might be pieces of a larger trade (Same EXDATE, ACCT, SYMBOL; close in price (+/-a few cents)) and combine the SHARES RCHRG and CCHRG to get the data for the whole trade.

Hope this makes some sense at all.

 

[SkipVought]

Hi,

Check out either the PivotTable wizard or the Data > Subtotal... feature.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mcongdon]

Hi Skip,
I may be not making sense or I might not understand what to do with a pivot table, but maybe if i gave you an example you'll see what I mean.

EXDATE    ACCT   SYMBOL    SHARES   PRICE   RCHRG   CCHRG
3/1/08    123     XYZ       100     1.25     5.00    2.00
3/1/08    123     XYZ       500     1.25     5.50    2.25
3/1/08    123     XYZ       200     1.25     5.25    2.50
3/1/08    123     XYZ       200     1.25     5.25    2.50
3/1/08    800     XYZ       100     1.25     5.00    2.00
3/3/08    800     XYZ       100     1.25     5.00    2.00
3/6/08    555     FFF       100     5.00     6.00    10.00
3/6/08    555     FFF       100     5.00     6.00    10.00
3/6/08    555     FFF       100     5.00     6.00    10.00

This should now look like:

EXDATE    ACCT   SYMBOL    SHARES   PRICE   RCHRG   CCHRG
3/1/08    123     XYZ       1000    1.25    21.00    9.25
3/1/08    800     XYZ       100     1.25     5.00    2.00
3/3/08    800     XYZ       100     1.25     5.00    2.00
3/6/08    555     FFF       300     5.00    18.00    30.00

 

[xlbo]

For the example you have posted, a pivot table would do the job

Group By (ROW Fields): EXDATE ACCT SYMBOL SHARES PRICE

Sum (Data Fields): RCHRG CCHRG


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

dirty example as Geoff explained procedurally...
[tt]
Data
EXDATE ACCT SYMBOL PRICE Sum of RCHRG Sum of CCHRG
3/1/2008 123 XYZ 1.25 21 9.25
800 XYZ 1.25 5 2
3/3/2008 800 XYZ 1.25 5 2
3/6/2008 555 FFF 5 18 30
Grand Total 49 43.25
[/tt]
Took LONGER to copy 'n' paste and parse the data into my sheet, than it took to do the PivotTable!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mcongdon]

Thanks guys it worked like a charm. The only thing that I wonder about is if there is any way to output the results of a pivot table so every line has all the data on it. So instead of:

3/1/08
    123
      XYZ
      FFF

It will look like this:

3/1/08   123   XYZ 
3/1/08   123   FFF

and so on and so forth. The only reason for this is that I have a huge report generator that has been already built to use the data in the original spreadsheet form, not the pivot table.

Thanks again guys

 

[SkipVought]

The try using Data > Subtotal....

You might be able to SUM on changes in ACCT, but you may have to concatenate EXTDATE & ACCT.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

There's no way to get a pivot tabel to fill in the blanks and keep it as a pivot table

As a 1 off, you can use the following:

copy the pivot table and paste special > values to transfer it to pure data

Select columns A:E (those that contain your ROW fields)

Ctrl+G (opens the "Goto box")
Select "Blanks" and click ok - this will select all the blank cells in your data

Press "=" and then the up arrow

then, rather than simply pressing "Enter" to submit the formula, press Ctrl + Enter. This will enter the same formula into all the blank cells and "fill in the blanks"

et voila

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Yet anpther option might be MS Query.

faq68-5829

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mcongdon]

Hey Guys,
What I ended up doing was to copy the pivot table and paste values into a new sheet. I then used some if statements to put binary values in new columns to use with filters. I ended up getting what I needed much faster than scrolling through like I was before. You all were very helpful with this one- I never really use pivot tables, so my skills were a little rusty ;-)

Thanks!