File formats for importing contacts and portfolios
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 through Importing preferences.
Download the CSV file templates:
The file columns are described below.
Updating contacts and portfolios
You can use CSV files to update existing contacts and portfolios. Specify the Contact ID or Portfolio ID in the first column, and put the values that you want to update in the other columns. The syntax to use for the column content depends on the field:
For single-value fields, such as
c.statusorp.status, 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 ones 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.
To remove existing values, import the CSV file with an empty field.
For other multi-value fields, see the field descriptions in the table.
Removing contact and portfolio 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 removing tag and profile values (tags and profileAttributes fields). To remove a value, specify the Contact ID or Portfolio ID in the first column and add the values 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: '---contact.profiling.totalAssets.
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 ---contact.profiling.totalAssets#---contact.profiling.activity.
You can remove certain values and add other ones, for example: --<tag name 1>,<tag name 2> or ---contact.profiling.totalAssets#contact.profiling.activity.
FA Format for importing contacts
# | Code | Required | Name | Description |
|---|---|---|---|---|
1 | c.contactId | Yes | Contact ID | Unique ID for the contact (e.g. social security number or other unique identifier). |
2 | c.externalId | No | External contact ID | E.g. social security number or other additional identification. |
3 | c.name | Yes | Name | Contact name (e.g. first name and last name, company name, etc.). |
4 | c.type | Yes | Role(s) | Define as a number: 1 = Customer 2 = Asset manager 3 = Broker 4 = Accountant 5 = Issuer 6 = Custody 7 = Lead |
5 | c.juridical | Yes | Juridical form | Juridical form code (configured in the system) |
6 | c.status | Yes | Status | Use the code of the status: A = Active P = Passive C = Closed |
7 | c.taxCountry | Yes | Tax country | Country code (configured in the system) |
8 | c.memo | No | Memo | Text, memo information |
9 | c.classification | Yes | Classification | Classification code (configured in the system) |
10 | c.identity | Yes | Identity | Identity code (configured in the system) |
11 | c.address1 | No | Address 1 | |
12 | c.address2 | No | Address 2 | |
13 | c.zipCode | No | Zip code | |
14 | c.city | No | City | |
15 | c.country | No | Country | |
16 | c.phone1 | No | Phone 1 | |
17 | c.phone2 | No | Phone 2 | |
18 | c.fax | No | Fax | |
19 | c.email | No | ||
20 | c.electronicCommunication | No | Electronic communication | Define as a number: 1 = allow electronic communication 0 = not allowed |
21 | c.marketingCommunication | No | Marketing communication | Define as a number: 1 = allow marketing communication 0 = not allowed |
22 | c.nationality | No | Nationality | Country code (configured in the system) |
23 | c.representatives | No | Representatives | Comma-separated list of contact IDs that represent the contact (e.g. 1223,1224,1225) You can use *** syntax to add new representatives and keep the existing ones. For example: ***,1223,1224. For details, see Updating contacts and portfolios. To add representatives to the contact that doesn't have any representatives, define "???," in the beginning of the column's content (e.g. ???,1223,1224). If the contact already had representatives, the existing representatives will be kept and the new ones won't be added. |
24 | c.language | No | Language | Language code (configured in the system) |
25 | c.subType | No | Sub type | Contact sub type code (configured in the system). When a sub type is given, it will find its own parent contact type and override the given contact type's code (in column 4). |
26 | c.transactionExtIdLevel | No | Transaction no. level | Define as a number: 0 = No level constraint 1 = Portfolio level constraint 2 = Customer level constraint |
27 | c.contactExchanges | No | Settlement place | Comma-separated list of contact settlement places with settlement place code (configured in the system) and counter information with BIC code. Use the following syntax to import settlement place information: <settlement place code>=<counter>. E.g. APKEFIHH=ESSEFIHX,MGTCBEBE=ESSEFIHX |
28 | c.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 contact and portfolio values with three minuses (---). |
29 | c.classification2 | No | Classification 2 | Classification 2 code (configured in the system). |
30 | c.classification3 | No | Classification 3 | Classification 3 code (configured in the system). |
31 | c.profileAttributes | No | Profile | Contact-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 or removed unless you specifically include their key within the import syntax. You can use three minuses (---) syntax to remove a profile attribute value. For example: ---<key>. For details, see Removing contact and portfolio values with three minuses (---). |
32 | c.extContactIdList | No | External IDs | List of contact's external ID's. Use the following syntax <ext_id1=ext_name1>,<ext_id2=ext_name2> etc. If ext_id and ext_name combination for the same contact exist just use the existing one, otherwise create new one. If ext_id and ext_name combination exist but for a different contact, will show error message. |
33 | c.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 and keep the existing ones. For example: ***:BBB=2007-01-01=1,2010-01-01=3. For details, see Updating contacts and portfolios. |
FA Format for importing portfolios
# | Code | Required | Name | Description |
|---|---|---|---|---|
1 | p.shortName | Yes | Portfolio ID | Text or number uniquely identifying a portfolio. When creating a new portfolio, define AUTO to automatically use the next available number as the portfolio ID, if the "Generate a running number for portfolio ID automatically" is enabled in Portfolios Preferences. |
2 | p.name | Yes | Name | Portfolio name |
3 | p.creationDate | No | Creation date | yyyy-MM-dd. Date when the portfolio was created. |
4 | p.extId | No | External ID | External contract ID such as book-entry account number |
5 | p.status | Yes | Status | Use the code of the status: A = Active P = Passive C = Closed |
6 | p.type | Yes | Type | Type code (configured in the system) |
7 | Match type | Not used. | ||
8 | p.currency | Yes | Reporting currency | Reporting currency code. |
9 | p.description | No | Description | Open text field |
10 | p.info1 | No | Info 1 | Open text field |
11 | p.info2 | No | Info 2 | Open text field |
12 | p.info3 | No | Info 3 | Open text field |
13 | p.notes | No | Notes | Text, notes or memo on the portfolio. |
14 | p.valuationMethod | No | Valuation method | Use the code of the valuation methods:
|
15 | p.language | Yes | Language | Language code (configured in the system) |
16 | p.primaryContact | No | Primary customer | Primary customer (use contact ID) |
17 | p.contact | No | Other customers | Comma-separated list of contact IDs |
18 | Default account | Not used. | ||
19 | p.accounts | No | Accounts | Colon-separated (:) list of portfolio's accounts. Use the following syntax to import account information: <Account no.>,<Currency with currency code>,<Name>,<Visibility as 0=not visible 1=visible>,<Shared as 0=not shared 1=shared>,<Priority as integer>;<BIC code>,<Mandate>,<Secondary account number>,<Category>,<Cash account as 0=not cash, 1=cash>,<Hide if zero as 0=don’t hide, 1=hide>,<Tags separated with #>,<Issuer with contact ID>,<Calendar with the calendar code>,<Fixing as a number>,<Base instrument with the security code>,<Spread as a number>,<Exclude balance as 0=don't exclude, 1=exclude>,<Shared with all as 0=not shared, 1=shared>, <Shared to all parent portfolio's subportfolios as 0=not shared, 1=shared>, <Decimals for transactions as a number>. For example, a list of two accounts: FI12 3456 78,EUR,Euro account,1,0,1,NDEAFIHHXXX,12345678,12345-678,Bank account,1,0,Client account#Status OK:234-321,USD,Dollar account,0,1,2,,,,,1,0,Client account#Status OK,,US30360,0.2,LIBOR,1.5,0,0,0,4 The account currency (second in the syntax) is validated to be a valid currency in the system. Supported calendar codes: 1/1 30/360ISDA 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 You can use *** syntax to add new accounts to the contact and keep the existing ones. For example: ***:<Account>:<Account>). For details, see Updating contacts and portfolios. To update the account number of an existing account, use the syntax "[old account no]new account no" in the first field of the account syntax. For example, if you want to update account number from 1000 to 1010, define the account number in the syntax as [1000]1010. If you try to update the account number of an account that does not exists in the portfolio, importing creates a new account. |
20 | p.assetManagers | No | Asset managers | Comma-separated list of contact IDs managing the portfolio |
21 | p.fees | No | Fees | List of portfolio fees: <type>=<fee percent>. Types are: transaction, payment, withdrawal, management. E.g. transaction=2.3, management=1.5 You can use *** syntax to add fees to the portfolio and keep the existing ones. For example: ***,payment=3,withdrawal=3.9. For details, see Updating contacts and portfolios. |
22 | p.startDate | No | Start date | yyyy-MM-dd, portfolio start date |
23 | p.updateDate | No | Update date | yyyy-MM-dd, portfolio update |
24 | p.ruleSet | No | Posting rule | Code of the posting rule set. For multiple posting rule sets, use comma-separated list of posting rule set codes (available from FA 3.12 onward). |
25 | p.extPortfolioIdList | No | External IDs | List of portfolio's external ID's. Use the following syntax <ext_id1=ext_name1>,<ext_id2=ext_name2> etc. If ext_id and ext_name combination for the same portfolio exist just use the existing one, otherwise create new one. If ext_id and ext_name combination exist but for a different portfolio, will show error message. |
26 | p.benchmark | No | Benchmark | Benchmark code (configured in the system) or in order to use portfolio specific benchmarks, use the following syntax <yyyy-MM-dd>,<security1 code>=<share1>,<security code2>=<share2>:<yyyy-MM-dd>,<security code3>=<share3> etc. You can use *** syntax to add new entries to the existing benchmark and keep the existing ones. For details, see Updating contacts and portfolios. |
27 | p.country | No | Country | Country code (configured in the system), if not given will use primary contact's tax country |
28 | p.juridical | No | Juridical | Juridical form code (configured in the system), if not given will use primary contact's juridical form |
29 | p.emailAddresses | No | Email addresses | Comma-separated list of email addresses. E.g. a@fasolutions.com, b@fasolutions.com |
30 | p.reportContacts | No | Report contacts | Comma-separated list of contact IDs |
31 | p.closeMethod | No | Close method | Define as a number: 0 = "Choose" 1 = "No change" 2 = "Depreciation" 3 = "Depreciation and refund" 4 = "Market value" |
32 | p.portfolioGroups | No | Group | Comma-separated list of groups (codes) that the portfolio belongs to. Groups and their codes are configured in the system. You can use *** syntax to add new groups to the portfolio and keep the existing ones. For example: ***,<group code>. For details, see Updating contacts and portfolios. |
33 | p.custody | No | Custody | Contact for the custody (use the Contact ID to identify the custody). |
34 | p.bookEntry | No | Book entry | String value for book entry account |
35 | p.modelPortfolio | No | Model portfolio | Name of the model portfolio (configured in the system) |
36 | p.costFormulas | No | Cost formula | Comma-separated list of cost formula codes (configured in the system) |
37 | p.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 contact and portfolio values with three minuses (---). |
38 | p.priceSource | No | Price source | If price source is not null, it will override the Security's "source" effect to fetch the close price of security, only when security source is 'DEFAULT'. Otherwise system would use security source. Default it's value is null. If you want to override that, use price source codes: DEFAULT, MANUAL, CLOSE1, CLOSE2, CLOSE3. |
39 | p.investmentPlan | No | Investment plan | Portfolio-specific investment plan, use the following syntax <yyyy-MM-dd>,<security 1 code>=<share 1>,<security 2 code>=<share 2>:<yyyy-MM-dd>,<security 3 code>=<share 3> etc. If you want to import an investment plan with minimum and maximum shares, use the following syntax <yyyy-MM-dd>,<securityCode1>=<share1#minShare1#maxShare1>,<securityCode2>=<share2#minShare2#maxShare2>;<yyyy-MM-dd>;,<securityCode3>=<share3#minShare3#maxShare3> etc. Min and max shares are validated to align in relation to the target share (i.e. min smaller / max higher or equal to the target). (Available from FA 3.8 onward) You can use *** syntax to add new entries to the investment plan and keep the existing ones. For example: ***:2019-01-01,NOKIA=25#20#30,AMAZON=15#10#20. For details, see Updating contacts and portfolios. |
40 | p.profileAttributes | No | Profile | Portfolio-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. To remove an attribute value, use the syntax described in Updating contacts and portfolios. For example: :---<key>. You can use three minuses (---) syntax to remove an attribute value. For example: ---<key>. For details, see Removing contact and portfolio values with three minuses (---). |
41 | p.strategy | No | Strategy | Portfolio specific strategy, use the following syntax <yyyy-MM-dd>,<assetTypeCode1#assetGroupCode1>=<share1#minShare1#maxShare1>,<assetTypeCode2#assetGroupCode2>=<share2#minShare2#maxShare2>:<yyyy-MM-dd>,<assetTypeCode3#asset & GroupCode3>=<share3#minShare3#maxShare3> etc. You can use *** syntax to add new entries to the strategy and keep the existing ones, for example: ***:2019-01-01,ASSET#MOD=100#0#100. For details, see Updating contacts and portfolios. |
42 | p.parent | No | Parent portfolio | Comma-separated list of parent portfolios' Portfolio IDs. The imported portfolio will be marked as the sub-portfolio to all the defined parent / main portfolios. You can use *** syntax to add new parent portfolios and keep the existing ones. For details, see Updating contacts and portfolios. |
43 | p.accruedInterestValuation | No | Accrued interest | Define as a number: 0 = "Transaction date" 1 = "Settlement date" If no value is filled in, the default value 0 "Transaction date" is used. (Available from FA 3.1 onward) |
44 | p.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>,... For example, AAA=2009-01-01=1.9,2010-01-01=1.10:TAX=2007-01-01=1,2010-01-01=2 You can use *** syntax to add new observations to the list and keep the existing ones. For details, see Updating contacts and portfolios. (Available from FA 3.4 onward) |
45 | p.aggregatingSubportfolios | No | Aggregation of subportfolios | Define as a number:
|
46 | p.locked | No | Locked | Define as a number:
|