OpenFinance Skill#

Query your financial accounts and transactions via the OpenFinance API.

Setup#

1. Go to openfinance.sh and create an account

2. Link a bank account through the dashboard

3. Copy your API key from the Connect tab

4. Set the environment variable: bash

   export OPENFINANCE_API_KEY="your_api_key_here"
   
   

All commands below use these variables:

text

BASE_URL="${OPENFINANCE_URL:-https://api.openfinance.sh}"
AUTH_HEADER="Authorization: Bearer $OPENFINANCE_API_KEY"

---

1. Get Accounts#

Fetch all connected financial accounts with balances and institution info.

bash

curl -s "$BASE_URL/api/accounts" -H "$AUTH_HEADER" | cat

Response shape per account:

• id (number) — account ID (use for filtering transactions)
• name, officialName — account names
• type (e.g. "depository", "credit"), subtype (e.g. "checking", "credit card")
• mask — last 4 digits
• currentBalance, availableBalance, isoCurrencyCode
• institutionName, institutionUrl
• status — "active" or "hidden"
• isSyncing (boolean), syncError (object or null)

A non-null syncError means the bank connection needs the owner to sign in again. Use Create Reconnect Link to fix it.

---

2. Get Transactions#

Search and filter transactions. Returns newest first by default.

bash

curl -s -G "$BASE_URL/api/transactions" \
  -H "$AUTH_HEADER" \
  --data-urlencode "startDate=YYYY-MM-DD" \
  --data-urlencode "endDate=YYYY-MM-DD" \
  --data-urlencode "search=coffee" \
  --data-urlencode "merchants=Starbucks,Walmart" \
  --data-urlencode "accountId=123" \
  --data-urlencode "limit=100" \
  --data-urlencode "cursor=YYYY-MM-DD:ID" \
  --data-urlencode "pending=false" \
  --data-urlencode "status=active,hidden" \
  --data-urlencode "fields=name,amount,date,merchantName" \
  --data-urlencode 'amountFilters=[{"operator":">","amount":100}]' \
  | cat

All query parameters are optional. Only include the ones you need.

Parameters#

ParameterTypeDescriptionstartDate``YYYY-MM-DDStart date filterendDate``YYYY-MM-DDEnd date filtersearchstringSearch by transaction name or merchantmerchantscomma-separatedFilter by exact merchant namesaccountIdnumberFilter by account IDlimitnumberMax results (default 100, max 500)cursorstringNext page: <date>:<id> of the last transaction you received. Date sort onlypendingbooleanFilter pending transactionsstatuscomma-separatedFilter by status: active, hidden, deleted``fieldscomma-separatedReturn only these fields per transaction (reduces payload)amountFiltersJSON arrayFilter by amount, e.g. [{"operator":">","amount":50}]

Transaction fields#

id, name, amount, date, authorizedDate, pending, merchantName, isoCurrencyCode, accountId, status, createdAt, updatedAt

---

3. Query Transactions (SQL)#

Run one validated SQL SELECT (maximum 2000 characters) against the unqualified txns relation for aggregations, grouping, and analysis. Only the caller's transactions are available; deleted transactions and hidden accounts are excluded. The query runs read-only with a 5-second timeout and a maximum of 1000 result rows. Aggregations still use all matching input rows, and smaller LIMITs and OFFSETs are preserved.

Filtering, DISTINCT, grouping, HAVING, sorting, subqueries, joins, and UNION/UNION ALL are supported. Every table source must be txns or a derived SELECT whose sources are txns. Other tables and schemas, caller-defined CTEs, window functions, SELECT INTO, locking clauses, table functions, transaction commands, and multiple statements are rejected.

Approved functions: COUNT, SUM, AVG, MIN, MAX, ABS, ROUND, CEIL, CEILING, FLOOR, LOWER, UPPER, LENGTH, TRIM, BTRIM, LTRIM, RTRIM, SUBSTRING, SUBSTR, REPLACE, DATE_TRUNC, DATE_PART, EXTRACT, TO_CHAR. CASE, COALESCE, NULLIF, GREATEST, LEAST, CURRENT_DATE, and CURRENT_TIMESTAMP are also supported. Casts are limited to built-in boolean, integer, numeric, floating-point, text, date, timestamp, and interval types. Arithmetic, comparisons, LIKE/ILIKE, IN, BETWEEN, and NULL/boolean tests are supported; custom operators, regex operators, COLLATE, and unlisted syntax are rejected. Function and type names may be unqualified or qualified with pg_catalog.

bash

curl -s -X POST "$BASE_URL/api/transactions/query" \
  -H "$AUTH_HEADER" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT SUM(amount), COUNT(*) FROM txns WHERE merchant_name ILIKE '\''%starbucks%'\''"}' \
  | cat

txns CTE columns#

id, name, amount (numeric), date, authorized_date, merchant_name, pending, iso_currency_code, account_id, status, created_at, updated_at

Note: SQL column names use snake_case (e.g. merchant_name), while the REST API returns camelCase (e.g. merchantName).

Example queries#

Monthly spend breakdown:

sql

SELECT TO_CHAR(date, 'YYYY-MM') as month, SUM(amount) as total, COUNT(*) as count
FROM txns GROUP BY 1 ORDER BY 1

Top merchants by total spend:

sql

SELECT COALESCE(merchant_name, name) as merchant, SUM(amount) as total
FROM txns GROUP BY 1 ORDER BY total DESC LIMIT 10

Spending in a date range:

sql

SELECT SUM(amount) as total FROM txns
WHERE date >= '2026-01-01' AND date < '2026-02-01'

Large transactions:

sql

SELECT name, merchant_name, amount, date FROM txns
WHERE amount > 500 ORDER BY amount DESC

Response shape#

Success: { "rows": [...], "rowCount": N } Error: { "error": "error message" } — fix the SQL and retry.

---

4. Create Reconnect Link#

Create a public link that reconnects a disconnected account without signing in to OpenFinance. Give the URL to the account's owner. Opening it walks them through their bank's sign-in (Plaid or Quiltt), then shows the account re-syncing. Only the owner can finish this step: bank credentials and verification codes can't be sent through the API.

Find accounts that need reconnecting:

bash

curl -s "$BASE_URL/api/accounts" -H "$AUTH_HEADER" \
  | jq '.accounts[] | select(.syncError != null) | {id, institutionName, syncError}'

Create a link for one of them:

bash

curl -s -X POST "$BASE_URL/api/accounts/ACCOUNT_ID/reconnect-link" \
  -H "$AUTH_HEADER" \
  | cat

Response ( 201):

json

{
  "url": "https://app.openfinance.sh/reconnect/<token>",
  "expiresAt": "2026-01-02T15:04:05.000Z",
  "accountId": 123,
  "institutionName": "Chase",
  "provider": "plaid"
}

• Links expire after 24 hours. Once the account is reconnected, the link only shows the sync's progress. If the bank still wants a sign-in afterwards, the same link can try again.
• To check it worked, poll GET /api/accounts: the account's syncError turns null as soon as the owner reconnects, and isSyncing stays true until the re-sync finishes, which can take a few minutes.
• Reconnecting repairs the account's whole bank connection, so it also fixes the other accounts linked through the same sign-in.
• 404: no account with that ID. 422: the account's provider (MX) isn't supported.

---

Tips#

• Use SQL for aggregations — monthly totals, top merchants, recurring charges. It's faster and returns less data than fetching all transactions. Transactions have no categories; group by COALESCE(merchant_name, name).
• Use fields parameter on GET /api/transactions to reduce payload size when you only need a few columns.
• Pagination: the response doesn't include a cursor. If a page returns limit transactions, request the next one with cursor=<date>:<id> from the last transaction, keeping the other parameters the same. Cursors only work with the default date sort.
• Amount values are positive for debits (money spent) and negative for credits (money received).
• When constructing SQL, always reference the txns CTE directly — do not define your own CTE or use transaction control statements.
• Disconnected accounts: if an account has a syncError, create a reconnect link and send it to the owner rather than asking them to open the app.