# 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 the data that changed since the last pull. 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` allow you to query split and non-split transactions easily, without needing to also query `subtransactions` and `scheduled_subtransactions` respectively.
## 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,
p.name AS payee,
SUM(t.amount) / -1000.0 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 payees AS p ON t.payee_id = p.id
INNER JOIN budgets AS b ON t.budget_id = b.id
WHERE
p.name != 'Starting Balance'
AND p.transfer_account_id IS NULL
AND NOT t.deleted
GROUP BY
b.id,
p.id
)
SELECT
budget_name,
payee,
net_spent
FROM
ranked_payees
WHERE
rnk <= 5
ORDER BY
budget_name ASC,
net_spent DESC;
```
To get payees with no transactions:
```sql
WITH st AS (
SELECT
budget_id,
payee_id,
MAX(NOT deleted) AS has_active_transaction
FROM
scheduled_flat_transactions
GROUP BY
budget_id,
payee_id
),
t AS (
SELECT
budget_id,
payee_id,
MAX(NOT deleted) AS has_active_transaction
FROM
flat_transactions
GROUP BY
budget_id,
payee_id
)
SELECT DISTINCT
b.name AS budget,
p.name AS payee
FROM
budgets AS b
INNER JOIN payees AS p ON b.id = p.budget_id
LEFT JOIN t
ON (
p.id = t.payee_id
AND p.budget_id = t.budget_id
)
LEFT JOIN st
ON (
p.id = st.payee_id
AND p.budget_id = st.budget_id
)
WHERE
NOT p.deleted
AND p.name != 'Reconciliation Balance Adjustment'
AND (
t.payee_id IS NULL
OR NOT t.has_active_transaction
)
AND (
st.payee_id IS NULL
OR NOT st.has_active_transaction
)
ORDER BY
budget,
payee;
```
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/64/a5/83010ef9deedf353855e7232814fadb0719e01128877961966b11f941dac/sqlite_export_for_ynab-1.0.0.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 the data that changed since the last pull. 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` allow you to query split and non-split transactions easily, without needing to also query `subtransactions` and `scheduled_subtransactions` respectively.\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 p.name AS payee,\n SUM(t.amount) / -1000.0 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 payees AS p ON t.payee_id = p.id\n INNER JOIN budgets AS b ON t.budget_id = b.id\n WHERE\n p.name != 'Starting Balance'\n AND p.transfer_account_id IS NULL\n AND NOT t.deleted\n GROUP BY\n b.id,\n p.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\nTo get payees with no transactions:\n\n```sql\nWITH st AS (\n SELECT\n budget_id,\n payee_id,\n MAX(NOT deleted) AS has_active_transaction\n FROM\n scheduled_flat_transactions\n GROUP BY\n budget_id,\n payee_id\n),\n\nt AS (\n SELECT\n budget_id,\n payee_id,\n MAX(NOT deleted) AS has_active_transaction\n FROM\n flat_transactions\n GROUP BY\n budget_id,\n payee_id\n)\n\nSELECT DISTINCT\n b.name AS budget,\n p.name AS payee\nFROM\n budgets AS b\nINNER JOIN payees AS p ON b.id = p.budget_id\nLEFT JOIN t\n ON (\n p.id = t.payee_id\n AND p.budget_id = t.budget_id\n )\nLEFT JOIN st\n ON (\n p.id = st.payee_id\n AND p.budget_id = st.budget_id\n )\nWHERE\n NOT p.deleted\n AND p.name != 'Reconciliation Balance Adjustment'\n AND (\n t.payee_id IS NULL\n OR NOT t.has_active_transaction\n )\n AND (\n st.payee_id IS NULL\n OR NOT st.has_active_transaction\n )\nORDER BY\n budget,\n payee;\n```\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "SQLite Export for YNAB - Export YNAB Budget Data to SQLite",
"version": "1.0.0",
"project_urls": {
"Homepage": "https://github.com/mxr/sqlite-export-for-ynab"
},
"split_keywords": [
"ynab",
" sqlite",
" sql",
" budget",
" cli"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "62aee2066e55de5ed49a766b35c9ac5c7714f5b876a826ede7712e5b79c4884f",
"md5": "51f2b4a6738e7ba182a7d93db132bad4",
"sha256": "5acb895ea8049d24a46cb6b827fca07e6521f00b02ee2916c506df44c2f4e650"
},
"downloads": -1,
"filename": "sqlite_export_for_ynab-1.0.0-py2.py3-none-any.whl",
"has_sig": false,
"md5_digest": "51f2b4a6738e7ba182a7d93db132bad4",
"packagetype": "bdist_wheel",
"python_version": "py2.py3",
"requires_python": ">=3.12",
"size": 14850,
"upload_time": "2025-01-26T19:42:15",
"upload_time_iso_8601": "2025-01-26T19:42:15.269865Z",
"url": "https://files.pythonhosted.org/packages/62/ae/e2066e55de5ed49a766b35c9ac5c7714f5b876a826ede7712e5b79c4884f/sqlite_export_for_ynab-1.0.0-py2.py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "64a583010ef9deedf353855e7232814fadb0719e01128877961966b11f941dac",
"md5": "385bac108c5e2c8fba5a1c85cbd375ac",
"sha256": "da7f94902ccec788d4fdc15b7ee552e01bcb5346d0e39b132f20b65e6a578beb"
},
"downloads": -1,
"filename": "sqlite_export_for_ynab-1.0.0.tar.gz",
"has_sig": false,
"md5_digest": "385bac108c5e2c8fba5a1c85cbd375ac",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.12",
"size": 14694,
"upload_time": "2025-01-26T19:42:17",
"upload_time_iso_8601": "2025-01-26T19:42:17.129078Z",
"url": "https://files.pythonhosted.org/packages/64/a5/83010ef9deedf353855e7232814fadb0719e01128877961966b11f941dac/sqlite_export_for_ynab-1.0.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-01-26 19:42:17",
"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,
"tox": true,
"lcname": "sqlite-export-for-ynab"
}
JSON
