Download

iCreateOFX Basic - Generic Script Help

Convert regular bank and creditcard CSV, QFX, QBO and QIF files to OFX


The Scripts dialog

The Generic Script (or Common CSV Script) is utilised by the program to load all the files that are laid out simply. You can see this when you load the Scripts dialog where any script with an Installed Version of Common Script actually uses the (Common CSV) Generic Script.
The script also provides a means by which you can load and convert files that you create (as opposed to downloading from a financial institution).

Because the Common CSV script is always installed (since other scripts depend on it), the process of installing it to the Script stack is slightly different. You'll need to open the Scripts dialog, then (without clicking on the Get Scripts button) scroll down and click on Show in the script's row. Whenever a new version of the Common CSV script is published (and unlike other scripts), the app will automatically update it during the periodic update check that the program runs.

iCreateOFX Basic Scripts dialog


The script, despite being named Common CSV, can also load Excel (*.xls and *.xlsx) in addition to the *.csv files. For the Excel files, you'll have to either have Excel installed or install Microsoft Access Database Engine Redistributable. Click on the Help button if prompted.

The file header

The Generic script supports a total of 18 different fields, some of which are inter-changeable. The supported fields are:
Date, FITID, ID, Memo, Details, Description, Payee, Reference, TRNTYPE, Currency, Account, Amount, Amount (Credit), Amount (Debit), Amount (Single), Debit, Credit, Balance
The inter-changeable items (grouped accordingly) are:

  1. FITID and ID - (optional as will be generated) this is a unique transaction ID which is optional as the application generates one based on the date of the transaction, if it is missing in the file.
  2. Details, Description and Payee - (optional) this can be either a simple description in the transaction (limited to 256 characters) or the Payee name (limited to 32 characters). The grid will still display a hading of Payee even when it is a description.
  3. Amount and Amount (Single) - (Required) this is the transaction amount. NOTE: Credit amount MUST be signed.
  4. Debit and Amount (Debit) - (Required if 3 above is not included) if the transaction amount is a debit entry, then populte this column, else leave it blank.
  5. Credit and Amount (Credit) - (Required if 3 above is not included)a signed transaction amount.

The other header fields are:

  1. Date - (Required) the transaction date. If loading a CSV, make sure the date is in a local system format OR select the format from the Date Format drop down above the script stack. For Excel, if the column is a string format then format it as date.
  2. Memo - (optional) this is simply a memo to the tranaction
  3. Reference(optional) this is a transaction reference which can be additional to Memo and / details / Description.
  4. TRNTYPE - (Required) DEBIT or CREDIT (or one of the OFX specification transaction types, i.e: DEBIT, CREDIT, CHECK, INT, FEE, SRVCHG, DEP, ATM, POS, XFER, PAYMENT, CASH, DIRECTDEP, DIRECTDEBIT, REPEATPMT)
  5. Currency (optional) this is the 3 letter currency code per ISO 4217. The default currency selected will be applied to all transactions if this column is not included. NOTE: the application supports multiple currencies (as does the OFX specification), however, you'll have to add a rate to all none primary currency transaction in the grid.
  6. Account - (optional) the account number to which the transaction belongs (must not be more than 22 characters). The application (as well as the OFX specification) supports multiple accounts in the same file and the default account number will be applied to all transactions if this column is absent.
  7. Balance - (optional) account balance NOTE: The first balance with the latest/ newest date will be assumed to be the closing balance.

The minimum file format

At a minimum, the CSV (or Excel) file MUST contain the following fields (with sample data with a UK date):

  1. With a single Amount column (debits must be signed):

    Date
    Memo
    Amount
    11/01/2022
    Payment
    -5.01
    12/01/2022
    Deposit
    12.02

  2. With two Amounts' columns:

    Date
    Memo
    Debit
    Credit
    11/01/2022
    Payment
    5.01
    12/01/2022
    Deposit
    12.02

1. The order in which the columns are laid out is NOT important, however the spelling and case are.
2. The Common CSV script assumes the header is the first line in the file.