Compress seq data. I want to pay to make it work. - 142146

Hi
This first step is written in VBA but if you can do it in VB it is even 
better.
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 6:17:05 AM
vb.general.discussion 1016 articles. 0 followers. Follow

0 Replies
658 Views

Similar Articles

[PageSpeed] 23

Reply:

Similar Artilces:

Money 2007 E-Pay
Noticed this morning when I was paying my bills this anomaly. Perhaps I am missing something. Scenario: Paying a credit card bill. In Money 2004 (my previous version of Money) I would use Credit Card as the category and in the subcategory use the particular credit card account I was paying. Worked great. Money 2007, there is a new category named Credit Cards/Transfer. When selecting this there are no payee categories listed. However further down the list is another category named Credit Card. Using Credit Card shows the various payees. Not sure what the difference is between Credit C...

My hide sheet option in excel is not working
My hide sheet option in Excel is not working. It is grayed out so I can not toggle nor use it to hide or unhide sheets. Security is at Medium - would appreciate any insight into how to fix this. Is the sheet protected? You cannot hide a protected sheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "arleen" <arleen @discussions.microsoft.com> wrote in message news:D028B90F-6DDB-4DE6-9F5C-22C137FBE927@microsoft.com... > My hide sheet option in Excel is not working. It is grayed out > so I can not > toggle n...

How do I make a 'box and whisker plot' graph in Microsoft Excel?
I am in Year 12 and am doing a stats assignment. The project states that i MUST insert a box and whisker plot for some data, but i don't know how to do this. Does anybody know? Hi, Here are some sites on the topic of stats charting not just box and whisker. http://peltiertech.com/Excel/Charts/statscharts.html http://www.processtrends.com/toc_statistical_charts.htm http://tushar-mehta.com/publish_train/data_analysis/16.htm http://people.stfx.ca/bliengme/ExcelTips.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Year 12 student who needs help" &...

Passing data from one form to another
Hello I have a form called frmMaindB and it has 5 text boxes on it (txtEmployeeTime, txtDTRegular, txtDTReason1, txtDTReason2, txtDTMaintenance) when I double click on the text box it opens up a pop up form named frm_DecimalConversion. On this form I have two text boxes one box I enter data into and the other calculates or converts the data to a decimal. The box that converts the data is called txtDecimal. Then I have a close button which I want to use to close the pop up form and insert the data into the text box I double clicked in to get the pop up or (frm_DecimalConversion). I have r...

How do I make hyperlink target scroll to the top-left of spreadshe
I can't remember the setting to make Excel automatically scroll my hyperlinked cell to the top-left. For example, if my hyperlink is in cell A1 and the hyperlink destination is cell Z30, when you click the hyperlink in A1 the cell Z30 is now displayed in the top-left of the spreadsheet. It seems like there was a checkbox in the Options dialog box, but now I can't find it. I did this awhile ago, but forgot to write down the information. Hi Lori Tools>Options On the Transition tab Transition navigations keys -- Regards Ron de Bruin http://www.rondebruin.nl "Lori W....

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

Ho to make one field required based on critera of another field?
I'm creating a form and need to make the "comments" field required if the "code" field is =>20. I appreciate suggestions! Deadline Monster is lurking! User enters the job processing endcode value (numeric) into the "code" field. If the endcode is =>20, comments are required. (P.S. I don't know VB) Thanks! Star You would put your validation code in the Form's BeforeUpdate event. If Me.EndCode >19 Then If Len(Me.Comments & "") = 0 Then MsgBox "Comments are required" Cancel = True End If End If ...

making an equation in access
Hi, I would like to create a field that works out a formula based on other fields. i.e field z=field4, when field 1<field 2 - field 3 etc. Yes, it may be a silly question but I am only new to this....Thanks, Dani. Dani, recommend against creating a "field" in a table and storing this value. One of the generally accepted rules of relational database development is not to store a "computed" (any value that is based on the value of other fields in your database) value in the table. It is a waste of database space, and will eventually result in bad data (...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of the ranges all charts in my view refreshes and it takes much time. My pc is aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to force the charts not to update all the time? ...

How to get a macro to do what i want?
I have designed a form in Excel and the data that is inputted i want to be able to save in a separate worksheet. The problem i am having is that eveytime i record the macro, it over writes the same row each time (if that makes sense). For example if i copied every cell in the form and paste them into the database Row 1, i then want the new piece of information inputted into the form to go into Row 2 and then Row 3 etc.... But it keeps going into Row 1, it wouldnt move down the rows. Hopefully this makes sense to someone and someone can give me some advise. Calculate the last row ...

how do I remove fx from the function line, can't enter data
I have the fx displayed just under my toolbar, and I can't enter or change data in any of the cells in the file. I can't get the red X, the Green check mark, or the black = sign to appear. There are very few areas that are not "greyed out" under the headings at the top. This situation applies to all of the excel files on this computer. I have Excel 2000. Please help. Can you move the cursor around anywhere in the spreadsheet? "dmdranch" wrote: > I have the fx displayed just under my toolbar, and I can't enter or change > data in any of the c...

Exchange Stops Working
Hello. MY SETUP: 1. Primary server named STECNICIL (THIS HAS WINDOWS 2003 ENTERPRISE SERVER) 2. Secondary server name TEC-EXCHNG (THIS IS THE EXCHANGE SBS SERVER 2003) All the user connect through STECNICIL to get internet and every other service... All users connect to TEC-EXCHNG to get their email through OUTLOOK 2003. MY PROBLEM: I don't know what's going on but approximatly every 30mn to 1 hour, the connections between the clients and the EXCHANGE SERVER stops working. No more email for clients after that period of time. I have restart the server and restart the EXCH...

how do i recover data in publisher
i have been entering addresses to set up a mail merge. i cllicked the "ok" button in the window and lost all data . can i recover it Look in a folder in My Documents named "My Data Sources". Publisher data is saved as .mdb(Access) file. Did you try selecting "Edit Address List" in the Mailings and Catalog menu (Tools)? -- Mary Sauer http://msauer.mvps.org/ "dee" <dee@discussions.microsoft.com> wrote in message news:690430F1-36DE-47EE-8B7D-DD12A096C075@microsoft.com... >i have been entering addresses to set up a mail merge. i cllicked ...

smtp connector restriction not working
Dear all Does anyone know how to debug smtp connector , Lately i configure smtp connector restriction , but it only work for one minute while i restart the smtp virtual server, after one minute , it seems that the restriction not working , so somebody help me to solve this problem,thanks. Chester Huang Chester: Which restriction are you talking about? Message size? Content Restrictions? Delivery times? Delivery restrictions? -- Jim McBee Blog: http://mostlyexchange.blogspot.com Web: http://www.somorita.com "Chester" <chesterh@aserve.com.tw>...

Pay code update with Integration Manager
I am trying to do a Pay Code update for all employees using Integration Manager using the General Payroll Update. I am able to get the standard Pay Codes to update, but cannot seem to get the "Based On" Pay Codes to update. When I log into GP and view the Employee Maintenance and Go To Pay Codes, I see the standard update. However the Based On Pay Codes remain the old rates unless I select select the Pay Code and simply click Ok. Then the update automatically is brought in. How can I get these updates to take without having to visit each of the Based On Pay Codes and sele...

Using subtotals as single data entries
Sorry about the subject--I couldn't figure out how to describe it simply. I have a large file (16,000 records) of amounts billed by roughly 10,000 service providers. A number of these providers have multiple office locations, so each record is unique to a specific office location. In other words, a provider who billed from 3 different office locations will have 3 entries. Each provider has a unique provider ID number, which stays the same regardless of which office location he is billing from. I want to be able to subtotal the amount billed by each provider for all their office locations...

I want to read,but:
The web display: Directory Listing Denied This Virtual Directory does not allow contents to be listed. could you tell how to access another web like this? "LeeTow" <fbjlt@pub3.fz.fj.cn> wrote in message news:eDQ5RlJmEHA.3612@TK2MSFTNGP12.phx.gbl... > The web display: > Directory Listing Denied > This Virtual Directory does not allow contents to be listed. > could you tell how to access another web like this? > > looks like the link got wrapped, you may have to copy it out and put it back together... or you could try this: http://tinyurl.com/3mtvv ...

How can I keep track of when (date and time) data is entered into.
I am trying to create a spreadsheet for a high school class. I need to be able to track when a student has entered data into specific cells of the spreadsheet. Any ideas? In the code behind the worksheet, enter (eg) Private Sub Worksheet_Change(ByVal Target As Range) Cells(1, 1).Value = Now() End Sub This will enter in Cell A1 the date and time at which any entry is made in that worksheet. If you need the location of the time-stamp to vary according to which cell is changed then you can test the value of Target and vary the destination cell accordingly. -- Return email address is n...

How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe ship w...

Retrieving sorted data from same table.
Hi All, I am working on a table (mentioned below) I am looking for a query which can get me the data according to the =93id=94 column with respect to speed. The condition is that I have to get three consecutive entries which have speed > 60 Below is the sample table with data on which I have to retrieve the data on above condition. The output i need can be as given below DVXC002 12/10/09 0:12 96 DVXC002 12/10/09 18:40 89 DVXC002 12/10/09 19:43 65 DVXC005 12/10/09 11:56 69 DVXC005 12/10/09 15:26 62 DVXC005 12/10/09 17:35 85 Need your help urgently....Thanks in advan...

"me." doesn't work
I write a lot of VBA. I find it very useful to start lines referring to the object I'm writing with "me." so that I can get the list of properties and methods. In the project I'm currently working on, this has suddenly stopped working. I type "me." and nothing happens. I can't figure out if I've accidentally disabled a feature or something like that. Anyone have any ideas? Thanks much in advance. "accesswanabe" <accesswanabe@discussions.microsoft.com> wrote in message news:B6B82001-53C9-4932-9BA5-D9D6B454DE1B@microsoft.c...

Import directory data into Excel 2003
I have over 1000 media files that I would like to extract information from and put into an Excel spreadsheet. Using Explorer, I have defined the fields I would like to see, such as title, duration, comment etc. Now, I need to import this data into Excel. So far, I've not been able to find a way to do this. Can someone offer some suggestions please? Thanks, Nigel -- www.myoldcontacts.com - Tell your friends to tell their friends www.sysadmininc.com - Consultancy, Service, Sales, Networking... www.british-expats.com - Connect with British Expats World Wide www.kxez.com/shows_britishinv...

Showing the perimeter of a set of (X,Y) data!
Good day all, I need to plot the perimeter of a set of data. I have a set of (X,Y) data with error bar and it is a nice mess so I just actually need to see (show) the area were the data can be found. Then hopefully overlay an other set of (X',Y') data and show that they both cover the same surface of existence. i.e this is a set of metrology measurement in X and Y of a part build from different mould. Obviously you get a nice cloud of X and Y but does the new material offer the same 'cloud' ? Thank you I think the easiest way to do this is plot the data on a XY Scatter cha...

show last data point in chart
Hello, I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? Thank you. Nathan - > I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? < Click the charted data once to select the entire data series. Pause. Click the single point to select it. Then use the Format menu. - Mike www.mikemiddleton.com Thanks for your reply. Well, that would work if I knew which point on the chart ...