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 Preference → 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 to guarantee the correct handling of Scandinavian letters.
Download the CSV file template:
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:
|
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 ???. 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 ???. |
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 ???. |
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 ???. |
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 ???. |
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 ???. 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 ???. |
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 ???. For example: :---<key>. You can use three minuses (---) syntax to remove an attribute value. For example: ---<key>. For details, see ???. |
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 ???. |
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 ???. |
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 ???. (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 portfolio while keeping the existing ones. For example: ***, <group code>. For details, see ???. |
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. For details, see ???. |
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. For details, see ???. |