Create your own importer
You can import transactions, trade orders, and market prices in external files such as files you receive from a counterparty. To do this, you need to configure the reader format so that the FA system can read the external file, and the following sections describe this process. To create a transaction importer, we recommend using the sample XML configuration. You can copy it from this page (see Quickstart: Use sample reader configuration for transaction import) and paste it into FA Back in step 1.
Step 1 - Define the basic info
Define the basic information of the importer:
Go to Preference → Importing → Reader format configuration and click Add.
In the Basic info tab, fill in the fields:
- Name
The importer name (shown in the Import window when you upload the file).
- Read from line
A number that defines how many header rows the importer skips. Define 1 if the importer should read all rows. If your file contains a header row, define 2 to skip it.
- Separator
The column separator, typically a comma or a semicolon.
- Date format
Date format used in the external file, for example, dd.MM.yyyy.
- Decimal point
The decimal point in the external file.
- Importer type
The type of information the external file contains: transactions, trade orders, or market prices.
Click Next.
The Format mapping tab appears.
Step 2 - Map and include columns
In the Format mapping tab, you need to select columns one by one on the left, map their values between the external file and the FA system, and add the columns to the list on the right. Before you include a column in the list on the right, you need to define its Format type based on if the format and values in your external file match the FA system (see External file column format and values match FA and External file column format or values don't match FA).
You need to include at least all required columns in the importer:
For transactions, see the required fields in FA Format for importing transactions in FA Back reference.
For trade orders, see the required fields in FA Format for importing trade orders in FA Back reference.
For market prices, the required fields are "Security update code", "Market date", and "Close price" (for field descriptions, see FA Format for importing security prices).
If a column is not required by the FA file format, you can use Skip column to have the importer skip it. Skipping a column creates an empty field.
External file column format and values match FA
If the format and values of the column in the external file are the same as in the FA system, the default format type can be used. For example, in the FA system, the transaction type column "type" is a string with values such as "B" (buy) and "S" (sell). If both the format and values of the column in your external file match the FA format and values, use the default format type:
Select a column in the list on the left and select the default option suggested by the system as the Format type (for example, "STRING_TYPE", "DATE_TYPE", or "NUMBER_TYPE").
Use the right arrow button to move the column into the list on the right.
The column is included in the importer. You can now select another column on the left.
External file column format or values don't match FA
If the format and values of the column in the external file are not the same as in the FA system, you need to map the values between the external file and the FA system. For example, in the FA system, the transaction type column "type" is a string with values such as "B" (buy) and "S" (sell). If your external file uses, for example, the values "Purchase" and "Sell" for this column, you need to map them to the FA values:
Select a column in the list on the left and select "MAP_TYPE" as the Format type.
Click the right arrow button to open the Map type parameter value window. Add keys and values. The type of key you select determines what values you need to define in the value field:
TRANSACTION_TYPE_MAPPING_EXPR. Option to map the column values in the external file to the values configured in the FA system. For example, if your external file has a transaction type column with the values "Purchase" and "Sell", map them to the FA values "B" (buy) and "S" (sell) with the equal sign and separate the values with semicolons. Example: Purchase=B; Sell=S.
EXT_PORTFOLIO_ID_NAME. Option to map a portfolio in the external file to the portfolio in the FA system using its external ID. Portfolios can have several external IDs in FA Back (defined in Portfolio window, IDs tab), so you need to define the external portfolio ID as the value to let the FA system know which ID to look for. Example: Nordea.
FIXED_VALUE. Option to map the external column to a fixed value, have the FA system fetch the value, or define the value with variables.
Set a fixed value: For example, you can set the settlement date as two business days after the transaction date by defining "2" as the value for the "settlementDate" column. You can also set a constant exchange rate by defining a number as the value for the "fxRate" column (using the decimal point you defined in the basic info).
Have FA fetch the value: You can have the FA system fetch a value, for example the market price on the transaction date, by defining "AUTO" as the value (see Automatic values: AUTO in FA Back reference).
Use variables: You can define the column value with variables, for example, to modify a value before importing. Place variables inside curly brackets and field names inside square brackets. For example, you can import the absolute value for the "tradeAmount" column by defining
{Math.abs([tradeAmount])}
as the value. You can also use conditionals such as{'[type]'.equals('Settlement fee') ? ‘Settlement fee debt SEB' : 'AUTO'}
where "Settlement fee debt SEB" is used as the transaction account if the external file transaction type is "settlement fee", and otherwise the system fetches the account.
Click OK.
The column values are mapped to the FA system and the column is included in the importer. You can now select another column on the left.
Step 3 - Arrange the column list to match the external file
After defining the format mappings and including the columns in the list on the right, you need to make sure the list is in the same order as your external file:
Use the Up and Down buttons to place the columns in the same order as in your external file.
Click Save.
The importer is ready to use.
Quickstart: Use sample reader configuration for transaction import
You can use our sample configuration as a basis to create your own transaction importer:
Go to Preference → Importing → Reader format configuration.
Click Add and then View reader as XML.
Copy and paste the following sample importer definition into the editor and edit the values according to your needs.
<?xml version="1.0" encoding="UTF-8"?> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormat> <importerType>TRANSACTION_IMPORTER</importerType> <readFrom>1</readFrom> <separator>;</separator> <decimalPoint>,</decimalPoint> <readFileName>false</readFileName> <convertParams class="linked-hash-map"> <entry> <int>0</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>reference</name> <type>STRING_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>1</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>counter</name> <type>STRING_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>2</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>transactionDate</name> <type>DATE_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>3</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>type</name> <type>MAP_TYPE</type> <mapValue> <entry> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY>TRANSACTION_TYPE_MAPPING_EXPR</fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY> <string>Buy=B;Sell=S;Settlement fee=COUT</string> </entry> </mapValue> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>4</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name/> <type>BLANK_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>5</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name/> <type>BLANK_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>6</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>amount</name> <type>MAP_TYPE</type> <mapValue> <entry> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY>FIXED_VALUE</fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY> <string>{Math.abs([amount])}</string> </entry> </mapValue> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>7</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>security</name> <type>STRING_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>8</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name/> <type>BLANK_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>9</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name/> <type>BLANK_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>10</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>cost</name> <type>NUMBER_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>11</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>tax</name> <type>NUMBER_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>12</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>tradeAmount</name> <type>MAP_TYPE</type> <mapValue> <entry> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY>FIXED_VALUE</fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY> <string>{Math.abs([tradeAmount])}</string> </entry> </mapValue> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>13</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>fxRate</name> <type>NUMBER_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>14</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name/> <type>BLANK_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>15</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name/> <type>BLANK_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>16</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name/> <type>BLANK_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>17</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>settlementDate</name> <type>MAP_TYPE</type> <mapValue> <entry> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY>FIXED_VALUE</fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY> <string>2</string> </entry> </mapValue> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>18</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name/> <type>BLANK_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>19</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>tags</name> <type>MAP_TYPE</type> <mapValue> <entry> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY>FIXED_VALUE</fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY> <string>{'[tags]'.equals('CD') ? 'Order Type-CD' : '[tags]'.equals('WH') ? 'Order Type-WH' : ''}</string> </entry> </mapValue> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>20</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>intInfo</name> <type>STRING_TYPE</type> <mapValue/> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>21</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>parentPortfolio</name> <type>MAP_TYPE</type> <mapValue> <entry> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY>EXT_PORTFOLIO_ID_NAME</fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY> <string>Nordea</string> </entry> </mapValue> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> <entry> <int>22</int> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> <name>account</name> <type>MAP_TYPE</type> <mapValue> <entry> <fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY>FIXED_VALUE</fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam_-MAP__TYPE__KEY> <string>{'[type]'.equals('Settlement fee') ? Settlement fee debt SEB' : 'AUTO'}</string> </entry> </mapValue> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormatParam> </entry> </convertParams> <dateFormat>dd.MM.yyyy</dateFormat> </fi.fasolutions.mod.portfoliomanagement.reader.xml.ReaderFormat>
Click OK and then Save.
The transaction importer is now defined and ready to use.