Convert 5-sec period data into 5-min period data

[danjuma]

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






[vba]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
[/vba]

[Bob Phillips]

Normally, I would expect that to happen if you leave events on, but your code disables them.

What are all the If tests for, it is a bit 'wood for the trees' problem? Is Worksheets (16) the sheet the code runs within?

[danjuma]

Normally, I would expect that to happen if you leave events on, but your code disables them.

What are all the If tests for, it is a bit 'wood for the trees' problem? Is Worksheets (16) the sheet the code runs within?

Yes, worksheets(16) is the worksheet the code runs withing.

Now to explain. let's assume the current date/time in cell N8 is 10/11/2011 20:00:00. Looking at the following code line by line


If Range("S1").value = "start" Then

I click a button to input 'start' in cell S1 to start the code

Range("AC8:AH68").value = 0 'reset cells to zero at the start

At the very beginning, I get the cells AC8:AH68 to reset to zero, in case there are values already in from previous session.


For r = 8 To 68
If Cells(r, 24).value = "Y" Then 'checks cell contains a ticker i.e. cell not empty

Checks cells 8 to 68 in column A to ensure not empty, i.e. got a stock id.


If Cells(r, 23).value = TimeSerial(0, 0, 0) Then 'cells(r,23) contains the mm:ss extracted from Col N

Checks to see the value (mm:ss) in cells (r,23), so in the above example, the value would be 00:00. As this is true in this case i.e. equals TimeSerial(0,0,0), then will do the followingCells(r, 29).value = Cells(r, 15).value 'Open price
Cells(r, 30).value = 0
Cells(r, 31).value = 0
Cells(r, 34).value = 0

Open price in cells(r,15) gets transferred to cells(r,29). Cells(r,30) - high price,(r,31)- low price and (r,34)-volume resets to zero ready for new values

End If

If Cells(r, 23).value >= TimeSerial(0, 0, 0) And Cells(r, 23).value <= TimeSerial(0, 4, 55) Then

if the mm:ss in cells(r,23) is between 00:00 and 04.55 (a 4-minute and 55 seconds period)

Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume

keep totaling the volume values

If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If

As the high price for the 5-minute period is going to be the highest value in 4-minute and 55-second period, keep comparing the values in cells(r,30) with values in cells(r,16) and only update the value in cells(r,30) with the current value in cells(r,16) if the value in cells(r,16) is greater.


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

Similar to high above, except looking for the lowest value in this case, and also checking first that the current value in cells(r,31) is not zero, or the comparison won't work.

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 the mm:ss in cells(r,23) is 04:55, then transfer current values in cells(r,20) and cells(r,18) to cells(r,33) and cells(r,32) respectively.And the If's just continues for the other possible mm:ss that could be in cells(r.23)

I hope this is clearer. Like I mentioned before, I am a newbie to VBA and this is just my crude elementary way of doing it.

Many thanks
Dan

[danjuma]

Ok, I think I have solved the problem. The rogue line was
Range("AC8:AH68").value = 0

[danjuma]

The only bit I am having problem with at the moment is

Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume

I think the code is wrong because the cells(r,34) just keep adding up and the value is nothing like what it should be. Any suggestion please? What I am trying to do is say if cells(r,19) is 2, then cells(r,34) becomes 2. If cells(r,19) then updates to 5, then cells(r,34) should be 7. If cells(r,19) updates to 1, then cells(r,34) becomes 8, and so on.

Thanks

[Bob Phillips]

That is what that code looks to be doing.

[danjuma]

That is what that code looks to be doing.

Ok, there must be something wrong somewhere then. Will have to look into this further. Thanks

[danjuma]

Ok, I have managed to get this coded myself and seems to be doing what I want it to do. Unfortunately I am having a bit of problem with the next stage. I am plodding along, but in the meantime will appreciate some assistance from anyone willing to help please. A quick summary:

Firstly, I am still a novice in VBA and some of my coding is probably not the most efficient way to do things, and just doing more by trial and error.

The code below is the main code I am using to capture the 5-second bars to make them into 5-min bars. I have put this in a module as it seems the only way I could get it work with a sort of timer I was using to call the sub every second. The variable 'r' I have declared Public in the module as I am using it for both this sub and another sub called 'PasteValues', which is also inserted in the same module.

Like I have already stated, the capturing of the 5-sec period data into every 5-min period seems to be working fine. The next stage for me was to capture these values into columns AI:AN for open one column at a time, AO:AT for high one column at a time, AU:AZ for low one column at a time, BA:BF for close one column at a time, and BG:BL for WAP one column at a time. And when all the 6 columns in each is populated, then move the values from the columns to the right to the columns to the left, with the 6th column for each (open, high, low, close, WAP) having the most recent values. So basically, the 6th column will have the most recent value, and 1st column will have the oldest value, if you know what I mean.

The logic I was using for my code is to use CountBlank function to count the number cells, for each row, in just columns AI:AN, and paste values to the relevant column depending on how many columns already have values. So the idea is say we take as example row 8, so first time around, row 8, column AI will be empty, so paste values in this column, second time around, paste values in row 8 column AJ, third time, row 8 column AK, and so on until it gets to row 8 col AN, after which it starts shifting the values from right to left. So value row 8 column AN jumps to row 8 column AM, and value in row 8 column AM jumps to row 8 column AL and so on.

What is happening, however, is that all the columns seem to be populated at once with the same value, and not one at a time with different values? The codes related to what I am trying to do that's giving me problem is the bolded one in the CaptureBars sub, and the sub PasteValues.

Many thanks.


[vba]Sub CaptureBars()
Dim mycount As Long
With Worksheets(16)
For r = 8 To 68
If Cells(r, 24).value = "Y" Then 'checks cell contains a ticker i.e. cell not empty
mycount = Application.WorksheetFunction.CountBlank(Range(Cells(r, 35), Cells(r, 40)))
Cells(r, 33).value = mycount
If Cells(r, 23).value = TimeSerial(0, 0, 0) Then 'cells(r,23) contains the mm:ss extracted from Col N
Cells(r, 26).value = Cells(r, 15).value 'Open price
Cells(r, 27).value = 0
Cells(r, 28).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 0, 0) And Cells(r, 23).value < TimeSerial(0, 5, 0) And Cells(r, 26).value <> 0 Then
If Cells(r, 16).value > Cells(r, 27).value Then 'High price
Cells(r, 27).value = Cells(r, 16).value
End If
If Cells(r, 28).value = 0 Then 'Low price
Cells(r, 28).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 28).value Then
Cells(r, 28).value = Cells(r, 17).value
End If
If Cells(r, 23).value = TimeSerial(0, 4, 55) Then
Cells(r, 30).value = Cells(r, 20).value 'WAP
Cells(r, 29).value = Cells(r, 18).value 'Close price
PasteValues
End If
End If

If Cells(r, 23).value = TimeSerial(0, 5, 0) Then
Cells(r, 26).value = Cells(r, 15).value 'Open price
Cells(r, 27).value = 0
Cells(r, 28).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 5, 0) And Cells(r, 23).value < TimeSerial(0, 10, 0) And Cells(r, 26).value <> 0 Then
If Cells(r, 16).value > Cells(r, 27).value Then 'High price
Cells(r, 27).value = Cells(r, 16).value
End If
If Cells(r, 28).value = 0 Then 'Low price
Cells(r, 28).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 28).value Then
Cells(r, 28).value = Cells(r, 17).value
End If
If Cells(r, 23).value = TimeSerial(0, 9, 55) Then
Cells(r, 30).value = Cells(r, 20).value 'WAP
Cells(r, 29).value = Cells(r, 18).value 'Close price
PasteValues
End If
End If
.
.
. More code of similar nature here.
.
.
.

If Cells(r, 23).value = TimeSerial(0, 55, 0) Then
Cells(r, 26).value = Cells(r, 15).value 'Open price
Cells(r, 27).value = 0
Cells(r, 28).value = 0
End If
If Cells(r, 23).value >= TimeSerial(0, 55, 0) And Cells(r, 23).value <> TimeSerial(0, 0, 0) And Cells(r, 26).value <> 0 Then
If Cells(r, 16).value > Cells(r, 27).value Then 'High price
Cells(r, 27).value = Cells(r, 16).value
End If
If Cells(r, 28).value = 0 Then 'Low price
Cells(r, 28).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 28).value Then
Cells(r, 28).value = Cells(r, 17).value
End If
If Cells(r, 23).value = TimeSerial(0, 59, 55) Then
Cells(r, 30).value = Cells(r, 20).value 'WAP
Cells(r, 29).value = Cells(r, 18).value 'Close price
PasteValues
End If
End If

End If
Next r
End With
End Sub[/vba]

[vba]Sub PasteValues()
With Worksheets(16)
Select Case Cells(r, 33).value
Case 6
Cells(r, 35).value = Cells(r, 26).value 'open
Cells(r, 41).value = Cells(r, 27).value 'high
Cells(r, 47).value = Cells(r, 28).value 'low
Cells(r, 53).value = Cells(r, 29).value 'close
Cells(r, 59).value = Cells(r, 30).value 'WAP
Case 5
Cells(r, 36).value = Cells(r, 26).value 'open
Cells(r, 42).value = Cells(r, 27).value 'high
Cells(r, 48).value = Cells(r, 28).value 'low
Cells(r, 54).value = Cells(r, 29).value 'close
Cells(r, 60).value = Cells(r, 30).value 'WAP
Case 4
Cells(r, 37).value = Cells(r, 26).value 'open
Cells(r, 43).value = Cells(r, 27).value 'high
Cells(r, 49).value = Cells(r, 28).value 'low
Cells(r, 55).value = Cells(r, 29).value 'close
Cells(r, 61).value = Cells(r, 30).value 'WAP
Case 3
Cells(r, 38).value = Cells(r, 26).value 'open
Cells(r, 44).value = Cells(r, 27).value 'high
Cells(r, 50).value = Cells(r, 28).value 'low
Cells(r, 56).value = Cells(r, 29).value 'close
Cells(r, 62).value = Cells(r, 30).value 'WAP
Case 2
Cells(r, 39).value = Cells(r, 26).value 'open
Cells(r, 46).value = Cells(r, 27).value 'high
Cells(r, 51).value = Cells(r, 28).value 'low
Cells(r, 57).value = Cells(r, 29).value 'close
Cells(r, 63).value = Cells(r, 30).value 'WAP
Case 1
Cells(r, 40).value = Cells(r, 26).value 'open
Cells(r, 47).value = Cells(r, 27).value 'high
Cells(r, 52).value = Cells(r, 28).value 'low
Cells(r, 58).value = Cells(r, 29).value 'close
Cells(r, 64).value = Cells(r, 30).value 'WAP
Case 0
Cells(r, 35).value = Cells(r, 36).value 'open
Cells(r, 36).value = Cells(r, 37).value
Cells(r, 37).value = Cells(r, 38).value
Cells(r, 38).value = Cells(r, 39).value
Cells(r, 39).value = Cells(r, 40).value
Wait
Cells(r, 40).value = Cells(r, 26).value

Cells(r, 41).value = Cells(r, 42).value 'high
Cells(r, 42).value = Cells(r, 43).value
Cells(r, 43).value = Cells(r, 44).value
Cells(r, 44).value = Cells(r, 45).value
Cells(r, 45).value = Cells(r, 46).value
Wait
Cells(r, 46).value = Cells(r, 27).value

Cells(r, 47).value = Cells(r, 48).value 'low
Cells(r, 48).value = Cells(r, 49).value
Cells(r, 49).value = Cells(r, 50).value
Cells(r, 50).value = Cells(r, 51).value
Cells(r, 51).value = Cells(r, 52).value
Wait
Cells(r, 52).value = Cells(r, 28).value

Cells(r, 53).value = Cells(r, 54).value 'close
Cells(r, 54).value = Cells(r, 55).value
Cells(r, 55).value = Cells(r, 56).value
Cells(r, 56).value = Cells(r, 57).value
Cells(r, 57).value = Cells(r, 58).value
Wait
Cells(r, 58).value = Cells(r, 29).value

Cells(r, 59).value = Cells(r, 60).value 'WAP
Cells(r, 60).value = Cells(r, 61).value
Cells(r, 61).value = Cells(r, 62).value
Cells(r, 62).value = Cells(r, 63).value
Cells(r, 63).value = Cells(r, 64).value
Wait
Cells(r, 64).value = Cells(r, 30).value

End Select
End With
End Sub[/vba]