Collapsing trading data from client allocations to get a total execution figure for the day

[mozza90]

Hi Folks,

I'm struggling to manipulate a data set I have and am hoping someone can help with a solution either in VBA/Pivot table/formula. I have a list of trades which are executed, then this trade is split down among a group of clients. For example, we buy 1,000 MSFT US @ $10 per share. These 1,000 shares are then split down and allocated to some clients. So Jack gets 300, Jill gets 500 and Jenny gets 200.

When I receive the data, I get the client breakdown rather than the total executed on the day. Below is an example:

Ticker	Trade Date	Quantity	Price	Broker	Client Name
MSFT US	09/15/2017	300	10	Broker1	Jack
MSFT US	09/15/2017	500	10	Broker1	Jill
MSFT US	09/15/2017	200	10	Broker1	Jenny
RDSA LN	09/15/2017	1000	5	Broker2	Paul
RDSA LN	09/15/2017	300	5	Broker2	Susie
RDSA LN	09/15/2017	500	4.99	Broker1	Jenny

<tbody>
</tbody>

I'd like this data to be:

Ticker	Trade Date	Quantity	Price	Broker	Client Name
MSFT US	09/15/2017	1000	10	Broker1
RDSA LN	09/15/2017	1300	5	Broker2
RDSA LN	09/15/2017	500	4.99	Broker1

<tbody>
</tbody>

The main considerations are:
1) Sum quantity if ticker, trade date and broker are all the same
2) Price should be the price, e.g. 10, not 30 in the above example

Also to note, I could have a data set of trades over a week or month so the trade date changes. We could trade the same ticker with different brokers on the same day, so these need to be separate rows. Client name is irrelevant once the trade is totaled (it can have a name there if it is easier, but it doesn't matter).

Whilst using a pivot table does sum all of this data for me, I then need to take this data and perform further calculations using the total executed on the day. This makes the pivot table difficult to use for a 1000+ row data set. Is there a simpler way to summarise this data?

Thanks!

 

[BGY23]

Hi,

Using your original data create a pivot table:

Row area Ticker, Trade date, Price, Broker
Values area Quantity.

Now go onto the ribon (whilst in the pivot table) and select analyze, options, Display and tick the "classic pivot table layout" box.

In the pivot table remove totals as required to get the layout you want.

You can now prioritise the layout for price and broker depending on your requirements.

Good luck.

 

[mozza90]

That's great and works perfectly. I didn't know there was a way to select classic layout, solves all the issues I was having with the new format pivot tables.

Thank you!

 

[BGY23]

That's great and works perfectly. I didn't know there was a way to select classic layout, solves all the issues I was having with the new format pivot tables.

Thank you!

You're Welcome

Yes ! Not every improvement is an Improvement. I mush prefer the "classic" layout.