# Thread: max drawdown expression (equatoin help wquery)

1. Registered User
Join Date
Jun 2012
Posts
85

## Unanswered: max drawdown expression (equatoin help wquery)

Hey,

I have a query set up like this: (comes from internal table "Monthly_Returns")
Code:
```CompanyName.....Return.....NAV....drawdown
aaa....................-0.829......91.71.....xxx (Ignore)
aaa....................0.0908.....100.79...Start
aaa....................0.001.......100.89
aaa....................0.051.......105.99
aaa....................0.0666.....112.65
aaa....................-0.0029....125.39
aaa....................-0.0921....125.10
aaa....................-0.0628....115.89
aaa....................-0.0457....109.61
bbb....................-0.0021....99.79....xxx (Ignore)
bbb....................-0.0021....99.58....Start Again
bbb....................-0.0021....99.37
bbb....................-0.0018....99.19
bbb....................-0.0017....99.02
bbb....................0.0554.....104.56
bbb....................-0.0514....99.42```
The Drawdown formula would be this in excel:

first aaa Is cell A2

=(A3/Max(A\$2\$:A2))-1

My question is how would you reference the starting point, and for each drawdown value you continue to have you Max list selection growing by one
Also, i need it to start over for each Company Name

2. Registered User
Join Date
May 2004
Location
New York State
Posts
1,178
first aaa Is cell A2

The Drawdown formula would be this in excel:

=(A3/Max(A\$2\$:A2))-1
Something's wrong. If CompanyName "aaa" (string value) is cell A2, then cell A2, and also A3 (also value "aaa", a string value) can't be part of a mathematical formula.

Sam

3. Registered User
Join Date
Jun 2012
Posts
85
sry that should be C not A, The AAA is A2 (starting) the formulae should be:

=(C3/Max(C\$2\$:C2))-1

4. Registered User
Join Date
May 2004
Location
New York State
Posts
1,178
Sorry to burst the bubble, Mike, but I wouldn't bother with a query on this one. The way I would attack it is to put the whole table, sorted, into a temp table (if it's not already sorted the way you want), open it as a recordset in VBA, and put each company separately into an array. That way you know where your starting and ending points are.

If you're unfamiliar with arrays, don't hesitate to read up on them in the Help file.

Sam

5. Registered User
Join Date
Jun 2012
Posts
85
the problem is theres over 100 companies
Thanks,

6. Registered User
Join Date
May 2004
Location
New York State
Posts
1,178
That's no problem. Do it in a loop.

1 - Do While (the company name = TheCurrentCompanyName) And (Not .EOF)

2 - Copy each record into the array as you go, until you reach a record with a new company name or .EOF.

4 - Make sure to empty the array completely each time you finish the analysis of an individual company, of course.

5 - Loop back to 1.

Sam

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•