Overview


To reduce the time our clients spend manually entering in investment details, this guide explains how to import a list of investments, from a pre-populated excel spreadsheet.


Checking and Confirming the 'Basic Import' Schema


The 'Basic Import' attempts to populate a base format for imports, suitable for most clients.


Prior to using this import for the first time, users will need to confirm the expected format of the Income Type Codes, Traded Type Codes, Listing Type Codes, CGT Status Codes and Investment Type Codes responses, which will be used in the spreadsheet, within the 'Basic Import' schema.


Within the Utilities module, select the Tools tab and the option Import Excel Investment Details.  From the Schema: field select the 'Basic Import' option, and click the Edit button.


No changes will be required to the first 11 fields of this import schema.


However, within the later Income Type Codes, Traded Type Codes, Listing Type Codes, CGT Status Codes and Investment Type Codes fields, options specific to your setup will be displayed within the drop down lists.


For example, within Income Type Codes, standard data should display:

  • Ordinary
  • Fixed Interest
  • Unit Funds
  • Foreign
  • Property
  • Overseas UT
  • Other


These now require confirming.


It is probably that users will wish to abbreviate their responses in their spreadsheet, e.g. ORD for Ordinary, or FI for Fixed Interest.  For each option, users should confirm how that option is to be referred to in your import spreadsheet, by populating the right hand column.


For example, in the event users wish to use a shortened 'ORD' to mark an imported investment to be assigned an income type of 'Ordinary', the field within the 'Basic Import' schema should be shown as:


Alternatively, in the event users prefer to use the standard full response, they should still overtype Ordinary to confirm it/commit it to the database.


For example:


Continue this for all other drop down options in each of the Income Type Codes, Traded Type Codes, Listing Type Codes, CGT Status Codes and Investment Type Codes fields.


Once done, click Save to save changes to the 'Basic Import' schema. 


Setting Up The Excel List


Create an Excel spreadsheet using the following as file headers in columns A to I:

  • Long Name
  • Short Name
  • ISIN Code
  • CGT Category
  • Traded
  • Listed
  • Income Type
  • Category Code
  • SEDOL Code


Populate the spreadsheet with the information you wish to import.


Our most standard setup will likely require the following:


The CGT Category column will likely require either:

  • Exempt
  • Liable
  • QCB (Qual Corp Bond)

The Traded column will likely require either:

  • Traded
  • Not Traded

The Listed column will likely require either:

  • Listed
  • Unlisted

The Income Type column will likely require either:

  • Ordinary
  • Fixed Interest
  • Unit Funds
  • Foreign
  • Property
  • Overseas UT
  • Other

The Category Code column will likely require either:

  • UK Dividends (listed)
  • UK Dividends (unlisted)
  • UK UnitTrust - Dividends (listed)
  • UK UnitTrust - Dividends (unlisted) 
  • Fixed Interest Unit Trust (listed)
  • Fixed Interest Unit Trust (unlisted)
  • UK Government Securities
  • Fixed Interest Securities (listed)
  • Fixed Interest Securities (unlisted)
  • Foreign securities not giving deceased control
  • Foreign securities that gave deceased control
  • Foreign unlisted securities not giving control
  • Real Estate Investment Trust
  • IC
  • UK Capital Investment Bonds
  • UK Unit Trust - Interest (quoted)
  • Private Equity


In the event users have chosen to use shortened responses (see above), you should amend your spreadsheet accordingly, with the expected responses.


An example import file (based on the above, most standard, setup) is attached.


Importing the List


Within the Utilities module, select the Tools tab and the option Import Excel Investment Details.


From the Path: option, navigate to the location of the excel file to be imported.


From the File: option, select the excel file to be imported from the list.  Note only those with a .xlsx extension will be listed here.


From the Schema: field select the 'Basic Import' option.


Click Import.


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

This article was last reviewed 10/2022.