I have to give out a daily report for work with brokerage earnings.
The pivot table is as follows.
Broker is on the top row, followed by a seperator for listed / OTC trades
Bank is in the right column.
The value used to generate the numbers in the chart is net commision. This is seperated per broker by OTC / Listed/
The trouble I am having is that my boss wants to see the total as a grand total of OTC and listed trades ( most brokers do both). I can't figure out how to get it to automatically sum up both into a row below the pivot table total.
He also wants a column under that with % of group total, and one with daily average below.
I need to find a way to automatically do this, as manually formating it everyday is much too time consuming.
Keep in mind I have a daily, weekly, monthly, quarterly, and yearly tab for this. These change size everyday, so everyday when I generate the pivot table with the days trades it changes size (if new banks trade with us)
Below is a brief view of how this looks.
Afinch Cblack Dsmith
Bank Listed Listed OTC
AGS 68
Barclays
Bluefin
BNP
Citadel 53
Citi 50
Consolidated
Credit Suisse
Deutsche Bank
Merrill Lynch
Morgan Stanley
Nomura 1,148
Oppenheim 11,463
RBS 750
Societe Generale
Spot 912
Stutland
Susquehenna 122
UBS 175
Wolverine 600
total 13,814 175 600 750
Grand Total 13,814 775 750
% of total 27.55% 1.55% 1.50%
Daily Average xx 7654 xxx
Afoley Cbenson Dwhitehall
Any ideas how I would do this? I have tried everything but can't figure out how to add these rows into the bottom. If anyone wants to exchange emails with me to help with this I would be very grateful.
Thanks and look forward to hearing for the help
Bookmarks