How to Automatically Backup Your Richart Bank Transactions to PostgreSQL Why You Need to Do This The Problem with Open Banking The Solution: Personal Automation 驗證碼 data struct playwright send request with current context push it to database setup cronjob result

How to Automatically Backup Your Richart Bank Transactions to PostgreSQL

Why You Need to Do This

Most banks do not hold your transaction data for long; they typically only store it for up to one year. This means you cannot access your historical personal finance information once that window closes.

The Problem with Open Banking

Open Banking in Taiwan is currently stagnant. Accessing the current Bank Open API platform requires as bank middleman between company and platform.

Furthermore, I am uncomfortable sharing all of my financial data with third-party companies (TSPs), even if they are regulated by the government. Your data belongs to you, not a middleman(TSP company).

The Solution: Personal Automation

The solution is to write your own script that regularly fetches your bank transactions.

As shown in the diagram, logging into a web-based bank account typically requires:

  • User ID
  • Account Name
  • Password
  • 驗證碼

To automate the login process, I use Playwright to simulate user actions in the browser. For the 驗證碼, I use traditional ddddocr to pass the verification.

驗證碼

def solve_captcha(page: Page, ocr_engine) -> str:
    """Extracts and solves captcha from the page."""
    img_element = page.locator(XPATHS["CAPTCHA_IMG"])
    src = img_element.get_attribute("src")
    if not src: return ""

    img_bytes = base64.b64decode(src.split(",")[1])
    return ocr_engine.classification(img_bytes).strip()

data struct

in browser network you can see the bank api tha we need

the return data should look like

api.post(URLS["SUMMARY"], data={"month": month, "type": t_type})
[{
  "pk": 12345,
  "amount": 150.75,
  "balance": 10000.00,
  "currencyAmount": "USD",
  "category": "Shopping",
  "transactionCategory": "Online",
  "mccCode": "5412",
  "title": "Amazon Purchase",
  "titleType": "0",
  "memo": "Payment for electronics",
  "comment": "For testing purposes",
  "postScript": "This is a test transaction",
  "payee": "Amazon.com",
  "accountNumber": "123456789",
  "bankCode": "",
  "authCode": "",
  "creditCardCh": "",
  "occurrenceDate": "2024-05-15T14:30:00Z",
  "tranDate": "",
  "recordDate": "",
  "favorite": false,
  "frequentContact": true
}]

so titleType are transaction type

  • 1 : income
  • 2 : domestic transaction
  • 3 : foreign transaction

playwright send request with current context

context = page.context
api = context.request
for month in range(1, 13):
  # Type 1 = Income/Expense, Type 2 = credit card
  for t_type in [0,1,2,3]:
    resp = api.post(URLS["SUMMARY"], data={"month": month, "type": t_type})
    logs = resp.json().get("result", {}).get("transLogList", [])

push it to database

 def insert_transaction(cur, data):
    """Saves all transaction data into PostgreSQL, mapping every field."""
    sql = """
        INSERT INTO transaction_log (
            pk, amount, balance, currency_amount,
            category, transaction_category, mcc_code, title, title_type,
            memo, comment, post_script, payee,
            account_number, bank_code, auth_code, credit_card_ch,
            occurrence_datetime, transaction_date, record_date,
            favorite, frequent_contact, raw_payload
        ) VALUES (
            %(pk)s, %(amount)s, %(balance)s, %(currency_amount)s,
            %(category)s, %(transaction_category)s, %(mcc_code)s, %(title)s, %(title_type)s,
            %(memo)s, %(comment)s, %(post_script)s, %(payee)s,
            %(account_number)s, %(bank_code)s, %(auth_code)s, %(credit_card_ch)s,
            %(occurrence_dt)s, %(tran_date)s, %(record_date)s,
            %(favorite)s, %(frequent)s, %(raw)s
        )
        ON CONFLICT (pk) DO UPDATE SET
            amount = EXCLUDED.amount,
            balance = EXCLUDED.balance,
            category = EXCLUDED.category,
            memo = COALESCE(NULLIF(transaction_log.memo, ''), EXCLUDED.memo),
            comment = COALESCE(NULLIF(transaction_log.comment, ''), EXCLUDED.comment),
            post_script = EXCLUDED.post_script,
            favorite = EXCLUDED.favorite,
            frequent_contact = EXCLUDED.frequent_contact,
            raw_payload = EXCLUDED.raw_payload;
    ...
    """
    cur.execute(sql, params)

setup cronjob

#every day 21:00 run
0 21 * * * docker run --rm --env-file .env  --network finance_default -v temp:/temp finance-richart:latest >> ./log.txt  2>&1

result

full code