Transforming Bank Data into a Real-Time Finance Dashboard with Grafana

In my previous post, I showed how to use Playwright to scrape transaction data and store it in a PostgreSQL database. However, raw tables aren’t very helpful for quick insights. In this post, we’ll visualize that data using Grafana.

Using Grafana Variables for Dynamic SQL Queries

Time Grouping

I use a variable called ${time_group} to control the aggregation granularity.

• Type: Interval or Custom
• Values: day, week, month

Time Filter

$__timeFilter is a built-in Grafana variable that allows you to dynamically filter data based on the selected dashboard time range.

Spending by Category

WITH base AS (
    SELECT
        COALESCE(record_date, occurrence_datetime, transaction_date) AS effective_date,
        amount,
        CASE
            WHEN category = 'others' THEN split_part(split_part(title, ' ', 1), '－', 1)
            ELSE category
        END AS category_label
    FROM transaction_log
    WHERE category != 'exclusion' AND category != 'income' AND amount > 0
)
SELECT
    DATE_TRUNC('${time_group}', effective_date) AS grouped_time,
    category_label,
    SUM(amount) AS total_amount
FROM base
WHERE $__timeFilter(effective_date)
GROUP BY grouped_time, category_label
ORDER BY grouped_time, category_label;

The result of this query cannot be directly visualized as a standard time series because it returns rows in the format (time, category, total_amount). However, most charts expect a matrix format where each category becomes a separate column.

To solve this, I used Grafana’s Transform → Grouping to Matrix feature to reshape the data.

---

Balance Trend (Net Worth)

Since our data is stored as a transaction log rather than periodic snapshots, calculating balance trends requires a different approach.

To track how your net worth evolves, we need the last recorded balance for each time period (day/week/month). We use ROW_NUMBER() to select the most recent record within each group.

WITH base AS (
    SELECT
        COALESCE(occurrence_datetime, record_date, transaction_date) AS effective_date,
        balance
    FROM transaction_log
    WHERE amount > 0 AND balance != 0
)
SELECT group_time, balance
FROM (
    SELECT
        DATE_TRUNC('${time_group}', effective_date) AS group_time,
        balance,
        ROW_NUMBER() OVER (
            PARTITION BY DATE_TRUNC('${time_group}', effective_date)
            ORDER BY effective_date DESC
        ) AS rn
    FROM base
) t
WHERE rn = 1
ORDER BY group_time;

Search Records

SELECT *,
       COALESCE(record_date, occurrence_datetime, transaction_date) AS time
FROM transaction_log
WHERE CONCAT_WS(' ', id, pk, title, memo, comment, post_script, category, currency_amount)
      ILIKE '%${search_text}%'
  AND $__timeFilter(COALESCE(transaction_date))
ORDER BY COALESCE(record_date, occurrence_datetime, transaction_date) DESC
LIMIT 1000;

---

Final Result

This setup turns raw financial transaction logs into a flexible, real-time dashboard, making it much easier to monitor spending patterns and track net worth over time.