Download

iCreateOFX Investment - Convert brokerage CSV to OFX
Generic Script help.


Introduction

The iCreateOFX Investment Generic CSV script reads investment / brokerage CSV files. The script is capable of reading data from any brokerage as long as the said file contains (at a minimum) specific data columns required for an investment OFX transaction.

The script expects transaction lines to be delimited by comma with any fields containing commas qualified with double quotes. Currently, the file needs to be laid out in a particular order for only the minimum and extended layouts, and the next section expounds further on this.

The file layout
  1. The minimum layout - DateActionTickerUnitsPrice FeesTotalMemoIDCurrency Rate
  2. The extended layout - Trade DateSettle DateActionTickerUnits Price FeesTotalMemoIDCurrency Rate
  3. The new flexible layout - Starting with version 3 of the main program (release due at the end of Q1 2017), the Generic Script will support a flexible CSV file layout.The flexible layout recognises all the unique column headers supported by the minimum and extended layouts in addition to Activity Date and Transaction Date as substitutes for the Trade Date and Settle Date in the extenteded layout respectively, or either as a substitute for the Date column in the minimum layout.
    Though the securities' dialog in the main program still supports security import, the CUSIP, SEDOL or ISIN plus the Security Name or Name optional columns have been added to help in consolidating security identification.

The script will default to the flexible layout if all six required columns are present in the first line / header of the CSV file. The only difference between the minimum and extended layouts are the dates. For the extended layout, the CSV file contains both the Trade Date and the Settle Date. In the case of the minimum layout, the single date provided for each transaction will be applied to both the trade and settlement date variables in the resultant OFX /QIF file.

NOTE: The bold items are required in order to be able to support the full set of investment transactions, however, the Ticker, Units and Price fields are not required for cash investment transactions, which include interest, deposits and withdrawals.
The flexibility in the flexible layout lies in not expecting the columns to be ordered in any particular way (which the former 2 require).

The layout fields
  1. Date Trade Date Activity Date REQUIRED - Only one and at least one of these columns is required. The date MUST be in the system's format.
  2. Settle Date Transaction Date Optional - This also has to be in the system date format if included and only one should be included.
  3. Action REQUIRED - This describes the investment transaction and there is a comprehensive list of the suported transactions in the OFX message set below.
  4. Ticker REQUIRED - Also known as the ticker symbol, it is an arrangement of characters representing a particular security listed on an exchange or otherwise traded publicly. This is NOT required for cash transactions.
  5. Units REQUIRED - The number of security units of the transaction. Not required for cash transactions.
  6. Price REQUIRED - The value of a single unit of the security in the transaction. Not required for cash and security transfer transactions.
  7. Fees Optional - The cost of the transaction. This item can apply to all investment transactions. Defaults to a zero value when not specified.
  8. Total REQUIRED - The aggregate net value of the transaction. All investment transactions require this field.
  9. Memo Optional - A description of the transaction limited to 256 characters including spaces. Defaults to nothing.
  10. ID Optional - The unique transaction ID for the transaction in an account. If you do not specify one (recommended), by default a unique ID will be generated from the transaction date sequenced by the transaction's position in the file.
  11. Currency Optional - ISO 4217 three letter currency code denoting the currency in which the transaction value was made. This defaults to the system's currency setting.
  12. Rate Optional - The exchange rate for the transaction currency to the account primary currency. Default is a rate of 1 which assumes the currency to be default primary currency.
  13. CUSIP SEDOL ISIN Optional - A unique security identifier. This is an optional field and you can use any one of these security ID's (your choice may be influenced by which identification your PFM supports if you depend on the PFM to update security prices). Default is a program generated unique identifier. NOTE: The default value is merely a place holder, please update security details via the Securities' dialog box before saving to OFX.
  14. Security Name Name Optional - The name associated with the security ticker. Only one of these fields if included. Defaults to the Ticker.

The values for Units, Price, Fees, Total and Rate should be in the system number format to be correctly parsed by the script. In the save vein, the values for Date, Trade Date, Activity Date, Settle Date and Transaction Date MUST be in any system date format to be correctly parsed otherwise the entire transaction will not be parsed (in the case of the date strings).


Supported transactions types.

The program supports all investment / brokerage transactions that are supported by the OFX specification. Investment transaction actions are preceded by the investment type, e.g for a stock buy the action is Stock - Buy and for a mutual fund buy Mutual Fund - Buy. QIF files do not have support for options transactions.

  1. Investment Buys - for Stocks, Debt(bonds), Mutual Funds and Options.
  2. Investment Sells - for Stocks, Debt(bonds), Mutual Funds and Options.
  3. Investment Positions - for Stocks, Debt(bonds), Mutual Funds and Options.
  4. Cash transactions - Deposits, Withdrawals, Journal Cash and Margin Interest.
  5. Journal Securities* - for Stocks, Debt(bonds), Mutual Funds and Options.
  6. Capital gains - for Stocks, Debt(bonds), Mutual Funds and Options.
  7. Return of Capital - for Stocks, Debt(bonds), Mutual Funds and Options.
  8. Re Investment - Dividend, Interest and Miscellaneous for Stocks, Debt and Mutual Funds.
  9. Investment Expense(s) and Income - for Stocks, Debt(bonds), Mutual Funds and, with the exception of income, Options.
  10. Investment Transfer(s) - for Stocks, Debt(bonds), Mutual Funds and Options.
  11. Investment Split(s) - for Stocks and Mutual Funds only.
Currently supported Investment CSV files.

The table below lists the currently supported files. Use the Generic script if your brokerage is not listed, and be sure to read the the layout expected by the script. To get a script written for your brokerage, send a sample file inclusing as many transaction types as possible to: support[@]icreateofx[.]co[.]uk

Allan Gray
BMO Investorline
BNY Mellon
CIBC
Charles Schwabb
Crypto
CryptoCom
Custom1
Daily Access
Dodge And Cox
FI TEK
Fidelity Investments
Fidelity UK
Fidelity US
Foreign and Colonial
Hargreaves And Lansdown
PenSys
Pension Plan Services
Pershing
QIF File
Rabobank Direct
Retirement Strategies
Robinhood
Scottrade
Scottrade Advisors
Stock Market Eye
TD Ameritrade
TDTrustCanada
Transamerica
U.S Bank
Vanguard