CSV2OFX Convert Help File
© Copyright 2019 MoneyThumb LLC
- CSV2OFX Convert
- Appendix A
CSV2OFX Convert
Getting Started
CSV2OFX Convert is a single step financial data translator to convert bank, credit card, and brokerage transactions into industry standard OFX format files.
Use CSV2OFX Convert to import transaction data when the data comes from a spreadsheet program such as Microsoft Excel® or Google Docs® or was downloaded from your financial institution in CSV (spreadsheet) format.
To get started first set your destination account information and date formats with the button.
Then either drag and drop your CSV file into the converter drop zone or select the button to choose a file to convert. This will bring up a standard file chooser to select your OFX download. There are two action buttons, plus the cancel button, at the bottom of the file chooser. Use the button to preview how .ofx files will convert, and to assign and verify which column is which before doing the import. Once you have previewed a file, then use the button to do one-step conversion of other files directly to OFX format suitable for input into your application.
Whenever opening a file from a different bank or that has a different style, always first use Preview to verify the column setup. Use the pull-down list at the bottom of each column to select the correct type of information in that column. Be sure to select one column, one column, and either one column or both and columns. If you have a balance column, the column selection should be blank (to ignore it). You can also choose which transactions to convert. See more about Preview Mode below.
Verify that credits to the account are positive and debits are negative. If they are switched, then select the checkbox for and the amounts will be correctly output to your .ofx file.
Select at the bottom of the Preview Screen to finish the conversion and create your .ofx file, suitable for input into your financial application. Run subsequent conversions of OFX statements from the same bank with select a file and thento create your .ofx file in a single step.
Installation
- Microsoft Windows® full install
- Download CSV2OFX.exe for Windows, save the file to your computer, and run the installation program by double clicking the file.
- If you do not have have Java installed it will be automatically downloaded during the installation.
- Mac OS X® full install
- Download CSV2OFX.dmg for Mac OS X, save the file to your computer. Locate the file in the download area, open it by double clicking, then and run the installer.app by double clicking it.
- If you do not have have Java installed it will be automatically downloaded during the installation.
Entering License Information
Enter the license by copying the license string (CTRL-C) from the confirmation e-mail and pasting it (CTRL-V) into the converter license dialog. To enter the license string manually from within the program select the License button, and paste (or type) the full license code into the dialog.
On Microsoft Windows, you can copy the license file csv2ofx.lic from the product confirmation e-mail to the same folder where you installed CSV2OFX Convert - i.e. C:\Program Files (x86)\MoneyThumb\csv2ofx Convert.
After you enter your license, your license email will be shown in the program title bar, and in .
Preparation
There are two things to do before running CSV2OFX Convert, although the second one may be optional depending on your finance application.
- Create a CSV file.
This can be downloaded from a bank or brokerage web site or be manually created in Excel and saved as a CSV (Comma Delimited) file in Excel. See Appendix A for guidelines on creating a file from scratch.
- Get the account number of the account into which you want to import transactions. If you are creating a new account, then any number will suffice. If you wish to import transactions into an existing account, then most financial applications will match up the account numbers, and you will want to import into the correct account.
Running CSV2OFX Convert
On Windows or Mac OS X, double click the CSV2OFX Convert icon on your desktop.
You may also run CSV2OFX Convert from the Windows Start Menu, or run CSV2OFX.exe on Windows or CSV2OFX Convert.app on Mac OS X.
Use the button to bring up the Settings Wizard or the full Settings dialog. The Wizard will ask questions that cover the vast majority of conversions. The Settings Dialog also contains a few additional options. You can switch back and forth between the Wizard and the full dialog.
Setting Account Info
First use the pull-down menu to select the correct type for the .ofx file - Bank, Credit Card, or Investment. There are also three additional pieces of account information that may be inserted into the OFX file when it is created.
OFX files are required to have account information. All files require an account number and bank accounts also require a bank routing number. If you don't want to save your accounts numbers for security reasons, then you can skip entering this this information. If you do provide your account number to be inserted into the OFX file, then your finance application can use that number to automatically determine which account to import into. If you are always importing into the same account, then CSV2OFX Convert will save the information from session to session, so you do not have to re-enter it. Note that CSV2OFX Convert does not access the Internet at all, so any information entered is only saved on your computer, and is not sent over other web or to any other computers.
To determine the account number to use in the OFX file, CSV2OFX Convert will look in the following locations, in order.
-
-
- The account header in the CSV file, typically the first line in the file. This method is best if you are converting data for different accounts on a regular basis, and don't want to always re-enter the numbers. To specify this information in the CSV file, use an account header of the form:
!Type:accounttype;accountname|accountnumber
- Values from the dialog window (see above). This method is best if you are doing a one time conversion, or are always converting the same account. Once again, if you are concerned about entering your account number, then don't, and either manually edit the OFX file after it is created, or match up accounts when importing.
- The CSV file name, if it is a number without any letters.
- As a last resort, CSV2OFX Convert will use an arbitrary default number for the routing number and account number. You will then have to manually match accounts when reading the OFX file into your finance application.
- The account header in the CSV file, typically the first line in the file. This method is best if you are converting data for different accounts on a regular basis, and don't want to always re-enter the numbers. To specify this information in the CSV file, use an account header of the form:
-
The bank account routing number is required by OFX for bank accounts (but not for credit cards or investment accounts). However, it is not actually used by most finance applications, so if you don't specify one, CSV2OFX Convert will insert a default value and it should be accepted.
Lastly, the currency needs to be specified. US Dollars are the initial setting, use the drop down to select a different currency.
Date Formats
CSV2OFX Convert can read dates either in US format (month-day-year) or European format (day-month-year). Use the Settings menu to select the date format that is used in your CSV file. If your dates have the month name or abbreviation rather than a number, then this setting is not applicable. Note that there is no need to specify a date format for OFX files.
Positive and Negative Charges
Normally bank statements will have charges as negative numbers and payments as positive numbers. That is what Quicken expects. Many credit card companies switch things so that charges are positive - showing an increase in your balance - and payments are negative. Use the dialog to select if this is the type of data in your CSV file.
CSV Terminator
CSV files (an acronym for Comma Separated Values) normally use commas to separate the values. However in European and other countries where commas are used to designate the decimal portion (cents) of currency values, CSV file are created with a semi-colon. This option specifies which type of terminator is used in the CSV file being read.
Assigned Column Names
Column names can be preassigned to your conversions. Whenever you use Preview Mode, the column names are saved and automatically assigned to subsequent conversions. See the description of Preview Mode, in the following section. The current column names are displayed in the text box in this section.
You can also read predefined column setting from a CSV format file. CSV2OFX Convert is shipped with a number of presets that facilitate conversion from popular CSV download sites, such as PayPal. To read presets select the button, and use the open file dialog to open a csv file with the column names. A preset CSV file is just a single line of text with the column names separated by commas. You can also save the current displayed column names to your own preset file with
Select the checkbox for to enable or disable the column settings.
Splitting output transactions
When reading transactions from payment systems such as PayPal, the transaction is often composed of a gross amount, a transaction fee, resulting in a net amount. The Separate option specifies whether split transactions such as these create one transaction in the OFX file, or whether each split generates a new transaction in the OFX file. When transactions are split, your Finance Application import will create one transaction that is the received payment amount, and another transaction that is the transaction fee. This enables you to match up payments with invoices, and to track transactions fees in a separate account. If the transaction is not split, such a transaction will be imported as a single transaction with the net amount. See more about specifying split amounts in the description of Preview Mode, in the following section.
Converting the CSV File
There are two ways to identify the CSV file to convert:
-
- Select the file in your operating system file browser (Windows Explorer, Mac Finder), and then drag it into the converter "Drop Zone"
or
- Select the button in CSV2OFX Convert to bring up the file chooser and select the file to convert.
CSV2OFX Convert can be run in two modes - Preview Mode and Express Mode. If you are just getting started, then use Preview Mode. If you have run CSV2OFX Convert previously on a similar file and are sure that the columns are correct, then it's faster to use Express Mode.
Preview Mode
Select the button and this will bring up the file chooser dialog. Navigate to the folder containing the .csv file, select the file, and then select at the bottom of the dialog.
This will bring up a preview window that displays the contents of your CSV file. First select the correct account type at the top of the window. At the bottom of each column is a selector that contains the name of the data in that column. It may have already been set correctly by CSV2OFX Convert based on column headings in the CSV file. If it's not correct, use the pull-down to select the correct type of data. Each type can only be used in one column, so types that have already been used will be grayed out. If you have many columns, you can increase the width of the columns of interest by going to the header row and dragging the column separator to increase the width of the column, and of course drag a corner of the window to enlarge it as well.
Some credit card files also have the the signs of amounts reversed so that credits have a minus sign. In this case the checkbox for should be selected. This will ensure that debits and credits are correctly labeled in the OFX file, and imported correctly into your finance application. The checkbox for will hide all the columns that do not currently have a name assigned. This is very useful for files that come from PayPal or Stripe that have too many columns for them all to be readable.
The only required fields are the , and depending on whether the transaction amounts are in one column or two, either or and . If your data has an column that has positive amounts for both credits and debits (i.e. mint.com) then you also need to select a column that has the type - credit/debit or CR/DB.
When the option is set under , you can specify which columns contain amounts that are to be split. Additional column names will be displayed for , , and . For these columns, if there is an amount present, it will generate its own transaction, with a payee of the column name. You can also define your own split names by using the column name. This will prompt you for the name to used for amounts in this column. You can force the amounts in a column to be either positive or negative amounts by specifying a plus or minus sign before the split name. For example, a transaction fee column can be split out with: Split<-Transaction Fee>
To create a Memo that combines two different columns choose for the the first part of the memo, and for the second part. The text from the two columns will be combined into a single Memo, with a space between the two text strings.
When the column names are correct select at the bottom of the preview window. CSV2OFX Convert will run, giving some statistics on how many lines were processed and create a OFX file with the same name. If a OFX file with that name already exists you will be prompted to overwrite it.
Preview Mode column settings will be automatically remembered, and will apply to subsequent Express Mode conversions. To clear Preview Mode, either run a different file in Preview mode or use the button and then uncheck the checkbox for .
Express Mode
Express mode can be used for all conversions, although it is highly recommended that whenever converting a csv file from a new source, you should first use Preview Mode to make sure the column setup is correct. Select the button and this will bring up the file chooser dialog. Navigate to the folder containing your input file, and select at the bottom of the dialog
CSV2OFX Convert will run, giving some statistics on how many lines were processed and create a OFX file with the same name. If a OFX file with that name already exists you will be prompted to overwrite it.
Processing payment systems downloads (PayPal, Square, Stripe...) with Presets
Payment systems such as PayPal, Square, Stripe create .csv files which contain transactions which contain additional information beyond the net amount of the sale. Most commonly the downloaded .csv file will contain columns with the gross amount of the sale, the transaction fee, and the net amount of the sale. It may also contain a column with the refund amount, if any. When importing this information into double entry accounting systems, it is often desirable to have this information as multiple transactions. For example the gross amount of the sale would become one transaction, and the transaction fee would be a second transaction. This allows the two amounts to go to different accounts in your accounting system, and also allows the the gross amount to be matched up with a customer invoice.
Presets in CSV2OFX Convert make this conversion very simple.
1. Select and read in the Preset file for the payment system processor with . CSV2OFX Convert is shipped with a number of standard preset files for common payment systems and credit cards.
2. You will also likely want to select the checkbox for so that individual transactions are created for each amount.
3. Select and run the conversion with . You will automatically have the correct columns mapped, and create a .ofx file. If you wish to verify that the column names are correct, you can instead the conversion from the Open File dialog before creating the .ofx file.
You can also create your own Preset files if you wish to have different names for the transaction fee, refund, or have a payment system that has additional fields. You can save the column settings that are displayed in the dialog with . You can also create preset files with a spreadsheet program such as Microsoft Excel that creates .csv files. The file format is just a single line with the column names, separated by commas.
Trouble Shooting
CSV2OFX Convert Error: No account header found, Using default account header...
If the CSV2OFX Convert log windows has the error, "No account header found, Using default account header", then it likely means that you are trying to read a CSV file whose first line does not label what each column contains. Some credit card companies create CSV files like this. Either Use Preview Mode to specify what is in each column, or use Notepad or Excel or any text editor to insert a line at the beginning of the CSV file that labels each column. For example, for AMEX the line would be
Date,Number,Amount,Payee,Memo
CSV2OFX Convert Error: Unknown section header type
If the CSV2OFX Convert log windows has the error: "Unknown section header type", then it means that there is no information on what type of account is being processed, or the type is not recognized. Either set the account type in , run in Preview Mode to define the account type , or edit the CSV file to add a line starting with !Type. See Appendix A for a full list of types supported by !Type.
CSV2OFX Convert Error: Incomplete header...
There are missing columns which are needed to process the CSV file. The date might be missing, there might be a credits column without a debits column, or similar types of missing data. Review the column names in Preview Mode, or edit the CSV file to label the columns.
Missing Information
If not all transaction information is being imported into your application, then verify that the CSV file has headers that correspond to the names in Appendix A. Although CSV2OFX Convert recognizes a wide variety of alternative names, the program or web site that created the CSV file might be using some names for column headers that are not being recognized. Review the column settings in Preview Mode, or edit the CSV file to correct the column names.
Switched Information
If after importing the OFX file into your financial application and information is switched (i.e. the Payee is what you expected for some other field) then the headers in the CSV file may be misunderstood. Either use Preview Mode to correct the column description, or look at the beginning of the conversion log to see what columns are being used for what information, and edit the headers in the CSV file accordingly.
If the amounts are switched (i.e. credit card charges are showing up as positive rather than negative) then use the menu to select and rerun the conversion.
Converting Security Names in Investment Transactions
OFX files are supposed to define stocks and other securities using the security name and the security CUSIP. The CUSIP is a 9 digit field that uniquely identifies the security. Finding a CUSIP for a security can be tricky, as there is currently no public Internet search for CUSIP's. Some brokerages do put the CUSIP as a column in the CSV file they create with transactions. CUSIPS for mutual funds and stocks can usually be found at the web site of the mutual fund company or the investor relations page of the company, but it can be hard to find. For well known stocks, you can often find them with a Google search for "symbol cusip". If you cannot find a CUSIP, CSV2OFX Convert will give a warning and create a dummy one, but importing that file could cause problems with future imports from downloads that have the correct CUSIP.
Assuming that one way or another you determined the CUSIP for the securities you want to import, CSV2OFX Convert can read them either by using an additional column in the transaction list that contains the CUSIP, or by using a Security List CSV file and utilizing the Security List to match up the security name, stock symbol and CUSIP.
You can create a Security List by using Excel or other spreadsheet editor to manually create a Security List and saving it as a CSV file.
To use the Security List, run CSV2OFX Convert and first read the security list CSV file, and then process the investment transactions CSV file, without exiting CSV2OFX Convert. When done in this sequence CSV2OFX Convert will remember the security names and do the substitution automatically. To create a security List in Excel or another spreadsheet program, it should look like this:
!Type:Security |
||
Security Name |
Cusip |
Symbol |
Apple Inc. |
037833100 |
AAPL |
Google Inc |
38259P508 |
GOOG |
You can also insert the security list at the beginning of the transaction CSV file as a separate section and only have to read one file. In this case, just insert one blank row between the security list and the start of the investment account transactions. See Appendix A for more detail
Multiple Accounts in a singe file
Because an OFX file can only contain transactions from a single financial institution, CSV2OFX Convert will only process the first account found in a CSV file when creating a OFX file. However, CSV2OFX Convert will process a CSV file containing both a security list and an investment account, merging that into a single OFX file.
Saving the CSV2OFX Convert Log
After CSV2OFX Convert has run, you may wish to save the log information to a file. Select the Save Log button. This will bring up a File Save dialog. Simply specify a file name and select Save.
To clear the log information select the button.
Appendix A
Spreadsheet conventions
If you want to create or edit the CSV file, the following is is a complete description of the conventions used by CSV2OFX Convert.
By way of example, a simple bank account might look like this:
!Type:Bank;My bank account^23456|1111222233334444 |
||||
Date |
Number |
Payee |
Amount |
Memo |
5/1/2011 |
|
Opening Balance |
0 |
|
5/2/2011 |
|
Deposit |
100 |
From David |
5/10/2011 |
ATM |
ATM Withdrawal |
50 |
|
5/18/2011 |
INT |
Interest Paid This Period |
0.01 |
|
First Line
The first line for an account must have the type of the account, and may optionally have the account name, FID, and account number. The punctuation is a semi colon after the account type, an up-arrow after the account name, and a vertical bar after the FID.
!Type:accounttype;accountname|accountnumber
The is one of the following:
-
- Bank - checking and savings accounts
- CCard - credit card accounts
- Invst - brokerage and investment accounts
- Security - security list
The is the name of the account being created, and the accountnumber is the account number that your financial application can use to match accounts.
If the first line does not have this type of information, then CSV2OFX Convert will look at the column headers to determine the account type, and read the rest of the information from the Account Info dialog.
Columns
All data must be in columns, and the columns should have the column name at the top of the column. Normally the column headers are in the second line of the spreadsheet, but CSV2OFX Convert will search until a header row is found. The order of the columns is not important, and only the columns in bold are required.
Many alternate column names are used by various financial institutions, and most of them are recognized by CSV2OFX Convert. If your CSV file does not have column headers, or uses different names, then running under Preview Mode is the easiest way to assign the column names for your CSV file.
The following is the list of 'standard' column names and their meanings for different account types.
Bank and Credit Card Accounts
-
- Date - transaction date
- Number - check number (for bank accounts only)
- Payee - payee or merchant
- Payee & Memo - payee or merchant, also copied to the start of the memo field
- Amount - Amount of the transaction in $ or other currency. Alternatively a credit and a debit column can be used
- Credit - credit amounts
- Debit - debit amounts
- Type - transaction type, such as credit or debit
- Transaction ID - the unique transaction ID
- Memo - memo or description of the transaction
- Memo Add-on - addition description to be added to the memo
- Gross Amount - when splitting transactions, gross amount of transaction
- Transaction Fee - when splitting transactions, the transaction fee charged
- Refund Amount - when splitting transactions, amount of refund
When the split transaction option is set, split transactions will be created by using a column header of
Split<Category>
The Category will become the payee name for output transactions after the first one created for this split transaction. The category name can be prefaced by a + or - to ignore the sign of the value in that column, and either add or subtract the value.
Investment Accounts
-
- Date - transaction date
- Transaction ID - the unique transaction ID
- Action - investment transaction. Supported actions are:
- Buy, Sell, Div, CGShort, CGMid, CGLong, IntInc, ReinvDiv, ReinvSh, ReinvMd, ReinvLg, ReinvInt,
- ShrsIn, ShrsOut, XIn, XOut, Cash, Contrib, MiscExp, MiscInc, MargInt, RtrnCap, ShtSell, CvrShrt,
- Exercise, Expire, StkSplit, Grant, Vest, Reminder, AdjShBal, Deposit, OnlPmt
- Note that the Memo contents will be searched for an Action, if no Action column is present.
- Security Name - stock name, mutual fund name, or other security name
- Symbol - stock or mutual fund symbol
- Cuisp - 9 digit security identifier
- Quantity - number of shares in the transaction
- Price - price per share
- Commission - commission
- Total - total amount (for a buy or sell should be quantity * price + commission)
- Memo - memo or description of the transaction.
- Memo Add-on - addition description to be added to the memo
Depending on the transaction type some or all of quantity, price, and total may be required for any particular transaction. For example all three are needed for a Buy or Sell, but only quantity is needed to transfer stock in or out of an account.
In addition, there are many different ways that brokerages create CSV files, and sometimes required information is only available in the memo column. CSV2OFX Convert uses the contents of the action, the security, and the memo to determine the complete transaction. If insufficient information is found, then that transaction will be skipped, and the log will describe why.
Security List
-
- Security Name - Name of the security
- Symbol - Stock symbol
- Cuisp - 9 digit security identifier
- Type - valid types are: Bond, CD, Emp. Stock Opt, ESPP, Market Index, Money Market, Mutual Fund, Option, Other, Stock, US Savings Bond
Saving a CSV file from within Microsoft Excel
- Do a,
- At the bottom of the File Save Dialog select the file to be .
- Select .
- When you get a popup warning that some features in the file may not be compatible with CSV (Comma Delimited) format, select to keep this format and the CSV file will be saved.
Saving a CSV file from within Google Docs
- Do a, and then .
- This will start a file download. Select to download.
- This will bring up a file chooser dialog to save your file. Just select an appropriate folder and select and the CSV file will be downloaded.