File format for importing postings
Postings are imported in FA in a CSV file or in an XLSX file, with the file columns described below. The file is based on column headers you can define in the first row of the import file – you can specify the columns in any order, and the system recognizes values in each column based on the text in the header row. After the first row, each row in the file should correspond to one posting. You can also skip the header row if the columns in your file are put in the same order as in the template file and in the table below.
You can define the following properties of the import file in Preferences → Importing → File format (see Preference - Importing for details):
CSV 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. By default, Windows-1252 is used. Choose UTF-8 if your import data includes non-ASCII characters, for example, such letters as "å", "ä", "č", "ē".
The file columns are described below.
Download the CSV and XLSX file templates here:
Updating postings
You can update existing postings or market price entries by importing a new file with corrected data. The system identifies the posting to update by the posting external ID (po.extId), transaction external ID (po.transactionExtId), bank account number (po.bankAccountNumber), and security code (po.securityCode), so these fields must be filled in.
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.
FA Format for importing postings
# | Code | Required | Name | Description |
|---|---|---|---|---|
1 | po.extId | No | No. | External ID of the posting. Used when updating an existing posting. If left empty, the system generates a running number as an ID. |
2 | po.status | Yes | Status | Posting status:
|
3 | po.parentPortfolio | Yes | Portfolio ID | Text or number uniquely identifying a portfolio. |
4 | po.postingDate | Yes | Posting date | Date formatted as yyyy-MM-dd. |
5 | po.effectiveDate | No | Effective date | Date formatted as yyyy-MM-dd. |
6 | po.accountNumber | Yes | Account number | Bookkeeping account number. |
7 | po.accountSetName | Yes | Account set | Account set name. |
8 | po.amount | Yes | Amount | Posting amount: positive number for credit, negative for debit. |
9 | po.reference | No | Posting reference. Free text, allowing you to fill in additional details on your postings. | |
10 | po.memo | No | Memo | Posting memo. Free text, allowing you to fill in additional details on your postings. |
11 | po.transactionExtId | No | External ID | External ID of the linked transaction. If you provide this ID, the bank account number and security code are fetched from the transaction (do not fill them in). |
12 | po.bankAccountNumber | No | Bank account no. | The number of a bank account linked to the portfolio. |
13 | po.securityCode | No | Security code | The code of the security this posting is related to. |