Metadata-Version: 2.4
Name: sqlite_export_for_ynab
Version: 2.0.0
Summary: SQLite Export for YNAB - Export YNAB Data to SQLite
Home-page: https://github.com/mxr/sqlite-export-for-ynab
Author: Max R
Author-email: maxr@outlook.com
License: MIT
Keywords: ynab,sqlite,sql,budget,plan,cli
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: Implementation :: CPython
Classifier: Programming Language :: Python :: Implementation :: PyPy
Requires-Python: >=3.12
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: aiohttp>=3
Requires-Dist: tldm
Dynamic: license-file

# sqlite-export-for-ynab

[![pre-commit.ci status](https://results.pre-commit.ci/badge/github/mxr/sqlite-export-for-ynab/main.svg)](https://results.pre-commit.ci/latest/github/mxr/sqlite-export-for-ynab/main) [![codecov](https://codecov.io/github/mxr/sqlite-export-for-ynab/graph/badge.svg?token=NVCP6RDKSH)](https://codecov.io/github/mxr/sqlite-export-for-ynab)

SQLite Export for YNAB - Export YNAB Budget Data to SQLite

## What This Does

Export all your [YNAB](https://ynab.com/) plans to a local [SQLite](https://www.sqlite.org/) DB. Then you can query your data with any tools compatible with SQLite.

## Installation

```console
$ pip install sqlite-export-for-ynab
```

## Usage

### CLI

Provision a [YNAB Personal Access Token](https://api.ynab.com/#personal-access-tokens) and save it as an environment variable.

```console
$ export YNAB_PERSONAL_ACCESS_TOKEN="..."
```

Run the tool from the terminal to download your plans:

```console
$ sqlite-export-for-ynab
```

Running it again will pull only data that changed since the last pull (this is done with [Delta Requests](https://api.ynab.com/#deltas)). If you want to wipe the DB and pull all data again use the `--full-refresh` flag.

You can specify the DB path with the following options
1. The `--db` flag.
1. The `XDG_DATA_HOME` variable (see the [XDG Base Directory Specification](https://specifications.freedesktop.org/basedir-spec/latest/index.html)). In that case the DB is saved in `"${XDG_DATA_HOME}"/sqlite-export-for-ynab/db.sqlite`.
1. If neither is set, the DB is saved in `~/.local/share/sqlite-export-for-ynab/db.sqlite`.

### Library

The library exposes the package `sqlite_export_for_ynab` and two functions - `default_db_path` and `sync`. You can use them as follows:

```python
import asyncio
import os

from sqlite_export_for_ynab import default_db_path
from sqlite_export_for_ynab import sync

db = default_db_path()
token = os.environ["YNAB_PERSONAL_ACCESS_TOKEN"]
full_refresh = False

asyncio.run(sync(token, db, full_refresh))
```

## Relations

The relations are defined in [create-relations.sql](sqlite_export_for_ynab/ddl/create-relations.sql). They are 1:1 with [YNAB's OpenAPI Spec](https://api.ynab.com/papi/open_api_spec.yaml) (ex: transactions, accounts, etc) with some additions:

1. Some objects are pulled out into their own tables so they can be more cleanly modeled in SQLite (ex: subtransactions, loan account periodic values).
1. Foreign keys are added as needed (ex: plan ID, transaction ID) so data across plans remains separate.
1. Two new views called `flat_transactions` and `scheduled_flat_transactions`. These allow you to query split and non-split transactions easily, without needing to also query `subtransactions` and `scheduled_subtransactions` respectively. They also include fields to improve quality of life (ex: `amount_major` to convert from [YNAB's milliunits](https://api.ynab.com/#formats) to [major units](https://en.wikipedia.org/wiki/ISO_4217) i.e. dollars) and filter out deleted transactions/subtransactions.

## Querying

You can issue queries with typical SQLite tools. *`sqlite-export-for-ynab` deliberately does not implement a SQL REPL.*

### Sample Queries

To get the top 5 payees by spending per plan, you could do:

```sql
WITH
ranked_payees AS (
    SELECT
        pl."name" AS plan_name
        , t.payee_name AS payee
        , SUM(t.amount_major) AS net_spent
        , ROW_NUMBER() OVER (
            PARTITION BY
                pl.id
            ORDER BY
                SUM(t.amount) ASC
        ) AS rnk
    FROM
        flat_transactions AS t
    INNER JOIN plans AS pl
        ON t.plan_id = pl.id
    WHERE
        t.payee_name != 'Starting Balance'
        AND t.transfer_account_id IS NULL
    GROUP BY
        pl.id
        , t.payee_id
)

SELECT
    plan_name
    , payee
    , net_spent
FROM
    ranked_payees
WHERE
    rnk <= 5
ORDER BY
    plan_name ASC
    , net_spent DESC
;
```

To get duplicate payees, or payees with no transactions:

```sql
SELECT DISTINCT
    pl."name" AS "plan"
    , dupes."name" AS payee
FROM (
    SELECT DISTINCT
        p.plan_id
        , p."name"
    FROM payees AS p
    LEFT JOIN flat_transactions AS ft
        ON
            p.plan_id = ft.plan_id
            AND p.id = ft.payee_id
    LEFT JOIN scheduled_flat_transactions AS sft
        ON
            p.plan_id = sft.plan_id
            AND p.id = sft.payee_id
    WHERE
        TRUE
        AND ft.payee_id IS NULL
        AND sft.payee_id IS NULL
        AND p.transfer_account_id IS NULL
        AND p."name" != 'Reconciliation Balance Adjustment'
        AND p."name" != 'Manual Balance Adjustment'
        AND NOT p.deleted

    UNION ALL

    SELECT
        plan_id
        , "name"
    FROM payees
    WHERE NOT deleted
    GROUP BY plan_id, "name"
    HAVING COUNT(*) > 1

) AS dupes
INNER JOIN plans AS pl
    ON dupes.plan_id = pl.id
ORDER BY "plan", payee
;
```

To count the spend for a category (ex: "Apps") between this month and the next 11 months (inclusive):

```sql
SELECT
    plan_id
    , SUM(amount_major) AS amount_major
FROM (
    SELECT
        plan_id
        , amount_major
    FROM flat_transactions
    WHERE
        category_name = 'Apps'
        AND SUBSTR(`date`, 1, 7) = SUBSTR(DATE(), 1, 7)
    UNION ALL
    SELECT
        plan_id
        , amount_major * (
            CASE
                WHEN frequency = 'monthly' THEN 11
                ELSE 1 -- assumes yearly
            END
        ) AS amount_major
    FROM scheduled_flat_transactions
    WHERE
        category_name = 'Apps'
        AND SUBSTR(date_next, 1, 7) < SUBSTR(DATE('now', '+1 year'), 1, 7)
)
;
```
