Gain Capital FX rate data

For most non-professional FX traders, one hurdle to start your own algorithmic trading research is tick-by-tick data. Gain Capital Group has been very kind and does the community a very good service by providing historical rate data on their website: http://ratedata.gaincapital.com/. The data look OK. Unfortunately, one serious problem that catches eyes are the very ad-hoc formats of the data files. For example,

• The newer csv files store data by weeks (e.g., in year 2010) while some older csv files store data by quarters (e.g., in year 2000).
• Some zipped files contains folders while other contains csv files.
• Some zipped files contains other zipped files.
• Some csv files have headers while others don’t.
• The orderings of the columns are not all the same for all csv files. Some csv files have even missing columns.
• The timestamp formats are not all the same.
• Worst, the csv files have different encoding!

In order to process these “raw” data zipped files into useful and more importantly usable format that we can use for research, I recommend these following steps.

1. We first unzip the raw zipped files recursively until we store store all plain csv files in one single folder. This is to handle the situation where zipped files in zipped files and folders in folders.
2. Read each csv file using an appropriate parser (depending on the encoding, format, headers, etc.), row by row, line by line.
3. Group the rows of the same date together and write them out as one csv file, e.g., AUDCAD.2007-10-7.csv.zip.
4. Zip the csv file for easy archive.
5. Write an adapter to read the processed zipped csv files and convert the data into whatever format your backtesting tool reads.

I have written some code to automate these steps, and they are now available in Algo Quant for free. Specifically, the important Java classes are:

1. GainCapitalFxCsvFilesExtractor.java: unzip all csv files into one folder
2. GainCapitalFXRawFile.java: read the Gain Capital csv files of various formats, save and zip the data by dates.
3. GainCapitalFXRawFile.java: read the zipped csv data files.
4. GainCapitalFXCacheFactory.java: the data feed/adapter to read the zipped csv data files into the Algo Quant backtesting system.

Here is a sample usage: To unzip the raw zipped files from Gain Capital, we do (c.f., source code here)

    public void test_0010() {
        GainCapitalFxCsvFilesExtracter extracter = new GainCapitalFxCsvFilesExtracter();
        String outputDirName = "./log/tmpGCcsv";
        String tmpDirName = "./log/tmpDir";
        int nCSV = extracter.extract("./test-data/gaincapital/2000", outputDirName,
            tmpDirName);
        assertEquals(18, nCSV);

        // clean up
        NMIO.deleteDir(new File(outputDirName));
        NMIO.deleteDir(new File(tmpDirName));
    }

To extract from the raw files the rows, group, save, and zip them by dates, we do (c.f., source code here)

    public void test_0030() throws IOException {
        String input = "C:/Temp/GCcsv";
        String output = "C:/Temp/byDates";

        File outputDir = new File(output);
        if (!outputDir.canWrite()) {
            outputDir.mkdir();
        }

        File inputDir = new File(input);

        //reading the file names
        File[] csvs = inputDir.listFiles(new FilenameFilter() {

            public boolean accept(File dir, String name) {
                return name.matches(".*[.]csv");
            }
        });

        //these pairs are already processed before, e.g., the program crashed
        Set done = new HashSet();
//        done.add("AUD_USD");
//        done.add("CHF_JPY");

        Set pairs = new HashSet();
        for (File csv : csvs) {
            String pair = csv.getName().substring(0, 7);
            if (pair.matches("..._...")) {
                if (!done.contains(pair)) {
                    pairs.add(pair);
                }
            }
        }

        GainCapitalFxDailyCsvZipFileConverter converter = new
              GainCapitalFxDailyCsvZipFileConverter();
        for (String pair : pairs) {
            String ccy1 = pair.substring(0, 3);
            String ccy2 = pair.substring(4, 7);
            System.out.println(String.format("working on %s/%s", ccy1, ccy2));
            converter.process(
                    ccy1, ccy2,
                    input,
                    String.format("%s/%s%s", output, ccy1, ccy2));
        }
    }

Have fun using the Gain Capital FX data for your own trading research!