Creating custom integrations for SFTP or API data sources

LUSID’s record capture framework enables you to build custom integrations that ingest data into LUSID from your own SFTP servers or external APIs. These self-built integrations function similarly to standard LUSID integrations, allowing you to process data from sources that don’t have dedicated integrations.

This integration feature is ideal for:

• Ingesting trades from external APIs without dedicated LUSID integrations

• Processing data from your own SFTP servers

• Triggering custom workflows based on incoming data

• Building flexible, configurable data pipelines

Prerequisites

Before getting started, ensure you have:

• Contacted your FINBOURNE representative to enable the custom integrations framework in your LUSID domain

• An SFTP server or API endpoint with authentication credentials

Supported data types and methods

Currently, you can map and ingest the following entities into LUSID via the custom integrations framework:

• Instruments

• Portfolio properties

• Holdings

• Transactions

• Quotes

• Legal entities

• Relational datasets

Setting up the integration

Step 1: Create an integration instance

An integration instance is a configuration of data points to import from an external SFTP or API.

1. Navigate to Integrations > Dashboard.

2. Click Create instance and select Record capture framework from the available integrations.
   

3. Specify the following in the Integration details tab:

   • Name: A friendly name for this integration instance

   • Description: A detailed description of the instance

4. Go to the Integration settings tab and specify the following:

   • Run date: Optional; defines the date for which the integration should run, defaulting to yesterday’s date (most recent close of business (COB)). You should only populate this field for adhoc reruns.

   • File date: Optional; defines the date for which LUSID should choose a file, defaulting to today’s date. You should only populate this field for adhoc reruns.

   • Drive root folder: The location of your control file in LUSID Drive.

   • Control filename: The name of your JSON configuration file that tells LUSID how to process the data in a custom integration file.

   • Additional parameters: The following additional parameters are currently supported:

     • row_limit:<integer>: Limit the number of rows processed from the input file, for example row_limit:20

   • API configuration: If importing data from an API, specify the following:

     • API Base URL

     • API Username

     • API Password

   • SFTP configuration: If importing data via SFTP, provide the SFTP configuration details

5. Click Save to create the integration instance.

Step 2: Supply configuration files to LUSID Drive

The record capture framework uses two types of configuration files stored in LUSID Drive to define mappings for your custom integration:

Control file

The control file defines how LUSID should transform data retrieved from an external source. Each control file must contain an array of fileSpecs in JSON format specifying:

• Which Drive folder to store the retrieved file

• What to name the retrieved file

• The location of mapping files in Drive

• How to transform the source data

For example:

{
  "FileReference": "positions-file",
  "FileCategory": "primary",
  "SftpServer": 1,
  "SftpSourceFolder": "/acme/positions/Data",
  "DriveSubfolder": "COBDATE:yyyy-MM-dd",
  "FileNameMask": "POSITIONS_COBDATE:yyyyMMdd_*.csv",
  "RenameAs": "positions.csv",
  "DataReaderType": "CsvReader",
  "MappingFile": "positions-mapping.json",
  "ImportTypes": ["Instruments", "Holdings"]
}

See Appendix A for the full list of control file fields with detailed descriptions.

Mapping file

The mapping file defines how data fields in the source file map to LUSID entities and, if necessary, how LUSID should transform values. Each mapping file can contain the following components in JSON format:

• The source columns from your input data

• Value sources that allow you to combine or transform values from multiple columns

• Map specific source values to target values

• Conditional value sources that enable conditional logic for mapping, including:

  • Instrument type selectors

  • Identifiers that reference value maps

• Load controls that define processing rules for each entity type

• Data quality checks that use LUSID filtering syntax to skip rows with specific characteristics

• Plug-in architecture that allows for custom hard-coded behaviour beyond the standard configuration options

For example:

{
  "CsvSeparator": "Comma",
  "ColumnSpecs": [
    { "ColumnHeader": "ISIN", "ColumnReference": "IsinColumn" },
    { "ColumnHeader": "Quantity", "ColumnReference": "QtyColumn" }
  ],
  "ValueSources": [
    {
      "Name": "IsinValue",
      "ColumnReferences": ["IsinColumn"]
    }
  ],
  "InstrumentsLoadControl": {
    "CreateMissingInstruments": true,
    "Identifiers": [
      {
        "IdentifierType": "Isin",
        "IdentifierValueSourceName": "IsinValue",
        "IsUnique": true
      }
    ]
  }
}

See Appendix B for the full list of mapping file fields with detailed descriptions and more examples.

Step 3: Configure portfolios for holdings/transactions loads

Before processing holdings or transactions, you must configure the target portfolios with the following properties:

You can use PortfoliosLoadControl to upsert these properties via the record capture framework itself. Read about other ways to add properties to portfolios.

Running the integration

See how to run the integration.

Monitoring and troubleshooting integration runs

Logs for LUSID’s record capture framework function like other LUSID integration logs.

See how to monitor integrations and troubleshoot general issues.

Appendix A: Control file fields

LUSID’s record capture framework uses a control file to define which source files to process and how to retrieve them. Each entry in the control file is a FileControlSpec that specifies configuration such as:

• File retrieval

• File processing

• Exception handling tasks in the Workflow Service

Naming the FileControlSpec

The following fields allow you to define the name and categorisation of this FileControlSpec:

FileReference

Mandatory; a unique name you can use to reference this FileControlSpec in other jobs.

FileCategory

Category of the FileControlSpec (e.g. primary, auxiliary)

SFTP file retrieval parameters

If the integration retrieves files via SFTP, the following fields allow you to specify the source of the file to retrieve and how LUSID should transform it:

SftpServer

1-based index indicating which SFTP server to use (defined in system configuration).

SftpSourceFolder

The source folder path in the SFTP server (e.g. /acme/data/transactions).

DriveSubfolder

Mandatory; the LUSID Drive location to load files into. Supports COBDATE:<format> or FILEDATE:<format> patterns, for example COBDATE:yyyy-MM-dd.

FileNameMask

Mandatory; a matching pattern to match files on (e.g. TRANSACTIONS_COBDATE:yyyyMMdd_*.csv). Uses COBDATE/FILEDATE substitution.

FileAvailabilityMarker

A file pattern (typically with a .cntl extension) to determine if the source file is ready for retrieval. Supports COBDATE:<format> or FILEDATE:<format> patterns, for example POSITIONS_COBDATE:yyyy-MM-dd_*.cntl.

RenameAs

A name for the file once loaded into LUSID.

API file retrieval parameters

If the integration retrieves data from an API, the following fields allow you to specify details for the API LUSID should retrieve data from:

ApiRetrievalType

An enum value for the type of API retrieval out of the following:

• None

• Enfusion

ReportPath

The path to append to the API URL for retrieving reports, for example /api/holdings.

ReportFilter

A filter to apply to the report request URL, for example &valueDate=10/28/2026.

File import parameters

The following fields allow you to specify import configuration details:

DataReaderType

One of the following data reader types:

MappingFile

The name of the mapping file for this integration in Drive.

ImportTypes

An array of any of the following data types the files in this integration can import:

• Instruments

• Holdings

• Portfolios (properties to existing portfolios)

• Transactions

• Quotes

• LegalEntities

• RelationalDatasets

MaxIngestionFailures

A limit for the number of ingestion failures that can occur for the file before LUSID stops the import.

Exception handling parameters

The following fields allow you to specify how LUSID should handle data quality check failures in runs of the integration:

WorkflowTaskScope and WorkflowTaskCode

The scope and code of a task definition in LUSID’s workflow service that LUSID should kick off a run of for DQ check failures.

LUSID data upsert parameters

The following fields allow you to specify how the integration should configure certain LUSID data points:

PortfolioScope

Specify a portfolio scope to only upsert holdings data to portfolios in the specified scope. This can be useful if you want to target a specific portfolio scope for holdings data imports, for example:

{
  "FileReference": "Holdings-Finbourne-Examples",
  "MappingFile": "holdings-mapping.json",
  "ImportTypes": ["Holdings"],
  "PortfolioScope": "Finbourne-Examples"
}

If no scope is specified, the integration processes all portfolios that match all other criteria, regardless of their scope.

InstrumentScopes

Specify an ordered list of LUSID scopes to search during instrument matching. The integration creates new instruments in the first scope in the list.

CutLabel

Specify a LUSID cut label to use as the EffectiveAt datetime for all the data the integration upserts. Defaults to 00:00 UTC.

Third party integration parameters

The following fields allow you to specify fields required for certain third party integrations:

BloombergRefreshInstanceId

Optionally, specify an integration instance ID to populate the value of the Bloomberg Data Licence Per Security Instrument/LUSIDConfig-Bloomberg-DLPerSecurity/RefreshReferenceData property.

Appendix B: Mapping file fields

LUSID’s record capture framework uses a mapping file to define how data from source files should be parsed, validated, and loaded into LUSID.

The integration guides data through the mapping file configuration in the following order:

1. Reads the file and applies ColumnSpecs

2. Validates data using DQChecks

3. If InstrumentsLoadControl is specified, creates/updates instruments

4. If PortfoliosLoadControl is specified, updates portfolio properties

5. If HoldingsLoadControl is specified, upserts holdings

6. If TransactionsLoadControl is specified, upserts transactions

7. If QuotesLoadControl and/or RelationalDatasetsLoadControl is specified, upserts quotes and/or relational datasets

CsvSeparator

Required when DataReaderType is set to CsvReader. Specify one of the following options for the delimiter used in CSV import files:

• Comma: Comma-separated (,) values (default)

• Pipe: Pipe-separated (|) values

• Tab

• None: Uses default behaviour

ExcelSpec

Required when DataReaderType is set to ExcelReader. Specify the following fields to define how to read data from an Excel file:

• SheetName: Case-sensitive worksheet name to process data from

• HeaderRowNumber: Row number of the column headers in the Excel worksheet. Note that blank or duplicate headers cause processing to fail.

• GlobalCells: An array of cells from the area above the headers (for example, fund name or report date) attached to every data row. Each list item should contain:

  • CellReference: The cell name in the Excel worksheet, for example B2

  • Reference: A name you can use to refer to this column in ColumnSpecs/ValueSources as usual

For example:

"ExcelSpec": {
  "SheetName": "EUHoldingsSheet",
  "HeaderRowNumber": 1,
  "GlobalCells": [
    { "CellReference": "B2", "Reference": "ReportDateColumn" }
  ]
}

ColumnSpecs

Specify an array of the columns in your source file and how to reference them in this mapping file. Each list item should contain:

• ColumnHeader: The column name as it appears in the source file

• ColumnReference: A reference name ending in column to use throughout the mapping file

For example:

"ColumnSpecs": [
  {
    "ColumnHeader": "Instrument ID",
    "ColumnReference": "InstrumentIdColumn"
  },
  {
    "ColumnHeader": "Price",
    "ColumnReference": "PriceColumn"
  }
]

FixedWidthRowSpecs

Required when DataReaderType is set to FixedWidthReader. Specify an array of row specifications for parsing plain-text files where fields are identified by character position and length (rather than delimiters). Each list item should contain:

• Reference: A label to identify this record type in the logs

• Global: Set to true to mark a header or summary row. The framework stores values from this row and makes them available to subsequent rows via FromGlobal: true.

• IdentifierSpec: Specifies how to recognize this record type

  • StartPos: Character position where the identifier starts (usually 1)

  • Length: Number of characters in the identifier. For example, a single-character value H requires a length of 1, while a multi-character value HDR requires a length of 3.

  • IdentifierValue: An exact string of characters that identify this record type (including whitespace)

• RowItemSpecs: An array of fields to extract from a line - in other words, each list item is a column you can reference in ValueSources

  • Reference: Column name to use in ValueSources

  • One of either:

    • FromGlobal: Set to true to use value from a global row instead of reading from the current line

    • StartPos and Length: Character position where the field starts and the number of characters to read, respectively

Note

All StartPos values are 1-indexed (the first character is position 1, not 0).

For example, if you wanted to load data from the following text file:

H20250101ACME FUND
D001     IBM    100   1500.00
D002     MSFT    50    420.00

You could use the following configuration:

"FixedWidthRowSpecs": [
  {
    "Reference": "HeaderRow",
    "Global": true,
    "IdentifierSpec": { "StartPos": 1, "Length": 1, "IdentifierValue": "H" },
    "RowItemSpecs": [
      { "Reference": "CobDateColumn",  "StartPos": 2,  "Length": 8 },
      { "Reference": "FundNameColumn", "StartPos": 10, "Length": 20 }
    ]
  },
  {
    "Reference": "DetailRow",
    "Global": false,
    "IdentifierSpec": { "StartPos": 1, "Length": 1, "IdentifierValue": "D" },
    "RowItemSpecs": [
      { "Reference": "CobDateColumn",  "FromGlobal": true },
      { "Reference": "FundNameColumn", "FromGlobal": true },
      { "Reference": "SeqNumColumn",   "StartPos": 2,  "Length": 8 },
      { "Reference": "TickerColumn",   "StartPos": 10, "Length": 6 },
      { "Reference": "QuantityColumn", "StartPos": 16, "Length": 6 },
      { "Reference": "PriceColumn",    "StartPos": 22, "Length": 8 }
    ]
  }
]

ValueSources

Specify an array of how to extract and transform values from columns in import files. Each list item should contain a unique reference Name ending in Value to use throughout the mapping file, and either:

• Value

• LogicalExpressions

• ColumnReferences

• MathematicalExpression

Value

Hardcode a specific value, for example:

{
  "Name": "InstrumentScopeValue",
  "Value": "default"
}

LogicalExpressions

Allows you to specify an array of complex logic for value transformations based on conditions.

Each list item should contain:

• Condition: A LUSID filtering syntax expression to evaluate the value source against. It must use the format <ValueSource> <Operator> <Specific value/ValueSource>.

  Supported operators

  The integration framework supports the following operators in addition to the standard LUSID filtering syntax supported operators:

  • InMapKeys: Value is a key in the defined ValueMaps, for example TypeValue InMapKeys ValidTypesMap

  • InMapValues: Value is a value in the defined ValueMaps, for example StatusValue InMapValues StatusMap

• Result: The value to return if the condition is met; this can be any of the following:

  • A specific value

  • One of the defined ValueSources

Always include an empty condition ("Condition": "",) as the last LogicalExpression; this ensures unexpected values are handled.

For example:

{
  "Name": "InstrumentTypeSelectorValue",
  "LogicalExpressions": [
    {
      "Condition": "AssetClassValue eq \"Equity\"",
      "Result": "Equity"
    },
    {
      "Condition": "AssetClassValue eq \"Cash\"",
      "Result": "SimpleInstrument"
    },
    {
      "Condition": "",
      "Result": "SimpleInstrument"
    }
  ]
}

ColumnReferences

A list of the columns that the value should be sourced from. You can additionally specify the following:

ValueConversion

Specify one of the following options to tell LUSID how to combine multiple columns:

• None: Takes the value from the first ColumnReference

  {
    "Name": "SedolValue",
    "ColumnReferences": ["SedolColumn"],
    "ValueConversion": "None"
  }

• Concatenate: Joins multiple ColumnReference values together using the specified ConcatenationSeparator

  {
    "Name": "FullNameValue",
    "ColumnReferences": ["FirstNameColumn", "LastNameColumn"],
    "ValueConversion": "Concatenate",
    "ConcatenationSeparator": " "
  }

• FirstNonEmpty: Uses the first non-empty ColumnReference value

  {
    "Name": "PreferredIdValue",
    "ColumnReferences": ["IsinColumn", "SedolColumn", "ClientIdColumn"],
    "ValueConversion": "FirstNonEmpty"
  }

• RegexExtraction: The integration applies a regex pattern to the value from either:

  • The first column in ColumnReferences

  • If no ColumnReferences are specified, the first item listed in ValueSources

  {
  	"Name": "IsinFromDescriptionValue",
  	"ColumnReferences": ["DescriptionColumn"],
  	"ValueConversion": "RegexExtraction",
  	"RegexPattern": "ISIN:\\\\s*([A-Z0-9]{12})"
  }

  Note

  • Regex patterns time out after 1 second; to avoid timeouts, use patterns that target specific locations (for example, ^ISIN:\\s*([A-Z0-9]{12}) rather than .*ISIN.*).

  • As the mapping file is JSON formatted, ensure you use double backslashes (for example, write \\d for a digit).

  • The framework only extracts the first parenthesised group in your pattern (if used).  

ConcatenationSeparator

The separator to use when ValueConversion is set to Concatenate; defaults to a space (“ “)

ValueMapName

Specify the name of a ValueMap to use for the value transformation, for example:

{
  "Name": "TransactionTypeValue",
  "ColumnReferences": ["TxnTypeColumn"],
  "ValueMapName": "TransactionTypeMap"
}

Note

If the column value is not a key in the specified ValueMap, the framework throws an error. You should specify a DQChecks check that validates InMapKeys before applying the map, or ensure your ValueMap includes all possible input values.

MultiplierValueSourceName

For numeric values, use this field to specify one of the defined ValueSources to multiply the numeric value by, for example:

{
  "Name": "ScaledQuantityValue",
  "ColumnReferences": ["QuantityColumn"],
  "MultiplierValueSourceName": "ContractSizeValue"
}

This can be useful for things such as:

• Converting contract quantities to actual units

• Scaling prices by exchange rates

• Calculating total consideration

• Applying percentage conversions

Note

• The base value and multiplier value must be parsaeable as decimals.

• LUSID returns the multiplied result as a string.

• LUSID throws an error if parsing fails, detailing which value failed.

IsCurrencyIdentifier

Set to true to add the prefix CCY_ to each value, for example:

{
  "Name": "CurrencyInstrumentIdValue",
  "ColumnReferences": ["CurrencyCodeColumn"],
  "IsCurrencyIdentifier": true
}

With the above example, LUSID might transform an original value of USD to CCY_USD.

MathematicalExpression

Specify a formula that can use other ValueSources to output a value. You can use any of the following in your formula:

• Names of ValueSources

• Numeric literals

• +, -, *, /

Note

The framework evaluates strictly left-to-right - there is no operator precedence.

For example:

{
  "Name": "CostValue",
  "MathematicalExpression": "PrincipalAmountValue / UnitsValue * 100"
}

RowIdentificationSourceName

Specify which ValueSources to use for identifying rows in logging and error messages.

ConditionalValueSources (legacy field)

Important

You must now supply conditional logic directly on ValueSources using LogicalExpressions. ConditionalValueSources is a legacy field and is merged into ValueSources at load time.

Allows you to specify an array of complex logic for value transformations based on conditions. See LogicalExpressions.

ValueMaps

Note

If the column value is not a key in the specified value map, the integration throws an error. Use a DQCheck with an InMapKeys operator to validate before applying the map, or ensure your value map includes all possible input values.

You can set up a conditional ValueSource to check a value exists in a value map using the InMapKeys or InMapValues operators, for example:

{
  "Condition": "RawTypeValue InMapKeys TransactionTypeMap",
  "Result": "Pass"
}

Key-value maps

You can specify an array of key-value pairs for mapping specific values. Each list item should contain:

• A unique reference Name suffixed with Map

• A dictionary of Map key-value pairs

"ValueMaps": [
  {
    "Name": "TransactionTypeMap",
    "Map": {
      "BUY": "Buy",
      "SELL": "Sell",
      "DIVIDEND": "StockDividend"
    }
  },
  {
    "Name": "FrequencyMap",
    "Map": {
      "Monthly": "1M",
      "Quarterly": "3M",
      "Semi-Annual": "6M",
      "Annual": "1Y"
    }
  }
]

Map from a Luminesce view at runtime

When lookup data lives in LUSID, you can populate a map from a Luminesce view at runtime.

Each list item should contain the following:

• Name: A unique reference name suffixed with Map

• View: The name of the Luminesce custom view

• InputColumn: The data to pass into the query at runtime

• OutputColumn: The data to use from the query output

Note

• The view must exist before an integration run starts.

• Queries timeout after 5 minutes.

For example:

"ValueMaps": [
  {
    "Name": "PolicyToInstrumentMap",
    "View": "PolicyCodeLookup",
    "InputColumn": "PolicyCode",
    "OutputColumn": "LusidInstrumentId"
  }
]

DQChecks

Allows you to specify how the integration should validate data before processing in LUSID. You can define filters for how to present the result of a DQ check on a row in the logs.

DQChecks apply across all your load controls; a Fail result prevents the load controls from processing the row. The Filter field available on individual load controls (such as HoldingsLoadControl) is scoped to that entity only, so a row filtered out at the holdings stage can still be processed as a transaction, for example.

Each list item should contain:

• A unique reference Name

• A list of ordered LogicalExpressions to evaluate for each row of data containing:

  • Condition: A LUSID filtering syntax expression to evaluate the value source against, as with conditional ValueSources

    Note

    • You cannot mix AND and OR inside a single condition.

    • If no condition matches, the integration sets the Result value to Fail.

  • Result: The value to return in the logs if the Condition is met; this can be any of the following:

    • Pass: No error recorded in the logs

    • Fail: Logged as error and row marked as failed

    • SkipWithMessage: Row is skipped and recorded in the logs as skipped

    • SkipSilently: Row is skipped but nothing is recorded in the logs

    Note

    If a check hits a SkipWithMessage or SkipSilently result, the integration stops any further DQ checking for that row of data.

• Optionally, a Message to log if a check fails

For example:

"DQChecks": [
  {
    "Name": "RequireInstrumentId",
    "Message": "Instrument ID is required",
    "LogicalExpressions": [
      {
        "Condition": "InstrumentIdValue neq \"\"",
        "Result": "Pass"
      },
      {
        "Condition": "",
        "Result": "Fail"
      }
    ]
  },
  {
  "Name": "SkipClosedPositions",
  "Message": "Skipping closed positions",
  "LogicalExpressions": [
    {
      "Condition": "QuantityValue eq \"0\" AND StatusValue eq \"Closed\"",
      "Result": "SkipWithMessage"
    },
    {
      "Condition": "",
      "Result": "Pass"
    }
  ]
}
]

Load controls

Each entity type has its own load control section defining how the entity should be processed. You only need to include sections for the entity types you specified in your control file ImportTypes list.

InstrumentsLoadControl

Required when ImportTypes includes Instruments.

Note

The record capture framework can only create instruments of type SimpleInstrument and Equity. The framework can match against and update other instrument types, but not create them.

For example:

"InstrumentsLoadControl": {
  "InstrumentTypeSelectorSourceName": "InstrumentTypeSelectorValue",
  "UnderlyingInstrumentHandlingSourceName": "UnderlyingHandlerValue",
  "BloombergDataRefreshSourceName": "BloombergDataRefreshValue",
  "CreateMissingInstruments": true,
  "Identifiers": [
    {
      "IdentifierType": "Isin",
      "IdentifierValueSourceName": "IsinValue",
      "IsUnique": true
    },
    {
      "IdentifierType": "Sedol",
      "IdentifierValueSourceName": "SedolValue",
      "IsUnique": false
    },
    {
      "IdentifierType": "ClientInternal",
      "IdentifierValueSourceName": "ClientIdValue",
      "IsUnique": false
    }
  ],
  "InstrumentDefControls": [
    {
      "InstrumentType": "SimpleInstrument",
      "InstrumentNameSourceName": "InstrumentNameValue",
      "DomesticCurrencySourceName": "CurrencyValue",
      "ForeignCurrencySourceName": "ForeignCcyValue",
      "SimpleInstrumentTypeSourceName": "SimpleTypeValue",
      "Properties": []
    },
    {
      "InstrumentType": "Equity",
      "InstrumentNameSourceName": "InstrumentNameValue",
      "DomesticCurrencySourceName": "CurrencyValue",
      "Properties": [
        {
          "LusidPropertyName": "Instrument/default/Sector",
          "ValueSourceName": "SectorValue"
        }
      ]
    }
  ],
  "CommonProperties": [
    {
      "LusidPropertyName": "Instrument/default/AssetClass",
      "ValueSourceName": "AssetClassValue"
    }
  ]
}

PortfoliosLoadControl

Required when ImportTypes includes Portfolios.

Note

All portfolios you wish to include in integration runs must be decorated with the following properties:

• Portfolio/rcf-config/LinkedFundCode matching the FundCode value in your source data

• Portfolio/rcf-config/IsIncluded set to true

For example:

"PortfoliosLoadControl": {
  "FundCodeSourceName": "FundCodeValue",
  "Properties": [
    {
      "LusidPropertyName": "Portfolio/default/Strategy",
      "ValueSourceName": "StrategyValue"
    },
    {
      "LusidPropertyName": "Portfolio/default/Manager",
      "ValueSourceName": "ManagerValue"
    }
  ]
}

HoldingsLoadControl

Required when ImportTypes includes Holdings.

Note

All portfolios you wish to include in holdings integration runs must be decorated with the following properties:

• Portfolio/rcf-config/LinkedFundCode matching the FundCode value in your source data

• Portfolio/rcf-config/IsIncluded set to true

• Portfolio/rcf-config/LoadPositions set to true

For example:

"HoldingsLoadControl": {
  "FundCodeSourceName": "FundCodeValue",
  "EffectiveDateSourceName": "EffectiveDateValue",
  "CurrencySourceName": "CurrencyValue",
  "CostSourceName": "CostValue",
  "QuantitySourceName": "UnitsValue",
  "TaxlotPriceSourceName": "PriceValue",
  "SubHoldingKeys": [
    {
      "LusidPropertyName": "Transaction/default/Strategy",
      "ValueSourceName": "StrategyValue"
    }
  ],
  "Properties": [
    {
      "LusidPropertyName": "Holding/default/MarketValue",
      "LusidPropertyDataType": "number",
      "ValueSourceName": "MarketValueUSDValue"
    }
  ],
  "Filter": [
  {
    "Name": "ExcludeZeroQuantity",
    "LogicalExpressions": [
      {
        "Condition": "QuantityValue eq \"0\"",
        "Result": "SkipSilently"
      },
      {
        "Condition": "",
        "Result": "Pass"
      }
    ]
  }
]
  "Aggregator": {
    "KeyValueSources": ["FundCodeValue", "InstrumentIdValue", "StrategyValue"],
    "SumValueSources": ["QuantityValue", "CostValue"]
  }
}

TransactionsLoadControl

Required when ImportTypes includes Transactions.

Note

All portfolios you wish to include in transactions integration runs must be decorated with the following properties:

• Portfolio/rcf-config/LinkedFundCode matching the FundCode value in your source data

• Portfolio/rcf-config/IsIncluded set to true

• Portfolio/rcf-config/LoadTransactions set to true

For example:

"TransactionsLoadControl": {
  "FundCodeSourceName": "FundCodeValue",
  "TransactionIdSourceName": "TransactionIdValue",
  "TransactionTypeSourceName": "TransactionTypeValue",
  "TransactionDateSourceName": "TradeDateValue",
  "SettlementDateSourceName": "SettlementDateValue",
  "UnitsSourceName": "UnitsValue",
  "TotalConsiderationCurrencySourceName": "CurrencyValue",
  "TotalConsiderationAmountSourceName": "ConsiderationValue",
  "TransactionPriceSourceName": "PriceValue",
  "Properties": [
    {
      "LusidPropertyName": "Transaction/default/Broker",
      "ValueSourceName": "BrokerValue"
    }
  ],
  "Filter": [
  {
    "Name": "OnlyEquities",
    "LogicalExpressions": [
      {
        "Condition": "AssetClassValue eq \"Equity\"",
        "Result": "Pass"
      },
      {
        "Condition": "",
        "Result": "SkipSilently"
      }
    ]
  }
  ],
  "Aggregator": {
    "KeyValueSources": ["FundCodeValue", "InstrumentIdValue", "StrategyValue"],
    "SumValueSources": ["QuantityValue", "CostValue"]
  }
}

QuotesLoadControl

Required when ImportTypes includes Quotes.

For example:

"QuotesLoadControl": {
  "ScopeSourceName": "QuoteScopeValue",
  "EffectiveDateSourceName": "QuoteDateValue",
  "ProviderSourceName": "ProviderValue",
  "InstrumentIdTypeSourceName": "IdTypeValue",
  "InstrumentIdSourceName": "InstrumentIdValue",
  "QuoteTypeSourceName": "QuoteTypeValue",
  "FieldSourceName": "FieldValue",
  "PriceValueSourceName": "PriceValue",
  "CurrencySourceName": "CurrencyValue",
  "Filter": []
}

LegalEntitiesLoadControl

Required when ImportTypes includes LegalEntities.

For example:

"LegalEntitiesLoadControl": {
  "DisplayNameSourceName": "DisplayNameValue",
  "DescriptionSourceName": "DescriptionValue",
  "Identifiers": [
    {
      "IdentifierType": "LegalEntity/default/LEI",
      "IdentifierValueSourceName": "LeiValue"
    },
    {
      "IdentifierType": "LegalEntity/default/InternalId",
      "IdentifierValueSourceName": "InternalIdValue"
    }
  ],
  "Properties": [
    {
      "LusidPropertyName": "LegalEntity/default/Country",
      "ValueSourceName": "CountryValue"
    }
  ]
}

RelationalDatasetsLoadControl

Required when ImportTypes includes RelationalDatasets.

For example:

"RelationalDatasetsLoadControl": {
  "RelationalDatasetDefinitionScope": "my-scope",
  "RelationalDatasetDefinitionCode": "my-dataset-code",
  "SeriesScopeSourceName": "SeriesScopeValue",
  "EntityTypeSourceName": "EntityTypeValue",
  "EntityIdentifierScopeSourceName": "EntityIdScopeValue",
  "Identifiers": [
    {
      "IdentifierType": "ClientInternal",
      "IdentifierValueSourceName": "RecordIdValue",
      "IsUnique": true
    }
  ],
  "EffectiveAtSourceName": "EffectiveDateValue",
  "ValueFieldsMap": {
    "Amount": "AmountValue",
    "Price": "PriceValue"
  },
  "MetaDataFieldsMap": {
    "Description": "DescriptionValue",
    "Category": "CategoryValue"
  },
  "Filter": []
}

Reference: Properties

Specify the following fields to define how to apply properties to a load control:

• LusidPropertyName: 3-stage property key in the format <domain>/<scope>/<code>  

• LusidPropertyDataType: One of the following data types for property values:

  • string (default)

  • number

  • date

• ValueSourceName: Where to source the property value from; see ValueSources

• IsPerpetual: Set to true if the property is perpetual, or false if time-variant; read more

• EffectiveFromName and EffectiveUntilName: Where to source the effective date range from for time-variant properties; see ValueSources

• DateFormats: An array of date formats (for example, ["M/d/yyyy", "MM/dd/yyyy", "yyyy-MM-dd"] ); the record capture framework attempts to apply each format to the value in order until it’s successful. The framework supports the following date formats:

  • yyyy-MM-dd (recommended)

  • yyyy-MM-ddTHH:mm:ssZ (recommended)

  • dd/MM/yyyy

  • MM/dd/yyyy

  • yyyy-MM-ddTHH:mm:ss

  • dd-MMM-yyyy

  • yyyyMMdd
    

Note

The framework combines the effective dates with the CutLabel specified in the control file.

Reference: Aggregators

Specify the following fields to define how to combine rows into a single entity:

• KeyValueSources: A list of ValueSources names that define the grouping key; rows are combined when they have matching values for all these fields

• SumValueSources: A list of ValueSources names; when rows are combined, these values are added together

For example:

"Aggregator": {
  "KeyValueSources": ["FundCodeValue", "InstrumentIdValue", "StrategyValue"],
  "SumValueSources": ["QuantityValue", "CostValue"]
}

Examples

ISIN,Name,Quantity,Price,Currency,Fund,Date
GB0002374006,Tesco PLC,1000,2.50,GBP,FUND001,2025-03-10
US0378331005,Apple Inc,500,150.00,USD,FUND001,2025-03-10

{
  "CsvSeparator": "Comma",
  "ColumnSpecs": [
    { "ColumnHeader": "ISIN", "ColumnReference": "IsinColumn" },
    { "ColumnHeader": "Name", "ColumnReference": "NameColumn" },
    { "ColumnHeader": "Quantity", "ColumnReference": "QtyColumn" },
    { "ColumnHeader": "Price", "ColumnReference": "PriceColumn" },
    { "ColumnHeader": "Currency", "ColumnReference": "CcyColumn" },
    { "ColumnHeader": "Fund", "ColumnReference": "FundColumn" },
    { "ColumnHeader": "Date", "ColumnReference": "DateColumn" }
  ],
  "RowIdentificationSourceName": "RowIdValue",
  "ValueSources": [
    {
      "Name": "RowIdValue",
      "ColumnReferences": ["IsinColumn", "FundColumn"],
      "ValueConversion": "Concatenate",
      "ConcatenationSeparator": "_"
    },
    {
      "Name": "IsinValue",
      "ColumnReferences": ["IsinColumn"]
    },
    {
      "Name": "InstrumentNameValue",
      "ColumnReferences": ["NameColumn"]
    },
    {
      "Name": "UnitsValue",
      "ColumnReferences": ["QtyColumn"]
    },
    {
      "Name": "PriceValue",
      "ColumnReferences": ["PriceColumn"]
    },
    {
      "Name": "CurrencyValue",
      "ColumnReferences": ["CcyColumn"]
    },
    {
      "Name": "FundCodeValue",
      "ColumnReferences": ["FundColumn"]
    },
    {
      "Name": "DateValue",
      "ColumnReferences": ["DateColumn"]
    },
    {
      "Name": "CostValue",
      "ColumnReferences": ["PriceColumn"],
      "MultiplierValueSourceName": "UnitsValue"
    }
  ],
  "DQChecks": [
    {
      "Name": "RequireIsin",
      "Message": "ISIN is required",
      "LogicalExpressions": [
        { "Condition": "IsinValue neq \"\"", "Result": "Pass" },
        { "Condition": "", "Result": "Fail" }
      ]
    },
    {
      "Name": "RequireQuantity",
      "Message": "Quantity must be greater than zero",
      "LogicalExpressions": [
        { "Condition": "UnitsValue gt \"0\"", "Result": "Pass" },
        { "Condition": "", "Result": "Fail" }
      ]
    }
  ],
  "InstrumentsLoadControl": {
    "CreateMissingInstruments": true,
    "Identifiers": [
      {
        "IdentifierType": "Isin",
        "IdentifierValueSourceName": "IsinValue",
        "IsUnique": true
      }
    ],
    "InstrumentDefControls": [
      {
        "InstrumentType": "Equity",
        "InstrumentNameSourceName": "InstrumentNameValue",
        "DomesticCurrencySourceName": "CurrencyValue",
        "Properties": []
      }
    ],
    "CommonProperties": []
  },
  "HoldingsLoadControl": {
    "FundCodeSourceName": "FundCodeValue",
    "EffectiveDateSourceName": "DateValue",
    "CurrencySourceName": "CurrencyValue",
    "CostSourceName": "CostValue",
    "QuantitySourceName": "UnitsValue",
    "TaxlotPriceSourceName": "PriceValue",
    "SubHoldingKeys": [],
    "Properties": []
  }
}

{
  "CsvSeparator": "Pipe",
  "ColumnSpecs": [
    { "ColumnHeader": "TxnId", "ColumnReference": "TxnIdColumn" },
    { "ColumnHeader": "AssetType", "ColumnReference": "AssetTypeColumn" },
    { "ColumnHeader": "Identifier", "ColumnReference": "IdColumn" },
    { "ColumnHeader": "TxnType", "ColumnReference": "TxnTypeColumn" },
    { "ColumnHeader": "TradeDate", "ColumnReference": "TradeDateColumn" },
    { "ColumnHeader": "SettleDate", "ColumnReference": "SettleDateColumn" },
    { "ColumnHeader": "Units", "ColumnReference": "UnitsColumn" },
    { "ColumnHeader": "Price", "ColumnReference": "PriceColumn" },
    { "ColumnHeader": "CCY", "ColumnReference": "CcyColumn" },
    { "ColumnHeader": "Portfolio", "ColumnReference": "PortColumn" }
  ],
  "RowIdentificationSourceName": "TxnIdentifierValue",
  "ValueSources": [
    {
      "Name": "TxnIdentifierValue",
      "ColumnReferences": ["TxnIdColumn"]
    },
    {
      "Name": "AssetTypeValue",
      "ColumnReferences": ["AssetTypeColumn"]
    },
    {
      "Name": "IdValue",
      "ColumnReferences": ["IdColumn"]
    },
    {
      "Name": "TxnTypeRawValue",
      "ColumnReferences": ["TxnTypeColumn"]
    },
    {
      "Name": "TradeDateValue",
      "ColumnReferences": ["TradeDateColumn"]
    },
    {
      "Name": "SettleDateValue",
      "ColumnReferences": ["SettleDateColumn"]
    },
    {
      "Name": "UnitsValue",
      "ColumnReferences": ["UnitsColumn"]
    },
    {
      "Name": "PriceValue",
      "ColumnReferences": ["PriceColumn"]
    },
    {
      "Name": "CcyValue",
      "ColumnReferences": ["CcyColumn"]
    },
    {
      "Name": "PortValue",
      "ColumnReferences": ["PortColumn"]
    },
    {
      "Name": "StandardTxnTypeValue",
      "ColumnReferences": ["TxnTypeColumn"],
      "ValueMapName": "TxnTypeMap"
    },
    {
      "Name": "TotalConsiderationValue",
      "ColumnReferences": ["PriceColumn"],
      "MultiplierValueSourceName": "UnitsValue"
    },
    {
      "Name": "InstrumentTypeSelectorValue",
      "LogicalExpressions": [
        {
          "Condition": "AssetTypeValue eq \"EQ\"",
          "Result": "Equity"
        },
        {
          "Condition": "AssetTypeValue eq \"CASH\"",
          "Result": "SimpleInstrument"
        },
        {
          "Condition": "",
          "Result": "SimpleInstrument"
        }
      ]
    }
  ],
  "ValueMaps": [
    {
      "Name": "TxnTypeMap",
      "Map": {
        "B": "Buy",
        "S": "Sell",
        "DIV": "StockDividend"
      }
    }
  ],
  "DQChecks": [
    {
      "Name": "ValidTxnType",
      "Message": "Transaction type must be valid",
      "LogicalExpressions": [
        { "Condition": "TxnTypeRawValue InMapKeys TxnTypeMap", "Result": "Pass" },
        { "Condition": "", "Result": "Fail" }
      ]
    },
    {
      "Name": "RequirePrice",
      "Message": "Price must be greater than zero",
      "LogicalExpressions": [
        { "Condition": "PriceValue gt \"0\"", "Result": "Pass" },
        { "Condition": "", "Result": "Fail" }
      ]
    }
  ],
  "InstrumentsLoadControl": {
    "InstrumentTypeSelectorSourceName": "InstrumentTypeSelectorValue",
    "CreateMissingInstruments": true,
    "Identifiers": [
      {
        "IdentifierType": "ClientInternal",
        "IdentifierValueSourceName": "IdValue",
        "IsUnique": true
      }
    ],
    "InstrumentDefControls": [
      {
        "InstrumentType": "SimpleInstrument",
        "InstrumentNameSourceName": "IdValue",
        "DomesticCurrencySourceName": "CcyValue",
        "SimpleInstrumentTypeSourceName": "AssetTypeValue",
        "Properties": []
      },
      {
        "InstrumentType": "Equity",
        "InstrumentNameSourceName": "IdValue",
        "DomesticCurrencySourceName": "CcyValue",
        "Properties": []
      }
    ],
    "CommonProperties": []
  },
  "TransactionsLoadControl": {
    "FundCodeSourceName": "PortValue",
    "TransactionIdSourceName": "TxnIdentifierValue",
    "TransactionTypeSourceName": "StandardTxnTypeValue",
    "TransactionDateSourceName": "TradeDateValue",
    "SettlementDateSourceName": "SettleDateValue",
    "UnitsSourceName": "UnitsValue",
    "TotalConsiderationCurrencySourceName": "CcyValue",
    "TotalConsiderationAmountSourceName": "TotalConsiderationValue",
    "TransactionPriceSourceName": "PriceValue",
    "Properties": []
  }
}

Best practice

Follow naming conventions

Always append suffixes to distinguish reference types:

• ColumnReference: Append "Column" → "IsinColumn", "PriceColumn"

• ValueSources Name: Append "Value" → "IsinValue", "PriceValue"

This makes it immediately clear what type of reference you're working with.

Validate early with DQ checks

Put critical validation at the beginning of your DQChecks array:

"DQChecks": [
  {
    "Name": "RequireIdentifier",
    "Message": "Instrument identifier is required",
    "LogicalExpressions": [
      { "Condition": "IsinValue neq \"\"", "Result": "Pass" },
      { "Condition": "", "Result": "Fail" }
    ]
  },
  {
    "Name": "ValidateBeforeMapping",
    "Message": "Transaction type must be valid",
    "LogicalExpressions": [
      { "Condition": "TxnTypeValue InMapKeys TransactionTypeMap", "Result": "Pass" },
      { "Condition": "", "Result": "Fail" }
    ]
  }
]

Reuse ValueSources

Define ValueSources once and reference them multiple times:

"ValueSources": [
  {
    "Name": "IsinValue",
    "ColumnReferences": ["IsinColumn"]
  }
]

Then use "IsinValue" in:

• DQChecks

• Conditional ValueSources

• Load controls

• Other ValueSources

Use ValueMaps for transformations

Always use ValueMaps instead of multiple conditional checks:

✅ Do:

"ValueMaps": [
  {
    "Name": "TransactionTypeMap",
    "Map": {
      "B": "Buy",
      "S": "Sell",
      "D": "Dividend"
    }
  }
],
"ValueSources": [
  {
    "Name": "StandardTxnTypeValue",
    "ColumnReferences": ["TxnTypeColumn"],
    "ValueMapName": "TransactionTypeMap"
  }
]

❌ Don’t:

"ValueSources": [
  {
    "Name": "StandardTxnTypeValue",
    "LogicalExpressions": [
      { "Condition": "TxnTypeRawValue eq \"B\"", "Result": "Buy" },
      { "Condition": "TxnTypeRawValue eq \"S\"", "Result": "Sell" },
      { "Condition": "TxnTypeRawValue eq \"D\"", "Result": "Dividend" }
    ]
  }
]

Always include default cases

Every conditional ValueSource should have a default case (empty condition):

{
  "Name": "InstrumentTypeSelectorValue",
  "LogicalExpressions": [
    {
      "Condition": "AssetClassValue eq \"Equity\"",
      "Result": "Equity"
    },
    {
      "Condition": "",
      "Result": "SimpleInstrument"
    }
  ]
}

This prevents errors when encountering unexpected values.

Test incrementally

Build your mapping file in stages:

1. Start with basic structure: ColumnSpecs and simple ValueSources

2. Add DQ checks: Validate required fields

3. Add one LoadControl: Test with a small file

4. Add complexity: conditional ValueSources, ValueMaps, additional LoadControls

5. Add advanced features: Filters, Aggregators, sub-holding keys

Use descriptive names

Make names self-documenting:

✅ Do:

• "InstrumentTypeSelectorValue"

• "SettlementDaysCalculatorValue"

• "ValidTransactionType" (DQ check)

❌ Don’t:

• "TypeValue"

• "Value1"

• "Check1"

Document complex logic

For sophisticated conditional ValueSources, add comments in your source control commit messages explaining the business logic.

Common snippets

Multi-identifier fallback

Use the first available identifier from multiple options:

{
  "Name": "BestIdentifierValue",
  "ColumnReferences": ["IsinColumn", "SedolColumn", "ClientIdColumn"],
  "ValueConversion": "FirstNonEmpty"
}

Conditional property setting

Only set a property when certain conditions are met:

{
  "Name": "MaturityDateValue",
  "ColumnReferences": ["MaturityColumn"]
},
{
  "Name": "MaturityPropertyValue",
  "LogicalExpressions": [
    {
      "Condition": "AssetClassValue eq \"Bond\"",
      "Result": "MaturityDateValue"
    },
    {
      "Condition": "",
      "Result": ""
    }
  ]
}

Then reference "MaturityPropertyValue" in properties - it returns empty string for non-bonds.

Composite key for row identification

Create a unique identifier by combining multiple fields:

{
  "Name": "RowIdValue",
  "ColumnReferences": ["FundColumn", "IsinColumn", "TradeDateColumn"],
  "ValueConversion": "Concatenate",
  "ConcatenationSeparator": "|"
}

Use this as your RowIdentificationSourceName for clear logging.

Nested conditional logic

Build reusable logic blocks and compose them:

{
  "Name": "USSettlementDaysValue",
  "LogicalExpressions": [
    { "Condition": "AssetClassValue eq \"Equity\"", "Result": "3" },
    { "Condition": "", "Result": "1" }
  ]
},
{
  "Name": "UKSettlementDaysValue",
  "LogicalExpressions": [
    { "Condition": "AssetClassValue eq \"Equity\"", "Result": "2" },
    { "Condition": "", "Result": "1" }
  ]
},
{
  "Name": "SettlementDaysValue",
  "LogicalExpressions": [
    { "Condition": "CountryValue eq \"US\"", "Result": "USSettlementDaysValue" },
    { "Condition": "CountryValue eq \"UK\"", "Result": "UKSettlementDaysValue" },
    { "Condition": "", "Result": "1" }
  ]
}

Validate before mapping

Always validate that a value exists in a ValueMap before applying it:

"DQChecks": [
  {
    "Name": "ValidateTransactionType",
    "Message": "Transaction type must be valid",
    "LogicalExpressions": [
      { "Condition": "RawTxnTypeValue InMapKeys TransactionTypeMap", "Result": "Pass" },
      { "Condition": "", "Result": "Fail" }
    ]
  }
],
"ValueSources": [
  {
    "Name": "StandardTxnTypeValue",
    "ColumnReferences": ["TxnTypeColumn"],
    "ValueMapName": "TransactionTypeMap"
  }
]

Conditional multiplier for scaling

Use conditional ValueSources to dynamically scale values:

{
  "Name": "ContractMultiplierValue",
  "LogicalExpressions": [
    { "Condition": "AssetTypeValue eq \"Future\"", "Result": "100" },
    { "Condition": "AssetTypeValue eq \"Option\"", "Result": "1000" },
    { "Condition": "", "Result": "1" }
  ]
},
{
  "Name": "ActualQuantityValue",
  "ColumnReferences": ["RawQuantityColumn"],
  "MultiplierValueSourceName": "ContractMultiplierValue"
}

Troubleshooting tips