MOVEMENT OF POINTS IN GIVEN DATA SET

[anup47]

Hi everybody

i have got a series of data which is captured live from exchange.

the format of the data is as follows:

Open high low close

5,912.60 5,912.60 5,907.25 5,911.65

5,911.95 5,920.80 5,911.95 5,920.70

5,920.80 5,925.00 5,920.80 5,924.60

5,924.10 5,927.00 5,922.25 5,927.00

5,927.00 5,932.00 5,927.00 5,931.10

5,931.70 5,933.00 5,924.70 5,924.70

5,926.00 5,926.00 5,922.30 5,924.00

5,924.80 5,925.00 5,924.00 5,924.00

5,924.00 5,924.00 5,918.35 5,919.00


etc.


I need to write a vba which can tell me how many time this data set points has changed by points 10 (for e.g., if we start with 5912.60 then when we reach 5922.60 then its one and then 5932.60 then its two and again if it is 5922.60 or 5942.60 then its three and so on, I need a vba since the no. of data points are in lakhs.) (Just to clarify i need to say if high is lets say 5950 and we start with 5912.60 it means it has moved 3 times i.e. 5922.60, 5932.60 and 5942.60)

regards

Anup

 

[Luke M]

Which points do we care about from the 4 you listed? Exaggerated Example:

Start: 100

Low: 70

High: 120

End: 110


Do we just care about start/stop points, in which case this is 1 point (100 -> 110) or do we follow the path of 100 -> 70 -> 120 -> 100 in which case it's 10 points, or is it just the volatility of 70 -> 120, which gives 5 points?

 

[anup47]

sir it should be from 5912.60 till 5919

sir in this example the data changes from 5912.60 to 5,925.00 i.e. one then from 5922.60 to 5933 and then from 5932.60 to 5918.35. here the movement occured 3 times.,


just to make it more clear. when we reached 5925 it means we have crossed 5922.60 etc


regards

anup

 

[Luke M]

You didn't answer my question directly, so I'm going to assume it's the second case. I tested this on 65536 rows of data, and it found an answer in under 10 seconds.

[pre]

Sub CountPoints()
Dim myRange As Range

Dim startValue As Double
Dim cVal As Double
Const pts = 10 'Define your point spread of interest
Dim ptUpper As Double
Dim ptLower As Double
Dim ptCount As Long

'IMPORTANT LINE!!!
'May need to modify, depending on setup
Set myRange = Range("A2:D600")

startValue = myRange.Cells(1, 1).Value
ptLower = startValue - pts
ptUpper = startValue + pts
ptCount = 0

For Each c In myRange

cVal = c.Value
If cVal >= ptUpper Then
'Price has gone up
ptCount = ptCount + 1
ptLower = ptLower + pts
ptUpper = ptUpper + pts

ElseIf cVal <= ptLower Then
'price has gone down
ptCount = ptCount + 1
ptLower = ptLower - pts
ptUpper = ptUpper - pts
End If
Next c

MsgBox "Number of points: " & ptCount, vbOKOnly
End Sub

[/pre]