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
|
|
|
|
Reply
|
 Rolf
|
2/2/2010 7:50:57 PM |
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
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
|
|
|
|
Reply
|
KC
|
2/6/2010 1:04:49 AM
|
|
|
20 Replies
309 Views
(page loaded in 0.463 seconds)
|
Search |
Post Question |
Groups |
Stream |
About |
Register
6508 articles. 
32 followers. 