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