I am attempting to create a sqlite query to process stocks buy sell transaction based on FIFO method to track summary of counters performance.
There are 2 queries written so far:
WITH Purchase AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY item ORDER BY date) AS rn FROM trans_fifo WHERE txtype = 'buy'
),
Dispose AS (
SELECT item, qty, amount ,ROW_NUMBER() OVER (PARTITION BY item ORDER BY date) AS s FROM trans_fifo WHERE txtype = 'sell'
)
SELECT
p.item Counter, round(d.amount - p.amount,2) PL
FROM
Purchase p
LEFT JOIN
Dispose d
ON
p.item = d.item and p.qty = d.qty and p.rn = d.s
WHERE PL NO NULL
;
Produce this output:
Counter PL
------- ------
AEMULUS 47.74
ARMADA 103.84
BAUTO 162.84
CIMB 150.48
D&O 85.49
EFORCE 90.46
HEVEA 200.55
IRIS 55.42
JCY 36.0
JCY -261.9
KPJ 191.9
MBMR 273.25
MBMR 173.12
MBSB 22.77
RSAWIT 173.3
SUCCESS 110.88
TNLOGIS 120.38
VELESTO 127.0
WITH Purchase AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY item ORDER BY date) AS rn FROM trans_fifo WHERE txtype = 'buy'
),
Dispose AS (
SELECT item, qty, amount ,ROW_NUMBER() OVER (PARTITION BY item ORDER BY date) AS s FROM trans_fifo WHERE txtype = 'sell'
)
SELECT
p.item Counter, round(d.amount - sum(p.amount),2) PL
FROM
Purchase p
LEFT JOIN
Dispose d
ON
p.item = d.item and p.qty <> d.qty
GROUP BY p.item
;
Produce this output
Counter PL
------- ------
AEMULUS [NULL]
ARMADA [NULL]
ARREIT [NULL]
BAUTO [NULL]
CIMB [NULL]
D&O [NULL]
EFORCE [NULL]
GENM [NULL]
HEVEA [NULL]
IGBREIT [NULL]
IRIS [NULL]
JCY [NULL]
KPJ [NULL]
MBMR [NULL]
MBSB [NULL]
N2N 57.21
RSAWIT [NULL]
SUCCESS [NULL]
SUNREIT [NULL]
TNLOGIS [NULL]
TWREIT [NULL]
VELESTO [NULL]
VIS 331.69
YTLPOWR 88.19
My request is how could I make use of these queries (or please suggest what are the better way to address this scenario) to compose an efficient query to produce a output to include both scenarios in one summary like below ?
Counter PL
------- ------
AEMULUS 47.74
ARMADA 103.84
BAUTO 162.84
CIMB 150.48
D&O 85.49
EFORCE 90.46
HEVEA 200.55
IRIS 55.42
JCY 36.0
JCY -261.9
KPJ 191.9
MBMR 273.25
MBMR 173.12
MBSB 22.77
RSAWIT 173.3
SUCCESS 110.88
TNLOGIS 120.38
VELESTO 127.0
N2N 57.21
VIS 331.69
YTLPOWR 88.19
Sample data trans_fifo table for testing below:
item date txtype qty price amount
------- ---------- ------ ----- ----- -------
AEMULUS 2020-09-17 buy 1000 0.67 678.62
AEMULUS 2020-10-23 sell 1000 0.735 726.36
ARMADA 2020-06-01 buy 2000 0.24 487.56
ARMADA 2020-09-02 sell 2000 0.3 591.4
ARREIT 2020-06-24 buy 2000 0.68 1370.95
CIMB 2021-01-21 buy 1000 3.98 3994.73
CIMB 2021-02-10 sell 1000 4.16 4145.21
D&O 2020-12-31 buy 1000 2.34 2352.24
D&O 2021-01-05 sell 1000 2.45 2437.73
EFORCE 2020-06-16 buy 2000 0.455 918.69
EFORCE 2020-07-28 sell 2000 0.51 1009.15
GENM 2020-02-26 buy 2000 2.93 5877.3
GENM 2020-12-07 buy 2000 2.56 5136.08
HEVEA 2020-09-11 buy 5000 0.435 2187.19
HEVEA 2020-09-29 sell 5000 0.48 2387.74
IGBREIT 2020-06-26 buy 1000 1.81 1822.08
IGBREIT 2021-01-08 buy 1000 1.68 1692.04
IGBREIT 2021-01-13 buy 1000 1.64 1652.03
IRIS 2020-07-03 buy 5000 0.2 1008.72
IRIS 2020-07-06 sell 5000 0.215 1064.14
JCY 2020-09-09 buy 2000 0.75 1511.99
JCY 2020-09-09 sell 2000 0.78 1547.99
JCY 2020-09-10 buy 2000 0.745 1500.99
JCY 2020-11-24 sell 2000 0.625 1239.09
KPJ 2020-10-22 buy 1800 0.88 1596.02
KPJ 2020-12-07 sell 1800 1.0 1787.92
MBMR 2020-10-30 buy 1000 2.63 2643.33
MBMR 2020-11-05 sell 1000 2.93 2916.58
MBMR 2020-11-10 buy 1000 2.9 2913.41
MBMR 2020-11-13 sell 1000 3.1 3086.53
MBSB 2021-01-13 buy 1000 0.625 633.61
MBSB 2021-02-16 sell 1000 0.665 656.38
N2N 2020-09-04 buy 2000 0.795 1602.02
N2N 2020-09-07 buy 1000 0.775 783.65
N2N 2020-09-10 buy 1000 0.695 703.63
N2N 2021-02-10 sell 4000 0.79 3146.51
RSAWIT 2020-09-24 buy 10000 0.26 2613.32
RSAWIT 2020-10-12 sell 10000 0.28 2786.62
SUCCESS 2020-07-27 buy 3000 0.56 1692.04
SUCCESS 2020-08-07 sell 3000 0.605 1802.92
SUCCESS 2021-01-22 buy 2000 0.79 1592.01
SUCCESS 2021-01-26 buy 2000 0.75 1511.99
SUCCESS 2021-02-18 buy 4000 0.74 2973.43
SUNREIT 2020-05-28 buy 1000 1.57 1582.01
SUNREIT 2020-10-06 buy 1000 1.5 1511.99
SUNREIT 2021-01-06 buy 1000 1.4 1410.96
TNLOGIS 2021-02-04 buy 5000 0.83 4164.79
TNLOGIS 2021-02-08 sell 5000 0.86 4285.17
TWREIT 2020-06-07 buy 2000 0.705 1420.96
VELESTO 2020-08-07 buy 10000 0.145 1460.98
VELESTO 2021-02-17 sell 10000 0.16 1587.98
VIS 2020-09-30 buy 5000 0.48 2412.26
VIS 2020-10-07 buy 1000 0.445 452.55
VIS 2020-12-07 sell 6000 0.535 3196.5
YTLPOWR 2020-02-07 buy 1000 0.725 733.64
YTLPOWR 2020-02-26 buy 2000 0.695 1400.96
YTLPOWR 2020-10-23 sell 3000 0.745 2222.79
BAUTO 2021-02-04 buy 5000 1.34 6718.55
BAUTO 2021-02-08 sell 5000 1.38 6881.39
trans_fifo schema below:
sqlite> .schema trans_fifo
CREATE VIEW trans_fifo
AS
SELECT
--broker.name as broker,
--investor.name as investor,
stock.counter as item,
trans.date as date,
type.tname as txtype,
trans.unit as qty,
trans.price as price,
trans.amount as amount
FROM
trans
INNER JOIN investor on investor.investor_id=trans.investor_id
INNER JOIN broker on broker.broker_id=trans.broker_id
INNER JOIN stock on stock.stock_id=trans.stock_id
INNER JOIN type on type.ttype=trans.ttype
WHERE
investor.name = 'SOON' AND broker.name = 'RAKUTEN'
-- stock.counter = 'SLP' AND investor.investor_id=1
ORDER BY
trans.broker_id ASC,
trans.stock_id ASC
/* trans_fifo(item,date,txtype,qty,price,amount) */;
Thank you and appreciate your time to shade some light and insight
After figuring out a few days, here is the solution on my own posting:
The solution utilise sqlite window frame functions to add caculation/manipulation variables to achieve the purpose.
WITH Purchase AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY item ORDER BY date) AS rn,
COUNT(item) OVER (PARTITION BY item) AS crn,
SUM(qty) OVER (PARTITION BY item ORDER By item, date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS bqsum,
SUM(amount) OVER (PARTITION BY item ORDER By item, date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS amsum
FROM trans_fifo WHERE txtype = 'buy'
),
Dispose AS (
SELECT item, date, qty, amount,
ROW_NUMBER() OVER (PARTITION BY item ORDER BY date) AS s
FROM trans_fifo WHERE txtype = 'sell' --GROUP BY item
)
SELECT
p.item,
(CASE
WHEN
p.item=out.item AND p.qty = d.qty AND p.rn = d.s THEN round(d.amount - p.amount,2)
WHEN
p.item=d.item AND p.bqsum = d.qty AND d.date >= p.date THEN round(d.amount - p.amsum,2)
END) PL
FROM
Purchase p
LEFT JOIN
Dispose d
ON
p.item = d.item
WHERE
PL IS NOT NULL
Here is the end result:
item PL
------- ------
AEMULUS 47.74
ARMADA 103.84
BAUTO 162.84
CIMB 150.48
D&O 85.49
EFORCE 90.46
HEVEA 200.55
IRIS 55.42
JCY 36.0
JCY -261.9
KPJ 191.9
MBMR 273.25
MBMR 173.12
MBSB 22.77
N2N 57.21
RSAWIT 173.3
SUCCESS 110.88
SUCCESS 265.12
TNLOGIS 120.38
VELESTO 127.0
VIS 331.69
YTLPOWR 88.19