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

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



0
Rolf
2/2/2010 7:50:57 PM
excel.programming 6508 articles. 2 followers. Follow

20 Replies
1015 Views

Similar Articles

[PageSpeed] 49

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" 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
> 
> 
> 
> .
> 
0
Utf
2/3/2010 12:52:01 PM
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" <Victor@discussions.microsoft.com> schrieb im Newsbeitrag 
news:42AB3995-4940-4DBC-8809-6644D2D58F69@microsoft.com...
>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" 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
>>
>>
>>
>> .
>> 


0
Rolf
2/3/2010 1:41:51 PM
Use ms query to return by date then.

"Rolf" <rolfe@algonet.se> wrote in message 
news:%239KMpaNpKHA.5224@TK2MSFTNGP05.phx.gbl...
> 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" <Victor@discussions.microsoft.com> schrieb im Newsbeitrag 
> news:42AB3995-4940-4DBC-8809-6644D2D58F69@microsoft.com...
>>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" 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
>>>
>>>
>>>
>>> .
>>>
>
> 


0
KC
2/4/2010 12:39:09 AM
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" <rolfe@algonet.se> wrote in message 
news:%239KMpaNpKHA.5224@TK2MSFTNGP05.phx.gbl...
> 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" <Victor@discussions.microsoft.com> schrieb im Newsbeitrag 
> news:42AB3995-4940-4DBC-8809-6644D2D58F69@microsoft.com...
>>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" 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
>>>
>>>
>>>
>>> .
>>>
>
> 


0
KC
2/4/2010 3:13:37 AM
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" <pynasocas@yahoo.com.sg> skrev i meddelandet 
news:%23mVV6eUpKHA.4836@TK2MSFTNGP05.phx.gbl...
> 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" <rolfe@algonet.se> wrote in message 
> news:%239KMpaNpKHA.5224@TK2MSFTNGP05.phx.gbl...
>> 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" <Victor@discussions.microsoft.com> schrieb im Newsbeitrag 
>> news:42AB3995-4940-4DBC-8809-6644D2D58F69@microsoft.com...
>>>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" 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
>>>>
>>>>
>>>>
>>>> .
>>>>
>>
>>
>
> 

0
Rolf
2/4/2010 3:16:36 AM
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" <rolfe@algonet.se> wrote in message 
news:uTAC%23jUpKHA.4860@TK2MSFTNGP05.phx.gbl...
> 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" <pynasocas@yahoo.com.sg> skrev i meddelandet 
> news:%23mVV6eUpKHA.4836@TK2MSFTNGP05.phx.gbl...
>> 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" <rolfe@algonet.se> wrote in message 
>> news:%239KMpaNpKHA.5224@TK2MSFTNGP05.phx.gbl...
>>> 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" <Victor@discussions.microsoft.com> schrieb im Newsbeitrag 
>>> news:42AB3995-4940-4DBC-8809-6644D2D58F69@microsoft.com...
>>>>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" 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
>>>>>
>>>>>
>>>>>
>>>>> .
>>>>>
>>>
>>>
>>
>>
> 


0
KC
2/4/2010 4:51:09 AM
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" <pynasocas@yahoo.com.sg> skrev i meddelandet 
news:OyYT5UVpKHA.5696@TK2MSFTNGP04.phx.gbl...
> 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" <rolfe@algonet.se> wrote in message 
> news:uTAC%23jUpKHA.4860@TK2MSFTNGP05.phx.gbl...
>> 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" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>> news:%23mVV6eUpKHA.4836@TK2MSFTNGP05.phx.gbl...
>>> 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" <rolfe@algonet.se> wrote in message 
>>> news:%239KMpaNpKHA.5224@TK2MSFTNGP05.phx.gbl...
>>>> 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" <Victor@discussions.microsoft.com> schrieb im Newsbeitrag 
>>>> news:42AB3995-4940-4DBC-8809-6644D2D58F69@microsoft.com...
>>>>>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" 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
>>>>>>
>>>>>>
>>>>>>
>>>>>> .
>>>>>>
>>>>
>>>>
>>>
>>>
>>
>
> 

0
Rolf
2/4/2010 10:57:09 AM
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" <rolfe@algonet.se> wrote in message 
news:%23Yp%23SlYpKHA.3948@TK2MSFTNGP06.phx.gbl...
> 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" <pynasocas@yahoo.com.sg> skrev i meddelandet 
> news:OyYT5UVpKHA.5696@TK2MSFTNGP04.phx.gbl...
>> 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" <rolfe@algonet.se> wrote in message 
>> news:uTAC%23jUpKHA.4860@TK2MSFTNGP05.phx.gbl...
>>> 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" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>>> news:%23mVV6eUpKHA.4836@TK2MSFTNGP05.phx.gbl...
>>>> 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" <rolfe@algonet.se> wrote in message 
>>>> news:%239KMpaNpKHA.5224@TK2MSFTNGP05.phx.gbl...
>>>>> 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" <Victor@discussions.microsoft.com> schrieb im Newsbeitrag 
>>>>> news:42AB3995-4940-4DBC-8809-6644D2D58F69@microsoft.com...
>>>>>>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" 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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> .
>>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
> 


0
KC
2/4/2010 11:53:01 AM
Thank you for your suggestion. I have asked but no. :(
"KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
> 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" <rolfe@algonet.se> wrote in message 
> news:%23Yp%23SlYpKHA.3948@TK2MSFTNGP06.phx.gbl...
>> 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" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>> news:OyYT5UVpKHA.5696@TK2MSFTNGP04.phx.gbl...
>>> 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" <rolfe@algonet.se> wrote in message 
>>> news:uTAC%23jUpKHA.4860@TK2MSFTNGP05.phx.gbl...
>>>> 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" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>>>> news:%23mVV6eUpKHA.4836@TK2MSFTNGP05.phx.gbl...
>>>>> 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" <rolfe@algonet.se> wrote in message 
>>>>> news:%239KMpaNpKHA.5224@TK2MSFTNGP05.phx.gbl...
>>>>>> 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" <Victor@discussions.microsoft.com> schrieb im Newsbeitrag 
>>>>>> news:42AB3995-4940-4DBC-8809-6644D2D58F69@microsoft.com...
>>>>>>>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" 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
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> .
>>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
> 

0
Rolf
2/4/2010 3:31:53 PM
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" <rolfe@algonet.se> wrote in message 
news:u17M1%23apKHA.5696@TK2MSFTNGP04.phx.gbl...
> Thank you for your suggestion. I have asked but no. :(
> "KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
> news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
>> 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" <rolfe@algonet.se> wrote in message


0
Tim
2/4/2010 11:21:59 PM
Wow, I never dream of 24*4 for 15 minutes.

"Tim Williams" <timjwilliams@comcast.net> wrote in message 
news:Oa7DdCfpKHA.1548@TK2MSFTNGP02.phx.gbl...
>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" <rolfe@algonet.se> wrote in message 
> news:u17M1%23apKHA.5696@TK2MSFTNGP04.phx.gbl...
>> Thank you for your suggestion. I have asked but no. :(
>> "KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>> news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>> 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" <rolfe@algonet.se> wrote in message
>
> 


0
KC
2/5/2010 12:16:51 AM
Better to replace that 24*4 wit the actual value I suppose....

Tim

"KC" <pynasocas@yahoo.com.sg> wrote in message 
news:%23blAvhfpKHA.4348@TK2MSFTNGP06.phx.gbl...
> Wow, I never dream of 24*4 for 15 minutes.
>
> "Tim Williams" <timjwilliams@comcast.net> wrote in message 
> news:Oa7DdCfpKHA.1548@TK2MSFTNGP02.phx.gbl...
>>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" <rolfe@algonet.se> wrote in message 
>> news:u17M1%23apKHA.5696@TK2MSFTNGP04.phx.gbl...
>>> Thank you for your suggestion. I have asked but no. :(
>>> "KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>>> news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>>> 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" <rolfe@algonet.se> wrote in message
>>
>>
>
> 


0
Tim
2/5/2010 12:36:04 AM
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" <timjwilliams@comcast.net> wrote in message 
news:O0AxprfpKHA.4608@TK2MSFTNGP02.phx.gbl...
> Better to replace that 24*4 wit the actual value I suppose....
>
> Tim
>
> "KC" <pynasocas@yahoo.com.sg> wrote in message 
> news:%23blAvhfpKHA.4348@TK2MSFTNGP06.phx.gbl...
>> Wow, I never dream of 24*4 for 15 minutes.
>>
>> "Tim Williams" <timjwilliams@comcast.net> wrote in message 
>> news:Oa7DdCfpKHA.1548@TK2MSFTNGP02.phx.gbl...
>>>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" <rolfe@algonet.se> wrote in message 
>>> news:u17M1%23apKHA.5696@TK2MSFTNGP04.phx.gbl...
>>>> Thank you for your suggestion. I have asked but no. :(
>>>> "KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>>>> news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>>>> 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" <rolfe@algonet.se> wrote in message
>>>
>>>
>>
>>
>
> 


0
KC
2/5/2010 2:49:18 AM
Thank you for your suggestion!! I will mail you a bigger "trades.txt". 
Regards Rolf
"Tim Williams" <timjwilliams@comcast.net> skrev i meddelandet 
news:O0AxprfpKHA.4608@TK2MSFTNGP02.phx.gbl...
> Better to replace that 24*4 wit the actual value I suppose....
>
> Tim
>
> "KC" <pynasocas@yahoo.com.sg> wrote in message 
> news:%23blAvhfpKHA.4348@TK2MSFTNGP06.phx.gbl...
>> Wow, I never dream of 24*4 for 15 minutes.
>>
>> "Tim Williams" <timjwilliams@comcast.net> wrote in message 
>> news:Oa7DdCfpKHA.1548@TK2MSFTNGP02.phx.gbl...
>>>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" <rolfe@algonet.se> wrote in message 
>>> news:u17M1%23apKHA.5696@TK2MSFTNGP04.phx.gbl...
>>>> Thank you for your suggestion. I have asked but no. :(
>>>> "KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>>>> news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>>>> 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" <rolfe@algonet.se> wrote in message
>>>
>>>
>>
>>
>
> 

0
Rolf
2/5/2010 3:41:55 AM
If you do, make sure to zip it up first.  And send to

tjwilliams   gmail

Tim

"Rolf" <rolfe@algonet.se> wrote in message 
news:efpyuWhpKHA.4836@TK2MSFTNGP02.phx.gbl...
> Thank you for your suggestion!! I will mail you a bigger "trades.txt". 
> Regards Rolf
> "Tim Williams" <timjwilliams@comcast.net> skrev i meddelandet 
> news:O0AxprfpKHA.4608@TK2MSFTNGP02.phx.gbl...
>> Better to replace that 24*4 wit the actual value I suppose....
>>
>> Tim
>>
>> "KC" <pynasocas@yahoo.com.sg> wrote in message 
>> news:%23blAvhfpKHA.4348@TK2MSFTNGP06.phx.gbl...
>>> Wow, I never dream of 24*4 for 15 minutes.
>>>
>>> "Tim Williams" <timjwilliams@comcast.net> wrote in message 
>>> news:Oa7DdCfpKHA.1548@TK2MSFTNGP02.phx.gbl...
>>>>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" <rolfe@algonet.se> wrote in message 
>>>> news:u17M1%23apKHA.5696@TK2MSFTNGP04.phx.gbl...
>>>>> Thank you for your suggestion. I have asked but no. :(
>>>>> "KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>>>>> news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>>>>> 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" <rolfe@algonet.se> wrote in message
>>>>
>>>>
>>>
>>>
>>
>>
> 


0
Tim
2/5/2010 4:41:09 AM
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" <pynasocas@yahoo.com.sg> wrote in message 
news:Oh83D2gpKHA.1544@TK2MSFTNGP06.phx.gbl...
>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" <timjwilliams@comcast.net> wrote in message 
> news:O0AxprfpKHA.4608@TK2MSFTNGP02.phx.gbl...
>> Better to replace that 24*4 wit the actual value I suppose....
>>
>> Tim
>>
>> "KC" <pynasocas@yahoo.com.sg> wrote in message 
>> news:%23blAvhfpKHA.4348@TK2MSFTNGP06.phx.gbl...
>>> Wow, I never dream of 24*4 for 15 minutes.
>>>
>>> "Tim Williams" <timjwilliams@comcast.net> wrote in message 
>>> news:Oa7DdCfpKHA.1548@TK2MSFTNGP02.phx.gbl...
>>>>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" <rolfe@algonet.se> wrote in message 
>>>> news:u17M1%23apKHA.5696@TK2MSFTNGP04.phx.gbl...
>>>>> Thank you for your suggestion. I have asked but no. :(
>>>>> "KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>>>>> news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>>>>> 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" <rolfe@algonet.se> wrote in message
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
Tim
2/5/2010 4:52:20 AM
I trust the first 7 minutes should be in the first 15 minute block. Rolf 
will confirm to you.

"Tim Williams" <timjwilliams@comcast.net> wrote in message 
news:%231n9a7hpKHA.3748@TK2MSFTNGP02.phx.gbl...
> 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" <pynasocas@yahoo.com.sg> wrote in message 
> news:Oh83D2gpKHA.1544@TK2MSFTNGP06.phx.gbl...
>>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" <timjwilliams@comcast.net> wrote in message 
>> news:O0AxprfpKHA.4608@TK2MSFTNGP02.phx.gbl...
>>> Better to replace that 24*4 wit the actual value I suppose....
>>>
>>> Tim
>>>
>>> "KC" <pynasocas@yahoo.com.sg> wrote in message 
>>> news:%23blAvhfpKHA.4348@TK2MSFTNGP06.phx.gbl...
>>>> Wow, I never dream of 24*4 for 15 minutes.
>>>>
>>>> "Tim Williams" <timjwilliams@comcast.net> wrote in message 
>>>> news:Oa7DdCfpKHA.1548@TK2MSFTNGP02.phx.gbl...
>>>>>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" <rolfe@algonet.se> wrote in message 
>>>>> news:u17M1%23apKHA.5696@TK2MSFTNGP04.phx.gbl...
>>>>>> Thank you for your suggestion. I have asked but no. :(
>>>>>> "KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>>>>>> news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>>>>>> 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" <rolfe@algonet.se> wrote in message
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
KC
2/5/2010 6:08:35 AM
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" <pynasocas@yahoo.com.sg> skrev i meddelandet 
news:eJ9UDlipKHA.4836@TK2MSFTNGP02.phx.gbl...
>I trust the first 7 minutes should be in the first 15 minute block. Rolf 
>will confirm to you.
>
> "Tim Williams" <timjwilliams@comcast.net> wrote in message 
> news:%231n9a7hpKHA.3748@TK2MSFTNGP02.phx.gbl...
>> 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" <pynasocas@yahoo.com.sg> wrote in message 
>> news:Oh83D2gpKHA.1544@TK2MSFTNGP06.phx.gbl...
>>>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" <timjwilliams@comcast.net> wrote in message 
>>> news:O0AxprfpKHA.4608@TK2MSFTNGP02.phx.gbl...
>>>> Better to replace that 24*4 wit the actual value I suppose....
>>>>
>>>> Tim
>>>>
>>>> "KC" <pynasocas@yahoo.com.sg> wrote in message 
>>>> news:%23blAvhfpKHA.4348@TK2MSFTNGP06.phx.gbl...
>>>>> Wow, I never dream of 24*4 for 15 minutes.
>>>>>
>>>>> "Tim Williams" <timjwilliams@comcast.net> wrote in message 
>>>>> news:Oa7DdCfpKHA.1548@TK2MSFTNGP02.phx.gbl...
>>>>>>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" <rolfe@algonet.se> wrote in message 
>>>>>> news:u17M1%23apKHA.5696@TK2MSFTNGP04.phx.gbl...
>>>>>>> Thank you for your suggestion. I have asked but no. :(
>>>>>>> "KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>>>>>>> news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>>>>>>> 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" <rolfe@algonet.se> wrote in message
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 

0
Rolf
2/5/2010 9:22:45 AM
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" <timjwilliams@comcast.net> wrote in message 
news:Oa7DdCfpKHA.1548@TK2MSFTNGP02.phx.gbl...
>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" <rolfe@algonet.se> wrote in message 
> news:u17M1%23apKHA.5696@TK2MSFTNGP04.phx.gbl...
>> Thank you for your suggestion. I have asked but no. :(
>> "KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>> news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>> 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" <rolfe@algonet.se> wrote in message
>
> 


0
KC
2/6/2010 12:49:35 AM
OK, per John Spencer advice

SELECT Trades.timetext AS T, Mid([t],4,2) AS M, Mid([t],7,2) AS S, 
IIf([s]>0,[m]+1,[m]) AS BM, -Int(-[bm]/15)*15 AS B
FROM Trades;

"KC" <pynasocas@yahoo.com.sg> wrote in message 
news:%234iG%23XspKHA.5308@TK2MSFTNGP05.phx.gbl...
> 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" <timjwilliams@comcast.net> wrote in message 
> news:Oa7DdCfpKHA.1548@TK2MSFTNGP02.phx.gbl...
>>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" <rolfe@algonet.se> wrote in message 
>> news:u17M1%23apKHA.5696@TK2MSFTNGP04.phx.gbl...
>>> Thank you for your suggestion. I have asked but no. :(
>>> "KC" <pynasocas@yahoo.com.sg> skrev i meddelandet 
>>> news:un3FgCZpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>>> 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" <rolfe@algonet.se> wrote in message
>>
>>
>
> 


0
KC
2/6/2010 1:04:49 AM
Reply:

Similar Artilces:

Entering Interest Only ARM mortgage data into MM2005
How do I enter the data correctly in MM 2005 for my loan type. I have not been able to figure this out. Any help would be appreciated! See http://umpmfaq.info/faqdb.php?q=160. There is an update coming to this one as soon as I get to it, but it won't change the substance of the answer. The update will refer to http://support.microsoft.com/default.aspx?scid=kb;en-us;893719. "Jason Stout" <Jason Stout@discussions.microsoft.com> wrote in message news:2289EADB-28F9-4B21-A6B0-8F61FB36C8D1@microsoft.com... > How do I enter the data correctly in MM 2005 for my loan type....

How to pick a biggest data from a group of data? #2
Thanks,I meet another question: I have some data with a date in he format below 03/11/1998 03/12/1998 03/13/1998 03/16/1998 I want to convert them into 19980311 19980312 19980313 19980316 I tried to set the cell property,but it didn't work. Could you please help to give me a formular to do so? Thanks in advance -- yoyo200 ----------------------------------------------------------------------- yoyo2000's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1548 View this thread: http://www.excelforum.com/showthread.php?threadid=27212 Use the custo...

data range
Hi I have a problem with external data. In the Data Range Properties, under Data Layout. There are 3 options after 'If the number of rows in the data range changes upon refresh:' 1 - Insert cells for new data, delete unused cells 2 - Insert entire rows for new data, clear unused cells 3 - Overwrite existing cells with new data, clear unused cells. I want option 3 to be the default oprion but it always defaults back to option 2. Is there any way I can change this.?? Cheers Baz ...

Keeping socket events coming and receiving all data
We're using CSocket, CSocketFile, and CArchive in our project. In an effort to keep this posting as streamlined as possible, I ask you to read first my original posting which describes the problem I was seeing and the environment in more detail. Please refer to "WM_SOCKET_NOTIFY just stops", dated 8/4/2004. The reply I received from George N to the original posting boosted my confidence in my approach to a workaround. I understood George's answer to mean this: as long as I reenable socket event notification before our thread's message loop runs again, there lik...

ColorIndex Sub-Routine not working as expected
Using XL 2003 & 97 The following routine is not functioning as expected. I expect that if the font color and the interior color are the same i.e. both white or the value of 2, that the interior color of the respective cell would be changed to Gray. When I recorded a macro for both variables, they were both valued as "2" Therefore, if 2=2 then the fill color s/b Gray? No? ************************ Sub ColorCellGray() If Selection.Font.ColorIndex = Selection.Interior.ColorIndex Then With Selection.Interior .ColorIndex = 38 ' Gray E...

The data file 'Mailbox
Good afternoon everyone, I have this user, using sbs 2003 SE, outlook 2003 on Windows xp, all of a sudden, when the outlook is started, he is getting a popup box from Microsoft Office Outlook. The data file 'Mailbox - username' was not closed properly. This file is bein gchecked for problems. Sometime take 30 minutes, sometime just halt the pc. Sometime when after restart the pc number of time, the time which take to chek the file would significantly reduced to about 10 minutes. Anyone any idea why this message. Is there a 'proper way' to to close the outlook 2003. Hav...

Re: Database Compression
Hi, Does anyone know any tools, microsoft or 3rd party for compressing exchange databases? There is a product call go exchange which can fix the database and compress it by upto 40%...however i have never seen it in action... Any recommendations on compressing a exchange 2003 database? Thx & Regards, In news:D4B54133-C15A-457E-AC58-D99ABF44DFAA@microsoft.com, Gary K <GaryK@discussions.microsoft.com> typed: > Hi, > > Does anyone know any tools, microsoft or 3rd party for compressing > exchange databases? > > There is a product call go exchange which can fix...

Sorting Data #10
I have downloaded an excel file from 'Lotto Draw History (https://www.national-lottery.co.uk/player/files/Lotto.csv) and want t sort the data so I can quickly check whether my numbers have previousl won any large amount in the past. I am struggling to do this and would appreciate any help. TIA, Mik -- mikey6 ----------------------------------------------------------------------- mikey69's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1323 View this thread: http://www.excelforum.com/showthread.php?threadid=31888 Hi, there is a formula (got it in this...

Graff data series
I have a graff for sales. Data series 1 = Budget Data series 2 = Actual I want the value that is shown with data series 2 to be a % of the value of data series 1. ...

Work Flow Manager not responding
I am triying to open the work flow manager, but never open. always i get the following message: CRM Server not responding. I tried with: localhost:port, servername:port, addressIp:port. never work, what happend. Thanks in advance. I was getting the same problem and I now use the server name as opposed to localhost. But now I am getting the following error: "You do not have sufficient Microsoft CRM privileges to run the Workflow Manager." Where do I set the privileges for this? Thanks Pete "savage" wrote: > remove :port > and also you need to run workf...

Filling in Data
I have several points where I need data. I only have the start and the end values and I want to fill in each of the points between the two. How is that accomplished. Example. Point 1 = 50, Point 10 = 600. I need Points 2-9. 550/9 = the increment to add moving from point 2 to point 9 Jamie wrote: > I have several points where I need data. I only have the start and the end > values and I want to fill in each of the points between the two. How is that > accomplished. > > Example. Point 1 = 50, Point 10 = 600. I need Points 2-9. Hi, Try this and ente...

How do I work with Excel 2007 and htm files?
I created a htm file in Excel 97-2003 and I was able to access my tables and change the information around. When I open the file in the new Excel 2007, it changes a lot of my numbers to dates and some other crazy numbers I have no idea where they come from. I've tried going to excel options and turning off automatic calculations but that doesn't help. How do I get excel to not change any of the information in the cells of my workbook? [In the previous excel all you had to do was insert an apostrophe ' to prevent any changes.] Example: Supposed to say: 01-05 Says: 39087 ...

Excel OLE data in PPT reverting to OLD data after macro updation
Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) ...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

don't want to show images for treeview child items
Hi, I set up a tree view control. I see all of the child items inherit their images from their parents. But for some child items, I don't want to show any image at all. How do I kill the inheritance? Your help is appreciated. JD I don't about child items inheriting images from the parent. But when you add your child items pass a -1 to InsertItem as the Image index. AliR. "JD" <jdt_young@yahoo.com> wrote in message news:uwvPga8%23HHA.5404@TK2MSFTNGP02.phx.gbl... > Hi, > > I set up a tree view control. I see all of the child items inherit their...

DATA HIDING
I WANT TO HIDE DATA IN A SINGLE CELL Format the cell with protection of Hidden, and then protect the worksheet. "REDSHARK" wrote: > I WANT TO HIDE DATA IN A SINGLE CELL ...

Chart does NOT show data entered for the values represented
I continue to find a problem in excel 2007 that was not present in 2003. I will input 2or more columns and rows of data to create a chart, then create the chart; but IF MORE THAN ONE data path is selected to be charted, the other data paths are charted incorrectly. They do not reflect the values in the cells! Obviously then, my charts are wrong and I can't rely on them for analysis. Please respond if this has happened to you and you know how to correct for this. I am very frustrated. -- Judi Hi Judi, Data Path? what is this, are your charts refering to other workbooks? Why ...

Combining data from 2 Columns into 1
I have a spread sheet that needs to be imported into a pre-set form. In the spread sheet the Area Code and Phone Number are separate columns, but in the form they are a single column. How do I get both the area code and phone number in just one column? This spread sheet has multiple phone numbers for each record and hundreds of records, so it is impractical to do it all by hand. Thanks, J. How about this: Assume area code in column a, phone number in colum b and you wan parens around area code (i.e. (403) 723-8833). 1. Insert a column to the right of column B (new column C). 2. Enter/p...

IMAGE
Hello I have set up some stationery but my nice crisp clean company logo looks poor qualiy when I do a test and receive it back in the email. It looks very good in outlook when sending but not so on receiving - does Outlook compress images to a certain quality - can I do something to ensure good quality image? thanks Jeff ...

Alphabetizing Data in Excel
I don't know if anyone in this forum knows the procedure (if there even is one), but I was hoping that someone may be able to inform me how I can edit data in Microsoft Excel such that the information in the first column can be rearranged in alphabetical order. As I say, I have no idea if there is even a function that will perform this, but it would be a great help if someone out there may know it. Thank you. PAR - Highlight the whole spreadsheet (very important!!!). Do this by clicking in the cell above 1 and to the left of A. The spreadsheet will have a dark color to it. Th...

GPO to Make a Screen Saver
Hi, I need some help about how to do a GPO for Screen Saver: - I have 2 jpg files and I intend to put them to run as Screen Saver in a interval of 10 minutes of user inativity. How Can I do that? Thanks for while. Luiz Hello Luiz, Never heard about an option to use just 2 pictures this way. You need a screensaver where you can add your own pictures and then can configuer screensaver GPO for this. Also would be nice to hear which OS versions are in use on the server and client side. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS...

Adding Formula to Cell with Data
Column A has hard coded data. I need to add to the hard coded data figure another cell. I do not want to insert a new column or change the format of my worksheet. How can this be done without manually adjusting each cell? =datanumber+CellAddress as in =123+B2 GatorGirl wrote: > Column A has hard coded data. I need to add to the hard coded data figure > another cell. I do not want to insert a new column or change the format of > my worksheet. How can this be done without manually adjusting each cell? > > What type of data in the cells? If numbers and you want ...

Graphics Compression in Visio
Hi, Will the new version of Visio correct the issue of automatically compressing a image on insert, it always blurs an image not matter what type (gif, jpg, png etc). Even when you set the compression to full it makes not difference. Thanks! ...

auto move cells/data
Does anyone know if its possible to get excell to automatically shift cells (and the data in them) if a new cell is added or an older one is deleted. This is what im trying to do. I have an excel spread sheet which is 6 colums and 30 rows wide. Each cell has a number in it going downwards in the list. Is it if i say delete the cell in say C13, is it possible to get excel to automatically move the cells up from the other colums, (ie the number that was in C30 is now in C29 and C30 is empty, so could it automatically move D1 into C30 and move the same for the rest of the rows, (ie move them ...

Data Manipulation (Trees?)
I have a set of records (over 5,000). Each record as a key tag and has multipel pointer to "Children". The Number of "Children" can be over 100. Example: rcd #1 ---> Rcd#5, Rcd#100, Rcd #2 On May 15, 4:02 pm, Pete <petebea...@comcast.net> wrote: > I have a set of records (over 5,000). Each record as a key tag and > has multipel pointer to "Children". The Number of "Children" can be > over 100. > > Example: > rcd #1 ---> Rcd#5, Rcd#100, Rcd #2 oooh for fast/fat finges: Lets continue: Rcd#2 -----> Rcd...