1. hleofxquotes-team Avatar hleofxquotes-team
  2. open
  3. hleofxquotes

Wiki

Clone wiki

hleofxquotes / csv2ofx - To convert CSV to OFX (for Checking / Saving and Credit Card)

View History

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"
You should also change (or add) a new key called 'name'. This will be used by the UI to display the account name in the account list.

# 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