DEV Community

Stackscale
Stackscale

Posted on

Extract Bank Statement Data to Google Sheets with Make and OpenAI

Bank statements are piling up as PDFs. Learn how to extract bank statement data to google sheets — Make watches for new statements, OpenAI reads the PDF, and every transaction files itself as a clean row, dated, categorised, with a running balance.

This is the companion tutorial for the StackScale video above. No code, no live bank account, and no real bank data — the whole flow runs on one fictional Demo Bank statement so you can test it safely.

What You Will Build

A Make scenario that watches for a new bank statement, sends the PDF to OpenAI, and files every transaction into Google Sheets: date, description, amount, category, and a running balance that always agrees with the statement. Twelve transactions in, twelve rows out, and the balance matches to the cent.

Prerequisites / Tools

  • A Make account (free plan is enough to start)
  • An OpenAI account
  • A free Google Sheet for the transactions
  • The Gmail inbox or Drive folder where your statements already arrive
  • One dummy statement to test with — the fictional Demo Bank statement used in the video

Disclosure: Some links below are affiliate links, meaning StackScale may earn a commission at no extra cost to you.

The Workflow

Make watches your inbox or Drive folder for a new statement PDF, then sends it to OpenAI with an extraction prompt. OpenAI returns every transaction as JSON. Make maps the fields into Sheets, and a running-balance formula keeps the totals honest. Converter tools only turn a PDF into a CSV when you ask them to; this flow starts by itself the moment a statement arrives.

Step 1: Build the Transactions Sheet

One row per transaction, with columns for date, description, amount, category, and running balance. Then add a tab called Statements Log, so every processed PDF is recorded once and never read twice.

Step 2: Trigger on a New Statement

In Make, set the scenario to watch your Gmail inbox or Drive folder for a new PDF from your bank. The scenario starts the moment one arrives — no button, no schedule to remember.

Step 3: The OpenAI Extraction Prompt

Send the PDF to OpenAI with this prompt (copy as-is):

You are reading a bank statement PDF. Extract every transaction as JSON. For each transaction return date (YYYY-MM-DD), description, amount (spending negative, deposits positive), and category (Food, Bills, Transport, Shopping, Income, Other). Return a JSON array only, one object per transaction, in date order. Example: [{"date": "2026-10-03", "description": "Demo Grocery Store", "amount": -48.20, "category": "Food"}].
Enter fullscreen mode Exit fullscreen mode

Tell it to keep the sign on every amount, so spending stays negative and deposits stay positive.

Step 4: Map the Fields into Sheets

Copy this field mapping into the Sheets module:

{
  "Date": "transaction date",
  "Description": "transaction description",
  "Amount": "amount (negative for spending)",
  "Category": "Food / Bills / Transport / Shopping / Income / Other",
  "Running Balance": "previous balance + new amount (formula)"
}
Enter fullscreen mode Exit fullscreen mode

And log each processed file once:

{
  "File name": "statement file name",
  "Date processed": "date processed",
  "Rows added": "rows added"
}
Enter fullscreen mode Exit fullscreen mode

Step 5: Test with the Dummy Statement

Drop the fictional Demo Bank PDF in and watch twelve rows appear: Demo Grocery Store, minus $48.20, Food; Demo Coffee, minus $4.50, Food; Demo Utilities, minus $120.00, Bills. Twelve rows in, all twelve extracted, and the balance matches to the cent.

The Finished Result

Every statement filed, every row categorised, balance always up to date. Open the sheet each month and the transactions are already there.

One Honest Limit

Statements vary by bank, so a new bank's layout needs a quick check the first time — a different date format or a new column can confuse the extraction. And this flow only reads PDFs you already receive; it never logs into your bank. Check the first few rows against the PDF before you trust it.

Watch the Full Tutorial

The video at the top walks through the same five steps with the synthetic mockups. All mockups in the video are synthetic, created for this tutorial, with dummy data only — no real bank statement is shown, and no real account appears anywhere.

New automation tutorials every day on StackScale — subscribe on YouTube at https://www.youtube.com/@StackScaleHQ.

Top comments (0)