Skip to main content

BGL Excel (XML) Import

Overview

A BGL Import XML upload is available in Simple Fund 360. This XML will provide you investment, cash and income transactions and can be uploaded via the Wraps screen.

  1. Enter and confirm Transactions in MS Excel
  2. Export Transactions 
  3. Import into Simple Fund 360

 

BGL XML Import Template

To download the XML Import template, click here: BGL XML Import Template.xls

Navigation

 

From the Main Toolbar, go to Connect. mceclip0.png
Select Feed Management from the list.

mceclip1.png

From within the Feed Management screen, select the 
Wraps and Platforms Global tab. 

 98.png

Add Feed

  1. From the Wrap and Platform Global tab, use the search bar to locate the fund and select (+) Setup.

    69.png

  2. The Wrap Feed Setup screen will appear. The fund name will appear in the Entity field.

  3. Select BGL File Import as the Feed Provider.

    99.png

  4. Keep the Account ID as BGL.

  5. Select the Bank Account for the XML to be uploaded to. The XML file can support more than one bank; however if no bank details are listed for transactions (see next tab) banking transactions will be loaded to this account.

    BTfeed5.png

  6. Link Holding Accounts to Feed: Linking holding accounts to a specific feed ensures transactions from this feed are allocated to these bank/investment accounts. This is particularly important if there are other feeds set up in the fund that have holding accounts that need to be kept separate from this feed’s holding accounts for reporting and CGT reasons. For more information on Account Mapping, click here. 

  7. Select Save.

XML Template Instructions

Editing the Template

Depending on your security settings, you may be asked to both:

  1. Enable Editing
  2. Enable Content

When opening the Excel Template file. You will need to answer affirmative for both these prompts.

    1. It is recommended that you copy the spreadsheet so a copy can be made for each fund.
    2. Clear the example data by clicking Clear ALL Transaction Data 

Clear All

Clicking Clear ALL Transaction Data will remove all data entered into the tab, including anything entered manually. This cannot be undone.

  1. Hover your mouse over a red triangle in a tab in the top right corner to learn more about the column requirements.

    ExcelXML1.png

  2. Enter one transaction per line. Users can select the Security Type for a unitised investment by clicking into the tab and selecting it from the drop down.

    ExcelXML2.png

    Validation

    The XML file will constantly validate the information entered to ensure that each appropriate field has been completed correctly based on the Transaction type selected.

  3. Note: Foreign currency conversion supported using The BGL XML importer (Bank Transaction type only).

    Prior to uploading the file, in the currency column, select a currency for Simple Fund 360 to automatically convert the amount on the transaction date using the Foreign Currency screen information (end of day rate). If no currency is selected AUD is the default rate. Refer to Foreign Currency for supported currencies. In the amount column record the foreign currency amount.

    XMLForex.png

  4. Select Create Transaction File.
    CreateTransactionFile.png

  5. You will be asked to confirm the ranges of the cells used to generate the XML file and where to save the generated XML to.

Investment Opening Balances

Investment purchase parcel history when inputting opening balances can be recorded under the Investment_OpeningBalance tab. The Notional Gainscolumn can be used for 30/06/2017 balances where Transitional CGT Relief was applied in an external software.

 

  1. Clear the example data by selecting Clear ALL Transaction Data. 

    Note: Clicking Clear ALL Transaction Data will remove all data entered into the tab, including anything entered manually. Ensure only example data has been removed.

  2. Input the following information for each parcel.

    Cell
    Explanation
    Transaction Type Investment Transaction is the only type that can be used in this tab.
    Transaction Date Input the date as 30/06/XX of the financial statements you are working off.
    Amount Record the cost base for the parcel.
    Description Record a narration.
    Security Type Select the Investment Type.
    Security Code Select the Security Code. This will populate as the INVESTCODE (sub account) when uploaded.
    Quantity Input the purchase parcel number of units.
    Settlement Date Record the Settlement Date.
    Contract Date Record the Contract Date. if not recorded, this field will pre-fill as the Transaction Date.
    Notional Gain Where a Notional Gain exists from the application of CGT Relief at 30 June 2017, record the notional gain amount. Note: Notional gains can only be recorded where the Transaction Date is 30 June 2017.

     

    XMLOB__5_.pngXMLOB__4_.png

     

  3. Select Create Transaction File.

Upload XML File

  1. From the Feed Management Dashboard, select Advanced Options & Reports.

    100.png

  2. From the drop-down menu, select File Import. 

    101.png

  3. Select BGL File XML import from the left hand side. 

    102.png
  4. Select the upload box. You have the option to drag-and-drop a file into this box, or click on it to navigate through your files and upload the XML document. Ensure the XML file is stored on your computer or network.

    103.png

  5. Select Import.

Note

Date Feed can Not be imported for the period when Create Entries have been processed.

Transaction Matching

Transaction Type
Matching Logic
Bank Transactions

Current Matching: Transactions will match one side to the bank and the contra side to the clearing account 91000. Transactions can then be matched via Transaction Matching.

Coming Soon: Users can input accounts to automatically match contra entries during upload.
Income Transactions Current Matching: Income transactions will be matched to the correct account code during upload based on the Security Code column. Non Unitised investment income transaction cannot be input in this tab. This income is input in the Bank Transactions tab.
Investment Transactions

Current Matching: Purchases and Disposals will be matched to the correct investment account during upload based on the Security Code column.

Coming Soon: Cost Base Adjustment transactions will be supported.

 

Reverse an XML Upload

 

From the Main Toolbar, go to Accounting.

AccountingselectionNew.png
Select Transaction list

TransactionList.png

 

  1. Select Transaction Filter.

    transactionfilter1.png
  2. Set the following transaction criteria: Data Source equals BGL XML File.

    ReverseXML1.png
  3. Select Search.
  4. Select the topmost check box in the Transaction List and select Select All from the drop down menu that appears. This will select all the transactions from the XML File Upload.

    ReverseXML2.png

    ReverseXML3.png

    ReverseXML4.png
  5. Select Delete.

    ReverseXML5.png
  6. Select Ok.

    ReverseXML6.png

Transaction Type Reference

Transaction Types Supported
Explanation
Bank Transactions

Banking entries are income and expense transactions including fees, non-unitised term deposit interest and pension payments.

Input the banking details next to each transaction and this will load transactions as follows:

DR/CR 60400/BANKCODE

DR/CR 91000 (Bank Data Clearing Account for Transaction Matching).

Coming soon: The ability to code directly to chart accounts.

Multiple banks can be supported in the one file. Ensure that the bank chart account and it's banking details are recorded in Simple Fund 360 and during the upload the bank transactions will be created.

ExcelXML3.png

If no BSB or Bank Account Details are recorded for a transaction it will be loaded to the default account selected in the feed setup. Expenses are recorded with a negative sign in front of the amount.

Required fields:

Transaction Date

Amount

 
Income Transactions

Record income transactions for unitised investments (such as share dividends). Tax data can also be recorded. Select the Security Type and record the security code (both listed and unlisted securities supported) to map the transaction to the correct income account.

Required fields:

Transaction Date

Amount

Security type

Security Code

Example

DR 60400/BANKCODE

CR 23900/INVESTCODE
Investment Transactions (Unitised)

Record unitised investment purchases and disposals with purchase/disposal parcel amounts. Select the Security Type and record the security code (ASX securities currently supported) to map the transaction to the correct account.

Required fields:

Transaction Date

Amount

Security type

Security Code

Quantity

Example:

DR/CR 60400/BANKCODE

DR/CR 77600/ASX or 91000 (for securities not listed on the ASX)

Non-ASX securities such as international shares are not currently supported for automatic matching.

 

Related articles