R
Rolf
Hi
I want to compress "tick"-data (ie trades) from a file into another file. In
the target file I want to chunk the data into 15 minutes activity (or other
number of minutes) and summorise it into date, time, open, high, low, open
and volume. I have managed it with days but minutes is harder. I have 3
cells in a worksheet that I have given the names: Sourcefile, Sourcefolder
and Targetfolder. And I have added "eod" (EndOfDday) at the beginning of the
target file. Below have I put parts of the sourcefile and the targetfile for
days for that sourcefile. As I wrote I have not managed to code the minute
chunks but I want to show the day code so it is easier to understand. Can
somebody help me with a minute version? Regards Rolf
Tickdata.txt
DATE,TIME,PRICE,VOLUME
12/11/2009,08:00:11,5720.00,1
12/11/2009,08:00:12,5720.00,1
12/11/2009,09:02:14,5736.50,1
12/11/2009,09:02:14,5736.50,1
12/16/2009,10:24:09,5863.00,1
12/16/2009,10:24:10,5863.00,1
12/16/2009,10:47:45,5859.50,4
12/16/2009,10:47:45,5859.50,1
12/17/2009,17:39:07,5842.00,2
12/17/2009,17:39:08,5842.00,4
eodtickdata.txt
12/11/2009,10:24,5720,5736.5,5720,5736.5,4
12/16/2009,17:39,5863,5863,5859.5,5859.5,7
12/17/2009,17:39,5842,5842,5842,5842,6
Sub TickToDays()
Dim aDate As String
Dim bDate As Date
Dim currentDate As Date
Dim lastDate As Date
Dim totDays As Single
Dim aTime As String
Dim price As Single
Dim openp As Single
Dim highp As Single
Dim lowp As Single
Dim closep As Single
Dim vol As Single
Dim totVol As Single
Dim numLoops As Single
Dim aText1 As String
Dim aText2 As String
Dim aText3 As String
Dim aText4 As String
Dim theSourcefolder As String
Dim theTargetfolder As String
On Error GoTo ErrorStop
theSourcefolder = [Sourcefolder] & [Sourcefile]
theTargetfolder = [Targetfolder] & "eod" & [Sourcefile]
Open theSourcefolder For Input As #1
Open theTargetfolder For Output As #2
Input #1, aText1, aText2, aText3, aText4
totVol = 0
startdatum = 1
numLoops = 0
Input #1, aDate, aTime, price, vol
bDate = CDate(aDate)
lastDate = bDate
totVol = vol
numLoops = 1
openp = price
highp = price
lowp = price
Do Until EOF(1)
Input #1, aDate, aTime, price, vol
bDate = CDate(aDate)
bTime = CDate(aTime)
cTime = Format(bTime, "hh:mm")
If lastDate = bDate Then
If highp <= price Then highp = price
If lowp >= price Then lowp = price
closep = price
totVol = totVol + vol
Else
Print #2, lastDate & "," & cTime & "," & openp & "," & highp & "," &
lowp & "," & closep & "," & totVol
lastDate = bDate
openp = price
highp = price
lowp = price
closep = 0
totVol = 0
totVol = vol
totDays = totDays + 1
End If
numLoops = numLoops + 1
Loop
Print #2, lastDate & "," & cTime & "," & openp & "," & highp & "," & lowp &
"," & closep & "," & totVol
MsgBox (numLoops & " trades are compressed to " & totDays & " daydata.")
Close #1
Close #2
ErrorStop:
'Print #2, lastDate & "," & cTime & "," & openp & "," & highp & "," & lowp &
"," & closep & "," & totVol
'MsgBox (numLoops & " trades are compressed to " & totDays & " daydata.")
Close #1
Close #2
End Sub
I want to compress "tick"-data (ie trades) from a file into another file. In
the target file I want to chunk the data into 15 minutes activity (or other
number of minutes) and summorise it into date, time, open, high, low, open
and volume. I have managed it with days but minutes is harder. I have 3
cells in a worksheet that I have given the names: Sourcefile, Sourcefolder
and Targetfolder. And I have added "eod" (EndOfDday) at the beginning of the
target file. Below have I put parts of the sourcefile and the targetfile for
days for that sourcefile. As I wrote I have not managed to code the minute
chunks but I want to show the day code so it is easier to understand. Can
somebody help me with a minute version? Regards Rolf
Tickdata.txt
DATE,TIME,PRICE,VOLUME
12/11/2009,08:00:11,5720.00,1
12/11/2009,08:00:12,5720.00,1
12/11/2009,09:02:14,5736.50,1
12/11/2009,09:02:14,5736.50,1
12/16/2009,10:24:09,5863.00,1
12/16/2009,10:24:10,5863.00,1
12/16/2009,10:47:45,5859.50,4
12/16/2009,10:47:45,5859.50,1
12/17/2009,17:39:07,5842.00,2
12/17/2009,17:39:08,5842.00,4
eodtickdata.txt
12/11/2009,10:24,5720,5736.5,5720,5736.5,4
12/16/2009,17:39,5863,5863,5859.5,5859.5,7
12/17/2009,17:39,5842,5842,5842,5842,6
Sub TickToDays()
Dim aDate As String
Dim bDate As Date
Dim currentDate As Date
Dim lastDate As Date
Dim totDays As Single
Dim aTime As String
Dim price As Single
Dim openp As Single
Dim highp As Single
Dim lowp As Single
Dim closep As Single
Dim vol As Single
Dim totVol As Single
Dim numLoops As Single
Dim aText1 As String
Dim aText2 As String
Dim aText3 As String
Dim aText4 As String
Dim theSourcefolder As String
Dim theTargetfolder As String
On Error GoTo ErrorStop
theSourcefolder = [Sourcefolder] & [Sourcefile]
theTargetfolder = [Targetfolder] & "eod" & [Sourcefile]
Open theSourcefolder For Input As #1
Open theTargetfolder For Output As #2
Input #1, aText1, aText2, aText3, aText4
totVol = 0
startdatum = 1
numLoops = 0
Input #1, aDate, aTime, price, vol
bDate = CDate(aDate)
lastDate = bDate
totVol = vol
numLoops = 1
openp = price
highp = price
lowp = price
Do Until EOF(1)
Input #1, aDate, aTime, price, vol
bDate = CDate(aDate)
bTime = CDate(aTime)
cTime = Format(bTime, "hh:mm")
If lastDate = bDate Then
If highp <= price Then highp = price
If lowp >= price Then lowp = price
closep = price
totVol = totVol + vol
Else
Print #2, lastDate & "," & cTime & "," & openp & "," & highp & "," &
lowp & "," & closep & "," & totVol
lastDate = bDate
openp = price
highp = price
lowp = price
closep = 0
totVol = 0
totVol = vol
totDays = totDays + 1
End If
numLoops = numLoops + 1
Loop
Print #2, lastDate & "," & cTime & "," & openp & "," & highp & "," & lowp &
"," & closep & "," & totVol
MsgBox (numLoops & " trades are compressed to " & totDays & " daydata.")
Close #1
Close #2
ErrorStop:
'Print #2, lastDate & "," & cTime & "," & openp & "," & highp & "," & lowp &
"," & closep & "," & totVol
'MsgBox (numLoops & " trades are compressed to " & totDays & " daydata.")
Close #1
Close #2
End Sub