Data Feeds

10.3.1 Bank Data Feeds: Creating a File for Manual Import

Most banks provide a statement export facility, accessed via their online banking portal. Please refer to instructions provided by your bank for creating and downloading an export file.

Typically, a range of file formats are available for selection when exporting. For manual import into Mclowd, you should choose the .CSV (Comma Separated Values) file format. This format can be readily opened with your spreadsheet application software (e.g. Microsoft Excel).
Export a bank statement for the period you wish to process.

Open the file in a spreadsheet application.

Mclowd requires that your file conforms to the following format:

  • Column 1: Date (formatted as dd/mm/yyyy)
  • Column 2: Amount
  • Column 3: Description

Modify the exported file, if necessary, to match this format, Modify spreadsheet for manual import

then save then save the modified spreadsheet as a .CSV file:

Save bank data file for import

 

Depending on the format of the file your bank provides, you may need to modify the file to suit Mclowd’s import specifications. For example:

  • National Australia Bank’s export file contains a column for the transaction date, amount (shown as positive for deposits and a negative number for payments), two blank columns, two columns containing the transaction description, and a column displaying the running balance.
    Exported NAB file

    • To combine the two description columns into one,
      • In cell C1, insert the formula ”=CONCATENATE(E1,F1)”.
      • Highlight cell C1 and click Copy
      • Place the cursor in cell C2 and hold the mouse button down while dragging to highlight all cells in the column
      • Click PASTE
        NOTE: There is no need to delete columns after C as they will be ignored.
  • ING Direct lists debits and credits in separate columns.
    • Insert a new column after the Date column and type ”=IF(D2 = ””,E2,D2)”.
    • Copy this formula and fill down to paste this formula into all filled rows.

For all banks:

  • Check that the date format is dd/mm/yyyy. Reformat the dates if necessary. (Excel may display a single digit day or month. This does not create any issues.)
  • Check for any rows that do not contain standard-format transaction data. For example, some banks export statements that contain messages. These rows should be deleted.

Mclowd programmers have adapted code to import Combank and ANZ export files without modification. For users exporting files from other banks, please contact Mclowd Support (email info@mclowd.com) if you have import issues, providing a sample export file.

 

 


about the author:

Lorraine Cobcroft

With a background in accounting and financial management, followed by two decades writing software documentation, Lorraine joined the Mclowd team in mid-2016 and is enjoying working with a dynamic team to enhance an innovative product that has the potential to revolutionize the way Australians manage their retirement funding.
Lorraine is also an accomplished business writer, ghost-writer, novelist and short-story writer and poet.

Leave a Reply