Grouping and aggregating metrics to create a meaningful valuation report

Views:

Related resources:

You can choose to group results in a valuation report by one or more metrics. If you do, you can aggregate grouped values for numeric metrics using mathematical operations such as Sum, Proportion and Average. Find out more about metrics and valid operations.

The metrics it makes sense to group by and aggregate values for are highly dependent on the kind of valuation report you want to produce.

Consider the example of this demo transaction portfolio, which has holdings divided into Growth and Income strategies, and BP and GBP in both:

Valuing without grouping

To perform a standard valuation of this portfolio, we might call the GetValuation API with the following request:

The groupBy field is omitted to report one result per holding.
The Value for each of a meaningful set of metrics is requested (metric operations highlighted in red below).
The PV metric (Valuation/PvInPortfolioCcy) is requested twice, once with its raw Value and once calculated as a Proportion:

curl -X POST "https://<your-domain>.lusid.com/api/api/aggregation/$valuation"
  -H "Authorization: Bearer <your-API-access-token>"
  -H "Content-Type: application/json-patch+json"
  -d '{
    "portfolioEntityIds": [ {"scope": "FBNUniversity", "code": "Module-3-1", "portfolioEntityType": "SinglePortfolio"} ],
    "valuationSchedule": {"effectiveAt": "2023-02-01T00:00:00.0000000+00:00"},
    "recipeId": {"scope": "FBNUniversity", "code": "Module-3-1Recipe"},
    "metrics": [
      {"key": "Instrument/default/Name", "op": "Value"},                            # Reports the friendly name of the underlying instrument
      {"key": "Valuation/EffectiveAt", "op": "Value"},                              # Confirms the valuation date
      {"key": "Holding/default/Units", "op": "Value"},                              # Reports the number of units held
      {"key": "Holding/Cost/Pfolio", "op": "Value"},                                # Calculates cost in GBP (the portfolio currency)
      {"key": "Quotes/Price", "op": "Value"},                                       # Retrieves market price from Quote Store
      {"key": "Valuation/PvInPortfolioCcy", "op": "Value"},                         # Calculates PV in GBP
      {"key": "Valuation/PvInPortfolioCcy", "op": "Proportion"},                    # Calculates PV as %
      {"key": "Valuation/PnL/Unrealised/PfolioCcy", "op": "Value"},                 # Calculates PnL in GBP (PV - Cost)
      {"key": "Transaction/FBNUniversity/InvestmentStrategy", "op": "Value"}        # Custom property (SHK) reporting the strategy
    ]
  }'

The response might look like this (transformed to a Pandas dataframe and with columns renamed for simplicity). Note one result is produced per holding, so BP and GBP are reported twice, once per strategy:

Grouping by underlying instrument

We can group by LUID (highlighted in yellow) to produce a single result for BP and GBP. To aggregate grouped values, we can Sum metrics where it makes sense to do so, for example for units, cost, PV and PnL. It doesn't make sense to Sum market prices, so we might choose to Average these instead (changed operations highlighted in red):

curl -X POST "https://<your-domain>.lusid.com/api/api/aggregation/$valuation"
  -H "Authorization: Bearer <your-API-access-token>"
  -H "Content-Type: application/json-patch+json"
  -d '{
    "portfolioEntityIds": [ {"scope": "FBNUniversity", "code": "Module-3-1", "portfolioEntityType": "SinglePortfolio"} ],
    "valuationSchedule": {"effectiveAt": "2023-02-01T00:00:00.0000000+00:00"},
    "recipeId": {"scope": "FBNUniversity", "code": "Module-3-1Recipe"},
    "groupBy": ["Instrument/default/LusidInstrumentId"],
    "metrics": [
      {"key": "Instrument/default/Name", "op": "Value"},                          # Reports the friendly name of the underlying instrument
      {"key": "Valuation/EffectiveAt", "op": "Value"},                            # Confirms the valuation date
      {"key": "Holding/default/Units", "op": "Sum"},                              # Sums the number of units held
      {"key": "Holding/Cost/Pfolio", "op": "Sum"},                                # Sums cost in GBP (the portfolio currency)
      {"key": "Quotes/Price", "op": "Average"},                                   # Averages market price
      {"key": "Valuation/PvInPortfolioCcy", "op": "Sum"},                         # Sums PV in GBP
      {"key": "Valuation/PvInPortfolioCcy", "op": "Proportion"},                  # Calculates PV as %
      {"key": "Valuation/PnL/Unrealised/PfolioCcy", "op": "Sum"},                 # Sums PnL in GBP (PV - Cost)
      {"key": "Transaction/FBNUniversity/InvestmentStrategy", "op": "Value"}      # Custom property (SHK) reporting the strategy
    ]
  }'

The response might look like this. Note that BP and GBP are reported once, and values for these holdings have been aggregated:

Grouping by strategy

We can group by the portfolio's sub-holding key (SHK, highlighted in yellow) to produce a result for each strategy. It no longer makes sense to report the instrument name or market price, so those metrics are omitted. Attempting to Sum units causes an aggregation error, since LUSID cannot aggregate units of a currency with units of equities, so this metric is also omitted:

curl -X POST "https://<your-domain>.lusid.com/api/api/aggregation/$valuation"
  -H "Authorization: Bearer <your-API-access-token>"
  -H "Content-Type: application/json-patch+json"
  -d '{
    "portfolioEntityIds": [ {"scope": "FBNUniversity", "code": "Module-3-1", "portfolioEntityType": "SinglePortfolio"} ],
    "valuationSchedule": {"effectiveAt": "2023-02-01T00:00:00.0000000+00:00"},
    "recipeId": {"scope": "FBNUniversity", "code": "Module-3-1Recipe"},
    "groupBy": ["Transaction/FBNUniversity/InvestmentStrategy"],
    "metrics": [
      {"key": "Valuation/EffectiveAt", "op": "Value"},                            # Confirms the valuation date
      {"key": "Holding/Cost/Pfolio", "op": "Sum"},                                # Sums cost in GBP (the portfolio currency)
      {"key": "Valuation/PvInPortfolioCcy", "op": "Sum"},                         # Sums PV in GBP
      {"key": "Valuation/PvInPortfolioCcy", "op": "Proportion"},                  # Calculates PV as %
      {"key": "Valuation/PnL/Unrealised/PfolioCcy", "op": "Sum"},                 # Sums PnL in GBP (PV - Cost)
      {"key": "Transaction/FBNUniversity/InvestmentStrategy", "op": "Value"}      # Custom property (SHK) reporting the strategy
    ]
  }'

The response might look like this:

Grouping by date

We can group by the valuation date (highlighted in yellow) to report a total for the portfolio as a whole. It no longer makes sense to report the Proportion of PV (which is always 100%), nor strategy names, so these metrics are omitted:

curl -X POST "https://<your-domain>.lusid.com/api/api/aggregation/$valuation"
  -H "Authorization: Bearer <your-API-access-token>"
  -H "Content-Type: application/json-patch+json"
  -d '{
    "portfolioEntityIds": [ {"scope": "FBNUniversity", "code": "Module-3-1", "portfolioEntityType": "SinglePortfolio"} ],
    "valuationSchedule": {"effectiveAt": "2023-02-01T00:00:00.0000000+00:00"},
    "recipeId": {"scope": "FBNUniversity", "code": "Module-3-1Recipe"},
    "groupBy": ["Valuation/EffectiveAt"],
    "metrics": [
      {"key": "Valuation/EffectiveAt", "op": "Value"},                        # Confirms the valuation date
      {"key": "Holding/Cost/Pfolio", "op": "Sum"},                            # Sums cost in GBP (the portfolio currency)
      {"key": "Valuation/PvInPortfolioCcy", "op": "Sum"},                     # Sums PV in GBP
      {"key": "Valuation/PnL/Unrealised/PfolioCcy", "op": "Sum"}              # Sums PnL in GBP (PV - Cost)
    ]
  }'

The response might look like this:

Keywords: metric, groupBy, Sum, Proportion, aggregation