Technical Overview

From: Paul Stephenson

Date: 4. May 2000

Re: Nicholas Applegate Landmark system

Introduction

The Landmark system, from Longview Consultants, is implemented at Nicholas Applegate as your traditional Vertically Integrated highly customised system. We essentially bought a database model with stored procedures and a front end (Triangle) which we subsequently replaced.

Functionality - by product.

This is broken out into 3 applications, Weights, NATS (Nicholas Applegate Trading System) and NATS Explorer and 4 main sets of users, Portfolio Managers, Traders, Operations / Trade Support and Compliance.

Weights

Portfolio Managers use Weights, which contain 3 spreadsheet style forms to help them manage accounts.

Appraisal Rebalance, shows account details, listing all securities held in an account with their market valuation and % of account, allowing rebalancing of holding based off targets or existing models. Models are ‘templates’ or idealised holdings which can be applied to accounts. These are maintained by a separate Model form which lists the security and target percent. This needs enhancing to allow models to be rebalanced by change of security price.

Security Cross Reference, shows all accounts, or combinations of accounts that hold a particular security. This is used to say sell off all holdings or review the exposure of the firm or portfolio group to a security

Composite Rebalance works off a group of accounts and allows a PM to add / remove a security or sector from groups of accounts.

Portfolio Managers create proposed orders to internally store possible new trades. There can only be one proposed order per account / security. When they are happy with their proposed orders they will create real orders. Proposed orders can be deleted and are in no way an obligation to trade that account / security combination.

NATS

This includes the main trade flow processing, with 4 screens: Trading, Placements, Executions and Allocations. In addition, 3 ‘blotter’ screens are available, a ‘Main / Retail / Conflict’ blotter running off blocked orders, a ‘Proposed Orders’ blotter based off proposed orders, and a ‘Notifications’ blotter listing any changes to any proposed orders / orders on the system.

The main trading screen allows manual entry of proposed orders, or loading from a CSV file. The proposed orders can then run compliance checks on them, and then create real orders off them. The real orders will be blocked with other orders entered on the same screen for the same security and direction.

Traders will use the Trading and Placement forms and have multiple blotters open to tell them of new orders. Trade Support / Ops will use the Execution and Allocation forms.

NATS Explorer

NATS Explorer is the static data maintenance application, allowing users to maintain Accounts, Securities, Holdings, Brokers, and a whole lot more. Typically used by Operations / Trade Support, and by Compliance to maintain their Compliance Models.

Back End

This is no processing engine as such for the Landmark system, but there are a patchwork of perl jobs run from the Autosys job scheduler maintained by Production Support. These perl jobs are responsible for:

Reports

There are maybe 100 reports on the system. Most are Crystal Reports, tied into the front end, runnable at request by the users, however these are not schedulable overnight. InfoReports is also used, as a “strategic” project, and is run overnight. Reports are emailed to the users or are available on the web via Datasurfer. Unfortunately these reports are not available to online users.

Functionality - by area

Trade Flow

Compliance Checks

Before a proposed order can become a real order which can be traded, compliance checks are run on it to ensure we meet various criteria. This is essentially a rule based check, and there are 5 levels: a pass, warning, override required (someone other than the trader has to enter a password), 2 overrides required (2 different people are required) and a hard error (we cannot trade this account / security combination).

Compliance Checks are defined at an account level, with any checks defined for any parents / grandparents etc of the account also included. The currently used restrictions are:

An example of a restriction would be the Sisters of Mercy account, which has a restriction against any Tabacco companies. If a buy for Philip Morris went against this account, a hard error would be created and the proposed order could not be promoted to a real order.

Rebalance

This is the heard of the Weights application and what Portfolio Managers do most of the time. The key form is the Appraisal screen which lists the holdings for an Account, and the percent of the account market value they make eg:

Say that the account had a policy of having 50% of it’s account market value in Telecoms, 50% in Technology. Hence the Target Percent for MCI Worldcom should be 50%, and say the IBM target percent should be 25%

The target quantity would be: Account Market Value in USD x Target Percent / USD Price

For MCI Worldcom: $1,000,000 x 50% / 22.5 = 22,222

For IBM: $1,000,000 x 25% / 30 = 8,333

Hence the order quantity would be: Target Quantity - (Held + Open)

For MCI Worldcom: 22,222 - 20,000 = 2,222. Round lot to: Buy of 2,200

For IBM: 8333 - 10,000 = -1,666. Round lot to: Sell of 1,700

Hence with the 2 proposed orders the account would be brought back into line.

To keep a number of accounts following the same investment strategy, a model could be created with the securities and their target percents, so in the above, we would have IBM 25%, Amazon 25%, MCI Worldcom 50%. This could then be applied to various accounts, and whatever their previous makeup, after rebalancing to the model, buys and sells would be generated to bring the account back into line.

Full rebalance documentation on http://sunrise/~pstephen

Database Design

There are 3 databases in Landmark. The database ‘landmark’ is the core database containing all the tables supplied by Longview Consultants. The database ‘landmark_client’ contains tables added by Nicholas Applegate, eg configuration tables. The database ‘nats_history’ is an overflow database to contain history data for trades.

Trade Types

Each trade type has it’s own table, which contains fields for every attribute held, so we have proposed_orders, orders, tickets, equity_execution and allocations.

1. proposed_orders is keyed on account_id and security_id. If a proposed order already exists, and a PM or trader wants to create a new proposed order for that account / security, the old one is deleted.

2. orders is keyed on order_id, and contains a link to the blocked_order via block_id. History is kept in orders_history

3. blocked_orders is keyed on block_id. This is the key table to the entity relationships. One blocked order may map to many orders. Once blocked order may map to many tickets, which may in turn map to many executions, which may in turn map to many allocations. History is kept in blocked_orders_history

4. Tickets are held on ticket with vertically split tables equity_ticket and equity_placement. These are keyed on ticket_id. History is kept in ticket_history, equity_ticket_history and equity_placement_history.

5. equity_execution is keyed on ticket_id and execution_id. History is on equity_execution_history

6. allocations is keyed on allocation_id, with a more common key used as ticket_id, order_id. History is on allocations_history.

Trades are only held on the main tables till the entire blocked_order is allocated and confirmed. Then all the rows are moved onto the history tables. Hence the main tables are relatively small, hence reducing any performance problems present in trade processing. Once on the history tables, there is little that can be done for them, no reports etc.

There is no ‘real’ Amendment or Cancel / Correct logic. If a user wants to amend the quantity on an order or another value, it simply updates the fields on the table. There is online processing that tries to recalculate blocks etc, this is well dodgy. There is no trade version history.

There is no ‘back end’ trade processing as such, no transaction engine. Any trade processing occurs synchronously when the user commits a trade. Any trade processing on a trade is hard wired in, there is no trade type definition.

Position Keeping

Oh dear. Landmark does not keep positions and relies on the external accounting system ‘Shaw’ to provide a positions table which is loaded in the overnight load. Sometimes, open orders are needed to be included in the positions, and this is done by additional queries which simply sum the open orders on the system. Positions are keyed on account / security and contain the allocated position as of last night. There is no history kept on the system. There is no ‘Trade Date positions’ or ‘Settle Date positions’, no depot positions or anything else.

If a position is wrong (users chose the wrong symbol etc), users can directly modify the positions table via NATS Explorer Accounts / Security Detail form, inserting / amending / deleting rows.

Static Data

Account detail is held in the account table, integer indexed on the account_id field. Related tables are:

Security detail is held on the security table, integer indexed on the security_id field. Related tables are:

User information is pretty simple. Security is maintained by having the user log directly into Sybase and relying on Sybases’ username / password checks. user_information table contains name and functional restriction information. It is integer indexed on user_id, and also contains the related suid for the user. Many tables have created_by (smallint) and created_time (datetime) fields and some also have modified_by (smallint) and modified_time (datetime), to aid auditing. The created_by and modified_by contains either the user_id from the user_information table, or the suid - there is no method in the madness.

Extensible Data

Landmark’s way of supporting extensible data - or user defined fields - is particularly crude. Each main table has a number of user_field_1-8 or user_id_1-8 fields, which can be filled with whatever you want. So for the security table, user_id_1 = Ticker, user_id_2 = Reuters, user_id_3 = Cusip, user_id_4 = Sedol, user_id_5 = ISIN. The stored procedures are then modified to use these ‘user’ fields. There are no ‘static data’ defined attributes, no ‘external refs’ table or traditional normalisation.

SQL Style

Triggers have thankfully been reduced down to the minimum possible, due to problems with transaction length and their maintainability issues. Returning errors to VB code is normally done via raiserror 99999. Most processing in Landmark is done one trade at a time, though work is underway to change the compliance checks and order creation to process sets of trades. Transaction protection is sometimes present, sometimes not, and even where it is present, there is no deadlock detection in VB to loop around and repeat code that has deadlocked.

Front End Application Design

All 3 main applications, Weights, NATS and NATS Explorer are written in Visual Basic. Each one has it’s own source base, even though some forms are duplicated across the applications, eg New Security and Notifications forms.

Database access is done via RDO, with most calls placed in a separate module called DB or DatabaseRoutines, with functions called DB_GetSecurity etc. Unfortunately, the rdo calls are called directly with no wrapper routine. There is little error detection, sometimes non at all, so any errors returned by stored procedures are normally ignored or may even cause a run time error. All output is returned as result sets as there is no code to interpret parameter outputs. Deadlocks are not detected and no warnings are displayed about them. However stored procedures are not always transaction protected so this doesn’t happen too frequently.

True DBGrid Pro 6.0 is heavily used, appearing in most forms. This allows users to view all appropriate rows and select rows for further actions. Active Thread Plus is used in NATS for Trading form processing.

NATS and Weights are pretty traditional applications. NATS Explorer relies on the form’s fields to build up SQL to update tables. So on the Account Detail form, the particular fields all match the account table’s fields. This provided quick application build time. It has however proved difficult to maintain, particularly handling the special cases that invariably turn up.