The project completion certificate has been uploaded HERE.
This was my Final Year Project for Bachelor of Engineering(Computers). It was an outhouse project for IDBI Intech, the IT arm of the nationalized bank Industrial Development Bank Of India.
Till date , this has been the most complex and informative project I have worked with.
IMPLEMENTATION OF RBI GUIDELINES ON BASEL 2 (Automated Data Extraction)
As per RBIs direction, compliance with Basel II norms is mandatory for the banks. This paper proposes an IT solution that could be offered by IT companies to help banks, adhere to Basel II norms.
Critical Success Factors for the banks are:
- 1) Risk Identification
- 2) Quantitative Risk Measurement
- 3) Risk Mitigation
- 4) Minimum Capital Allocation
But banks find compliance to Basel II norms in the above areas difficult, due to
increasing number of customer base of the banks, absence of effective risk management solution and absence of system interfaces between the existing stand alone applications of the banks.
Midsized IT (Information Technology) companies could offer an End to End Risk
Management Solution to the banks which will identify measure, mitigate credit risk,
market risk and operational risk and also suggest real time capital allocation for each of
the above risks. The element of the proposed solution consists of following applications:
- 1. CRM (Customer Relationship Management) - application
- 2. BI (Business Intelligence) based Banking Dashboard
- 3. Risk Measurement (core) Application
- 4. Risk Mitigation Application
In a Nut Shell:
- 1. Help banks to satisfy the three pillars of Basel II viz.
- a) Minimum capital requirement
- b) Supervisory review process
- c) Market discipline requirements
- 2. Real time monitoring of clients credit position (including credit score)
- 3. Real time monitoring of banks financial position with respect to equity/derivative market.
This is how it looks like!
Data from different sources, are extracted and populated into data repository, which
serves as input engine for risk measurement module. The following table represents the details.
There are complex forumlas which are used to measure the varioud types of risks(Market risk, Credit risk, Operational Risk) .
THE UML DIAGRAM FOR THIS PROJECT : (Only the essential features have been represented. The complexity of the project renders several components beyond the scope of this document)
Context Level Diagram:
Level 0 Data Flow Diagram:
Use Case Diagram:
The project was very vast and could not have been completed in a short duration of time. Hence, the objective was to complete a small module of the project. The module given to my group was automated data
extraction using GUI and this was successfully completed.
I was activeley involved in the development phase and testing phase.
- The front end was constructed using JAVA SWING. Classes like JFrames and JPanels were used to put into place a GUI.
- The screen shots for the front end can be viewed HERE.
- There were options to execute various queries depending on the user choice. A GUI enabled the user to choose which queries were to be executed. The output of the query was written to a notepad file which was
named according to the type of extractions and date of extraction.
- A screenshot of the extracted file can be viewed HERE. These files were later uploaded automatically to the CAM computation section to perform complex calculations and derive important results.
- A screen shot of the CAM computation has been put up HERE. This process was done manually earlier(using toad to execute queries). And my group automated it.
- The sessions of every user were written to a log file which stored the details of every extraction like which query was executed, who executed it, time of execution and so on.
- Additional checks were put in to eliminate SQL injection attack.
- There were constraints in force to ensure integrity and prevent users from entering user dubious values in the form fields. (eg. passwords with a 'space' and so on)
- A forgot password feature allowed users to reclaim their account access in case they lost their password. For this purpose , a secret question had to be answered. The answer to the secret question had to be inputted at the time of account creation. In case the user forgot his secret answer, he/she had to approach the System Admin.
The back end consisted of 2 databases:
A JDBC connection was used to establish a connection with both the databases.
- 1)My SQL Database:
- This database was created by me for the puropse of the project.
- This database was used to store various users who would be accessing the system. There were two classes of users: Admins and Normal users. Admins had special privileges, in that they could create new users
and reset the passwords of existing users. They could also perform data extraction and execute queries.
- The database consisted of tables which stored the login information, flags and type of user a particular user was. All the login information was encrypted using AES algorithm to prevent any one from viewing the passwords or login information.
- Normal Users could execute queries and perform data extractions and execute other queries. Sessions were logged to note which user ran which query.
- 2)Oracle Databases
- These were complexes databases. They consisted of all the tables used by the bank to store account holder's information. We did not have access to them apart from the purpose of running queries and to pull out data.
Here is a sample query from the project:
A late requirement by the project leader stated that the project had to be made available via a web browser rather by installing a client on every machine. This predicament was solved by installing
a software called Ajax Swing on the server which produced HTML pages on the fly from the jar client. Do have a look at the link to know more about this wonderful tool.