ASCII Import

Overview

If you have historical market data available in files (e.g., because you purchased a database in the past), then you can choose to import this data into IQBroker. This process is described in Importing Data. In this section we describe in more detail how the ASCII import works.

The general procedure is as follows:

  • Create an ASCII Data Connection
  • Specify the directory that contains your ASCII files
  • Then, use IQBroker as usual. Whenever you perform an action that requires historical data (e.g., viewing a Chart), IQBroker will search the ASCII files for a file that contains this data, and then it will import the data.

Filenames

Whenever IQBroker wants to import data, it searches the directory that you had specified for a file that contains the desired data. It expects files that contain data for a certain symbol to have that symbol in their filename. The filename is case-insensitive, and the file extension is ignored. 

For example, if you're requesting data for AAPL then the following filenames are acceptable (i.e., IQBroker will read these files):

  1. Just the symbol. E.g.: AAPL.csv, or aapl.txt.
  2. Or: start with the symbol; then a punctuation character; and then the rest of the filename is ignored. E.g.: AAPL-20161101.txt, or AAPL-data.blah.

However, the following filenames will not work:

  • data.txt - doesn't contain "AAPL"
  • data-AAPL.txt - because it doesn't start with "AAPL"
  • AAPLdata.txt - because there's no punctuation character after "AAPL"

Once IQBroker has found all the files that contain data for the requested symbol it reads them one by one, to see which one contains the exact data that was requested. First, it needs to find a file that contains the requested bar type. E.g., if the request was for Minute bars, then only files that contain Minute data can be used; IQBroker will ignore files that contain Day or Tick data. Second, the data in the file must match the requested date range.

Filenames by Instrument Type

Here's what IQBroker expects symbols to look like:

Equities - the symbol as-is. E.g.: AAPL, IBM.

Forex - the symbol without slashes or any other punctuation characters. E.g.: EURUSD. (But not EUR-USD.)

Futures - the symbol in the filename should be formatted as SYMBOL_MM_YYYY, where MM_YYYY are the contract month and year. E.g.: ES_09_2016.

IQBroker supports several Futures contract types. The symbol shown above (e.g, "ES_09_2016") is used to import a Single Contract. But if you want to import a Continuous Contract then do the following:

1. Prepare a single ASCII file that contains long-range historical data for the contract. When naming this file, add the string "#0" to the end of the symbol. E.g.: "ES_09_2016#0". The string "#0" indicates that the file contains data for a "Continuous By Data Provider" contract.

2. In IQBroker, when you select the Futures contract, indicate that you want to use the contract type "Continuous By Data Provider".

If you want to import contracts of type "Continuous Back-Adjusted" or "Continuous Non Back-Adjusted" then you need to provide multiple files: one for each of the individual contracts that together form the continuous contract. E.g., your files would be named: "ES_09_2016", "ES_06_2016", "ES_03_2016", "ES_12_2015", etc.

If you want to import a "Cash-Settled" contract then add the character "$" to the end of the symbol. E.g.: "ES_09_2016$".

Examples

IQBroker auto-detects the file format. It recognizes a wide variety of formats, which are described below. But first, here are some examples of files that IQBroker is able to understand:

Date,Open,High,Low,Close,Volume,OpenInterest
2015-11-02,119.26,121.36,119.25,121.25,293091,0
2015-11-03,121,123.49,120.33,122.52,419470,0
2015-11-04,122.7,123.82,121.62,122.2,399557,0
2015-11-05,121.83,122.69,120.18,121.37,357944,0
2015-11-06,121.26,121.81,120.62,121,289695,0
2015-11-09,121.02,121.81,119.7,120.41,314079,0
2015-11-10,120.45,120.45,116.06,116.4,541001,0

When IQBroker tries to import this file, it automatically recognizes the following attributes of the file format:

  • Header Lines = 1 (IQBroker ignores lines at the start of the file that don't match the format of the rest of the lines)
  • Bar Type = Day (vs. Minute, Tick or Bid/Ask)
  • Date Format = YYYY-MM-DD (year in 4 digits; month in 2 digits; day in 2 digits)
  • Field Delimiter = comma (the character that separates different fields)
  • Decimal Delimiter = period (the character that separates the whole and fractional parts of a price)

Here's another file, which uses a different format:

Date Time Open High Low Close Volume OpenInterest

14112016 09:01:00 42,86 42,96 42,85 42,92 1138 0
14112016 09:02:00 42,92 42,92 42,75 42,77 980 0
14112016 09:03:00 42,76 42,85 42,74 42,84 900 0
14112016 09:04:00 42,84 42,88 42,8 42,86 567 0
14112016 09:05:00 42,87 42,87 42,81 42,85 415 0
14112016 09:06:00 42,86 42,96 42,85 42,9 878 0
14112016 09:07:00 42,9 42,93 42,86 42,91 430 0
14112016 09:08:00 42,91 42,91 42,89 42,9 221 0
14112016 09:09:00 42,9 42,91 42,82 42,87 422 0
14112016 09:10:00 42,86 42,93 42,86 42,91 372 0

Here are the auto-detected attributes of this file:

  • Header Lines = 2 (including the blank line on Line 2)
  • Bar Type = Minute
  • Date Format = DDMMYYYY
  • Time Format = HH:MM:SS (hour in 2 digits; minute in 2 digits; second in 2 digits)
  • Field Delimiter = space
  • Decimal Delimiter = comma

Supported File Formats

General Format

Files should have one bar per line.

There can optionally be some header lines at the start of the file: e.g., a line that contains the name of the fields. IQBroker automatically ignores these lines.

Very short files (with fewer than 5 lines) might not be recognized.

Supported Bar Types

Each file contains data for a single bar type. The following bar types are supported:

  • Day
  • Minute
  • Tick - each row specifies which type of tick it contains (Bid/Ask/Last)
  • Bid/Ask - similar to Tick bars, but each row contains both Bid and Ask ticks. This is used in Forex.

The way IQBroker determines whether a file contains Tick bars or Bid/Ask bars is by the symbol: for Forex symbols, it assumes that the file contains Bid/Ask bars. For other symbols, it assumes that the file contains Tick bars.

Field Order

IQBroker expects fields to appear in a certain order. Note that IQBroker does not read the field order from the header line, even if it exists: header lines are always ignored.

Day Bars

  • Date, Open, High, Low, Close, Volume, Open Interest

The "Open Interest" field is optional.

Minute Bars

  • Date+Time, Open, High, Low, Close, Volume, Open Interest
  • Date, Time, Open, High, Low, Close, Volume, Open Interest

The difference between these two formats is in how the Date and Time are formatted. In the first format (Date+Time), the date and time are part of the same field. In the second format (Date, Time) they are two separate fields.

The "Open Interest" field is optional.

Tick Bars

  • Date+Time, Price, Size, Type (Bid/Ask/Last)
  • Date, Time, Price, Size, Type

The "Type" field is optional; if it's missing then the tick is assumed to be a Last tick.

Bid/Ask Bars

  • Date+Time, Bid Price, Ask Price, Bid Size, Ask Size
  • Date, Time, Bid Price, Ask Price, Bid Size, Ask Size

The Bid Size and Ask Size are expected to be the full size of the trade, e.g. 300,000. They shouldn't specify the number of lots in the trade (e.g., 3).

Field Delimiters

The following characters may be used to separate fields: 

  • Comma
  • Space
  • Tab
  • Semicolon

Decimal Delimiters

The following characters may be used to separate the whole and fractional parts of a price:

  • Period
  • Comma

Date/Time Formats

The following date formats are supported. The most common format is highlighted in bold:

  • yyyy-MM-dd
  • yyyy-dd-MM
  • MM-dd-yyyy
  • dd-MM-yyyy
  • MMddyyyy
  • ddMMyyyy
  • yyyyMMdd
  • yyyyddMM
  • MM/dd/yyyy
  • dd/MM/yyyy
  • yyyy/MM/dd
  • yyyy/dd/MM
  • MM.dd.yyyy
  • dd.MM.yyyy
  • yyyy.MM.dd
  • yyyy.dd.MM
  • MMddyy
  • ddMMyy
  • yyMMdd
  • yyddMM
  • M/d/yyyy
  • d/M/yyyy
  • yyyy/M/d
  • yyyy/d/M
  • M-d-yyyy
  • dd-M-yyyy
  • yyyy-M-d
  • yyyy-d-M
  • d-MMM-yyyy

The following time formats are supported. The most common format is highlighted in bold:

  • HH:mm:ss
  • HH.mm.ss
  • HHmmss
  • HH:mm
  • HH.mm
  • HHmm
  • H:mm
  • H.mm
  • HH:mm:ss.fff
  • HH:mm:ss:fff
  • HH.mm.ss.fff
  • HHmmss.fff
  • HHmmssfff

("fff" = milliseconds)

ASCII Data Connection Settings

In addition to the auto-detected file format attributes (above), there are a couple of attributes that IQBroker can't auto-detect, so you must specify them yourself:

Time Zone - which timezone the timestamps in the file belong to. The default option, "Use exchange time", means that the timezone is assumed to be the one where the exchange is located. E.g., if the imported symbol is AAPL, which is traded on NASDAQ, then the timezone is assumed to be EST. If a different timezone is used (not the exchange's timezone) then you must specify it here.

Minute Bars Timestamp - when importing Minutes data, this determines whether the timestamps belong to the beginning or end of the minute.

This setting isn't used when importing Day data, because IQBroker auto-detects whether the timestamps belong to the beginning of end of the session in that case. This setting isn't used when importing Ticks data either, because ticks are a moment in time, so they don't have separate beginning and end timestamps.