Nestor Sulikowski

The booking system is a crucial module of any back-testing platform. This is the module that takes all the trades and prices, and calculates the P&L and performance indicators like Sharpe ratios, maximum drawdown’s, etc. SAMOA’s booking system goes a step further, and handles automatically (if the user chooses to) the calculation of

• currency rolls (using historical LIBOR curves from the different countries)
• carry for Treasuries (using historical Repo rates, and accounting for coupon payments)
• interest gained on the margin account
• and all the other profits and losses besides those resulting from changes in prices.

Although this complex subsystem takes care of a myriad of tasks, it can be treated by the user as a simple function, with 5 types of allowed inputs, and a single output.

Output = BookingSystem( Inputs )

The inputs can be one of the following:

• AddTrade(timeS, instrument, Q, P, comment)
• AddMark(dateS, instrument, mark)
• AddCapital(dateS, amount, comment)
• AddIncome(dateS, amount, comment)
• AddBookTag(name, value)

The image on the left shows a simple simulation with the following inputs:

(NOTE: The image shows the XML version of these inputs)

The function BookingSystem(*) takes these inputs (in chronological order) and produces rolling calculations that can be queried (if necessary) while the trades are happening.

After the simulation is finished, it returns an XML document with a predefined schema of type “book”. This document can be queried using xquery (supported by SQL 2008).

The schema of the output is not particularly important (If you are curious, here is a representation of the output XML using HTML nested tables). The important thing is that it has been cleverly designed to store as little information as possible (to limit it’s size), yet it has all the data needed to produce the performance reports with minimal post processing.

BookingSystem(*) matches the trades using FIFO (first-in, first-out), and keeps a daily record of realized P&L (of positions that have been closed), and unrealized P&L (mark-to-market of open positions).

The booking system can be accessed using several API’s (SQL, Mathematica, Excel, etc). Let’s replicate the example above using the SQL API.

• Let’s start with the table variable @A_XmlCalls, with the inputs:

select * from @A_XmlCalls 

• Now let’s apply the function BookingSystem(*), which in SQL is called “dbo.ssBookXml”. We’ll save the output into the variable @A_Book_Xml.

declare @A_Book_Xml xml;
select @A_Book_Xml = dbo.ssBookXml( (select TS.* from @A_XmlCalls TS FOR XML AUTO, xmlschema('type'), root ('ROOT'), TYPE) );
Select @A_Book_Xml as ScalarVariable; 

• Now that we have the XML output in the variable @A_Book_Xml, we can query all sort of things. For instance, let’s get the list of trades:

select
   Trade.value('@TradeID', 'bigint') as TradeID,
   Trade.value('@TimeS', 'datetime') as TimeS,
   Trade.value('@Instrument', 'nvarchar(50)') as Instrument,
   Trade.value('@Settlement', 'datetime') as Settlement,
   Trade.value('@Q', 'decimal(38,20)') as Q,
   Trade.value('@P', 'decimal(38,20)') as P,
   Trade.value('@Comment', 'nvarchar(500)') as Comment
from @A_Book_Xml.nodes('/Book/MarkMonth/MarkDay/Instrument/Trade') Trades(Trade); 

• What prices did we use for marking the book?

select
   Instrument.value('@DateS', 'datetime') as DateS
   ,Instrument.value('@Name', 'nvarchar(100)') as Instrument
   ,Instrument.value('@Mark', 'decimal(38,10)') as P
from @A_Book_Xml.nodes('/Book/MarkMonth/MarkDay/Instrument[@Mark][@Seq]') Instruments(Instrument); 

• Daily returns and NAV

with MainT as (
    select
        MarkDay.value('@DateS', 'datetime') as DateS,
        MarkDay.value('@AccPL', 'decimal(38,10)') as AccPL,
        MarkDay.value('@DeltaPL', 'decimal(38,10)') as DeltaPL,
        convert(xml, MarkDay.value('@Positions', 'nvarchar(MAX)')) as Positions,
        MarkDay.value('@AccAtRisk', 'decimal(38,10)') as AccAtRisk,
        MarkDay.value('@AccK', 'decimal(38,10)') as AccK,
        MarkDay.value('@NAV', 'decimal(38,10)') as NAV,
        MarkDay.value('@InitialNAV', 'decimal(38,10)') as InitialNAV,
        MarkDay.value('@Return', 'decimal(38,10)')*10000.0 as ReturnBP
    from @A_Book_Xml.nodes('/Book/MarkMonth/MarkDay') MarkDays(MarkDay)
)
select *, AccAtRisk / NAV as Leverage from MainT; 

• Finally, let’s extract the original inputs

select //Trades
    Trade.value('@Seq', 'bigint') as Seq,
    convert(nvarchar(max), dbo.ssRemoveXmlChildren(Trade.query('.')) ) as XmlEvent
from @A_Book_Xml.nodes('/Book/MarkMonth/MarkDay/Instrument/Trade') Trades(Trade)
where ISNULL(Trade.value('@Internal', 'bit'),0)=0
UNION ALL
select //Capital
    Capital.value('@Seq', 'bigint') as Seq,
    convert(nvarchar(max),Capital.query('.')) as XmlEvent
from @A_Book_Xml.nodes('/Book/MarkMonth/MarkDay/Capital') Capitals(Capital)
where ISNULL(Capital.value('@Internal', 'bit'),0)=0
UNION ALL
select //Marks
    Instrument.value('@Seq', 'bigint') as Seq,
    convert(nvarchar(max),Instrument.query('
        element Mark {
        attribute Seq { @Seq },
        attribute DateS { @DateS },
        attribute Instrument { @Name },
        attribute P { @Mark }
    }')) as XmlEvent
from @A_Book_Xml.nodes('/Book/MarkMonth/MarkDay/Instrument[@Mark][@Seq]') Instruments(Instrument)
where ISNULL(Instrument.value('@Internal', 'bit'),0)=0;
order by Seq 

You get the idea… There is a lot of information that can be extracted from the XML output, using a little xquery.

In other blogs I’ll dig a bit deeper into how SAMOA handles the different instruments. Till then!