de_ynab

# DeYnab

Convert YNAB CSV exports to files that [hledger](https://hledger.org) 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`:

```elixir
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](https://plaintextaccounting.org/FAQ#what-are-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:

```elixir
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:

```elixir
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:

```elixir
# 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.

```elixir
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.

```bash
# 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](#configuration).

## Command-line usage

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

```bash
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](#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](#configuration): a `config/config.exs`
in the repository checkout, or a `--accounts my-accounts.exs` file that evaluates to a map:

```elixir
# 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):

```bash
# 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:

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

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

See the [hledger manual](https://hledger.org/hledger.html) for the rest.

## How the register is translated

Rows go through these steps, in order:

1. **Transfers are paired** (see below).
2. Each transfer gets its target account as the Category.
3. Check numbers become the hledger code, rendered in parentheses (`(1234) Some Payee`).
4. Account and category names are normalized.
5. Amount is computed as Inflow minus Outflow (with the configured currency symbol).
6. Rows with no Category get a counter account (see below).
7. Income posting dates are added.
8. The YNAB Cleared flag becomes the hledger status.
9. Zero-amount rows are dropped.
10. Split rows are merged.
11. 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

```bash
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.