1

Data Mining and Analytics approach to brokerage audit

Background

SKP had to perform brokerage audit for one of the largest mutual fund players having its distribution

network all over India. Mutual Funds allows for portfolio diversification and relative risk aversion

through collection of funds from the households and investment of the same in Equity, Debt

Markets and Gold Exchange Traded Fund (ETF) schemes. For mobilization of funds from investors,

they take help of intermediaries and largely depend on services provided by intermediaries for

collection of funds, advisory services and other related processes. As compensation for these

services, the company pays commission to intermediaries in the following forms:

Upfront Commission and additional Upfront Commission

Trail Commission

Additional Trail Commission

Incentives and/or Target based incentive

Reward points

The Mutual Fund appoints a Registrar and Transfer Agent (R&TA) for transaction processing activities

inclusive of brokerage payment to the intermediaries. Periodically Mutual Fund communicates

brokerage structures to R&TA and this covers details of scheme, brokerage rates, brokerage criteria,

period covered, threshold for any particular broker, etc. The brokerage rate also differs based on the

ability of the intermediary to generate funds. R&TA process starts with accounting of transactions of

purchase, sale, switch-in and switch-out, receiving of brokerage structure from the company,

processing the brokerage amount payable to intermediaries and continues right up to dispatch of

brokerage warrants to brokers either directly or though the Mutual Fund.

The Mutual Fund makes payment to intermediaries based on payment files provided by R&TA;

hence accuracy of data is the prime requirement. In order to ensure that the instructions issued by

way of brokerage structure have been properly followed, an independent audit was conducted with

specific objective to check the accuracy, timely settlement and effective regulatory compliance. We

were appointed to conduct the verification and submit our report on brokerage payment processed

by R&TA.

Challenges Faced

1. Enormous Volume of Data: The Mutual Fund has 50,00,000 folios (number of investors) with voluminous transactions of Purchase, Sale, Switch-in and Switch-out. Majority of its transactions are carried out though its intermediaries. Total number of intermediaries itself were 18,501 for Group and 39,961 for other intermediaries. Traditional approaches using tools like Excel or Access fail to work with data this large. Excel can only handle up to a million rows of data, whereas Access can only work with about 2GB of data.

2. Data Format: The Carved out data from R&TA database was in the Dbase format having extension of .DBF. The size of each file was above 1 GB and total size of data was around 20 GB covering approximately 90 Million transactions. Adding a layer of complexity was the fact that not all data was in the same format as their system software underwent changes from time to time. Further, some data was missing some columns; some had additional ones, etc.

2

3. Complex Data Correlation: With approx. 200 brokerage structures and 90 Million transactions, the main complexity was the dynamic nature of structures. The structure changes every fortnight depending on broker, percentage and period.

SKP s Approach and Technology used

Routine audits involve sifting through files/spread-sheets matching/tallying figures. For large data

sets, it becomes almost impossible (given time and money constraints) to audit every transaction or

record. The traditional approach is to fall back to random samplings of data and checking only those.

Now for extremely large systems having millions of records held within backend databases, this

becomes an even more difficult task.

Data mining means sorting through huge data sets looking to identify or discover patterns and

anomalies. Data analytics focuses on inference and deriving conclusions on what is already known.

Using both to combine the known and the unknown completes the picture.

When working with electronic data in huge volumes across diverse systems such as SAP or custom

built software and databases such as MSSQL or Oracle, there is no choice but to audit this

information electronically through custom SQL queries, programs and scripts. Every system is

different and every database structure is distinct, thus requiring database technical experts to do the

job. We work with a copy of almost the entire database at times where we run our own queries to

aggregate and mine the data.

This job required a full blown Database setup such as MS-SQL, MySQL or Oracle. Manual sorting,

filtering and sifting through the data was infeasible even if excel could handle the data size.

We setup a MySQL server and imported all the data into it. Then using Sequential Query Language

(SQL), it was possible to get the desired analysis performed. SQL is like a computer programming

language but only for communicating with databases. To accomplish our task, we required a good

powerful desktop workstation and several software to run and manage the database server, to

import data, to run queries and to export the results.

3

Figure 1 Basic steps of the technical process involved

SKP Value

Our approach has helped us detect anomalies and irregular activity more effectively. Moreover, we

are doing a comprehensive audit checking every transaction ensuring completeness and accuracy.

With custom written SQL queries, it has been possible to get the data of brokerage payment broken

down (analysed) by type (Upfront or Trail), % of brokerage rates applied to the respective brokers,

period covered for brokerage and total amount of brokerage paid. The advantages are tabulated

below.

1. The query can be run on the entire data base with minimum time required to produce the results.

2. Entire data can be accounted for as there is no sampling. For instance, as per SEBI regulation, broker is not authorised to receive brokerage on its own investment. With the support of SQL we could easily find out all such types of own investment where commission was erroneously claimed by the broker.

3. Even if the data were small, the complexity of brokerage structures would still have taken a long time to verify through traditional methods. As per brokerage structure, different brokerage rates were applicable to different categories of brokers and for different periods. SQL greatly helps in reducing the per broker type computations as we can define complex queries and then apply to all data simultaneously.

4. Duplicate transaction processing is easily monitored.

Data Analytics - Query DB using SQL queries to verify payments, derive statistics & generate reports

Broker-wise Scheme-

wise Percentage-

wise Commission

-wise Period-wise

Consolidate data into Tables & Views

Import to MySQL Database

Convert data to importable format

Data Exported from R&TA Database