danjuma
10-12-2011, 02:16 AM
Hello,
Firstly, I am just learning VBA so consider myself still a newbie. What I m trying to do is this. I have a spreadsheet linked to a stock quote data feed which is updated every 5 seconds.
Column A on the sheet has the id of the stock, e.g. ABC
Col N has the date/time of the last update, so goes for example 10/11/2011 20:50:00, 10/11/2011 20:50:05 etc
Col O has the open price
Col P has the High price
Col Q has the Low price
Col R has the Close price
Col S has the volume
Col T has something called the WAP
Like I already mentioned, this data is updated every 5-sec, in essence, the data are for 5-sec periods (or bars in stock charting terms). I want to convert this into 5-min periods (or bars) instead. So my logic is for example, at say 20:50:00, the open price will be the value in Col O at that time, the high price will the highest value in the period 20:50:00 to 20:54:55, the low will be the lowest value in the period 20:50:00 to 20:54:55, the volume will be the sum of all the values in the period 20:50:00 to 20:54:55, the close price will be the value in Col R at 20:54:55, and the WAP will be the value in Col T at 20:54:55. Hope you are still with me so far. These values are transferred to columns AC (for open price), AD(for high price), AE(for low price), AF(for close price), AG(for WAP), and AH(for volume).
I have done the coding which works to an extent but not working properly how it should. The High, Low and Volume columns just keep permanent updating as if stuck in an endless loop with the spreadsheet becoming non-responsive as a result. I have racked my brains but now stuck and don’t really know where to go from here. Your assistance would be greatly appreciated. Many thanks
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("N8:N68")) Is Nothing Then
Application.EnableEvents = False
Dim r As Integer
With Worksheets(16)
If Range("S1").value = "start" Then
Range("AC8:AH68").value = 0 'reset cells to zero at the start
For r = 8 To 68
If Cells(r, 24).value = "Y" Then 'checks cell contains a ticker i.e. cell not empty
If Cells(r, 23).value = TimeSerial(0, 0, 0) Then 'cells(r,23) contains the mm:ss extracted from Col N
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 0, 0) And Cells(r, 23).value <= TimeSerial(0, 4, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 4, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 5, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 5, 0) And Cells(r, 23).value <= TimeSerial(0, 9, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 9, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 10, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 10, 0) And Cells(r, 23).value <= TimeSerial(0, 14, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 14, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 15, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 15, 0) And Cells(r, 23).value <= TimeSerial(0, 19, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 19, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 20, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 20, 0) And Cells(r, 23).value <= TimeSerial(0, 24, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 24, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 25, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 25, 0) And Cells(r, 23).value <= TimeSerial(0, 29, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 29, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 30, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 30, 0) And Cells(r, 23).value <= TimeSerial(0, 34, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 34, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 35, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 35, 0) And Cells(r, 23).value <= TimeSerial(0, 39, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 39, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 40, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 40, 0) And Cells(r, 23).value <= TimeSerial(0, 44, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 44, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 45, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 45, 0) And Cells(r, 23).value <= TimeSerial(0, 49, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 49, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 50, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 50, 0) And Cells(r, 23).value <= TimeSerial(0, 54, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 54, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 55, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 55, 0) And Cells(r, 23).value <= TimeSerial(0, 59, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 59, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
End If
Next r
End If
End With
Application.EnableEvents = True
End If
End Sub
Firstly, I am just learning VBA so consider myself still a newbie. What I m trying to do is this. I have a spreadsheet linked to a stock quote data feed which is updated every 5 seconds.
Column A on the sheet has the id of the stock, e.g. ABC
Col N has the date/time of the last update, so goes for example 10/11/2011 20:50:00, 10/11/2011 20:50:05 etc
Col O has the open price
Col P has the High price
Col Q has the Low price
Col R has the Close price
Col S has the volume
Col T has something called the WAP
Like I already mentioned, this data is updated every 5-sec, in essence, the data are for 5-sec periods (or bars in stock charting terms). I want to convert this into 5-min periods (or bars) instead. So my logic is for example, at say 20:50:00, the open price will be the value in Col O at that time, the high price will the highest value in the period 20:50:00 to 20:54:55, the low will be the lowest value in the period 20:50:00 to 20:54:55, the volume will be the sum of all the values in the period 20:50:00 to 20:54:55, the close price will be the value in Col R at 20:54:55, and the WAP will be the value in Col T at 20:54:55. Hope you are still with me so far. These values are transferred to columns AC (for open price), AD(for high price), AE(for low price), AF(for close price), AG(for WAP), and AH(for volume).
I have done the coding which works to an extent but not working properly how it should. The High, Low and Volume columns just keep permanent updating as if stuck in an endless loop with the spreadsheet becoming non-responsive as a result. I have racked my brains but now stuck and don’t really know where to go from here. Your assistance would be greatly appreciated. Many thanks
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("N8:N68")) Is Nothing Then
Application.EnableEvents = False
Dim r As Integer
With Worksheets(16)
If Range("S1").value = "start" Then
Range("AC8:AH68").value = 0 'reset cells to zero at the start
For r = 8 To 68
If Cells(r, 24).value = "Y" Then 'checks cell contains a ticker i.e. cell not empty
If Cells(r, 23).value = TimeSerial(0, 0, 0) Then 'cells(r,23) contains the mm:ss extracted from Col N
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 0, 0) And Cells(r, 23).value <= TimeSerial(0, 4, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 4, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 5, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 5, 0) And Cells(r, 23).value <= TimeSerial(0, 9, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 9, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 10, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 10, 0) And Cells(r, 23).value <= TimeSerial(0, 14, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 14, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 15, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 15, 0) And Cells(r, 23).value <= TimeSerial(0, 19, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 19, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 20, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 20, 0) And Cells(r, 23).value <= TimeSerial(0, 24, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 24, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 25, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 25, 0) And Cells(r, 23).value <= TimeSerial(0, 29, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 29, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 30, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 30, 0) And Cells(r, 23).value <= TimeSerial(0, 34, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 34, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 35, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 35, 0) And Cells(r, 23).value <= TimeSerial(0, 39, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 39, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 40, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 40, 0) And Cells(r, 23).value <= TimeSerial(0, 44, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 44, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 45, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 45, 0) And Cells(r, 23).value <= TimeSerial(0, 49, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 49, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 50, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 50, 0) And Cells(r, 23).value <= TimeSerial(0, 54, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 54, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
If Cells(r, 23).value = TimeSerial(0, 55, 0) Then
Cells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 55, 0) And Cells(r, 23).value <= TimeSerial(0, 59, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If
If Cells(r, 23).value = TimeSerial(0, 59, 55) Then
Cells(r, 33).value = Cells(r, 20).value 'WAP
Cells(r, 32).value = Cells(r, 18).value 'Close price
End If
End If
Next r
End If
End With
Application.EnableEvents = True
End If
End Sub