"'"MIDAS ~ Historic Orderbook Rebuild

LONDON STOCK EXCHANGE

SERVICE DELIVERY

Historic OrderBook Rebuild

Data Description and Guidance notes

Version 7.0

Prepared by: To be reviewed by: To be signed off by: Date:

J.Puddick Tim Carroll Gurbinder Bansal 23 March 2004
Tom Obrian
Chris Porton

21 June 2007 Page 1 of 10 Vers ion 1.0

/
 MIDAS

1 INTRODUCTION ................................................................................................... 3

2 ASSUMPTIONS .................................................................................................... 3

3 OVERVIEW OF THE DATA FILES .................................................................•..... 3

3.1 File Format ...................................................................................... ............................................. 3
3.2 Order Details ............................................................................................................................. ... 3
3.3 Order History .............................................................................................................................. . 4
3.4 Trade Reports ............. ................................................................... .............................................. 5

4 START AND END POSITIONS AND OVERLAPPING DATA. ............................. 6

5 LINKING THE DATA FILES ......................................... ........................................ 7

5.1 Message Sequencing ....................................................................................... .......................... 7

6 WORKED EXAMPLE ....................................................................................... .... 8

7 SPECIAL CONSIDERATIONS FOR ICEBERG ORDERS ................................... 9

-8 APPENDIX A BROADCAST UPDATE ACTIONS .............................................. 10

21 June 2007 Page 2 of 10 Version 1

' MIDAS

1 Introduction If\
~/
Currently on MIDAs Orderbook rebuild files are produced each day for that day's transaction history.
Files are written to file staging server, for selected segments, containing details of Orders and
automatic trades for each instrument in time order.

Exchange Customers have expressed an interest in obtaining these files historically, however this
would require a long time to complete using the existing processing.

To accelerate this process and provide the Exchange with an efficient way of providing this data to
customers a process has been developed to extract the necessary information from the warehouse as
a set of three separated text files containing all the information required to rebuild the Orderbook for a
period of days .

2 Assumptions
This document assumes a familiarity with:

• LSE Order mechanism types (including Iceberg Orders) and Orderbook execution processing.
• LMIL broadcast messages and data formats .
• Segment structure and rules and period rules and timings

3 Overview of the data files

Three files will be produced to cover each historic one month date range. The three files relate to the
database tables on MI DAS that contain the warehouse data.

The files are :

T OrderDetail.CSV
T_ OrderHistory.CSV
T_ TradeReport.CSV

3.1 File Format

Each file is produced in a com ma separa ed 'orm at. The number of fi elds and the format of each field
is given in the tables below.

3.2 Order Details

.- . /
~Utrmn~am (f€fRm~ '
.:t:: __ ..:~~~
tMtijlab1e'.., $l~iP~- .~~ , /~~
OrderCode CHAR(10) N Jhis code is a unique reference number allocated to eactYOrder
MarketSegmentCode CHAR(4) N A code which uniq uely identifies a specific trading aretv'
N This code identifies a division of the market withir arket
MarketSectorCode CHAR(4) Segment. .
G: 10-
N Together with the Country of Register, the c/ ,used to uniquely
trlCode CHAR(12) Identify a tradable instrument
N [This specifies the Country of Register for ~ pecific tradable
CountrvOfRegister CHAR(2) Instrument.
N [This field contains the currency in which prices for a tradable
CurrencyCode CHAR(3) instrument must be expressed .
fY JA code that uniquely identifies an investor, an intermediary or a
Isubscriber. For investors and intermediaries, this will be the Bank
ParticipantCode CHAR(11) Identifier Code (BIC).

21 June 2007 Page 3 of 10 Version 1

/
,
MIDAS

N Identifies whether the participant wishes to buy or sell a quantity of

BuySelllnd CHAR(1) a tradabl e instrument/currency.
N I his classifies the market mechanism types as quotes or orders. (0
MarketMechanismGroup CHAR( 1) Orders, Q Quotes)
N A trading mechanism supported by the London Stock Exchange.
MarketMechanismType CHAR(2) (see list belovy)
N he price at wh ich a participant ente ring an order wishes to buy/sell
Price DECIMAL{18,8) a tradable instrument/cu rrency.
N [The quantity of a tradable instrument still sought or on offer at a
particular time. Since an order may be partially matched against,
~ggregateSize DECIMAL(12) this need not E!qual the amount of stock originally entered.
N [This field indicates that order execution against an entered order
twill only ta ke place if the trade is executed to the full size of the
order. The single fill indicator applies to all order types except hit
SingleFilllnd CHAR(1) orders.
N Identifies the action to be taken by customers on receipt of a
BroadcastUpdateAction CHAR(1) message.
Date DATE(YYYY-MM-DD N IThe Date that the Order was first added
Time [TIME(HI;-l:MI:SS) N [The Time that the Oreer was first added
N ~ sequence number used to assist in sorting orders received in the
MessageSequenceNumbe INTEGER(10) same second

This file contains details of new orders entering the trading system. It on ly contains details of orders
that enter the Orderbook.This file does not contain details of any orders that fail val idation on entry to
Trading or non-persistent order mechanism types - Aggressive type A (AA), Aggressive type B (AB),
Hit Orders (HO), and orders or parts of orders that execute aggressively, a~d for some Iceberg Order
execution scenarios (see section 7 below). ' 'l .
Order mod ifications are disseminated as a delete followed by an add , the add is shown as a new order
in the Order details file with a new order code and there is .no way of distinguishing this from an .0
ordinary order addition. i;.C;'- f.)5
3.3 Order History ~(~&-

This file contains a history of changes to each order and the method by which it is removed. For each
order there are one or more rows shown with an order action type for each record. The order action
type indicates if the order was partially or fully executed against, deleted , expi red etc. A full list of the
ord er action types is given below.

21 June 2007 Page 4 of 10 Version 1

, MIDAS

Order Action Typ e Description

D Deletion
E Expiry
P Partial Match
M Full Match
T Transaction Li mit (150 trades)

Together with the order deta il s file, the Order history file al lows the complete life cyc le of every order to
be reconstructed. In the cases where there has been an execution, the resulting trade code and the
match ing order code are included in each appropriate Order History record.

A fu ll list of Market Mechanism Types is given below:

AA Aggressive Type A (None in these files)

AB Aggressi ve Type B (None in these files)
CP Committed Principal Order
FE Firm Exp osu re Order
FQ Firm Quote (None in these file s)
IE Indicative Expos ure Order
HO Hit Order (None in these files)
IQ Indicative Quote (None in th ese files )
LO Limit Order
MO Market Order
PI Priced Interaction Order (None in these files)

3. 4 T rade Re ports

C6Tfim nNam.e5~J;:i~'TW~;,;;,'r F @R[VI'~lJl t;;~3tfj\,"i~~~ t'l t#latlLe..lI l!-'e.li(;I lRVI OD",, ~~. s.t{t{ ;~. .i.i4 y]i;;;
N ~ seq uence number used to assist in sorting Trades recei ved in
MessageSequenceNumber INTEGER(1 0) the same second
N 1T0gether with the Country of Register, th e code used to
TICode CHAR( 12) uniquely identify a tradable instrument
MarketSegmentCode CHAR(4) N ~ code which uniquely identifies a specific trading area
N This specifies the Country of Register for a specific tradable
. Coun tryOfRegister CHAR(2) instrument.
N Th is fi eld con tains the currency in wh ich prices for a tradable
CurrencyCode CHAR(3) instrum en t must be expressed.
N ~ code which is assign ed by the Exchange to uniquely identify
TradeCode CHAR(10) executed trades.
TradePrice DECIMAL(18,8) N "T_he price at which a trade was executed.
Trade Size DEC IMAL(12) N I he quantity of trad able instrument which was bough t or sold.
Trad eDa te DATE(YYYY-MM-DD) N The Date when the Tra de took place
TradeTime TI ME(HH: MI :SS ) N The Time when th e Trade took place
N Identifies the action to be taken by customers on receipt of a
Broa dcastUpdateAction CHAR(1) message.
Tra deTypelnd CHAR(2 ) N This indicates the type of trade whi ch has occurred.
N This field indicates whether a reported trade was made outside
a normal trade repo rting period, whether it was repo rted 'late' or
whether it was reported on time. (N-Norma l, L-Late , 0 -
TradeTimelnd CHAR( 1) Overnight)
N Ind icates whether an y bargain conditions appl y to a trad e
BargainCond itions CHAR( 1) report.
N Indicates whe ther the price and currency entered on a trade
report is the price and currency in which the transaction was
dealt (N), or whethe r con version into the valid currency for the
ConvertedPricelnd CHAR(1) tradab le instrument has ·occurred. (Y)
Publication Date DATE(YYYY-MM-DD) N The Date the trad e wa s published
PublicationTime 'TIME(HH:MI:SS) N The Ti me the trade was published

The trade report file contains deta ils of every automatic and manual trade that has taken place, except
for trades that have not been publis hed. Automatic trades only are incl uded in the current orderbook

20 June 2007 Page 5 of 10 Version 1

/
MIDAS

rebuild dai ly files. Th is historic file has been enhanced to include manual trade types al so . Note
however manual trades cann ot be linked to the order files, but can be placed in publica tion time order

Two timestamps are given for each trade. The Trade Date/Trade Time give the date and time that the
trade took place , the Pu blication Date/Publication Time give th e date and ti me that the trade was
published on LMIL. These timestamps will be the same for automatic trades and non-delayed manual
trades. L; U ) N Q i{v M1V :? Isei iJ'f FCK"" 14,T l O -:~ L-I IV !~
Trade cancellations are shown as a separate record with the same tradecode but with a Broadcast
Update Action of "D" , and the Publication DatelTime gives the cancellation datelTime. Contra trades
are shown as a separate record with a new tradecode but with a tradetype of CT, and Post Contra
trades with a trade type of PC. There is no definitive method for linking the original automatic trade ,
with a contra or post co ntra trade, since linking by trade price and trade size may give more than one
match.

On LMIL all uncrossing trades from an auction are aggregated and disseminated to the market as a
single Trad e report (different uncrossing trades in an instru ment wil l have th e same price and time).
This Trade repo rt has a dummy trade code that is unknown to MI DAS. MI DAS receives details of each
individual uncross ing trade, each with the same trade time and price but a uniqu e trade code. These
individual trades will be detailed on the Historic Orderbook deta ils files.

4 Start and End positions and overlapping data.

Since each set of fil es will cover a one month period in time , th ere are som e special circumstances for
orders tha t span more than a one month period .

For the first da y of the period being covered all orders not Deleted/Expired/Fully execu ted from the
previous day will be included. This means that although full detail s are given for on e month, the data in
the Order Detail s file wi ll conta in details of orders added earlier th an th e period covered - i.e. orders
that were placed on the orderbook prior to this period but which have not been deleted expired or ful ly
matched. These orders are the same as those that would appear in the orderbook download LMIL
service on the first day of the period. These records can be identified from th e Bro adcastUpdateAction
that wi ll be set to "F". For these orders the Size will be the remaini ng order size and not th e size of the
original order.

Since orders ca n have a life span of up to 90 days (depends on the segment), Order detail information
for some orders ca n be repeated in several files .

To obtain Start of Day position for other days within the period spanned by the fi les , customers must
derive the state of the orderbook from th e add itions and removals that take place. For example if there
are 20 days covered in a set of files, to find the start of day position for day 8, select all add records
from the Order Details file where there is no corresponding removal record on the Order History file
with an Order Action Type of D, E, M or T with a date less than day 8.

Orders which are left on the Orderbook at the end of the fin al day covered wi ll have no order history
records detailing the ir removal , to complete the history for these orders , files coverin g subsequent
periods will be required. These orders can be identified from the Order History fil e wh ere there is no
record with an Order Action Type of D, E, M or T.

20 Ju ne 2007 Page 6 of 10 Version 1

·'MIDAS

5 Linking th e data files

! OrderDetaiJ t _OrderHislory r TradeReport <~ ",
OrderCode CHAR(10 OrderCode CHAR(10) MessageSequenceNumber INTEGER(10)
Market5egrnentCode CHAR(4) OrderAc! ionType CHAR( l ) TICode CHAR(12)
MarketSectorCode CHAR (4 ) MatchingOrderCode CHAR(10) MarketSegmentCode CHAR (4)
TI Code CHA R(12) TradeDate DATE(YYYY· MM· DD) CountryOfRegister CHAR(2)
CountryOfRegister CHAR(2) TradeTime TlME(HH:MI:SS.S(6)) CurrencyCode CHAR (3)
CLJrrencyCode
Pa rticipantCode
CHAR (3)
CHAR(l1)
TradeSize
TradeCode
DECIMAL(8)
CHAR(10)" -
~ TradeCode
TradePrice
CHAR(10)
DECIMAL(18,8)
Buy5el11nd CHAR(1) TICode CHAR(12) TradeSize DEC IMAL(12)
MarkelMeChanismGroup CHAR(l) CurrencyCode CHAR (3) TradeOale DATE(YYYY·MM-DD)
MarketMechanismType CHAR(2) CountryOfRegister CHAR (2) TradeTime TIME(HH:MI :SS.S(6))
Price DE CIMAL(lB,B) MarketSegmentCode CHAR (4) BroadcaslUpdateAGtion CHAR(l )
AggregateSize DECIMAL(1 2) AggregateSize DECIMAL(12) TradeTypelnd CHAR(2)
SingleFilllnd CHAR(l) BuySellind CHAR (l) TradeTimelnd CHAR(l)
Broad cast Upd at eA ct ion CHAR (1) MarketMechanismType CHAR (2) BargainConditions CHAR (l )
Date DATE(YYYY· MM· DD) MessageSequenceNumber INT EGER(10) ConvertedPricelnd CHAR(l)
Time TIM E(HH:MI :SS.S(6)) DATE(VYYY-MrV\-DD) ContraStatus CHAR(l)
Date
MessageSequenceNumber INTE GER(10) Time TIM EfHH:MI:SS.S(6)) PublicationDate DATE(YYYY·MM-DD)
PublicationTim e TIME(HH:MI:SSS(6))

The Order detail fi le is linked to the Order history fil e by order cod e. Th is is a one-to-one or on e-to-
many or one-to -none relationship depen ding on the particu lar ord er.

Th e Order History file is linked to the Trade Repo rt file by th e Trad e Code for Automa tic Trades only.
The Order History file also con tains th e Matching Order Code for au tomatic trades, allowing both the
buy and sell orders to be identified . However it is not always po ss ible to obtain details of both orders
for every trade, since some non-pers isten order typ es , or aggress ive Order Details and executions
against the hidden volume of an Iceberg Order are not shown.

5.1 Message Seq uencing

Each record has a timestamp th at ca n be used to determ ine the sequence of events to the orderbook.
T imes are given to the nearest second, so to reproduce events in the correct sequence where more
than one record occurs in one second, The data should be sorted in the following order

Time
Instrument
Message Seq uence Number
OrderCode
Order Deta il reco rds before Order History records
OrderHistory records by Aggregate Size in desce nding order

Norma lly for an Instrument for an y day, expect to see the fo ll owing message types

• A group of orders that were left on the Order book from the previous day
• A group of Expire Order history records for Orders that were left on the Orderbook but expired
overnight.
• A mix of all message types as continuous trad ing takes place ,
• Manual Trade reports only followi ng Orderbook close

20 June 2007 Page 7 of 10 Vers ion 1

/
MIDAS

8 Appendix A Broadcast Update Actions

Order Details A Add

F Start of Day Order

Trade Report E Automatic Execution

A Manu al Trade Addition
D Manu al Trade Cancellation

(/ J

• .J

20 June 2007 Page 10 of 10 Version 1

MIDAS

6 Worked Example

The fo llowing gives a small examp le of data for a singl e ins trum ent , the Start date is 10 Novem ber
2003

T OrderDetails
OJ
"0
>- Z
0 \!)
f- \!)
E u
'-' -g OJ
c:
~
§,
'-'
0 ~
'"
'",
Q)
Q)
"0
0 '-'
Q)
"0
0
'"ro
'1"
{j
~
Uj "0
Q)
::>
CT
Q)
<3
Y.. cro
Q)

",§ '" "-~

(/)
"
0
Q)
(/)
'"
(/)
0 g
Q)
2 iO Q)

i
OJ "- OJ
'-' 0> ro
~ ~
Qj
g ~
~
OJ
~ m m "0
'-'
['!
:u
(/)
>- §- :u
OJ
u
\!)
0, 0>
c: iO
OJ Q)
E
'" OJ
"'"
OJ E
0 2 2 i=
0
'-' (5 "-
::>
co 0 20-
Q)
tt <'" Uj
c:
0 0 i= 2 ::>

A032ESD803 SET 1 FT10 G80030559776 GB G8 X 8 0 LO 138.5 6401 N F 07/11/200 3 16:29:33 191763

C040KL5M03 SET1 FT10 G80030559776 G8 G8X B 0 LO 135.25 143639 N F 07111/2003 16:31:13 193036

F0310XKT03 SET1 FT 10 G80030559776 GB GBX 8 0 LO 136.5 44700 N F 07/11/2003 16:24:15 188690

90358DBW03 SET 1 FT1 0 G8003055977 6 GB G8 X B 0 LO 137.75 161224 N F 07111/200 3 16:31 :13 193024

F0310Z7A03 SET1 FT10 G80030559776 GB G8X S 0 LO 141 44700 N F 07111/200 3 16:24:15 188694

F031450003 SETI FT10 G80030559776 G8 GBX S 0' LO 138 54300 N A 10/11/2003 10:21 :23 40446

70340YOM03 SET1 FT10 G80030559776 GB G8X B 0 MO 0 3148 N A 10/11/2003 16:31 :36 150374

60380MWD03 SET 1 FT10 GB0030559776 GB GBX 8 0 LO 138 32 N A 10/11/2003 15:08:03 111664

T 0 r der H'IS ory

Q) c:
"- '2OJ
'" -=Q; '" !g
~
"ij)
>-
\!) "0
\!)
~ 0-
m ~<3 "2 0: 0
" Uj OJ

'"
Q)
0 (/) ~
v
0 en
c:
Q)
N
Uj
"0
0 ~
0
(;'
Q)
Q)
iO " 2
:§
Q)
Q)
", "
Q)

'-' < '-' Q)

Qj Qj :cu Q) Q) "0
0 §
c: Qj
~ Q) '"0,
\!)
Qj
(/)
-
'" 0-
-i' >-
OJ ro E
'" ::> Q) Q)

to "2 - Q)
"f-ill "f-ill '-'
8CB 5
~
roo
"O
::>
>- rof- "' 2 iO E
<'"
ro"
0 0 2 0 i= 0 2 al 2 E 2'" uQ) 0 i=

A032ESDB03 E 0 GB0030559776 GB G8X S ET~ 5401 B LO 462 11/10103 7:03:04

C040KL5M03 E 0 GB0030559776 GB G8X SET 1 143539 B LO 635 11/10103 7:04:17

F0310XKT03 E 0 GB0030559776 GB G8X SET I 44700 B LO 947 11/10103 7:06:1 7

90358D8W03 E 0 GB0030559776 GB G8X SET 1 161224 B LO 395 11/10103 7:02:43

F0310Z7A03 E 0 G80030559776 GB G8X SE T1 44700 S LO 952 11/10103 7:06:19

F031450Q03 D 0 GB0030559776 GB G8X SET1 54300 S LO 407 46 11/10103 10:23:08

70340YOM03 M D043380A03 3148 5037GJ0703 GB0030559776 G8 G8X SET 1 0 B MO 151 59 1 11 /10103 16:35:09

D0433BOA03 p 70340YOM03 3148 5037 GJ0703 GB003055977 6 GB G8X SET 1 2902 S MO 151562 11/10103 16:35:09

60380MWD03 M I E03J FP9V03 32 60380MWE03 GB0030559776 GB GBX SET 1 0 8 LO 111 666 11/10103 15:08:03

T T ra d e R epo rt
iii::> § ~ iii g 1) !"
Q)
E
CT
c:
CD
0
~
"
"0
0
"0
"-
"0
c: "0;c: '6
c:: Ct
ro
'ilg i=
'"
(/) ~ G)
Y..u u
Q) Q)
l!l
OJ
.s
Q) OJ 0; 0 U
c:
Q)
en "
ro E
Q)

-g
0
0
(f)

~E
>-
c:
0
0Q)
u
(t
OJ
Uj
iii
0
Q) '; 'B" e
'J .Q
~
';
E
i=
Q)
0
c:
'ro
l
Q)

~
.Q
iii
.<e
"ill'"
C
'" ::>
"f-ill "i=ro "i=ro "0
e~ " "f-ill 2' :0 :g
"'2
Q)
2 u
Q) S2
f- 2" 00 80 (5 f- f-
ill
al", ~ co '"
alc::
0"
'-' c:
::>
"- "-

151595 GB0030559775 S=--- G3 G8X 5037GJ0703 138 3148 10/11/03 16:35:09 E UT N Y N 10/11103 16:35:09

111670 GB0030559776 S"'T- G3 I GBX 60380MWE03 138 32 10/11/03 15:08:03 E AT N Y N 10/11103 15:08:03

120438 GB0030559776 S81 G3 I GBX 102BG 18M03 137.3 200 10/11/03 15:19:00 0 0 L Y N 10/1 1/03 15:29:08

120384 GB0030555775 Sq· GB GBX 102BG18M03 137.3 200 10/11103 15:19:00 A 0 L Y N 10/11/03 15:28:58

20 June 2007 Page 8 of 10 Version 1