Lusid.Portfolio.HoldingAdjustment

Type	Read/write	Author	Availability
Data provider	Read	Finbourne	Provided with LUSID

The Lusid.Portfolio.HoldingAdjustment provider enables you to write a Luminesce SQL query that retrieves holdings you have set or adjusted in one or more portfolios.

A single query answers two questions that otherwise take two API calls: on which dates holdings were set or adjusted (the ListHoldingsAdjustments API), and what exactly was set or adjusted on each of those dates (the GetHoldingsAdjustment API).

The provider returns what you asked LUSID to set: one record per tax lot, per holding, per date. To see the AdjustmentIncrease and AdjustmentDecrease output transactions LUSID generates to provide those holdings, use Lusid.Portfolio.Txn.Output instead.

Note the following:

Always specify a date or a date range using the EffectiveAt and EffectiveFrom parameters. If you specify neither, the provider looks only for holdings set or adjusted at the exact moment you run the query, so it almost always returns no records. See how to choose dates.
By default, Lusid.Portfolio.HoldingAdjustment cannot retrieve properties. To do this, you must first configure the provider to 'inline' properties from the Holding, Instrument, Transaction (sub-holding keys) or Portfolio domains. See how to do this.

Basic usage

select * from Lusid.Portfolio.HoldingAdjustment where EffectiveFrom = #<date># and EffectiveAt = #<date># [and <filter-expression>];

Query parameters

Lusid.Portfolio.HoldingAdjustment has parameters that enable you to filter or refine a query. None is mandatory, but the date parameters decide which dates the provider looks at, so in practice you need at least one of them:

EffectiveAt on its own. Returns holdings set or adjusted at exactly this date and time. For example, EffectiveAt = #2026-11-07# returns a set or adjust made effective at midnight on 7 November. It does not return holdings set on an earlier date and still in force: if you set holdings on 7 November, EffectiveAt = #2026-11-08# returns nothing.
EffectiveFrom and EffectiveAt together. Returns every set and adjust between the two dates, including both. EffectiveAt can be in the future, so you can include holdings set for a future date.
EffectiveFrom on its own. Returns every set and adjust from this date up to the moment you run the query. Holdings set for a future date are not included.
AsAt. Returns sets and adjusts as they were recorded in LUSID at a point in the past, for example to see what was set before a correction. Defaults to the latest.

Note: Filtering on the EffectiveDate field is not a substitute for these parameters. For example, where EffectiveDate = #2026-11-07# returns no records, because the provider still looks only at the current moment and then filters the (empty) results. Use EffectiveAt = #2026-11-07# instead.

We recommend setting EffectiveFrom no earlier than the date the portfolio was created (the Created field of Lusid.Portfolio). An earlier date still returns every set and adjust, but also returns records with a populated Error field for the period before the portfolio existed.

To list all parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Lusid.Portfolio.HoldingAdjustment' and FieldType = 'Parameter';

Data fields

By default, Lusid.Portfolio.HoldingAdjustment returns a table of data populated with particular fields (columns). You can return a subset of these fields.

To list fields available to return, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.Portfolio.HoldingAdjustment' and FieldType = 'Column';

Note: Fields marked 'main' are returned by queries that select a ^ character, for example select ^ from Lusid.Portfolio.HoldingAdjustment.

Note the following about particular fields:

EffectiveDate is the date on which the holdings were set or adjusted.
UnmatchedHoldingMethod tells you whether the holdings were set or adjusted: PositionToZero for a set (holdings not in the request were reduced to zero) and KeepTheSame for an adjust (holdings not in the request were left untouched).
Units, CostAmount, Price, PurchaseDate, SettlementDate and the remaining amount fields are the values you supplied for each tax lot. A holding with more than one tax lot returns one record per lot.
SubHoldingKey identifies the holding, for example LusidInstrumentId=LUID_00003HDS/GBP, followed by any sub-holding keys.
InstrumentUid is the LUSID Instrument ID (LUID) of the instrument. A value of LUID_ZZZZZZZZ means LUSID could not find the instrument you specified when you set or adjusted the holding.
AsAtDate is when you made the set or adjust.

Errors

We recommend examining the results of every query using the Error field.

This field is populated if Lusid.Portfolio.HoldingAdjustment cannot retrieve set or adjusted holdings for some reason.

Examples

Note: For more example Luminesce SQL queries, visit our Github repo.

Example 1: List the dates on which holdings were set or adjusted in a particular portfolio

This is the equivalent of the ListHoldingsAdjustments API.

select distinct EffectiveDate, UnmatchedHoldingMethod, AsAtDate from Lusid.Portfolio.HoldingAdjustment
where PortfolioScope = 'Finbourne-Examples' and PortfolioCode = 'UK-Equities'
and EffectiveFrom = #2026-01-01# and EffectiveAt = #2026-12-31#
order by EffectiveDate

Example 2: Retrieve the holdings set or adjusted on a particular date in a particular portfolio

This is the equivalent of the GetHoldingsAdjustment API.

select * from Lusid.Portfolio.HoldingAdjustment
where PortfolioScope = 'Finbourne-Examples' and PortfolioCode = 'UK-Equities'
and EffectiveAt = #2026-11-07#

Example 3: Retrieve every set and adjust in a particular month in every portfolio in a particular scope

select * from Lusid.Portfolio.HoldingAdjustment
where PortfolioScope = 'Finbourne-Examples'
and EffectiveFrom = #2026-11-01# and EffectiveAt = #2026-11-30#

Example 4: Retrieve set or adjusted holdings in a particular portfolio, including properties

To retrieve properties, Lusid.Portfolio.HoldingAdjustment must have been configured to 'inline' the chosen properties (in this case a Holding property called Analyst and an Instrument property called Sector) into the standard set of fields.

select EffectiveDate, InstrumentUid, Units, CostAmount, Analyst, Sector from Lusid.Portfolio.HoldingAdjustment
where PortfolioScope = 'Finbourne-Examples' and PortfolioCode = 'UK-Equities'
and EffectiveFrom = #2026-01-01# and EffectiveAt = #2026-12-31#
and Analyst = 'J Smith'