Skip to content

Financial Risk Calculation

Shared by: Allan Konar

The financial industry’s current approach to managing mandated operational risk capital requirements, batch processing, often leads to over- or under-allocation of certain classes of funds, operating with tight time constraints, and slow reactions to changing market conditions.

By responding to market changes in real time, organizations can provide adequate coverage for risk exposure while ensuring their regulatory compliance minimally affects their asset allocation.

This recipe intends to show an example of conditionally adjusting data (investment value) based on the manifested nodes' property (investment class) before aggregating the adjusted values at multiple levels. Further, it uses the adjusted aggregates to alert on threshold crossings (ratio of adjusted values of specific classes to the sum of all the adjusted values).

The recipe highlights three technical strategies:

  1. Use of NumberIteratorIngest to generate sample transactions
  2. Conditional handling of data
  3. Real-time graph-based data (from #2) aggregated across multiple levels

Full Recipe

Financial Risk Recipe
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
version: 1
title: Financial Risk Recipe
description: |-
  The financial industry’s current approach to managing mandated operational risk capital 
  requirements, batch processing, often leads to over- or under-allocation of certain 
  classes of funds, operating with tight time constraints, and slow reactions to changing 
  market conditions.

  By responding to market changes in real time, organizations can provide adequate coverage 
  for risk exposure while ensuring their compliance minimally affects their asset allocation.

  The intent of this recipe is to show an example of conditionally adjusting data (investment 
  value) based on a property (investment class) of the manifested nodes prior to aggregating the 
  value at multiple levels.  Further, the adjusted aggregates are used to alert on threshold 
  crossing (percentage of value of specific classes).

  This is accomplished via three technical strategies:

  1. Use of `NumberIteratorIngest` to generate sample transactions
  2. Conditional handling of data
  3. Real-time graph-based data (from #2) aggregated across multiple levels

ingestStreams:
  - type: NumberIteratorIngest
    ingestLimit: 1
    format:
      type: CypherLine
      query: |-
        WITH 0 AS institutionId
        // Generate 10 desks - change the range bound to alter the number of generated desks
        UNWIND range(1, 10) AS deskId
        MATCH (institution), (desk)
        WHERE id(institution) = idFrom('institution', institutionId)
            AND id(desk) = idFrom('desk', institutionId, deskId)

        SET institution:institution

        SET desk:desk,
            desk.deskNumber = deskId

        CREATE (institution)-[:HAS]->(desk)

        WITH *
        // Generate 1000 investments per desk- change the range bound to alter the number of investments generated per desk
        UNWIND range(1, 1000) AS investmentId
        MATCH (investment)
        WHERE id(investment) = idFrom('investment', institutionId, deskId, investmentId)

        SET investment:investment,
            investment.investmentId = toInteger(toString(deskId) + toString(investmentId)),
            investment.type = toInteger(rand() * 10) + 1,
            investment.code = gen.string.from(strId(investment), 25),
            investment.value = gen.float.from(strId(investment)) * 100

        WITH id(investment) AS invId, desk, investment
        CALL { 
              WITH invId
              MATCH (investment:investment) 
              WHERE id(investment) = invId
              SET investment.class = CASE
                WHEN investment.type <= 5 THEN '1'
                WHEN investment.type >= 6 AND investment.type <= 8 THEN '2a'
                WHEN investment.type >= 9 THEN '2b'
              END

              RETURN investment.type AS type
            }

        CREATE (desk)-[:HOLDS]->(investment)

standingQueries:
  - pattern:
      type: Cypher
      query: |-
        MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
        RETURN DISTINCT id(investment) AS id
      mode: DistinctId
    outputs:
      adjustValues:
        type: CypherQuery
        query: |-
          MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
          WHERE id(investment) = $that.data.id

          SET investment.adjustedValue = CASE
                WHEN investment.class = '1' THEN investment.value
                WHEN investment.class = '2a' THEN investment.value * .85
                WHEN investment.class = '2b' AND investment.type = 9 THEN investment.value * .75
                WHEN investment.class = '2b' AND investment.type = 10 THEN investment.value * .5
              END

  - pattern:
      type: Cypher
      query: |-
        MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
        WHERE investment.adjustedValue IS NOT NULL
        RETURN DISTINCT id(investment) AS id
      mode: DistinctId
    outputs:
      rollUps:
        type: CypherQuery
        query: |-
          MATCH (investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
          WHERE id(investment) = $that.data.id
            AND investment.adjustedValue IS NOT NULL

          UNWIND [["1","adjustedValue1"], ["2a","adjustedValue2a"], ["2b","adjustedValue2b"]] AS stuff

          WITH institution,investment,desk,stuff
          WHERE investment.class = stuff[0]

          CALL float.add(institution,stuff[1],investment.adjustedValue) YIELD result AS institutionAdjustedValueRollupByClass
          CALL float.add(institution,"totalAdjustedValue",investment.adjustedValue) YIELD result AS institutionAdjustedValueRollup

          CALL float.add(desk,stuff[1],investment.adjustedValue) YIELD result AS deskAdjustedValueRollupByClass
          CALL float.add(desk,"totalAdjustedValue",investment.adjustedValue) YIELD result AS deskAdjustedValueRollup

          SET institution.percentAdjustedValue2 = ((institution.adjustedValue2a + institution.adjustedValue2b)/institution.totalAdjustedValue) * 100,
              institution.percentAdjustedValue2b = (institution.adjustedValue2b/institution.totalAdjustedValue) * 100

  - pattern:
      type: Cypher
      query: |-
        MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
        RETURN DISTINCT id(investment) AS id
      mode: DistinctId
    outputs:
      class2CompositionAlert:
        type: CypherQuery
        query: |-
          MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
          WHERE id(investment) = $that.data.id
            AND (institution.investments = 2500 OR institution.investments = 5000 OR institution.investments = 10000)
            AND institution.percentAdjustedValue2 > 40

          RETURN institution.percentAdjustedValue2 AS Class_2_Composition
        andThen:
          type: PrintToStandardOut
      class2bCompositionAlert:
        type: CypherQuery
        query: |-
          MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
          WHERE id(investment) = $that.data.id
            AND (institution.investments = 2500 OR institution.investments = 5000 OR institution.investments = 10000)
            AND institution.percentAdjustedValue2b > 15

          RETURN institution.percentAdjustedValue2b AS Class_2b_Composition
        andThen:
          type: PrintToStandardOut
quickQueries:
  - predicate:
      propertyKeys: []
      knownValues: {}
    quickQuery:
      name: "[Node] Adjacent Nodes"
      querySuffix: MATCH (n)--(m) RETURN DISTINCT m
      queryLanguage: Cypher
      sort: Node
  - predicate:
      propertyKeys: []
      knownValues: {}
    quickQuery:
      name: "[Node] Parent Node"
      querySuffix: MATCH (n)<-[]-(m) RETURN DISTINCT m
      queryLanguage: Cypher
      sort: Node
  - predicate:
      propertyKeys: []
      knownValues: {}
    quickQuery:
      name: "[Node] Refresh"
      querySuffix: RETURN n
      queryLanguage: Cypher
      sort: Node
  - predicate:
      propertyKeys: []
      knownValues: {}
    quickQuery:
      name: "[Text] Local Properties"
      querySuffix: RETURN id(n) AS NODE_ID, labels(n) AS NODE_LABELS, properties(n) AS NODE_PROPERTIES
      queryLanguage: Cypher
      sort: Text
  - predicate:
      propertyKeys: []
      knownValues: {}
    quickQuery:
      name: "[Text] Node Label"
      querySuffix: RETURN labels(n)
      queryLanguage: Cypher
      sort: Text

sampleQueries:
  - name: Last 10 Nodes
    query: CALL recentNodes(10)
  - name: Legend (show one of each node type)
    query: MATCH (n) WHERE labels(n) IS NOT NULL WITH labels(n) AS kind, collect(n) AS legend RETURN legend[0]
  - name: Show distribution of investment node classes (grouped by desk)
    query: MATCH (investment:investment)<-[]-(desk:desk) RETURN desk.deskNumber AS DESK, investment.investmentId AS INVESTMENT, investment.class AS CLASS ORDER BY desk.deskNumber
  - name: Wiretap Standing Query 1
    query: 'CALL standing.wiretap({ name: "STANDING-1"}) YIELD meta, data WHERE meta.isPositiveMatch MATCH (n) WHERE id(n) = data.id RETURN properties(n)'
  - name: Wiretap Standing Query 2
    query: 'CALL standing.wiretap({ name: "STANDING-2"}) YIELD meta, data WHERE meta.isPositiveMatch MATCH (n) WHERE id(n) = data.id RETURN properties(n)'
  - name: Wiretap Standing Query 3
    query: 'CALL standing.wiretap({ name: "STANDING-3"}) YIELD meta, data WHERE meta.isPositiveMatch MATCH (n) WHERE id(n) = data.id RETURN properties(n)'

nodeAppearances:
  - predicate:
      propertyKeys:
        - type
      knownValues:
        type: 1
      dbLabel: investment
    icon: ion-cash
    color: "#85BB65"
    size:
    label:
      type: Property
      key: investmentId
      prefix: "Investment ID: "
  - predicate:
      propertyKeys:
        - type
      knownValues:
        type: 2
      dbLabel: investment
    icon: ion-cash
    color: "#85BB65"
    size:
    label:
      type: Property
      key: investmentId
      prefix: "Investment ID: "
  - predicate:
      propertyKeys:
        - type
      knownValues:
        type: 3
      dbLabel: investment
    icon: ion-cash
    color: "#85BB65"
    size:
    label:
      type: Property
      key: investmentId
      prefix: "Investment ID: "
  - predicate:
      propertyKeys:
        - type
      knownValues:
        type: 4
      dbLabel: investment
    icon: ion-cash
    color: "#85BB65"
    size:
    label:
      type: Property
      key: investmentId
      prefix: "Investment ID: "
  - predicate:
      propertyKeys:
        - type
      knownValues:
        type: 5
      dbLabel: investment
    icon: ion-cash
    color: "#85BB65"
    size:
    label:
      type: Property
      key: investmentId
      prefix: "Investment ID: "
  - predicate:
      propertyKeys:
        - type
      knownValues:
        type: 6
      dbLabel: investment
    icon: ion-android-warning
    color: "#FFAA33"
    size:
    label:
      type: Property
      key: investmentId
      prefix: "Investment ID: "
  - predicate:
      propertyKeys:
        - type
      knownValues:
        type: 7
      dbLabel: investment
    icon: ion-android-warning
    color: "#FFAA33"
    size:
    label:
      type: Property
      key: investmentId
      prefix: "Investment ID: "
  - predicate:
      propertyKeys:
        - type
      knownValues:
        type: 8
      dbLabel: investment
    icon: ion-android-warning
    color: "#FFAA33"
    size:
    label:
      type: Property
      key: investmentId
      prefix: "Investment ID: "
  - predicate:
      propertyKeys:
        - type
      knownValues:
        type: 9
      dbLabel: investment
    icon: ion-android-alert
    color: "#880808"
    size:
    label:
      type: Property
      key: investmentId
      prefix: "Investment ID: "
  - predicate:
      propertyKeys:
        - type
      knownValues:
        type: 10
      dbLabel: investment
    icon: ion-android-alert
    color: "#880808"
    size:
    label:
      type: Property
      key: investmentId
      prefix: "Investment ID: "
  - predicate:
      propertyKeys: []
      knownValues: {}
      dbLabel: desk
    icon: ion-archive
    color: "#aaa9ad"
    size:
    label:
      type: Property
      key: deskNumber
      prefix: "Desk: "
  - predicate:
      propertyKeys: []
      knownValues: {}
      dbLabel: institution
    icon: ion-android-home
    color: "#AA4A44"
    size:

Download Recipe

Scenario

This recipe is modeled on regulatory monitoring requirements like the Basel III Liquidity Coverage Ratio (LCR), Net Stable Funding Ratio (NSFR), and liquidity risk monitoring tools as described in https://www.bis.org/bcbs/basel3.htm. This class of capital and liquidity requirements calls for, at a minimum, aggregation of trades/collateral/investments by class, as well as potentially adjusting valuations based on the potential for a haircut before rollups.

How it Works

INGEST

The recipe generates a hierarchy for an institution with a (configurable) fixed number of desks and investments in the ingest stream utilizing the NumberIteratorIngest. We limit the ingest to simulate one set of investments and associated desks in the recipe by configuring ingestLimit: 1.

Ingest Flow

The first part of the ingest generates ten trading desks using a Cypher UNWIND clause and connects them to the parent institution node.

  - type: NumberIteratorIngest
    ingestLimit: 1
    format:
     type: CypherLine
     query: |-
      WITH 0 AS institutionId
      // Generate 10 desks - change the range bound to alter the number of generated desks
      UNWIND range(1, 10) AS deskId
      MATCH (institution), (desk)
      WHERE id(institution) = idFrom('institution', institutionId)
       AND id(desk) = idFrom('desk', institutionId, deskId)

      SET institution:institution

      SET desk:desk,
       desk.deskNumber = deskId

      CREATE (institution)-[:HAS]->(desk)
POST /api/v1/ingest/INGEST-1
{
  "type": "NumberIteratorIngest",
  "format": {
    "type": "CypherLine",
    "query": "WITH 0 AS institutionId
       // Generate 10 desks - change the range bound to alter the number of generated desks
       UNWIND range(1, 10) AS deskId
       MATCH (institution), (desk)
       WHERE id(institution) = idFrom('institution', institutionId)
           AND id(desk) = idFrom('desk', institutionId, deskId)

       SET institution:institution

       SET desk:desk,
           desk.deskNumber = deskId

       CREATE (institution)-[:HAS]->(desk)",
  }
}

Note

You can change the number of trading desks by altering the upper bound of the UNWIND clause. For example, if we wanted to generate 100 desks, we would modify the clause to be UNWIND range(1, 100) AS deskId.

The second part of the ingest stream generates 1000 investments per desk using a Cypher UNWIND clause and connects them to the parent desk node. There are two interesting techniques in this part of the recipe:

  1. Both random (investment.type) and deterministic (investment.code and investment.value) property generation for the investment nodes; and
  2. Assigning one of several potential property values based on the value of a previously generated property.

A value between 1-10 for investment.type is generated with a combination of the rand Cypher function to generate a random float between 0 (inclusive) and 1 (exclusive), multiplying that value by 10, casting it to an integer, then adding 1.

investment.code and investment.value utilize the gen class of custom Cypher functions to deterministically generate a value of a specific class from the provided input. Because we generate the QuineId via the idFrom() function, which act as seeds for the gen classes in use (gen.string.from() and gen.float.from()).

Lastly, the investment.class is assigned based on the value of investment.type via a subquery that utilizes the CASE statement. This value (which models the classes in LCR) is used later in the recipe to determine the adjustment to the investment value aggregated at the desk and institution levels.

WITH *
// Generate 1000 investments per desk- change the range bound to alter the number of investments generated per desk
UNWIND range(1, 1000) AS investmentId
MATCH (investment)
WHERE id(investment) = idFrom('investment', institutionId, deskId, investmentId)

SET investment:investment,
    investment.investmentId = toInteger(toString(deskId) + toString(investmentId)),
    investment.type = toInteger(rand() * 10) + 1,
    investment.code = gen.string.from(strId(investment), 25),
    investment.value = gen.float.from(strId(investment)) * 100

WITH id(investment) AS invId, desk, investment
CALL { 
      WITH invId
      MATCH (investment:investment) 
      WHERE id(investment) = invId
      SET investment.class = CASE
        WHEN investment.type <= 5 THEN '1'
        WHEN investment.type >= 6 AND investment.type <= 8 THEN '2a'
        WHEN investment.type >= 9 THEN '2b'
      END

      RETURN investment.type AS type
    }

CREATE (desk)-[:HOLDS]->(investment)
POST /api/v1/ingest/INGEST-1
{
  "type": "NumberIteratorIngest",
  "format": {
    "type": "CypherLine",
    "query": "        WITH *
    // Generate 1000 investments per desk- change the range bound to alter the number of investments generated per desk
    UNWIND range(1, 1000) AS investmentId
    MATCH (investment)
    WHERE id(investment) = idFrom('investment', institutionId, deskId, investmentId)

    SET investment:investment,
        investment.investmentId = toInteger(toString(deskId) + toString(investmentId)),
        investment.type = toInteger(rand() * 10) + 1,
        investment.code = gen.string.from(strId(investment), 25),
        investment.value = gen.float.from(strId(investment)) * 100

    WITH id(investment) AS invId, desk, investment
    CALL { 
          WITH invId
          MATCH (investment:investment) 
          WHERE id(investment) = invId
          SET investment.class = CASE
            WHEN investment.type <= 5 THEN '1'
            WHEN investment.type >= 6 AND investment.type <= 8 THEN '2a'
            WHEN investment.type >= 9 THEN '2b'
          END

          RETURN investment.type AS type
        }

    CREATE (desk)-[:HOLDS]->(investment)",
  }
}

This generates a three-level tree structure with Institution at the top, ten desks next and 1,000 investments under each desk.

Three-level tree

STANDING-1

Generate Adjusted Value

The first standing query generates a new property with a value based on another property. The standing query pattern is used to match every generated investment.

Standing Query Flow

    - pattern:
        type: Cypher
        query: |-
          MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
          WHERE investment.adjustedValue IS NOT NULL

          RETURN DISTINCT id(investment) AS id
/api/v1/query/standing/STANDING-1
[
    {
        "pattern": {
        "type": "Cypher",
        "query": "MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
            WHERE investment.adjustedValue IS NOT NULL

            RETURN DISTINCT id(investment) AS id"
        },
        "outputs": {
        "adjustValues": {
            "type": "CypherQuery",
            "query": "MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
              WHERE id(investment) = $that.data.id

              SET investment.adjustedValue = CASE
                    WHEN investment.class = '1' THEN investment.value
                    WHEN investment.class = '2a' THEN investment.value * .85
                    WHEN investment.class = '2b' AND investment.type = 9 THEN investment.value * .75
                    WHEN investment.class = '2b' AND investment.type = 10 THEN investment.value * .5
                END",
        }
        }
    }
]

The standing query output then sets a property to track the adjusted value of the investment in a way that mimics Basel III LCR HQLA (High Quality Liquid Assets).

Class Factor
1 100%
2A 85%
2B RMBS 75%
2B Equities 50%

Where the Factor represents the required adjustment to the investment class, for example, an investment of $100 will be rolled up with the following adjusted values:

Class Adjusted Value
1 $100
2A $85
2B RMBS $75
2B Equities $50

This is accomplished via the use of a CASE statement, the last two of which utilize a combination of properties to determine the adjusted value.

    outputs:
      rollUps:
        type: CypherQuery
        query: |-
          MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
          WHERE id(investment) = $that.data.id

          SET investment.adjustedValue = CASE
                WHEN investment.class = '1' THEN investment.value
                WHEN investment.class = '2a' THEN investment.value * .85
                WHEN investment.class = '2b' AND investment.type = 9 THEN investment.value * .75
                WHEN investment.class = '2b' AND investment.type = 10 THEN investment.value * .5
              END

investment node properties

STANDING-2

Calculate Rollups

The second standing query serves three purposes:

  1. Aggregating the total adjusted values of investments to both the desk and institution levels;
  2. Aggregating the per-class adjusted values of investments to both the desk and institution levels; and
  3. Calculating the Class 2 and 2B composition of the total adjusted values at the institutional level

As with the first standing query, the query pattern is used to match every generated investment.

Standing Query Flow

    - pattern:
        type: Cypher
        query: |-
          MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
          WHERE investment.adjustedValue IS NOT NULL

        RETURN DISTINCT id(investment) AS id
/api/v1/query/standing/STANDING-2
    [
        {
            "pattern": {
            "type": "Cypher",
            "query": "MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
                WHERE investment.adjustedValue IS NOT NULL

                RETURN DISTINCT id(investment) AS id"
            },
            "rollUps": {
            "adjustValues": {
                "type": "CypherQuery",
                "query": "MATCH (investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
                    WHERE id(investment) = $that.data.id
                      AND investment.adjustedValue IS NOT NULL

                    UNWIND [["1","adjustedValue1"], ["2a","adjustedValue2a"], ["2b","adjustedValue2b"]] AS stuff

                    WITH institution,investment,desk,stuff
                    WHERE investment.class = stuff[0]

                    CALL float.add(institution,stuff[1],investment.adjustedValue) YIELD result AS institutionAdjustedValueRollupByClass
                    CALL float.add(institution,"totalAdjustedValue",investment.adjustedValue) YIELD result AS institutionAdjustedValueRollup

                    CALL float.add(desk,stuff[1],investment.adjustedValue) YIELD result AS deskAdjustedValueRollupByClass
                    CALL float.add(desk,"totalAdjustedValue",investment.adjustedValue) YIELD result AS deskAdjustedValueRollup

                    SET institution.percentAdjustedValue2 = ((institution.adjustedValue2a + institution.adjustedValue2b)/institution.totalAdjustedValue) * 100,
                        institution.percentAdjustedValue2b = (institution.adjustedValue2b/institution.totalAdjustedValue) * 100",
            }
            }
        }
    ]

The standing query output can be grouped by functionality:

Determine class per investment

We determine the class per investment for conditional processing in the next steps.

We create a list of tuples, operate on them with the UNWIND clause, and assign a new name for the inner values (Cypher requires this).

UNWIND [["1","adjustedValue1"], ["2a","adjustedValue2a"], ["2b","adjustedValue2b"]] AS stuff

We then use the first value of the ordered pairs to match the investment.class by referring to its value by index.

WITH institution,investment,desk,stuff
WHERE investment.class = stuff[0]
Generate property name based on class

We then use the second value of the ordered pairs to match the generated property name by referring to its value by index and aggregate the adjusted value at both the desk and institutional levels. At the same time, we aggregate all of the classes' investments at the desk level (in the desk.totalAdjustedValue property) and institutional level (in the institution.totalAdjustedValue property).

CALL float.add(institution,stuff[1],investment.adjustedValue) YIELD result AS institutionAdjustedValueRollupByClass
CALL float.add(institution,"totalAdjustedValue",investment.adjustedValue) YIELD result AS institutionAdjustedValueRollup

CALL float.add(desk,stuff[1],investment.adjustedValue) YIELD result AS deskAdjustedValueRollupByClass
CALL float.add(desk,"totalAdjustedValue",investment.adjustedValue) YIELD result AS deskAdjustedValueRollup

desk rollup

Calculate composition of class 2 and 2b investments

Lastly, we utilize the aggregations to calculate the composition of class 2 and 2b investments at the desk and institutional levels for the third standing query for threshold-based alerting..

SET institution.percentAdjustedValue2 = ((institution.adjustedValue2a + institution.adjustedValue2b)/institution.totalAdjustedValue) * 100,
    institution.percentAdjustedValue2b = (institution.adjustedValue2b/institution.totalAdjustedValue) * 100

institution percentage

STANDING-3

Alert based on thresholds

The third standing query utilizes the aggregations from the previous standing query for threshold-based alerting. As with the first standing query, the query pattern matches every generated investment.

Standing Query Flow

  - pattern:
      type: Cypher
      query: |-
        MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
        RETURN DISTINCT id(investment) AS id
      mode: DistinctId
/api/v1/query/standing/STANDING-3
[
  {
      "pattern": {
        "type": "Cypher",
        "query": "MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
                  WHERE investment.adjustedValue IS NOT NULL
                  RETURN DISTINCT id(investment) AS id"
      },
      "outputs": {
        "class2CompositionAlert": {
            "type": "CypherQuery",
            "query": "MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
        WHERE id(investment) = $that.data.id
            AND (institution.investments = 2500 OR institution.investments = 5000 OR institution.investments = 10000)
        AND institution.percentAdjustedValue2 > 40

        RETURN institution.percentAdjustedValue2 AS Class_2_Composition","andThen": {
              "type": "PrintToStandardOut"
            },
        },
        "class2bCompositionAlert": {
            "type": "CypherQuery",
            "query": "MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
            WHERE id(investment) = $that.data.id
              AND (institution.investments = 2500 OR institution.investments = 5000 OR institution.investments = 10000)
              AND institution.percentAdjustedValue2b > 15

            RETURN institution.percentAdjustedValue2b AS Class_2b_Composition","andThen": {
              "type": "PrintToStandardOut"
            }
        }
      }
  }
]

For the sake of simplicity, we set two parameters for alert thresholding. To mimic the Basel III LCR asset allocation requirements:

  • Class 2 investments must not account for more than 40% of total HQLA; and
  • Class2B investments must not account for more than 15% of total HQLA

by setting thresholds for institution.percentAdjustedValue2 and institution.percentAdjustedValue2b.

To minimize the rate of alerts, we set thresholds on institution.investments.

MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
WHERE id(investment) = $that.data.id
      AND (institution.investments = 2500 OR institution.investments = 5000 OR institution.investments = 10000)

We would use other thresholds in production, such as time of day.

STANDING-3 produces output in the terminal window like:

2023-05-11 17:56:58,207 Standing query `class2CompositionAlert` match: {"meta":{"isPositiveMatch":true,"resultId":"93868c2f-10fe-a76b-9195-01b4007e11a5"},"data":{"Class_2_Composition":43.66579601931527}}
2023-05-11 17:56:59,621 Standing query `class2CompositionAlert` match: {"meta":{"isPositiveMatch":true,"resultId":"59fdf2b4-093b-1755-458d-0c4934f328a6"},"data":{"Class_2b_Composition":17.5875533423801}}

Running the Recipe

Warning

This greatly simplified financial risk recipe simulates a single ingest cycle to demonstrate the capabilities of Quine. Do not make financial decisions based on its output.

 java -jar quine-1.8.1.jar -r finance_risk.yaml
Graph is ready
Running Recipe: Financial Risk Recipe
Using 12 node appearances
Using 5 quick queries
Using 4 sample queries
Running Standing Query STANDING-1
Running Standing Query STANDING-2
Running Standing Query STANDING-3
Running Ingest Stream INGEST-1

Summary

Summary.

Tip

Quick Queries are available by right clicking on a node.

Quick Query Node Type Description
Adjacent Nodes All Display the nodes that are adjacent to this node.
Refresh All Refresh the content stored in a node
Local Properties All Display the properties stored by the node
Node Label All Return the label(s) for the selected node
Parent Node All Retrieve the parent node

Build your skills

What would happen if we switched ingestLimit: 1 in the ingest to ingestLimit: 2?

Answer

The ingest would run twice, generating the same institution, desks, and investment nodes. The investment nodes would generate new types and values, affecting the total rollup value.

How could we add aggregating levels to the recipe?

Answer

By adding nodes to the ingest and adding additional CALL float.add() custom Cypher procedures to STANDING-2.

The recipe utilises UNWIND to generate desks and investments serially and deterministically. How could we do the same in a non-serial form?

Answer

We could utilise other methods to generate the IDs for the desks and investments rather than relying on the UNWIND. For example, generating hashes for each of the node classes based on properties:

WITH toInteger($that) AS x
WITH *,
    x AS investmentID
WITH *,
    toInteger(gen.float.from(hash(x, 'desk')) * 10) AS deskId

The recipe doesn't calculate net cash flow or the ratio of HQLA to net cash flow. How could we do that?

Answer

We could configure additional ingests for the cash flow data and standing queries to generate the required calculations.