Rapprochement de trésorerie

Name: Rapprochement de trésorerie
Rating: 4.5 (10 reviews)
Author: Vinay Gangidi

Intermédiaire

Ceci est unContent Creation, Multimodal AIworkflow d'automatisation du domainecontenant 15 nœuds.Utilise principalement des nœuds comme Code, MistralAi, ManualTrigger, MicrosoftExcel, Agent. Automatisation de la réconciliation des factures et des relevés bancaires avec Mistral AI et OpenAI GPT-4

Prérequis

•Clé API OpenAI

Nœuds utilisés (15)

Catégorie

Création de contenu

IA Multimodale

Aperçu du workflow

Visualisation des connexions entre les nœuds, avec support du zoom et du déplacement

Lors du clic sur 'Exécuter le workflow'

Extraire le texte

Obtenir le relevé bancaire

Code en JavaScript

OpenAI Chat Model1

Obtenir transaction, correspondances, résumé

Extraire les données du fichier non structuré

Traiter les données facture vs relevé bancaire

Obtenir les données de facture

React Flow

Exporter le workflow

Copiez la configuration JSON suivante dans n8n pour importer et utiliser ce workflow

{
  "id": "n4bNr0cnmlkuN8fy",
  "meta": {
    "instanceId": "db1715da5f21adba44ce4ea3b08abb06cd1771e876f5ad2751fcafd78c5eb9dc",
    "templateCredsSetupCompleted": true
  },
  "name": "CashReconciliation",
  "tags": [
    {
      "id": "7Hqs1zOnO1KyMmlS",
      "name": "Cashreconciliation",
      "createdAt": "2025-09-25T22:03:57.474Z",
      "updatedAt": "2025-09-25T22:03:57.474Z"
    },
    {
      "id": "HdENOIIKDc5O1stL",
      "name": "Accountant",
      "createdAt": "2025-09-25T22:04:06.515Z",
      "updatedAt": "2025-09-25T22:04:06.515Z"
    },
    {
      "id": "kAdvJMsTQvyVnrF9",
      "name": "AccountReceivable",
      "createdAt": "2025-09-25T22:04:25.528Z",
      "updatedAt": "2025-09-25T22:04:25.528Z"
    },
    {
      "id": "lsoR6uHgfiOrR6C6",
      "name": "OrdertoCash",
      "createdAt": "2025-09-25T22:04:29.775Z",
      "updatedAt": "2025-09-25T22:04:29.775Z"
    },
    {
      "id": "uKun50piys98JE3C",
      "name": "Invoices",
      "createdAt": "2025-09-18T00:47:51.695Z",
      "updatedAt": "2025-09-18T00:47:51.695Z"
    }
  ],
  "nodes": [
    {
      "id": "451c356d-2215-4c59-8f92-40678b080c2b",
      "name": "Lors du clic sur 'Exécuter le workflow'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        224,
        0
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "8e42332e-f1cb-41f8-a0e1-f37b6ab3e75a",
      "name": "Extraire le texte",
      "type": "n8n-nodes-base.mistralAi",
      "position": [
        1344,
        0
      ],
      "parameters": {
        "options": {
          "batch": false
        },
        "binaryProperty": "Data"
      },
      "credentials": {
        "mistralCloudApi": {
          "id": "<Mistral OCR API KEY>",
          "name": "Mistral Cloud account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "a8014539-db77-4ade-97c5-d42077119291",
      "name": "Obtenir le relevé bancaire",
      "type": "n8n-nodes-base.microsoftOneDrive",
      "position": [
        896,
        0
      ],
      "parameters": {
        "fileId": "01WVQSKIIAS4II25G37JGK6QHSYCDROS76",
        "operation": "get"
      },
      "credentials": {
        "microsoftOneDriveOAuth2Api": {
          "id": "<Microsoft One Drive API KEY>",
          "name": "Microsoft Drive account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "822968f4-1066-417d-9521-d1064f511bb7",
      "name": "Code en JavaScript",
      "type": "n8n-nodes-base.code",
      "position": [
        672,
        0
      ],
      "parameters": {
        "jsCode": "// n8n Code node\n// Input: 1 item that contains `json.data` array\n// Output: one item with a single JSON array erpLedger\n\nconst data = items[0].json.data;   // all rows live here\n\nconst ledger = data\n  .map(d => {\n    const row = d.values?.[0];     // [\"Ansys\", 1, \"08-15-2025\", 5096.96]\n    if (!row || row.length < 4) return null;\n\n    return {\n      CustomerName: row[0],              // first column\n      invoice_number: row[1],            // second column\n      invoice_due_date: row[2],          // third column\n      amount: Number(row[3]),            // fourth column\n      id: String(row[1])                 // use invoice number as ID\n    };\n  })\n  .filter(r => r !== null);\n\nreturn [\n  {\n    json: {\n      erpLedger: ledger\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "3a9cc7b1-aeeb-4f5f-b61f-db5fe3dfc402",
      "name": "OpenAI Chat Model1",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        1568,
        224
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4.1-mini"
        },
        "options": {
          "temperature": 0
        }
      },
      "credentials": {
        "openAiApi": {
          "id": "<OPENAI API KEY>",
          "name": "OpenAi account 2"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "99f88b81-4e58-4a5d-a47a-67f6ba0771a4",
      "name": "Obtenir transaction, correspondances, résumé",
      "type": "n8n-nodes-base.code",
      "position": [
        1920,
        0
      ],
      "parameters": {
        "jsCode": "// n8n Code node\n// Input: one item with .json.output (string containing transactions, matches, summary)\n// Output: multiple items (one per row in the reconciliation table)\n\nconst raw = $input.first().json.output;\n\n// ---------- Step 1: Parse safely ----------\nlet transactions = [];\nlet matches = [];\nlet summary = {};\n\ntry {\n  // Normalize separators: replace triple dashes with blank lines\n  const normalized = raw.replace(/---/g, \"\\n\\n\");\n\n  // Split into JSON blocks\n  const blocks = normalized\n    .split(/\\n\\s*\\n/)\n    .map(b => b.trim())\n    .filter(Boolean);\n\n  if (blocks[0]) {\n    transactions = JSON.parse(blocks[0]);\n  }\n  if (blocks[1]) {\n    matches = JSON.parse(blocks[1]);\n  }\n  if (blocks[2]) {\n    summary = JSON.parse(blocks[2]);\n  }\n} catch (e) {\n  return [{\n    json: { error: \"Parse failed\", message: e.message, rawStart: raw.substring(0, 200) }\n  }];\n}\n\n// ---------- Step 2: Index matches by transaction_id ----------\nconst matchMap = {};\nfor (const m of matches) {\n  matchMap[m.transaction_id] = {\n    ...m,\n    // Normalize classification fields\n    unmatched_classification: m.unmatched_classification || m.classification || null\n  };\n}\n\n// ---------- Step 3: Build reconciliation rows ----------\nconst rows = [];\n\nfor (const txn of transactions) {\n  const m = matchMap[txn.transaction_id];\n\n  if (m && Array.isArray(m.matches) && m.matches.length > 0) {\n    // Matched transaction (can have multiple invoices)\n    for (const match of m.matches) {\n      rows.push({\n        \"Bank Transaction Date\": new Date(txn.date).toLocaleDateString(\"en-US\"),\n        \"Bank Transaction Description\": txn.description,\n        \"Bank Amount\": txn.amount,\n        \"ERP Invoice Number(s)\": match.invoice_number || null,\n        \"ERP Customer Name(s)\": \"N/A\",  // not provided in your JSON\n        \"ERP Amount(s)\": txn.amount,\n        \"Match Status\": \"Matched\",\n        \"Confidence Score\": match.confidence || null,\n        \"Reason\": match.reason || \"\"\n      });\n    }\n  } else {\n    // Unmatched transaction\n    rows.push({\n      \"Bank Transaction Date\": new Date(txn.date).toLocaleDateString(\"en-US\"),\n      \"Bank Transaction Description\": txn.description,\n      \"Bank Amount\": txn.amount,\n      \"ERP Invoice Number(s)\": null,\n      \"ERP Customer Name(s)\": \"N/A\",\n      \"ERP Amount(s)\": null,\n      \"Match Status\": m?.unmatched_classification || \"Unapplied\",\n      \"Confidence Score\": null,\n      \"Reason\": m?.reason || \"No match\"\n    });\n  }\n}\n\n// ---------- Step 4: Return as n8n items ----------\nreturn rows.map(r => ({ json: r }));\n"
      },
      "typeVersion": 2,
      "alwaysOutputData": true
    },
    {
      "id": "b9395a80-9e22-4e11-8e8e-85f558cc618f",
      "name": "Extraire les données du fichier non structuré",
      "type": "n8n-nodes-base.microsoftOneDrive",
      "position": [
        1120,
        0
      ],
      "parameters": {
        "fileId": "={{ $json.id }}",
        "operation": "download",
        "binaryPropertyName": "=Data"
      },
      "credentials": {
        "microsoftOneDriveOAuth2Api": {
          "id": "<Microsoft One Drive API KEY>",
          "name": "Microsoft Drive account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "464d6980-ee91-4509-b433-012adb2bcf88",
      "name": "Traiter les données facture vs relevé bancaire",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        1568,
        0
      ],
      "parameters": {
        "text": "=You are a cash reconciliation specialist.\n\nINPUT DATA:\n- Bank transactions (raw text): {{ $json.extractedText }}\n- ERP ledger entries (JSON): {{ JSON.stringify($('Code in JavaScript').item.json.erpLedger) }}\n\nTASKS\n1) Parse bank text into JSON rows with fields:\n   [{\"date\":\"YYYY-MM-DD\",\"description\":\"string\",\"amount\":number,\"currency\":\"string\",\"transaction_id\":\"string\"}]\n2) Match each bank transaction to one or more ERP entries (keys: exact amount, date ±2 days, reference similarity).\n3) Unmatched items: classify as \"unapplied\", \"suspense\", or \"needs_review\" with reasons.\n4) For partial/one-to-many matches, propose splits with allocation amounts.\n5) Provide a summary: total_txns, total_matched, total_unmatched, reconciliation_rate_pct.\n6) Add a confidence score (0–1) and a short reason for each match/split.\n\nCONSTRAINTS\n- Return JSON ONLY. No prose, no markdown.\n- Limit candidates to top 3 per transaction by confidence.\n- If best confidence < 0.6, treat as unmatched.\n- Use transaction_id and invoice numbers from the inputs.\n\nI want the output in Tabular format\n",
        "options": {},
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2.2
    },
    {
      "id": "84487907-0767-4877-89cf-d8ce56a9ac39",
      "name": "Note adhésive",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -560,
        -432
      ],
      "parameters": {
        "color": 4,
        "width": 2080,
        "height": 272,
        "content": "## **Problem Statement**\n### Cash reconciliation is one of the most time-consuming and error-prone processes for Accounts Receivable teams. Every day, specialists need to take the bank statement, scan through hundreds of line items, and manually check which transactions correspond to outstanding invoices in the ERP system. This slows down the month-end close, creates a backlog of unapplied cash, and impacts visibility into actual cash flow.\n\n## **The challenge is twofold**\n\n### Volume & Complexity – Bank statements contain dozens of deposits, withdrawals, fees, and transfers. Invoices may partially match or differ slightly in timing/amounts, making manual matching tedious.\n### Accuracy & Speed – Missing a match means open invoices stay unresolved, while mis-matches lead to reconciliation errors and corrections later in accounting."
      },
      "typeVersion": 1
    },
    {
      "id": "f02dc91c-34e1-48b5-8aca-51ad5c3001db",
      "name": "Note adhésive1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -560,
        -64
      ],
      "parameters": {
        "color": 6,
        "width": 736,
        "height": 288,
        "content": "## **Value**:\n\n### Time saved: Removes repetitive manual matching.\n### Cash flow visibility: Gives near real-time reconciliation metrics.\n### Error reduction: Uses AI confidence scoring and reasons for unmatched items.\n### Scalability: Can handle daily statement volumes without extra staff."
      },
      "typeVersion": 1
    },
    {
      "id": "5e1cd291-8e85-4869-ad70-f8a3958ac55b",
      "name": "Note adhésive2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        208,
        176
      ],
      "parameters": {
        "color": 4,
        "width": 1312,
        "height": 176,
        "content": "## ***Input***:\n\n### Open invoices are loaded from Excel.\n### Daily bank statement is fetched from OneDrive.\n### OCR extracts transaction data from the statement."
      },
      "typeVersion": 1
    },
    {
      "id": "a45de2dd-7ee6-4fa9-a167-d22ceab156e2",
      "name": "Note adhésive3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1712,
        240
      ],
      "parameters": {
        "color": 4,
        "width": 784,
        "height": 240,
        "content": "## ***AI Processing***:\n\n### Both invoice data and bank transactions are passed into an OpenAI Chat model.\n### The model evaluates and returns:\n Transaction → Invoice matches\n Confidence scores\n Unmatched transactions with reasons\n Summary metrics (total matched, unmatched, reconciliation %)."
      },
      "typeVersion": 1
    },
    {
      "id": "123cec6b-f238-4a07-a75f-2646c3ce0106",
      "name": "Note adhésive4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        208,
        368
      ],
      "parameters": {
        "color": 4,
        "width": 1328,
        "height": 496,
        "content": "## ***Post-Processing***:\n\n### Custom code nodes parse the AI output.\n### Results are converted into a structured table with columns like:\n\nBank Transaction Date\nDescription\nAmount\nERP Invoice Number(s)\nERP Customer Name(s)\nERP Amount(s)\nMatch Status\nConfidence Score\nReason\n\n## ***Output***:\n\n### The AR specialist sees a ready-made reconciliation table showing exactly which invoices can be closed in the ERP and which need further review. This reduces manual effort, improves reconciliation accuracy, and accelerates cash application."
      },
      "typeVersion": 1
    },
    {
      "id": "38449472-1724-44cc-aa6b-af80c8eaeb6b",
      "name": "Obtenir les données de facture",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        448,
        0
      ],
      "parameters": {
        "table": {
          "__rl": true,
          "mode": "list",
          "value": "{6220E30B-55BD-614F-AA73-5C275D263361}",
          "cachedResultUrl": "https://netorg17303936x-my.sharepoint.com/personal/vinay_optinext_ca/_layouts/15/Doc.aspx?sourcedoc=%7B3B366271-85B1-4FF7-9FE1-BDD145027E90%7D&file=CashARData.xlsx&action=default&mobileredirect=true&DefaultItemOpen=1&activeCell=Sheet1!A1:D51",
          "cachedResultName": "Table1"
        },
        "filters": {},
        "rawData": true,
        "resource": "table",
        "workbook": {
          "__rl": true,
          "mode": "list",
          "value": "01WVQSKILRMI3DXMMF65HZ7YN52FCQE7UQ",
          "cachedResultUrl": "https://netorg17303936x-my.sharepoint.com/personal/vinay_optinext_ca/_layouts/15/Doc.aspx?sourcedoc=%7B3B366271-85B1-4FF7-9FE1-BDD145027E90%7D&file=CashARData.xlsx&action=default&mobileredirect=true&DefaultItemOpen=1",
          "cachedResultName": "CashARData"
        },
        "operation": "getRows",
        "returnAll": true,
        "worksheet": {
          "__rl": true,
          "mode": "list",
          "value": "{A31DAD5C-F7E0-2A4B-B868-E4B2444E9398}",
          "cachedResultUrl": "https://netorg17303936x-my.sharepoint.com/personal/vinay_optinext_ca/_layouts/15/Doc.aspx?sourcedoc=%7B3B366271-85B1-4FF7-9FE1-BDD145027E90%7D&file=CashARData.xlsx&action=default&mobileredirect=true&DefaultItemOpen=1&activeCell=Sheet1!A1",
          "cachedResultName": "Sheet1"
        }
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "<Microsoft Account API KEY>",
          "name": "Microsoft Excel account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "7c23ec26-e63b-4dfe-b82b-79b5a892c91d",
      "name": "Note adhésive5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -560,
        272
      ],
      "parameters": {
        "color": 2,
        "width": 704,
        "height": 176,
        "content": "***Possible Enhancements***: \n\n1. Getting Invoice data from Data Table such as Snowflake, Databricks\n2. Getting Bank Statement from Bank accounts directly \n3. Posting the Data back to either ERP Systems or Data based with Matched Invoices to update the cash flow. "
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "983d50ae-2007-4b12-9645-838649f3db28",
  "connections": {
    "8e42332e-f1cb-41f8-a0e1-f37b6ab3e75a": {
      "main": [
        [
          {
            "node": "464d6980-ee91-4509-b433-012adb2bcf88",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "38449472-1724-44cc-aa6b-af80c8eaeb6b": {
      "main": [
        [
          {
            "node": "822968f4-1066-417d-9521-d1064f511bb7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "822968f4-1066-417d-9521-d1064f511bb7": {
      "main": [
        [
          {
            "node": "a8014539-db77-4ade-97c5-d42077119291",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a8014539-db77-4ade-97c5-d42077119291": {
      "main": [
        [
          {
            "node": "b9395a80-9e22-4e11-8e8e-85f558cc618f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3a9cc7b1-aeeb-4f5f-b61f-db5fe3dfc402": {
      "ai_languageModel": [
        [
          {
            "node": "464d6980-ee91-4509-b433-012adb2bcf88",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "99f88b81-4e58-4a5d-a47a-67f6ba0771a4": {
      "main": [
        []
      ]
    },
    "451c356d-2215-4c59-8f92-40678b080c2b": {
      "main": [
        [
          {
            "node": "38449472-1724-44cc-aa6b-af80c8eaeb6b",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b9395a80-9e22-4e11-8e8e-85f558cc618f": {
      "main": [
        [
          {
            "node": "8e42332e-f1cb-41f8-a0e1-f37b6ab3e75a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "464d6980-ee91-4509-b433-012adb2bcf88": {
      "main": [
        [
          {
            "node": "99f88b81-4e58-4a5d-a47a-67f6ba0771a4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Foire aux questions

Comment utiliser ce workflow ?

Copiez le code de configuration JSON ci-dessus, créez un nouveau workflow dans votre instance n8n et sélectionnez "Importer depuis le JSON", collez la configuration et modifiez les paramètres d'authentification selon vos besoins.

Dans quelles scénarios ce workflow est-il adapté ?

Intermédiaire - Création de contenu, IA Multimodale

Est-ce payant ?

Ce workflow est entièrement gratuit et peut être utilisé directement. Veuillez noter que les services tiers utilisés dans le workflow (comme l'API OpenAI) peuvent nécessiter un paiement de votre part.