Loading price history from text files

This tutorial starts from a brand new settings file, loads a text file of historical tick prices, creates price bars from those ticks, and charts the price bars. The file in question was obtained from Gain Capital, but the theory is the same for text files from any source.

Note, however, that the file we used in this particular case was an older one. Gain Capital's newer files are slightly different. See the settings to use on the File Loader window.

This tutorial assumes:

1. You started ATM, created a new settings file, opened it, and ATM is sitting there ready to go.
2. You downloaded some text files containing historical prices.

This tutorial does these things:

1. Loads the historical tick prices from the files.
2. Creates 10-Minute bars from the ticks.
3. Creates Hourly bars from the 10-Minute.
4. Charts the 10-Minute bars.

A very similar process is described (in words) in this post.

Click here to watch the video, and use the browser's back button to come back. Or click here to open in a new window.
 

See also:

• Video: Backtesting

The files are available at http://freeserv.dukascopy.com/exp/. Also www.dukascopy.com/swiss/english/data_feed/csv_data_export/.

But you can't start the downloading of all the files and go and do other things - you have to export the data you want one-by-one. Scroll about 4/5 of the way down to get to the currencies available. They have |f| next to them. You can choose any format you like, but make sure you choose "Windows" in the "End line" field.

The good part is that they have data going back to 1986 for some currencies.

DukasCopy provides indicative data, which has no spread. During backtesting, make sure you add on a few pips in the "Commission" field to simulate the spread, otherwise your backtesting results will be overly optimistic.

The Currency Secrets website has covered the issue of indicative data in some depth, so I point you to their pages:

• Beware Of indicative data
• Is indicative data really *that* bad?

p.s. I think their conclusion is that as long as you add the spread, it should be ok.

I guess it's always better to use the prices from the broker you are going to use, if possible.

Once they are finished downloading, follow the rest of the instructions to load text files. DukasCopy historical prices are already in price bar format though. So you don't need to do the steps to create price bars, unless you want to create other price bars not provided by DukasCopy.
 

The method described below works the same for any symbol/interval/broker. We will use the example of creating EUR/USD Hourly and Daily price bars from historical tick prices provided by Gain Capital.

These steps assume you have already downloaded the text files containing the historical prices to your computer. See here for instructions for:

• Gain Capital
• Oanda
• DukasCopy
   

Create the Data Manager entries

Here's a good explanation of what Data Manager entries do.
 

1. Create a Data Manager entry to store the tick prices

1a. In ATM, click "Price Data" > "Price data configuration"
1b. Click "Add"
1c. In the top box, choose EUR/USD, Tick, Gain Capital
1d. In the bottom box, choose the bottom option - "Just set up (or keep)..."
1e. Click OK.
 

2. Create a Data Manager entry to store the Hourly bars

2a. (Should still have the Data Manager Search window open).  Click "Add"
2b. In the top box, choose EUR/USD, Hourly, Gain Capital
2c. In the bottom box, choose the second option - "...shorter time-period..."
2d. Choose "EUR/USD, Tick, Gain Capital" from the list.
2e. Click OK.
 

3. Create a Data Manager entry to store the Daily bars

3a. (Should still have the Data Manager Search window open).  Click "Add"
3b. In the top box, choose EUR/USD, Daily, Gain Capital
3c. In the bottom box, choose the second option - "...shorter time-period..."
3d. Now you have a choice - you can either choose "EUR/USD, Tick, Gain Capital" from the list, just like you did with the Hourly bars. That would mean the Daily bars are also made from the tick prices. Or, you can choose "EUR/USD, Hourly, Gain Capital", to have the Daily bars made from the Hourly. It's up to your preference, but the second option would be far quicker.
3e. Click OK.
 

Side note #1:

Each Data Manager entry creates a file on your computer which will store the prices. If you look in your file system you should now see 3 new files: gain_32_tk.tsphd, gain_32_1h.tsphd, and gain_32_1d.tsphd, respectively.
 

Side note #2:

The number 32 is what we call the "FIP ID", or "Financial Product ID". It's a number only useful within the Thinking Stuff world. 32 equates to the currency with symbol "EUR/USD".
 
Now to get the price history from the text files into the ATM files. To do this, we first create a "file load task", then process the task.
 

Load the files

 
4. Create the file load task

4a. Click "Price Data" > "Price history file loader"
4b. Files to load - browse for and select the file(s) to load. Recommend to just choose 1 file if this is your first time.
4c. Fill in the "File format" and "Dates in the file(s)" fields as per these standard settings for your broker.
4d. Price data to be loaded - EUR/USD, Tick, Gain Capital
4e. Click "Add To List"
 

5. Process the task

5a. Still on the File Loader window, go to "The Task List" tab
5b. Click "Start Loading"
5c. Wait.
 

6. Confirm the task worked

6a. Go to the "Completed List" tab.
6b. Scroll all the way to the right to see the result - "Success" is good. Anything in the "Error Text" field is bad.
6c. Back to the Data Manager Search window.
6d. Right-click on the "EUR/USD, Tick, Gain Capital" entry. A little pop-up menu appears. Choose "Table Information (row count, etc)"
6e. The message that shows up should display a count greater than zero.
 
So now we have the tick prices. What comes next is creating the Hourly and Daily price bars. This time we create "interval creation tasks", then process the tasks.
 

Create the price bars

 
7. Create the interval creation tasks

7a. (Should still have the Data Manager Search window open). Click (normal left-click) on the "EUR/USD, Hourly, Gain Capital" entry to highlight it.
7b. At the bottom of that window, go to the "Bulk Price Bar Creation" tab.
7c. Click "Add To List"
7d. Click on "EUR/USD, Daily, Gain Capital" to highlight it.
7e. Click "Add To List"
 

8. Process the tasks

8a. Still on the Data Manager Search window, go to "The Task List" tab
8b. Click "Start Creating Price Bars"
8c. Wait.
 

9. Confirm the tasks worked

9a. Go to the "Completed List" tab.
9b. Scroll all the way to the right to see the result - "Success" is good. Anything in the "Error Text" field is bad.
9c. Back to the "Price Data Configurations" tab.
9d. Right-click on the "EUR/USD, Hourly, Gain Capital" entry. A little pop-up menu appears. Choose "Table Information (row count, etc)"
9e. The message that shows up should display a count greater than zero. Same for the Daily one.
 

Cleanse the prices

Not all text files are created equal. Some have minor errors which can throw out your backtesting results. The easiest way to check for these errors is to view the chart. Ridiculous prices are immediately obvious.
 

10. View the chart

10a. Click "Price Data" > "View charts"
10b. Fill in the search criteria.
10c. Click "Chart"
 

11. Cleanse the data

11a. If the chart looks fine then you're all done.
11b. If there are extreme spikes in the chart then it's possible there is some erroneous data in the text files. Follow these steps to perform the cleanse.
 

[Note: these instructions for Oanda were written a couple of years ago. If you have more recent experience of obtaining their historical prices we would appreciate an update].

Oanda, through the FXTicks tool in their FXLabs section, offers tick data going back to January 1st, 2004. This is for five currencies only though - EUR/USD, GBP/USD, USD/CAD, USD/CHF, USD/JPY.

From the website:

In order to qualify for this service, you must have an FXTrade account with a balance of 1000 USD or more. Data may be ordered as far back as January 1, 2004, and as recently as two (2) months ago.

Steps to obtain the files:

1. Go to Oanda to order the files.
2. Fill in the form on that webpage. ATM can handle ASCII Text, CSV, and Pipe-delimited.
3. Repeat step 2 for all currencies that you want the data for.
4. Wait up to 2 weeks for Oanda to process your request. When they notify you, they will provide you with a website link to click.
5. Click the link and download the file. Whether you use downTHEMall! or not is up to you.

Now you have the files downloaded to your computer. But they are huge! Seriously. They are going to be more manageable if you split them into smaller files. That's where Text File Cleaver comes in.

Start Text File Cleaver, select the file to cleave, and select the directory to put the small files in. Click the "Go!" button. Repeat that for each big file you downloaded.

Then follow the rest of the instructions to load text files and create price bars. But on the File Loader window, select the smaller files to load instead of the big file.
 

The File format and Dates in the file(s) fields on the File Loader window have standard settings based on where you got the files from. (You could also just look in the file).

By "file formats", I mean how the price data is arranged in the text file. As an example, here's how Gain Capital price data looks like:

277248713,EUR/USD,2006-11-05 17:00:02,1.271600,1.271900,D

Comparing that with one from an Oanda text file, you can see they are completely different:

13/02/04 08:36:34,1.280600,1.280800
 

Gain Capital

 
Older files up to and including 2009/05 May/Week3

Order of fields - ID, Currency, Data, Bid, Ask, "D"
Delimiter - Comma
Ignore first row - No
Date format - yyyy-MM-dd HH:mm:ss
Timezone - New York (GMT-5/-4)
 

Files from 2009/05 May/Week4 up to and including 2009/11 November/Week3

Order of fields - ID, Currency, Data, Bid, Ask, "D"
Delimiter - Comma
Ignore first row - Yes
Date format - yyyy-MM-dd HH:mm:ss
Timezone - New York (GMT-5/-4)
 

Newer files from 2009/11 November/Week4 to present

Order of fields - ID, "D", Currency, Data, Bid, Ask
Delimiter - Comma
Ignore first row - Yes
Date format - yyyy-MM-dd HH:mm:ss
Timezone - New York (GMT-5/-4)
 

Oanda

Order of fields - Date, Bid, Ask
Delimiter - depends on what you requested from Oanda. XML files cannot be used.
Ignore first row - check the file
Date format - dd/MM/yy HH:mm:ss (note only 2-digit year)
Timezone - Greenwich Mean Time (GMT)
 

DukasCopy

Order of fields - Date, Time, Volume, Open, Close, Min, Max
Delimiter - depends on what you requested from DukasCopy
Ignore first row - check the file
Date format - can't remember - you should check the file
Timezone - Greenwich Mean Time (GMT)

Something to be aware of with these files is that there is no spread included. Therefore ensure you use a few pips of commission when backtesting, to try and mimick the spread. If you fail to do so, you're backtesting results will be overly fantastic.
 

ATM price history files (*.tsphd)

Order of fields - ATM Price History File (*.tsphd)
Delimiter - choose any
Date format - choose any
Timezone - choose any

The File Loader has some mandatory fields that aren't actually mandatory in this case. Just choose any value to get past the mandatory field check.
 

Text files exported by ATM v3+

Order of fields:

• for files containing tick prices - Date, Utc, FIP ID, Symbol, Bid, Ask, Volume
• for files containing price bars - Date, Utc, FIP ID, Symbol, Bid OHLC, Ask OHLC, Volume

Delimiter - depends on what the setting was when the file was exported.
Ignore first row - check the file
Date format - yyyy-MM-dd HH:mm:ss
Timezone - choose any

As the date and Utc offset is written into these files, the timezone field is not actually required. So just choose any value to get past the mandatory field check.
 

Text files exported by ATM pre-v3

Order of fields:

• for files containing tick prices - Date, Currency, Bid, Ask
• for files containing price bars - Date, Currency, Bid OHLC, Ask OHLC

Delimiter - depends on what the setting was when the file was exported.
Ignore first row - check the file
Date format - yyyy-MM-dd HH:mm:ss
Timezone - Greenwich Mean Time (GMT)
 

Other

ATM supports just about any date format, and any 1-character delimiter. So if you have a text file from somewhere else, all you have to do is somehow get the order of fields to match one of the available options.

If that's beyond your technical prowess, or you know of another popular file format that you think we should support, you can add a post to the feature request forum.
 

Gain Capital files are available to all - no account required.

1. Download the files from http://ratedata.gaincapital.com/ to your computer.
2. They are zip files, so unzip them.

Then follow the rest of the instructions to load text files and create price bars.
 

An easier way to download

Gain Capital gives one file per week per currency. About 20 currencies equals about 80 files per month and about 960 files per year. That's a lot of clicks. downTHEMAll! will make the downloading much easier than doing it one-by-one.

1. Browse to http://ratedata.gaincapital.com using Firefox.
2. Click through to the month of data that you want to download.
3. Click "Tools" > "downTHEMAll!" > "downTHEMall!..." from the main menu (in Firefox).
4. On the downTHEMall! window:
   1. In the Filters area at the bottom, check the "Archives (zip, rar...)" checkbox.
   2. Click the Browse button to select the directory you want to download the files to.
   3. Click the "Start Downloads!" button.