Best Tools for Automating Portfolio Risk Reports (Advice Needed)

I am a trainee in a hedge fund company. My current tasks involve automating risk reports that need to be done weekly or less. Currently, everything is done very manually, copy pasting new securities in our portfolio and deleting ones that we sold already. As much as possible, my boss wants everything to be user-friendly so that any adjustments to be made can be done easily for someone with no coding knowledge. 

Here's the current workflow: we have raw, messy data of ~150 rows in the form of Excel ranges. I made a template to clean the data so that all the information comes in 4 tables, one of them being the portfolio holdings + various calculations like MTM, Dv01, average cost, and so on. All 4 tables then becomes the data source for a lot of calculations in different sheets. Each sheet is a different test of our portfolio, containing many tables as well. For example, credit spread test, default shock test, etc. Each of them will reference the 4 tables I mentioned, using VLOOKUPs, calculations like SUMs, quadruple-nested IF functions, and true/false checking, etc.

The data also needs to be smartly organized by the system automatically. After we divide securities from High Yield / IG, we mainly want an aggregate summing of the calculations whenever 1. securities are bought together (have the same Code) and 2. securities from the same company. For example (numbers are random) :

Sample InputSample Output

And then each of these output tests will have a summary statistic like summing all of the Value A columns in High Yield, for example.

So, in summary this is everything we are doing manually right now all in Excel: Raw Input -> Cleaned Input (has 4 tables including main table has portfolio) -> Multiple Sheet Output (each containing multiple tables). The output has a lot of aggregate summing, calculations and referencing other tables (from input datasource and also from across other output sheets).

Now, I want to use a different approach so that everything is done automatically, as in when new data gets imported as the 'Clean Input', all the complex calculations are done automatically. I've tried Power Query (use all 4 tables) + Power Pivot + DAX calculations but I don't think its possible to achieve the smart filtering and aggregate summing that we want, nor is it trivial to do calculations that involve values from different tables. Whenever we filter something, everything else is hidden, but we want everything to show up at once so that we can compare different positions in the portfolio.  I've since turned my attention to Power BI, but need your advice on if Power BI is indeed capable to do everything that I discussed so far in a quick and efficient, automatic manner. Ideally, everything can be done in a few mins or less with little manual intervention. If a sample file is needed to illustrate everything above, do let me know. Thanks in advance.