AI-Powered Bank Statement Analysis and Transaction Categorization

Receive PDF/Excel/CSV statements via n8n webhook, extract structured transactions with AI, categorize expenses, store in PostgreSQL, and return a summary

Who

Finance or operations staff who need to automate bank statement ingestion, transaction extraction, and categorized storage

You get

After uploading a statement file, automatically receive a standardized JSON response with account info, transaction details, and expense category breakdowns, with data persisted to a database

You need

n8n · postgres

Steps

1. Prerequisites and credential setup

   Configure OpenAI API credentials (openAiApi) and PostgreSQL database credentials (postgres) in n8n. Create a bank_statements table in PostgreSQL with columns matching the Save to Database node mappings: raw_data, bank_name, processed_at, total_income, account_number, total_expenses, statement_period, total_transactions. Customize category labels in the AI node system prompt if needed.

2. Upload statement and smart routing

   Upload a bank statement file (PDF, Excel, or CSV) via POST to the /upload-statement webhook endpoint. The Webhook node receives the file; the File Handler code node extracts metadata (filename, MIME type) and preserves binary data. The Check File Type node routes based on contentType containing application/pdf: PDFs go to Extract PDF Text; Excel/CSV files go to Parse Excel/CSV.

   Execution prompt · Source text。File Handler node (n8n-nodes-base.code) jsCode parameter

   Copy prompt

   // Simple file processor
   const inputData = $input.all()[0];
   const files = [];
   
   // Handle file uploads
   if (inputData.binary) {
     Object.keys(inputData.binary).forEach(key => {
       const file = inputData.binary[key];
       files.push({
         filename: file.fileName,
         contentType: file.mimeType,
         uploadedAt: new Date().toISOString()
       });
     });
   }
   
   return files.map(file => ({
     json: {
       filename: file.filename,
       contentType: file.contentType,
       uploadedAt: file.uploadedAt,
       status: 'ready_for_processing'
     },
     binary: inputData.binary
   }));

3. AI extraction and categorization

   Output from Extract PDF Text or Parse Excel/CSV flows into the AI Data Extractor node (OpenAI). The system prompt instructs the model to extract account info, transactions, and balances and return JSON in a specified schema. The user prompt passes the extracted text or structured data. Default model is gpt-4o-mini; source also mentions GPT-4 series. Categories: groceries, dining, gas, shopping, utilities, healthcare, entertainment, income, fees, other.

   Execution prompt · Source text。AI Data Extractor node (OpenAI) system message field

   Copy prompt

   Extract bank statement data and return clean JSON:
   
   {
     "account_number": "****1234",
     "bank_name": "Bank Name",
     "statement_period": "2024-01-01 to 2024-01-31",
     "opening_balance": 1500.00,
     "closing_balance": 1250.00,
     "transactions": [
       {
         "date": "2024-01-15",
         "description": "GROCERY STORE",
         "amount": -45.67,
         "category": "groceries"
       }
     ]
   }
   
   Use negative amounts for expenses, positive for income. Categorize transactions as: groceries, dining, gas, shopping, utilities, healthcare, entertainment, income, fees, or other.

   Execution prompt · Source text。AI Data Extractor node (OpenAI) user message field

   Copy prompt

   {{ $json.data || $json.extracted_text }}

4. Data cleaning and summarization

   The Process & Summarize code node receives the AI output, parses the JSON content, cleans transaction data (generates unique IDs, formats dates, uppercases descriptions, numeric amounts, fills missing categories), and calculates total expenses, total income, net change, and category-level expense totals. If parsing fails, it returns an empty transactions array to avoid breaking the flow.

   Execution prompt · Source text。Process & Summarize node (n8n-nodes-base.code) jsCode parameter

   Copy prompt

   // Clean and validate extracted data
   const inputData = $input.all()[0];
   let extractedData = {};
   
   // Parse AI response
   try {
     const content = inputData.json.message?.content || inputData.json;
     if (typeof content === 'string') {
       const jsonMatch = content.match(/{[\s\S]*}/);
       if (jsonMatch) {
         extractedData = JSON.parse(jsonMatch[0]);
       }
     } else {
       extractedData = content;
     }
   } catch (error) {
     console.log('Parse error:', error.message);
     extractedData = { transactions: [] };
   }
   
   // Clean transaction data
   const cleanTransactions = (extractedData.transactions || []).map((tx, index) => ({
     id: `tx_${Date.now()}_${index}`,
     date: tx.date,
     description: (tx.description || '').trim().toUpperCase(),
     amount: parseFloat(tx.amount) || 0,
     category: tx.category || 'other',
     processed_at: new Date().toISOString()
   }));
   
   // Calculate summary
   const totalExpenses = cleanTransactions
     .filter(tx => tx.amount < 0)
     .reduce((sum, tx) => sum + Math.abs(tx.amount), 0);
   
   const totalIncome = cleanTransactions
     .filter(tx => tx.amount > 0)
     .reduce((sum, tx) => sum + tx.amount, 0);
   
   const categoryTotals = {};
   cleanTransactions.forEach(tx => {
     if (tx.amount < 0) { // Only expenses
       categoryTotals[tx.category] = (categoryTotals[tx.category] || 0) + Math.abs(tx.amount);
     }
   });
   
   return [{
     json: {
       account_info: {
         account_number: extractedData.account_number || 'Unknown',
         bank_name: extractedData.bank_name || 'Unknown',
         statement_period: extractedData.statement_period || 'Unknown',
         opening_balance: parseFloat(extractedData.opening_balance) || 0,
         closing_balance: parseFloat(extractedData.closing_balance) || 0
       },
       transactions: cleanTransactions,
       summary: {
         total_transactions: cleanTransactions.length,
         total_expenses: totalExpenses,
         total_income: totalIncome,
         net_change: totalIncome - totalExpenses,
         category_breakdown: categoryTotals
       },
       processed_at: new Date().toISOString(),
       status: 'completed'
     }
   }]);

5. Store in PostgreSQL database

   The Save to Database node inserts processed data into the bank_statements table. Field mappings include: raw_data (full JSON), bank_name, processed_at, total_income, account_number, total_expenses, statement_period, total_transactions. Ensure the database table structure matches these mapped fields or the insert will fail.

6. Return JSON response

   The Send Response node returns JSON to the webhook caller, including success status, processed transaction count, total expenses, total income, and category breakdown. The responseBody uses n8n expressions referencing upstream summary fields.

You are setting up an n8n workflow to automatically receive bank statement files (PDF/Excel/CSV), extract and categorize transactions with AI, store results in PostgreSQL, and return a summary. Please confirm: 1) n8n instance is running; 2) OpenAI and PostgreSQL credentials are configured; 3) the bank_statements table is created; 4) you have imported or rebuilt the 9 nodes and connections per this case. To adjust categories, edit the category list in the AI Data Extractor node system prompt.