help cleaning up data and retrieving the time of day for high and low values...

I don't understand your hours 9:30-14:15
there are a lot of hours that aren't in that range

Sub M_BSALV()
  Dim a, Dict As Object, MijnDatum As Date, MijnTijd As Double, arr(1 To 1, 1 To 6)
  Workbooks.OpenText Filename:="C:\testdata.txt", Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=True, Comma:=False, Space:=True, Other:=True, OtherChar:="\", FieldInfo:=Array(Array(1, 5), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)), DecimalSeparator:=".", ThousandsSeparator:=",", TrailingMinusNumbers:=False
  a = ActiveSheet.UsedRange.Value
  ActiveWorkbook.Close 0
  Set Dict = CreateObject("scripting.dictionary")
  With Dict
    For i = 1 To UBound(a)
      If IsDate(a(i, 1)) Then
        MijnDatum = a(i, 1) + TimeValue(Format(a(i, 2), "00\:00\:00"))

        GoTo EindeProbleem
        If Not IsEmpty(arr(1, 1)) Then
          If MijnDatum > arr(1, 1) Then
            Do
              a(1, 1) = DateAdd("s", 60, arr(1, 1))
              MijnTijd = arr(1, 1) - Int(arr(1, 1))
              If MijnTijd = WorksheetFunction.Median(TimeValue("09:30"), TimeValue("14:30"), MijnTijd) Then
                .Item(.Count) = arr
              Else
                MijnDatum = Int(MijnDatum) + 1 + TimeValue("09:30")
                If Weekday(MijnDatum, 1) > 5 Then MijnDatum = MijnDatum - Weekday(MijnDatum, 1) + 8
              End If
            Loop While Tijd > DateAdd("s", 60, a(i, 1))
          End If
        End If
EindeProbleem:

        arr(1, 1) = MijnDatum
        arr(1, 2) = a(i, 3)
        arr(1, 3) = a(i, 4)
        arr(1, 4) = a(i, 5)
        arr(1, 5) = a(i, 6)
        arr(1, 6) = a(i, 7)
        .Item(.Count) = arr
      End If
    Next
    Sheets.Add
    Range("A1").Resize(.Count, UBound(arr, 2)).Value = Application.Index(.items, 0, 0)
  End With
End Sub

BASLV,

Can I ask one more favor? Can you help me to amend the top line so that this will work on a Mac? I also got a compilation error complaining of "TrailingMinusNumbers" (named argument not found)

Thanks again.

see attachment

sorry , an error with the weekday

               arr(1, 1) = Int(arr(1, 1)) + 1 + TimeValue("09:30")
                If Weekday(arr(1, 1), vbMonday) > 5 Then arr(1, 1) = arr(1, 1) - Weekday(arr(1, 1), vbMonday) + 8

I've actually figured out a way to export the data more cleanly. It now comes direct to excel and there is no overnight data. I just need help determining the open, high, low and close of each day as well as the time the high and low were made. I hope this is helpful.

EDIT

I posted this before I saw your post above. This looks amazing! Thank you. Is there a way I can exclude Sundays? Also - is there a way to know at what time the high and low were made? I really appreciate the help.

Sub HighLow()
  Dim Dict     As Object, a, i&, it, arr(1 To 1, 1 To 5), Dag&
  a = Sheets("Bars_ZS").Range("A1").CurrentRegion.Value
  Set Dict = CreateObject("scripting.dictionary")
  With Dict
    For i = 2 To UBound(a)
      Dag = Int(CDbl(a(i, 1)))
      it = .Item(Dag)
      If VarType(it) = vbEmpty Then
        arr(1, 1) = Dag
        arr(1, 2) = a(i, 2)                                '1st high
        arr(1, 3) = a(i, 3)                                '1st low
        arr(1, 4) = CDbl(a(i, 1) - Dag)                    'time
        arr(1, 5) = arr(1, 4)                              'time
        .Item(Int(a(i, 1))) = arr
      Else
        If it(1, 2) < a(i, 2) Then                         'new high > highest
          it(1, 2) = a(i, 2)                               'new high
          it(1, 4) = CDbl(a(i, 1) - Dag)                   'time highest high
          .Item(Int(a(i, 1))) = it
        End If
        If it(1, 3) > a(i, 3) Then                         'new low < lowest
          it(1, 3) = a(i, 3)                               'new low
          it(1, 5) = CDbl(a(i, 1) - Dag)                   'time lowest low
          .Item(Int(a(i, 1))) = it
        End If
      End If
    Next
  End With

  With Sheets("Bars_ZS").Range("J2").Resize(Dict.Count, UBound(arr, 2))
    .Value = Application.Index(Dict.items, 0, 0)
    .Columns(1).NumberFormat = "ddd dd-mm-yy"
    .Columns(2).Resize(, 2).NumberFormat = "0.00"
    .Columns(4).Resize(, 2).NumberFormat = "hh:mm"
    .Offset(-1).Resize(1).Value = Array("date", "highest", "lowest", "Time Highest", "Time Lowest")
    .EntireColumn.AutoFit
  End With

End Sub

the old fashion way with an array