Automating recurring processes: Formulas, Macros, or Pivot tables?

[badass]

So here’s my problem. I have to prepare a “portfolio summary report” (See FINAL PRODUCT below) for many different entities that I work with. I want to know if there is a better way of doing this portfolio summary report than the way that I’m currently doing it. I am looking to automate the production of this report so that it is error free and I can get it done on a timely basis. But I am clueless on how to go about it. Let me explain how I do this…

I have my “source data” below and from all this data, and from this I create a pivot table. The pivot table’s ROWS must list items in the following order 1) FS line item 2) Security type 3) Country 4) Industry and then in the COLUMNS there should be totals for 1) Level 1, 2) Level 2, 3) Level 3, 4) Total fair value 5) Percentage of NAV and 6) Total cost in that order…

SOURCE DATA

So based on this above “source data”, I create this pivot table and here are the results of the pivot table.

PIVOT TABLE

The things that are wrong with the pivot table are that it looks nothing like my final product, which is below. I have to further process this pivot table to get it looking like this below final product.

FINAL PRODUCT

The problem with the pivot table is that I have to further process a lot of this information manually in order to get it to look like the final product…The things that I have to do after I created the pivot table are as follows:

1. Delete all subtotals (highlighted in yellow on the Pivot table) for the 1) FS line item, 2) Security type, and 3) Country. This takes quite a bit of time to delete all these values when there are big data sets and mistakes can be made…Is there any way to prevent the pivot table from reporting the totals for these rows?

1. Then the second problem is that if any item from the source data whose Percentage of NAV is greater than 5%, then that item must be broken out separately from the data (see the final product, under Germany, Auto manufacturers, where Volkswagen is broken out separately due to its value being over 5% (Orange highlight). How can this process be automated? So if a specific value is above 5%, can we automatically disclose this item by itself in the table?

1. This final process is another manual process that needs to be automated but I’m not sure how to go about it. If you look at the final product and notice the total categories (in red text) have a total for each item type with “proceeds in parenthesis and an amount”, how can we get excel to automate putting this totals in to these cells? For example, how can I get excel to put the “Total United States (proceeds $837,387,243)” and “Total Equity securities (proceeds $1,220,365,663)” in this format? The way I am calculating the “Total United States (proceeds $837,387,243)” and “Total Equity securities (proceeds $1,220,365,663)” is manually because I don’t know how else to do it…This takes a lot of time to do and also prone to errors...

SO…

Is this possible to fully automate this report? With formulas, pivot tables, or macro?
Any help and suggestions would be appreciated…

And if anything needs clarification, please let me know…thanks!

 

[badass]

Any of the excel gurus have any input on this topic please? It's been itching at more for a long time and I need a solution real quick!