File formats for importing contacts and portfolios
Contact and portfolios are imported in FA in semicolon-separated CSV files, with the file columns described below. You can import multiple entries (contacts or portfolios) at the same time with one file: each row of the CSV file is read as a separate object. However, you cannot import contacts and portfolios 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 file columns are described below.
Updating contacts and portfolios
You can update existing contacts and portfolios by importing a new file with corrected data.
The system identifies the contact to update by the Contact ID, and the portfolio to update by the Portfolio ID, so these fields must be filled in.
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 that 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 using three stars (***), for example, ***<new value1>,<new value2>.
Some fields have specific syntax for updates, see the field descriptions in the tables.
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 | Type | 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 representing the contact, for example, 1223,1224,1225. To import tag values for a contact representative, add them after ###:
You can use *** syntax to add new representatives and keep existing ones (for example: ***,04012000###...). For details, see Updating contacts and portfolios. To add representatives to a contact without any existing representatives, add "???," at the beginning of the column's content (for example: ???,04012000####...). If the contact already has representatives, existing representatives will be kept and the new ones are not 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: 1 = Portfolio level 2 = Contact level |
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. 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 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. |
34 | c.parent | No | Issuer group | Contact ID of the issuer group. Available only for contacts of the Issuer type. |
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 contact | Primary contact (contact ID). To import representative tag values, add them after ###:
|
17 | p.contact | No | Other contacts | Portfolio's other contacts. Contact IDs as a comma-separated list. To import representative tag values, add them after ###:
|
18 | Default account | Not used. | ||
19 | p.accounts | No | Accounts | A colon-separated (:) list of the portfolio accounts. To add accounts to a portfolio, use the following syntax: <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>,<Account type as 0=not cash, 1=cash, 2=credit>,<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>, <Transaction type code>, <Offset days as an integer>. 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,,Act/360,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:
You can use *** syntax to add new accounts for the portfolio while keeping the old ones, but this is not required. Without ***, old values are still preserved. 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 an account number from 1000 to 1010, define the account number in the syntax as [1000]1010. To keep the accounts unchanged, import an empty value in the field. Existing accounts cannot be removed using import. |
20 | p.assetManagers | No | Asset managers | Portfolio's asset managers. Contact IDs as a comma-separated list. To import representative tag values, add them after ###.
|
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 | Portfolio groups | Portfolio groups the portfolio is in. Specify the portfolio groups as a comma-separated list of group codes. You can use *** to add new groups to the portfolio while keeping 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 | "The "Tags" field is a comma-separated list of tags that you can use to categorize your portfolios. 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 contact and portfolio values with three minuses (---). |
38 | p.priceSource | No | Price source | The method to determine the close market price for the securities in the portfolio. To use the prices from Security's Market data info tab, leave the field empty. To use the first available price from the price fields in the Security's Market data info tab (checking the column values in the order specified above), specify Default. To define 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 contacts and portfolios. You can use three minuses (for example, ---Close 1) to remove a value. |
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. 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. 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#FIXED>,<assetTypeCode2#assetGroupCode2>=<share2#minShare2#maxShare2>:<yyyy-MM-dd>,<assetTypeCode3#asset & GroupCode3>=<share3#minShare3#maxShare3> etc. For interpolation, specify "INTERPOLATION", calendar code, and two dates before the asset types. For example: INTERPOLATION=SatSun:<yyyy-MM-dd>,<assetTypeCode1#assetGroupCode1>=<share1#minShare1#maxShare1>,<assetTypeCode2#assetGroupCode2>=<share2#minShare2#maxShare2>, etc. For an asset type that is excluded from strategy analysis, specify "EXCLUDED" followed by three dots: <yyyy-MM-dd>,<assetTypeCode1#assetGroupCode1>=EXCLUDED... 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:
|
47 | p.postingsOnParent | No | Generate postings on parent level | Define as a number:
|
48 | p.securityGroups | No | Security groups | Security groups the portfolio is linked to. Specify the security groups as a comma-separated list of group codes. You can use *** to add new groups to the security while keeping the existing ones. For example: ***, <group code>. For details, see Updating contacts and portfolios. |
49 | p.twrDate | No | Import TWR date | yyyy-MM-dd. The date until which Analytics+ should use precalculated TWR values. |