# sqlite-export-for-ynab
[](https://results.pre-commit.ci/latest/github/mxr/sqlite-export-for-ynab/main) [](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[](https://results.pre-commit.ci/latest/github/mxr/sqlite-export-for-ynab/main) [](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"
}
JSON
