File formats for importing securities and security prices
Securities and security prices are imported in FA in semicolon-separated CSV files, with the file columns described below. You can import multiple entries (securities or security prices) at the same time with one file: each row of the CSV file is read as a separate object. However, you cannot import for example securities and prices to the system with the same file.
You can define the following properties of the CSV file in Preferences → Importing → File format (see Preference - Importing for details):
File delimiter. By default, FA is configured to accept semicolon-separated values. You can change it to another character.
Encoding: UTF-8 or Windows-1252. To import Scandinavian letters, the files should be Windows-1252 encoded in order to guarantee the appropriate handling of Scandinavian letters.
Download the CSV file templates:
The columns of the file format are described below.
Updating securities
You can update existing securities or market price entries by importing a new file with corrected data.
The system identifies the security to update by the Security code. The market price entry to update is identified by the Security update code and the Market date, so you must fill in these fields. Additionally, you must fill in at least one of the following fields: Close, Accrual, Delta, Manual, or Close1 - Close5.
Note that you must specify if you want to keep the old values in each field:
To replace the field value, specify the new value in the import file.
To keep the existing field value saved in the system, add three stars *** in the field. Note: if you import an empty field without ***, the field value previously saved in the system is removed. For multi-value fields, you can import new values while keeping the old ones by adding three stars and a comma before the values (***,). Example: ***,<new value1>,<new value2>.
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.
# | Code | Required | Name | Description |
1 | s.securityCode | Yes | Security code | Code used to identify the security in the system. |
2 | s.tradeCode | No | Trade code | Trading code |
3 | s.updateCode1 | No | Update code 1 | Update code 1 (e.g. Bloomberg code, Reuters code, SIX code, etc.) |
4 | s.updateCode2 | No | Update code 2 | Update code 2 (e.g. Bloomberg code, Reuters code, SIX code, etc.) |
5 | s.updateCode3 | No | Update code 3 | Update code 3 (e.g. Bloomberg code, Reuters code, SIX code, etc.) |
6 | s.isinCode | No | ISIN code | ISIN code |
7 | s.name | Yes | Name | Security name |
8 | s.market | No | Market | Not used, use "Market place" (no. 15) or "Settlement place" (no. 55) |
9 | s.type | Yes | Type | Security type, define with security type's code (configured in the system) |
10 | s.currency | Yes | Currency | Currency of the security, define with the currency code. Currency is a mandatory field for all other type of securities except for security type "Currency". |
11 | s.description | No | Description | Description for the security |
12 | s.url | No | URL | URL |
13 | s.url2 | No | URL 2 | Secondary URL |
14 | s.country | No | Country | Country code (configured in the system) |
15 | s.exchange | No | Market place | Market place code (configured in the system) |
16 | s.classType | No | Class 1 | Describes a classification 1 for the security with a code. Code/name values for classifications are defined in the system. |
17 | s.issuer | No | Issuer | Issuer of the security (use contact ID of the issuer saved in the system). If no contact is found, the issuer value is saved as text in the "Issuer description" field instead. |
18 | s.underlySecurity | No | Linked security | Define with the security code of the linked security. The linked security must exist in the system. Linked security is used e.g. with options and other derivatives to link the security to the underlying instrument. |
19 | s.blockSize | No | Block size | Number, describing, how many units is one trading block. |
20 | s.rating | No | Rating | Rating of the security such as AAA |
21 | s.maturityDate | No | Maturity date | yyyy-MM-dd. Maturity date of a security. |
22 | s.maturityPrice | No | Maturity price | Price in the end of the maturity as a number. In most cases 100, if applicable. |
23 | s.couponFrequency | No | Coupon frequency | Define with a code (before FA 3.1): ANNUALLYSEMI_ANNUALLYQUARTERLYMONTHLY Define with a code:
|
24 | s.accrualCalendar | No | Accrual calendar | Define with a calendar code:
|
25 | s.issueDate | No | Issue date | yyyy-MM-dd |
26 | s.firstCouponDate | No | First coupon date | yyyy-MM-dd |
27 | s.coupon | No | Coupon rate | Fixed coupon rate percentage. E.g. 5 => 5 % |
28 | s.coupons | No | Coupons | Paid coupons for floating rate notes. Format is <date yyyy-MM-dd>=<coupon>,<date yyyy-MM-dd>=<coupon>. E.g. 2009-01-01=5.2,2010-01-01=6.2 Importing a coupon with an existing date updates the coupon instead of creating a duplicate one. |
29 | s.multiplier | No | Multiplier | Price divider to use when calculating with the security price. If bond prices are quoted in the market data as a percentage e.g. 101.23, then multiplier should be 100. |
30 | s.redemptionFrequency | No | Redemption frequency | Define with a code:
|
31 | s.firstRedemptionDate | No | First redemption date | yyyy-MM-dd |
32 | s.redemptions | No | Redemptions | Redemptions. Format is <date yyyy-MM-dd>=<redemption percentage>,<date yyyy-MM-dd>=<redemption>. E.g. 2009-01-01=30,2010-01-01=70 Importing a redemption with an existing date updates the redemption instead of creating a duplicate one. |
33 | s.allocations | No | Allocations | Format is <asset group code>:<date yyyy-MM-dd>:<asset type code>=<share> (defined in the system configurations) and its share separated by comma. E.g. ASSET:2013-12-12:FINSTO=50, ASSET:2013-12-12:USSTO=50. Original format, asset class code (defined in the system configurations) and its share separated by comma, e.g. FINSTO=50, USSTO=50 will be parsed as ASSET:2009-01-01:FINSTO=50, ASSET:2009-01-01:USSTO=50. You can use *** syntax to add new allocations and keep the existing ones. For details, see Updating securities. |
34 | s.paymentFee | No | Payment fee | Payment fee in security currency |
35 | s.paymentFeePercentage | No | Payment fee % | Payment fee in percentage. 10 => 10 % |
36 | s.minPaymentFee | No | Min payment fee | Minimum payment fee |
37 | s.maxPaymentFee | No | Max payment fee | Maximum payment fee |
38 | s.managementFee | No | Management fee | Management fee in security currency |
39 | s.managementFeePercentage | No | Management fee % | Management fee percentage. 10 => 10 % |
40 | s.minManagementFee | No | Min management fee | Minimum management fee |
41 | s.maxManagementFee | No | Max management fee | Maximum management fee |
42 | s.startUpFee | No | Start up fee | Start up fee in security currency |
43 | s.status | Yes | Status | Define with a code:
|
44 | s.multiplierView | No | Multiplier 2 | Price multiplier to use when showing the security price. E.g. bond prices are quoted as a percentage (that is, multiplied by 100) and therefore, the multiplier for a bond should be 100. Used also when importing prices. |
45 | s.invert | No | Manipulation | Define as a number:
If no value is filled in, the default value 0 "Direct" is used. |
46 | s.subType | No | Sub type | Sub type code (configured in the system) |
47 | s.classType2 | No | Class 2 | Describes a classification 2 for the security with a code. Code/name values for classifications are defined in the system. |
48 | s.classType3 | No | Class 3 | Describes a classification 3 for the security with a code. Code/name values for classifications are defined in the system. |
49 | s.tags | No | Tags | The "Tags" field is a comma-separated list of tags that you can use to categorize your securities. To add a tag:
To remove a tag, use the syntax "---" followed by the tag name or tag group name. For example:
For details, see Removing security values with three minuses (---). |
50 | s.share | No | Outstanding units | The number of shares outstanding. |
51 | s.classType4 | No | Class 4 | Describes a classification 4 for the security with a code. Code/name values for classifications are defined in the system. |
52 | s.classType5 | No | Class 5 | Describes a classification 5 for the security with a code. Code/name values for classifications are defined in the system. |
53 | s.kickbackPercentage | No | Kickback % | Kickback fee percentage. 5 => 5 % |
54 | s.priceFromSecurity | No | Price from security | Security code of the security's price from another linked security. Must be an existing security in the system. |
55 | s.settlement | No | Settlement place | Settlement place code (configured in the system) |
56 | s.profileAttributes | No | Profile | Security-specific profile (configured in the system). Use the following syntax to import / update profile information: <key>=<value>:<type>#<key>=<value>:<type> etc. Note that importing checkbox fields requires you to use the type "boolean" and importing date fields requires you to use the type "date" in the syntax. Profile import allows you to add or modify profile values with a specific key - existing profile values are not replaced / removed unless you specifically include their key within the import syntax. You can use three minuses (---) syntax to remove an attribute value. For example: ---<key>. For details, see Removing security values with three minuses (---). |
57 | s.linkedIndex | No | Linked index | Linked index is used with e.g. index linked bonds. Security code of another security whose price is used linked security. Must be an existing security in the system . |
58 | s.indexBaseValue | No | Index base value | Base value used when calculating coefficient for the transaction or market price versus the linked index. |
59 | s.minTradeAmount | No | Minimum trade amount | Security minimum trade amount as a number. |
60 | s.baseInstrument | No | Base instrument | Security code of another security as a base instrument, can be used for example to calculate fixings with a separate process. Must be an existing security in the system. |
61 | s.spread | No | Spread | Security spread as a number, can be used for example to calculate fixings with the base instrument with a separate proces. |
62 | s.callDate | No | Call date | yyyy-MM-dd |
63 | s.issuePrice | No | Issue price | Security issue price as a number. |
64 | s.referenceIndexConvention | No | Reference index convention | Pre-defined Reference index convention. Define as a number:
If you define a reference index convention between 1 - 4, then calculation mode and lags are automatically set according to the selected contention. If 0, you can use parameters in columns #65, #66, and #67. |
65 | s.referenceIndexCalculationMode | No | Reference index calculation mode | Define as a number:
Only available if the Reference index convention in column #64 is set to 0. |
66 | s.referenceIndexlag1 | No | Reference index lag 1 | Define the lag in months as a number between 1 - 12. Only available if the Reference index convention in column #64 is set to 0. |
67 | s.referenceIndexlag2 | No | Reference index lag 1 | Define the lag in months as a number between 1 - 12. Only available if the Reference index convention in column #64 is set to 0. |
68 | s.securityExCosts | No | Ex-ante / Ex-post costs | Security-specific Ex-ante / Ex-post costs. Use the syntax <date 1>:<cost category code 1>=<cost percentage as a number>: <cost category code 2>=<cost percentage as a number>:calculationBase=<0 or 1 for the market value to use for the day (optional parameter), where 0 is ingoing market value and 1 is the same day market value>, <date 2>:<cost category code 1>=<cost percentage as a number>:<cost category code 2>=<cost percentage as a number>:calculationBase=<0 or 1 for the market value to use for the day (optional parameter), where 0 is ingoing market value and 1 is the same day market value>. For example: (without spaces) 01.01.2018: exPostSecCostCat1=0.2: exAnteSecCostCat7=1.3:calculationBase=0, 01.01.2019: exPostSecCostCat3=0.3:calculationBase=0 You can use *** syntax to add new observations to the list and keep the existing ones. For details, see Updating securities. Available cost category codes: Ex-ante costs:
Ex-post costs:
|
69 | s.baseCurrency | (Yes) | Base currency | Base currency of the currency cross security, define with the currency code. Base currency is only available for securities of the type "Currency cross" - for "Currency cross" type securities, base currency is a mandatory field! |
70 | s.holidayCalendar | No | Holiday calendar | Define with the holiday calendar code. Specify a built-in holiday calendar with a code:
Or use the code of your custom holiday calendar defined in preferences. If you import an empty field, the default holiday calendar specified in Preference - Securities is used. |
71 | s.buySettlement | No | Buy settlement | Number of business days required for buy trade orders to settle. Sell settlement is on row 95. |
72 | s.setSettlementPeriod | No | Set settlement period | Define as a number:
If no value is filled in, the default value 0 "Not enabled" is used. |
73 | s.stubConvention | No | Stub convention | Define with a code:
|
74 | s.businessDayConvention | No | Business day convention | Define with a code:
|
75 | s.exCouponPeriod | No | Ex-coupon period | Define the ex-coupon period as a number of days. |
76 | s.yieldConvention | No | Yield convention | Define with a code:
|
77 | s.macaulayDuration | No | Macaulay duration | Define as date-value pairs as a comma-separated list with the format <yyyy-MM-dd>=<value>,<yyyy-MM-dd>=<value>. E.g. 2019-01-01=7.1,2019-04-01=1.1 You can use *** syntax to add new observations to the list and keep the existing ones. For details, see Updating securities. |
78 | s.modifiedDuration | No | Modified duration | Define as date-value pairs as a comma-separated list with the format <yyyy-MM-dd>=<value>,<yyyy-MM-dd>=<value>. E.g. 2019-01-01=7.1,2019-04-01=1.1 You can use *** syntax to add new observations to the list and keep the existing ones. For details, see Updating securities. |
79 | s.convexity | No | Convexity | Define as date-value pairs as a comma-separated list with the format <yyyy-MM-dd>=<value>,<yyyy-MM-dd>=<value>. E.g. 2019-01-01=7.1,2019-04-01=1.1 You can use *** syntax to add new observations to the list and keep the existing ones. For details, see Updating securities. |
80 | s.offsetDays | No | Offset days | Define as a number. |
81 | s.tenor | No | Tenor | Define the tenor for your security with alternatives:
|
82 | s.ytm | No | YTM | Define as date-value pairs as a comma-separated list with the format <yyyy-MM-dd>=<value>,<yyyy-MM-dd>=<value>. E.g. 2019-01-01=7.1,2019-04-01=1.1 You can use *** syntax to add new observations to the list and keep the existing ones. For details, see Updating securities. |
83 | s.keyFigures | No | Key figures | Define the key figure code and individual observations as date-value pairs as a comma-separated list with the format <code>=<date:yyyy-MM-dd>=<value>,<date:yyyy-MM-dd>=<value>:<code>=<date:yyyy-MM-dd>=<value>,... E.g. AAA=2009-01-01=1.9,2010-01-01=1.10:TAX=2007-01-01=1,2010-01-01=2 When importing categorized key figures (e.g. 1=Low, 2=Medium, 3=High), prior to 3.7.7 you should use the "number" (e.g. 1, 2 or 3), and after 3.7.7 you should use the "text" (e.g. Low, Medium, High). You can use *** syntax to add new observations to the list and keep the existing ones. For details, see Updating securities. |
84 | s.rollConvention | No | Roll convention | Define with a code:
|
85 | s.linkedPortfolio | No | Linked portfolio | Define with the portfolio ID of the linked portfolio. The linked portfolio must exist in the system. Linked portfolio is used e.g. to link a fund portfolio to your fund security. (Available from FA 3.9 onward) |
86 | s.votesPerShare | No | Votes per share | The number of votes this security has per share. |
87 | s.source | No | Price source | The method to determine the close market price for the security. To use the first available price in the Manual, Close 1, 2, 3, 4 or 5 columns in the Security window, Market data info tab (checking the column values in the order specified above), specify Default. To determine a different priority of price source fields, enter a comma-separated list of columns in the order of priority. The values are: Manual, Close 1, Close 2, Close 3, Close 4, Close 5. You can use *** syntax to add new values in the list of price source fields and keep the existing ones. For details, see Updating securities. You can use three minuses (for example, ---Close 1) to remove a value. |
88 | s.collateralRatio | No | Collateral ratio | Collateral ratio is a percentage of a position's market value that can be used to calculate collateral value for portfolio credit. The field can be empty. Use the following format: YYYY-MM-DD=XX (where XX is the collateral ratio value). For example: 2023-12-10=45, 23-11-09=30 |
89 | s.paymentType | No | Payment type | Payment type for bonds. Define as a number:
|
90 | s.securityGroups | No | Security groups | Security groups the security is in. Specify the security groups as a comma-separated list of group codes. You can use *** to add new static groups to the security while keeping the existing ones. For example: ***, <group code>. For details, see Updating securities. |
91 | s.faceValue | No | Face value | Face value of a bond (integer). |
92 | s.repaymentPlan | No | Repayment plan | Define with a code:
|
93 | s.bookkeepingAccount | No | Bookkeeping account | Bookkeeping account number to use in postings (string). |
94 | s.capitalGainsTaxApplicable | No | CGT applicable | Define as a number:
|
95 | s.sellSettlement | No | Sell settlement | Number of business days required for sell trade orders to settle. Buy settlement is on row 71. |
96 | s.coefficientDecimals | No | Coefficient decimals | Define as an integer, for example, 5. |
97 | s.floorIndexBaseValue | No | Floor index at base value | Define as a number:
|
98 | s.redemptionPriceType | No | Redemption price type | Define as a number:
|
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 (deprecated) | Close price. When defined, the system determines in which "Close" field it stores the price. For bonds, unit price can be set with % (for example, 3.45%) and the system calculates the price according to YTM. This field is deprecated. You can use Close 1-5 to import a price directly into a specific field. |
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).
Update by trade code (code LOAD_BY_TRADE_CODE) - identify the security by using "Trade code".
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.