Category Archives: VBA

Small self post

It surprises me how far my VBA skills have advanced. There’s still so much that I don’t know and for the things I’m doing, I’ll probably never learn about. But I’ve learned some things that have become invaluable to executing fast code.

 

I’ve learned that for whatever reason, VBA isn’t a fan of the cut past option, regardless of the cutcopymode=false line. I’m not entirely sure what the line means, but I’ve heard that it’s suppose to clear the memory of excel (as far as it’s cut/copy memory is concerned). This makes sense as once you’ve cut something, you no longer need excel to store it since it’s in the place where it needs to go. However, when running code that is as lengthy as mine is (10s of thousands of pieces of data), the code starts lagging, freezing up, and sometimes crashing the program. The workaround for this is that excel has no problem  inserting data from another cell.

In other words, excel does not like:

cut A1 and place it in A2

But there’s no issue with

make A2=A1, then delete A1

The second piece of code runs MUCH MUCH faster (less than 2 seconds whereas my cut paste code was taking me as long as 5-10 minutes to run with the chance to crash). Additionally, bulk deleting makes it so you often don’t have to run the second part of the second code until all the values have been inserting.

The following code I was using to shift data. the before and after. I had data stored in 7 columns that ran down (columns A-G, rows 1-x. I call this vertical data) I wanted the data to be “pushed” in a sense, to be formatted in 7 ROWS, running to the right (columns A-x, rows 1-7, or horizontal data).

Before:

Sub datapush()
‘second part containing max/min/high/low
Cells(1, 1).Select
executionnumbers = ActiveCell.End(xlDown).Row
For x = 1 To executionnumbers
Range(Cells((x + 1), 1), Cells((x + 1), 7)).Select
Selection.Cut
Range(“I1: N1”).Select
ActiveSheet.Paste
Application.CutCopyMode = False

‘for max
Cells(1, 9).Select
Selection.Cut
Cells(3, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for min
Cells(1, 10).Select
Selection.Cut
Cells(4, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for High
Cells(1, 11).Select
Selection.Cut
Cells(5, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for Low
Cells(1, 12).Select
Selection.Cut
Cells(6, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for size
Cells(1, 13).Select
Selection.Cut
Cells(7, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for first
Cells(1, 14).Select
Selection.Cut
Cells(8, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for same/difference
Cells(1, 15).Select
Selection.Cut
Cells(9, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next x
End Sub

 

 

After:

Sub smarterdatapush()
‘second part containing max/min/high/low
‘places cells starting with the I1 cell
Cells(1, 1).Select
executionnumbers = ActiveCell.End(xlDown).Row
executionrows = ActiveCell.End(xlToRight).Column
‘for how many columns of data there are
For y = 1 To executionrows
‘for how many rows of data there are
For x = 1 To executionnumbers
‘where to place data
Cells(y, 8 + x) = Cells(x, y)
Next x
Next y
End Sub

Advertisements

Wave modeling 1.6 (time)

-Following up on the journal entry I wrote. The results here need to be looked at; I might have some sloppy coding leading to some waves being classified as the wrong one. Yet I still consider these results fairly accurate.

In this project I looked at the simple time when the MM begins. I’ve previously done some work on when the MM ends as well as how long the waves are (which I think should be redone), but I haven’t done the simpleton approach of when the wave begins.

Capture

The A waves aren’t shown for obvious reasons, and it might be worth noting that A waves make up about 23% of all waves. I think there’s some nice things about this info, and although mostly supporting intuition, it’s always nice to have core statistics to back it up. Since I should be mostly concerned about A-B-C waves (making up 89% of all waves), the fact that I have the nice cluster around the 2-4 AM EST, or London open gives me some ideas of how to trade moving into the day’s action. It’s basically saying I can treat B and C waves the same, which is really really good.

 

The next steps:

-Check the waves and wave sorting coding wise

-See if I can distinguish A waves from the now B/C waves.

-See if anything noteworthy is happening in the cluster times (fake outs)

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.