Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Converting data (financial)

Avatar of cakester
cakester asked on
PerlC#Visual Basic.NET
33 Comments1 Solution596 ViewsLast Modified:
I have some market data in a CSV that I want to convert to the standard OHLC (Open High Low Close) format with a minimum timeframe of 1 minute, preferably 4 hour bars.
I have been searching around for a utility to do the job as after some extensive searching around there are a lot of other people trying to do the same thing as me.
I have tried utilities like MSConv, Meta2ASCII and Convert2MS but they don’t seem to be able to handle the HH:MM:SS timestamp. I think they expect a minimum of one minute data.

The output I need is like this:
=============================
Date,Time,Open,High,Low,Close
06/16/2010,1213,1.48001,1.48534,1.47252,1.47288
=============================
time in this example is 12.13pm

The same question has been asked in this thread and a coder has posted some code I am unfamiliar with.
See the Authors comments at the bottom..
https://www.experts-exchange.com/Database/MySQL/Q_22055783.html?sfQueryTermInfo=1+ohlc


I am looking for a solution to my problem either by some code or script that can be run locally on my PC or a recommended utility that I can buy that must do the job perfectly.
I am not a perl or C# programmer but have had some scripting experience and therefore can make logical modifications to a similar script to do the job if someone has some code like this.

Perhaps the code in the above thread can be adapted outside of a database, just to be run on my PC to process the data as a oneoff.
The tick data files can be as large as 2GB so keep this in mind........

Sample data file attached.


C
1184145144,D,GBP/USD,2010-06-13 17:01:24,1.457400,1.458100
1184145152,D,GBP/USD,2010-06-13 17:01:33,1.457300,1.457900
1184145177,D,GBP/USD,2010-06-13 17:01:40,1.457400,1.458100
1184145189,D,GBP/USD,2010-06-13 17:01:51,1.457300,1.458000
1184145204,D,GBP/USD,2010-06-13 17:01:56,1.457400,1.458100
1184145226,D,GBP/USD,2010-06-13 17:02:02,1.457200,1.457900
1184145281,D,GBP/USD,2010-06-13 17:02:26,1.457100,1.457800
1184145302,D,GBP/USD,2010-06-13 17:02:36,1.457200,1.457900
1184145333,D,GBP/USD,2010-06-13 17:02:43,1.457400,1.458100
1184145340,D,GBP/USD,2010-06-13 17:02:44,1.457200,1.457900
1184145379,D,GBP/USD,2010-06-13 17:02:53,1.457400,1.458100
1184145484,D,GBP/USD,2010-06-13 17:03:50,1.457300,1.458000
1184145490,D,GBP/USD,2010-06-13 17:03:56,1.457400,1.458100
1184145692,D,GBP/USD,2010-06-13 17:05:46,1.457200,1.457900
1184145720,D,GBP/USD,2010-06-13 17:06:03,1.457100,1.457800
1184145728,D,GBP/USD,2010-06-13 17:06:06,1.457200,1.457900
1184145735,D,GBP/USD,2010-06-13 17:06:17,1.457100,1.457800
1184145745,D,GBP/USD,2010-06-13 17:06:20,1.457200,1.457900
1184145750,D,GBP/USD,2010-06-13 17:06:23,1.457100,1.457800
1184145756,D,GBP/USD,2010-06-13 17:06:26,1.457200,1.457900
1184145762,D,GBP/USD,2010-06-13 17:06:32,1.457400,1.458100
1184145769,D,GBP/USD,2010-06-13 17:06:33,1.457200,1.457900
1184145787,D,GBP/USD,2010-06-13 17:06:37,1.457300,1.458000
1184145803,D,GBP/USD,2010-06-13 17:07:47,1.457200,1.457900
1184145861,D,GBP/USD,2010-06-13 17:09:36,1.457100,1.457800
1184145866,D,GBP/USD,2010-06-13 17:09:49,1.457200,1.457900
1184145871,D,GBP/USD,2010-06-13 17:09:49,1.457100,1.457800
1184146148,D,GBP/USD,2010-06-13 17:12:29,1.457200,1.457900
1184146155,D,GBP/USD,2010-06-13 17:12:29,1.457100,1.457800
1184146225,D,GBP/USD,2010-06-13 17:12:54,1.457200,1.457900
1184146251,D,GBP/USD,2010-06-13 17:13:01,1.457100,1.457800
1184146301,D,GBP/USD,2010-06-13 17:13:51,1.457000,1.457700
1184146319,D,GBP/USD,2010-06-13 17:13:51,1.457100,1.457800
1184146410,D,GBP/USD,2010-06-13 17:14:35,1.457200,1.457900
smgbpusd.zip