Using a general ledger profile to break down account activity in a trial balance

By default, LUSID reports each general ledger account in a trial balance on a single line, automatically aggregating all the debits and credits in that account.

Consider the following example, of a trial balance for an ABOR with four accounts and debits and credits aggregated separately for each:

You can use a general ledger profile to break down one, some or all accounts into levels (or categories), to aggregate debits and credits in a more granular way. For example, you could break down all four accounts into their constituent asset classes (see the level 1 column below). Debits and credits for accounts with multiple asset classes (1-Investments and 4-PnL) are now aggregated separately; the totals, however, remain the same:

You could go further and break down the asset classes of just the 1-Investments account by currency (see the level 2 column below). Now, debits and credits are aggregated separately for GBP equities and USD equities:

You could go further still and break down the currencies of the 1-Investments account by economic bucket (see the level 3 column below), and so on:

To enable breakdown of account activity in a trial balance in this way, you must:

1. Add a general ledger profile to your chart of accounts, with an ordered set of mappings that LUSID uses to apply levels to journal entry lines matching the rules, and which then impact the trial balance. Syntax of a mapping.
2. Apply the general ledger profile when you create a trial balance.

For each journal entry line generated in preparation for the trial balance, LUSID uses the first matching mapping found in the general ledger profile. For example, to implement the scenario above and break down all accounts by asset type, and further break down the 1-Investments account by currency and then by economic bucket, your general ledger profile might look like this:

{
  "chartOfAccountsId": {
    "scope": "Abor",
    "code": "DailyNAV"
  },
  "generalLedgerProfileCode": "DailyNAV",
  "displayName": "GL profile for daily NAV",
  "generalLedgerProfileMappings": [
    {
      "mappingFilter": "GeneralLedgerAccountCode eq '1-Investments'",
      "levels": [
        "Instrument.AssetClass",
        "DefaultCurrency",
        "EconomicBucket"
      ]
    },
    {
      "mappingFilter": "True",
      "levels": [
        "Instrument.AssetClass"
      ]
    }
  ],
  ...
}

For each generated journal entry line, LUSID:

1. Applies three levels if the journal entry line represents a debit or credit to the 1-Investments account, in the following order: asset class, currency, economic bucket.
2. If the journal entry line represents a debit of credit to any other account, LUSID applies a single level of asset class.

In this example, mapping 2 is a ‘catch all' rule that applies levels to unmatched accounts. You could omit mapping 2, in which case accounts other than 1-Investments would have a level of No matching mapping. Best practice might be to specify a final mapping that always evaluates with an empty set of levels for unmatched journal entry lines, for example:

{
  "mappingFilter": "True",
  "levels": []
}

Syntax of a general ledger profile mapping

Each general ledger profile mapping consists of:

• A mappingFilter expression that describes criteria for matching journal entry lines. More information.
• Up to five levels to apply to journal entry lines matching those criteria, specified as an ordered list. More information.

Mapping filter expressions

The syntax of a mappingFilter expression is:

<attribute> <operator> <value>

...where:

• <attribute> is a field, property or other data item to test against. Allowed values are GeneralLedgerAccountCode and all the attributes in this table.
• <operator> is one of the LUSID filter operators.
• <value> is a value appropriate to the data type of <attribute>.

Note the following:

• A mappingFilter expression is case-insensitive.
• A string <value> must be enclosed in single straight quote marks (the %27 UTF-8 encoding).
• You can concatenate expressions using the and and or operators, for example:

  GeneralLedgerAccountCode eq '1-Investments' and subholdingkeys[Transaction/Ibor/Strategy] eq 'Income'

  If you use both, standard boolean operator precedence applies:

  EconomicBucket startswith 'NA' or (DefaultCurrency in 'GBP', 'USD' and HoldType eq 'P')

Levels

You can specify up to five levels as an ordered, comma-separated list of strings. In the following example, Instrument.AssetClass is the first (or most significant) level, and Account.type the last (or least):

["Instrument.assetClass", "Properties[Portfolio/Ibor/Region]", "DefaultCurrency", "EconomicBucket", "Account.type"]
 

Available <levels>	Syntax example	Notes
ActivityDate		For a journal entry line originating from a transaction, this is the trade date. For a journal entry line originating from a valuation, this is the date of the latest detected change to market data.
EconomicBucket		LUSID automatically categorises a journal entry line into a broad economic bucket. More information.
DefaultCurrency		The currency of the instrument underlying the journal entry line, if specified.
A sub-holding key on a portfolio	subholdingkeys[Transaction/Ibor/Strategy]	Under-the-hood, a SHK is defined as a custom property with a 3-stage key in the Transaction domain.
A portfolio field	Portfolio.displayName Portfolio.parentPortfolioId.Code	This can be any of the stored fields for a portfolio except the href, links and version fields, prefixed by Portfolio. Nested fields can be accessed using dot notation.
A portfolio property	properties[Portfolio/Ibor/Manager]	This can be any property with a 3-stage key in the Portfolio domain.
An instrument field	Instrument.identifiers['LusidInstrumentId'] Instrument.instrumentDefinition.instrumentType	This can be any of the stored fields for an instrument except the href, links and version fields, prefixed by Instrument. Nested fields can be accessed using dot notation.
An instrument property	properties[Instrument/Ibor/AnalystRating]	This can be any property with a 3-stage key in the Instrument domain.
A transaction field	Transaction.type Transaction.transactionDate	This can be any of the stored fields for a transaction, prefixed by Transaction. Nested fields can be accessed using dot notation.
A transaction property	properties[Transaction/Ibor/Broker]	This can be any property with a 3-stage key in the Transaction domain.
An account field	Account.type	This can be any of the stored fields for an account, prefixed by Account. Nested fields can be accessed using dot notation.
An account property	properties[Account/Type/AssetClass]	This can be any property with a 3-stage key in the Account domain.
An ABOR property	properties[Abor/Client/Accountant]	This can be any property with a 3-stage key in the Abor domain.