|
|
The table "File Format" is linked to data update from text file. In this function the text files formats which are used to update the historical data are defined.
It is possible to create any number of formats. In fact 3 or 4 will be sufficient to describe the majority of the format encountered.
|
New
|
Create New file format
|
|
Copy
|
Copy the selected File Format to a new one.
|
|
Delete
|
Delete the selected File Format
|
|
Save
|
Confirm and apply all modifications done so far.
|
|
Exit
|
Exit from the window.
|
To describe the correct file format, it is suggested to get from your data provider a typical file, and open it first with a text editor like notepad.
Usually, an update file has as many rows as the periods or securities to update. Each line is a record, and every record has the same format:
Here is reported a typical example of security updating with a daily interval:
BNL, D, 07/03/02, 1.7, 1.705, 1.57, 1.655, 38967700
Often, the first line in an update file, contains the headers with information on the columns content:
<>
<ticker>,<per>,<date>,<open>,<high>,<low>,<close>,<vol>
In the example above:
|
BNL
|
is the symbol which identifies a security
|
|
D
|
is the periodicity (Daily, in this case)
|
|
07/03/02
|
is the period date.
|
|
1.7
|
is the open price in the period, for the security
|
|
1.705
|
is the max price in the period, for the security
|
|
1.57
|
is the min price in the period, for the security
|
|
1.655
|
is the closing price in the period, for the security
|
|
38967700
|
is the volume exchanged in the period
|
To describe the record format follow these steps:
-
Define, for each field (date, time, open, close, min, close, volume, etc.) the column number corresponding to the record of the text file. Referring to the previous example, the Volume is found at column 8. This is the correct number to insert in the relative field. The symbol is on column one. The number 1 is the correct value to insert in the symbol field. Proceed the same way for all fields found.
It is possible to specify the same column number for some fields, in case for example that the specific column is not present. Other fields must have a unique column number. Some of these fields are mandatory (like the close, date, or time for intraday) others are optional. For optional values, when they do not exist in the text file, insert zero as the column number.
-
Define the symbol which is used for the periodicity in the text file. In this example "D" stands for "Daily", "I" is for "Intraday" and "W" is for "Weekly" and so on for all the others.
-
Then define:
-
The field separator used (for this example it is a comma). If the field separator is the TAB, then write TAB
-
the character used as a string delimiter, often it is the double quote character. In this example file, no string delimiter is used.
-
The decimal separator used (for this example is a dot)
-
The thousands separator used (for this example is none)
-
Define the exact date and time formats.
In the example the data format to insert is mm/dd/yy. where "mm" stands for month, "dd" for day, and "yy" represents the two digit year.
We will now see some example to make things clearer.
If in the text file, the data is in the format 20020701 where 2002 is the year, 07 is the month, and 01 it the day, the correct format to insert is yyyymmdd.
If in the text file the data is in the format 2002-07-01 where 2002 is the year, 07 is the month, and 01 it the day, the correct format to insert is yyyymmdd.
The same applies to the time format.
If in the text file the time is in the format 123001 where 12 is the hour, 30 are the minutes, and 01 are the seconds, the correct time format to insert is hhmmss.
If in the text file the time is in the format 13:23:01, where 13 is the hour, 23 are the minutes, and 01 are the seconds, the correct time format to insert is hh:mm:ss.
|
|
