Using VBA to take a snapshot of live data when a referenced cell reaches a certain value.
Hi there,
Im a beginner using VBA but guess my question would be a more advanced subject.
I have a stock price trading indicator inputting data into column L each minute. This indicator oscillates positive to negative around 0 as below:
6.4453125
8.333333333
10.546875
12.890625
15.234375
14.296875
11.640625
9.84375
8.515625
6.822916667
4.31640625
1.630859375
-0.908203125
-0.384114583
1.325954861
1.81640625
Each minute, a new value is inputted into the empty cell below and the previous bottom value remains at its last value at the end of the minute. The new bottom cell value is constantly changing with the stock price. In a nut shell I would like VBA to take a snapshot of the current stock price (in cell A1) and the time, and put it in a fresh cell, as soon as;
- the bottom cell turns greater than the previous, if the bottom cell is below -15
or
- the bottom cell turns less than the previous, if the bottom cell is above 15.
This in effect is my trading signal but I just cant seem to code it so it constantly references the bottom cell as new data is inputted each minute.
Id be most grateful for any help or pointers you could give.
Best regards,
Steve
I'll start u
I'll start u off:
http://excelexperts.com/VBA-Tips-Run-Code-Every-Hour-Minute-or-Second
Thanks!
Hi Nick,
Thanks for you prompt reply!
I have something similar to this which is actually inputting the data into my sheet;
Dim I As Integer
Sub High_Low_Capture()
If I = 0 Then I = 1
Sheets("Sheet1").Cells(I, 6) = Sheets("Sheet1").Range("A1")
Sheets("Sheet1").Cells(I, 4) = Sheets("Sheet1").Range("A2")
Sheets("Sheet1").Cells(I, 5) = Sheets("Sheet1").Range("A3")
Sheets("Sheet1").Cells(I, 3) = Now
Application.OnTime Now + TimeValue("00:01:00"), "High_Low_Capture"
I = I + 1
Range("A2:A3").Value = [A1]
End Sub
As this Sub inputs data into columns D,E and F, my indicator is a formula in columns G to L. L is the column I wish to focus on. I have a Sub ready, but cant seem to get it to move down and only look at the very bottom 2 cells in column L as new data is input - so L5 compared to L6, then as a new row of data is input into row 7 from the above Sub, then look at L6 and L7, then L7 and L8 etc.