Extracting minimum and maximum points above and below moving average

[nirvehex]

Hi,
15532
I have a worksheet with two tabs: (1) "Data Table" and (2) "Peak Bottom Cycles"

I have attached a sample file and here is the explanation of what I'm trying to do.

The data table tab consists of different stock metrics pertaining to one single stock in chronological order, with the date in column A. On the peak bottom cycles tab, I'm trying to extract, from the data table tab, a 6 month snapshot consisting of various metrics.

Here is what I'm doing manually. I look back 6 months to the last week day from the current date and I always start on an "up day" where the close price has increased. That is day 1 and is listed in row 2 on the data table tab. I then log the associated metrics to that date using a vlookup and put a "1" in column K, meaning it was an up day. Here is where it gets tricky (I think).

In row 3 I find the lowest close price where the close price is less than the moving average. I would record this date in row 3 and the corresponding metrics. Once the close price moves up above the moving average again, I would insert a new row, row 4, and find the highest close price above the moving average and log that information. Then once the close price moves again, below the moving average I log the lowest price below the moving average in row 5. I follow this up and down process.

Put another way: So if you picture a stock chart as a sine wave. I'm looking for min and max points below and above the moving average. These min and max points often change if the price moves higher or lower and stays above or below the moving average.

My file is attached. If anyone can help me automate this with VBA it would be amazing!

Thank you!

[nirvehex]

Anyone have any ideas here? I have a sample file in my original post. I'm really at a loss here. If needed, I can explain further. Thank you to anyone who can offer some help here :)

[SamT]

I look back 6 months to the last week day from the current date 6 months back is a vague date. It could be 6 calendar months earlier but the same day of the month. (9/20/15) It could be 180 days earlier, (9/22/15) or it can be 24 weeks earlier, (10/4/15) or even the start or the 3rd or 4th quarter of 2015, depending if you count the current quarter.


and I always start on an "up day" where the close price has increased. Moving later than the Start date 6 months ago? What id the up movement is still below the Moving Average and closely resemble a valley? IOW, a gain of .001 is an upward movement, but it may lie right next to a minimum.


n row 3 I find the lowest close price where the close price is less than the moving average... Once the close price moves up above the moving average again So there can be many dips and peaks where the Closing price does not cross the Moving Average? And vice versa on the down slope.

I supposed this Moving Average is based on the closing Prices for the vague 6 month time frame mentioned above? Or should it be based on a strict 6 calendar months or some other Frame?



Please note that I am already involved in a complex project and won't be helping you past clarifying your requirements. I will answer any non code questions you have for us in order to clarify your needs.

The more concisely your requirements are stated, the better the chance of getting helped.

After reading Cross Posting, (http://www.vbaexpress.com/forum/showthread.php?18537-Cross-Posting&p=137004&viewfull=1#post137004)you are welcome to try other forums and you can point them to this thread, so you don't have to go over all this again.

The Short link to this thread is http://www.vbaexpress.com/forum/showthread.php?55328

[nirvehex]

Ok let me try to explain more clearly as I know this is a lot:

In the file attached there are two tabs, "Data Table" & "Peak Bottom Cycles". What I'm trying to automate is this:

Creating A Starting Point

Step 1: On "Data Table" tab, look back 180 days from the last date in column A. Doing so puts us back at 6/16/15, row 271.

Step 2: Find first date after this with a "1" in column K. Doing so puts us at 6/18/15, row 273. This will be our starting point.

Identifying The First Range (Max Price)

Step 3: Find next highest price in column E before the value in column E is less than the value in column H. To do this we need to define a range. That range is going to be row 273 through row 277. In row 278, the value of column E is less than the value in column H. Then within this range we need to find the highest value in column E. So row 274 has a price of 7.70 in column E which is the max price before the price in column E is less than the price in column H. In row 278, the value of column E is less than the value of column H. Row 278 will be the starting row in our next range. This will be the first date recorded on the "Peak Bottom Cycles" tab in column A.

Identifying the Second Range (Min Price)

Step 4: Once the price in column E is less than the price in column H (row 278) we are now looking for the lowest price in column E before the price in column E is greater than the price in column H. To do this we again need to define a range. That range is going to be row 278 through row 290. Then within this range we need to find the lowest value in column E. So in row 281, column E has a price of 6.61 which is the lowest price before the price in column E rises above the price in column H which can be seen happening in row 291. So then next range would start in row 291.

Repeat Process Until End of Data

Step 5: Repeat steps 3 & 4 until this process takes us to the end of the data.

Summary & Import

So essentially, the code will define ranges and identify max and min points within the ranges. The ranges change every time the price in column E crosses the price in column H. At the starting point we look for the highest price in the range in column E. Once this value in column E is less than the value in column H we define a new range and look for the lowest price in the range in column E. Then we define a new range in column E and we look for the highest price in column E until the value in column E is less than the value in column H.

What I need to happen simultaneously is to extract these dates onto the "Peak Bottom Cycles" tab in column A. I did this manually so you can see what dates I pulled out. But I'm really hoping someone can help me automate code to do this.

Thank you so much and I really hope this clarifies the problem I'm trying to solve.

15807

[nirvehex]

Hi All,

I recently cross posted this at http://www.mrexcel.com/forum/excel-questions/933973-extracting-minimum-maximum-points-above-below-moving-average.html

I'm really struggling with this one. I hope my outline above is more clear than my first post. If anyone can help I'd really appreciate it!

Thank you :)

[SamT]

You have changed the requirements, and you have not answered the questions. Originally the Base Price was a moving average, now it is some unknown cell in column H.

Here is a possible algorithm:
(NB: An Algorithm Is Not Code)

Declare CheckAboveBasePrice As Boolean
Declare RangeEnds(1 to 180, 1 to 2 as String) 'Memory is cheap

Step 1: get BasePrice (need more information)

Step 2: Initialize
LastCell = Cells(Rows.Count, "A").End(xlUp)
StartDate = DateAdd("d", LastCell, -180)
If LastCell.Row > 180 Then: Set Cel = Cells(LastCell.Row -180, "E"): Else: Set Cel = Range("E2")

Step 3: Refine Starting position(Date >= startDate)
If Cel.Row <> 2 then
Do while Cel.Offset(,-4) < StartDate: Set Cel = Cel.Offset(1): Loop

(first upward movement)
Do while Cel <= Cel.Offset(-1): Set Cel = Cel.Offset(1): Loop
End IF

Step 4: Set initial CheckAboveBasePrice
IF Cel >= BasePrice then CheckAbovebasePrice = True
IF Cel < BasePrice then CheckAbovebasePrice = False

Step 5: Fill RangeEnds Array
i = 1
RangeEnds(i, 1) = Cel.Address
IF Cel > BasePrice then
RangeEnds(i, 2) = "Above"
Else: RangeEnds(i, 2) = "Below
End IF

Do while Cel <> ""
If CheckAboveBasePrice then
Do while Cel <> "" And Cel > BasePrice
Set Cel = Cel.Offset(1): Loop
RangeEnds(i +1, 1) = Cel.Address '(start of next Range)
RangeEnds(i +1, 2) = "Below"
CheckAboveBasePrice = Not CheckAboveBasePrice
Else
Do while Cel <> "" And Cel < BasePrice
Set Cel = Cel.Offset(1): Loop
RangeEnds(i +1) = Cel.Address
RangeEnds(i +1, 2) = "Above"
CheckAboveBasePrice = Not CheckAboveBasePrice
End If
i = i + 1
loop

Step 6: Get Min/Max Dates
For j = 1 to i - 2 '(Last Range Address is LastCell)
If RangeEnds(j, 2) = "Above" Then
'Get Date Of Max Value in Range(Range(RangeEnds(j, 1), Range(RangeEnds(j+1, 1))
'Put Date in "Peak Bottom Cycles"
Else '(RangeEnds(j, 2) = "Below")
'Get Date Of Min Value in Range(Range(RangeEnds(j, 1), Range(RangeEnds(j+1, 1))
'Put Date in "Peak Bottom Cycles"
End If
Next

[nirvehex]

Hi SamT,

Thanks for your continued support here. I apologize if my explanation differed from my original post. I think it changed because you made me really think about what I was doing and I went back and tried to outline my problem logically, as detailed as possible.

Regarding the get BasePrice in your algorithm, to get the base price I do the following:

Select the last row in column A on the Data Table tab and count back 180 days. That puts me at row 271, 6/16/15. Then from there I move down the table in to the next row that has a "1" in column K. This puts me at row 273. The price in column E is 7.53 on this row. This is the BasePrice. I hope this clears up how I obtain the BasePrice.

From there I move on to the next step in my outline, Identifying The First Range (Max Price) and continue to follow the steps in my previous post. Additionally, in my attachment, on the Peak Bottom Cycles tab, the dates I have listed in column A, are what are extracted from the Data Table tab when I follow my outline above. I'm hoping someone can help me transform your algorithm into VBA that I can put back into that Excel sheet to do this automatically.

Again, I apologize for my unclear explanation, and I thank you for your help here! I really do!

[SamT]

Post #1:
I look back 6 months to the last week day from the current date and I always start on an "up day" where the close price has increased.
Post#7
Then from there I move down the table in to the next row that has a "1" in column K.
Are those the same?

[nirvehex]

Hi SamT,

Yes those are the same. Sorry for the confusion.

Thank you for helping! I appreciate it!

[SamT]

I wrote this using hierarchical architecture where each sub does the minimum and calls secondary subs for detailed work. I used this architecture because, IMO, it is easiest to understand and modify. I took into account that you might want to use this code on another stock's two sheets.

Copy all the code below into a standard Module and for now with just one stock to track, run GetPeakBottomCycles from Excel's Tools+Macros menu


Option Explicit
'For help see: http://www.vbaexpress.com/forum/showthread.php?55328

Private SourceSht As Worksheet '
Private DestinationSht As Worksheet '
Private PricesColumn As Range

Dim BasePrice As Double

Private BottomPeaks(1 To 180, 1 To 2)
Private LastCell As Range
Private Const Price2Date As Long = -4 'A column offset value




Public Sub GetPeakBottomCycles(Optional SrcSht As Worksheet, Optional DestSht As Worksheet)

If IsNull(SrcSht) Then Set SrcSht = Sheets("Data Table")
If IsNull(DestSht) Then Set DestSht = Sheets("Peak Bottom Cycles")

Initialize_PublicVariables SrcSht, DestSht

GetBottomPeaks
PushToDestination

FreeMemory
End Sub




Private Sub GetBottomPeaks()
Dim Cel As Range
Dim LoPriceCel As Range
Dim i As Long

Do While Cel.Row <= LastCell.Row
Do While Cel.Value > BasePrice And Cel.Row <= LastCell.Row
Set Cel = Cel.Offset(1)
Loop 'closing price is getting smaller

Set LoPriceCel = Cel

'find the minimum price in cycle
Do While Cel.Value < BasePrice And Cel.Row < LastCell.Row
Set Cel = Cel.Offset(1)
If Cel.Value < LoPriceCel Then Set LoPriceCel = Cel
Loop

If LoPriceCel.Value < BasePrice Then
'add date and value to array
i = i + 1
BottomPeaks(i, 1) = LoPriceCel.Offset(Price2Date)
BottomPeaks(i, 2) = LoPriceCel.Value
End If
Loop

End Sub





Private Sub PushToDestination()

With DestinationSht
.Range("A2:B184").ClearContents
.Range("A2") = BottomPeaks
End With

End Sub







Private Sub Initialize_PublicVariables(SrcSht As Worksheet, DestSht As Worksheet)

Set SourceSht = SrcSht
Set DestinationSht = DestSht
Set LastCell = SourceSht.Cells(Rows.Count, "A").End(xlUp)
Initialize_DatePriceRanges
End Sub




Private Sub FreeMemory()
Set SourceSht = Nothing
Set DestinationSht = Nothing
Set LastCell = Nothing
Set PricesColumn = Nothing
End Sub




Private Sub Initialize_DatePriceRanges()
Dim StartDate As Date
Dim Cel As Range

With SourceSht
StartDate = DateAdd("d", LastCell, -180)

''''Working from bottom cell in closing prices column'''
If LastCell.Row > 180 Then
Set Cel = .Cells(LastCell.Row - 180, "E")
Else
Set Cel = .Range("E2")
End If

'(Date >= startDate)
Do While Cel.Offset(0, Price2Date) < StartDate
Set Cel = Cel.Offset(1)
Loop

'(first upward movement)
If Cel.Address <> "$E$2" Then
Do While Cel <= Cel.Offset(-1)
Set Cel = Cel.Offset(1)
Loop
End If

BasePrice = Cel.Value
Set PricesColumn = .Range(Cel, LastCell)
End With
End Sub