sqlite-export-for-ynab


Namesqlite-export-for-ynab JSON
Version 1.4.1 PyPI version JSON
download
home_pagehttps://github.com/mxr/sqlite-export-for-ynab
SummarySQLite Export for YNAB - Export YNAB Budget Data to SQLite
upload_time2025-07-24 02:29:16
maintainerNone
docs_urlNone
authorMax R
requires_python>=3.12
licenseMIT
keywords ynab sqlite sql budget cli
VCS
bugtrack_url
requirements No requirements were recorded.
Travis-CI No Travis.
coveralls test coverage No coveralls.
            # 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 your [YNAB](https://ynab.com/) budget to a local [SQLite](https://www.sqlite.org/) DB. Then you can query your budget 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 budget:

```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: budget ID, transaction ID) so data across budgets 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 budget, you could do:

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

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

To get duplicate payees, or payees with no transactions:

```sql
WITH txns AS (
    SELECT DISTINCT
        budget_id
        , payee_id
    FROM
        flat_transactions

    UNION ALL

    SELECT DISTINCT
        budget_id
        , payee_id
    FROM
        scheduled_flat_transactions
)

, p AS (
    SELECT
        budget_id
        , id
        , name
    FROM
        payees
    WHERE
        NOT deleted
        AND name != 'Reconciliation Balance Adjustment'
)

SELECT DISTINCT
    budget
    , payee
FROM (
    SELECT
        b.name AS budget
        , p.name AS payee
    FROM
        p
    INNER JOIN budgets AS b
        ON p.budget_id = b.id
    LEFT JOIN txns AS t
        ON p.id = t.payee_id AND p.budget_id = t.budget_id
    WHERE
        t.payee_id IS NULL

    UNION ALL

    SELECT
        b.name AS budget
        , p.name AS payee
    FROM
        p
    INNER JOIN budgets AS b
        ON p.budget_id = b.id
    GROUP BY budget, payee
    HAVING
        COUNT(*) > 1

)
ORDER BY budget, payee
;
```

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

```sql
SELECT
    budget_id
    , SUM(amount_major) AS amount_major
FROM (
    SELECT
        budget_id
        , amount_major
    FROM flat_transactions
    WHERE
        category_name = 'Apps'
        AND SUBSTR(`date`, 1, 7) = SUBSTR(DATE(), 1, 7)
    UNION ALL
    SELECT
        budget_id
        , amount_major
    FROM scheduled_flat_transactions
    WHERE
        category_name = 'Apps'
        AND SUBSTR(date_next, 1, 7) < SUBSTR(DATE('now', '+1 year'), 1, 7)
)
;
```

            

Raw data

            {
    "_id": null,
    "home_page": "https://github.com/mxr/sqlite-export-for-ynab",
    "name": "sqlite-export-for-ynab",
    "maintainer": null,
    "docs_url": null,
    "requires_python": ">=3.12",
    "maintainer_email": null,
    "keywords": "ynab, sqlite, sql, budget, cli",
    "author": "Max R",
    "author_email": "maxr@outlook.com",
    "download_url": "https://files.pythonhosted.org/packages/35/a3/e86176b73b32e2de79d346a872e9c60de5f366fd29a22779a9db204303ee/sqlite_export_for_ynab-1.4.1.tar.gz",
    "platform": null,
    "description": "# sqlite-export-for-ynab\n\n[![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)\n\nSQLite Export for YNAB - Export YNAB Budget Data to SQLite\n\n## What This Does\n\nExport your [YNAB](https://ynab.com/) budget to a local [SQLite](https://www.sqlite.org/) DB. Then you can query your budget with any tools compatible with SQLite.\n\n## Installation\n\n```console\n$ pip install sqlite-export-for-ynab\n```\n\n## Usage\n\n### CLI\n\nProvision a [YNAB Personal Access Token](https://api.ynab.com/#personal-access-tokens) and save it as an environment variable.\n\n```console\n$ export YNAB_PERSONAL_ACCESS_TOKEN=\"...\"\n```\n\nRun the tool from the terminal to download your budget:\n\n```console\n$ sqlite-export-for-ynab\n```\n\nRunning 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.\n\nYou can specify the DB path with the following options\n1. The `--db` flag.\n1. 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`.\n1. If neither is set, the DB is saved in `~/.local/share/sqlite-export-for-ynab/db.sqlite`.\n\n### Library\n\nThe library exposes the package `sqlite_export_for_ynab` and two functions - `default_db_path` and `sync`. You can use them as follows:\n\n```python\nimport asyncio\nimport os\n\nfrom sqlite_export_for_ynab import default_db_path\nfrom sqlite_export_for_ynab import sync\n\ndb = default_db_path()\ntoken = os.environ[\"YNAB_PERSONAL_ACCESS_TOKEN\"]\nfull_refresh = False\n\nasyncio.run(sync(token, db, full_refresh))\n```\n\n## Relations\n\nThe 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:\n\n1. Some objects are pulled out into their own tables so they can be more cleanly modeled in SQLite (ex: subtransactions, loan account periodic values).\n1. Foreign keys are added as needed (ex: budget ID, transaction ID) so data across budgets remains separate.\n1. 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.\n\n## Querying\n\nYou can issue queries with typical SQLite tools. *`sqlite-export-for-ynab` deliberately does not implement a SQL REPL.*\n\n### Sample Queries\n\nTo get the top 5 payees by spending per budget, you could do:\n\n```sql\nWITH\nranked_payees AS (\n    SELECT\n        b.name AS budget_name\n        , t.payee_name AS payee\n        , SUM(t.amount_major) AS net_spent\n        , ROW_NUMBER() OVER (\n            PARTITION BY\n                b.id\n            ORDER BY\n                SUM(t.amount) ASC\n        ) AS rnk\n    FROM\n        flat_transactions AS t\n    INNER JOIN budgets AS b\n        ON t.budget_id = b.id\n    WHERE\n        t.payee_name != 'Starting Balance'\n        AND t.transfer_account_id IS NULL\n    GROUP BY\n        b.id\n        , t.payee_id\n)\n\nSELECT\n    budget_name\n    , payee\n    , net_spent\nFROM\n    ranked_payees\nWHERE\n    rnk <= 5\nORDER BY\n    budget_name ASC\n    , net_spent DESC\n;\n```\n\nTo get duplicate payees, or payees with no transactions:\n\n```sql\nWITH txns AS (\n    SELECT DISTINCT\n        budget_id\n        , payee_id\n    FROM\n        flat_transactions\n\n    UNION ALL\n\n    SELECT DISTINCT\n        budget_id\n        , payee_id\n    FROM\n        scheduled_flat_transactions\n)\n\n, p AS (\n    SELECT\n        budget_id\n        , id\n        , name\n    FROM\n        payees\n    WHERE\n        NOT deleted\n        AND name != 'Reconciliation Balance Adjustment'\n)\n\nSELECT DISTINCT\n    budget\n    , payee\nFROM (\n    SELECT\n        b.name AS budget\n        , p.name AS payee\n    FROM\n        p\n    INNER JOIN budgets AS b\n        ON p.budget_id = b.id\n    LEFT JOIN txns AS t\n        ON p.id = t.payee_id AND p.budget_id = t.budget_id\n    WHERE\n        t.payee_id IS NULL\n\n    UNION ALL\n\n    SELECT\n        b.name AS budget\n        , p.name AS payee\n    FROM\n        p\n    INNER JOIN budgets AS b\n        ON p.budget_id = b.id\n    GROUP BY budget, payee\n    HAVING\n        COUNT(*) > 1\n\n)\nORDER BY budget, payee\n;\n```\n\nTo count the spend for a category (ex: \"Apps\") between this month and the next 11 months (inclusive):\n\n```sql\nSELECT\n    budget_id\n    , SUM(amount_major) AS amount_major\nFROM (\n    SELECT\n        budget_id\n        , amount_major\n    FROM flat_transactions\n    WHERE\n        category_name = 'Apps'\n        AND SUBSTR(`date`, 1, 7) = SUBSTR(DATE(), 1, 7)\n    UNION ALL\n    SELECT\n        budget_id\n        , amount_major\n    FROM scheduled_flat_transactions\n    WHERE\n        category_name = 'Apps'\n        AND SUBSTR(date_next, 1, 7) < SUBSTR(DATE('now', '+1 year'), 1, 7)\n)\n;\n```\n",
    "bugtrack_url": null,
    "license": "MIT",
    "summary": "SQLite Export for YNAB - Export YNAB Budget Data to SQLite",
    "version": "1.4.1",
    "project_urls": {
        "Homepage": "https://github.com/mxr/sqlite-export-for-ynab"
    },
    "split_keywords": [
        "ynab",
        " sqlite",
        " sql",
        " budget",
        " cli"
    ],
    "urls": [
        {
            "comment_text": null,
            "digests": {
                "blake2b_256": "4d08c345f881e1a2f646a436eab200569b6365bdb76c8c02d4a68f7da9729e14",
                "md5": "7776b938a208ae3db2713fc63d5c920e",
                "sha256": "8a56308da712a9518bc6fe210b2a00375434b592bbe71b66edf093dc860bf0e2"
            },
            "downloads": -1,
            "filename": "sqlite_export_for_ynab-1.4.1-py2.py3-none-any.whl",
            "has_sig": false,
            "md5_digest": "7776b938a208ae3db2713fc63d5c920e",
            "packagetype": "bdist_wheel",
            "python_version": "py2.py3",
            "requires_python": ">=3.12",
            "size": 15974,
            "upload_time": "2025-07-24T02:29:15",
            "upload_time_iso_8601": "2025-07-24T02:29:15.671063Z",
            "url": "https://files.pythonhosted.org/packages/4d/08/c345f881e1a2f646a436eab200569b6365bdb76c8c02d4a68f7da9729e14/sqlite_export_for_ynab-1.4.1-py2.py3-none-any.whl",
            "yanked": false,
            "yanked_reason": null
        },
        {
            "comment_text": null,
            "digests": {
                "blake2b_256": "35a3e86176b73b32e2de79d346a872e9c60de5f366fd29a22779a9db204303ee",
                "md5": "553675ab0ec0b1819549d4abea4da59c",
                "sha256": "eb5cba54e2adde1bab00b47987beac7ab802eb4a372ea4f54ac484b60a4414e8"
            },
            "downloads": -1,
            "filename": "sqlite_export_for_ynab-1.4.1.tar.gz",
            "has_sig": false,
            "md5_digest": "553675ab0ec0b1819549d4abea4da59c",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": ">=3.12",
            "size": 16269,
            "upload_time": "2025-07-24T02:29:16",
            "upload_time_iso_8601": "2025-07-24T02:29:16.488608Z",
            "url": "https://files.pythonhosted.org/packages/35/a3/e86176b73b32e2de79d346a872e9c60de5f366fd29a22779a9db204303ee/sqlite_export_for_ynab-1.4.1.tar.gz",
            "yanked": false,
            "yanked_reason": null
        }
    ],
    "upload_time": "2025-07-24 02:29:16",
    "github": true,
    "gitlab": false,
    "bitbucket": false,
    "codeberg": false,
    "github_user": "mxr",
    "github_project": "sqlite-export-for-ynab",
    "travis_ci": false,
    "coveralls": false,
    "github_actions": true,
    "lcname": "sqlite-export-for-ynab"
}
        
Elapsed time: 0.71045s