Trade Settlement Workflow Analytics

Real-time Trade Settlement Monitoring & Analytics

This demo showcases how Neo4j can be used to monitor and analyze trade settlement workflows in real-time, helping hedge funds:

Track trade settlement progress across systems
Identify bottlenecks and stuck trades
Monitor settlement SLAs
Analyze settlement patterns and performance

Let's explore how graph technology can transform trade settlement operations!

Data Model Overview

First, let's understand our data model for trade settlement:

Trader -> initiates -> Trade
Trade -> goes through -> Settlement Steps
Steps -> connected by -> NEXT_STEP relationships
Final Step -> connects to -> Completed (for settled trades)

// View the database schema
CALL db.schema.visualization();

Setup Demo Data

Let's load our sample trade data which includes:

2 Completed trades (AAPL and MSFT)
1 Stuck trade (ESTC)
1 In-progress trade (MDB)

// Delete Graph
MATCH (n) DETACH DELETE n;

//
// Completed Trade 1
//
CREATE (tr:Trader {name: 'Trader 1'})

CREATE (t:Trade {trade_id: 'T-1001'})
SET 
    t.symbol = 'AAPL',
    t.qty = 1000,
    t.price = 175.23,
    t.status = 'Settled',
    t.origin = 'Trader'
CREATE (tr)-[:TRADES]->(t)

// Calculate timestamps relative to current time
WITH t, datetime() AS now

// Calculate Step timestamps in reverse order (most recent to oldest)
WITH t, now, 
        now - duration({hours: 4}) AS s9_completed,
        now - duration({hours: 12}) AS s8_completed,
        now - duration({hours: 24}) AS s7_completed, 
        now - duration({hours: 36}) AS s6_completed,
        now - duration({hours: 44}) AS s5_completed,
        now - duration({hours: 50}) AS s4_completed,
        now - duration({hours: 52}) AS s3_completed,
        now - duration({hours: 56}) AS s2_completed,
        now - duration({hours: 58}) AS s1_completed

// Create Step 1
CREATE (s1:Step {seq: 1, name: 'Alloc'})
SET
    s1.sys = 'System A',
    s1.hrs = 2,
    s1.team = 'Operations',
    s1.duration = duration({hours: 2}),
    s1.completed = s1_completed
CREATE (t)-[:HAS_STEP {duration: 2.0}]->(s1)

// Create Step 2
CREATE (s2:Step {seq: 2, name: 'Confirm'})
SET
    s2.sys = 'System A',
    s2.hrs = 4,
    s2.team = 'Operations',
    s2.duration = duration({hours: 4}),
    s2.completed = s2_completed
CREATE (s1)-[:NEXT_STEP {duration: 4.0, completed: True}]->(s2)

// Create Step 3
CREATE (s3:Step {seq: 3, name: 'Book'})
SET
    s3.sys = 'System A',
    s3.hrs = 2,
    s3.team = 'Operations',
    s3.duration = duration({hours: 2}),
    s3.completed = s3_completed
CREATE (s2)-[:NEXT_STEP {duration: 2.0, completed: True}]->(s3)

// Create Step 4
CREATE (s4:Step {seq: 4, name: 'RiskCheck'})
SET
    s4.sys = 'System B',
    s4.hrs = 6,
    s4.team = 'Risk',
    s4.duration = duration({hours: 6}),
    s4.completed = s4_completed
CREATE (s3)-[:NEXT_STEP {duration: 6.0, completed: True}]->(s4)

// Create Step 5
CREATE (s5:Step {seq: 5, name: 'Netting'})
SET
    s5.sys = 'System B',
    s5.hrs = 8,
    s5.team = 'Risk',
    s5.duration = duration({hours: 8}),
    s5.completed = s5_completed
CREATE (s4)-[:NEXT_STEP {duration: 8.0, completed: True}]->(s5)

// Create Step 6
CREATE (s6:Step {seq: 6, name: 'SettlementInstr'})
SET
    s6.sys = 'System C',
    s6.hrs = 12,
    s6.team = 'BackOffice',
    s6.duration = duration({hours: 12}),
    s6.completed = s6_completed
CREATE (s5)-[:NEXT_STEP {duration: 12.0, completed: True}]->(s6)

// Create Step 7
CREATE (s7:Step {seq: 7, name: 'Affirmation'})
SET
    s7.sys = 'System C',
    s7.hrs = 12,
    s7.team = 'BackOffice',
    s7.duration = duration({hours: 12}),
    s7.completed = s7_completed
CREATE (s6)-[:NEXT_STEP {duration: 12.0, completed: True}]->(s7)

// Create Step 8
CREATE (s8:Step {seq: 8, name: 'Match'})
SET
    s8.sys = 'System C',
    s8.hrs = 8,
    s8.team = 'BackOffice',
    s8.duration = duration({hours: 8}),
    s8.completed = s8_completed
CREATE (s7)-[:NEXT_STEP {duration: 8.0, completed: True}]->(s8)

// Create Step 9
CREATE (s9:Step {seq: 9, name: 'Settle'})
SET
    s9.sys = 'System C',
    s9.hrs = 4,
    s9.team = 'BackOffice',
    s9.duration = duration({hours: 4}),
    s9.completed = s9_completed
CREATE (s8)-[:NEXT_STEP {duration: 4.0, completed: True}]->(s9)

// Create Completed Node
CREATE (c:Completed {trade_id: t.trade_id})
SET
    c.completed = s9_completed,
    c.status = 'Success'
CREATE (s9)-[:COMPLETED]->(c);

//
// Completed Trade 2
//
CREATE (tr:Trader {name: 'Trader 2'})

CREATE (t:Trade {trade_id: 'T-1002'})
SET 
    t.symbol = 'MSFT',
    t.qty = 10,
    t.price = 389.08,
    t.status = 'Settled',
    t.origin = 'Trader'
CREATE (tr)-[:TRADES]->(t)

// Calculate timestamps relative to current time
WITH t, datetime() AS now

// Calculate Step timestamps in reverse order (most recent to oldest)
WITH t, now, 
        now - duration({hours: 4}) AS s9_completed,
        now - duration({hours: 12}) AS s8_completed,
        now - duration({hours: 24}) AS s7_completed, 
        now - duration({hours: 36}) AS s6_completed,
        now - duration({hours: 44}) AS s5_completed,
        now - duration({hours: 50}) AS s4_completed,
        now - duration({hours: 52}) AS s3_completed,
        now - duration({hours: 56}) AS s2_completed,
        now - duration({hours: 58}) AS s1_completed

// Create Step 1
CREATE (s1:Step {seq: 1, name: 'Alloc'})
SET
    s1.sys = 'System A',
    s1.hrs = 2,
    s1.team = 'Operations',
    s1.duration = duration({hours: 2}),
    s1.completed = s1_completed
CREATE (t)-[:HAS_STEP {duration: 2.0}]->(s1)

// Create Step 2
CREATE (s2:Step {seq: 2, name: 'Confirm'})
SET
    s2.sys = 'System A',
    s2.hrs = 4,
    s2.team = 'Operations',
    s2.duration = duration({hours: 4}),
    s2.completed = s2_completed
CREATE (s1)-[:NEXT_STEP {duration: 4.0, completed: True}]->(s2)

// Create Step 3
CREATE (s3:Step {seq: 3, name: 'Book'})
SET
    s3.sys = 'System A',
    s3.hrs = 2,
    s3.team = 'Operations',
    s3.duration = duration({hours: 2}),
    s3.completed = s3_completed
CREATE (s2)-[:NEXT_STEP {duration: 2.0, completed: True}]->(s3)

// Create Step 4
CREATE (s4:Step {seq: 4, name: 'RiskCheck'})
SET
    s4.sys = 'System B',
    s4.hrs = 6,
    s4.team = 'Risk',
    s4.duration = duration({hours: 6}),
    s4.completed = s4_completed
CREATE (s3)-[:NEXT_STEP {duration: 6.0, completed: True}]->(s4)

// Create Step 5
CREATE (s5:Step {seq: 5, name: 'Netting'})
SET
    s5.sys = 'System B',
    s5.hrs = 8,
    s5.team = 'Risk',
    s5.duration = duration({hours: 8}),
    s5.completed = s5_completed
CREATE (s4)-[:NEXT_STEP {duration: 8.0, completed: True}]->(s5)

// Create Step 6
CREATE (s6:Step {seq: 6, name: 'SettlementInstr'})
SET
    s6.sys = 'System C',
    s6.hrs = 12,
    s6.team = 'BackOffice',
    s6.duration = duration({hours: 12}),
    s6.completed = s6_completed
CREATE (s5)-[:NEXT_STEP {duration: 12.0, completed: True}]->(s6)

// Create Step 7
CREATE (s7:Step {seq: 7, name: 'Affirmation'})
SET
    s7.sys = 'System C',
    s7.hrs = 12,
    s7.team = 'BackOffice',
    s7.duration = duration({hours: 12}),
    s7.completed = s7_completed
CREATE (s6)-[:NEXT_STEP {duration: 12.0, completed: True}]->(s7)

// Create Step 8
CREATE (s8:Step {seq: 8, name: 'Match'})
SET
    s8.sys = 'System C',
    s8.hrs = 8,
    s8.team = 'BackOffice',
    s8.duration = duration({hours: 8}),
    s8.completed = s8_completed
CREATE (s7)-[:NEXT_STEP {duration: 8.0, completed: True}]->(s8)

// Create Step 9
CREATE (s9:Step {seq: 9, name: 'Settle'})
SET
    s9.sys = 'System C',
    s9.hrs = 4,
    s9.team = 'BackOffice',
    s9.duration = duration({hours: 4}),
    s9.completed = s9_completed
CREATE (s8)-[:NEXT_STEP {duration: 4.0, completed: True}]->(s9)

// Create Completed Node
CREATE (c:Completed {trade_id: t.trade_id})
SET
    c.completed = s9_completed,
    c.status = 'Success'
CREATE (s9)-[:COMPLETED]->(c);

//
// STUCK TRADE
//
CREATE (tr:Trader {name: 'Trader 3'})

CREATE (t:Trade {trade_id: 'T-1003'})
SET 
    t.symbol = 'ESTC',
    t.qty = 100,
    t.price = 81.87,
    t.status = 'Pending',
    t.origin = 'Trader'
CREATE (tr)-[:TRADES]->(t)

// Calculate timestamps relative to current time
WITH t, datetime() AS now

// Calculate Step timestamps in reverse order (most recent to oldest)
WITH t, now, 
        now - duration({hours: 14}) AS s5_completed,
        now - duration({hours: 22}) AS s4_completed,
        now - duration({hours: 34}) AS s3_completed, 
        now - duration({hours: 46}) AS s2_completed,
        now - duration({hours: 54}) AS s1_completed
    //  now - duration({hours: 50}) AS s4_completed,
    //  now - duration({hours: 52}) AS s3_completed,
    //  now - duration({hours: 56}) AS s2_completed,
    //  now - duration({hours: 58}) AS s1_completed

// Create Step 1
CREATE (s1:Step {seq: 1, name: 'Alloc'})
SET
    s1.sys = 'System A',
    s1.hrs = 2,
    s1.team = 'Operations',
    s1.duration = duration({hours: 2}),
    s1.completed = s1_completed
CREATE (t)-[:HAS_STEP {duration: 2.0}]->(s1)

// Create Step 2
CREATE (s2:Step {seq: 2, name: 'Confirm'})
SET
    s2.sys = 'System A',
    s2.hrs = 4,
    s2.team = 'Operations',
    s2.duration = duration({hours: 4}),
    s2.completed = s2_completed
CREATE (s1)-[:NEXT_STEP {duration: 4.0}]->(s2)

// Create Step 3
CREATE (s3:Step {seq: 3, name: 'Book'})
SET
    s3.sys = 'System A',
    s3.hrs = 2,
    s3.team = 'Operations',
    s3.duration = duration({hours: 2}),
    s3.completed = s3_completed
CREATE (s2)-[:NEXT_STEP {duration: 2.0}]->(s3)

// Create Step 4
CREATE (s4:Step {seq: 4, name: 'RiskCheck'})
SET
    s4.sys = 'System B',
    s4.hrs = 6,
    s4.team = 'Risk',
    s4.duration = duration({hours: 6}),
    s4.completed = s4_completed
CREATE (s3)-[:NEXT_STEP {duration: 6.0}]->(s4)

// Create Step 5
CREATE (s5:Step {seq: 5, name: 'Netting'})
SET
    s5.sys = 'System B',
    s5.hrs = 8,
    s5.team = 'Risk',
    s5.duration = duration({hours: 8}),
    s5.completed = s5_completed
CREATE (s4)-[:NEXT_STEP {duration: 8.0}]->(s5);


//
// NOT SETTLED TRADE
//
CREATE (tr:Trader {name: 'Trader 4'})

CREATE (t:Trade {trade_id: 'T-1004'})
SET 
    t.symbol = 'MDB',
    t.qty = 100,
    t.price = 81.87,
    t.status = 'Pending',
    t.origin = 'Trader'
CREATE (tr)-[:TRADES]->(t)

// Calculate timestamps relative to current time
WITH t, datetime() AS now

// Calculate Step timestamps in reverse order (most recent to oldest)
WITH t, now, 
        now - duration({hours: 2}) AS s4_completed,
        now - duration({hours: 10}) AS s3_completed,
        now - duration({hours: 22}) AS s2_completed, 
        now - duration({hours: 34}) AS s1_completed
    //  now - duration({hours: 44}) AS s1_completed
    //  now - duration({hours: 50}) AS s4_completed,
    //  now - duration({hours: 52}) AS s3_completed,
    //  now - duration({hours: 56}) AS s2_completed,
    //  now - duration({hours: 58}) AS s1_completed

// Create Step 1
CREATE (s1:Step {seq: 1, name: 'Alloc'})
SET
    s1.sys = 'System A',
    s1.hrs = 2,
    s1.team = 'Operations',
    s1.duration = duration({hours: 2}),
    s1.completed = s1_completed
CREATE (t)-[:HAS_STEP {duration: 2.0}]->(s1)

// Create Step 2
CREATE (s2:Step {seq: 2, name: 'Confirm'})
SET
    s2.sys = 'System A',
    s2.hrs = 4,
    s2.team = 'Operations',
    s2.duration = duration({hours: 4}),
    s2.completed = s2_completed
CREATE (s1)-[:NEXT_STEP {duration: 4.0}]->(s2)

// Create Step 3
CREATE (s3:Step {seq: 3, name: 'Book'})
SET
    s3.sys = 'System A',
    s3.hrs = 2,
    s3.team = 'Operations',
    s3.duration = duration({hours: 2}),
    s3.completed = s3_completed
CREATE (s2)-[:NEXT_STEP {duration: 2.0}]->(s3)

// Create Step 4
CREATE (s4:Step {seq: 4, name: 'RiskCheck'})
SET
    s4.sys = 'System B',
    s4.hrs = 6,
    s4.team = 'Risk',
    s4.duration = duration({hours: 6}),
    s4.completed = s4_completed
CREATE (s3)-[:NEXT_STEP {duration: 6.0}]->(s4)

View All Trade Settlement Paths

This query shows the complete settlement journey for all trades, including:

Trade details (symbol, quantity, price)
Settlement steps and their sequence
Completion status

MATCH base=(t:Trader)-[:TRADES]->(tr:Trade)-[:HAS_STEP]->(s:Step)-[:NEXT_STEP*0..10]->(last:Step)
OPTIONAL MATCH completed=(last)-[:COMPLETED]->(:Completed)
RETURN base, completed

View Completed Trades Only

Let's focus specifically on trades that have completed the entire settlement process:

// Find all completed trades
MATCH p=(tr:Trade)-[:HAS_STEP]->(s:Step)-[:NEXT_STEP*]->(end:Step)-[:COMPLETED]->(:Completed)
RETURN p

This visualization shows only trades that have successfully completed all settlement steps.

Identify Unsettled Trades

Now let's look at trades that haven't completed settlement yet:

// Find all trades that have not settled
MATCH path =
      (tr:Trade)-[:HAS_STEP]->(first_s:Step)
          // Walk forward through NEXT_STEP hops, validating each pair on the fly
          ( (s_i)-[:NEXT_STEP]->(s_j)
                WHERE NOT (s_j)-[:COMPLETED]->(:Completed)
          )*
      (last_s:Step)
WHERE NOT (last_s)-[:NEXT_STEP]->(:Step)
WITH tr, last_s
RETURN 
    tr.trade_id as trade_id,
    tr.symbol as symbol,
    tr.status as status,
    last_s.name as current_step,
    last_s.team as responsible_team,
    last_s.sys as current_system
ORDER BY tr.trade_id

Settlement Performance Analytics

Let's analyze the average duration for each settlement step:

MATCH (:Step)-[r:NEXT_STEP {completed: True}]->(s:Step)
RETURN s.name, 
       s.team as team,
       s.sys as system,
       avg(r.duration) as avg_duration_hours
ORDER BY avg_duration_hours

Identify Stuck Trades

This query identifies trades that are taking longer than expected at their current step:

// Match the current path of an in-progress trade through its steps
MATCH path =
        (tr:Trade)-[:HAS_STEP]->(first_s:Step)
            // Traverse the sequence of steps that are not yet completed
            ( (s_i)-[:NEXT_STEP]->(s_j)
                WHERE NOT (s_j)-[:COMPLETED]->(:Completed)
            )*
        (last_s:Step)
// Ensure that the last step in the path has no further NEXT_STEPs (i.e. it's the current active step)
WHERE NOT (last_s)-[:NEXT_STEP]->(:Step)

// Extract relevant information for each trade and calculate how long the current step has been running
WITH  tr.trade_id AS trade_id, 
        tr.symbol as symbol,
        last_s.name AS step_name, 
        last_s.team as team,
        duration.inSeconds(last_s.completed, datetime()).hours AS current_step_slippage

// Find completed transitions between steps that match the current step name
MATCH (s:Step)-[r:NEXT_STEP {completed: True}]->(:Step {name: step_name})

// Calculate average duration and SLA threshold (20% above average) for that step
WITH step_name, trade_id, symbol, team,
        avg(r.duration) AS expected_duration, 
        avg(r.duration) * 1.2 AS sla_threshold, 
        current_step_slippage

// Filter to only those steps where the current step has exceeded the SLA threshold
WHERE current_step_slippage > sla_threshold

// Return trade info along with expected vs actual time, and the delay
RETURN trade_id, 
        symbol,
        step_name,
        expected_duration as expected_hours,
        current_step_slippage as actual_hours,
        (current_step_slippage - expected_duration) as delay_hours
ORDER BY delay_hours DESC

Key Benefits for Hedge Funds

Real-time visibility into trade settlement status
Early warning system for potential settlement fails
Performance analytics by step, system, and team
SLA monitoring and breach alerts
Historical pattern analysis for process optimization

The graph model makes it easy to:

Track complex settlement workflows
Identify bottlenecks and dependencies
Monitor settlement efficiency
Generate real-time alerts