Excel Challenge - Macro to Return a Date

Hcs1210 · Dec 24th 2017

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!
H

Max1616 · Dec 27th 2017

Try this:

Code

Sub ConsecutiveDates()
Dim lrow1 As Long
Dim lrow2 As Long
Dim i As Long
Dim counter As Integer


lrow1 = Cells(Rows.Count, 1).End(xlUp).Row 'Last row for loop


For i = 2 To lrow1
    If Cells(i, 8) > 0 Then counter = counter + 1
    If Cells(i, 8) <= 0 Then counter = 0
    If counter = 7 Then
        lrow2 = Cells(Rows.Count, 12).End(xlUp).Row + 1 'Row to place the date (12 signifies column L)
        Cells(lrow2, 12) = Cells(i, 1)
        Cells(lrow2, 12).NumberFormat = "mm/dd/yyyy"
        counter = 0
    End If
Next i
End Sub

Display More

Let me know if it works for you!

Max1616 · Dec 27th 2017

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.

Excel Challenge - Macro to Return a Date

Participate now!

Share