Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to complete the visualization calculation for Max Drawdown: https://www.investopedia.com/terms/m/maximum-drawdown-mdd.asp
Max Drawdown must be calculated dynamically when a user selects a date range, so I cannot calculate this in my load script.
Definitions:
Drawdown = Cumulative PNL - Maximum of Daily Cumulative PNL in a selected date range
Max Drawdown would be the negative Drawdown value with the greatest magnitude of this across a selected time series.
Assume my Data is like the below:
Date | Department | PNL |
---|---|---|
2018/06/03 | A | 50 |
2018/06/04 | A | 60 |
2018/06/05 | A | 10 |
2018/06/06 | A | -50 |
2018/06/07 | A | 20 |
I have already successfully come up with a formula to calculate the Maximum of Daily Cumulative PNL in a selected date range:
Max(Aggr(RangeSum(Above(Sum(PNL), 0, RowNo())), Department, (Date, (TEXT, Ascending))))
For the above data, we would get the max cumulative of 120, and ideally the drawdown calculations below:
Date | Department | PNL | Cumulative PNL | Max(Cumulative) | Drawdown | Max Drawdown |
---|---|---|---|---|---|---|
2018/06/03 | A | 50 | 50 | 50 | 0 | 0 |
2018/06/04 | A | 60 | 110 | 110 | 0 | 0 |
2018/06/05 | A | 10 | 120 | 120 | 0 | 0 |
2018/06/06 | A | -50 | 70 | 120 | -50 | -50 |
2018/06/07 | A | 20 | 90 | 120 | -30 | -50 |
How can I calculate Max Drawdown in a field if the table I am trying to put it in should look like the below with the date range of 2018/06/03 - 2018/06/07:
Department | PNL | Max(Cumulative | Drawdown | Max Drawdown |
---|---|---|---|---|
A | 90 | 120 | -30 | -50 |
B | ||||
C | ||||
... |
I have tried the following, but it does not give any numbers, only '-' in each cell of the column:
Min(Aggr(RangeSum(Above(Sum(DV01PNL), 0, RowNo())) - Max(RangeSum(Above(Sum(DV01PNL), 0, RowNo()))), DEPARTMENT, (REPORT_DATE, (TEXT, Ascending))))
This almost worked, but definitely got me on the right track- just needed to use rangesum instead of sum for the cumulative sum. Thank you!
The formula that ended up working was:
Min(TOTAL <DEPARTMENT> Aggr(RangeSum(Above(Sum(DV01PNL), 0, RowNo())) - RangeMax(Above(RangeSum(Above(Sum(DV01PNL), 0, RowNo())))), DIVISION, (REPORT_DATE, (TEXT, Ascending))))
May be this
Max(TOTAL <DEPARTMENT> Aggr(RangeMax(Above(Sum(DV01PNL), 0, RowNo())) - RangeMax(Above(Sum(DV01PNL), 0, RowNo())), DEPARTMENT, (REPORT_DATE, (TEXT, Ascending))))
This almost worked, but definitely got me on the right track- just needed to use rangesum instead of sum for the cumulative sum. Thank you!
The formula that ended up working was:
Min(TOTAL <DEPARTMENT> Aggr(RangeSum(Above(Sum(DV01PNL), 0, RowNo())) - RangeMax(Above(RangeSum(Above(Sum(DV01PNL), 0, RowNo())))), DIVISION, (REPORT_DATE, (TEXT, Ascending))))
No problem, I am glad I was able to help