Importing a broker data file
While it is possible to import a broker data file using Mclowd’s bank manual import method, you must ensure the import file is in the correct format:
- Date (dd/mm/yyy)
Files exported from a broker account (such as the below example from an NAB trade) will typically list amounts in Debit and Credit columns. As well as deleting additional columns, you need to combine the amounts into a single column, converting all payments to negative numbers.
The steps to modify the file for import to Mclowd are:
- Select Columns D and E and format as Currency.
- Delete Column F (Balance).
- Delete Column B (Type).
- Insert a new Column B after the date column. Format the new column as Currency.
- In the new column B, in cell B2, type formula =IF(D2=0,E2,D2). This tells Excel to combine the Debit and Credit amounts into a single column, but converts debits to negative numbers.
- Copy the formula from cell B2 and paste down into all filled rows.
- Delete the top row (the headings).
Leave Columns D and E – if you delete them, the formula in Column B will not work. Mclowd will ignore these columns on import. The new file now presents as:
Save the file as an CSV file. This file is now ready to be imported into Mclowd using the MANUAL IMPORT function located on the TRANSACTIONS tab.