thewealthworks Customer Support Portal

Overview

Simple investment purchase and sale transactions can be imported into Troika using the Transaction Import menu option accessed from Troika Utilities. The investment 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 bank 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 a new file mapping’ button using the button with the red arrow.

Set the file mapping description as ‘FinApps_Inv’. 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, add the following entry to the ‘system settings’ in the Utilities module:

ImportFileNameUnique = True

The file definition and field requirements are outlined at the end of this document.

Importing the investment transaction file

To import an investment transaction file, select the file mapping ‘FinApps_Inv’ 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 only be selected as csv (comma separated value) file. 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 editing of the transaction details also allows an investment to be selected or amended either by name or by typing in the SEDOL code. The units and narrative can also be edited.

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.

Investment Transaction Import – File Specification

This file format does not require a file header row record.

The file can only be saved as 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.

An example investment import file (csv format) is attached.

Content is confirmed as:

,,,4948.75,,GBP,,,FPS986,001,FPS986,12-Jan-24,,,,,,,,,,,,,,,,,4700,1.0425,B012YP7,,,,Initial purchase of units,,,,,,REF12345,P,,,,,FPS,12-Jan-24,14-Jan-24,,,BARGAIN

,,,10070.21,,USD,,,FPS986,001,FPS986,12-Jan-24,,,,,,,,,,,,,,,,,4200,2.3645642,B012YP7,,,,Initial purchase of units,,,,,,0327D313E6,P,,,,,FPS,12-Jan-24,14-Jan-24,,,BARGAIN

This article is confirmed to be applicable from Troika release 2.08.027.