FIFO Profit Calculation in T-SQL

Question

I need some help or pointers with a single query, that can build a profit/loss for each individual stock trade based on FIFO (first-in-first-out) principle on the dataset attached below. Should be compatible with SQL Server 2016+ and Azure SQL.

Example data looks like this:

StockName	TransactionDate	TranCode	Quantity	PriceUSD	TotalAmountUSD
StockABC	2017-12-11 11:16:11.000	BUY	2364,444444	0,114323	270,310382
StockXYZ	2017-12-11 11:16:11.000	BUY	2364,444444	0,114323	270,310382
StockABC	2017-12-14 14:16:24.000	SELL	1000	0,158849	158,849
StockXYZ	2017-12-14 14:16:24.000	SELL	1000	0,158849	158,849
StockABC	2017-12-14 19:38:46.000	SELL	700	0,198934	139,2538
StockXYZ	2017-12-14 19:38:46.000	SELL	700	0,198934	139,2538
StockABC	2017-12-15 09:38:09.000	SELL	664,4444444	0,207171	137,65362

I need a calculation, that calculates profit for each individual trade.

E.g. if i buy 10 of stockABC at price 5 and buy 10 later to price 6, and i sell all 15 at price 9, i need to profit calculated, taking in regard price for for BUYS.

I have tried to play around with CTE's cursors and the likes, but i have only been able to find help regarding Totals on quantities.

Overall data amount is low (> 50000 rows in total), so don't put too much regard in performance.

Test data can be inserted from below:

CREATE TABLE dbo.Stock 
(
    Currency VARCHAR(50),
    TransactionDate DATETIME,
    TranCode VARCHAR(5), 
    Quantity NUMERIC(38,18),
    Price NUMERIC(38,18),
    CostBasis NUMERIC(38,18)
)

INSERT INTO dbo.Stock (Currency, TransactionDate, TranCode, Quantity, Price, CostBasis)
    SELECT * 
    FROM
        (SELECT 
             'StockABC' AS Currency,
             'Dec 11 2017 11:16AM' AS TransactionDate,
             'BUY' AS TranCode, 
             '2364.444444440000000000' AS Quantity, 
             '0.114323000000000000' AS Price,   
             '270.310382000000000000' AS CostBasis 
         UNION
         SELECT 
             'StockABC' AS Currency,    
             'Dec 14 2017  2:16PM' AS TransactionDate,  
             'SELL' AS TranCode,    
             '1000.000000000000000000' AS Quantity, 
             '0.158849000000000000' AS Price,   
             '158.849000000000000000' AS CostBasis 
         UNION
         SELECT 
             'StockABC' AS Currency,    
             'Dec 14 2017  7:38PM' AS TransactionDate,  
             'SELL' AS TranCode,    
             '700.000000000000000000' AS Quantity,  
             '0.198934000000000000' AS Price,   
             '139.253800000000000000' AS CostBasis 
         UNION
         SELECT 
             'StockABC' AS Currency,    
             'Dec 15 2017  9:38AM' AS TransactionDate,  
             'SELL' AS TranCode,    
             '664.444444440000000000' AS Quantity,  
             '0.207171000000000000' AS Price,   
             '137.653620000000000000' AS CostBasis 
         UNION
         SELECT 
             'StockABC' AS Currency,    
             'Dec 18 2017 11:34AM' AS TransactionDate,  
             'BUY' AS TranCode, 
             '1334.531919170000000000' AS Quantity, 
             '0.489515000000000000' AS Price,   
             '653.273392000000000000' AS CostBasis 
         UNION
         SELECT 
             'StockABC' AS Currency,    
             'Dec 21 2017  9:02PM' AS TransactionDate,  
             'SELL' AS TranCode,    
             '334.531919170000000000' AS Quantity,  
             '0.473771000000000000' AS Price,   
             '158.491522000000000000' AS CostBasis 
         UNION
         SELECT 
             'StockABC' AS Currency,    
             'Dec 26 2017 10:44AM' AS TransactionDate,  
             'BUY' AS TranCode, 
             '400.000000000000000000' AS Quantity,  
             '0.440974000000000000' AS Price,   
             '176.389600000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Dec 26 2017 10:45AM' AS TransactionDate,   'SELL' AS TranCode, '399.000000000000000000' AS Quantity,   '0.438398000000000000' AS Price,    '174.920802000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Dec 30 2017 11:34AM' AS TransactionDate,   'SELL' AS TranCode, '500.000000000000000000' AS Quantity,   '0.633751000000000000' AS Price,    '316.875500000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Jan  3 2018  5:45PM' AS TransactionDate,   'SELL' AS TranCode, '20.000000000000000000' AS Quantity,    '1.056470000000000000' AS Price,    '21.129400000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Jan 10 2018  8:48AM' AS TransactionDate,   'BUY' AS TranCode,  '292.310860920000000000' AS Quantity,   '0.750911000000000000' AS Price,    '219.499441000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Mar  6 2018  6:51PM' AS TransactionDate,   'BUY' AS TranCode,  '700.495360820000000000' AS Quantity,   '0.283468000000000000' AS Price,    '198.568019000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Mar 21 2018  3:42PM' AS TransactionDate,   'SELL' AS TranCode, '1472.806221740000000000' AS Quantity,  '0.217339000000000000' AS Price,    '320.098231000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Apr 11 2018  6:03PM' AS TransactionDate,   'BUY' AS TranCode,  '910.127737230000000000' AS Quantity,   '0.164000000000000000' AS Price,    '149.260949000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Apr 16 2018  7:53AM' AS TransactionDate,   'SELL' AS TranCode, '450.000000000000000000' AS Quantity,   '0.235482000000000000' AS Price,    '105.966900000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Apr 24 2018  8:15PM' AS TransactionDate,   'SELL' AS TranCode, '460.127737230000000000' AS Quantity,   '0.297748000000000000' AS Price,    '137.002114000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Jul 13 2018  8:08PM' AS TransactionDate,   'BUY' AS TranCode,  '1500.009200180000000000' AS Quantity,  '0.133000000000000000' AS Price,    '199.501224000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Jul 22 2018  5:08PM' AS TransactionDate,   'SELL' AS TranCode, '500.000000000000000000' AS Quantity,   '0.174302000000000000' AS Price,    '87.151000000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Aug  5 2018  5:34PM' AS TransactionDate,   'SELL' AS TranCode, '1000.009200180000000000' AS Quantity,  '0.127404000000000000' AS Price,    '127.405172000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Sep 22 2018  1:29PM' AS TransactionDate,   'BUY' AS TranCode,  '1810.263568330000000000' AS Quantity,  '0.082654000000000000' AS Price,    '149.625525000000000000' AS CostBasis                     
         UNION 

SELECT 'StockABC' AS Currency,  'Nov 25 2018  2:55AM' AS TransactionDate,   'BUY' AS TranCode,  '3000.000000000000000000' AS Quantity,  '0.035000000000000000' AS Price,    '105.000000000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Feb 11 2019  9:07AM' AS TransactionDate,   'BUY' AS TranCode,  '1782.043635700000000000' AS Quantity,  '0.042000000000000000' AS Price,    '74.845833000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Mar  9 2019  2:19PM' AS TransactionDate,   'BUY' AS TranCode,  '4190.000000000000000000' AS Quantity,  '0.043930000000000000' AS Price,    '184.066700000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Apr 25 2019  5:41PM' AS TransactionDate,   'BUY' AS TranCode,  '2261.000000000000000000' AS Quantity,  '0.075000000000000000' AS Price,    '169.575000000000000000' AS CostBasis 
         UNION

SELECT 'StockABC' AS Currency,  'Sep  9 2019  5:06PM' AS TransactionDate,   'BUY' AS TranCode,  '2200.000000000000000000' AS Quantity,  '0.045927000000000000' AS Price,    '101.039400000000000000' AS CostBasis ) A

FYI, you've been able to use a `VALUES` table construct in an `INSERT` statement since 2008; no need to use an expensive `UNION`, nor use a less expensive `UNION ALL` any more. — Thom A, Jan 28 '21 at 15:03
Also, the numeric columns should **NOT** be put into single quotes in your `INSERT` statement - that just causes an unnecessary conversion to string, and then back again to the appropriate numeric value - just use `158.849 AS CostBasis` - no single quotes! — marc_s, Jan 28 '21 at 15:18

SteveC · Accepted Answer · 2021-01-28T19:45:26.960

Here's a simple way (sort of) which uses a tally function called dbo.fnTally to generate 1 row for each item or "share" of the Quantity. Both 'buy_cte' and 'sell_cte' uses CROSS APPLY dbo.fnTally(1, s.Quantity) to expand the rows for each item of quantity. Also both CTE's assign an ordinal row_number called 'trans_rn'. By joining the two CTE's on 'trans_rn' the FIFO profitability can be calculated by just summing the prices. Something like this

dbo.fnTally

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    )V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8;

;with 
buy_cte(Currency, TransactionDate, Price, trans_rn) as (
    select Currency, TransactionDate, Price,
           row_number() over (partition by Currency order by TransactionDate)
    from #Stock s
         cross apply dbo.fnTally(1, s.Quantity) fn
    where TranCode='BUY'),
sell_cte(Currency, TransactionDate, Price, trans_rn) as (
    select Currency, TransactionDate, Price,
           row_number() over (partition by Currency order by TransactionDate)
    from #Stock s
         cross apply dbo.fnTally(1, s.Quantity) fn
    where TranCode='SELL')
select s.Currency, s.TransactionDate, 
       cast(sum(b.price) as decimal(14,2)) buy_sum, 
       cast(sum(s.price) as decimal(14,2)) sell_sum, 
       cast(sum(s.price-b.price) as decimal(14,2)) profit_sum, 
       count(*) q_sold
from buy_cte b
     join sell_cte s on b.Currency=s.Currency
                    and b.trans_rn=s.trans_rn
                    and b.TransactionDate<s.TransactionDate
group by s.Currency, s.TransactionDate
order by TransactionDate;

Currency    TransactionDate         buy_sum sell_sum    profit_sum  q_sold
StockABC    2017-12-14 14:16:00.000 114.32  158.85      44.53       1000
StockABC    2017-12-14 19:38:00.000 80.03   139.25      59.23       700
StockABC    2017-12-15 09:38:00.000 75.91   137.56      61.65       664
StockABC    2017-12-21 21:02:00.000 163.50  158.24      -5.26       334
StockABC    2017-12-26 10:45:00.000 195.32  174.92      -20.40      399
StockABC    2017-12-30 11:34:00.000 244.76  316.88      72.12       500
StockABC    2018-01-03 17:45:00.000 9.79    21.13       11.34       20
StockABC    2018-03-21 15:42:00.000 633.45  319.92      -313.53     1472
StockABC    2018-04-16 07:53:00.000 73.92   105.97      32.05       450
StockABC    2018-04-24 20:15:00.000 75.44   136.96      61.52       460
StockABC    2018-07-22 17:08:00.000 66.53   87.15       20.62       500
StockABC    2018-08-05 17:34:00.000 133.00  127.40      -5.60       1000

Hi SteveC. Thank you very much for this. This is exactly what I was looking for. Much appreciated! — Benny Christiansen, Jan 29 '21 at 09:09

Jiacheng Gao · Answer 2 · 2021-01-28T18:10:07.130

0

Thanks for the feedback, and let me make some changes.

SELECT Currency,TransactionDate,
SUM(CASE WHEN TranCode = 'SELL' THEN CostBasis * (-1) ELSE CostBasis END )OVER(PARTITION BY Currency ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTAL
FROM #Stock
GROUP BY Currency, TransactionDate, CostBasis, TranCode

edited Jan 28 '21 at 18:10

answered Jan 28 '21 at 15:17

Jiacheng Gao

365
3
9

Hi, thank you for your suggestion. But this does not does solve what I need. Please read "I need a calculation, that calculates profit for each individual trade. E.g. if i buy 10 of stockABC at price 5 and buy 10 later to price 6, and i sell all 15 at price 9, i need to profit calculated, taking in regard price for for BUYS." Meaning i need to see the profit/loss for each time i sell, based on the price from the first available purchase, – Benny Christiansen Jan 28 '21 at 17:36
@BennyChristiansen hi there, I've update answers, please check and let me know if there is another issue. thanks – Jiacheng Gao Jan 28 '21 at 18:10
Hi again. Thanks again, but the numbers does not seem to match. Take the "SELL" from '2017-12-14 19:38:00.000'. 700 QTY sold with price 0.198934000000000000, and bought in the batch from 2017-12-11 11:16:00.000 @ price 0.114323000000000000, gives a negative profit in your calculation. That should be positive profit, since the 700 QTY where bought at almost half the price as they were sold to. :) I've been using two days on this, without finding anything :D – Benny Christiansen Jan 28 '21 at 18:54
ok, then what about change 'SELL' to 'BUY' – Jiacheng Gao Jan 28 '21 at 19:09

FIFO Profit Calculation in T-SQL

2 Answers2

Linked