Skip to main content

Bank reconciliation on SFTP statement

This workflow retrieves the daily bank statement dropped on an SFTP server, enriches each transaction, archives the processed file, then notifies the treasury team.

Loading the diagram…

The need​

The treasury team reconciles the bank statement transactions every day. The daily statement is dropped on an SFTP server: it must be read, each line enriched (running balance, abnormal amounts), the processed file archived, then the treasury team notified.

What the workflow does​

  1. New bank statement — the File trigger (source FTP_SFTP) watches the drop directory and fires the workflow when the file arrives; its name arrives in data.filename.
  2. Read the SFTP statement — the FTP — Read node loads the file content into context.file (if the file is valid JSON, it is automatically parsed into an object).
  3. Enrich the transactions — the Code node splits the CSV line by line, computes the running balance and flags amounts above €5,000; the result is published in context.variables (list, balance, count of abnormal amounts).
  4. Archive the statement — the FTP — Move node moves the file from the releves directory (point A) to archive (point B), to keep a trace without reprocessing the same file.
  5. Notify the treasury team — an email summarizes the number of transactions, the computed balance and the flagged amounts.

What you need​

Construction​

Copy the workflow definition and paste it into the editor (the Trigger node is recreated automatically, it is not in the snippet):

[
  {
    "id": "read-110",
    "name": "Read the SFTP statement",
    "config": {
      "type": "READ_FILE_FROM_FTP",
      "filePath": "/banque/releves/{{ data.filename }}",
      "parseAs": {
        "type": "RAW"
      },
      "fileKey": "file",
      "stopWorkflowOnFailure": true
    },
    "parents": [
      "trigger"
    ],
    "position": {
      "x": 0,
      "y": 200
    },
    "next": "code-110"
  },
  {
    "id": "code-110",
    "name": "Enrich the transactions",
    "config": {
      "type": "CODE",
      "code": "const raw = typeof context.file === 'string' ? context.file : JSON.stringify(context.file);\nconst transactions = [];\nlet balance = 0;\nlet flagged = 0;\n\nraw.split('\\n').forEach((line, index) => {\n  if (index === 0 || !line.trim()) return;\n  const [date, label, amount] = line.split(';');\n  const value = Number(amount);\n  if (Number.isNaN(value)) return;\n  balance += value;\n  const transaction = { date, label, amount: value, balance };\n  if (Math.abs(value) > 5000) {\n    transaction.flagged = true;\n    flagged += 1;\n  }\n  transactions.push(transaction);\n});\n\ncontext.variables.transactions = transactions;\ncontext.variables.balance = balance;\ncontext.variables.flaggedCount = flagged;\n\nreturn { transactions, balance, flaggedCount: flagged };"
    },
    "parents": [
      "read-110"
    ],
    "position": {
      "x": 0,
      "y": 400
    },
    "next": "move-110"
  },
  {
    "id": "move-110",
    "name": "Archive the statement",
    "config": {
      "type": "MOVE_FILE_FROM_FTP",
      "sourcePath": "/banque/releves/{{ data.filename }}",
      "destinationPath": "/banque/archive/{{ data.filename }}",
      "stopWorkflowOnFailure": true
    },
    "parents": [
      "code-110"
    ],
    "position": {
      "x": 0,
      "y": 600
    },
    "next": "email-110"
  },
  {
    "id": "email-110",
    "name": "Notify the treasury team",
    "config": {
      "type": "SEND_EMAIL",
      "to": [
        "tresorerie@banque-partenaires.fr"
      ],
      "subject": "Bank statement processed: {{ data.filename }}",
      "content": "{{ context.variables.transactions.length }} transactions, balance of {{ context.variables.balance }} €, including {{ context.variables.flaggedCount }} amount(s) above 5,000 €.",
      "sendAsHtml": true
    },
    "parents": [
      "move-110"
    ],
    "position": {
      "x": 0,
      "y": 800
    }
  }
]

Check that it works​

  • CSV format: the Code node skips the header then splits each line with split(';') on date;label;amount.
  • Expected data: filePath (read) and sourcePath (move) use {{ data.filename }}; the content is loaded into context.file, the default output key of the FTP — Read node.
  • Sequence: the email is only sent once the move succeeds; with stopWorkflowOnFailure: true, an archiving failure stops the flow without notifying.
  • Deploy the workflow (Deploy a workflow), then trigger the real scenario: every execution generates a trace you can view (View traces).

Variations​

  • Adapt the separator or the structure of the CSV if the bank statement differs from the date;label;amount format.
  • If the file name is not available, list the directory with an FTP — List node then select the most recent file.
  • The output key of the FTP — Read node (context.file) is customizable in the node configuration.

Going further

See also: Examples and integrations