Results 1 to 6 of 6
  1. #1
    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

    thanks in advance,
    Mike
    ________

  2. #2
    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. #3
    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
    Mike
    ________

  4. #4
    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. #5
    Join Date
    Jun 2012
    Posts
    85
    the problem is theres over 100 companies
    Thanks,
    Mike
    ________

  6. #6
    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.

    3 - Do your analysis.

    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
  •