Creating Max Drawdown calculation

[#1]

Hi all,

I'm looking to find out if anyone can help with creating a Max Drawdown calculation please.

I've attached a spreadsheet that lists the following -

2x tabs are a snapshot of the tables that would be coming in (Values,Indate)

1x Tab to show the Excel workings of how you calculate Max Drawdown on that data set.

Importantly each Product has an Indate that is when the Max Drawdown needs to start (and has a vast array of dates and values for before that date. So I need to apply either Set/Aggr/If to define the range to look at as well.

I've searched and worked through the current examples on here about Drawdown but they havent worked.

Hoping you guys can help,

Thanks!

Ryan

[sunny_talwar]

2 questions for you

1) Do you want this to be done strictly on the front end?

2) InDate is 6/1/2015, but the 'Workings' tab expressions use no sort of logic to incoporate InDate? Is that by mistake or am I missing something here?

[#3]

Hi Sunny,

Thanks for replying,

1) Nope, happy to backend it

2) Its because currently they manually edit down the data once exported to excel so the Indate was part of the export (leaving them with just that dataset) I changed the date to be mid way through the dataset to be more realistic to what I'm working with, as in I have data for the last 10 years, but the calculations must start from the InDate, does that make sense?

Many thanks

Ryan

[sunny_talwar]

So the data before InDate is not relevant then? Make sense... will try to get a sample across in a little bit

[#5]

Yeh exactly, it was because I needed to illustrate the requirement to use the date to define the range, else I'd have got replies that didn't take that into consideration and just used the full data set Awesome thank you!

[sunny_talwar · Accepted solution]

Try this script

Table1:

LOAD Date,

    Value,

    Product

FROM

[..\..\Downloads\TestData.xlsx]

(ooxml, embedded labels, table is Values);

Left Join (Table1)

LOAD Product,

    InDate

FROM

[..\..\Downloads\TestData.xlsx]

(ooxml, embedded labels, table is InDate);

TempTable:

LOAD Product,

  Date,

  Value,

  If(Product = Previous(Product), RangeSum(Peek('Ret + 1'), Log(1+Value)), Log(1+Value)) as [Ret + 1]

Resident Table1

Where Date >= InDate

Order By Product, Date;

TempTable2:

LOAD Product,

  Date,

  Value,

  Exp([Ret + 1]) as [Ret + 1],

  If(Product = Previous(Product), If(Exp([Ret + 1]) <= Peek('Ret + 1 New'), Peek('Ret + 1 New'), Exp([Ret + 1])), Exp([Ret + 1])) as [Ret + 1 New],

  Exp([Ret + 1])/If(Exp([Ret + 1]) <= Peek('Ret + 1 New'), Peek('Ret + 1 New'), Exp([Ret + 1])) - 1 as temp

// If(Product = Previous(Product),

// If(Exp([Ret + 1])/If(Exp([Ret + 1]) <= Peek('Ret + 1 New'), Peek('Ret + 1 New'), Exp([Ret + 1])) - 1 < Peek('Max Draw'),

// Exp([Ret + 1])/If(Exp([Ret + 1]) <= Peek('Ret + 1 New'), Peek('Ret + 1 New'), Exp([Ret + 1])) - 1,

// Peek('Max Draw')), 0) as [Max Draw]

Resident TempTable;

Left Join (Table1)

LOAD Product,

  Min(temp) as [Max Draw]

Resident TempTable2

Group By Product;

DROP Table TempTable, TempTable2;

[#7]

Worked like a charm, absolute hero thank you!