Basic Retracement and MMLC statistics

Finished the first step in what could turn out to be multiple statistics stemming from the same set of manipulated data, or variations of it.

This was my take, and first attempt, at recreating Rels MMLC (Major Move Life Cycle). I won’t speak for exactly what he was trying to complete, but my set looks at the following: In a 24 hour period, I calculated each hour to be either a HH (higher high), LL(lower low), Both (if it made both) or in the case of neither, it returned a percentage that price retraced. The first 3 are easy to understand, the 4th takes a bit more explanation to be clear.

At each hour, I asked the question: “is the current open of this hour higher than the open of today?” If the answer was yes, I considered the day to be a current “up” day, and vice versa. Then, if the hour was an inside bar (neither HH or LL), I calculated the retracement value based on how far the opposite move made in the range. (for example, if the day was currently an up day, the retracement would be the low of that hour divided by the current days range). This calculation involved a lot of shifting values, something I couldn’t quite get a macro to complete, so I learned VBA and made my first script!

Sub macro1()
‘specify worksheet and start at G7
Worksheets(“Sheet2”).Range(“G:G”).Delete
Worksheets(“Sheet2”).Cells(2, 7).Select
‘run the whole thing from row 1 to 84625
For q = 1 To 84625
‘current Row working in
w = ActiveCell.Row
y = ActiveCell.Column
‘If the A column has something in it then..
If Not (IsEmpty(Cells(w, 1))) Then
e = Selection.Row
r = Selection.Column
‘Only run the following if the “number” assigned to it is 1, aka 20:00
If (ActiveCell.Offset(0, -1)) – 1 = 0 Then
‘is the first bar green or red?
If (Cells(ActiveCell.Row, 5) > ActiveCell.Offset(0, -5)) Then
Application.Cells(e, r).Value = “HH”
Else
Application.Cells(e, r).Value = “LL”
End If
End If
End If
‘if it’s not the first hour then either HH, LL, or some retracement
‘Solved using a rounding numbers technique
If (ActiveCell.Offset(0, -1) – 1) > 0 Then
n = Int(((ActiveCell.Row) – 2) / 24)
CDH = 2 + (n * 24)

Max = Application.Max(Range(Cells(CDH, 3), Cells(w, 3)))
Min = Application.Min(Range(Cells(CDH, 4), Cells(w, 4)))
‘If price makes a higher high
If (Cells(w, 3) >= Max) Then
Application.Cells(e, r).Value = “HH”
End If
‘if price makes a lower low
If (Cells(w, 4) <= Min) Then
Application.Cells(e, r).Value = “LL”
End If
‘ if price makes both a higher high and lower low
If (Cells(w, 3) >= Max) And (Cells(w, 4) <= Min) Then
Application.Cells(e, r).Value = “Both”
End If
‘if price makes an inside bar
If ((Cells(w, 3) < Max) And Cells(w, 4) > Min) Then
‘if current open is higher than day open
If (ActiveCell.Offset(0, -5) > Cells(CDH, 2)) Then
Application.Cells(e, r).Value = (Cells(w, 4) – Min) / (Max – Min)
End If
‘if current open is lower than day open
If (ActiveCell.Offset(0, -5) < Cells(CDH, 2)) Then
Application.Cells(e, r).Value = (Cells(w, 3) – Min) / (Max – Min)
End If
‘if current open is the same as day open
If (ActiveCell.Offset(0, -5) = Cells(CDH, 2)) Then
Application.Cells(e, r).Value = (Cells(w, 5) – Min) / (Max – Min)
End If
End If
End If
‘Move the cell down to the next one and start again
ActiveCell.Offset(1, 0).Select
Next q
End Sub

Sub cutpaste2()
Worksheets(“Sheet2”).Select
For x = 1 To 3527
a = ActiveCell.Row
b = ActiveCell.Column
old = b – 1
Range(Cells(25, old), Cells((84648 – ((x – 1) * 24)), old)).Select
Selection.Cut
Range(Cells(a, b), Cells((84648 – ((x – 1) * 24)), b)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Select
Next x
End Sub

Couldn’t quite get .xldown to work properly, so I just plugged in numbers. Code is probably super tacky, but got the job done! Hopefully I can continue to improve my VBA skills. It’s also of no use without having the dataset in the correct cells (and having the data properly formatted)

Image

Here’s a glimpse of the data, with time of the left, and the output split into columns so I can do further studies soon.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s