AN EXECUTIVE INFORMATION SYSTEM FOR SECURITIES

BROKER’S RISK MANAGEMENT WITH DATA

WAREHOUSING AND OLAP

Yung-Hsin Wang, Shing-Han Li

Department of Infrmation Management, Tatung University, Taipei, Taiwan

Kuo-Lung Sun

Oriental Securities Corporation, Taipei, Taiwan

Keywords: Executive Information System, Data Warehouse, OLAP, Securities Brokers, Margin Purchase, Short Sale.

Abstract: With the open domestic financial market, the targets of investment and money management are toward

diversity. The competition from internationalization makes the stock market no more flourishing as usual.

The risk of margin trading becomes important information that securities firms try to analyze and get

controlled. According to current regulations and working process, this study constructs an executive

information system with the application of data warehouse and online analytical processing (OLAP) to help

securities brokers make decisions in the operation of risk management for margin purchase and short sale of

securities. The result solves the problem that managers of margin trading usually face when using traditional

account systems.

1 INTRODUCTION

In the past years, the securities industry in Taiwan

has developed vigorously. However, with the open

domestic financial market and internationalized

competition, the targets of investment and money

management are toward diversity, which makes the

stock market no more flourishing in recent years.

Due to price reduction to compete with others, the

profit of handling fee of the stock trading is not as

good as usual, and the margin trading becomes the

key business of security firms.

What is margin trading? According to Article 2

in “Regulations Governing Margin and Stock Loans

by Securities Firms” (

TSEC, 2006) stipulated by

Taiwan Stock Exchange Corporation (TSEC), the

term “margin loan” means that a securities firm

provides monetary financing to its customers; the

term “stock loan” means that a securities firm lends

securities to its customers. Article 16, in addition,

stipulates that when a securities firm dealing with

margin loan and short stock loan extends monetary

financing to a customer, it shall collect the margin

for margin purchase based on the ratio prescribed by

the SFC (Securities & Futures Commission), and all

the securities procured through margin purchase

shall be used as collateral; when extending stock

loan to a customer, the securities firm shall collect a

margin for short sale in accordance with the

percentage prescribed by SFC, and the proceeds

from the short selling shall be used as collateral.

However, the stock market is to vary at any time.

To ensure the creditor’s right of security firm,

Article 17 regulates that “A securities firm dealing

with margin loan and stock loan shall calculate the

ratio of the value of collateral in each credit account

to the customer’s debt on daily basis. If the ratio is

lower than the required ratio, the securities firm shall

immediately notify the customer to make up the

difference within a prescribed time limit.” The

above-mentioned ratio, called “maintenance ratio,”

is one of the most important indexes to measure the

loan state of the customers for securities firms.

Securities brokers usually will face a risk that

only when the collateral maintenance ratio of the

customers is lower than 120%, the credit extension

organization can send out notification of payment

(margin call), and in the third day the target stocks

can be disposed of. By calculating with the current

7% greatest price fluctuation per day, if that finance

(stock) rises or falls everyday, the surplus money

508

Wang Y., Li S. and Sun K. (2007).

AN EXECUTIVE INFORMATION SYSTEM FOR SECURITIES BROKER’S RISK MANAGEMENT WITH DATA WAREHOUSING AND OLAP.

In Proceedings of the Ninth International Conference on Enterpr ise Information Systems - DISI, pages 508-511

DOI: 10.5220/0002375705080511

 SciTePress

will be unable to compensate creditor’s right. On the

other hand, even the customer maintenance ratio for

a stock looks good and everything seems normal, yet

there still is a hidden worry of higher centralization.

At this moment, if that stock falls suddenly and

rapidly, it might cause a severe loss to the customers

and affect the creditor’s right of security dealer.

Most current securities brokers count on their

account systems to present statistic data after closing

quotation which has the following shortcomings:

 In the aspect of data integration: Users have to

check several reports (forms) in order to have an

overall understanding of current status.

 In the aspect of portability: It is inconvenient for

the manager of margin trading to carry the forms

everywhere to discuss with others or report to a

superior when an anomaly is found.

 To alert the anomaly of key indexes: The closing

form is usually used to make account check and

is hard to alert for the anomaly.

 To judge the trend of some key indexes: The

margin trading manager cannot know the

previous data simply from today’s account

balance (need comparisons to historical data).

Thus, our objective of this study is to design and

implement an Executive Information System (EIS)

for managing the risk of margin purchase and short

sale of securities. We will first construct a data

warehouse for margin trading of securities brokers,

and then choose KPIs (Key Performance Indicators)

and analysis dimensions according to practical

requirements for the data cube to be analyzed using

OLAP (Online Analytical Processing) technique

(Chaudhuri and Dayal, 1997; Inmon, 1996; Turban

and Aronson, 2001). Note that MDX (Spofford,

2001) is used in this study to inquire cube data.

2 SYSTEM DESIGN

2.1 Data Model Setup

Three dimensions are required as shown in Table 1:

 Customer Dimension: The entire company data

is used to query situation of all customers to

understand current quality of margin loan. Since

customers and the trading volume are different in

different locations, with the branch level we can

compare business performance of each branch.

For the level of customer account, the analysis

target is a single customer.

Table 1: Structures for the dimension hierarchy.

 Stock Dimension: By matching with other

dimensions, the level of all shares can be used to

inquire the entire situation of all stocks. Stocks

of the same class might not have the same

operating status, yet their natures might be the

same. The level of securities code is the

individual stock. With the account number, it can

reveal data for balance of stock loan and margin

loan of the stock held by that customer.

 Time Dimension: It is necessary for analyzing

time variation of the balance of margin loan.

Since the data processed in our system is

balance, “day” is most popularly used. The other

two are used to calculate mean value.

2.2 Data Warehouse Establishment

In this study, we need one Fact Table and three

Dimension Tables to finish data cube for the OLAP.

Note that according to this study’s measurement

indexes shown in Table 2, the measurements for the

Fact Table will be defined.

 Fact Table: Based on the Balance of Margin and

Stock Loan file of the customers in the account

system, it transforms directly the branch code,

customer’s account, security code, balance of

margin purchase, balance of margin loan,

balance of short selling, balance of collateral and

balance of margin of stock loan, and adds some

fields such as that of shareholding ratio and

market share, etc.

 Stock Dimension Table: Based on the main

securities file, it transforms the security code,

stock name, security class stocks and market

type, and imports the field of Balance of Margin

Loan from the external data form.

 Customer Dimension Table: Based on the main

file of customers, it transfers directly the

company’s code, customer’s account, customer’s

name and executive code.

 Time Dimension Table: To avoid the error of

placing the date information of non-trading day,

this table will use system date as the format for

data transformation.

Dimension Structure Details

Customer Star Schemas Entire Company ->

Branch -> Account No.

Stock Star Schemas All Shares -> Securities

Class Stocks ->

Securities Code

Time Star Schemas Year -> Month -> Day

AN EXECUTIVE INFORMATION SYSTEM FOR SECURITIES BROKER’S RISK MANAGEMENT WITH DATA

WAREHOUSING AND OLAP

509

Table 2: The measurement and description for the indexes.

Index Description Measurement

Balance of Margin

Loan

The amount lent by a company (i.e.,

customer borrows) and is not paid off

Has different criteria according to the specification of

each stock; usually analyzed with other measurement.

Maintenance Ratio

The index for measuring the credit

right of customer

The higher the ratio is, the better the result is.

The warning value is 120% of that currently regulated.

Customer

Shareholding

Concentration

Ratio for the customer shareholding

and the total balance of margin loan in

specific stock

The lower the ratio is, the better the result is.

The warning value is 50%.

Company Margin

Loan Concentration

Ratio for the shares in margin loan

and that in market for a company

The lower the ratio is, the better the result is.

The warning value is 10%.

Margin and Stock

Loan Ratio

The short sale utilization rate of the

stock for margin loan

To look after both risk and circulation, the ratio of listed

stock is better not over 70% and GTSM not over 50%.

3 SYSTEM DEVELOPMENT AND

EVALUATION

This section discusses the implementation and

evaluation for the EIS system of credit transaction

risk management. The data warehouse is complete

through DTS (Data Transformation Service) of SQL

Server 2000. Since the Balance data of Stock Loan

and Margin Loan during trading time will distort

depends on the operation of specifying trading or

day-trading, the system is designed to close price

everyday. The transaction data will be transformed

automatically after confirming the account is settled.

The first phase of DTS is to copy the original

extracted data to the data warehouse host. Since data

in the account host is specifically designed

according to account requirement that some data

tables and presentation ways may not fit the data

warehouse, part of the data needs to be transformed

while some have to be pruned and organized first.

The second phase is to merge the extracted data into

data warehouse; the mission is to transform the

copied data into data warehouse. Finally, the third

phase then processes dimensions and data tables.

Since source data table has been updated, the Cube

cannot operate normally before such processing.

3.1 System Functions and Interface

The system starts with a dashboard and has four

function groups. Each time entering the system it

shows the margin loan balance chart of current

month. We shall explain each function and their sub-

functions in what follows (due to space limits this

paper cannot demonstrate all the screenshots; only a

couple of figures are presented for illustration):

 KPIs: There are three sub-functions:

1. Overall Index: Shows branches whose

Balance of Margin Loan, Balance of Stock

Loan and Balance of Margin Loan surpass the

setting threshold (see Figure 1).

2. Single Securities Index: Market percentage of

margin purchase stocks, Margin and Stock

Loan Ratio and Top 5 Margin Loan Net Buy

& Sell (as shown in Figure 2).

3. Self Selecting Data Query: Using the function

design provided by Microsoft PivotTables, the

manager can choose freely the operating

interface of dimension and measurement to

query.

 The rank of top thirty shares for each index: It

lists the sorted shares with four sub-functions:

1. Balance of Margin Loan Descending: allows

users to drill down to understand and check

the stock with higher finance balance.

2. Close Price Descending: allows users to watch

out the risk of high-price stock.

3. Close Price Ascending: allows users to watch

out the risk of garbage stock.

4. Maintenance Ratio Ascending: allows users to

understand and check the stock of lower

maintenance ratio.

 Comparison of Historical Data: It shows three

indexes for that day and the previous four

trading days to carry out dynamic analysis.

1. Balance of Margin Loan (Margin Purchase)

2. Balance of Stock Loan (Short Sale)

3. Maintenance ratio

 Query for Specific Data: Users can query for the

specific data such as the following:

1. Shareholding Concentration

2. Balance Check for Single Share

3. Balance Check for Individual Customer

ICEIS 2007 - International Conference on Enterprise Information Systems

510

Figure 1: Overall index of margin loan balance KPI.

Figure 2: Individual stock index of balance KPIs.

3.2 System Test and Evaluation

The system being developed was tested and

evaluated by margin trading and IT managers, and is

confirmed of the following advantages:

 In the aspect of data integration: With OLAP and

drill-down, the system can check the aggregated

and detailed information at the same time. Take

maintenance ratio control as an example. When

the manager of credit risk management finds out

abnormal condition appearing on the total

maintenance ratio of the company at that trading

day, he can use the system to investigate

maintenance ratio data for every branch, even a

single customer, to figure out the problem

source.

 In the aspect of system portability: With the

web-based interface, users can use browser to

inquire information via the Intranet without

additional installation of other software.

 In the aspect of key index alert: To assist the

manager to easily find the anomaly, alert

threshold is set for some KPIs, e.g., the

maintenance ratio for one stock or customer

which is less than 120% will be highlighted.

 In the aspect of historical data comparison: The

system will reveal the historical data of the

previous trading day and the discrepancy against

three measurement indexes, Balance of Margin

Loan, Balance of Stock Loan and Maintenance

Ratio. This comparison data is critical to find the

trend of those KPIs.

4 CONCLUSIONS

This study has achieved our goal to design and

implement an EIS for controlling and managing the

risk of margin purchase and short sale of securities.

A data warehouse for margin trading of securities

brokers to integrate their transaction accounts has

been developed and used for efficient analysis of

predefined KPIs using OLAP technique. One future

work direction is to combine the customer

transaction log and balance data to study the model

of customer transaction. The other direction could be

the consideration of real-time risk control.

ACKNOWLEDGEMENTS

Portions of this work have been supported by the

National Science Council, Taiwan, under grant no.

NSC96-2416-H-036-001.

REFERENCES

Chaudhuri, S., Dayal, U., 1997. An Overview of Data

Warehousing and OLAP Technology. SIGMOD

Record, 26 (1), 65-74.

Inmon, W.H., 1996. Building the Data Warehouse, John

Wiley & Sons. New York.

Spofford, G., 2001. MDX Solutions: With Microsoft SQL

Server Analysis Services, Wiley. New York.

TSEC, 2006. Regulations Governing Margin and Stock

Loans by Securities Firms. Retrieved from

http://eng.selaw.com.tw/ FLAWDAT0202.asp.

Turban, E., Aronson, J.E., 2001. Decision Support Systems

and Intelligent Systems, Prentice Hall. NJ, 6th edition.

AN EXECUTIVE INFORMATION SYSTEM FOR SECURITIES BROKER’S RISK MANAGEMENT WITH DATA

WAREHOUSING AND OLAP

511