sqlite-export-for-ynab

SQLite Export for YNAB - Export YNAB Budget Data to SQLite

What This Does

Export your YNAB budget to a local SQLite DB. Then you can query your budget with any tools compatible with SQLite.

Installation

$ pip install sqlite-export-for-ynab

Usage

CLI

Provision a YNAB Personal Access Token and save it as an environment variable.

$ export YNAB_PERSONAL_ACCESS_TOKEN="..."

Run the tool from the terminal to download your budget:

$ 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 --db. Otherwise, the DB is stored according to the XDG Base Directory Specification. If XDG_DATA_HOME is set then the DB is saved in "${XDG_DATA_HOME}"/sqlite-export-for-ynab/db.sqlite. If not, then 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:

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))

SQL

The schema is defined in create-tables.sql. It is very similar to YNAB's OpenAPI Spec however some objects are pulled out into their own tables (ex: subtransactions, loan account periodic values) and foreign keys are added as needed (ex: budget ID, transaction ID). You can query the DB with typical SQLite tools.

Sample Queries

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

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
            transactions t
            JOIN payees p ON t.payee_id = p.id
            JOIN budgets 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,
    net_spent DESC
;

To get payees with no transactions:

SELECT DISTINCT
    b.name,
    p.name
FROM
    budgets b
    JOIN payees p ON b.id = p.budget_id
    LEFT JOIN (
        SELECT
            budget_id,
            payee_id,
            MAX(NOT deleted) AS has_active_transaction
        FROM
            transactions
        GROUP BY
            budget_id,
            payee_id
    ) t ON (
        p.id = t.payee_id
        AND p.budget_id = t.budget_id
    )
    LEFT JOIN (
        SELECT
            budget_id,
            payee_id,
            MAX(NOT deleted) AS has_active_transaction
        FROM
            scheduled_transactions
        GROUP BY
            budget_id,
            payee_id
    ) 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
    1,
    2
;