With the introduction of MiFID II regulations the transparency requirements when trading with non-professional clients have increased. Generally speaking financial institutes are now required to reveal the different kinds of costs the client incurs by buying financial products indirectly through an intermediary as compared to what these products would cost on the interbank market. This gives the client a clear view into what the intermediary is earning on the trade and is expected to increase competition and reduce costs.

The information needs to be provided together with the price quote before the client makes a decision to enter the trade (ex-ante) as well as within a yearly report for all trades made throughout the year (ex-post costs). The generation of ex-ante costs was covered here (hyperlink). Here we focus on the generation of the ex-post reports.

As with ex-ante the first challenge with ex-post is calculating the costs. The calculation logic of ex-ante and ex-post should be the same but because of the time gap involved between negotiating with the client, closing the deal and hedging it on the interbank market there will be differences. While the price quoted to the client doesn’t change the market parameters will change and the trader will have slightly different costs. In cases where the costs have been calculated based on present values (e.g. swaps) another source of deviations will be the fact that Prime doesn’t save intraday present values. One can use here the end-of-day MTM valuations as an approximation, which especially with one-to-one hedging is justified because the effect of the intraday market change should cancel out for the client trade and hedge.

With these functional questions cleared the inputs needed for the ex-post reports can be programmed like any other calculation as columns in ADFL. The ex-post report is supposed to have a table structure with a layout similar to the one bellow and should be generated as a PDF file.

This kind of layout can easily be generated out of Trading Manager with a Trade sheet. The standard script FWorksheetReport can be used to export the data from Trading manager and it offers different output formats (xlsx, csv, pdf, html). Since the requirement here is a PDF-file let’s look at the steps involved with this kind of format. When the script is executed Prime first generates an XML file that contains the data of the Trading Manager sheet. As a second step the XML-file is read through and XSL-template that defines the layout and formatting and selects the necessary data from the XML and generates an XSL-FO file. This file is read into the Apache FOP command line tool and it renders a PDF-file. The tool itself is free and the technology is open source.

This sounds complicated and programming XSLT is not without its trickiness, but FIS already provides some pre-built templates that can be adjusted for the client’s needs. Also programming the XSL-Template can easily be outsourced to lower-cost contractors since this is a task that does not require a Front-Arena installation.

The alternative would be to use Adaptiv to generate the PDF. For this we need to create a confirmation in Prime which requires some triggering event and some object to attach it to. In the standard usage of the Confirmation setup the triggering event is saving a trade or a reset and the confirmation is attached to these objects. With these reports however we don’t have this as we are generating them at the end of the year and they are generated per client and not per trade. It is still possible to write a script that would find a workaround for this but since the preference is to use the standard tools where possible and FIS has committed to the XSLT-technology for these reports it was decided to take this approach.

With this in mind implementing the ex-post reports involves:

  • Setting up the cost columns in ADFL according to the calculation logic
  • Setting up a Trading Manager template for the report
  • Creating a selection for the trades to be reported
  • Setting up a task with FWorksheetReport including
  • Writing some Python functions for XML pre-processing to add additional information and text to the XML that is not within the TM-Template. The table above is placed within a letter so all the text needs to be added to the XML
  • Preparing the XSL-Template for the PDF
  • Writing a Python script that loops through all clients and executes this task