=Paper=
{{Paper
|id=Vol-2407/paper-15-169
|storemode=property
|title=
Web based application for operational loss collection and value-at-risk and expected shortfall calculation
|pdfUrl=https://ceur-ws.org/Vol-2407/paper-15-169.pdf
|volume=Vol-2407
|authors=Sergey G. Shorokhov,Victoria V. Khaptakhanova
|dblpUrl=https://dblp.org/rec/conf/ittmm/ShorokhovK19
}}
==
Web based application for operational loss collection and value-at-risk and expected shortfall calculation
==
https://ceur-ws.org/Vol-2407/paper-15-169.pdf
141
UDC 004.652.5
Web based application for operational loss collection and
value-at-risk and expected shortfall calculation
Sergey G. Shorokhov, Victoria V. Khaptakhanova
*
Department of Information Technology
Peoples’ Friendship University of Russia (RUDN University)
6 Miklukho-Maklaya St, Moscow, 117198, Russian Federation
Email: shorokhov-sg@rudn.ru, vkhaptakhanova@mail.ru
We study some issues of operational risk information system design and implementation in
compliance with new requirements of Basel Committee on Banking Supervision and Bank
of Russia. To meet regulatory requirements, banks need to create and regularly update an
analytical database on operational risk losses extending back for some years. We provide
requirements to the form and contents of the information to be registered for operational risk
events and losses and list mandatory classifiers for risk event database. The structure of tables
in risk event database is presented on entity-relationship diagram. Operational risk information
system can be rapidly developed as a distributed system using web-framework Django. Such
Django features as MVC (Model-View-Controller) pattern and ORM (Object-Relational
Mapping) speed up the development of web-based application for risk event database and
allow to avoid manipulating sophisticated SQL expressions. We provide examples of Django
classes in Python for model component of Django risk event database application. We also
develop and present algorithm for Value-at-Risk and Expected Shortfall calculation using
data in operational risk event database. The algorithm can be easily implemented in Python
using Django database-abstraction API.
Key words and phrases: operational risk, Model-View-Controller, Object-Relational
Mapping, Django framework, Value-at-Risk, Expected Shortfall.
Copyright © 2019 for the individual papers by the papers’ authors. Copying permitted for private and
academic purposes. This volume is published and copyrighted by its editors.
In: K. E. Samouylov, L. A. Sevastianov, D. S. Kulyabov (eds.): Selected Papers of the IX Conference
“Information and Telecommunication Technologies and Mathematical Modeling of High-Tech Systems”,
Moscow, Russia, 19-Apr-2019, published at http://ceur-ws.org
142 ITTMM—2019
1. Introduction
Basically, operational risk is the risk of losses due to unreliability of the internal
management procedures, negligence of employees, failure of information systems or the
impact of external events [1]. Along with market and credit risks, operational risk is
regarded as one of the most material financial risks and credit institutions worldwide
are obliged to establish a risk and capital management system to detect, evaluate,
and aggregate operational risk and other most material risks and assess their capital
adequacy [2, 3].
In 2017, Basel Committee on Banking Supervision (BCBS) finalized the Basel III
framework [4], updating minimum capital requirements for operational risk and criteria
for identification, collection and treatment of internal loss data. In 2018, Bank of Russia
released draft regulation [5] on the requirements to the operational risk management.
We study some methodological and practical aspects of operational risk information
system design and implementation and subsequent calculation of operational risk indi-
cators Value-at-Risk and Expected Shortfall using available data on operational losses
occurred.
2. Operational risk event database structure
According to [1] each credit institution creates and maintains the analytical database
on losses incurred due to operational risk events (event database). The event database
shall contain information on the amount and type of losses, the dates of event occurrence,
recognition and completion, event circumstances and other relevant information [6].
According to the requirements for maintaining the event database [5] (ch. 6), the
event database should contain, in particular, the following information:
– unique serial identification number of the event;
– identifier of the group of homogeneous events (if any);
– date and time of event occurrence;
– date and time of event identification;
– date and time of event completion;
– event status;
– business unit, associated with the event;
– detailed description of the event;
– loss event source (cause) codes, including the code of main source (cause);
– loss event type code;
– additional (level 2) loss event type code;
– operational risk type code;
– other banking risk type codes (if any);
– business line (or business process level 1) code;
– business process (level 2) code;
– code of the bank information system used when the event occurred;
– operational risk loss data.
The following classifiers should be used when recording operational loss events:
– classifier of loss event sources (causes) [5] (clause 2.3);
– classifier of operational risk types [5] (clause 2.3.5);
– classifier of loss event types (level 1) [5] (clause 2.7);
– classifier of loss event types (level 2) [5] (addendum 1);
– classifier of business lines (level 1) [5] (clause 2.10);
– classifier of business lines (level 2) or business processes according to internal
documents of the credit institution or, for instance, [7];
– classifier of direct and indirect operational losses [5] (clause 2.11);
– classifier of business units of the credit institution [5] (clause 6.6);
– classifier of other types of banking risks [5] (clause 6.6);
– classifier of information systems of the credit institution [5] (clause 6.6).
Shorokhov S. G., Khaptakhanova V. V. 143
Figure 1. Operational risk event database structure
Thus, the event database should contain the main table with detailed information on
operational risk event and loss and classifiers for the event table fields (Fig. 1).
3. Implementation of operational risk event database application
Operational risk event database should embrace all divisions of the bank, including
geographically remote branches, and risk event collection application should be designed
and implemented as a distributed system.
At present web development is essentially done in Python language [8] and Django
is the core Python framework for creating database-driven websites [9]. Some of the
well-known Django websites include Instagram, Pinterest, Dropbox, Spotify.
Django framework is an example of Model–View–Controller (MVC) architecture,
first introduced in Smalltalk programming environment [10], which allows developers
to change the visual part and the business logic of an application separately, with-
out affecting one another [11]. Actually, Django architecture is called sometimes as
Model–View–Template (MVT), because there are three independent layers (Model, View,
and Template) in Django, responsible for managing different parts of an application (Fig.
2). Communication between the layers is possible only via an application programming
interface (API).
Model component is the single source of information about application data in Django.
It contains separate model (Python class) for each table (entity) of the application
database. Django officially supports four application databases: PostgreSQL, MySQL,
SQLite, and Oracle.
Model in Django is a special Python class and it holds business logic, properties,
specific methods, and other behavior of manipulated data. Models in Django allow
developers to create, read, update, and delete data in the original database without
using complicated SQL expressions. Django also automatically generates web pages for
maintaining model data on special administrative site of Django application [12].
144 ITTMM—2019
HTTP request
URLS
(urls.py)
Forwarding request
Model Data View Page Template
(models.py) (views.py) (filenames.html)
HTML
HTTP response
Figure 2. Django Model-View-Template architecture
class OR_Business_Process2(models.Model):
OR_business_process2_ID = models.CharField(max_length=2, unique = True,
verbose_name = "Business Process Code")
OR_business_process_ID = models.ForeignKey(OR_Business_Process, on_delete=
models.DO_NOTHING, verbose_name = "Business Line Code")
OR_business_process2_name = models.CharField(max_length=50,
verbose_name = "Business Process Name")
OR_business_process2_description = models.TextField(max_length=500,
verbose_name = "Business Process Description")
class Meta:
ordering = ["OR_business_process2_ID"]
verbose_name = "Business Process"
verbose_name_plural = "Business Processes"
def __str__(self):
"""String for representing the OR_Business_Process2 object"""
return ’%s) %s’ % (self.OR_business_process2_ID,
self.OR_business_process2_name)
Figure 3. Django model class for business process classifier
An example of model for business process (level 2) classifier is given in Fig. 3.
The view component of Django executes three main tasks: it accepts HTTP requests
from users, applies business logic from Django models, and provides HTTP responses to
users’ requests.
Django has a powerful template engine and its own markup language with many
tools. Templates in Django are HTML files used to present data.
Django has built-in object-relational mapping (ORM) that helps developers interact
with databases. ORM is a mechanism that automatically transfers data stored in
application database into objects used in application code [13].
Shorokhov S. G., Khaptakhanova V. V. 145
Django ORM speeds up web application development and helps developers build
working prototypes in reduced time. Developers may not know the details of SQL
implementation for a specific database to manipulate data.
The Django framework can be used for creating various applications, including client
relationship management (CRM) systems, communication platforms, booking engines,
machine learning systems and many other applications [14, 15].
Due to advantages of Django architecture operational risk event database application
can be rapidly implemented in Django web framework.
4. Value-at-Risk and Expected Shortfall Calculation
Operational risk may be measured using various approaches and risk metrics, for
instance, Basel II approaches (basic indicator approach, standardized approach, advanced
measurement approaches), expected loss and unexpected loss, etc [16].
Value-at-Risk (VaR) and Expected Shortfall (ES) are the most widely used financial
risk metrics [17, 18]. In market risk management Value-at-Risk is the worst loss of asset
portfolio over a given time interval that will not be exceeded with a given confidence
level. Expected Shortfall at a given confidence level 𝛼 is the expected loss of asset
portfolio in the worst 1 − 𝛼 percent of losses.
Value-at-Risk and Expected Shortfall can be calculated for operational risk in a
number of ways [19]. If event database with losses incurred due to operational risk
events is available for certain time period, then the calculation of VaR and ES may be
based on data in risk event database [20].
Assume that operational risk loss 𝐿 per day is distributed discretely, i.e. operational
risk loss per day 𝐿 for the period of 𝑛 days takes some values 𝑙1 , 𝑙2 , ..., 𝑙𝑛 with equal
1
probability 𝑝 = 𝑛 , i.e.
1
P [𝐿 = 𝑙𝑖 ] = 𝑝 = , 𝑖 = 1, 𝑛.
𝑛
Suppose for simplicity that 𝑙𝑖 ̸= 𝑙𝑗 , 𝑖 ̸= 𝑗 and day losses 𝑙𝑖 are sorted in ascending
order:
0 < 𝑙1 < 𝑙2 < ... < 𝑙𝑛 .
Due to property P [𝐿 6 𝑙] = 1 − P [𝐿 > 𝑙] the definition of 𝑉 𝑎𝑅 (𝛼) takes the form
𝑉 𝑎𝑅 (𝛼) = inf{𝑙 ∈ R : P [𝐿 6 𝑙] > 𝛼} = inf{𝑙 ∈ R : P [𝐿 > 𝑙] 6 1 − 𝛼}.
If 𝑙 ∈ [𝑙𝑘 , 𝑙𝑘+1 ) for some index 𝑘 between 1 and 𝑛 (1 6 𝑘 < 𝑛), then
𝑛
∑︁ 𝑛−𝑘
P [𝐿 > 𝑙] = P [𝐿 = 𝑙𝑖 ] = .
𝑖=𝑘+1
𝑛
If for some 𝑘 the confidence level 𝛼 is equal to
𝑘
𝛼= , 1 6 𝑘 < 𝑛, 𝑘 ∈ N,
𝑛
then for any 𝑙 ∈ R inequality
𝑘 𝑛−𝑘
P [𝐿 > 𝑙] 6 1 − 𝛼 = 1 − =
𝑛 𝑛
is equivalent to inequality 𝑙 > 𝑙𝑘 , therefore
𝑉 𝑎𝑅 (𝛼) = inf{𝑙 ∈ R : P [𝐿 > 𝑙] 6 1 − 𝛼} = inf{𝑙 ∈ R : 𝑙 > 𝑙𝑘 } = 𝑙𝑘 .
146 ITTMM—2019
Algorithm: calculation of operational 𝑉 𝑎𝑅 and 𝐸𝑆 on a given event database.
Input:
1. start date 𝑡1 and end date 𝑡2 of time interval [𝑡1 , 𝑡2 ]
2. event database with operational loss data for time interval [𝑡1 , 𝑡2 ]
3. confidence level 𝛼 ∈ (0, 1)
Output: daily operational 𝑉 𝑎𝑅 and 𝐸𝑆 with confidence level 𝛼
Method:
(1) calculate total number of days in time interval [𝑡1 , 𝑡2 ]: 𝑁 = 𝑡2 −𝑡 1 +1
365
(2) for each day of time interval [𝑡1 , 𝑡2 ] sum operational losses incurred on that
day
(3) sort the losses received on previous step in descending order and store the
losses in array 𝐴 (days with high losses at the beginning of array, array
index starts from 1)
𝑘
(4) if (confidence level 𝛼 is equal to 𝑁 for some integer 𝑘 between 1 and 𝑁 ) {
(5) 𝑉 𝑎𝑅 (𝛼) = 𝐴 [𝑁 − 𝑘 + 1]
−𝑘
𝐸𝑆 (𝛼) = 𝑁 1−𝑘 𝑁
∑︀
(6) 𝑖=1 𝐴 [𝑖]
(7) }
𝑘
(8) else if ( 𝑛 < 𝛼 < 𝑘+1
𝑛
for some integer 𝑘 between 1 and 𝑁 − 1) {
(9) 𝑉 𝑎𝑅 (𝛼) = 𝐴 [𝑁 −
(︁ ∑︀𝑘] (︁ )︁ )︁
𝑁 −𝑘−1
(10) 𝐸𝑆 (𝛼) = 1−𝛼1 1
𝑁 𝑖=1 𝐴 [𝑖] + 𝑘+1 𝑛
− 𝛼 𝐴 [𝑁 − 𝑘]
(11) }
Figure 4. Algorithm for operational 𝑉 𝑎𝑅 and 𝐸𝑆 calculation
If for some 𝑘 the confidence level 𝛼 satisfies the double inequality
𝑘 𝑘+1
<𝛼< , 1 6 𝑘 < 𝑛, 𝑘 ∈ N,
𝑛 𝑛
then for any 𝑙 ∈ [𝑙𝑘 , 𝑙𝑘+1 )
𝑛−𝑘
P [𝐿 > 𝑙] = > 1 − 𝛼,
𝑛
hence P [𝐿 > 𝑙] < 1 − 𝛼 only when 𝑙 > 𝑙𝑘+1 and
𝑉 𝑎𝑅 (𝛼) = inf{𝑙 ∈ R : 𝑙 > 𝑙𝑘+1 } = 𝑙𝑘+1 .
So finally under made assumptions
⎧
𝑘
𝑙𝑘 , when 𝛼 = 𝑛 ,
⎨
𝑉 𝑎𝑅 (𝛼) =
⎩𝑙
𝑘+1 ,
𝑘
when 𝑛 < 𝛼 < 𝑘+1
𝑛
,
where 1 6 𝑘 < 𝑛, 𝑘 ∈ N.
For the calculation of Expected Shortfall for given confidence level 𝛼 we have to
determine worst 1 − 𝛼 percent of losses and calculate average value for these losses.
Shorokhov S. G., Khaptakhanova V. V. 147
For 𝛼 = 𝑛 𝑘
, 1 6 𝑘 < 𝑛, 𝑘 ∈ N we have 1 − 𝛼 = 𝑛−𝑘
𝑛
, so worst 1 − 𝛼 percent of losses
are the last 𝑛 − 𝑘 losses 𝑙𝑘+1 , ..., 𝑙𝑛 and
∑︀𝑛 𝑛
𝑖=𝑘+1 𝑝 𝑙𝑖 1 ∑︁
𝐸𝑆 (𝛼) = E [𝐿 | 𝐿 > 𝑙𝑘 ] = ∑︀𝑛 = 𝑙𝑖 .
𝑖=𝑘+1 𝑝 𝑛 − 𝑘 𝑖=𝑘+1
𝑘
< 𝛼 < 𝑘+1 , 1 6 𝑘 < 𝑛, 𝑘 ∈ N we have 1 − 𝛼 = 𝑝˜ + 𝑛−𝑘−1
(︀ 1 )︀
For 𝑛 𝑛 𝑛
for some 𝑝˜ ∈ 0, 𝑛 ,
𝑝˜ = 1 − 𝛼 − 𝑛−𝑘
𝑛
, so worst 1 − 𝛼 percent of losses are the last 𝑛 − 𝑘 − 1 losses 𝑙𝑘+2 , ..., 𝑙 𝑛
and a part of the loss 𝑙𝑘+1 and
⎡ ⎤
𝑝˜ 𝑙𝑘+1 + 𝑛 𝑛
∑︀
𝑖=𝑘+2 𝑝 𝑙𝑖 1 ⎣ 1 ∑︁ ⎦
𝐸𝑆 (𝛼) = = 𝑝˜ 𝑙𝑘+1 + 𝑙𝑖
𝑝˜ + 𝑛
∑︀
𝑖=𝑘+2 𝑝 1−𝛼 𝑛 𝑖=𝑘+2
So under made assumptions
⎧
⎨ 1 ∑︀𝑛 𝑘
𝑖=𝑘+1 𝑙𝑖 , when 𝛼 = 𝑛 ,
𝐸𝑆 (𝛼) = 𝑛−𝑘 [︁ ]︁
1 ∑︀𝑛
⎩ 1 𝑝˜ 𝑙
1−𝛼 𝑘+1 + 𝑛 𝑖=𝑘+2 𝑙𝑖 ,
𝑘
when 𝑛 < 𝛼 < 𝑘+1
𝑛
,
where 1 6 𝑘 < 𝑛, 𝑘 ∈ N.
From received formulae for 𝑉 𝑎𝑅 (𝛼) and 𝐸𝑆 (𝛼) we receive the following algorithm
(Fig. 4) for the calculation of Value-at-Risk and Expected Shortfall under made assump-
tions.
Algorithm on Fig. 4 can be easily implemented in Python, using methods aggregate
and annotate of Django database API.
5. Conclusion
We presented some design and implementation considerations for a web based appli-
cation for operational loss collection. Using Django web framework significantly speeds
up and facilitates application development. Algorithm for calculation of operational
Value-at-Risk and Expected Shortfall using data in risk event database is also developed.
Acknowledgments
The publication has been prepared with the support of the «RUDN University
Program 5-100» (risk event database design and implementation). The reported study
was funded by RFBR, grant No 16-08-00558 (VaR and ES algorithm development).
References
1. Bank of Russia, On the requirements to the risk and capital management system
of credit institution and banking group (ordinance no. 3624-u of april 15, 2015),
Vestnik Banka Rossii (51) (2015) 15–35.
2. A. Aloqab, F. Alobaidi, B. Raweh, Operational risk management in financial
institutions: An Overview, Business and Economic Research 8 (2) (2018) 11. doi:
10.5296/ber.v8i2.12681.
3. S. Pakhchanyan, Operational risk management in financial institutions: A Literature
Review, International Journal of Financial Studies 4 (4) (2016) 20. doi:10.3390/
ijfs4040020.
148 ITTMM—2019
4. BCBS, Basel III: Finalising post-crisis reforms, Publication, Basel Committee on
Banking Supervision, Basel (dec 2017).
URL http://www.bis.org/bcbs/publ/d424.pdf
5. Bank of Russia, On the requirements to the operational risk management system of
credit institution and banking group (draft regulation), Draft regulation, Moscow
(September 2018).
URL http://www.cbr.ru/StaticHtml/File/41186/180918-41_1.pdf
6. L. Wei, J. Li, X. Zhu, Operational loss data collection: A Literature Review, Annals
of Data Science 5 (3) (2018) 313–337. doi:10.1007/s40745-018-0139-2.
7. BCBS, The Quantitative Impact Study for Operational Risk: Overview of Individual
Loss Data and Lessons Learned, Working paper, Basel Committee on Banking
Supervision, Basel (jan 2002).
URL https://www.bis.org/bcbs/qis/qisopriskresponse.pdf
8. D. Beazley, G. Van Rossum, Python: Essential Reference, New Riders Publishing,
1999.
9. A. Holovaty, J. Kaplan-Moss, The Definitive Guide to Django, Apress, 2009. doi:
10.1007/978-1-4302-1937-8.
10. A. Mével, T. Guéguen, M. Wolczko, Smalltalk-80, Macmillan Education UK, 1987.
doi:10.1007/978-1-349-09653-4.
11. G. E. Krasner, S. T. Pope, A cookbook for using the model-view controller user
interface paradigm in smalltalk-80, Journal of Object-Oriented Programming 1 (3)
(1988) 26–49.
URL http://dl.acm.org/citation.cfm?id=50757.50759
12. M. Alchin, Pro Django, Apress, 2013. doi:10.1007/978-1-4302-5810-0.
13. M. Lorenz., G. Hesse., J. Rudolph., Object-relational mapping revised - a guideline
review and consolidation, in: Proceedings of the 11th International Joint Conference
on Software Technologies - Volume 1: ICSOFT-EA, (ICSOFT 2016), INSTICC,
SciTePress, 2016, pp. 157–168. doi:10.5220/0005974201570168.
14. K. Cao, F. Wang, J. G. Liu, Study and implementation of pm2.5 data download
service based on python, Applied Mechanics and Materials 411-414 (2013) 555–558.
doi:10.4028/www.scientific.net/amm.411-414.555.
15. Z. Li, Design and implementation of the software testing management system based
on django, Applied Mechanics and Materials 525 (2014) 707–710. doi:10.4028/www.
scientific.net/amm.525.707.
16. R. Coleman, A VaR too far? The pricing of operational risk, Journal of Financial
Transformation 28 (2010) 123–129.
URL https://EconPapers.repec.org/RePEc:ris:jofitr:1420
17. P. Jorion, Value at Risk: The New Benchmark for Managing Financial Risk, 3rd
Edition, Mcgraw-Hill Professional, 2006.
18. C. Acerbi, D. Tasche, Expected shortfall: A Natural Coherent Alternative to Value
at Risk, Economic Notes 31 (2) (2002) 379–388. doi:10.1111/1468-0300.00091.
19. A. S. Chernobai, S. T. Rachev, F. J. Fabozzi, Operational Risk: A Guide to Basel
II Capital Requirements, Models, and Analysis, John Wiley & Sons, Inc., 2012.
doi:10.1002/9781119201922.
20. J. Esterhuysen, P. Styger, G. W. V. Vuuren, Calculating operational value-at-risk
(opvar) in a retail bank, South African Journal of Economic and Management
Sciences 11 (1) (2012) 1–16. doi:10.4102/sajems.v11i1.374.