openfinance

Connect bank accounts to AI models using openfinance.sh

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

Parameter	Type	Description
startDate	YYYY-MM-DD	Start date filter
endDate	YYYY-MM-DD	End date filter
search	string	Search by transaction name or merchant
merchants	comma-separated	Filter by exact merchant names
accountId	number	Filter by account ID
limit	number	Max results (default 100, max 500)
cursor	string	Next page: <date>:<id> of the last transaction you received. Date sort only
pending	boolean	Filter pending transactions
status	comma-separated	Filter by status: active, hidden, deleted
fields	comma-separated	Return only these fields per transaction (reduces payload)
amountFilters	JSON array	Filter 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.