Compressed seq data, again. I want to pay to make it work.

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? 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
 
V

Victor

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(helpercolumn7>0, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

Rolf said:
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



.
Click to expand...
 
R

Rolf

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 said:
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(helpercolumn7>0, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

Rolf said:
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



.
Click to expand...
Click to expand...
 
K

KC

Use ms query to return by date then.

Rolf said:
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 said:
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(helpercolumn7>0, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

Rolf said:
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



.
Click to expand...
Click to expand...
Click to expand...
 
K

KC

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 said:
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 said:
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(helpercolumn7>0, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

Rolf said:
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



.
Click to expand...
Click to expand...
Click to expand...
 
R

Rolf

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 said:
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 said:
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 said:
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(helpercolumn7>0, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

:

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



.
Click to expand...
Click to expand...
Click to expand...
 
K

KC

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 said:
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 said:
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 said:
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. :)


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(helpercolumn7>0, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

:

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



.
Click to expand...
Click to expand...
Click to expand...
 
R

Rolf

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 said:
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 said:
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 said:
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

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.
:)


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(helpercolumn7>0, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

:

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



.
Click to expand...
Click to expand...
Click to expand...
 
K

KC

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 said:
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 said:
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 said:
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" <[email protected]> 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

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. :)


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(helpercolumn7>0, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

:

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



.
Click to expand...
Click to expand...
Click to expand...
 
R

Rolf

Thank you for your suggestion. I have asked but no. :(
KC said:
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 said:
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 said:
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

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" <[email protected]> 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

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. :)


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(helpercolumn7>0, helpercolumn6+1, helpercolumn6);
helpercolumn9=ceiling(helpercolumn8,15);
sort by date/hour/helpercolumn9;

the rest is routine

:

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



.
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Williams

I tried this approach on a *small* file of data, and it *seemed* to work.
You can see if you can work out the details and whether it works on your
full dataset.
(or if you want to send a sample file I can try it myself out of
interest...)

Right now it dumps the results to a worksheet but an ADO recordset also has
a "GetString()" method.

http://www.w3schools.com/ado/met_rs_getstring.asp

Tim


'**************************************************
Sub TestText()

Dim FileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f, i As Integer, sSQL As String
Dim rngDest As Range

FileTitle = "trades.txt"

sSQL = " select [date], round([time]*24*4)/(24*4) as rndTime, " & _
" max([price]) as MaxPrice, " & _
" min([price]) as MinPrice, " & _
" sum([volume]) as totalVolume from [" & _
FileTitle & "] group by [date], round([time]*24*4)/(24*4) "

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open sSQL, sConnectionString

Set rngDest = Sheet1.Range("A1")

i = 0
For Each f In rs.Fields
rngDest.Offset(0, i).Value = f.Name
i = i + 1
Next f

rngDest.Offset(1, 0).CopyFromRecordset rs


End Sub
'*************************************************
 
K

KC

Wow, I never dream of 24*4 for 15 minutes.

Tim Williams said:
I tried this approach on a *small* file of data, and it *seemed* to work.
You can see if you can work out the details and whether it works on your
full dataset.
(or if you want to send a sample file I can try it myself out of
interest...)

Right now it dumps the results to a worksheet but an ADO recordset also
has a "GetString()" method.

http://www.w3schools.com/ado/met_rs_getstring.asp

Tim


'**************************************************
Sub TestText()

Dim FileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f, i As Integer, sSQL As String
Dim rngDest As Range

FileTitle = "trades.txt"

sSQL = " select [date], round([time]*24*4)/(24*4) as rndTime, " & _
" max([price]) as MaxPrice, " & _
" min([price]) as MinPrice, " & _
" sum([volume]) as totalVolume from [" & _
FileTitle & "] group by [date], round([time]*24*4)/(24*4) "

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open sSQL, sConnectionString

Set rngDest = Sheet1.Range("A1")

i = 0
For Each f In rs.Fields
rngDest.Offset(0, i).Value = f.Name
i = i + 1
Next f

rngDest.Offset(1, 0).CopyFromRecordset rs


End Sub
'*************************************************


Rolf said:
Thank you for your suggestion. I have asked but no. :(
Click to expand...
Click to expand...
 
T

Tim Williams

Better to replace that 24*4 wit the actual value I suppose....

Tim

KC said:
Wow, I never dream of 24*4 for 15 minutes.

Tim Williams said:
I tried this approach on a *small* file of data, and it *seemed* to work.
You can see if you can work out the details and whether it works on your
full dataset.
(or if you want to send a sample file I can try it myself out of
interest...)

Right now it dumps the results to a worksheet but an ADO recordset also
has a "GetString()" method.

http://www.w3schools.com/ado/met_rs_getstring.asp

Tim


'**************************************************
Sub TestText()

Dim FileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f, i As Integer, sSQL As String
Dim rngDest As Range

FileTitle = "trades.txt"

sSQL = " select [date], round([time]*24*4)/(24*4) as rndTime, " & _
" max([price]) as MaxPrice, " & _
" min([price]) as MinPrice, " & _
" sum([volume]) as totalVolume from [" & _
FileTitle & "] group by [date], round([time]*24*4)/(24*4) "

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open sSQL, sConnectionString

Set rngDest = Sheet1.Range("A1")

i = 0
For Each f In rs.Fields
rngDest.Offset(0, i).Value = f.Name
i = i + 1
Next f

rngDest.Offset(1, 0).CopyFromRecordset rs


End Sub
'*************************************************


Rolf said:
Thank you for your suggestion. I have asked but no. :(
"KC" <[email protected]> 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" <[email protected]> wrote in message
Click to expand...
Click to expand...
Click to expand...
 
K

KC

I am curious to test the 24*4 theory.
10:05:00 x 24 x 4 gives 40.333
Rounding gives 40 but 41 is more correct in this situation.
I think my earlier suggestion to use "ceiling" is more appropriate.

Tim Williams said:
Better to replace that 24*4 wit the actual value I suppose....

Tim

KC said:
Wow, I never dream of 24*4 for 15 minutes.

Tim Williams said:
I tried this approach on a *small* file of data, and it *seemed* to work.
You can see if you can work out the details and whether it works on your
full dataset.
(or if you want to send a sample file I can try it myself out of
interest...)

Right now it dumps the results to a worksheet but an ADO recordset also
has a "GetString()" method.

http://www.w3schools.com/ado/met_rs_getstring.asp

Tim


'**************************************************
Sub TestText()

Dim FileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f, i As Integer, sSQL As String
Dim rngDest As Range

FileTitle = "trades.txt"

sSQL = " select [date], round([time]*24*4)/(24*4) as rndTime, " & _
" max([price]) as MaxPrice, " & _
" min([price]) as MinPrice, " & _
" sum([volume]) as totalVolume from [" & _
FileTitle & "] group by [date], round([time]*24*4)/(24*4) "

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open sSQL, sConnectionString

Set rngDest = Sheet1.Range("A1")

i = 0
For Each f In rs.Fields
rngDest.Offset(0, i).Value = f.Name
i = i + 1
Next f

rngDest.Offset(1, 0).CopyFromRecordset rs


End Sub
'*************************************************


Thank you for your suggestion. I have asked but no. :(
"KC" <[email protected]> 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.
Click to expand...
Click to expand...
Click to expand...
 
R

Rolf

Thank you for your suggestion!! I will mail you a bigger "trades.txt".
Regards Rolf
Tim Williams said:
Better to replace that 24*4 wit the actual value I suppose....

Tim

KC said:
Wow, I never dream of 24*4 for 15 minutes.

Tim Williams said:
I tried this approach on a *small* file of data, and it *seemed* to work.
You can see if you can work out the details and whether it works on your
full dataset.
(or if you want to send a sample file I can try it myself out of
interest...)

Right now it dumps the results to a worksheet but an ADO recordset also
has a "GetString()" method.

http://www.w3schools.com/ado/met_rs_getstring.asp

Tim


'**************************************************
Sub TestText()

Dim FileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f, i As Integer, sSQL As String
Dim rngDest As Range

FileTitle = "trades.txt"

sSQL = " select [date], round([time]*24*4)/(24*4) as rndTime, " & _
" max([price]) as MaxPrice, " & _
" min([price]) as MinPrice, " & _
" sum([volume]) as totalVolume from [" & _
FileTitle & "] group by [date], round([time]*24*4)/(24*4) "

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open sSQL, sConnectionString

Set rngDest = Sheet1.Range("A1")

i = 0
For Each f In rs.Fields
rngDest.Offset(0, i).Value = f.Name
i = i + 1
Next f

rngDest.Offset(1, 0).CopyFromRecordset rs


End Sub
'*************************************************


Thank you for your suggestion. I have asked but no. :(
"KC" <[email protected]> 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.
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Williams

If you do, make sure to zip it up first. And send to

tjwilliams gmail

Tim

Rolf said:
Thank you for your suggestion!! I will mail you a bigger "trades.txt".
Regards Rolf
Tim Williams said:
Better to replace that 24*4 wit the actual value I suppose....

Tim

KC said:
Wow, I never dream of 24*4 for 15 minutes.

I tried this approach on a *small* file of data, and it *seemed* to
work.
You can see if you can work out the details and whether it works on
your full dataset.
(or if you want to send a sample file I can try it myself out of
interest...)

Right now it dumps the results to a worksheet but an ADO recordset also
has a "GetString()" method.

http://www.w3schools.com/ado/met_rs_getstring.asp

Tim


'**************************************************
Sub TestText()

Dim FileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f, i As Integer, sSQL As String
Dim rngDest As Range

FileTitle = "trades.txt"

sSQL = " select [date], round([time]*24*4)/(24*4) as rndTime, " & _
" max([price]) as MaxPrice, " & _
" min([price]) as MinPrice, " & _
" sum([volume]) as totalVolume from [" & _
FileTitle & "] group by [date], round([time]*24*4)/(24*4) "

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open sSQL, sConnectionString

Set rngDest = Sheet1.Range("A1")

i = 0
For Each f In rs.Fields
rngDest.Offset(0, i).Value = f.Name
i = i + 1
Next f

rngDest.Offset(1, 0).CopyFromRecordset rs


End Sub
'*************************************************


Thank you for your suggestion. I have asked but no. :(
"KC" <[email protected]> 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.
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Williams

Kc,

Here's how it looks for me for a small time series.
Seems just to round to the nearest 15min as expected.

Time Binned
2:01:00 2:00:00 AM
2:02:00 2:00:00 AM
2:03:00 2:00:00 AM
2:04:00 2:00:00 AM
2:05:00 2:00:00 AM
2:06:00 2:00:00 AM
2:07:00 2:00:00 AM
2:08:00 2:15:00 AM
2:09:00 2:15:00 AM
2:10:00 2:15:00 AM
2:11:00 2:15:00 AM
2:12:00 2:15:00 AM
2:13:00 2:15:00 AM
2:14:00 2:15:00 AM
2:15:00 2:15:00 AM
2:16:00 2:15:00 AM
2:17:00 2:15:00 AM
2:18:00 2:15:00 AM
2:19:00 2:15:00 AM
2:20:00 2:15:00 AM
2:21:00 2:15:00 AM
2:22:00 2:15:00 AM
2:23:00 2:30:00 AM
2:24:00 2:30:00 AM
2:25:00 2:30:00 AM
2:26:00 2:30:00 AM
2:27:00 2:30:00 AM
2:28:00 2:30:00 AM
2:29:00 2:30:00 AM
2:30:00 2:30:00 AM
2:31:00 2:30:00 AM


Tim

KC said:
I am curious to test the 24*4 theory.
10:05:00 x 24 x 4 gives 40.333
Rounding gives 40 but 41 is more correct in this situation.
I think my earlier suggestion to use "ceiling" is more appropriate.

Tim Williams said:
Better to replace that 24*4 wit the actual value I suppose....

Tim

KC said:
Wow, I never dream of 24*4 for 15 minutes.

I tried this approach on a *small* file of data, and it *seemed* to
work.
You can see if you can work out the details and whether it works on
your full dataset.
(or if you want to send a sample file I can try it myself out of
interest...)

Right now it dumps the results to a worksheet but an ADO recordset also
has a "GetString()" method.

http://www.w3schools.com/ado/met_rs_getstring.asp

Tim


'**************************************************
Sub TestText()

Dim FileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f, i As Integer, sSQL As String
Dim rngDest As Range

FileTitle = "trades.txt"

sSQL = " select [date], round([time]*24*4)/(24*4) as rndTime, " & _
" max([price]) as MaxPrice, " & _
" min([price]) as MinPrice, " & _
" sum([volume]) as totalVolume from [" & _
FileTitle & "] group by [date], round([time]*24*4)/(24*4) "

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open sSQL, sConnectionString

Set rngDest = Sheet1.Range("A1")

i = 0
For Each f In rs.Fields
rngDest.Offset(0, i).Value = f.Name
i = i + 1
Next f

rngDest.Offset(1, 0).CopyFromRecordset rs


End Sub
'*************************************************


Thank you for your suggestion. I have asked but no. :(
"KC" <[email protected]> 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.
Click to expand...
Click to expand...
Click to expand...
 
K

KC

I trust the first 7 minutes should be in the first 15 minute block. Rolf
will confirm to you.

Tim Williams said:
Kc,

Here's how it looks for me for a small time series.
Seems just to round to the nearest 15min as expected.

Time Binned
2:01:00 2:00:00 AM
2:02:00 2:00:00 AM
2:03:00 2:00:00 AM
2:04:00 2:00:00 AM
2:05:00 2:00:00 AM
2:06:00 2:00:00 AM
2:07:00 2:00:00 AM
2:08:00 2:15:00 AM
2:09:00 2:15:00 AM
2:10:00 2:15:00 AM
2:11:00 2:15:00 AM
2:12:00 2:15:00 AM
2:13:00 2:15:00 AM
2:14:00 2:15:00 AM
2:15:00 2:15:00 AM
2:16:00 2:15:00 AM
2:17:00 2:15:00 AM
2:18:00 2:15:00 AM
2:19:00 2:15:00 AM
2:20:00 2:15:00 AM
2:21:00 2:15:00 AM
2:22:00 2:15:00 AM
2:23:00 2:30:00 AM
2:24:00 2:30:00 AM
2:25:00 2:30:00 AM
2:26:00 2:30:00 AM
2:27:00 2:30:00 AM
2:28:00 2:30:00 AM
2:29:00 2:30:00 AM
2:30:00 2:30:00 AM
2:31:00 2:30:00 AM


Tim

KC said:
I am curious to test the 24*4 theory.
10:05:00 x 24 x 4 gives 40.333
Rounding gives 40 but 41 is more correct in this situation.
I think my earlier suggestion to use "ceiling" is more appropriate.

Tim Williams said:
Better to replace that 24*4 wit the actual value I suppose....

Tim

Wow, I never dream of 24*4 for 15 minutes.

I tried this approach on a *small* file of data, and it *seemed* to
work.
You can see if you can work out the details and whether it works on
your full dataset.
(or if you want to send a sample file I can try it myself out of
interest...)

Right now it dumps the results to a worksheet but an ADO recordset
also has a "GetString()" method.

http://www.w3schools.com/ado/met_rs_getstring.asp

Tim


'**************************************************
Sub TestText()

Dim FileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f, i As Integer, sSQL As String
Dim rngDest As Range

FileTitle = "trades.txt"

sSQL = " select [date], round([time]*24*4)/(24*4) as rndTime, " & _
" max([price]) as MaxPrice, " & _
" min([price]) as MinPrice, " & _
" sum([volume]) as totalVolume from [" & _
FileTitle & "] group by [date], round([time]*24*4)/(24*4) "

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open sSQL, sConnectionString

Set rngDest = Sheet1.Range("A1")

i = 0
For Each f In rs.Fields
rngDest.Offset(0, i).Value = f.Name
i = i + 1
Next f

rngDest.Offset(1, 0).CopyFromRecordset rs


End Sub
'*************************************************


Thank you for your suggestion. I have asked but no. :(
"KC" <[email protected]> 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.
Click to expand...
Click to expand...
Click to expand...
 
R

Rolf

I would prefer 2:01 until 2:15 goes to 2:15...
Does it work better if you compress it into 1 minute- chunks? Rolf

KC said:
I trust the first 7 minutes should be in the first 15 minute block. Rolf
will confirm to you.

Tim Williams said:
Kc,

Here's how it looks for me for a small time series.
Seems just to round to the nearest 15min as expected.

Time Binned
2:01:00 2:00:00 AM
2:02:00 2:00:00 AM
2:03:00 2:00:00 AM
2:04:00 2:00:00 AM
2:05:00 2:00:00 AM
2:06:00 2:00:00 AM
2:07:00 2:00:00 AM
2:08:00 2:15:00 AM
2:09:00 2:15:00 AM
2:10:00 2:15:00 AM
2:11:00 2:15:00 AM
2:12:00 2:15:00 AM
2:13:00 2:15:00 AM
2:14:00 2:15:00 AM
2:15:00 2:15:00 AM
2:16:00 2:15:00 AM
2:17:00 2:15:00 AM
2:18:00 2:15:00 AM
2:19:00 2:15:00 AM
2:20:00 2:15:00 AM
2:21:00 2:15:00 AM
2:22:00 2:15:00 AM
2:23:00 2:30:00 AM
2:24:00 2:30:00 AM
2:25:00 2:30:00 AM
2:26:00 2:30:00 AM
2:27:00 2:30:00 AM
2:28:00 2:30:00 AM
2:29:00 2:30:00 AM
2:30:00 2:30:00 AM
2:31:00 2:30:00 AM


Tim

KC said:
I am curious to test the 24*4 theory.
10:05:00 x 24 x 4 gives 40.333
Rounding gives 40 but 41 is more correct in this situation.
I think my earlier suggestion to use "ceiling" is more appropriate.

Better to replace that 24*4 wit the actual value I suppose....

Tim

Wow, I never dream of 24*4 for 15 minutes.

I tried this approach on a *small* file of data, and it *seemed* to
work.
You can see if you can work out the details and whether it works on
your full dataset.
(or if you want to send a sample file I can try it myself out of
interest...)

Right now it dumps the results to a worksheet but an ADO recordset
also has a "GetString()" method.

http://www.w3schools.com/ado/met_rs_getstring.asp

Tim


'**************************************************
Sub TestText()

Dim FileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f, i As Integer, sSQL As String
Dim rngDest As Range

FileTitle = "trades.txt"

sSQL = " select [date], round([time]*24*4)/(24*4) as rndTime, " &
_
" max([price]) as MaxPrice, " & _
" min([price]) as MinPrice, " & _
" sum([volume]) as totalVolume from [" & _
FileTitle & "] group by [date], round([time]*24*4)/(24*4)
"

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open sSQL, sConnectionString

Set rngDest = Sheet1.Range("A1")

i = 0
For Each f In rs.Fields
rngDest.Offset(0, i).Value = f.Name
i = i + 1
Next f

rngDest.Offset(1, 0).CopyFromRecordset rs


End Sub
'*************************************************


Thank you for your suggestion. I have asked but no. :(
"KC" <[email protected]> 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.
Click to expand...
Click to expand...
Click to expand...
 
K

KC

Hi Tim

Which ADO reference are you using please?
I cannot get the sub to work.

I am using this sql
SELECT Trades.datetraded, Trades.timetext, Left([timetext],2) AS [hour],
Mid([timetext],4,2) AS [minute], Mid([timetext],7,2) AS sec,
IIf([sec]>0,[minute]+1,[minute]) AS BigMin
FROM Trades;

Is there a function in sql for "ceiling" as equivalent in Excel please?

Tim Williams said:
I tried this approach on a *small* file of data, and it *seemed* to work.
You can see if you can work out the details and whether it works on your
full dataset.
(or if you want to send a sample file I can try it myself out of
interest...)

Right now it dumps the results to a worksheet but an ADO recordset also
has a "GetString()" method.

http://www.w3schools.com/ado/met_rs_getstring.asp

Tim


'**************************************************
Sub TestText()

Dim FileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f, i As Integer, sSQL As String
Dim rngDest As Range

FileTitle = "trades.txt"

sSQL = " select [date], round([time]*24*4)/(24*4) as rndTime, " & _
" max([price]) as MaxPrice, " & _
" min([price]) as MinPrice, " & _
" sum([volume]) as totalVolume from [" & _
FileTitle & "] group by [date], round([time]*24*4)/(24*4) "

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open sSQL, sConnectionString

Set rngDest = Sheet1.Range("A1")

i = 0
For Each f In rs.Fields
rngDest.Offset(0, i).Value = f.Name
i = i + 1
Next f

rngDest.Offset(1, 0).CopyFromRecordset rs


End Sub
'*************************************************


Rolf said:
Thank you for your suggestion. I have asked but no. :(
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compress seq data from txt-file into 15 min chunks in another file? 0
Tick data compressed into minute data? I want to pay to get this to work. 4

Top