Skip to content

Latest commit

 

History

History
487 lines (338 loc) · 16.2 KB

README.md

File metadata and controls

487 lines (338 loc) · 16.2 KB
Raw

icsv2ledger

This is a command-line utility to convert CSV files of transactions, such as you might download from an online banking service, into the format used by John Wiegley's excellent Ledger system.

The 'i' stands for interactive. Here's what it's designed to do:

  • For each CSV input you give it, it creates a Ledger output.

  • As it runs through the entries in the CSV file, it tries to guess which Ledger account and Ledger payee they should be posted against, based on your historical decisions.

  • It shows you which account, payee, (optionally tags), it's going to use, giving you the opportunity to change it. If it got it right, just hit return.

  • When you are entering an account/payee name, you get auto-completion if you press the Tab key. You don't have to match the start of the name, so typing 'foo[tab]' inserts 'Expenses:Food'.

  • When you are entering a tag, you get auto-completion if you press the Tab key. If you would like to remove a tag from the list of tags just prefix the tag with a minus '-'. When you are done with the tags just hit return.

  • It stores the history of auto-completion in a mapping file, for converting transaction descriptions onto payee/account(/tag) names. You can also edit this by hand. It can load this in future as the basis of its guesses.

  • The payee/account names used in the auto-completion are read both from the mapping file and, optionally, from a Ledger file or files. It runs ledger payees and ledger accounts to get the names. It can also scan a seperate ledger file containing 'account' definitions. The tags are only read from the mapping file.

Synopsis

icsv2ledger.py [options] -a STR [infile [outfile]]

Arguments summary

infile                input filename or stdin in CSV syntax
outfile               output filename or stdout in Ledger syntax

Options summary

Options can either be used from command line or in configuration file. --account is a mandatory option on command line. --config-file and --help are only usable from command line.

--account STR, -a STR
                      ledger account used as source
--clear-screen, -C    clear screen for every transaction
--cleared-character {*,!, }
                      character to clear a transaction
--config-file FILE, -c FILE
                      configuration file
--credit INT          CSV column number matching credit amount
--csv-date-format STR
                      date format in CSV input file
--csv-decimal-comma   comma as decimal separator in the CSV
--currency STR        the currency of amounts
--date INT            CSV column number matching date
--debit INT           CSV column number matching debit amount
--default-expense STR
                      ledger account used as destination
--delimiter           CSV delimiter
--desc STR            CSV column number matching description
--effective-date INT  CSV column number matching effective date
--encoding STR        text encoding of CSV input file
--incremental         append output as transactions are processed
--ledger-date-format STR
                      date format for ledger output file
--ledger-decimal-comma
                      comma as decimal separator in the ledger
--ledger-file FILE, -l FILE
                      ledger file where to read payees/accounts
--mapping-file FILE   file which holds the mappings
--accounts-file FILE  file which holds a list of allowed accounts
--quiet, -q           do not prompt if account can be deduced
--reverse             reverse the order of entries in the CSV file
--skip-dupes          skip transactions that have already been imported
--skip-lines INT      number of lines to skip from CSV file
--skip-older-than     skip entries more than X days old
--tags, -t            prompt for transaction tags
--template-file FILE  file which holds the template
-h, --help            show this help message and exit

Options

Options can either be used from command line or in configuration file. From command line the syntax is --long-option VALUE with dashes, and in configuration file the syntax is long_option=VALUE with underscores.

There is an order of precedence for options. First hard coded defaults (documented below) are used, overridden by options from configuration file if any, and finally overridden by options from command line if any.

--account STR, -a STR

is the ledger account used as source for ledger transactions. This is the only mandatory option on command line. Default is Assets:Bank:Current.

When used from command line, it is both the section name in configuration file and the account name. Account name could then be overridden in configuration file. See section Configuration file example where SAV from command line is overridden with account=Assets:Bank:Savings Account.

--clear-screen, -C

will clear the screen before every prompting. Default is False.

--cleared-character {*,!, }

is the character to mark a transaction as cleared. Ledger possible value are * or ! or . Default is *.

--config-file FILE, -c FILE

is configuration filename.

The file used will be first found in that order:

  1. Filename given on command line with --config-file,
  2. .icsv2ledgerrc in current directory,
  3. .icsv2ledgerrc in home directory.

--credit INT

is the CSV file column which contains credit amounts. The first column in the CSV file is numbered 1. Default is 4.

See also documentation of --debit option for negating amounts.

--csv-date-format STR

describes the date format in the CSV file.

See the python documentation for the various format codes supported in this expression.

--csv-decimal-comma

will assume that number use the comma ',' as decimal in the csv.

If the --ledger-decimal-comma option is not set, comma will be converted into dot.

--currency STR

is the currency of amounts. Default is locale currency_symbol.

--date INT

is the CSV file column which contains the transaction date. Default is 1.

--debit INT

is the CSV file column which contains debit amounts. Default is 3.

If your bank writes all amounts in same column, credits as positive amounts and debits as negative amounts, then set credit to correct column and debit to 0.

If your bank writes debits as a negative number and you want to negate the amount, then use --debit=-3. It will negate amounts in column 3 and use them as debits amounts.

--default-expense STR

is the default ledger account used as destination (generally an expense) for ledger transactions. Default is Expenses:Unknown.

--delimiter STR

is the CSV delimiter character. Default is ,. Special characters can be expressed using standard escape sequences, such as \t for a tab.

--desc STR

is the CSV file column which contains the transaction description as supplied by the bank. Default is 2.

This description will be used as the input for determining which payee and account to use by the auto-completion.

It is possible to provide a comma separated list of CSV column indices (like desc=2,5) that will concatenate fields in order to form a unique description. That enriched description will serve as base for the mapping.

--effective-date INT

is the CSV column number which contains the date to be used as the effective date. Default is 0. Use of this option currently requires a template file. See section Transaction template file.

--encoding STR

is the text encoding of the CSV input file. Default is utf-8. The encoding should be specified if the CSV file contains non-ASCII characters (typically in the transaction description) in an encoding other than UTF-8.

--incremental

appends output as transactions are processed. The default flow is to process all CSV input and then output the result. When --incremental is specified, output is written after every transaction. This allows one to stop (ctrl-c) and restart to progressively process a CSV file (--skip-dupes is a useful companion option).

--ledger-date-format STR

describes the date format to be used when creating ledger entries. If --ledger-date-format is defined, then --csv-date-format must also be defined to be able to convert dates. If --ledger-date-format is not defined, then the date from CSV file is reused.

See the python documentation for the various format codes supported in this expression.

--ledger-decimal-comma

will assume that number should be print using the comma ',' as decimal when creating ledger entries.

If the --csv-decimal-comma option is not set, dot will be converted into comma.

--ledger-file FILE, -l FILE

is ledger filename where to get the list of already defined accounts and payees.

The file used will be first found in that order:

  1. Filename given on command line with --ledger-file,
  2. .ledger in current directory,
  3. .ledger in home directory.

--mapping-file FILE

is the file which holds the mapping between the description and the payee/account names to use. See section Mapping file.

The file used will be first found in that order:

  1. Filename given on command line with --mapping-file,
  2. .icsv2ledgerrc-mapping in current directory,
  3. .icsv2ledgerrc-mapping in home directory.

Warning: the file must exists so that mappings are added to the file.

--accounts-file FILE

is an optional file that can be used to hold a master list of all account names, and will be used as a source for account names. See section Accounts file.

The file used will be first found in that order:

  1. Filename given on command line with --accounts-file,
  2. .icsv2ledgerrc-accounts in current directory,
  3. .icsv2ledgerrc-accounts in home directory.

--quiet, -q

will not prompt if account can be deduced from existing mapping. Default is False.

--reverse

will print ledger entries in reverse of their order in the CSV file.

--skip-dupes

will skip transactions if the exact CSV already appears as a ; CSV: ... comment in the current ledgerfile (which means your output template will need this comment). This can help if you download statements without using a precise date range. A useful pattern is to include CSV comments for both "sides" of a transaction if you download from multiple sources that resolve to a single transaction (e.g. paying a credit card from checking).

--skip-lines INT

is the number of lines to skip from the beginning of the CSV file. Default is 1.

--tags, -t

will interactively prompt for transaction tags. Default is False.

The normal behavior is for one description to prompt for payee and account, and store this in mapping file. By setting this option, the description can also be mapped to additional tags.

At the prompt: fill a tagname and press Enter key as many times as you need tags. Remove an existing tag by preceding it with minus, like -tagname. When finished, press Enter key on an empty line.

This --tags option only prompt for tags. You have to add ; {tags} in your template to make tags appear in generated Ledger transactions.

--template-file FILE

is template filename, which contains the template to use when generating ledger transactions. See section Transaction template file.

The file used will be first found in that order:

  1. Filename given on command line with --template-file,
  2. .icsv2ledgerrc-template in current directory,
  3. .icsv2ledgerrc-template in home directory.

--skip-older-than DAYS

will not process any entries in the CSV file which are more than DAYS old. If DAYS is negative then the entire CSV file is processed.

Example

The below command will use the [SAV] section of the configuration file to process the CSV file.

./icsv2ledger.py -a SAV file.csv

Configuration file example

The following is an example configuration file where you can save your icsv2ledger's options.

A configuration file typically contains one section per bank account to be imported. In the below example there are two bank accounts: SAV and CHQ.

[SAV]
account=Assets:Bank:Savings Account
currency=AUD
date=1
csv_date_format=%d-%b-%y
ledger_date_format=%Y/%m/%d
desc=6
credit=2
debit=-1
mapping_file=mappings.SAV

[SAV_addons]
beneficiary=3
purpose=4


[CHQ]
account=Assets:Bank:Cheque Account
currency=AUD
date=1
csv_date_format=%d/%m/%Y
ledger_date_format=%Y/%m/%d
desc=2
credit=3
debit=4
mapping_file=mappings.CHQ
skip_lines=0

Addons

In section Configuration file example the SAV_addons section enables to save a CSV field value to a tag value. Those tags can then be used, for the SAV account, in your own transaction template:

 ; purpose: {addon_purpose}
 ; beneficiary: {addon_beneficiary}

Mapping file

A typical mapping file might look like:

/SAFEWAY/,Safeway,Expenses:Food
/ITUNES.*/,iTunes,Expenses:Entertainment
THE WRESTLERS INN,"The ""Wrestlers"" Inn",Expenses:Food
/MACY'S/,"Macy's, Inc.",Expenses:Food
MY COMPANY 1234,My Company,Income:Salary
MY COMPANY 1234,My Company 1234,Income:Salary:Tips

It uses simple string-matching by default, but if you put a '/' at the start and end of a string it will instead be interpreted as a regular expression.

Mapping is based on your historical decisions. Later matching entries overwrite earlier ones, that is in example above MY COMPANY 1234 will be mapped to My Company 1234 and Income:Salary:Tips.

Accounts File

To prevent inconsistencies it is possible to user ledger --strict mode, along with a file that defines a list of allowable accounts. (See the ledger 3 manual, section 4.6 'Keeping it Consistent')

The accounts file should look like:

account Expenses:Food
account Expenses:Enternainment
account Income:Salary
account Income:Salary:Tips

All other lines will be ignored so you if you have a single ledger file that has account definitions mixed throughout it, it is safe (although potentially time consuming) to pass it to icsv2ledger as the accounts-file.

Transaction template file

The built-in default template is as follows:

{date} {cleared_character} {payee}
    ; MD5Sum: {md5sum}
    ; CSV: {csv}
    {debit_account:<60}    {debit_currency} {debit}
    {credit_account:<60}    {credit_currency} {credit}

Details on how to format the template are found in the Format Specification Mini-Language.

The values that can be used are: date, effective_date, cleared_character, payee, transaction_index, debit_account, debit_currency, debit, credit_account, credit_currency, credit, tags, md5sum, csv. And also the addon tags like addon_xxxx. See section Addons.

Contributing

Feedback/contributions most welcome.

Known Issues

On Mac OS X when CSV is passed via stdin to icsv2ledger you may not see any prompts offering defaults and asking for your input. This is due to an inferior readline library (libedit) installed by default on Mac OS X. Install a proper readline library and you're good to go.

% sudo easy_install readline

On Windows the default Python installation does not provide a readline library. The pyreadline library provides native python emulation of this functionality and must be installed to run this utility.

Author

icsv2ledger was originally created by Quentin Stafford-Fraser but includes valuable contributions from many others, including Peter Ross, Alexis Hildebrandt, Thierry and Eric Entzel.

See also

ledger, hledger