9/18/2018 3:55:34 PM Goran Kliček, Data Management Consultant
MIFIR Reporting
When I started working in Comminus, I was assigned to the team whose main job was to support and implement new functionalities for clients using IMS (Investment Management System) and PMS (Pension Management System) custom applications. As a rookie in Comminus, but with experience in another fields of IT, I had to learn a lot of new terms in a short period of time just to be able to speak to my colleagues and clients. First major project that was assigned to me was to implement MiFIR Reporting for our clients. I thought: “Sounds great, but what is it?” and 2 seconds later I was googling “MiFIR”. After visiting web pages Google found, I was still confused, nothing made sense and I was asking myself “Now what?”. In this blog, I will present to you the MiFIR Reporting and how it was successfully implemented.

History of MiFIR

MiFIR, abbreviation of Markets in Financial Investments Regulation, is a part of revised Markets in Financial Instruments Directive (MiFID) that was brought by the European Commission in 2014. It has been implemented due to market development (growth of algorithmic trading), lessons learned from experience of how MiFID had operated so far (MiFID I) and to address issues identified because of the 2008 financial crisis. The Commission made a proposal on 20th of October 2011 for revisions to MiFID which were agreed in 2014. New MiFID was about to set four objectives:
  • strengthen investor protection
  • reduce the risks of a disorderly market
  • reduce systemic risks and
  • increase the efficiency of financial markets and reduce unnecessary costs for participants
The Directive and Regulation aim was to make financial markets more open, efficient, resilient and transparent. The MiFIR reporting obligations are in place from January 2018 and here are the subjects that need to “play by the rules of MiFIR”:
  • financial Counterparties (investment firms, investment manager, credit institutions...)
  • non-financial counterparties exceeding the clearing threshold
  • Market Operators, including any Trading Venues
  • Central Counterparties (CCPs) and persons with proprietary rights to benchmarks
  • Third-Country Entities (TCEs) providing investment services or activities within the EU
If you have read the chapter above and still don’t understand a thing what MiFIR reporting does or when or why to use it, no worries – I felt the same first time I read about it. To simplify MiFIR, lets imagine a scenario:

You were able to save some money and you want to invest in securities. You don’t want to bother by doing it by yourself and you let the professionals (investment firm) take care of buying and selling securities. You brought your money and you became an investor. You strongly believe that they will handle your money “with care” and bring you even more money. What MiFIR says, investment firm is obliged to report any trading activity (sell or buy) for any investor on daily basis. By doing that, as stated in MiFIR objectives, you as an investor will be more protected, risk of “untidy” market will be reduced, efficiency of financial market will increase and unnecessary costs for participants (you) will be reduced. From a perspective of an investor, you don’t have to do anything – investment firms have implemented mechanisms for MiFIR Reporting or, in case of our clients – we in Comminus did it for them.

Finally – what is it and how it has been done?

MiFIR report is made of 65 different fields that need to be populated with data from different sources. Fields are divided into 10 groups to simplify reporting:
  1. General fields
    1. Report Status
    2. Transaction Reference Number
    3. Trading Venue Transaction Identification
    4. Executing Entity Identification Code
    5. Investment Firm Covered by Directive 2004/39/EC or Directive 2014/65/EU
    6. Submitting Entity Identification Code
  2. Buyer Decision Maker
    1. Buyer Decision Maker Code
    2. Buyer Decision Maker – First Name
    3. Buyer Decision Maker – Surname
    4. Buyer Decision Maker – Date of Birth
  3. Buyer
    1. Buyer Identification Code
    2. Country of the branch for the buyer
    3. Buyer - First Name
    4. Buyer – Surname
    5. Buyer – Date of Birth
  4. Seller
    1. Seller Identification Code
    2. Country of the branch for the seller
    3. Seller – First Name
    4. Seller – Surname
    5. Seller – Date of Birth
  5. Seller Decision Maker
    1. Seller Decision Maker Code
    2. Seller Decision Maker – First name
    3. Seller Decision Maker – Surname
    4. Seller Decision Maker – Date of Birth
  6. Transaction Details
    1. Trading Date Time
    2. Trading Capacity
    3. Quantity
    4. Quantity Currency
    5. Derivative Notional Increase/Decrease
    6. Price
    7. Price Currency
    8. Net Amount
    9. Venue
    10. Country of the branch Membership
    11. Up-front Payment
    12. Up-front Payment Currency
    13. Complex Trade Component
  7. Transmission Details
    1. Transmission of order indicator
    2. Transmitting firm identification code for the buyer
    3. Transmitting firm identification code for the seller
  8. Trader  and Algorithms
    1. Investment decision within firm
    2. Country of the branch responsible for the person making the investment
    3. Execution within firm
    4. Country of the branch supervising the person responsible for the execution
  9. Instrument Details
    1. Instrument Identification Code
    2. Instrument Full Name
    3. Instrument Classification
    4. Notional Currency 1
    5. Notional Currency 2
    6. Price Multiplier
    7. Underlying Instrument Code
    8. Underlying Index Name
    9. Term of the underlying Index
    10. Option Type
    11. Strike Price
    12. Strike Price Currency
    13. Option Exercise Style
    14. Maturity Date
    15. Expiry Date
    16. Delivery Type
  10. Indicators
    1. Waiver Indicator
    2. Short Selling Indicator
    3. OTC post-trade Indicator
    4. Commodity derivative Indicator
    5. Securities Financing Transaction Indicator
To be honest, that’s a lot of fields to fill in a single report. Each field has either a default value (if no value can be provided) or it has a special rule that must be applied in order to successfully report. For instance, “Seller identification code” is a combination of country from which seller originates and personal identification number concatenated in a single string, “Seller – first name” has to be a string without spaces if a seller has 2 or more first names and it should not be longer that 5 characters (“Ana Marija” would become ANAMA)...

To fulfill MiFIR specification, custom SQL functions have been implemented with a different logic to get the data in specified format from a different source.

MiFIR reporting file creation and distribution

When MiFIR reporting became mandatory (in January 2018) our client came up with an extra proposal: two separate report types must be generated – bulk and individual. Difference between those 2 reports is in the amount of data, format of the file should not be changed. Bulk report summarizes trading activity on transaction level, while individual report shows a list of all investors that were involved into a trade referenced by trade identifier with personal details (personal identification number, name, surname, date of birth...)

MiFIR reporting file distribution has been implemented using Microsoft SQL Server Integration Services (SSIS). Package is executed once a day and it collects trading activity for the last working day. Package itself is made of two logical parts – one for bulk and one for individual report generation and distribution.

List of steps to execute within a SSIS package:
  1. Collect data for bulk reports
  2. If no records exist, move file created into archive folder
  3. If records exist, send generated file to SFTP server of Stock Exchange
  4. Collect data for individual report
  5. If no records exist, move file created into archive folder
  6. If records exist, send generated file to SFTP server of Stock Exchange
SSIS package has implemented its own error handling– if any part of the package fails, e-mail is being generated with information what step failed and why. This option really helps a lot when an error occurs – it can be fixed within a minute.


Being a part of MiFIR reporting project was a good thing – I’ve learned a lot about bargain process itself and was able to use Microsoft SQL Server Integration Services to solve complex processes. It’s been almost 9 months after implementation ended and we had no problems with it (knocks on wood). Feedback from our clients is great and lets hope it will stay that way.

Tags: Data Management, MiFID, MiFIR, Reporting, SQL Server, SSIS