Basic terminology and operations of a stock exchange – from a BI point of view
Looking from a high perspective, the operations of any stock exchange (SE) in the world are pretty simple: buyers and sellers give orders for certain products and if their prices match, a trade is made. Buyers and sellers are symmetrical in the sense, that neither side has more rights or obligations than the other, the SE has to treat them identically. Actually a buyer can be a seller and vice versa , or even buy from itself. Once a trade is made, participants (buyers and sellers) pay a fee for it.
Of course practical operations make things a lot more complex, and that is where BI can enter the game.
Orders and Quotes
Orders are the basis of trading, because for every trade there has to be two distinct orders, one buy and one sell. The main attributes of an order consist of five things: instrument or security (the product what I want to buy or sell), price (which is called limit since you’d always be willing to sell something on higher and buy something on a lower price, not just on the exact one you’ve added), quantity, termination datetime and type. From this five attributes instrument and type can never be changed, the other three can. If you have to change the instrument (put an order for Apple but want to buy BMW) or type (later explained) you have to delete the given order and add a new one. Price, quantity and termination can be changed as wished. The side of an order (buy or sell) can never be changed.
Any given order can be part of multiple trades, if not its whole size are matched with the opposing order. If for example, there is sell for 100 shares and a buy for 30, then the sell order will remain there with 70 shares after the trade. (A trade on the other hand always consists of two different orders.)
There are two main types of order, and a lot of minor ones. The main ones are market order and stop order. A stop order is only visible (or is in the orderbook or just book– explained later) if the price of an instrument reaches a certain level. For a buy stop order, if the order price (or order limit) is 100 and the stop price (or stop limit) is 90, the order will only be visible if the price of the security reaches or passes 90 (for a sell order the stop limit is higher than the order limit). A market order is always in the orderbook from the time it is added without any constraint.
Apart from this, there are many minor types of order, which may or may not be traded in an exchange. Examples are ice-berg orders (where only a small part of the order is shown in the orderbook and when it is traded, another comes up automatically), fill or kill orders (if it is not traded instantaneously, it is terminated).
Quotes are indeed orders made in pairs: a buy and sell order is placed parallelly by a trader for the same instrument. Quotes are generally used for market making, that is, when a trader is obliged contractually the “make market” for an instrument (and of course it is renumerated in one way or another).
Orders are said to be aggressive if they are traded as soon as they are placed. On the other hand, if an order just “sits” in the book (because for example it is very high sell or very low buy order), they are passive. It is interesting to mention that an aggressive order can become passive if not the whole order are matched when placed in the book. The order will be passive after the trade, though its size will be less than the original one. On the other hand, a passive order can never be aggressive.
Persistent – non-persistent orders
After an exceptional circumstance emerges, non-persistent orders are deleted from the book, whereas persistent ones are not.
The average difference between order prices. Usually taken as an average over a certain period of time.
BI-potential: Even though orders are the basis of trading and are quite numerous, they aren’t really of great interest in themselves. On the other hand, derived topics, most of all orderbook and market making related (see below) problems are good candidates for BI-solutions. As for orders themselves, compulsory MIFID2 (see again below) compliance reports can be supported by BI-solutions.
Existing solutions: no reports available which contains orders themselves, though other topics contain orders indirectly (MIFID publications, MM, orderbook).
Orderbook and Market Making
There can hundreds of orders of different size and price which are two low (for a buy) or to high (for a sell) to enter in a trade. The state (or snapshot) for all the orders for any given security is called the orderbook. The depth of the orderbook is the number of orders on sell or buy side (whichever is higher). A 10-deep orderbook means that on either the sell or buy side there are 10 orders (the other side might contain less than 10 orders). The pair of highest buy and lowest sell price orders are said to be on the top of the book (or at best price level).
On well-functioning SE those sell and buy orders which have equal prices, or where the sell is lower than the buy price, are matched, a trade is made, and the orders affected are removed from the book. If such orders remain in the book, meaning no trade is made, the SE might not function well (except for auction-trading, or indicative orders).
There are two main types of orderbooks, aggregated and non-aggregated. An aggregated book shows the prices and volumes, not the individual orders. For example if there 2 buy orders for a share, both with 100 price, but one for 10 the other for 25 shares, then the aggregated book will show that at price 100 there are 35 buy orders for this shares. Whereas the non-aggregated orderbook will show these two orders separated.
The problem with working with orderbooks is simply quantitative. If for an order either the price or the volume changes, so will the orderbook containing that order. If it changes substantially, a change in price could affect all the other levels in a book as well. Above this, to create an orderbook one should pair all the orders that are active for any given time, which is a very calculation intensive task.
The liquidity can refer to an exact measure (the volume of the orders in the orderbook) or to a soft term for how many orders there are for a security on average.
Market making is useful for such securities which are not traded regularly, orders turn up only occasionally (even less then daily), but is importand for the SE in some respect. In such circumstances no one would be interested in trading, since there were rarely any opposing orders available, let alone one with a suitable price. So an SE can decide that it gives a discount from certain fees if a trader undertakes market making (MM) in one or more securities. MM is done by placing quote (a sell and buy order parallelly) for the given security with a minimum amount of volume and a maximum spread (the difference between the sell and buy price, sometimes expressed as a percentage of the average of the two prices – the former is the absolute, the latter is the relative spread). With this a trader basically guarantees that it will buy or sell the given security within the price range of the spread up to a maximum amount. For example if a trader is an MM for a security with minimum volume of 500 and spread 1%, than it has to have a buy and sell order with at least 500 amount, and e.g. with a sell price of 1000 and buy price 990 (1000-990 = 10, and 10/995=1.005%>1%).
The reason for minimizing the amount is to guarantee a minimum volume that can be bought or sold any time on the SE. If this could be any low, then an MM could simply fulfil its obligations by placing 1 sell and 1 buy order, which not quite useful. The spread maximization is for keeping the price within reasonable limits. Again, if the spread would not be maximized an MM could have a sell order for 9999999999 and a buy for 0 – which is, again, not an attractive opportunity either.
Because of these limits, an SE has to be sure that an MM meets all its obligations, which can be supported by tailor-made dashboards and reports.
BI-potential: orderbooks are pretty tricky to produce on an SE level. Standard SE tools support orderbook reports for a selected part of the instruments, but a general reporting solution might not exist. MM performance analysis being based directly on the structure and content of the orderbook, nice BI-solutions can be produced which show MM-perfomance on a daily basis, drillable for the most detailed information (down to orders). To verify and validate the monthly MM-calculations, background reports could be made available.
Existing solutions: MM-perfomance reports (for prompt market on a daily basis, for the derivative on detailed orderbook basis);
Trades and prices
Compared to orders, trades are straightforward: if buy order is at least as high as sell order, than a trade is made. The volume of the trade is lesser of the size of the two orders, the price of the trade is the price of the passive order (which came before the other). The value of the trade is usually the price*volume, though sometimes the price itself if referred to as the value.
As it was mentioned before, a trade always consists of two distinct orders, but an order can be part of several trades.
Price of an instrument is simply the price where the previous trade was made. Closing price is the last price available for an instrument on an SE. For indicative price, see indicative orders.
Types of trading (trading model)
There two main types of trading: continuous matching and auction. With some simplification we can say the continuous matching means that as soon as on order is entered it is examined if there is a matching one already in the book in the other side. If there is, then a trade is made. If the trading model is auction, than trades are only be made after an auction closes. Auctions are started (one or more during a day) with the call phase, during which orders can placed, modified or deleted as wished. However, trades are not made instantaneously when orders are entered (as opposed to continuous trading). Instead, after a random amount of time (to avoid market manipulation) the call phase ends, and price determination phase sets in (which might take only 1 or 2 seconds), during which the matching is done, and price is determined.
During the call phase the price is called indicative (since it is not yet determined), and orders are called indicative orders.
If an SE doesn’t work 24/7, for example from 9-17 on weekdays, then even the ones with continuous matching will have actions as well. At trading start there is an opening auction, at trade-end there is the closing auction. If an exceptional situation occurs during trade, there can be an intraday auction as well. All three types of auctions have a call and price determination phase.
Since the closing price of a security is the price of the last trade, there can be situation where price remains non-determined. This can occur when the at closing auction all the sell orders are on a higher price than the buy ones. Then the price-without-turnover is calculated as the simple arithmetic mean of the highest buy and lowest sell price.
Orders placed during an auction.
Fees – trading and others
One of the main types of revenue of an SE are trading and other fees. Trading fees are calculated after every executed trades, usually as a percentage of value (price*volume) of a trade, although they are capped both at the bottom and at the top. Thus, monitoring how the fees from trading is performing is a vital part of an SE.
There can be other fees, like one for being able to enter into trade for a given type of security, or issuing a security. But these are minor compared to trading fees.
BI-potential: Trades are what SE-s for, this is one of the main source of their revenue. Any solution that gives a detailed view on how trades are performing in time and across participants are a must for an SE.
Existing solutions: Full trade list (Kötéslista – under construction, but can be demod; Havi sajtó), fee dashboards (Tranzakciós díjak).
The other main source of revenue for an SE are from selling the data generated on an SE. The companies that buy these data are called data vendors, which might sound strange since they are the ones who buy something. The reason for this is that usually they are rather wholesalers then end-users (like Bloomberg, Thompson-Reuters etc.), so actually they themselves sell the data again.
From this perspective there are two types of data: pre- and post-trade. To keep it simple, pre-trade means orders and the orderbook, whereas post-trade deals with the trades itself. They have to be sold separately, though usually a vendor subscribes for both.
Pricing by an SE is determined not just by the volume of data (for pre-trade one can buy just best-level book, or 5-depth, 10-depth etc.) but to how many end-users it is reselled. Thus, it is inevitable to be able to track the number of end-users for the available vendors.
BI-potential: Classic sales report aimed at data vending: who-when-how (what channel) buys what? The act itself of selling the data is not a BI-topic, since it is done very close to real-time (which is not supported by BI tools).
Existing solutions: Vendor report along with excel templates for standardizing data compilation.
MIFID2 is a very complex and detailed regulatory framework. As its name suggests, it has a predecessor, MIFID, which has been in force since years. Apart from operational tasks and regulatory issues, MIFID2 has a lot of publication requirements for SE-s (all can identified by their RTS number, which stands for regulatory technical standard). These include daily publication in XML for all securities (reference data RTS23, transparency calculation RTS3), bi-weekly for selected securities (DVC – double volume cap; RTS3 again), monthly text files for orders (OTR – Unexecuted order to Transactions RTS9) and quarterly text file for orders, trades and orderbooks (best execution RTS27 – for BSE this is 1.5 million files). Though there are other ones as well, these are those that BSE publishes currently.
BI-potential: Though the content of the publications are of questionable quality and usability, these are a must for all European SEs. Probably a lot of SEs cope with this requirement already, but even if they do, they might need substantial manual work, their processes are not quite automatized, or well below optimal. (FYI: a lot of SEs didn’t implement RTS27 – Best Execution yet, though the deadline was July, 01 – this information needs to be verified).
Existing solutions: Existing jobs that fully automatically compile the data for publications and do the publication themselves. SPF dashboards that helps to track XML publications (RTS3 and RTS23), shows errors and missing data in them.
Yield and price
This topic is not related directly to reporting or BI in general. It is a more fundamental issue, that might occur anywhere – among others in BI. The root problem can be summed up as follows: although it is very easy to calculate price if yield is given, the other way round (that is, calculate yield if price is given) is all the more problematic. The reason for this is purely mathematical: to get price from yield you basically have to run a power() function, which is standard feature of every programming languages since the 90s (even SQL). However, to get yield from price, you have to solve an n-factor polynomial equation, which can not be done explicitly if n>4, only by approximation. While a lot of languages have some or full support for this (built-in Newton-method for example), SQL has none. To solve this with a generic and reusable method in DW, let alone using only SQL, is not straightforward at all, and needs some tricks to work fast enough for all practical situations.
BI-potential: This topic can be treated as minor compared to the others, because it is not problem that needs a BI solution. However, if some SEs struggle with, we could help!
Existing solutions: A close to real-time report that is still under-construction, along with optimized database functions and ETL solutions.
Just some words on trading systems. There are three big ones, which is used by a lot of SEs all around the word: Nasdaq/OMX, Euronext and Xetra. And some minor ones, like MMTS. In BSE Xetra and MMTS are used together, the former for prompt market (shares, bonds etc.), the latter for derivatives (like options and futures). Xetra is the product of Deutsche Börse (DBAG) and BSE uses it through Wiener Börse (WBAG). Apart from BSE, Xetra is used in several other countries: Ljubjana, Zagrab, Prague, Sofia and Malta.
Xetra is interesting because we have a lot of experience and easy-to-install solutions with it. Though for SEs using other trading systems, necessary interfaces might not be too complex to produce since SEs are basically the same everywhere.