Nestor Sulikowski

The back-testing in SAMOA is performed in stages:

1. The daily and/or tick data are combined into one table. From that table, a stream of Events is produced. Each event carries the data that triggered it.
2. Whenever an event is triggered, it’s data are fed as global variables to the JScript, and the corresponding event function is called.
3. The JScript function interacts with the Book object to add trades, marks and what not. It’s up to the user to decide how to program this JScript.

The function that carries this workflow is ssRunStrategyS (or ssRunPortfolioS in the case of a portfolio of strategies)

select @simulationOutput = ssRunStrategyS( @xmldailyEvents, @tickEventsQuery, @tickFrom, @tickTo, @strategy, @yieldAll ) 

This function takes a strategy JScript (parameter “strategy”) and executes that script through the list of events. The input data is provided with the first 2 parameters:

1. Low frequency events (parameter “xmldailyEvents”)
2. High frequency events (parameter “tickEventsQuery”)

These parameters can be provided in the following combinations:

The strategy script (parameter “strategy”) can perform actions for each or some events. The function ssRunStrategyS collects these actions and returns the table “simulationOutput”. This table is simply a log of events and actions. Some actions can be the inputs for booking system (trades, marks, etc). However, “simulationOuput” can contain any other information the user desires to output. The columns of “simulationOutput” are the following:

[TimeS] datetime
[Event] xml
[Outputs] xml

Parameter: xmldailyEvents

This is an xml variable containing the list of low frequency events (as rows). Typically the SQL clause “FOR XML AUTO” is used to convert a table with daily rows into an xml variable that is later passed as this parameter. For instance:

declare @xmldailyEvents xml = (select TS.* from table1 TS FOR XML AUTO, root('ROOT'), xmlschema('type'), TYPE);

Parameter: tickEventsQuery

This is a string with the actual SQL query used to retrieve the high frequency events. For instance:

declare @tickEventsQuery nvarchar(max) = 'select TimeS, [Last] from Trades_Final where Instrument=''AUD'' ORDER BY TimeS';

Parameter: tickFrom / tickTo

These two parameters provide the opening and closing time filter for the high frequency events provided in “tickEventsQuery”. Even high frequency event that is outside this range will get discarded. These parameters are optional, and can be provided in any combination:

For instance:

declare @tickFrom time(0) = '13:00';
declare @tickTo time(0) = '16:00';

Parameter: strategy

This is a script written in JScript that defines the strategy. The user can program anything in this script. However, one of the most useful techniques is to attach JScript functions to events. An events can be one of the following:

For every “Open” event there is a “Close” event, and vice versa (that’s the reason for the coloring in the table above).

Any JScript function with the name “event_X()” (where X is the name of the event) will only get executed when the event is triggered.

The columns of the low and high frequency rows (coming from the parameters xmldailyEvents and tickEventsQuery) are accessible through the JScript object “vars”, and the trading book is available through the JScript object “book”. Furthermore, to manipulate the book and guarantee that those actions get logged in “simulationOuput”, the user can access the JScript object “context” that exposes the following methods:

context.AddMark(string instrument, decimal p)
context.AddTrade(DateTime? settlement, string instrument, decimal q, decimal p, string comment)
context.AddCapital(decimal amount, string comment)
context.AddIncome(decimal amount, string comment)
context.AddBookTag(string tagName, string tagV)

Parameter: yieldAll

This is a debugging parameter. The default is 0, which will produce a “simulationOutput” containing only the rows that have values in the column [outputs]. When you pass a 1, “simulationOutut” will contain every single row. For performance reasons, a 1 should only be used when debugging the strategy.

An Example

Let’s assume that we want to run a strategy that will trade TY futures, during the week of 2008-01-14 to 2008-01-18.

• The front contract is the TYH08.

SELECT dbo.ssDateTimeToString('2008-01-14', 'yyyy-MM-dd dddd') as 'Day', dbo.ssToFrontContract( 'TY', '2008-01-14', null) as 'Front'
union all
SELECT dbo.ssDateTimeToString('2008-01-18', 'yyyy-MM-dd dddd') as 'Day', dbo.ssToFrontContract( 'TY', '2008-01-18', null) as 'Front'

• We can query the closing prices for that week:

select DateS, [Close] from DailyFut where Market='TY' and Expiry='H08' AND DateS >='2008-01-14' AND DateS<='2008-01-18' ORDER BY DateS

• These closing prices are the “Low frequency” events, which will be used to mark the book to market. Therefore, let’s convert the table to xml, and store it in the variable @xmldailyEvents for later use.

declare @xmldailyEvents xml = (
    select DateS, [Close] from DailyFut TS where Market='TY' and Expiry='H08' AND DateS >='2008-01-14' AND DateS<='2008-01-18' ORDER BY DateS
    FOR XML AUTO, root('ROOT'), xmlschema('type'), TYPE
);

• The “High frequency” events will be the minute prices (the following query returns 5,479 rows of tick data):

select TimeS, [Last] from Trades_Final where Instrument='TY' and TimeS>='2008-01-14' and TimeS<'2008-01-19' ORDER BY TimeS

• This sample strategy is very simple: It calculates the signal = (close - min)/(max - min) in the period between 9am and 1pm, and it takes a long position if the signal > 0.5, and a short if the signal < 0.5.

• Let’s program the strategy, and save the script in the @script variable:

declare @script nvarchar(max) = '...script...'

• Where ‘…script…’ is the following:

• Now we are ready to run the strategy, and save the simulation output in the table variable @so:

declare @so TABLE (
   [TimeS] datetime,
   [Event] xml,
   [Outputs] xml
);
declare @xmldailyEvents xml = (
    select DateS, [Close] from DailyFut TS where Market='TY' and Expiry='H08' AND DateS >='2008-01-14' AND DateS<='2008-01-18' order by DateS
    FOR XML AUTO, root('ROOT'), xmlschema('type'), TYPE
);
----SQLTable: Run_Strategy----
insert into @so
select * from dbo.ssRunStrategyS(
    @xmldailyEvents,
    'select TimeS, [Last] from Trades_Final where Instrument=''TY'' and TimeS>=''2008-01-14'' and TimeS<''2008-01-19'' ORDER BY TimeS',
    '9:00','13:00',
    @script,
    0
);
Select * from @so Order by TimeS;

• From @so it’s simple to extract the xml inputs for the Booking System:

select
    ROW_NUMBER() OVER (ORDER BY TimeS, BookCallsT.Seq) as Seq,
    TimeS,
    convert(nvarchar(max), BookCallsT.xout) as 'XmlCall'
from @o1
CROSS APPLY
(
    select *
    from dbo.ssUnlistXmlList(Outputs)
) BookCallsT;

• Which produces the following P&L

• If we had executed the function ssRunStrategyS(…) with the parameter yieldAll set to 1 (for debugging), we would have gotten not only the 9 events in which we took actions (and therefore with values in the “Outputs” column), but also all all the others:

select * from dbo.ssRunStrategyS(
    @xmldailyEvents,
    'select TimeS, [Last] from Trades_Final where Instrument=''TY'' and TimeS>=''2008-01-14'' and TimeS<''2008-01-19'' ORDER BY TimeS',
    '9:00','13:00',
    @script,
    1
);

(returned 1216 rows)