Suppose that you do the following trades:
- On Monday 2008-06-23 11AM buy EUR 100,000 at 1.2853
- On Tuesday 2008-06-24 11AM sell EUR 100,000 at 1.2873
Congratulations! You’ve made 20 pips, for a profit of (1.2873-1.2853)*100,000 = $200.
But wait… there is more. The trades above have the following settlements:
- Wednesday 2008-06-25
- Thursday 2008-06-26
That means that while you closed the position on Tuesday 2008-06-24, you still need to roll from Wednesday to Thursday.
The function ssCurrencyRoll(currency,BPSpread ) calculates the rolls, using the appropriate LIBOR curves.
select top 5 *
from dbo.ssCurrencyRoll('EUR', 12.5)
where DateS>'2008-06-19' order by DateS asc
We can see that the bid side of the roll is $9.20 for every 100,000.
Then, the final P&L of the trade described above will actually be $209.20.
The good news is that SAMOA can take care of these steps automatically.
- First, let’s create the book with the function ssBookXml and store it in the table variable @EURUSD_Long_20Pips_TradingBook_Xml (for more information on this function, please read this blog)
declare @EURUSD_Long_20Pips_XmlCalls_Inline table (
Seq bigint IDENTITY(1,1)
,TimeS datetime
,XmlCall nvarchar(max)
);
insert into @EURUSD_Long_20Pips_XmlCalls_Inline (TimeS, XmlCall) VALUES
('2008-06-23 11:00', '<Trade TimeS="2008-06-23 11:00" Instrument="EUR" Q="100000" P="1.2853" Comment="Open" />' ),
('2008-06-23', '<Mark DateS="2008-06-23" Instrument="EUR" P="1.3" />' ),
(’2008-06-24 11:00', '<Trade TimeS="2008-06-24 11:00" Instrument="EUR" Q="-100000" P="1.2873" Comment="Close" />' ),
('2008-06-24', '<Mark DateS="2008-06-24" Instrument="EUR" P="1.28" />' ),
('2008-06-25', '<Mark DateS="2008-06-25" Instrument="EUR" P="1.28" />' );
----SQLTable: EURUSD_Long_20Pips_XmlCalls----
declare @EURUSD_Long_20Pips_XmlCalls table (
[Seq] bigint
,[XmlCall] nvarchar(1024)
);
insert into @EURUSD_Long_20Pips_XmlCalls
select * from dbo.ssXmlCalls(
(select TS.* from @EURUSD_Long_20Pips_XmlCalls_Inline TS FOR XML AUTO, xmlschema('type'), root ('ROOT'), TYPE),
NULL,NULL,NULL,NULL
);
declare @EURUSD_Long_20Pips_TradingBook_Xml xml;
select @EURUSD_Long_20Pips_TradingBook_Xml = dbo.ssBookXml(
(select TS.* from @EURUSD_Long_20Pips_XmlCalls TS FOR XML AUTO, xmlschema('type'), root ('ROOT'), TYPE)
);
Select @EURUSD_Long_20Pips_TradingBook_Xml as ScalarVariable;
- Now let’s query the final P&L:
select
MarkMonth.value('@DateS', 'datetime') as DateS,
MarkMonth.value('@AccPL', 'decimal(38,10)') as AccPL
from @EURUSD_Long_20Pips_TradingBook_Xml.nodes('/Book/MarkMonth') MarkMonths(MarkMonth)
We can wee that the P&L is correct, because the roll was added automatically.
-
If we list the income streams we’ll see the roll:
select
Income.value('@Seq', 'bigint') as [Seq],
Income.value('@DateS', 'datetime') as DateS,
Income.value('@Amount', 'decimal(38,10)') as Amount,
Income.value('@Comment', 'nvarchar(100)') as Comment
from @EURUSD_Long_20Pips_TradingBook_Xml.nodes('/Book/MarkMonth/MarkDay/Income') Incomes(Income);
