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.
Download the CSV file templates:
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
# | 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 (from FA 3.1 onward): P1D = Daily P1W = Weekly P2W = Every two weeks P4W = Every four weeks P13W = Every 13 weeks P26W = Every 26 weeks P52W = Every 52 weeks P1M = Monthly P2M = Every two months P3M = Every three months P4M = Every four months P6M = Every six months P12M = Annually TERM = Once |
24 | s.accrualCalendar | No | Accrual calendar | Define with a calendar code: 1/1 30/360 ISDA 30/360 PSA 30E+/360 30E/360 30E/360 ISDA 30U/360 30U/360 EOM Act/360 Act/364 Act/365 Actual Act/365.25 Act/365F Act/365L Act/366 Act/366.375 Act/Act AFB Act/Act ICMA Act/Act ISDA Act/Act Year NL/365 |
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 From FA 2.3 onward, importing a coupon with an existing date will update 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: ANNUALLY SEMI_ANNUALLY QUARTERLY MONTHLY |
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 From FA 2.3 onward, importing a redemption with an existing date will update 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: ACTIVE PASSIVE CLOSED |
44 | s.multiplierView | No | Multiplier 2 | Price multiplier to use when showing the security price. E.g. bond prices are quoted as a percentage (i.e. 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: 0 = Direct 1 = Invert 2 = In its own currency 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 | Comma-separated list of tags. Tags import allows you to add new tags:
You can use three minuses (---) syntax to remove a tag:
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. 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 (available from FA 2.3 onward). |
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 (available from FA 2.3 onward). |
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 process (available from FA 2.3 onward). |
62 | s.callDate | No | Call date | yyyy-MM-dd (available from FA 2.3 onward). |
63 | s.issuePrice | No | Issue price | Security issue price as a number (available from FA 2.3 onward). |
64 | s.referenceIndexConvention | No | Reference index convention | Pre-defined Reference index convention (available from FA 2.6 onward). Define as a number: 1 = Canada, US, France, Germany, Italy, Denmark, Sweden, UK after July 2005 (2,2,3) 2 = South Africa (2,3,4) 3 = UK prior to July 2005 (1,3,NaN) 4 = Finland (1,8, NaN) 0 = Other 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, then you can use parameters in columns #65, #66, and #67. |
65 | s.referenceIndexCalculationMode | No | Reference index calculation mode | Define as a number: 1 = Direct 2 = Linear Only available if the Reference index convention in column #64 is set to 0. (Available from 2.6 onward). |
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. (Available from 2.6 onward). |
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. (Available from 2.6 onward). |
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>, <date 2>:<cost category code 1>=<cost percentage as a number>:<cost category code 2>=<cost percentage as a number>. E.g. (without spaces) 01.01.2018: exPostSecCostCat1=0.2: exAnteSecCostCat7=1.3, 01.01.2019: exPostSecCostCat3=0.3 Start defining the costs with a date followed by a semicolon (:), and add the costs you want as cost category and cost percentage pairs (<cost category code> = <percentage>), separated with a semicolon (:). Separate costs for different dates with a comma. You can use *** syntax to add new observations to the list and keep the existing ones. For details, see Updating securities (available from FA 3.12 onwards). Available cost category codes: Ex-ante costs: exAnteSecCostCat1 = One off charges (%) exAnteSecCostCat2 = On-going charges (%) exAnteSecCostCat3 = Transaction charges (%) exAnteSecCostCat4 = Ancillary service charges (%) exAnteSecCostCat5 = Incidental charges (%) exAnteSecCostCat6 = Other costs 1 (%) exAnteSecCostCat7 = Other costs 2 (%) exAnteSecCostCat8 = Other costs 3 (%) exAnteSecCostCat9 = Other costs 4 (%) exAnteSecCostCat10 = Other costs 5 (%) Ex-post costs: exPostSecCostCat1 = One off charges (%) exPostSecCostCat2 = On-going charges (%) exPostSecCostCat3 = Transaction charges (%) exPostSecCostCat4 = Ancillary service charges (%) exPostSecCostCat5 = Incidental charges (%) exPostSecCostCat6 = Other costs 1 (%) exPostSecCostCat7 = Other costs 2 (%) exPostSecCostCat8 = Other costs 3 (%) exPostSecCostCat9 = Other costs 4 (%) exPostSecCostCat10 = Other costs 5 (%) |
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. Built-in holiday calendars' codes: NoHolidays = No holidays/weekends (NoHolidays) Sat/Sun = Saturday/Sunday weekends (SatSun) Fri/Sat = Friday/Saturday weekends (FriSat) Thu/Fri = Thursday/Friday weekends (ThuFri) GBLO = London, United Kingdom (GBLO) FRPA = Paris, France (FRPA)DEFR = Frankfurt, Germany (DEFR) CHZU = Zurich, Switzerland (CHZU) EUTA = European Union TARGET system (EUTA) FIHE = Helsinki, Finland (FIHE) USGS = United States Government Securities (USGS) USNY = New York, United States (USNY) NYFD = Federal Reserve Bank of New York (NYFD) NYSE = New York Stock Exchange (NYSE) JPTO = Tokyo, Japan (JPTO) AUSY = Sydney, Australia (AUSY) BRBD = Brazil (BRBD) CAMO = Montreal, Canada (CAMO) CATO = Toronto, Canada (CATO) CZPR = Prague, Czech Republic (CZPR) DKCO = Copenhagen, Denmark (DKCO) HUBU = Budapest, Hungary (HUBU) MXMC = Mexico City, Mexico (MXMC) NOOS = Oslo, Norway (NOOS) NZAU = Auckland, New Zealand (NZAU) NZWE = Wellington, New Zealand (NZWE) PLWA = Warsaw, Poland (PLWA) SEST = Stockholm, Sweden (SEST) ZAJO = Johannesburg, South Africa (ZAJO) Or use the code of your custom holiday calendar defined in preferences. |
71 | s.settlementDateOffset | No | Settlement date offset | Define the settlement date offset as a number of days. |
72 | s.enableSettlementDateOffset | No | Enable settlement date offset | Define as a number: 1 = Enabled 0 = Not enabled If no value is filled in, the default value 0 "Not enabled" is used. |
73 | s.stubConvention | No | Stub convention | Define with a code: None = None ShortInitial = Short initial LongInitial = Long initial SmartInitial = Smart initial ShortFinal = Short final LongFinal = Long final SmartFinal = Smart final Both = Both |
74 | s.businessDayConvention | No | Business day convention | Define with a code: ModifiedFollowing = Modified following (without crossing month end) Nearest = Nearest ModifiedPreceding = Modified preceding (without crossing month start) Following = Following ModifiedFollowingBiMonthly = Modified following (without crossing mid-month or month end) NoAdjust = No adjustment Preceding = Preceding |
75 | s.exCouponPeriod | No | Ex-coupon period | Define the ex-coupon period as a number of days (available from FA 3.1 onward). |
76 | s.yieldConvention | No | Yield convention | Define with a code: GB-Bump-DMO = UK BUMP/DMO method US-Street = US street DE-Bonds = German bonds JP-Simple = Japan simple yield |
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: 1D, 2D, 3D 1W, 2W, 3W, 4W, 6W, 13W, 26W, 52W 1M, 2M, 3M, 4M, 5M, 6M, 7M, 8M, 9M, 10M, 11M, 15M, 18M, 21M 1Y, 2Y, 3Y, 4Y, 5Y, 6Y, 7Y, 8Y, 9Y, 10Y, 11Y, 12Y, 13Y, 14Y, 15Y, 20Y, 25Y, 30Y, 35Y, 40Y, 45Y, 50Y |
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: DAY_1 = 1st day of the month DAY_2 = 2nd day of the month DAY_3 = 3rd day of the month, and so on. DAY_MON = Monday DAY_TUE = Tuesday DAY_WED = Wednesday DAY_THU = Thursday DAY_FRI = Friday DAY_SAT = Saturday DAY_SUN = Sunday EOM = End of month (EOM) IMMNZD = First Wednesday on or after the ninth day of the month (IMMNZD) TBILL = Next Monday (TBILL) SFE = Second Friday (SFE) IMM = Third Wednesday (IMM) IMMAUD = Thursday before the second Friday (IMMAUD) IMMCAD = Two days before the third Wednesday (IMMCAD) NONE = None |
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. |
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).
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.