High frequency data

Hi everybody. I have 4 column data in the form of (below is a sample):


06/02/2012 15:30:00 TRADE 56.71 1000
06/02/2012 15:30:00 TRADE 56.7 1000
06/02/2012 15:30:00 TRADE 56.7 350
06/02/2012 15:30:01 TRADE 56.7 371
06/02/2012 15:30:01 TRADE 56.7 519
06/02/2012 15:30:01 TRADE 56.7 312
06/02/2012 15:30:01 TRADE 56.7 169
06/02/2012 15:30:01 TRADE 56.7 461
06/02/2012 15:30:01 TRADE 56.7 500
06/02/2012 15:30:01 TRADE 56.7 200
06/02/2012 15:30:01 TRADE 56.7 800
06/02/2012 15:30:03 TRADE 56.69 300
06/02/2012 15:30:05 TRADE 56.7 400
06/02/2012 15:30:06 TRADE 56.714 100
06/02/2012 15:30:06 TRADE 56.7 190
06/02/2012 15:30:08 TRADE 56.7 500
06/02/2012 15:30:10 TRADE 56.69 961
06/02/2012 15:30:10 TRADE 56.69 696
06/02/2012 15:30:10 TRADE 56.7 135
06/02/2012 15:30:10 TRADE 56.7 346
06/02/2012 15:30:11 TRADE 56.7 500
06/02/2012 15:30:12 TRADE 56.69 100


As you can see the price is in column 3 and volumn in colmn 4. The price changes at non equal time intervals and more than once in 1 second.

I want to change the intervals to 1 second and have the closing price within the second. So for the example above the output would be: 15:30:00 = 56.6, 15:30:01 = 56.7 , 15:30:02 = 56.7, 15:30:03 = 56.69 etc........

Need it from 15:30:00 to 22:00:00.

Look forward to hearing from some excel geniuses

maybe something like this

Please Login or Register  to view this content.