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:
- Use of
NumberIteratorIngestto generate sample transactions - Conditional handling of data
- 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 | |
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.
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)
{
"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:
- Both random (
investment.type) and deterministic (investment.codeandinvestment.value) property generation for the investment nodes; and - 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
}
CREATE (desk)-[:HOLDS]->(investment)
{
"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
}
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.
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.
- 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
[
{
"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
STANDING-2¶
Calculate Rollups¶
The second standing query serves three purposes:
- Aggregating the total adjusted values of investments to both the desk and institution levels;
- Aggregating the per-class adjusted values of investments to both the desk and institution levels; and
- 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.
- 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
[
{
"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
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
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.
- pattern:
type: Cypher
query: |-
MATCH (investment:investment)<-[:HOLDS]-(desk:desk)<-[:HAS]-(institution:institution)
RETURN DISTINCT id(investment) AS id
mode: DistinctId
[
{
"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.6.2.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.