Wiki
Clone wikihleofxquotes / csv2ofx - To convert CSV to OFX (for Checking / Saving and Credit Card)
csv2ofx - To convert CSV to OFX (Checking/Saving and Credit Card)
Tool for converting CSV to OFX file
Features
- Generate valid FITID such the same transactions can be matched.
- Generate strict OFX file (per spec). Adhere to field length limitation, escaping invalid characters.
- Able to deal with multiple amount format such as $12.34, $(12.34), --12.34 ...
Prerequisites
- You will need Java installed on your machine (https://adoptium.net/)
- CVS files
- for USAA: (see this link on how to download USAA CVS files)
Contact
If you have questions or concerns about this tool, you can contact me at hleofxquotes at gmail dot com.
Download
From the Downloads link. Look for latest version with a link with name looking like this 'hleOfxQuotes-csv2ofx-Build_nnnn_m.zip'
Install
- Unzip to get a directory
# example - adjust fileName as appropriated $ unzip hleOfxQuotes-csv2ofx-Build_20210206_1.zip Archive: hleOfxQuotes-csv2ofx-Build_20210206_1.zip creating: hleOfxQuotes-csv2ofx-Build_20210206_1/ inflating: hleOfxQuotes-csv2ofx-Build_20210206_1/hleOfxQuotes-csv2ofx-Build_20210206_1-exec.jar
- Go to the new folder
cd hleOfxQuotes-csv2ofx-Build_20210206_1
To run in GUI mode
- Double-click on the jar file
- Or
java -jar hleOfxQuotes-csv2ofx-Build_20210206_1-exec.jar
The account file are stored in directory '.csv2ofx' in your home directory (*.yaml files).
To setup
- Check the account configuration files (the *.yaml files) located in directory '.csv2ofx' in your home directory. At minimum, edit those files and set the appropriate values for 'acctId' and 'bankId'.
IMPORTANT: See also section 'Setting that you MUST change' down further in this document.
To convert
- In 'Accounts' window, select an account
- Drag-and-drop a *.csv file onto the 'Accounts' window
- If there are no errors, the *.ofx file content will be shown, you can then click on the 'Open' button to open it.
To add additional account files, just add a new *.yaml file. You can copy from an existing one to start. Stop and restart tool to pick up the new changes.
Errors
The common cause for errors is that a "wrong" account file is being used. The account file has configuration on how to parse the CSV file (how much columns, which column has date? amount? ...) So if there is mis-match, parsing error will occur.
To run in command-line mode
You will need to provide 3 arguments
- A CSV fileName (input)
- A configuration file for information that specific to you such as your accountId and so on (more on this later)
- A OFX fileName (output)
This tool will read the CSV and configration file and will then generate the OFX file.
A sample run
[hle@hungle-laptop7 target]$ java -Dloader.main=app.Csv2OfxCmd -jar hleOfxQuotes-csv2ofx-Build_20210206_1-exec.jar /tmp/bk_download_cc.csv /tmp/bk_download_cc.yaml /tmp/bk_download_cc.ofx Error: Unable to access jarfile hleOfxQuotes-csv2ofx-Build_20210206_1-exec.jar [hle@hungle-laptop7 target]$ java -Dloader.main=app.Csv2OfxCmd -jar hleOfxQuotes-csv2ofx-Build_20210210_3-exec.jar /tmp/bk_download_cc.csv /tmp/bk_download_cc.yaml /tmp/bk_download_cc.ofx 2021-02-10 08:18:02,799 [INFO ] [main] Csv2OfxCmd2.main(Csv2OfxCmd2.java:45) – csvFile=/tmp/bk_download_cc.csv 2021-02-10 08:18:02,805 [INFO ] [main] Csv2OfxCmd2.main(Csv2OfxCmd2.java:46) – configFile=/tmp/bk_download_cc.yaml 2021-02-10 08:18:02,805 [INFO ] [main] Csv2OfxCmd2.main(Csv2OfxCmd2.java:47) – ofxFile=/tmp/bk_download_cc.ofx 2021-02-10 08:18:03,072 [INFO ] [main] Csv2OfxCmd2.main(Csv2OfxCmd2.java:59) – acctType=CC 2021-02-10 08:18:03,075 [INFO ] [main] Csv2OfxCmd2.main(Csv2OfxCmd2.java:61) – template=/templates/csv2ofx-cc.vm ... 2021-02-10 08:18:03,131 [INFO ] [main] Csv2OfxCmd2.main(Csv2OfxCmd2.java:75) – Parsed 12 transactions. 2021-02-10 08:18:03,132 [INFO ] [main] Csv2OfxCmd2.main(Csv2OfxCmd2.java:76) – RESULT ofxFile=/tmp/bk_download_cc.ofx 2021-02-10 08:18:03,132 [INFO ] [main] Csv2OfxCmd2.main(Csv2OfxCmd2.java:77) – < DONE
In above example
- /tmp/bk_download_cc.csv: is the CSV file. This file is what you downloaded from your bank.
- /tmp/bk_download_cc.yaml: is the configuration file. See the next section 'Configuration file'
- /tmp/bk_download_cc.ofx: is the name of the OFX file (what this tools generated)
Configuration file
Here are two sample configuration files for USAA
- USAA Checking bk_download_checking.yaml - for Checking CSV file with a header and 6 columns looking like this
Date,Description,Original Description,Category,Amount,Status 2021-02-01,"o9aweurwa Transfer","o9aweurwa e7783e03 CR",Transfer,2500.00,Posted
- USAA Credit Card bk_download_cc.yaml - for Credit Card CSV file with NO header and 7 columns looking like this
,,"2/03/2021",,"JDKSFJSDFJ eba1eb72-ad0f-411b-9880-e00ff6466eb3 ","",17.99
Setting that you MUST change
Your bank info. You will need to set value of your bank OFX 'fid' and 'org'. You can find them at http://www.ofxhome.com. For example, for USAA
## OFX info # FI (Financial Institution) info # For example: http://www.ofxhome.com/index.php/institution/view/483 fid : "24591" org : "USAA"
Your account info. If the account for the CSV data is checking/saving, set
acctType : "CHECKING" # or acctType : "SAVING" acctId : "123456789" # For CHECKING/SAVING, you also need to specify the bankId bankdId : "987654321"
If the account for the CSV data is credit card, set
acctType : "CC" acctId : "123456789"
# Name of the account, use by the UI to display name: USAA Checking
Security/data concern
This tool does NOT read any other files than the two you specified
- the input CSV file
- the input configuration file
This tool does NOT write to any other files than the one you specified
- the output OFX file
This tool does not make any network connection.
This tool does not make change to your personal finance data. The tool just generate an OFX file which is text file. You can open and visually inspect the output. No data will be modified until you import the OFX using your personal finance software. And any changes will be done by your personal finance software and not this tool.
Advance configuration - CSV parsing
The following settings control how to parse the input CSV
# CSV - Does the CSV file has a header line? csvHeader: "True" # CSV - Where we can find the value of Date,Description and Amount csvColumnDescriptors: "Date,Description,,,Amount," # CSV - what is the date pattern for the Date field? #https://docs.oracle.com/javase/10/docs/api/java/text/SimpleDateFormat.html csvDatePattern: "yyyy-MM-dd"
Value of 'csvColumnDescriptors' specifies (which column) to find value for
- Date
- Description
- Amount
For example, given
csvColumnDescriptors: "Date,Description,,,Amount,"
- There are 6 columns
- 'Date' can be found at column-1
- 'Description' can be found at column-2
- 'Amount' can be found at column-5
- Data from column-3,4 and 6 are ignored.
Value of 'csvDatePattern' specifies how to parse the Date column.
Link to this page
The shorten URL for this page is http://bit.ly/3cPajDZ
Updated