SQL Query for Calculating Trade Profits

Question

Trying to build a SQL script to calculate profit/loss percent per trade

Below is the original table

SELECT id, exchange, ticker, signal, time, price 
from trade_data 
ORDER BY exchange, ticker, time;

id	exchange	ticker	signal	time	price
3	BSE_DLY	UDAICEMENT	long	2021-08-05 22:00:43.800603	46.4
27	BSE_DLY	UDAICEMENT	close	2021-08-06 10:00:26.718044	47.1
24	NAS	TATAMOTORS	long	2021-08-06 05:45:16.384777	300.85
22	NAS	UDAICEMENT	long	2021-08-06 05:00:25.23066	47.75
4	NSE	NATIONALUM	long	2021-08-05 22:00:43.996958	94.8
26	NSE	NATIONALUM	close	2021-08-06 09:45:17.511157	93.65
2	NSE	NMDC	close	2021-08-05 22:00:43.600858	178.05
14	NSE	NMDC	long	2021-08-06 03:45:11.243861	179.45
5	NSE	SAIL	long	2021-08-05 22:00:44.197164	140.4
25	NSE	SAIL	close	2021-08-06 09:45:17.441593	140.5
1	NSE	TATACONSUM	close	2021-08-05 21:57:56.840043	761
13	NSE	TATACONSUM	long	2021-08-06 03:37:38.371882	773
15	NSE	TATAMOTORS	long	2021-08-06 03:45:12.891371	300.65

Formula for calculating profit/loss percent

profit % =   (close_price-entry_price)   *  100
            ---------------------------
                 entry_price

Print profit as 0 if Trade Entry is not available

The resulting dataset should look like below

id	exchange	ticker	signal	time	price	profit %
3	BSE_DLY	UDAICEMENT	long	2021-08-05 22:00:43.800603	46.4
27	BSE_DLY	UDAICEMENT	close	2021-08-06 10:00:26.718044	47.1	1.5
24	NAS	TATAMOTORS	long	2021-08-06 05:45:16.384777	300.85
22	NAS	UDAICEMENT	long	2021-08-06 05:00:25.23066	47.75
4	NSE	NATIONALUM	long	2021-08-05 22:00:43.996958	94.8
26	NSE	NATIONALUM	close	2021-08-06 09:45:17.511157	93.65	-1.21
2	NSE	NMDC	close	2021-08-05 22:00:43.600858	178.05	0
14	NSE	NMDC	long	2021-08-06 03:45:11.243861	179.45
5	NSE	SAIL	long	2021-08-05 22:00:44.197164	140.4
25	NSE	SAIL	close	2021-08-06 09:45:17.441593	140.5	0.07
1	NSE	TATACONSUM	close	2021-08-05 21:57:56.840043	761	0
13	NSE	TATACONSUM	long	2021-08-06 03:37:38.371882	773
15	NSE	TATAMOTORS	long	2021-08-06 03:45:12.891371	300.65

Answer 1

I guess you could use the LAG function to achieve this. Idea would be to check the previous signal for specific exchange/ticker combination. If current signal is equal to 'close' and there is no previous record (I guess that would be signal = 'long') then we put 0 and if there is previous record then we do the math. And for signal = 'long' we do nothing.

Please check if this is acceptable.

select id, exchange, ticker, signal, time, price,
    case 
        when signal = 'close' and prev_signal is null then 0 
        when signal = 'close' then round(((price-prev_price)*100)/price, 2)
    end as profit
from (
    select id, exchange, ticker, signal, time, price, 
        lag(signal) OVER (PARTITION BY exchange, ticker ORDER BY time) as prev_signal,
        lag(price) OVER (PARTITION BY exchange, ticker ORDER BY time) as prev_price
    from trade_Data
) as t0