+ Reply to Thread
Results 1 to 4 of 4

Can we use a simple Excel formula to get the same results as the VBA macro?

  1. #1
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Can we use a simple Excel formula to get the same results as the VBA macro?

    Dear all,

    There is a macro to calculate the maximum drawdown for a stock during a time period:

    http://investexcel.net/2450/maximum-drawdown-vba/

    Can I ask if we do the same thing using a simple Excel formula, rather than having to use macro?

  2. #2
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: Can we use a simple Excel formula to get the same results as the VBA macro?

    It will be very helpful if there is a simple Excel formula for this, rather than having to use VBA. Any great idea?

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Can we use a simple Excel formula to get the same results as the VBA macro?

    You would need to analyze the logic of the function to see what it is doing.

    I'm not a financials guy, so take my analysis for what it is worth. It appears that the function calculates every possible combination of x(j)/x(i)-1 (% change) where j>=i and returns the minimum value from these calculations.

    I don't see a simple, single cell formula that can replicate this calculation. I'm not very good with the complex, single cell array functions that a lot of people like to use. I expect this could be fairly simple if you are willing to use a helper column.

    I haven't rigorously tested the math, but it seems like, the minimum %change relative to each point will be at the minimum data point after that point. So, assuming data is in A2:A20 (comma delimited):
    Please Login or Register  to view this content.
    Then use the =MIN() function on column C.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: Can we use a simple Excel formula to get the same results as the VBA macro?

    Thanks, MrShorty. I am now convinced that there will not be a single formula to handle this. I'll use multiple separate steps to calculate it

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1