DeYnab

Convert YNAB CSV exports to files that hledger can read.

YNAB is not a double-entry system, so the program rebuilds the other side of every entry. Read further for details on how to run it, how each kind of YNAB row is translated, and how to run the tests.

Features

Proper double-entry transactions
YNAB records a transfer twice (once per account) and our conversion pairs the two rows to a single transaction.
Account-name mapping to your own hledger account structure
Budget export
- "Income for the next month" is still budgeted correctly in hledger.
  We'll use a date: posting comment so it counts toward the month it was budgeted in. We use hledger's periodic transactions for budget-vs-actual reporting.
- Budget-aware transfers
  A transfer that counts against a budget category becomes a virtual posting on the same transaction.
Split transactions merge into one balanced transaction (limit: 20 splits)
Uncategorized rows get a counter account
We'll infer the best counter account to use (interest, equity adjustments, income from the payee...)
Check numbers, state, and color flags map to hledger features
They become codes ((1234)), state flags (*/!) and flag: tags on the transaction comment.
Locale support.
Input amounts may carry any currency symbol anywhere ($12.48, 12,48 €, 1.234,56ден) with US or EU separators; the output symbol and the export date order are configurable.
Hidden categories stay grouped under Expenses:Hidden Categories:...

Non-features

These exist in YNAB but are not part of a YNAB CSV export, so no conversion can recover them:

Budget notes and category notes (YNAB does not put notes in the CSV export)
Scheduled / repeating transactions (these don't appear in a register export either)
Goals and target amounts on categories.
Attachments and links between transactions.
Account numbers and institution details are not exported (arguably a good thing).

Installation

If using this library in another Elixir application, add the dependency to your mix.exs:

defp deps do
  [{:de_ynab, "~> 0.6"}]
end

Configuration

Account names in YNAB are short names which do not indicate which of the five top level accounts they belong to. You'll need to indicate whether each account is an Asset, Liability, or Expense.

This mapping is done by DeYnab.proper_account_names/0. Without your configuration, the program only knows generic names (e.g. "Cash" → "Assets:Cash") and won't translate anything it doesn't recognize.

There are two ways to supply your mapping.

Callers using the library as a dependency can pass it as the optional third argument:

account_names = %{"Checking" => "Assets:Banks:Checking", "Chase Visa" => "Liabilities:cc:Chase"}
DeYnab.parse_ynab_csv("ynab-register.csv", "hl-register.csv", account_names)
DeYnab.parse_ynab_budget("ynab-budget.csv", "hl-budget.journal", account_names)

Command-line users can instead configure it in config/config.exs, which is used whenever no map is passed:

config :de_ynab, account_names: %{
  "Checking" => "Assets:Banks:Checking",
  "Savings" => "Assets:Banks:Savings",
  "Chase Visa" => "Liabilities:cc:Chase",
  "Car Loan" => "Liabilities:Loans:Car"
}

This same info is used to normalize category names and the target accounts of transfers (in the budget export), you'll want to include your loan and credit-card accounts even though they appear as "categories" in YNAB.

You may alsp specify settings to control currency and dates:

# the symbol written to the output amounts (default "$"; amounts in the YNAB
# export may use any symbol on input, e.g. "12,48 €" or "1.234,56ден")
config :de_ynab, currency_symbol: "€"

# how YNAB export dates are written: :month_first ("01/15/2024", default) or
# :day_first ("15/01/2024"); used for the next-month income posting dates
config :de_ynab, date_format: :day_first

If you change date_format, also edit the date-format line in hl-register.csv.rules so hledger parses the dates the same way (the command-line script does this for you).

Usage

Two exports are used: the register (every transaction) and the budget (one row per category per month). Their names below are examples; pass whatever files you export.

iex -S mix
iex> DeYnab.parse_ynab_csv("ynab-register.csv", "hl-register.csv")
iex> DeYnab.parse_ynab_budget("ynab-budget.csv", "hl-budget.journal")

The register is converted to an intermediate CSV that hledger reads through a rules file. The budget is converted straight to an hledger journal of periodic transactions.

# convert the CSV to a journal; hledger picks up the bundled hl-register.csv.rules
# automatically because it sits next to the CSV, named after it
hledger print -f hl-register.csv > hl-register.journal

export LEDGER_FILE=./hl-register.journal
hledger bal assets liabilities

# budget vs. actual, with the budget journal included
hledger -f hl-register.journal -f hl-budget.journal bal -M --budget -H expenses

If you write the output CSV under a different name or location, copy hl-register.csv.rules next to it, named after it (hledger looks for <data file>.rules). The CSV columns and the rules file's fields line must match exactly (a test checks this). The --hledger flag of the command-line script does the copy for you.

Account names are mapped in DeYnab.proper_account_names/0; see Configuration.

Command-line usage

The ynab_to_hledger.exs script wraps both conversions for the command line, assuming you have Elixir isntalled locally:

mix deps.get          # once; the script needs the compiled dependencies
mix compile

# register only
elixir ynab_to_hledger.exs --register ynab-register.csv

# register + budget, with an accounts map, and run hledger for you
elixir ynab_to_hledger.exs \
  --register ynab-register.csv \
  --budget ynab-budget.csv \
  --accounts my-accounts.exs \
  --hledger

Non-USD exports: pass --currency € and --date-format day-first (or set the same options in config/config.exs; see Configuration). The --hledger flag will patch the copied rules file's date-format for you.

Run elixir ynab_to_hledger.exs --help for the full option list. Outputs are written next to the inputs (<input>.hl.csv for the register, <input>.hl.journal for the budget) unless you pass --out FILE. The --hledger flag copies the bundled hl-register.csv.rules next to the output CSV, named after it (hledger's requirement), and runs hledger print to produce the journal.

Accounts can come from either side of Configuration: a config/config.exs in the repository checkout, or a --accounts my-accounts.exs file that evaluates to a map:

# my-accounts.exs
%{
  "Checking" => "Assets:Banks:Checking",
  "Car Loan" => "Liabilities:Loans:Car"
}

A map passed with --accounts takes precedence over the config file.

Note: the script runs outside Mix and resolves dependencies relative to its own directory, so run it from a checkout of this repository (or point it at a compiled checkout).

Reporting

Once converted, the journal is plain hledger — every report it offers works. A few useful ones (set LEDGER_FILE=./hl-register.journal or pass -f each time):

# monthly spending per top-level category
hledger bal -M --depth 1 expenses

# average monthly spend in a category, with a running average
hledger reg -M --average "expenses:everyday:dining"

# multi-column balance by month, including the budget journal and budget goals
hledger -f hl-register.journal -f hl-budget.journal bal -M --budget -H expenses

# income statement and balance sheet
hledger incomestatement
hledger balancesheet

The conversion also adds metadata you can filter on:

# everything you flagged Red in YNAB
hledger reg tag:flag

# a transaction by its check number
hledger reg code:77

See the hledger manual for the rest.

How the register is translated

Rows go through these steps, in order:

Transfers are paired (see below).
Each transfer gets its target account as the Category.
Check numbers become the hledger code, rendered in parentheses ((1234) Some Payee).
Account and category names are normalized.
Amount is computed as Inflow minus Outflow (with the configured currency symbol).
Rows with no Category get a counter account (see below).
Income posting dates are added.
The YNAB Cleared flag becomes the hledger status.
Zero-amount rows are dropped.
Split rows are merged.
YNAB color flags become flag: <color> tags on the transaction comment (see below).

Ordinary rows

An expense row becomes a two-posting transaction: the Account on one side and Expenses:<Master Category>:<Sub Category> on the other. Income: categories post to Revenue:income:<Payee>. Starting Balance and Pre-YNAB Debt rows post to Equity:starting balances.

YNAB Cleared	hledger status
`R` (reconciled)	`*`
`C` (cleared)	`!`
`U` (uncleared)	none

Transfers

YNAB records a transfer as two rows, one in each account: same date, mirrored accounts (Transfer : B in account A, Transfer : A in account B) and opposite amounts.

The two rows are matched on date, accounts and amount, and one transaction is kept. There is no hard-coded list of which side to drop.
Identical transfers on the same day pair one-to-one.
A transfer with no counterpart in the export is kept (with a warning logged), not dropped.
The kept transaction takes the strongest status of the two halves (reconciled > cleared > uncleared), and falls back to the other half's memo if its own is empty.

Transfers that count against a budget category

YNAB lets a transfer carry a Category, so it counts against that budget category. If either half has one, the transaction gets a virtual posting in the same transaction:

2022-01-20 * Transfer : Brokerage  ; sweep
    Assets:Investments:VOO                      $13800.00
    Assets:Investments:Brokerage               $-13800.00
    (Expenses:Long Term Savings:Retirement)     $13800.00

The virtual amount is Outflow minus Inflow of the half that had the category, so a category on the inflow side gives a negative amount. An Income: category becomes (Revenue:income:...) instead of an Expenses account.

Hidden categories

YNAB exports hidden categories with the Master Category Hidden Categories and a mangled Sub Category such as Debt ` Old Car ` A35. They are kept grouped instead of being unhidden, using the clean Category as the sub account:

Expenses:Hidden Categories:Debt:Old Car

This applies to the register, split transactions, virtual postings and the budget journal. The Pre-YNAB Debt categories are the exception: they are opening balances and go to Equity:starting balances.

Split transactions

YNAB exports a split as consecutive rows whose Memo starts with (Split n/m). They are merged into one transaction: the Account carries the net amount and each split is its own posting.

2015-02-06 * Some Payee
    Assets:Checking                  $-68.58
    Expenses:House:Gas                 $8.51
    Expenses:House:Internet           $60.00
    Expenses:Everyday:Refund        $-189.63

Edge cases:

The (Split n/m) prefix is removed from the memo and the distinct memos are joined with ; .
A run ends at n == m, so back-to-back splits stay separate transactions.
A split number that does not increase, or a change of Account or Date, starts a new run.
Splits can be incomplete (siblings dropped as zero amounts, for example). Whatever is present is merged; a lone split row passes through as an ordinary row.
A payee like Employer / Transfer : Reimbursements has its transfer part stripped, and that split posts to the transfer target account.
The merged transaction takes the strongest status of its splits.
At most 20 splits per transaction are supported. More raises an error; to raise the limit change @max_splits in lib/de_ynab.ex and add matching blocks to the rules file.

Rows with no Category

A row that is not a transfer and has no Category would produce a blank Expenses:: account, so a counter account is chosen:

Account type	Money in	Money out
Liabilities	`Equity:adjustments`	`Expenses:Interest`
Investments	`Revenue:income:<Payee>`	`Equity:Investment withdrawals`
Other assets	`Revenue:income:<Payee>`	`Expenses:Uncategorized`

Money leaving an investment account (for example a refunded excess 401k contribution) is not spending. The money that reaches Checking is counted as income when it is deposited.

Income "this month" vs. "next month"

YNAB income is budgetable either this month or next (Income:Available this month or Income:Available next month). Next-month income gets a posting date on its Revenue posting, the first of the following month (December rolls into January):

2018-07-12 * Employer
    Assets:Checking                  $5249.84
    Revenue:income:Employer         $-5249.84  ; date:2018-08-01

hledger reads date: there as the posting date. This-month income needs no extra date.

Flags

A YNAB color flag (Red, Orange, …) is written to the transaction comment as an hledger tag, alongside the memo:

2018-07-12 * Employer  ; receipt; flag: Red

Filter on it from hledger: hledger reg tag:flag=Red (or just tag:flag for any flag).

Data-quality warnings

When the export contains data DeYnab doesn't expect, it logs a warning through Elixir's Logger (at warning level) and keeps going:

a row with both an Inflow and an Outflow — the difference is used as the amount;
a memo containing (Split that isn't a valid split marker — the row is treated as an ordinary row instead of a split;
a transfer whose counterpart is missing from the export — the transfer is kept as its own transaction.

How the budget is translated

parse_ynab_budget/2 writes one periodic transaction per month. Rows with a zero Budgeted amount and blank rows are skipped, and a negative Budgeted amount is kept:

~ monthly in 2020-01
    Expenses:House:Water  $20.00
    Expenses:Hidden Categories:Debt:Old Car  -$681.61
    Assets

This records what was budgeted, not YNAB's running Category Balance. To see the balance, compare cumulative budget with cumulative actual spending (--budget -H). A month with Budgeted = $0 that is spent from a carried-over balance shows as spending against a zero goal in a single-month report.

Tests

mix test                  # everything
mix test --only hledger   # just the tests that run the real hledger
mix test --exclude hledger
mix test --cover          # with coverage

The hledger tests run the real hledger against hl-register.csv.rules, and are skipped automatically if hledger is not installed.

The tests cover each step above, including the edge cases: transfer pairing (unmatched, same-day duplicates, either half carrying the category), split runs (incomplete, back-to-back, too many, differing payees), uncategorized rows, posting dates, hidden categories, color flags, data-quality warnings, and the budget journal. Two tests check that the columns written by DeYnab stay in sync with the fields line and split blocks in hl-register.csv.rules.

All test data is made up, built with the helpers in test/test_helper.exs. Do not paste rows from a real export into the tests; use generic account, payee and category names.