iCreateOFX Investment User Guide
A description of the sections is detailed below in the Program usage guide. Next to the usage guide are the different dialogs that you will encounter using the program, i.e The Scripts' Dialog, The Actions' Dialog and The Securities' Dialog.
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.
The Scripts Dialog
The Top Menu
The top menu hosts the settings' menu items for:
All the data in the above dialogs is persisted to the next session.
- Downloads - This invokes the Scripts' Dialog to enable you add, remove or update scripts.
- Action Map - This invokes the Actions' Dialog to enable you to map actions in a file to the program's actions.
- Security Details - this dialog enables you to change the values of securities and import new securities into the program.
- The Help menu item to invoke this help message.
- The Script Stack
This is where all the scripts are listed. Choose the script(s) you require from the Scripts Dialog and then selecting Show (to install) or Hide (to remove) a script from the the stack.
- The File Settings
This contains the file settings to use for the OFX and QIF files generated.
For files containing multiple currencies, QIF files do NOT support this and therefore a separate file will be generated for each currency. OFX Files support multiple currencies, however, since most files do not contain exchange rates, please ensure that you set the rate applicable to each transaction before writing the OFX file.
By default, an account number of 1234567890 and your system's default currency are set. These values can be set to your chosen values and will be persisted. The last entered account number and currency after writing an OFX file will persist as the default value when the script is next used to load a file.
The Save Menu
Use this to write your OFX and / or QIF file(s). If the Auto Start check-box next to the respective button is enabled, then selecting it will result in the program starting the associated PFM on your system.
This also contains the Option to clear the grid and show options' columns. In addition, whenever an option column is selected, you will get a prompt that describes the currently selected transaction option.
The Transactions' Grid
The grid contains 16 columns containing data relating to each transaction. Not all the values in columns are required for each transaction, and where this is the case they will have a gray background. The columns are:
Using the program would thus entail installing your respective script (from the Downloads dialog), selecting the script from the script stack, choosing the CSV or QIF file from the resultant dialog, ensuring you have no Totals highlighted in red, and finally writing your OFX or QIF file.
- The Trade and Settle dates - If your transaction file does not contain a settle date, then it is assumed to be the trade date. Trade Date is REQUIRED for all transactions.
- Action - This represents the action of the transaction. The program supports ALL Investment actions per OFX specification. This is REQUIRED for all transactions. Select Not Applicable for transaction to omit.
- Ticker - This is the short identification symbol of the security. Not all Investment actions require this value, e.g cash / banking transactions, however, all investment related cash transaction should have a value for ticker.
- FITID - This is a unique identifier for each transaction. Most brokers do not include this value in their CSV or QIF downloads, and therefore, the program will automatically generate an ID for each transaction without one. This is REQUIRED for all transactions.
- Units, Price, Fees and Total - These relate to the fundamentals of each transaction. The program currently expects to aggregate Fees with commissions. The Total column will flag for errors in computation by highlighting the respective cell in RED. Hovering over or selecting the cell will display the error. Please ensure you correct these errors before proceeding to write your file since it will result in a corrupt OFX or QIF file. For values that are within the margin of error but not exact, the program will highlight the Total in yellow. These are mostly rounding anomalies and you can proceed to write OFX files but should change the values to exact amount(s) for QIF files. The Total column is REQUIRED for all transactions.
- The Memo - This is simply descriptive text, either supplied by the broker in the CSV file, or generated by the program. This is NOT required for any transaction.
- Currency and FX rate columns - These will only be available if you load a file with multiple currencies. Please note that the rate defaults to 1 and that you have to enter the respective rate for each transaction, where these are not included in the file, for multiple currency files. Single currency files will not trigger this option, thus the values in these columns are NOT required.
- Option column - Some transactions do require extra settings and this column provides an area to set those options. Where the value(s) need to be set, selecting the cell will trigger a description on the Save Menu. Where availed, this is a REQUIRED value.
- Security and Funding Acc.(ount) - For most investment transactions (not all) these are the sub-accounts within the Investment accounts in your PFM. One is the default account where securities are kept and the other is where all funding is drawn from / to. These are REQUIRED for all investment related transactions.
- The dtExpire - This relates solely to Options Securities and represents the Date of Expiration for the Option in the transaction.
The Scripts Dialog's grid has four columns of interest.
By default, some scripts can not be removed from the stack, and these include the QIF and Generic scripts.
The Actoins Dialog
- The Installed column
This contains the version number of the installed scripts with your installation. The program ships with all available scripts at the time of publishing.
- The Available column
When the dialog is loaded, the program automatically (tries to) check(s) for available scripts from our servers and lists the server version here. If the server and installed versions are the same, the entire row is shown in italics on a gray background.
- The Check-box(es) column
This option is disabled if the installed and available versions are the same, in which case, you can only show or hide the script in the script stack. Select the respective check-box to install or update the script. The installation process will start as soon as you select the OK button.
- The Show / Hide column
For installed scripts, clicking on the button will either show or hide the script in the script stack.
The Action(s) Dialog has three areas.
The Security Details' Dialog
- The Script Name selector:
Use this selector to choose the script whose actions you want to view and / or edit. When the dialog is opened automatically on file load, the option to select a different script will be dis-abled and you will only be able to edit the actions of the invoking script.
- The File Action:
This field is NOT editable and is populated from the respective script file. The field lists all the actions that are not mapped to the program's actions. e.g some brokers use "Buy" for any purchase, whereas the program distinguishes a stock buy from a mutual fund buy with "Stock - Buy" and "Mutual Fund - Buy" action respectively.
The Action area:
These are the actions supported by the program and are divided into two main categories:
An un-classified action defaults to "Not Applicable". The program will not include transactions classified as such when writing OFX or QIF files.
- Security Actions - These are actions that are related to securities and the security type is prepended, i.e Debt, Mutual Fund, Option and Stock.
- Cash Actions - These DO NOT relate to securities and are: Cash Deposit, Cash Withdrawal, Journal Cash and Margin Interest.
There are two areas to the securities' dialog.
Note that when importing stocks the security Ticker should be exactly the same as it appears in a transactions' file (including case) in order to have them matched; a new security will otherwise be created.
- The Grid
This consists of the Ticker, Name, CUSIP, Price and Price date. The Ticker and Price and Price date details are collected from your transactons' file when you open it. Some brokerages have more details in the transactions' file including the stock name and CUSIP /SEDOL. If this information is available, then it will be collated to the stock details, however, in cases where this is not the case, especially for the CUSIP /SEDOL value, the program will generate a sequential ID to for the stock. With regard to the Price and Price date, the program will persist the latest price by date. All values can be edited with the exception of the Ticker value.
- Import Stocks
This option provides a means to import a list of stocks into the program as well as to update any existing stocks. The format expected is in the form: Ticker,Name,CUSIP,Price,Date (though at a minimum the first two columns are adequate). The import routine relies on matching the Ticker with any values already in the database to update any values, else a new entry is added to the database. Additionally, where the routine can not parse the date in the file, and the file contains a Price column, then the date will be assumed to be the date the import is made.Imports can also be achieved via a QIF file with a header line: !Type:Prices (refer to the QIF specification for layout).
Currently supported Investment files.
|Dodge And Cox|
|Foreign and Colonial|
|Hargreaves And Lansdown|
|Pension Plan Services|
|Stock Market Eye|