Skip to main content

File format for importing portfolios

Portfolios are imported into the FA system in semicolon-separated CSV files using the file columns described below. You can set the following properties of the CSV file in PreferenceImportingFile 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 to guarantee the correct handling of Scandinavian letters.

Download the CSV file template:

FAFormat_portfolios.csv

Update portfolio values

You can update existing portfolios by importing a new file with corrected data. The system identifies the contact to update by the portfolio ID, so you need to fill in this field. 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, the previously saved value is removed. For multi-value fields, you can import new values while keeping old ones using three stars (***). For example: ***<new value1>,<new value2>.

  • The fields can have specific syntax for updates, see the field descriptions in the table.

Remove portfolio values

You can remove existing values in fields by importing a new file with corrected data. The system identifies the portfolio to update by the portfolio ID, so you need to fill in this field.

  • For a single-value field, import an empty value in the field to remove the existing value.

  • For tags and profile attribute fields, see the details in the table (rows 37 and 40).

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

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

  • Add single-select group tags as <representative's contact ID>###<group name>-<tag name 1>#<group name>-<tag name 2>. For example: 258258###Permission-View#Relationship type-Advisor

  • Add multi-select group tags as <representative's contact ID>###<group name>:<tag name 1>#<group name>:<tag name 2>. For example: 04012000###Permission for trade orders:Make#Permission for trade orders:Modify

  • To import multiple types of tags simultaneously, separate the tags with #. For example: 04012000###Permission-View#Employee:Finance#Employee:Accounting#Relationship type-Advisor#Standalone1#Standalone2

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

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

  • Add single-select group tags as <representative's contact ID>###<group name>-<tag name 1>#<group name>-<tag name 2>. For example: 04012000###Permission-View#Relationship type-Advisor

  • Add multi-select group tags as <representative's contact ID>###<group name>:<tag name 1>#<group name>:<tag name 2>. For example: 04012000###Permission for trade orders:Make#Permission for trade orders:Modify

  • To import multiple types of tags simultaneously, separate the tags with #. For example: 04012000###Permission-View#Employee:Finance#Employee:Accounting#Relationship type-Advisor#Standalone1#Standalone2

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

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.

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.

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

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

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.

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

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.

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 individual value, use three minuses (---) in the field. If you edit the CSV file in MS Excel, you need to enter an apostrophe in the beginning of the column. For example: '---contact.profiling.totalAssets.

  • To remove an individual value, use three minuses (---) in the field. If you edit the CSV file in MS Excel, you need to enter an apostrophe in the beginning of the column. For example: '---contact.profiling.totalAssets.

  • To remove an individual value, use three minuses (---) in the field. If you edit the CSV file in MS Excel, you need to enter an apostrophe in the beginning of the column. For example: '---contact.profiling.totalAssets.

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.

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.

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.

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.

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

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 portfolio while keeping the existing ones. For example: ***, <group code>.

49

p.twrDate

No

Import TWR date

yyyy-MM-dd. The date until which Analytics+ should use precalculated TWR values.

50

p.limitGroups

No

Limit groups

Limit groups linked to the portfolio. Specify a comma-separated list of limit group codes. Note that you can import either limit groups or single limits in one file, but you can't import both simultaneously.

You can use *** to add limit groups to the portfolio while keeping the existing limit groups and single limits. For example: ***,GROUPCODE1,GROUPCODE2.

51

p.limits

No

Limits

Single limits linked to the portfolio. Specify a comma-separated list of limit codes. Note that you can import either limit groups or single limits in one file, but you can't import both simultaneously.

You can use *** to add new single limits to the portfolio while keeping the existing limit groups and single limits. For example: ***,MAX20,EURO40.