+ Reply to Thread
Results 1 to 2 of 2

High frequency data

Hybrid View

  1. #1
    Registered User
    Join Date
    02-14-2012
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    1

    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

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: High frequency data

    maybe something like this
    Sub ertert()
    Dim x, y(), i&, j&, k&
    With Range("A1").CurrentRegion
        x = .Resize(.Rows.Count + 1).Value
    End With
    ReDim y(1 To UBound(x, 1), 1 To UBound(x, 2))
    
    For i = 1 To UBound(x, 1) - 1
        j = j + 1
        For k = 1 To UBound(x, 2): y(j, k) = x(i, k): Next k
        Do While x(i, 1) = x(i + 1, 1): i = i + 1: Loop
    Next i
    
    If j > 0 Then [a1].Offset(, UBound(x, 2) + 1).Resize(j, UBound(x, 2)).Value = y
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1