|
Modeling a Trade
A trade is the action of buying or selling a security. While there is great variety in financial products, large number of trade execution venues, numerous methods for managing p&l and risk and diverse trade life cycles, the basic concept of the trade is quite simple. The following attributes are sufficient to capture financial and descriptive information about the trade and make up the Trade Model::
Action ( Buy, Sell )
Quantity( may mean different things depending on product )
Price and Currency
Security Reference
Trade Date/Time
Settlement Date
Trade Cashflows, representing trade settlement amount, commission and other fees and taxes
Descriptive Trade Attributes, indicating legal entities, strategy, sales channel and other attributes
Trade Identifiers: Providing ability to uniquely reference the trade, link related trades together and provide reference to for external counterparties
Additional Trade Details: These may include execution details, such as order type and venue, trade processing and clearing instructions, calculation agent, trade documentation and allocations
In addition to the descriptive fields, to support effective reporting, trade needs to contain attributes describing trade lifecycle with respect to multiple consumers of trade data.
|
|
Descriptive Trade Attributes
Key responsibility of the Trade Model is ability to provide flexible reporting of trade, position and P&L data. Descriptive Trade Attributes are at the core of this. To support books and records quality P&L, it’s important that all trades are tagged with correct internal and external legal entities. A combination of these entities will represent the unique bucket to be used for Tax Lot calculation and determination of realized and unrealized p&l. For a Hedge Fund, typical legal entities on the trade will be the Master Fund and Prime Broker or a Clearing Agent. In addition to legal entity tagging, trade model needs to allow position aggregation by strategy, analyst, trader, internal portfolio and other arbitrary attributes. While it’s not necessary to support Tax Lot based P&L at these aggregation levels, we need to support positions, risk and average cost/trading p&l.
Storing descriptive trade attributes directly on the trade, does not support adding custom fields well and carries a significant overhead in data and index sizes for large trade tables. Instead, we should create a portfolio for each combination of these attributes and keep only the portfolio id on the trade. Portfolio would in turn have attributes for fund, clearer, analyst and other grouping tags.
Since the portfolio table would be orders of magnitude smaller than the trade table, it should reduce database IO requirements for sql queries, significantly simplify process of adding custom attributes and provide simple, yet sufficiently granular notion of position.
|
|
Trade IDs
Another important responsibility of the Trade Model is ability to represent and maintain complex trade/deal structures in a way it was executed and to enable effective post execution communication with counterparties about the trade. To support this, trade model needs to support rich yet fairly rigid trade id schema. First, the trade model itself, only needs two ids, a unique record identifier and trade id which will remain constant across versions of the trade. All other ids should be maintained in a separate table referencing only the trade id, a vertical table containing ( trade id, alternate id and alternate id type) provides sufficient representation for this.
In practice a given trade may have anywhere from 0 and a dozen of alternate trade ids. These will include deal/contract ids, counterparty ids, broker ids, trade linking/grouping ids and others. They will tend to be populated rather sparsely based on product type, execution venue… In some cases they will have 1-to-1 relationship with the trade id, in other cases such as deal linking or grouping, a single alternate id will span multiple trade ids. While, the number of records in this table will get quite large and will likely exceed the number of records in the trade table, size of the data will be rather small and look ups on this table will be direct with very low IO cost.
Trade Lifecycle and Amendments
Trade Model needs to support the notion of trade status. The primary responsibility of trade status is to determine if the trade is live or not, though it can also be effectively used to provide additional information about the trade lifecycle. For example, initially the trade may be entered in the pre-execution state, then the trade will be executed, following execution a trade would be either confirmed or canceled. These states allow construction of multiple views of position( i.e, only executed vs pending and executed ). When designing trade states, it’s important to keep in mind that the primary responsibility of the trade model is to provide rich and flexible view of positions. Furthermore, there are significant complexities with document tracking and confirmation/matching workflows with many of the workflow steps not having impact on validity of position, risk and p&l. Hence, it’s best to keep complex operational workflows in subsystems that use the trade model without duplicating data.
To support complex operational workflows without the need to duplicate trade data, trade model needs to support multiple trade lifecycles. The above described trade lifecycle supports front and parts of the middle office workflow. However, there are other operational workflows such as trade reconciliation workflow where the reconciliation team needs to see T+1 positions frozen until all breaks have been accounted for and explained. Having front or middle office teams amend or cancel yesterday’s trades to correct risk will cause disruptions to the reconciliation team. Trade Model can support both workflows on the same data by providing versioning and secondary trade status to indicate that the trade is active for recon positions in conjunction with having each trade amendment handled by adding a new row. Example below:
Suppose on 3/25 we executed a trade for 1000 shares of yahoo but it was keyed into the system as 100. as below
ID
|
TradeId
|
Security
|
Quantity
|
Price
|
Date
|
Portfolio
|
Trade Status
|
Recon Visible
|
100
|
10
|
YHOO
|
100
|
45
|
3/25/15
|
123
|
executed
|
Yes
|
On 3/26 it was discovered that the trade was booked in error and was amended with correct quantity as follows.
ID
|
TradeId
|
Security
|
Quantity
|
Price
|
Date
|
Portfolio
|
Trade Status
|
Recon Visible
|
100
|
10
|
YHOO
|
100
|
45
|
3/25/15
|
123
|
canceled
|
Yes
|
101
|
10
|
YHOO
|
1000
|
45
|
3/26/15
|
123
|
confirm
|
No
|
In this model on 3/26, front and middle office views will see record id 101, since they query positions based on trade status while the recon team view will continue to see record 100 since they query positions based on recon visible flag. Once reconciliation is complete, recon team will turn on status propagation which will mark record 100 as invisible and record 101 as visible for recons.
Cancel/Rebook model of trade amendments described above carries overhead of duplicating trade data though in practice, such overhead will rarely exceed 5-10% of the trade table.
Another design consideration is whether or not to keep Recon Visible flag on the trade model itself or in a separate table. Since there will be 1-1 relationship between the tables, the trade off is between potential deadlocks and update collisions with other updates to the trade table and the cost of table joins and extra storage needed for new table. In practice, adding a new secondary trade lifecycle is a very rare event as it adds significant operational complexity. Therefore, it is acceptable to have secondary statuses directly on the trade table.
Historical views and audit trail
Trade Model needs to support three functions with respect to historical views and audit trail.
Ability to show correct positions for a given historical trade date. This is naturally supported by the trade model leveraging trade status and trade date fields.
Ability to show what positions looked liked at a particular point time. It’s important to realize that we only need ability to demonstrate what positions looked like, not what trades looked like. This distinction allows us to apply the cancel/rebook model only to economic/position impacting changes. To support this view, we need to introduce Effective Date and Cancel Effective Date fields to the trade model. Effective Date will be set once the trade becomes executed, Cancel Effective Date once the trade becomes canceled. Extending the previous example we will get the following, allowing us to see 100 share trade with effective date of 3/25 and 1000 share trade with effective date of 3/26
ID
|
TradeId
|
Security
|
Quantity
|
Price
|
Date
|
Portfolio
|
Trade Status
|
Recon Visible
|
Effect Date
|
Cancel Date
|
100
|
10
|
YHOO
|
100
|
45
|
3/25/15
|
123
|
canceled
|
Yes
|
3/25/15
|
3/26/15
|
101
|
10
|
YHOO
|
1000
|
45
|
3/26/15
|
123
|
confirm
|
No
|
3/26/15
|
|
Ability to demonstrate an audit trail of all changes made to a given trade. Audit goes beyond economic changes. It’s required to audit every field that has changed on the trade indexed by both ID and TradeID. Audit data should be managed by a separate subsystem, recording every update to the trade.
More to come
This article only covers the very basics of the trade model. Fully functional trade models need to support cash management, trade allocations, trade event processing, order fill management, fast position API, data archiving strategies and much much more. Those components are beyond the scope of this article and will be covered at some point in the future.
|
No comments:
Post a Comment