Query to find financial drawdown
-
Hi,
I'm looking for a query on some financial data to show me the largest drawdown that has occurred on an account. Drawdown is defined as the largest percent loss in equity that has happened over a series of trades.
I do not have the account balances, but I do have the percent change on the account per trade. From this the largest drawdown in percentage terms should be able to be calculated. The data I have is tradeId, market, tradeDateTime, pctAccountChange. In the sample data below, you could seed the account with any number and at the end get a final account balance. So, if we start with $1,000 we could see the final balance being $1,607.16. I need a query that returns the largest drawdown in terms of percentage of the account. You could think of it as the largest percentage decrease in the account equity from a peak to a trough. I hope this makes sense.
Here's the sample data and table definition. You can ignore the market as that's not relevant, really.
-- schemaIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[accountPerformance]') AND type in (N'U'))
DROP TABLE [dbo].[accountPerformance]
GO
CREATE TABLE [dbo].[accountPerformance](
[tradeId] [int] NOT NULL,
[market] [char](6) NOT NULL,
[tradeDateTime] [datetime] NOT NULL,
[pctAccountChange] [decimal](38, 6) NOT NULL,
CONSTRAINT [PK_accountPerformance] PRIMARY KEY NONCLUSTERED
([tradeId] ASC)
)
GO
CREATE CLUSTERED INDEX [CIX_accountPerformance] ON [dbo].[accountPerformance]
(
[tradeDateTime] ASC
)
GO
-- sample data
set nocount on
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(17,'GBPUSD','Jan 7 2010 11:00:00:000AM',-2.000000)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(18,'GBPUSD','Jan 18 2010 3:00:00:000AM',0.275862)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(1,'EURUSD','Jan 19 2010 11:00:00:000AM',1.375000)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(39,'USDJPY','Jan 27 2010 3:00:00:000AM',-0.987500)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(40,'USDJPY','Jan 28 2010 3:00:00:000PM',-1.154638)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(2,'EURUSD','Jan 28 2010 3:00:00:000PM',0.697986)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(19,'GBPUSD','Jan 29 2010 11:00:00:000AM',1.379746)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(3,'EURUSD','Feb 3 2010 7:00:00:000PM',3.764226)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(20,'GBPUSD','Feb 4 2010 7:00:00:000AM',2.225988)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(41,'USDJPY','Feb 10 2010 11:00:00:000AM',-1.588234)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(4,'EURUSD','Feb 15 2010 7:00:00:000AM',-0.326796)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(42,'USDJPY','Feb 17 2010 7:00:00:000AM',1.422534)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(21,'GBPUSD','Feb 18 2010 7:00:00:000AM',2.426966)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(23,'GBPUSD','Feb 23 2010 7:00:00:000AM',1.074074)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(5,'EURUSD','Feb 23 2010 3:00:00:000PM',1.168674)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(43,'USDJPY','Feb 25 2010 3:00:00:000AM',0.797752)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(44,'USDJPY','Mar 12 2010 3:00:00:000AM',-0.333332)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(6,'EURUSD','Mar 19 2010 3:00:00:000PM',0.418438)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(24,'GBPUSD','Mar 23 2010 7:00:00:000AM',2.473684)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(45,'USDJPY','Mar 24 2010 11:00:00:000AM',2.290000)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(7,'EURUSD','Apr 6 2010 4:00:00:000AM',2.153846)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(25,'GBPUSD','Apr 13 2010 7:00:00:000AM',0.100718)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(26,'GBPUSD','Apr 20 2010 7:00:00:000AM',-0.804122)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(8,'EURUSD','Apr 20 2010 3:00:00:000PM',3.047058)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(27,'GBPUSD','Apr 26 2010 7:00:00:000AM',-0.639784)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(9,'EURUSD','Apr 27 2010 3:00:00:000PM',1.128000)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(28,'GBPUSD','May 3 2010 3:00:00:000AM',8.554544)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(10,'EURUSD','May 3 2010 7:00:00:000AM',8.530000)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(11,'EURUSD','May 11 2010 11:00:00:000PM',2.570652)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(29,'GBPUSD','Jun 8 2010 11:00:00:000AM',-0.901162)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(30,'GBPUSD','Jun 18 2010 7:00:00:000AM',-0.681318)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(31,'GBPUSD','Jun 25 2010 3:00:00:000PM',0.642156)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(12,'EURUSD','Jul 5 2010 7:00:00:000AM',-0.145250)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(13,'EURUSD','Jul 7 2010 11:00:00:000PM',-1.012578)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(14,'EURUSD','Jul 14 2010 7:00:00:000PM',2.636362)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(32,'GBPUSD','Jul 23 2010 3:00:00:000AM',5.094488)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(15,'EURUSD','Aug 20 2010 7:00:00:000AM',-0.919354)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(33,'GBPUSD','Aug 31 2010 11:00:00:000AM',-0.914634)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(35,'GBPUSD','Sep 7 2010 11:00:00:000AM',-0.657894)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(16,'EURUSD','Sep 8 2010 7:00:00:000AM',-0.711538)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(36,'GBPUSD','Sep 15 2010 11:00:00:000AM',0.563492)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(38,'EURUSD','Sep 15 2010 3:00:00:000PM',4.675976)
INSERT INTO [accountPerformance] ([tradeId],[market],[tradeDateTime],[pctAccountChange])VALUES(37,'GBPUSD','Sep 23 2010 3:00:00:000PM',1.112582)
Thanks very much for looking at this. It is greatly appreciated.
-
Hi keymoo,
Am I right in assuming that the drawdown has to be continuous series of down movements? E.g if the value went:
10,9,8,7,8,7,6,5,4,5
Then I have two drawdowns, one from 10 to 7 and one from 8 to 4?
Also, what about simultaneous trades? Do these net off? E.g. TradeId 40 & 2
Regards, Iain
-
Hi Iain,
I guess to work out the drawdown you could calculate highs in equity - lows in equity and then look for the largest one and that would be the largest drawdown. For example in this image
Editor: Image from http://www.myforexresults.com/article.php?product_id=2
the max drawdown is illustrated. Does that help explain it? Another way is to take the sample data I provided and put it into excel and create a new column called balance and then calculate the balance after each trade. Then create a chart of datetime against balance and you can see the ups and downs in the account very clearly. I want to show, using T-SQL, the biggest downward trend in the account expressed in terms of percent of account equity.
I know it's a bit complicated, hence my troubles with it. Here's the equity curve for the sample data I provided with a start balance of $1,000.
-
Hi,
The following should help, I think. That said, the final query to return the result looks quite inefficient (triangular join?) - there's probably a smarter way. I'll keep playing around to see if I can find something better...
-- aggregate the simultaneous tx-- not sure if this is required but seems logical
select row_number() over (order by tradedatetime) as Row_Order
, tradedatetime, sum(pctaccountchange) as net_pctChange
into dbo.grouped_Performance
from dbo.accountPerformance
group by tradedatetime
-- cluster on row order
create clustered index ix_row_order on dbo.grouped_Performance(Row_Order)
-- add column for running sum
-- not sure about required level of precision
-- but pct change is 6dp so use that
alter table dbo.grouped_Performance
add Running_Sum decimal(38,6)
-- do the quirky update as per Lutz
-- define some variable to help with the quirky update
DECLARE
@runningValue [DECIMAL](38, 6), -- to hold the intermediate values
@Sequence INT -- verify that the Sequence of processing is the same as the reqired Sequence
SET @Sequence=0
SET @runningValue=1000
;
WITH SafeTable AS
(
SELECT net_pctChange
, Row_Order AS Sequence -- define the sequence for data processing
, Running_Sum
FROM dbo.grouped_Performance
)
UPDATE SafeTable
SET
@Sequence = CASE WHEN Sequence = @Sequence + 1 THEN Sequence ELSE 1/0 END, -- double check to be still in sync
@runningValue = (@runningValue*(1+net_pctChange/100)), -- calculate the new value
Running_Sum = @runningValue -- store the value in the requested format
FROM SafeTable
WITH (TABLOCKX) -- IMPORTANT: lock the table to avoid data changes in between
OPTION (MAXDOP 1); -- avoid parallel processing to ensure proper sequence
-- calculate the max drop for each data point
-- by looking for the lowest value in running sum
-- that is dated after the current row's date
select *
, (select min(Running_Sum) from dbo.grouped_Performance
where Row_Order > gp.Row_Order
and Running_Sum < gp.Running_Sum) as min_future_low
, Running_Sum - (select min(Running_Sum) from dbo.grouped_Performance
where Row_Order > gp.Row_Order
and Running_Sum < gp.Running_Sum) as total_drop
-
This looks a bit better on plan and will also let you return the dates:
select gpa.*, gpb.*
, gpa.Running_Sum - gpb.Running_Sum as diff
from dbo.grouped_Performance gpa
join dbo.grouped_Performance gpb
on gpa.Row_Order < gpb.Row_Order
and gpa.Running_Sum > gpb.Running_Sum
order by gpa.Row_Order
-
Hi Iain,
Thanks for looking at this but it's not returning what I'm after. I want to find the largest peak to valley drop in percentage value of the account, so it should return just one value. I'm not really sure what your query is returning to me. Would you mind having another look?
For example in this image the yellow boxes highlight the drawdown areas. I am looking for the largest drawdown area, but done in T-SQL. 😉
Thanks
-
Hi keymoo,
The query posted gives all drops over the period. You should read each result as:
Between the tradedatetime in column 6 and the tradedatetime in column 2, the value of the account dropped by the amount in the diff column.
So, to get the maximum, all you need to do is select the maximum value of [diff]:
;with cte as (select gpa.Running_Sum - gpb.Running_Sum as diff
from dbo.grouped_Performance gpa
join dbo.grouped_Performance gpb
on gpa.Row_Order < gpb.Row_Order
and gpa.Running_Sum > gpb.Running_Sum
)
select max(diff) from cte
If you also want to return the dates for the period of the drop, you can use:
;with cte as (select gpa.tradedatetime as start_tradedatetime
, gpb.tradedatetime as end_tradedatetime
, gpa.Running_Sum - gpb.Running_Sum as diff
from dbo.grouped_Performance gpa
join dbo.grouped_Performance gpb
on gpa.Row_Order < gpb.Row_Order
and gpa.Running_Sum > gpb.Running_Sum
)
select top 1 * from cte
order by diff desc
Regards, Iain
-
Actually, having re-read your original post, you're looking for the biggest percentage drop, not absolute value drop. To get that, use the following:
;with cte as (select gpa.tradedatetime as start_tradedatetime
, gpb.tradedatetime as end_tradedatetime
, gpa.Running_Sum - gpb.Running_Sum as diff
, (gpa.Running_Sum - gpb.Running_Sum) / gpa.Running_Sum * 100 as pct_drop
from dbo.grouped_Performance gpa
join dbo.grouped_Performance gpb
on gpa.Row_Order < gpb.Row_Order
and gpa.Running_Sum > gpb.Running_Sum
)
select top 1 * from cte
order by pct_drop desc
-
Thanks very much Iain, absolutely perfect! 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply