Excel Challenge - Macro to Return a Date

  • Hi friends,

    New here and really hope someone can help. This is for a work challenge so is super important to me. I have never created a macro in excel, though I'm almost positive this is the only way to get excel to perform the task.

    In my workbook I have price data for a stock by day (I am using end of day closing price).

    Date, Close Price of Stock for given date, % Change (to determine whether day gave positive or negative return)

    The first task - Finding the number of times the stock has had 7 consecutive days of positive returns. I used a frequency function (and help from a previous thread in this community) to find that this has happened 14 times since 2010. (I believe my formula is correct by only 80% sure)

    Current task - Now I need excel to spit back to me the dates where this has occurred. The idea would be to have a list of the dates in a column in the workbook. I only need the date of the 7th day of consecutive positive return.

    What is the easiest formula or macro to get the dates?

    I am attaching my workbook for reference.

    THANK YOU ALL SO MUCH and Happy Holidays!

  • Try this:

    Let me know if it works for you!

  • Also, I think your count formula is wrong. I am showing 15 instances of 7 day consecutive positive returns. You might just want to count column L since the macro provides the dates for each run.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!