VBA CODE FOR CALCULATION OF MOVEMENT IN SERIES OF NUMBERS.

[anup47]

Dear all

I've got a series of numbers lets say(90, 92, 86, 99, 100, 101, 102, 104, 109, 110, 111, 104, 107 ......120, 125, 130, 140 .......100, 95, 90, 85, 84, ......70) and these numbers are written in excel sheet in column b (the numbers are written in order b1, b2, b3 ...and so on).For simplicity we can assume that these are the daily closing prices of a stock.

In this series of numbers, starting with 100 say i need to know that how many 10 points movement are there. for example when we start with 100 and move forward we wait for 110 or 90 which ever comes first becomes the base for other move.say 110 comes first then we wait for 120 or 100 which ever comes first becomes the base for next move. Now in case 111 comes but 110 does not arrive it means that 110 has arrived since 111 is greater than 110. similarly for 100 if 99 comes we assume that that 100 has arrived, and 100 becomes the base for next move. this way i need to count how many such moves have occurred in this series of numbers.

I need a vba for this and also i need the flexibility for changing the starting no. and movement of points, which is in above case 100 and 10.


Regards

Anup

 

[NARAYANK991]

Hi Anup ,


It would be nice if you could upload a sample file , with the input data , and the expected output over at least 50 values , so that whatever solution is provided can be tested against that data.


Narayan

 

[anup47]

Hi Narayank,


the link to download excel spreadsheet is

http://vll.co.in/wp-content/uploads/2013/03/data-series.xlsx


here you would see the price chart, in this chart we you'll see the price is moving from 90, 100, 110, 120, 130, 140 again 130 then 140 150 and so on.


here the no. of movement is 8 till i've explained. I just want to know it with the help of vba rather than the chart.


regards

Anup.