SQL Database Integration

With LexiFi, there is just no excuse not to immediately automate the operational management and reporting for new products, regardless of their diversity or complexity. LexiFi provides a simple, schema-independent way of storing contracts in a SQL database. All management and reporting features are available as soon as a contract is added to the database. This feature also opens MLFi to a broad range of users including business analysts, who rely on graphical query and reporting tools, and SQL programmers.

How it Works

MLFi contracts may be stored in a relational database and manipulated with user-defined SQL functions:

• Contract Storage.  The representation of MLFi contracts is based on a simple and readable IT model: transactions and their life cycle history are represented in a binary object that may be stored in a single table column. Other elements (e.g., parties, portfolios, etc.) are described with additional columns and/or tables: these elements are usually better understood and easier to represent than the mechanics of complex financial contracts.
• Contract Manipulation.  LexiFi's contract manipulation capabilities are exposed as stored procedures, which enable users to query and modify any contract or portfolio using standard SQL syntax.

  LexiFi provides pre-packaged stored procedures to handle common tasks such as adding a contract to a database, managing contract events, and reporting on a portfolio. A simple interface enables users to create stored procedures for all functions exposed in LexiFi's Finance library.

  LexiFi also provides optional access to the internals of MLFi contract definitions. Users may view the abstract syntax tree (AST) of MLFi contract definitions in order to develop custom functions for querying contracts.

Benefits

LexiFi's approach to contract persistence presents a number of advantages:

• Simplicity.  A binary representation minimizes the number of tables used to represent financial contracts. Financial contracts have traditionally been decomposed into dozens of tables. With LexiFi, the atomic storage unit is the managed contract, which occupies a single field.

  This design results from a trade-off between the number of tables and the number of stored procedures. LexiFi has chosen to dramatically simplify the database schema of portfolio management applications and to deliver a complete set of pre-packaged stored procedures to query and modify contracts. In addition, users may independently define custom stored procedures to meet specific requirements.

  In effect, the design reflects more a necessity than a choice: the diversity or complexity of structured financial products makes the search for a comprehensive data model illusory. As an analogy, consider the task of designing a data model for describing the content any Microsoft Excel worksheet. The exercise is likely to be painful and fruitless. The same applies to financial contracts, which may be represented, like Microsoft Excel worksheets, as an AST. The considerable diversity of financial contracts imposes a unique representation and therefore storage in a single column.

• Adaptability.  The introduction of new products does not affect database schemas. As contract definitions occupy a single column, the database schema is insensitive to their complexity.
• Choice.  Users choose the tools they like to develop applications and to produce reports. The MLFi contract description language is hidden both from business analysts who rely on graphical query and reporting tools, and from SQL programmers who work with a familiar, tabular view of data. At the same time, power users benefit from MLFi's precise contract modeling features to add functionality. This ability to (i) open MLFi to a broad community of end-users and SQL programmers and (ii) let power users develop new capabilities directly using MLFi obeys an 80/20 logic.
• Control.  Access to MLFi's contract AST enables the independent development of custom contract manipulation functions. We believe that no other commercial system provides the degree of flexibility and control afforded by MLFi for querying and modifying financial contracts. For example, access to the AST enables power users to develop domain-specific queries and to find answers to previously challenging questions such as:

  "Show me the list of all underlyings that were removed from my portoflio of Himalaya contracts in the second quarter."

  "List all coupons paid since the beginning of the year for LIBOR-based corridor products and the dates on which range levels change, with their corresponding values, for remaining coupons."

• Performance.  With stored procedures, the intelligence is on the server: client applications minimize network traffic as they only retrieve query results instead of loading the entire contract definition. In addition, users may create specialized tables or views to optimize database performance.
• Flexibility.  Users add stored procedures at run-time. There is no need to stop the database server.

Example

Let us define a European EUR call (USD put) currency option using MLFi syntax (the premium is omitted):

let EUR_USD_call_001 =
  let exercise =
    cash_flow(EUR:1000000, 2004-12-17) `and`
    give (cash_flow(USD:1200000, 2004-12-17)) in
  let no_exercise = zero in
  acquire {[2004-12-15T16:00]} (
  ("(EUR call/USD put)", exercise) `or` ("(No exercise)", no_exercise))

The contract holder has the right, but not the obligation, on 15 December 2004 at 16:00 to purchase EUR 1,000,000 for USD 1,200,000 for settlement on 17 December 2004.

Using SQL, we create a view that contains the list of future fixings and exercises:

CREATE VIEW __future_fixings_exercises AS
  SELECT id, key, future_fixings_exercises(contract) AS future_fixing_exercise
  FROM position;
CREATE VIEW future_fixings_exercises AS
  SELECT
  id, key, date_of_future_fixing_exercise(future_fixing_exercise) AS date,
  event_type_of_future_fixing_exercise(future_fixing_exercise) AS event_type,
  event_description_of_future_fixing_exercise(future_fixing_exercise) AS event_description
  FROM __future_fixings_exercises ORDER by id, key;

The first, intermediate view applies a pre-defined SQL function (stored procedure) named future_fixings_exercises to the contract column of the position table. The second view retrieves relevant information from the first view.

We save the currency option contract in the database and then query the future_fixings_exercises view by running the SQL command

select * from future_fixings_exercises;

to return the list of future events:

date	contract_type	trade_id	key	event_type	event_description
2004-12-15 16:00:00	currency_option	1	EUR_USD_call_001	Exercise	(No exercise), (EUR call/USD put)

We insert a EUR vanilla interest rate swap using the insert_contract stored procedure, which takes several strings and a parametric swap definition as argument:

select insert_contract
    ('swap', 'EUR_LIBOR_swap_001', 'Bank',
     'JP_Morgan_Chase-LO', 'Demo', 'Trading',
      swap('2004-10-01', 'EUR_LIBOR_BBA_6M', '3Y', 10000000., 0.05, 0.));

The swap function returns a 3-year interest rate swap contract traded on 1st October 2004 that pays a fixed rate of 5%, receives 6-month EUR_EURIBOR_BBA flat on a EUR 10,000,000 notional, applying standard market conventions (i.e., two business days from trade date to effective date, annual interest payments, semi-annual resets, 30/360 day count fraction on the fixed side and ACT/360 on the floating side).

The SQL command

select * from future_fixings_exercises;

returns the combined set of currency option and interest rate swap events, ordered by date:

date	contract_type	trade_id	key	event_type	event_description
2004-10-01 11:00:00	swap	2	EUR_LIBOR_swap_001	Fixing	EUR_LIBOR_BBA_6M
2004-12-15 16:00:00	currency_option	1	EUR_USD_call_001	Exercise	(No exercise), (EUR call/USD put)
2005-04-01 11:00:00	swap	2	EUR_LIBOR_swap_001	Fixing	EUR_LIBOR_BBA_6M
2005-10-03 11:00:00	swap	2	EUR_LIBOR_swap_001	Fixing	EUR_LIBOR_BBA_6M
2006-04-03 11:00:00	swap	2	EUR_LIBOR_swap_001	Fixing	EUR_LIBOR_BBA_6M
2006-10-03 11:00:00	swap	2	EUR_LIBOR_swap_001	Fixing	EUR_LIBOR_BBA_6M
2007-04-03 11:00:00	swap	2	EUR_LIBOR_swap_001	Fixing	EUR_LIBOR_BBA_6M

We now manage portfolio events up to and including 15 December 2004. The following SQL command fixes 6-month EUR_EURIBOR_BBA on 1st October 2004:

UPDATE position
  SET contract=
    make_fixing_contract(contract,'2004-10-01 11:00:00',
      'EUR_LIBOR_BBA_6M',0.05)
  WHERE key = 'EUR_LIBOR_swap_001';

The next command, exercises the currency option at expiry:

UPDATE position
  SET contract=
    euro_exer_contract(contract, '2004-12-15 16:00:00', '(EUR call/USD put)')
  WHERE key = 'EUR_USD_call_001';

As of 15 December 2004, the residual list of future events is obtained by running

select * from future_fixings_exercises;

one more time:

date	contract_type	trade_id	key	event_type	event_description
2005-04-01 11:00:00	swap	2	EUR_LIBOR_swap_001	Fixing	EUR_LIBOR_BBA_6M
2005-10-03 11:00:00	swap	2	EUR_LIBOR_swap_001	Fixing	EUR_LIBOR_BBA_6M
2006-04-03 11:00:00	swap	2	EUR_LIBOR_swap_001	Fixing	EUR_LIBOR_BBA_6M
2006-10-03 11:00:00	swap	2	EUR_LIBOR_swap_001	Fixing	EUR_LIBOR_BBA_6M
2007-04-03 11:00:00	swap	2	EUR_LIBOR_swap_001	Fixing	EUR_LIBOR_BBA_6M

As the above example shows, MLFi contracts may be viewed as structured documents that evolve over time and that lend themselves to instantaneous storage and manipulation in a relational database management system.