Skip to main content

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:

FAFormat_portfolios.csv

FAFormat_contacts.csv

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: 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

Email

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 contact representative tag values, add them after ###:

  • Add standalone tags as <representative's contact ID>###<tag name>. For example: 04012000##parent.

  • Add single-select group tags as <representative's contact ID>###<group name>-<tag name>. For example: 04012000##relative-parent.

  • Add multi-select group tags as a comma-separated list of values: <representative's contact ID>###<group name>:<tag name 1>,<group name>:<tag name 2>. For example: 04012000###Employee:Finance,Employee:Accounting.

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 (for example, ???,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:

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:

  • Standalone tags are added with a <tag name>. For example: Fund,ContractOK.

  • Single-select group tags should be added as <group name>-<tag name>. For example: office-Helsinki. If a contact already has a tag with the same group name, the tag is replaced.

  • Multi-select group tags should be added as a comma-separated list of values <group name>:<tag name 1>,<group name>:<tag name 2>. For example: Interests:Golf,Interests:Culture. If a contact already has tags with the same group name, the new tag is added to the existing ones.

You can use three minuses (---) syntax to remove a tag:

  • Standalone tags: ---<tag name>. For example: ---Fund,---ContractOK.

  • Single-select group tags: ---<group name>-<tag name>. For example: —office-Helsinki.

  • Multi-select group tags: ---<group name>:<tag name 1>,---<group name>:<tag name 2>. For example: ---Interests:Golf,---Interests:Culture.

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:

  • FIFO (default)

  • FIFOP = FIFO (combined)

  • AVER = Average price

  • AVER2 = Average price with adjustments

  • AVER3 = Average price with separate items.

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 ###:

  • Standalone tags are added as <contact ID>###<tag name>. For example: 04012000##parent.

  • Single-select group tags are added as <contact ID>###<group name>-<tag name>. For example: 04012000##relative-parent.

  • Multi-select group tags are added as a comma-separated list of values: <contact ID>###<group name>:<tag name 1>,<group name>:<tag name 2>. For example: 04012000###Employee:Finance,Employee:Accounting.

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 ###.

  • Standalone tags are added as <contact ID>###<tag name>. For example: 04012000##parent.

  • Single-select group tags are added as <contact ID>###<group name>-<tag name>. For example: 04012000##relative-parent.

  • Multi-select group tags are added as a comma-separated list of values: <contact ID>###<group name>:<tag name 1>,<group name>:<tag name 2>. For example: 04012000###Employee:Finance,Employee:Accounting.

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:

  • 1/1

  • 30/360ISDA

  • 30/360 PSA

  • 30E+/360

  • 30E/360

  • 30E/360 ISDA

  • 30U/360

  • 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 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 ###.

  • Standalone tags are added as <contact ID>###<tag name>. For example: 04012000##parent.

  • Single-select group tags are added as <contact ID>###<group name>-<tag name>. For example: 04012000##relative-parent.

  • Multi-select group tags are added as a comma-separated list of values: <contact ID>###<group name>:<tag name 1>,<group name>:<tag name 2>. For example: 04012000###Employee:Finance,Employee:Accounting.

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

"The "Tags" field is a comma-separated list of tags that you can use to categorize your portfolios.

To add a tag: 

  • Standalone tags can be added by simply typing the tag name. For example, if you want to add a tag for a Fund and a tag for a ContractOK, you can add them like this: "Fund,ContractOK".

  • Single-select group tags should be added in the format <group name>-<tag name>. For example, if you want to add a tag for an office location in Helsinki, you can add it like this: "office-Helsinki". If a portfolio already has a tag with the same group name, the new tag will replace the old one.

  • Multi-select group tags should be added in the format <group name>:<tag name 1>,<group name>:<tag name 2>, etc. For example, if you want to add tags for stocks and bonds in your Investments group, you can add them like this: "Investments:Stocks,Investments:Bonds". If a portfolio already has tags with the same group name, the new tags will be added to the existing ones.

To remove a tag, use the syntax "---" followed by the tag name or tag group name. For example: 

  • To remove a standalone tag for "Fund", you can use "---Fund".

  • To remove a single-select group tag for "office-Helsinki", you can use "---office-Helsinki".

  • To remove multi-select group tags for "Investments:Stocks" and "Investments:Bonds", you can use "---Investments:Stocks,---Investments:Bonds".

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:

  • 0 = Sum up values

  • 1 = Recalculate values

46

p.locked

No

Locked

Define as a number:

  • 0 = Not locked (default)

  • 1 = Locked

47

p.postingsOnParent

No

Generate postings on parent level

Define as a number:

  • 0 = Generate postings on portfolio level

  • 1 = Generate postings on parent portfolio level