Simple bank receipt and bank payment transactions can be imported into Troika using the Transaction Import menu option accessed from Troika Utilities, Tools tab. The bank transactions can be in the client reporting currency or their own transactions currency. Foreign currency transactions can only be posted to nominal accounts with the same fixed currency or a mixed currency account. This document outlines how to setup and import the file, along with the file specification so you create your own import files.
A separate guide outlines how to import investment transactions using the same import menu option.
Setting up the file format for importing bank transactions
From the Transaction Import menu option in Troika Utilities, click on the Add New Entry button. Set the file mapping description as ‘FinApps’:
This file mapping will allow bank receipts and payments to be imported in Troika. Click OK to accept. The edit button will allow the name to be edited or the mapping name can be deleted in full by clicking the delete button.
Unique import file names
To prevent transaction imports from using the same file name more than once, within the Troika Utilities module, System tab and the menu option System Settings add the following entry in the 'ImportFileNameUnique' setting:
ImportFileNameUnique True
The file definition and field requirements are outlined at the end of this document.
Importing the bank transaction file
To import a bank transaction file, select the file mapping ‘FinApps’ at the top of the screen. Click on the edit button next to the import file option to navigate to the file which is going to be imported. The file type can be selected from either a txt (text file) or csv (comma separated value file). The file type filter can be changed at the bottom to view all file types. Click ‘Open’ to select the file once highlighted.
Clicking on the ‘Import’ button will load the transactions from the file into the pending area where these will be previewed. This screen can be maximised to show more details. The transactions are in the system but not posted at this point. Troika will display the number of records ‘processed’ and the number of records ‘ignored’.
For example:
Clicking on the ‘Preview button’ will display the pending transactions in a report. You will only be able to view transactions imported into the transaction import utility for the user currently logged on to Troika.
Transactions with missing or invalid fields are highlighted in red. Select the show 'Invalid’ option at the top of the screen to restrict the list to transactions with missing data. Entries can be amended by clicking on the edit button to the left of each record. The last column displays the number of errors or invalid fields, each record has.
The detail of each entry can also be amended, simply by double clicking on the record detail and typing in the revised details or correction. When editing the individual transactions; missing or invalid entries are highlighted.
For example:
Corrections can be made by simply over typing the entry and selecting an entry from a drop-down list. Changes to the client and fund can also be made by using the entry box to search for the correct client/fund.
The ‘Next’ and ‘Back’ buttons at the bottom of the edit screen can be used to review invalid transactions by selecting the ‘errors only’ check box.
Once all the transactions have been reviewed, valid transactions can be posted. Click on the ‘Post’ button to process the valid transactions. Records successfully posted are automatically removed from the import preview area. Invalid records remain in the preview until either:
- The transactions are corrected and posted.
- The transactions are cleared from the preview by clicking on the ‘Clear List’ button.
The Log File
The Log File option on the ‘Transaction Import’ menu option gives access to the history of the importing process for each file. Select the file type at the top of the screen, the import file and any additional filters required (date range, user). The log file history can be previewed in a report and/or printed.
The Undo... Option
In the event an import has been duplicated in error, there is an undo option.
Click the Undo... button to open the Undo Import Batches window. Any prior successfully completed batches will be shown within here.
Select the blue arrow at the end of the batch/import line you need to undo. Click ___ to confirm the undo.
The undo will take a period of time to complete as it recalculates the individual trial balances and writes audit records:
Recalculating TBs...
Writing audit trail records...
On successful completion of the undo a 'Batch undone' message will display. Click Close to exit.
Bank Transaction Import – File Specification
This file format requires a header record which is shown in the sample file below.
The file can be saved as a CSV or a TXT file as long as the file is a CSV (comma delimited) file. The file must be comma delimited and not tab delimited. When preparing the CSV file within Microsoft Excel, please ensure that you select the file type carefully as Excel can override the file type very easily.
Also, cell formats should be carefully checked ensuring text isn’t converted to numeric values in error. For example, a FundID with leading zeros ‘001’ can be easily converted to ‘1’ which will not be successfully imported into Troika.
Bank records can be imported in a single currency (the client reporting currency) or a foreign currency. For foreign currency transactions the following fields must be used within the file:
- MAINAMOUNT to record the transaction amount in transaction currency
- CURRENCYCODE to record the transaction currency
- FAMOUNT to record the transaction amount in the client reporting currency
- CURRENCYRATE to record the exchange rate between the MAINAMOUNT and FAMOUNT.
Options for supplying FX values in the import:
- You can supply in the file just the MAINAMOUNT and CURRENCYCODE, and the import will set the FAMOUNT (reporting amount at the prevailing rate).
- You can supply in the file the MAINAMOUNT, CURRENCYCODE and FAMOUNT, and the import will calculate the correct rate between the two given the values.
- You can supply in the file the MAINAMOUNT, CURRENCYCODE, FAMOUNT and CURRENCYRATE and the imported transaction will accept all your given values.
Field No | Field Name | Type | Size | Mandatory | Content Description |
Example Data |
Field01 | PERIODID | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field02 | TRANSDELETE | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field03 | TRANSIMPORTID | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field04 | TRUSTOWNCODE (File Ref) | Character | 50 | Mandatory | Client identifier | XYZ123 |
Field05 | FUNDID | Character | 3 | Mandatory | Fund identifier | ‘001’ |
Field06 | TRANSDATE | Date/Time | 8 | Mandatory | Transaction date DD/MM/YY | 12/11/16 |
Field07 | TRUSTINVESTMENTLEDGERID | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field08 | MAINAMOUNT | Numeric/double | Mandatory | Transaction amount in transaction currency. A positive value will credit the bank nominal code and negative value will debit the bank nominal code. | 20,000.00 | |
Field09 | UNITS | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field10 | CURRENCYRATE | Numeric/double | Mandatory unless no currency | Enter the exchange rate if a foreign currency transaction | 0.6283285 | |
Field11 | CURRENCYRATEMARKER | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field12 | DETAIL | Character | 255 | Optional | Transaction detail | |
Field13 | VOUCHERREF | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field14 | BANKREF | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field15 | TRANSTYPEID | Long Integer | 4 | Mandatory | 22 for payments, 23 for receipts | 22 |
Field16 | TRANSSTYPEID | Long Integer | 4 | Mandatory | 83 for payments, 82 for receipts | 83 |
Field17 | TRANSSTATUS | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field18 | TRANSMAINID | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field19 | MAINNOMINALCODE | Character | 8 | Mandatory | Nominal code for analysis | 10300 |
Field20 | BANKNOMINALCODE | Character | 8 | Mandatory | Nominal code for bank | 50200 |
Field21 | MAINTICKED | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field22 | TICKED | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field23 | CHEQUENUM | Character | 10 | Optional | Cheque number | 12345 |
Field24 | VALUEDATE | Date/Time | 8 | Mandatory | Value date DD/MM/YY | 12/11/16 |
Field25 | ENTRYDATE | Date/Time | 8 | Mandatory | Entry date DD/MM/YY | 12/11/16 |
Field26 | USERID | Character | 3 | Mandatory | User initials | XYZ |
Field27 | WHEREFROM | Long Integer | 4 | Optional | Source transaction reference | 56994A |
Field28 | BATCHNUMBER | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field29 | BATCHTYPEID | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field30 | UNITPRICE | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field31 | TS | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field32 | Qualdate | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field33 | Narrative1 | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field34 | Narrative2 | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field35 | CGTExclude | No Data – this field is ignored by the import | Field must exist but leave blank | |||
Field36 | FAmount | Numeric/double | Mandatory | Transaction amount in reporting currency, | 12,566.57 | |
Field37 | CurrencyCode | Character | 3 | Mandatory | Currency Code | GBP |
Example single currency file content (CSV file) : -
PERIODID,TRANSDELETE,TRANSIMPORTID,TRUSTOWNCODE,FUNDID,TRANSDATE,TRUSTINVESTMENTLEDGERID,MAINAMOUNT,UNITS,CURRENCYRATE,CURRENCYRATEMARKER,DETAIL,VOUCHERREF,BANKREF,TRANSTYPEID,TRANSSTYPEID,TRANSSTATUS,TRANSMAINID,MAINNOMINALCODE,BANKNOMINALCODE,MAINTICKED,TICKED,CHEQUENUM,VALUEDATE,ENTRYDATE,USERID,WHEREFROM,BATCHNUMBER,BATCHTYPEID,UNITPRICE,TS,Qualdate,Narrative1,Narrative2,CGTExclude,FAmount,CurrencyCode ,,,BER986,001,12/11/16,,920.00,,,,Bank Payment,,,22,83,,,20120,51200,,,1545,12/11/16,20/11/16,KGP,56994A,,,,,,,,,920.00,GBP ,,,BER986,001,12/11/16,,1050.00,,,,Bank Payment,,,22,83,,,20120,55160,,,1546,12/11/16,20/11/16,KGP,56994B,,,,,,,,,1050.00,GBP ,,,BER986,001,12/11/16,,850.25,,,,Bank Payment,,,22,83,,,20120,55160,,,1547,12/11/16,20/11/16,KGP,56994C,,,,,,,,,850.25,GBP ,,,BER986,001,12/11/16,,350.50,,,,Bank Payment,,,22,83,,,20120,55160,,,1548,12/11/16,20/11/16,KGP,56994D,,,,,,,,,350.50,GBP
Example mixed foreign currency file content (CSV file) : -
PERIODID,TRANSDELETE,TRANSIMPORTID,TRUSTOWNCODE,FUNDID,TRANSDATE,TRUSTINVESTMENTLEDGERID,MAINAMOUNT,UNITS,CURRENCYRATE,CURRENCYRATEMARKER,DETAIL,VOUCHERREF,BANKREF,TRANSTYPEID,TRANSSTYPEID,TRANSSTATUS,TRANSMAINID,MAINNOMINALCODE,BANKNOMINALCODE,MAINTICKED,TICKED,CHEQUENUM,VALUEDATE,ENTRYDATE,USERID,WHEREFROM,BATCHNUMBER,BATCHTYPEID,UNITPRICE,TS,Qualdate,Narrative1,Narrative2,CGTExclude,FAmount,CurrencyCode ,,,BER986,001,12/11/16,,1500.00,,0.771268,,Bank Payment USD,,,22,83,,,20120,51200,,,1545,12/11/16,20/11/16,KGP,56994A,,,,,,,,,1156.90,USD ,,,BER986,001,12/11/16,,6500.50,,0.880480Ê,,Bank Payment EUR,,,22,83,,,20120,55160,,,1546,12/11/16,20/11/16,KGP,56994B,,,,,,,,,5723.56,EUR ,,,BER986,001,12/11/16,,800.00,,Ê0.605203,,Bank Payment CAD,,,22,83,,,20120,55160,,,1547,12/11/16,20/11/16,KGP,56994C,,,,,,,,,484.16,CAD
An example import (XLS file) is attached.
This article is confirmed to be applicable from Troika release 2.08.027.