Lets imagine you have the following table called Table1 of Orders in chronological order returned from an In-line UDF. Please note that the OrderID may be out of sync so I have intentionally created an anomaly there (i.e. I have not included the Date field but I have access to the column if easier for you).
OrderID BuySell FilledSize ExecutionPrice RunningTotal AverageBookCost RealisedPnL
339 Buy 2 24.5 NULL NULL NULL
375 Sell 3 23.5 NULL NULL NULL
396 Sell 3 20.5 NULL NULL NULL
416 Sell 1 16.4 NULL NULL NULL
405 Buy 4 18.2 NULL NULL NULL
421 Sell 1 16.7 NULL NULL NULL
432 Buy 3 18.6 NULL NULL NULL
I have a function that I would like to apply recursively from the top to the bottom that will calculate the 3 NULL columns, however the imputs into the function will be the outputs from the previous call. The function I have created is called mfCalc_RunningTotalBookCostPnL and I have attached this below
CREATE FUNCTION [fMath].[mfCalc_RunningTotalBookCostPnL](
@BuySell VARCHAR(4),
@FilledSize DECIMAL(31,15),
@ExecutionPrice DECIMAL(31,15),
@OldRunningTotal DECIMAL(31,15),
@OldBookCost DECIMAL(31,15)
)
RETURNS @ReturnTable TABLE(
NewRunningTotal DECIMAL(31,15),
NewBookCost DECIMAL(31,15),
PreMultRealisedPnL DECIMAL(31,15)
)
AS
BEGIN
DECLARE @SignedFilledSize DECIMAL(31,15),
@NewRunningTotal DECIMAL(31,15),
@NewBookCost DECIMAL(31,15),
@PreMultRealisedPnL DECIMAL(31,15)
SET @SignedFilledSize = fMath.sfSignedSize(@BuySell, @FilledSize)
SET @NewRunningTotal = @OldRunningTotal + @SignedFilledSize
SET @PreMultRealisedPnL = 0
IF SIGN(@SignedFilledSize) = SIGN(@OldRunningTotal)
-- This Trade is adding to the existing position.
SET @NewBookCost = (@SignedFilledSize * @ExecutionPrice +
@OldRunningTotal * @OldBookCost) / (@NewRunningTotal)
ELSE
BEGIN
-- This trade is reversing the existing position.
-- This could be buying when short or selling when long.
DECLARE @AbsClosedSize DECIMAL(31,15)
SET @AbsClosedSize = fMath.sfMin(ABS(@SignedFilledSize), ABS(@OldRunningTotal));
-- There must be Crystalising of PnL.
SET @PreMultRealisedPnL = (@ExecutionPrice - @OldBookCost) * @AbsClosedSize * SIGN(-@SignedFilledSize)
-- Work out the NewBookCost
SET @NewBookCost = CASE
WHEN ABS(@SignedFilledSize) < ABS(@OldRunningTotal) THEN @OldBookCost
WHEN ABS(@SignedFilledSize) = ABS(@OldRunningTotal) THEN 0
WHEN ABS(@SignedFilledSize) > ABS(@OldRunningTotal) THEN @ExecutionPrice
END
END
-- Insert values into Return Table
INSERT INTO @ReturnTable
VALUES (@NewRunningTotal, @NewBookCost, @PreMultRealisedPnL)
-- Return
RETURN
END
So the t-SQL command I am looking for (I dont mind if someone can creates an Outer Apply too) would generate the following Result/Solution set:
OrderID BuySell FilledSize ExecutionPrice RunningTotal AverageBookCost RealisedPnL
339 Buy 2 24.5 2 24.5 0
375 Sell 3 23.5 -1 23.5 -2
396 Sell 3 20.5 -4 21.25 0
416 Sell 1 16.4 -5 20.28 0
405 Buy 4 18.2 -1 20.28 8.32
421 Sell 1 16.7 -2 18.49 0
432 Buy 3 18.6 1 18.6 -0.29
A few notes, the above stored procedure calls a trivial function fMath.sfSignedSize which just makes ('Sell',3) = -3. Also, for the avoidance of doubt, I would see the solution making these calls in this order assuming I am correct in my calculations! (Note that I start off assuming the OldRunningTotal and OldBookCost are both zero):
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Buy',2,24.5,0,0)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',3,23.5,2,24.5)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',3,20.5,-1,23.5)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',1,16.4,-4,21.25)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Buy',4,18.2,-5,20.28)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',1,16.7,-1,20.28)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Buy',3,18.6,-2,18.49)
Obviously, the [fMath].[mfCalc_RunningTotalBookCostPnL] may need to be tweaked so that it can start off with NULL entries as the OldRunningTotal and OldBookCost but this is trivially done. The SQL Set theory of applying the resursive nature is a little harder.
Many thanks,
Bertie.
