File formats for importing securities and security prices

This article describes the file formats used for importing securities and security prices to FA. The file format is a semicolon separated text file (CSV). One row in the file represents one contact or portfolio. You can configure the file delimiter of you CSV files in Importing preferences.

FAFormat_marketprices.csv

FAFormat_securities.csv

The columns of the file format are described below.

Updating securities

You can use CSV files to update existing securities or market price entries. For securities, specify the Security code in column 1. For market prices, specify Security update code and Market date values in columns 1 and 2. Put the values you want to update in other columns. The syntax to use for the column content depends on the field:

• For single-value fields, such as s.name , import the CSV file with the corrected data – the new values from the file will replace the existing ones.

• For multi-value fields that support three stars (***) syntax (see the filed description in the table):

• To add values to the ones that already exist, write three stars (***) in the beginning of the column content – the values saved in the system will be preserved, and the new one added to them. For details, see the field descriptions in the table.

• To replace an existing value, import the CSV file with the corrected data – the new values from the file will replace the existing ones.

• For other multi-value fields, see the field description in the table.

Note

You can also update existing securities with #6 Security ISIN code if the system cannot find a matching security with the security code from the system, but will find a security with the same ISIN code. In such a scenario, the security code will also be updated.

Removing security values with three minuses (---)

You can remove individual values from multi-value fields by marking them with three minuses (---) in the CSV file. Currently, this syntax can be used for tags and profiles (tags and profileAttributes fields). Specify Security code in column 1. Add the fields you want to remove prefixed with three minuses (---). If you edit the CSV file in MS Excel, you need to put an apostrophe in the beginning of the column. For example: '---<tag_name_1>.

To remove multiple values, divide them with a comma for tags, and with a hash (#) for profile attributes. For example: ---<tag_name_1>,---<tag_name_2> or ---<profile attribute>#---<profile attribute>.

You can remove certain values and add other ones, for example: --<tag name 1>,<tag name 2> or ---<profile attribute>#<profile attribute>.

FA Format for importing securities

Note

Certain fields are only available for certain types of securities - consider what fields are available for your chosen security type.

FA Format for importing security prices

 # Code Required Name Description 1 pr.updateCode Yes Security update code Code used to identify the security in the system.Securities can be identified with update codes, code, ISIN code or trade code (see below for more details). 2 pr.marketDate Yes Market date yyyy-MM-dd 3 pr.close No Close price Close price. When you define a close price, the system will determine which "Close" field the price will be store in (see below for more details).You can instead use a specific "Close" field to import a price directly to that field.For bonds, unit price can be set with % (e.g. 3.45%), when the system calculates the price according to YTM automatically. 4 pr.currency No Currency Currency of the security (optional, used to identify the security if there are two securities with the same ISIN-code but different currency). 5 pr.accrual No Accrual Price to be stored in "Accrual". (Available from FA 3.7 onward) 6 pr.delta No Delta Price to be stored in "Delta". (Available from FA 3.7 onward) 7 pr.closeMan No Manual Price to be stored as "Manual". (Available from FA 3.7 onward) 8 pr.close1 No Close 1 Price to be stored as "Close 1". (Available from FA 3.7 onward) 9 pr.close2 No Close 2 Price to be stored as "Close 2". (Available from FA 3.7 onward) 10 pr.close3 No Close 3 Price to be stored as "Close 3". (Available from FA 3.7 onward) 11 pr.close4 No Close 4 Price to be stored as "Close 4". (Available from FA 3.7 onward) 12 pr.close5 No Close 5 Price to be stored as "Close 5". (Available from FA 3.7 onward)

Which "update code" is used to identify securities (pr.mode)

When importing security prices through the update menu, you can select "Load by format" - this selection determines what security information the code in first column in your import file is mapped against to find a matching security.

• Default (code LOAD_BY_DEFAULT) – identify the security primarily by using "Update code" (1, 2 or 3). If no matching security is found, then use the logic in "update by ISIN" (using "ISIN code" + currency if given), or if no unique security is still found, then use the login in "update by code" (using "Code").

• Update by code (code LOAD_BY_CODE) - identify the security primarily by using "ISIN code", or if no unique security is found, then by using "Code".

• Update by ISIN (code LOAD_BY_ISIN) - identify the security by using "ISIN code" (and #4 Currency if defined in the import file - currency is used to identify the security if there are two securities with the same ISIN-code but different currency).

When importing security prices programmatically, you can use code pr.mode with the above LOAD_BY codes to determine which logic to use to identify your securities with the code you provide in pr.updateCode (available from FA 3.7 onward).

Which "close" the close prices are imported to (pr.n)

When importing security prices through the update menu, first row of the market price file can define (code pr.n), which "close" the prices are imported to. Security can have manual and up to five different close prices, and you can import prices to "manual" and any of the "closes". In the first row of the file, a numeric value 0 indicates the market price entries within the file are imported to the "manual" field (available from FA 2.4 onward), and a numeric value 1 through 5 indicates which "close" the market price entries within the file are imported to. If no numeric value is defined, the prices are imported to "Close 1". When using the update codes to import your prices (selection "Default" in the Import window), then the numeric value on the first row of the file also indicates which "Update code" is used to identify the securities: a numeric value 1 through 3 uses the corresponding "Update code" to identify the security, and imports the prices to the corresponding close.

When importing security prices through auto import in the update menu, a numeric value other than number 1 through 5 in the first row indicates that prices are updated with security's ISIN code and are saved to Close 1 (only available in Auto import).

When importing security prices programmatically, you can use codes pr.n (for the entire import) or or.updateColumn (for each row, available from FA 3.7 onward) with a numeric value 0 (manual), 1, 2, 3, 4 or 5 to indicate which "close" field you want to store pr.close into.