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? I want to pay to get a version that
works. 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
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 would try:

import into Excel in one gulp;
text to column into 4 columns;
column 2 is time format;
helpercolumn5=hour(column2);
helpercolumn6=minute(column2);
helpercolumn7=second(column2);
helpercolumn8=if(helpercolumn70, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

"Rolf" wrote:

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? I want to pay to get a version that
works. 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
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



.

Thanks for the suggestion. The problem is that the tick-file has over 7 000
000 lines. I opend it in Excel and it choked. "Only" 65 000 rows. :)


"Victor" schrieb im Newsbeitrag
...
I would try:

import into Excel in one gulp;
text to column into 4 columns;
column 2 is time format;
helpercolumn5=hour(column2);
helpercolumn6=minute(column2);
helpercolumn7=second(column2);
helpercolumn8=if(helpercolumn70, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

"Rolf" wrote:

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? I want to pay to get a version
that
works. 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
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



.

Use ms query to return by date then.

"Rolf" wrote in message
...
Thanks for the suggestion. The problem is that the tick-file has over 7
000 000 lines. I opend it in Excel and it choked. "Only" 65 000 rows. :)


"Victor" schrieb im Newsbeitrag
...
I would try:

import into Excel in one gulp;
text to column into 4 columns;
column 2 is time format;
helpercolumn5=hour(column2);
helpercolumn6=minute(column2);
helpercolumn7=second(column2);
helpercolumn8=if(helpercolumn70, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

"Rolf" wrote:

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? I want to pay to get a version
that
works. 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
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



.

On second thought, to stick with sequential read
I would read the source file into Access;
break the time field into hour, minute, second;
sort by date/hour/minute/seond;
dump it out in same format as source file.

Again the rest is routine

"Rolf" wrote in message
...
Thanks for the suggestion. The problem is that the tick-file has over 7
000 000 lines. I opend it in Excel and it choked. "Only" 65 000 rows. :)


"Victor" schrieb im Newsbeitrag
...
I would try:

import into Excel in one gulp;
text to column into 4 columns;
column 2 is time format;
helpercolumn5=hour(column2);
helpercolumn6=minute(column2);
helpercolumn7=second(column2);
helpercolumn8=if(helpercolumn70, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

"Rolf" wrote:

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? I want to pay to get a version
that
works. 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
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



.

Thank you for your suggestion. I was thinking that too but not hard enough
but now I will try. It was 10 years ago that I used Access.

"KC" skrev i meddelandet
...
On second thought, to stick with sequential read
I would read the source file into Access;
break the time field into hour, minute, second;
sort by date/hour/minute/seond;
dump it out in same format as source file.

Again the rest is routine

"Rolf" wrote in message
...
Thanks for the suggestion. The problem is that the tick-file has over 7
000 000 lines. I opend it in Excel and it choked. "Only" 65 000 rows. :)


"Victor" schrieb im Newsbeitrag
...
I would try:

import into Excel in one gulp;
text to column into 4 columns;
column 2 is time format;
helpercolumn5=hour(column2);
helpercolumn6=minute(column2);
helpercolumn7=second(column2);
helpercolumn8=if(helpercolumn70, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

"Rolf" wrote:

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? I want to pay to get a version
that
works. 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
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



.

Make it simpler
Read into Access;
sort by date/time;
dump it out.

sequential read #1 into Excel;
pause at change of date;
process data;
sequential append #2 out;
loop to EOF #1

cheers

"Rolf" wrote in message
...
Thank you for your suggestion. I was thinking that too but not hard enough
but now I will try. It was 10 years ago that I used Access.

"KC" skrev i meddelandet
...
On second thought, to stick with sequential read
I would read the source file into Access;
break the time field into hour, minute, second;
sort by date/hour/minute/seond;
dump it out in same format as source file.

Again the rest is routine

"Rolf" wrote in message
...
Thanks for the suggestion. The problem is that the tick-file has over 7
000 000 lines. I opend it in Excel and it choked. "Only" 65 000 rows. :)


"Victor" schrieb im Newsbeitrag
...
I would try:

import into Excel in one gulp;
text to column into 4 columns;
column 2 is time format;
helpercolumn5=hour(column2);
helpercolumn6=minute(column2);
helpercolumn7=second(column2);
helpercolumn8=if(helpercolumn70, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

"Rolf" wrote:

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? I want to pay to get a version
that
works. 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
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



.

Thank you for your suggestion. The sourcefile is sorted in date and time
from the start. The code in the question that I wrote compresses it into day
chunks. It is the adding in the code of compressing into minute chunks that
I can not manage. I dont see it clear enough. It should be easy but I am not
good at this. :(

"KC" skrev i meddelandet
...
Make it simpler
Read into Access;
sort by date/time;
dump it out.

sequential read #1 into Excel;
pause at change of date;
process data;
sequential append #2 out;
loop to EOF #1

cheers

"Rolf" wrote in message
...
Thank you for your suggestion. I was thinking that too but not hard
enough but now I will try. It was 10 years ago that I used Access.

"KC" skrev i meddelandet
...
On second thought, to stick with sequential read
I would read the source file into Access;
break the time field into hour, minute, second;
sort by date/hour/minute/seond;
dump it out in same format as source file.

Again the rest is routine

"Rolf" wrote in message
...
Thanks for the suggestion. The problem is that the tick-file has over 7
000 000 lines. I opend it in Excel and it choked. "Only" 65 000 rows.
:)


"Victor" schrieb im Newsbeitrag
...
I would try:

import into Excel in one gulp;
text to column into 4 columns;
column 2 is time format;
helpercolumn5=hour(column2);
helpercolumn6=minute(column2);
helpercolumn7=second(column2);
helpercolumn8=if(helpercolumn70, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

"Rolf" wrote:

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? I want to pay to get a
version that
works. 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
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



.

My suggestion on Feb 3 is explicit enough.
Give it to your favorite service provider.
I am sure he can code it up accordingly.

"Rolf" wrote in message
...
Thank you for your suggestion. The sourcefile is sorted in date and time
from the start. The code in the question that I wrote compresses it into
day chunks. It is the adding in the code of compressing into minute chunks
that I can not manage. I dont see it clear enough. It should be easy but I
am not good at this. :(

"KC" skrev i meddelandet
...
Make it simpler
Read into Access;
sort by date/time;
dump it out.

sequential read #1 into Excel;
pause at change of date;
process data;
sequential append #2 out;
loop to EOF #1

cheers

"Rolf" wrote in message
...
Thank you for your suggestion. I was thinking that too but not hard
enough but now I will try. It was 10 years ago that I used Access.

"KC" skrev i meddelandet
...
On second thought, to stick with sequential read
I would read the source file into Access;
break the time field into hour, minute, second;
sort by date/hour/minute/seond;
dump it out in same format as source file.

Again the rest is routine

"Rolf" wrote in message
...
Thanks for the suggestion. The problem is that the tick-file has over
7 000 000 lines. I opend it in Excel and it choked. "Only" 65 000
rows. :)


"Victor" schrieb im Newsbeitrag
...
I would try:

import into Excel in one gulp;
text to column into 4 columns;
column 2 is time format;
helpercolumn5=hour(column2);
helpercolumn6=minute(column2);
helpercolumn7=second(column2);
helpercolumn8=if(helpercolumn70, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

"Rolf" wrote:

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? I want to pay to get a
version that
works. 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
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



.

Thank you for your suggestion. I have asked but no. :(
"KC" skrev i meddelandet
...
My suggestion on Feb 3 is explicit enough.
Give it to your favorite service provider.
I am sure he can code it up accordingly.

"Rolf" wrote in message
...
Thank you for your suggestion. The sourcefile is sorted in date and time
from the start. The code in the question that I wrote compresses it into
day chunks. It is the adding in the code of compressing into minute
chunks that I can not manage. I dont see it clear enough. It should be
easy but I am not good at this. :(

"KC" skrev i meddelandet
...
Make it simpler
Read into Access;
sort by date/time;
dump it out.

sequential read #1 into Excel;
pause at change of date;
process data;
sequential append #2 out;
loop to EOF #1

cheers

"Rolf" wrote in message
...
Thank you for your suggestion. I was thinking that too but not hard
enough but now I will try. It was 10 years ago that I used Access.

"KC" skrev i meddelandet
...
On second thought, to stick with sequential read
I would read the source file into Access;
break the time field into hour, minute, second;
sort by date/hour/minute/seond;
dump it out in same format as source file.

Again the rest is routine

"Rolf" wrote in message
...
Thanks for the suggestion. The problem is that the tick-file has over
7 000 000 lines. I opend it in Excel and it choked. "Only" 65 000
rows. :)


"Victor" schrieb im Newsbeitrag
...
I would try:

import into Excel in one gulp;
text to column into 4 columns;
column 2 is time format;
helpercolumn5=hour(column2);
helpercolumn6=minute(column2);
helpercolumn7=second(column2);
helpercolumn8=if(helpercolumn70, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

"Rolf" wrote:

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? I want to pay to get a
version that
works. 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
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



.