http://formulas.tutorvista
gowflow
Sub GetLowestPoints()
Dim WS As Worksheet
Dim Ch As ChartObject
Dim Pts As Variant
Dim I As Long, J As Long
Dim PrevPoint As Double
Dim MaxRow As Long
Dim bDir As String
Set WS = ActiveSheet
WS.Range("Z:Z").EntireColumn.Delete
WS.Range("Z1") = "Lowest Points"
PrevPoint = 10000000
J = 2
bDir = "Down"
For Each Ch In WS.ChartObjects
Pts = Ch.Chart.SeriesCollection(1).Values
For I = LBound(Pts) To UBound(Pts)
If Pts(I) > PrevPoint Then
If bDir = "Down" Then
'---> Record ONLY the lowest point in Col Z
If J = 2 Then
WS.Range("Z" & J) = PrevPoint
J = J + 1
End If
If J > 2 And Abs(PrevPoint - WS.Range("Z" & J - 1)) < 30 Then
WS.Range("Z" & J) = PrevPoint
J = J + 1
End If
bDir = "Up"
End If
Else
If bDir = "Up" Then
bDir = "Down"
End If
End If
PrevPoint = Pts(I)
Next I
Next Ch
WS.Range("Z:Z").EntireColumn.AutoFit
End Sub
Purpose of doing this - by finding duration of cycle we can pin point low or high of stock price. we just need to find where we are in cycle & we can predict future high & low. this is very early stag of this there 3 or 4 more cycles.
Title | # Comments | Views | Activity |
---|---|---|---|
How can I modify this Excel worksheet with a script? | 15 | 40 | |
Excel VBA - Gather data into a single summary sheet from multiple worksheets | 7 | 23 | |
second highest value difference | 11 | 23 | |
Formula to copy cell and its "format" | 3 | 24 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
7 Experts available now in Live!