Home

About Us

PnL Explained Professionals FAQ

Site Map

Glossary

Membership

Contact Us

           

Main Page by Topic

A. PnL Explained

B. CTRM Software

C. Statistics

 

 

Considerations when Migrating Transactions from a Legacy to a New CTRM

 

This page shares with you, gentle reader, thoughts and considerations on when Migrating Transactions from a Legacy to a New CTRM.

 

There are seven key elements to this, which should be considered and planned separately:

Key Element 1) Migration strategy

This includes in scope for this topic:

1.1) Migrate all trades at once, i.e., a go live/cutover weekend or run in parallel for period?

1.2) Migrate all trades since the origin of the legacy CTRM system or just active ones or somewhere in between like one year back?

1.3) For individual trade, e.g., a calendar year, Jan to Dec swap, migrate the whole trade, i.e., all 12 months, or just the months still in the future, e.g., remaining 3?

 

Key Element 2) Actual Migration ‘Scripts’ (Code)

2.1) Build Trade Export from legacy system

2.2) Build Trade Import into New System

2.3) Not so easy… many trades will be re-modeled/new design for implementation.  Instead of one-to-one, may be 2 to 1 or 5 to 1 or a change in trade type, etc.

2.4) May include mappings, e.g., to new counterparty names

 

Key Element 3) Data Cleanup

3.1) It is common for ‘bad’ or corrupt trades to exist in legacy system. 

3.2) E.g., since they were booked, a holiday schedule changed, which would require that the payment dates be generated, but that hasn’t happened yet.

3.3) Good project management would be to track these and clean them up in the legacy system prior to go live on the new system.

 

Key Element 4) Project Management

4.1) Good project management is essential.  There will likely be many test cycles, i.e., full end-to-end trade migration tests.

4.2) Project Managers should have a report that shows one column per test cycle (e.g., one column per week) where the rows are the Trade Types (a.k.a., ‘instrument types’).  The data in the report would be percent complete in terms of migration success.  Again, this should be per trade type and with percentages trending over time (as columns).

4.3) The PM (project manager) should also track the status of corrupt (‘bad’) deals in the legacy system as they get fixed.

4.4) And keep in mind that while an implementation of a new system is going on, trading continues in the legacy system.  New Trade Types may get added, i.e., trading a new product, and additional deals may get corrupted.

 

Key Element 5) Trade Reconciliation – Trade Details

5.1) Additional extracts will need to be written from the Legacy system and an extra would need to be written from the new system. 

5.2) Need payment level details for the Trade Details Recon.  E.g., a trade may get exported as one row for a swap with start/end dates of ‘Jan to Dec’ and that may be fine for trade booking.  For the Trade Details recon, that would not be enough.  You would need 12 rows for the deal, one row for each payment, shown with the payment dates (one per month) and the volumes and other details.

 

Key Element 6) MTM (Mark to Market) Reconciliation

6.1) After the Trade Details recon checks out, you can and should run a MTM (‘valuation’) recon.  Even if the trade details match 100%, you can still have a MTM difference due to pricing model differences, differences in market data (prices, volatilities) or differences in interpolation methods of market data.

6.2) If a MTM difference is expected, make sure to get whatever approvals are needed to allow for the MTM change.  Especially if the MTM will be higher in the new system versus the legacy system.

 

Key Element 7) Regulatory Considerations

7.1) This is mainly about retention of data.  e.g., if you are migrating only active trades from the legacy system and you have a regulatory requirement to retain 7 years of data, what is your plan do to that?  Keeping the legacy system around for 7 years may not be possible/practical.

7.2) Another regulatory consideration is if the MTM changes and how that would be approved. 

 

 

 

This page focuses specifically on the considerations and challenges for migrating trades.  For a more general document that covers migration strategies, see this:

Link: Strategic Approaches for Switching CTRM Systems

 

For this document, ‘Transaction’ is synonymous with ‘Trade’ and vice-versa.

 

Also, note that the below provides additional details compared to the above section, which is an intro/summary.

 

Outline for This Page

1) Intro to Trade Details Reconciliation

2) Trade Details Reconciliation – Special Considerations 

3) Trade Details Reconciliation – Project Management Tips 

4) Intro to MTM (Mark to Market) Reconciliation

5) Trade Migration – General Considerations

6) Trade Migration – Regulatory Considerations

 

 

1) Intro to Trade Details Reconciliation

 

Plan on building two separate reconciliations.   One for trade details and one for MTM (mark to market), a.k.a., valuations.   These are one-offs for use for your system migration that would be discarded after new system go live. 

 

1.1) Trade Details Reconciliation

This should

a) Take an export of trade data from the legacy system and

b) Take an export of trade data from the new system and

c) Compare them for differences. 

 

1.2) You might need several export files. 

a) One with one row per trade with trade level details

b) And one with payment level details.  E.g., a fixed/float Commodity Swap (financial deal, a derivative) might be for one calendar year and so have 12 payments, each with a different payment date.  You’ll want to reconcile the payment dates (and other details) between the legacy and the new system. 

 

If a payment date is different, then the MTM would be different, if only due to different discount factors applied to different dates.   By doing the trade details reconciliation first, before the MTM reconciliation, then you’ll likely catch most of the issues that you would have found in the MTM reconciliation.

 

1.3) You would likely already have an export of trade data from the legacy system.  The one you might have used to build up the import files for importing trades into the new system.  Some of that may be reused, although you might need to build out additional exports, e.g., at the payment level (12 rows per trade if the trade is for one calendar year).

 

2) Trade Details Reconciliation – Special Considerations 

 

2.1) In order to tie things between the legacy and new system, you’ll need to copy the original trade number, from the legacy system, onto the deal into the new system.  E.g., if the original trade number is E123456 and in the new system, it gets a value like ABC-100001, you’ll need to stamp the original value, i.e., ‘E123456’ somewhere on the trade in the new system. 

 

Most CTRM systems allow for extra fields to be added to a trade, i.e., custom fields.  

 

In addition to adding ‘legacy trade number’ as an extra, custom field, you may also want to add ‘system source’.  There may be more than one source of trade data when going from a legacy to a new system.   E.g., maybe some trades are loaded that were formerly managed solely in Excel and the rest are from a legacy CTRM system. 

 

2.2) Most CTRM systems support a concept called either ‘interbook’ or ‘internal’ trades.  This is when a user enters in one trade, e.g., a buy, to an internal-to-the-firm group and the system automatically generates a second trade that is equal and opposite, i.e., a sell.  Two different trade numbers would get generated.

 

If you have 1000 trades in total, all Interbooks, that means 500 buys and then another 500 linked sells.  When these get booked into the New CTRM, you would only book the 500 original trades. Meaning via an import file.  And then the system would auto-generate the offsetting 500 trades for 1000 in total.

 

Consider this when building a trade details reconciliation.  An easy approach is to ignore the auto-offset trades in the recon, assuming those to be correct if the original side of the interbook trades is correct.  And then catch issues, if any, in the MTM recon.

 

If you do plan to also reconcile the auto-generated offset trades (the ‘sell’ to an original ‘buy’ and vice-versa), then consider how you might migrate the trade number of the offset trade into the new system, which could be tricky since you don’t actually book the offset trade, that gets auto-booked by the system.

 

2.3) If you have a multi-month trade, e.g., a commodity swap that lasts a calendar year, i.e., Jan to Dec, with 12 payments, one per month, consider that in some migrations only the remaining active months will get migrated.  E.g., with a go live planned for September, maybe only the remaining 3 months, October, November and December would get booked/created in the New CTRM system. 

 

Any trade details reconciliation would need to take this into account.   E.g., the original start date of the trade would be January 1 and the start date of the newly booked trade would be October 1.  That can’t be flagged as an error by the recon, since it is an intentional change.

 

2.4) There are often intentional trade modelling differences. e.g., due to issues with the legacy system, one ‘trade’ in the real world might get booked as two separate trades as a workaround. 

 

Then, in the new CTRM system, which would have better capabilities, the trade could be booked more accurately as one single (correct) trade.  

 

The recon would have to reconcile two trades to one, which would require custom/special logic. 

 

3) Trade Details Reconciliation – Project Management Tips 

 

3.1) Team members will want a Percent Reconciled (matching) number for each test run.   Providing one overall number can be misleading.  Better to provide numbers by trade type.

 

For example:

Exchange-Traded Futures Reconciliation Status: 100%

(e.g., 27,123 deals out of 27,123)

Financial Commodity Swaps (derivatives): 90%

Financial Commodity Options (derivatives): 60%

Financial Commodity Swaptions (derivatives): 00%

Physical Natural Gas deals: 50%

And so on.

 

3.2) Project managers should track the status of deals that are corrupt (‘bad’) in the legacy system, meaning if/when they have been corrected by the business.  Ideally, all corrupt deals would be corrected prior to the go live weekend.

 

4) Intro to MTM (Mark-to-Market) Reconciliation

 

4.1) Make sure that before you run a MTM reconciliation for any given test run, that you run a Trade Details reconciliation and make sure that checks out first.

 

4.2) Consider building a separate reconciliation for market data, i.e., prices, volatilities, correlations, interest rates and FX rates.  And run that before you run your MTM recon on the trades.  If a market price is not matching between the legacy system and the new system, then the MTM will be off on the impacted trades. 

 

In fact, simply running a MTM on a set of trades and extracting the results can be very time consuming.  All of that effort can be saved in the event of a bad run by confirming that the market data (prices, etc.) are a match first.

 

4.3) Also double check that system settings, especially the run date, are a match, between the legacy and the new system. 

 

Typically you might do a test reconciliation run with recent data from production, e.g., from a week old.  Whatever date was used for the data from the legacy system for calculating MTM values needs to be the same date used when calculating MTM data in the new system.

 

4.4) There are numerous reasons why MTM values may not match and some of them may be unexpected.  As examples:

4.4.1) If the holiday schedules don’t match between the legacy and the new system, payment dates as calculated by the new system may not match, leading to small MTM differences due to different discount factors for different payment dates.

 

4.4.2) If the holiday schedules for good business days for exchanges do not match, then the dates using in monthly averaging swaps may be off, causing MTM differences.  e.g., in the legacy system, the price may be an average of 21 good business days for a month (inclusive of correct holidays), while in the New CTRM, the price may be based on 22 ‘good business days’ (due to a missing holiday).

 

4.4.3) For the MTM for deals, in some cases, they’ll get a market price value that is an interpolated value between two other points.  E.g., a mid-month value taken as the average of two monthly values. 

 

If the interpolation methods are different, e.g., log-linear in the legacy system and linear in the new system, then the prices that are fed into the MTM valuation process will be different and so the MTM will be different. 

 

It can be tricky to identify this as a root cause of a MTM issue, since all of the trade details would reconcile between the two systems as well as the Price input values (only the one per month) would also match.

 

5) Trade Migration – General Considerations

 

5.1) Interbook trades.

 

Interbook trade considerations for trade reconciliation are also mentioned above.


As a summary, when migrating interbook trades, be sure to only book one side of the interbook trade into the new CTRM system.  The new CTRM system will automatically generate the other side.  If you were to book both sides, you would end up with twice as many deals in the new system as needed.

 

See also:  Interbook trades

 

5.2) Considerations regarding modelling changes for deals

 

It is common for deal modelling to change when switching systems.

 

Here are some real world examples to consider.  Consider how these or the cases you will encounter would impact deal migration, whether manual or automatic and any trade detail or trade MTM reconciliations.

 

5.2.1) A legacy system did not support spread options.  A small number of spread options were booked as if they were simple spreads, i.e., non-options.  Then, separate from that, a deal meant to represent a cash flow (‘cash deal’) was booked each day as a way to pass in the MTM of the deal.

 

The new system did support, natively, spread options.  So the workaround deals booked into the legacy system were booked correctly in the new system.  Which was not ‘like-for-like’ as booked, and instead booked the way they should ideally be modelled and valued.

 

5.2.2) A legacy system had minimal support for power trading (a.k.a., ‘Electricity Trading’).  Power trades were modelled using functionality intended for Natural Gas.  That left two functionality gaps.  First is the modelling down to the hourly level.  Power trades hourly and NatGas trades daily.  Second was other types of trades that you get with Power, e.g., something called ‘Ancillary Services’. 

 

The new system natively supported Power Trades, including details down to the hourly level and the other types of trades associate with Power such as ‘Ancillary Services’ and ‘Capacity’.  When trades were migrated, they were booked to the correct (better) way of modelling things.  And that required a good amount of manual work to decode the workaround trades in the original source legacy system.

 

5.2.3) Poor modelling of what are called ‘pass through trades’ in one system required five separate trades to be booked, and they were not linked together in the system, even though they are really all part of the same trade.

 

In the new system, these could (correctly) be booked as a single trade, one that correctly shows exposure to multiple internal trading desks and to an external client.  So 5 to 1.  This needed special logic in the deal conversion scripts/code and in the reconciliations.

 

5.3) Considerations regarding changing the term of a trade

 

Example:  if you have a one-year long swap trade with monthly payments and you are partway through the term of the trade, do you migrate to the new system the whole trade, i.e., all 12 months, even the months in the past, or do you migrate just the remaining active months.

 

5.4) Prior period adjustments for physical deals

Prior Period Adjustments occur for physical deals, e.g., for a physical natural gas purchase deal, where updated ‘actual’ volumes come in after an invoice date.  e.g., maybe when the invoice went out, you thought the volume flowed of Physical NatGas on a given date was 9,000 MMBTU.   And maybe the pipeline comes back 45 days later and informs you that the actual volume was 9,100 MMBTU, for an increment of 100 MMBTU.   

 

In a legacy system, you would likely already have a capability to adjust the original trade.   Whereas in the new system, that trade may not have been migrated if it was considered not ‘active’.

 

The point here is to bring this up as a consideration that might impact your planning around trade migration and not to necessarily recommend the best approach to handling this situation, which would depend on the circumstances.

 

6) Trade Migration – Regulatory Considerations

 

Often firms will migrate only active trades plus trades that were recently active, e.g., 6 months of prior date or up to a year.

 

However, regulatory requirements may call for 7 years of historic data.  Which means that a firm may need to either

a) keep the legacy system around for a certain number of years.  This could be expensive in terms of licensing.   And the third party software, e.g., version of Windows or the version of the database server, may no longer be supported by those vendords.

 

b) or to extract trade data into a format for archiving that is approved for the purposes of audit.  That might be as simple as a CSV file or may require a more complicated solution.

 

 

Introduction to CTRM

Click on this link for a great introduction to CTRM software: Introduction to CTRM Software

 

 

Home

Site Map

Contact Us