SQLite Export for YNAB - Export YNAB Budget Data to SQLite
Export all your YNAB plans to a local SQLite DB. Then you can query your data with any tools compatible with SQLite.
$ pip install sqlite-export-for-ynabProvision 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 plans:
$ sqlite-export-for-ynabRunning it again will pull only data that changed since the last pull (this is done with Delta Requests). 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
- The
--dbflag. - The
XDG_DATA_HOMEvariable (see the XDG Base Directory Specification). In that case the DB is saved in"${XDG_DATA_HOME}"/sqlite-export-for-ynab/db.sqlite. - If neither is set, the DB is saved in
~/.local/share/sqlite-export-for-ynab/db.sqlite.
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))The relations are defined in create-relations.sql. They are 1:1 with YNAB's OpenAPI Spec (ex: transactions, accounts, etc) with some additions:
- Some objects are pulled out into their own tables so they can be more cleanly modeled in SQLite (ex: subtransactions, loan account periodic values).
- Foreign keys are added as needed (ex: plan ID, transaction ID) so data across plans remains separate.
- Two new views called
flat_transactionsandscheduled_flat_transactions. These allow you to query split and non-split transactions easily, without needing to also querysubtransactionsandscheduled_subtransactionsrespectively. They also filter out deleted transactions/subtransactions and project payee/category fields to make querying more ergonomic.
You can issue queries with typical SQLite tools. sqlite-export-for-ynab deliberately does not implement a SQL REPL.
You can run the queries from this README using a tool like mdq. For example:
$ mdq '```sql dupes' path/to/sqlite-export-for-ynab/README.md -o plain \
| sqlite3 path/to/sqlite-export-for-ynab/db.sqliteThe DB path is documented above.
To get the top 5 payees by spending per plan, you could do:
WITH ranked_payees AS (
SELECT
pl.name AS plan_name
, t.payee_name AS payee
, SUM(t.amount_currency) 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:
WITH used_payees AS (
SELECT
plan_id
, payee_id
FROM transactions
WHERE
TRUE
AND payee_id IS NOT NULL
AND NOT deleted
UNION
SELECT
plan_id
, payee_id
FROM subtransactions
WHERE
TRUE
AND payee_id IS NOT NULL
AND NOT deleted
UNION
SELECT
plan_id
, payee_id
FROM scheduled_transactions
WHERE
TRUE
AND payee_id IS NOT NULL
AND NOT deleted
UNION
SELECT
plan_id
, payee_id
FROM scheduled_subtransactions
WHERE
TRUE
AND payee_id IS NOT NULL
AND NOT deleted
)
SELECT
pl.name AS "plan"
, dupes.name AS payee
FROM (
SELECT
p.plan_id
, p.name
FROM payees AS p
LEFT JOIN used_payees AS up ON p.plan_id = up.plan_id AND p.id = up.payee_id
WHERE
TRUE
AND up.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
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):
SELECT
plan_id
, SUM(amount_currency) AS amount_currency
FROM (
SELECT
plan_id
, amount_currency
FROM flat_transactions
WHERE
category_name = 'Apps'
AND SUBSTR("date", 1, 7) = SUBSTR(DATE(), 1, 7)
UNION ALL
SELECT
plan_id
, amount_currency * (
CASE
WHEN frequency = 'monthly' THEN 11
ELSE 1 -- assumes yearly
END
) AS amount_currency
FROM scheduled_flat_transactions
WHERE
category_name = 'Apps'
AND SUBSTR(date_next, 1, 7) < SUBSTR(DATE('now', '+1 year'), 1, 7)
)
;To estimate taxable interest for a given year1:
-- Parameters expected by this query:
-- @tax_rate
-- @year
-- @plan_id (optional, defaults to output for all plans)
-- @estimated_additional_interest (optional,
-- estimated interest not in YNAB such as investment income)
-- @interest_reporting_threshold (optional, defaults to the $10
-- common threshold, but confirm with actual documents)
-- @interest_payee_name (optional, defaults to Interest)
--
-- Example with only required params:
-- sqlite3 -header -box path/to/db.sqlite \
-- -cmd '.parameter init' \
-- -cmd ".parameter set @tax_rate 0.25" \
-- -cmd ".parameter set @year 2025" \
-- < query.sql
--
-- Example with all params:
-- -cmd ".parameter set @tax_rate 0.25" \
-- -cmd ".parameter set @year 2025" \
-- -cmd ".parameter set @estimated_additional_interest 250.00" \
-- -cmd ".parameter set @interest_reporting_threshold 10" \
-- -cmd ".parameter set @interest_payee_name Interest" \
-- -cmd ".parameter set @plan_id your-plan-id" \
-- < query.sql
WITH interest_by_account AS (
SELECT
plan_id
, account_name
, SUM(-amount_currency) AS total
FROM flat_transactions
WHERE
TRUE
AND payee_name = COALESCE(NULLIF(@interest_payee_name, ''), 'Interest')
AND SUBSTR("date", 1, 4) = CAST(@year AS TEXT)
AND (COALESCE(@plan_id, '') = '' OR plan_id = @plan_id)
GROUP BY plan_id, account_name
HAVING total >= CAST(COALESCE(@interest_reporting_threshold, 10) AS REAL)
)
, interest_by_plan AS (
SELECT
plans.id AS plan_id
, plans.name AS plan_name
, COALESCE(SUM(interest_by_account.total), 0) AS interest_in_ynab
FROM plans
LEFT JOIN interest_by_account ON plans.id = interest_by_account.plan_id
WHERE COALESCE(@plan_id, '') = '' OR plans.id = @plan_id
GROUP BY plan_id, plan_name
)
, ranked_interest AS (
SELECT
plan_id
, plan_name
, interest_in_ynab
, interest_in_ynab
+ CAST(COALESCE(@estimated_additional_interest, 0) AS REAL)
AS interest_with_estimate
, ROW_NUMBER() OVER (ORDER BY plan_name, plan_id) AS row_num
FROM interest_by_plan
)
, estimated_interest AS (
SELECT
plan_id
, plan_name
, interest_in_ynab
-- Additional interest is per-tax-return not per-YNAB-plan. Only add
-- additional interest to one plan's output to avoid double counting.
, CASE
WHEN row_num != 1 THEN interest_in_ynab
WHEN
interest_with_estimate
< CAST(COALESCE(@interest_reporting_threshold, 10) AS REAL)
THEN 0
ELSE interest_with_estimate
END AS estimated_total_taxable_interest
FROM ranked_interest
)
SELECT
plan_name AS "plan"
, PRINTF('%.2f', interest_in_ynab) AS interest_in_ynab
, PRINTF('%.2f', estimated_total_taxable_interest)
AS estimated_total_taxable_interest
, PRINTF(
'%.2f'
, estimated_total_taxable_interest * CAST(NULLIF(@tax_rate, '') AS REAL)
) AS estimated_tax_liability
FROM estimated_interest
ORDER BY plan_name, plan_id
;To compare assigned category values to a given account's balance:
-- Parameters expected by this query:
-- @account_name_like (required, the account name to match against)
-- @plan_id (optional, defaults to output for all matching plans)
-- @include_category_groups
-- (optional, comma-separated category-group names to include;
-- exclusive with @exclude_category_groups)
-- @exclude_category_groups
-- (optional, comma-separated category-group names to exclude;
-- exclusive with @include_category_groups)
--
-- Example:
-- sqlite -header -box path/to/db.sqlite \
-- -cmd '.parameter init' \
-- -cmd ".parameter set @account_name_like %Savings%" \
-- -cmd ".parameter set @include_category_groups 'Home,Food'" \
-- < query.sql
CREATE TEMP TABLE excess_query_results AS
WITH params AS (
SELECT
TRIM(COALESCE(@account_name_like, '')) AS account_name_like
, TRIM(COALESCE(@plan_id, '')) AS plan_id
, TRIM(COALESCE(@include_category_groups, ''))
AS include_category_groups
, TRIM(COALESCE(@exclude_category_groups, ''))
AS exclude_category_groups
)
, scoped_plans AS (
SELECT
p.id
, p.name
FROM plans AS p
CROSS JOIN params AS prm
WHERE prm.plan_id = '' OR p.id = prm.plan_id
)
, split_include_category_groups (value, rest) AS (
SELECT
''
, prm.include_category_groups || ','
FROM params AS prm
UNION ALL
SELECT
TRIM(SUBSTR(rest, 1, INSTR(rest, ',') - 1))
, SUBSTR(rest, INSTR(rest, ',') + 1)
FROM split_include_category_groups
WHERE rest != ''
)
, include_category_groups AS (
SELECT value AS name
FROM split_include_category_groups
WHERE value != ''
)
, split_exclude_category_groups (value, rest) AS (
SELECT
''
, prm.exclude_category_groups || ','
FROM params AS prm
UNION ALL
SELECT
TRIM(SUBSTR(rest, 1, INSTR(rest, ',') - 1))
, SUBSTR(rest, INSTR(rest, ',') + 1)
FROM split_exclude_category_groups
WHERE rest != ''
)
, exclude_category_groups AS (
SELECT value AS name
FROM split_exclude_category_groups
WHERE value != ''
)
, matching_accounts AS (
SELECT
sp.id AS plan_id
, sp.name AS plan_name
, COUNT(*) AS matches
FROM scoped_plans AS sp
INNER JOIN accounts AS a ON sp.id = a.plan_id
CROSS JOIN params AS prm
WHERE NOT a.deleted AND a.name LIKE prm.account_name_like
GROUP BY sp.id, sp.name
)
, validation AS (
SELECT
p.account_name_like
, p.plan_id
, p.include_category_groups
, p.exclude_category_groups
FROM params AS p
)
, validation_errors AS (
SELECT 'Set @account_name_like' AS error
FROM validation AS v
WHERE v.account_name_like = ''
UNION ALL
SELECT
'Set only one of @include_category_groups'
|| ' or @exclude_category_groups' AS error
FROM validation AS v
WHERE v.include_category_groups != '' AND v.exclude_category_groups != ''
UNION ALL
SELECT 'No plan matched @plan_id' AS error
FROM validation AS v
WHERE
v.plan_id != '' AND NOT EXISTS (
SELECT 1
FROM scoped_plans
)
UNION ALL
SELECT 'No account names matched @account_name_like' AS error
FROM validation AS v
WHERE
v.account_name_like != '' AND NOT EXISTS (
SELECT 1
FROM matching_accounts
)
UNION ALL
SELECT
'Matched more than 1 account in plan: '
|| ma.plan_name AS error
FROM matching_accounts AS ma
WHERE ma.matches > 1
UNION ALL
SELECT
'Unknown include category group in plan '
|| sp.name
|| ': '
|| icg.name AS error
FROM scoped_plans AS sp
CROSS JOIN include_category_groups AS icg
LEFT JOIN category_groups AS cg
ON
sp.id = cg.plan_id
AND NOT COALESCE(cg.deleted, 0)
AND LOWER(cg.name) = LOWER(icg.name)
WHERE cg.id IS NULL
UNION ALL
SELECT
'Unknown exclude category group in plan '
|| sp.name
|| ': '
|| ecg.name AS error
FROM scoped_plans AS sp
CROSS JOIN exclude_category_groups AS ecg
LEFT JOIN category_groups AS cg
ON
sp.id = cg.plan_id
AND NOT COALESCE(cg.deleted, 0)
AND LOWER(cg.name) = LOWER(ecg.name)
WHERE cg.id IS NULL
)
, valid_params AS (
SELECT
v.account_name_like
, v.plan_id
, v.include_category_groups
, v.exclude_category_groups
FROM validation AS v
WHERE NOT EXISTS (
SELECT 1
FROM validation_errors
)
)
, matched_accounts AS (
SELECT
p.id AS plan_id
, p.name AS plan_name
, a.name AS account_name
, a.cleared_balance / 1000.0 AS account_amount
FROM plans AS p
INNER JOIN accounts AS a ON p.id = a.plan_id
CROSS JOIN valid_params AS v
WHERE
TRUE
AND NOT a.deleted
AND a.name LIKE v.account_name_like
AND (v.plan_id = '' OR p.id = v.plan_id)
)
, category_totals AS (
SELECT
c.plan_id
, COALESCE(SUM(c.balance), 0) / 1000.0 AS total
FROM categories AS c CROSS JOIN valid_params AS v
WHERE
TRUE
AND NOT c.deleted
AND c.category_group_name != 'Credit Card Payments'
AND c.category_group_name != 'Internal Master Category'
AND (
v.include_category_groups = ''
OR EXISTS (
SELECT 1
FROM include_category_groups AS icg
WHERE LOWER(icg.name) = LOWER(c.category_group_name)
)
)
AND (
v.exclude_category_groups = ''
OR NOT EXISTS (
SELECT 1
FROM exclude_category_groups AS ecg
WHERE LOWER(ecg.name) = LOWER(c.category_group_name)
)
)
AND (v.plan_id = '' OR c.plan_id = v.plan_id)
GROUP BY c.plan_id
)
SELECT
ve.error AS error_message
, NULL AS "plan"
, NULL AS account
, NULL AS total
, NULL AS excess
FROM validation_errors AS ve
UNION ALL
SELECT
NULL AS error_message
, ma.plan_name AS "plan"
, ma.account_name AS account
, PRINTF('%.2f', COALESCE(ct.total, 0)) AS total
, PRINTF('%.2f', ma.account_amount - COALESCE(ct.total, 0)) AS excess
FROM matched_accounts AS ma
LEFT JOIN category_totals AS ct ON ma.plan_id = ct.plan_id
;
SELECT error_message
FROM excess_query_results
WHERE error_message IS NOT NULL
;
SELECT
eqr."plan"
, eqr.account
, eqr.total
, eqr.excess
FROM excess_query_results AS eqr
WHERE
NOT EXISTS (
SELECT 1
FROM excess_query_results AS eqr_errors
WHERE eqr_errors.error_message IS NOT NULL
)
;Footnotes
-
This query is a rough estimate based on YNAB data and optional user inputs. It is not financial advice, tax advice, or a substitute for Forms 1099-INT, brokerage statements, bank records, or guidance from a qualified professional. ↩