How do you even track transactions? (Part 1)

As you might already know, when collecting credit card points, you need to keep track of transactions, especially merchant categories and bonus points limits. And with the recent changes and nerfs in 2025, it gets even harder and harder.

Now, how do I keep track of all these? It is not a big deal if you only have one or two cards. But it is a different ball game if you have multiple (read: more than 5?) cards across multiple banks, each with different bonus categories and limits, and different bonus calendar cycle dates. To make things worse, some banks have recently implemented sub-limits for various bonus categories, which make it very hard to track relevant transactions.

Top that off with supplementary cards, and it is a whole different beast altogether. As you might have guessed, it's kind of impractical to track all these stuff manually...

Starting Point

I know I hinted we won't do it manually. But let's begin with how we would do it manually – this is always a good starting point for any automation project. Basically, whenever you make a transaction, we want to record the relevant attributes. These are things like transaction date, transaction amount, merchant and its category, currency, and which card was used. These are relevant since most rewards cards give bonus points only on certain merchant categories or currency, and up to a certain cumulative amount per cycle.

Suppose we track these transactions as a database table (or a spreadsheet), then the table would have the following schema (or columns).

Let's call this Table 1: Transactions.

• transaction_date: Date of transaction
• currency: Transaction currency
• amount: Transaction amount in the above currency
• card_num: Last 4 digits of card
• merchant: Merchant name
• category: Merchant category
• is_online: Whether the transaction an online transaction

Notice I have not added information about the card account itself, e.g. bonus cycle, what kind of card it is, etc. Table 1 simply records the raw transaction data.

Ending Point

Now, let's look at what we want the final data to look like so we know what other tables we need. Eventually, we want to use the data to answer questions such as this: "How much have I spent on card ABC with XYZ category within this cycle?" since this allows us to optimise our card usage.

Essentially, the final table should allow us to do some analysis and aggregation, mainly to track the total spend amount since the last bonus cycle date for each card and for each relevant category.

For those who know some SQL, the group-by query we want looks something like this.

SUM(amount), currency, card_num, category, is_online
GROUP BY 2,3,4,5
WHERE transaction_date >= latest_cycle_start_date

Without SQL, you can also create pivot tables on a spreadsheet to get similar aggregations. Or... manually sum up the amounts...

Card Data

Now, how can we handle different card accounts with different bonus cycles, and also supplementary cards? We need to have tables with card information to join with our transaction table. They can look something like this.

Table 2: Card Accounts. This table stores the actual card accounts and the bonus cycles, which might either be calendar cycle or billing cycle. It has nothing to do with the card numbers, which will be stored in another table. You only update this table when you sign up for a new card account or cancel an account. The schema for the account table can be as such.

• card_id: Identifier for the card account, e.g. UOB PPV, DBS WWMC
• cycle_start_day
• bank
• is_active

Besides the cycle start day, you can also add other information such as the bank name, and indicators like whether the account is active or not, in case you cancel the account but want to keep the data.

Table 3: Card Numbers. This table stores the card numbers which can be matched with the transactions in Table 1, and the accounts in Table 2. It helps to track changes to card numbers (due to fraud, etc), and also supplementary cards.

• card_num: Last 4 digits of card
• card_id: Identifier for the card account, as per Table 2
• is_supplementary
• is_active

Currency Data

Sometimes we transact in foreign currency and in the transaction Table 1, we record the transaction amount in the raw transaction currency. We need a way to convert this to our base currency SGD, to aggregate the cumulative spend. One way to achieve this is to have a separate currency table that is dynamically updated. In Google Sheets, this can be achieved using such a formula GOOGLEFINANCE("CURRENCY:USDSGD")

Table 4: Exchange Rate

• from_currency: The foreign currency symbol
• to_currency: SGD
• rate: GOOGLEFINANCE formula

Manual Implementation

The above tables can be implemented manually, for example in Google Sheets. Then we manually insert transactions (maybe using Google Forms) whenever a transaction is made, and aggregate the transactions to track our cumulative spend for each card for the relevant categories.

We can already answer questions like this: How much have I used UOB LADY card for Dining category in this statement month?

So at this point, we would already have a working tracking system, albeit a troublesome one since we need to manually update Table 1 with each transaction made – yours and those made by supplementary card holders.

If the above is good enough for you, you may stop reading here. Otherwise, if manually updating transactions sounds too troublesome... read on.

Next Part: Automation Pipeline

The next part of the article is where the magic happens.

I will describe one way to fully automate the above transaction tracking using Google Cloud Platform. Don't worry, whatever you have learnt reading this first part will not go to waste as it utilises the same four tables described above as the backbone.

The automation comes in here: whenever a transaction is made, Table 1 is updated automatically, along with the aggregation, and optionally a visualisation or reporting dashboard for easy and convenient view on our current spend for each card and for each relevant category.

Stay tuned...

Hope you have learnt something from this article! Support Pondering Potato by using the referral links in the posts, sharing on Telegram, subscribing, or donating.