BI Financial Markets with Data Mining

I have been a little absent due to the preparation of my Master's thesis in Decision Support Systems (DSS or BI), and it’s with a great pleasure that I share with you the theme that I will investigate during next year. This research project will be focused in the behaviour of financial markets through the use of Data Mining techniques. It’ll combine the technology through the creation of ETL processes, a Data Mart and Data Mining techniques, with the financial markets knowledge.

And the reasons that led me to choose this theme, are without doubt, the fascination for the mysterious world of financial markets, the fascination by data mining techniques (that are for me the true intelligence of a BI solution) and the need to create a DSS that enable the understand the impact of movements in financial markets in the budget of each one of us
Many of us invest money in the stock market through mutual funds, pension savings plans, through the brokerage firms and others ... but do we know what they are doing with our money? Could we trust on institutions that are investing our money? I think it's time to be ourselves the owners of our money and define what we want to do with it.

We have to know the right moment to subscribe, transfer or redeem financial products offered by various financial institutions, or even whether we should invest ourselves (in a long term and in a diversified way, we have high probability of good profits) in financial markets or put the money from under our beds.

And despite the banking sector is the most regulated in the world, still occurs several big frauds that could put in risk the survival of a company or even put in risk our savings... after several scandals in Portugal, France (Société General), and United States of America, today a new one…

Bernard Madoff, the former Nasdaq chairman and founder and president of a New York firm that invested funds for wealthy individuals, hedge funds and other institutions, was charged with operating what he told employees was a long-running $50 billion Ponzi scheme in what may be one of the largest frauds in history. Bernard Madoff was arrested today at 8:30 a.m… and some of his clients are BBVA, o HSBC, o Royal Bank of Scotland, Credit Agricole and Banesto.

How can a former chairman at the world's largest stock market with 20,000 listed companies have entered into schemes of fraud? Are these same markets efficient? There is much speculation and a lot of confidential information (inside trading) that is used to always generate profits for the same persons in the markets...

Aren’t you tired of being a spectator? I will use my arms (Data Mining) to understand better the financial markets and react to it with logic and not following the panic or the movements of the masses…

I never invested directly in stocks because I respect the stock market, however I was investing for few years in mutual funds that I earned about 20%... but I had the happy idea of leaving the market when I heard on the radio some advertising to subscribe mutual funds... which led me to think that people were entering into the market by “marketing” rather than the real value of companies... this one lesson that I learned by myself…

SSIS - Updating rows from a FlatFile

Dear Friends,
Today I will describe a subset of an ETL I made and I think that could be useful for you and it will be useful for me if I receive your feedback.

The Problem
I have customer’s data (sent by an application by text files to a specific file system share), that must be imported to a SQL Server 2005 database, more precisely, to the customers database table.

All Fridays, all the data in the text file, must be extracted, transformed and loaded into the SQL Server 2005 database. After the extraction the file must be moved to another directory in the file system. (…\ProcessedFiles)

The text file is a fixed column type, and there is a column that describes the event to follow when ETL update the Customers table.
N (means new customer to insert)
A (means update existent customer)
D (means update/inactivate existent customer)

The Solution
The data will be extracted from the text file into a preTable (“preCustomer”) without any transformation/rules (to avoid risks). For each row imported to this preTable, I will have an extra column that is a foreign key to my audit table information. Next, I read all the records from the last extraction, transform and load into Customer table.

The end user, from an asp application, will be able to correct or ignore the redirected rows, and next time the ssis package executes, these errors will disappear. (Note: the warning and error rows will be stored in redirectCustomer table, until some action (correct or ignore) from the end user)

Logical Data Flow
I have several steps that could be useful to explain, but today I will focus only in the subset that I think is more interesting. Below you can see the logical data flow for the ssis package number 3, that transform and load the customer data from the preCustomer table into customer table.

SSIS Package
And finally the ssis package...
(Note that the schema of ETL_Customer is identically to the ETL_redirectCustomer, for this reason you can union all the two sources in the pipeline)

Operational Risk
Remember that in a business intelligence project 70% of the risks occurred in the ETL module. So pay attention to the ETL and always try to identify the existence of an operational risk. I use the ssis error logs only for the real unexpected errors, if you think that there is a probability of some task fail, don’t wait to see the error log, try to mitigate the error before it!!!

“Operational risk is defined as the risk of loss resulting from inadequate or failed internal processes, people and systems or from external events. This defi nition includes legal risk, but excludes strategic and reputational risk.”
The European Commission’s Directive 2006/48/EC9

Some tips for using the best practices

I selected some important tips that could be followed in order to implement a good ETL project, using the best pratices.

- Always comment you project

- Always create the control flow and dataflow logical model and always before implementation of ssis package.

- For all the documents created, insert each document inside a template with all the important information to share with all the teams involved in the project. It’s very important that when you look for a document, you could identify the subject and scope of the document fast. For example, for the template I created for Logical Data Flow, I added important information as project, package, dataflow, developer/team, date, version, purpose, reference…
- Use naming conventions for the tasks and components of the SSIS Packages

- Use SSIS log provider to catch error and warning events.

- Could be useful implement not only the SSIS log provider, but also some custom logs, using event handlers.

- Test you package! How?! Use SQL Data Generator to generate thousands of rows to your source data. This software is very good, for each column you can select all the possible values that you want to random. (These values could come from a database, text file…)

I hope this post could be useful for you, and I also hope that you can give some feedbak about this.

Next post I will try to write something about data mining!!!

Cheers!!

Operational Risk - Societe General Fraud!

Dear Friends,
I was planning to write this post last week, but today It’s the right time to do that! And the reason is the Societe General… do you know what these two words mean? I confess that I didn’t know it, but it’s only the second bigger French bank, and today it’s notice in all newspapers of the world.

The Societe General Bank was victim of a “massive” fraud from an employee. The bank said the fraud was based on simple transactions, but concealed by "sophisticated and varied techniques". The losses are estimated around 5 billion Euros!! Too much zeros, but I will try: 5.000.000.000€…uuuauuuuu… see all the news in the following link:
http://news.bbc.co.uk/2/hi/business/7206270.stm

How can be possible that only one employee makes several trades and no one else knows?! This men control the entire bank?! It’s very strange… but as Gilles Glicenstein BNP Paribas chief executive told in the above link, "there is still some information missing to understand what happened” and "the scale of the fraud is so large, there must be a complex explanation”
I initiate this post with this amazing, but real history, to write something about operational risk that I’m learning in my master BI degree. So, how these things could be possible? This Bank doesn’t have a Risk Management Unit?!

Operational risks are not only in a bank sector but in all activities, the risk is everywhere every time!! You have the risk to get fired! You have the risk of a car accident… Normally, and mainly in financial sector, all this operational losses, caused by people, systems, external factors or processes were hidden! And they were hidden because critical information could be dangerous for the health of a company. But I think that nowadays, this kind of information is not so dangerous for a company than the past, because the shareholders want TRANSPARENCY, and only with it, the shareholders put their money in the companies! And don’t forget that we are in a globalization, bad news in china can affect Europe, and bad news in Europe can affect China…
So, what is Operation Risk?!

“Operational Risk is defined as the risk of loss resulting from inadequate or failed internal processes, people and systems or from external events. This definition includes legal risk, but excludes strategic and reputational risk.” In http://www.bis.org/

Operational Risk nowadays it’s so important as credit risk and market risk, and for today I leave you with the picture below that I will describe with more detail in a next post:

So, This was the first post about RISK, I promise that I will be back tomorrow not only to write about RISK and write about my fantastic classes with the teacher Rui Gonçalves (SAS Portugal).
Cheers!!