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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ticker[/TD]
[TD]Trade Date[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[TD]Broker[/TD]
[TD]Client Name[/TD]
[/TR]
[TR]
[TD]MSFT US[/TD]
[TD]09/15/2017[/TD]
[TD]300[/TD]
[TD]10[/TD]
[TD]Broker1[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]MSFT US[/TD]
[TD]09/15/2017[/TD]
[TD]500[/TD]
[TD]10[/TD]
[TD]Broker1[/TD]
[TD]Jill[/TD]
[/TR]
[TR]
[TD]MSFT US[/TD]
[TD]09/15/2017[/TD]
[TD]200[/TD]
[TD]10[/TD]
[TD]Broker1[/TD]
[TD]Jenny[/TD]
[/TR]
[TR]
[TD]RDSA LN[/TD]
[TD]09/15/2017[/TD]
[TD]1000[/TD]
[TD]5[/TD]
[TD]Broker2[/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD]RDSA LN[/TD]
[TD]09/15/2017[/TD]
[TD]300[/TD]
[TD]5[/TD]
[TD]Broker2[/TD]
[TD]Susie[/TD]
[/TR]
[TR]
[TD]RDSA LN[/TD]
[TD]09/15/2017[/TD]
[TD]500[/TD]
[TD]4.99[/TD]
[TD]Broker1[/TD]
[TD]Jenny[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'd like this data to be:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ticker[/TD]
[TD]Trade Date[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[TD]Broker[/TD]
[TD]Client Name[/TD]
[/TR]
[TR]
[TD]MSFT US[/TD]
[TD]09/15/2017[/TD]
[TD]1000[/TD]
[TD]10[/TD]
[TD]Broker1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RDSA LN[/TD]
[TD]09/15/2017[/TD]
[TD]1300[/TD]
[TD]5[/TD]
[TD]Broker2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RDSA LN[/TD]
[TD]09/15/2017[/TD]
[TD]500[/TD]
[TD]4.99[/TD]
[TD]Broker1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.