What is a TreasuryBot?

TreasuryBot is a low-cost, no-code automation tool designed to help SMEs streamline their cash flow management, payments, and reconciliations without requiring a Treasury Management System (TMS) or ERP module. It works using Excel, Google Sheets, APIs, and chat-based automation to make treasury operations more efficient.

Why SMEs Need TreasuryBot

SMEs struggle with:

• Manual treasury processes – Copy-pasting bank balances, tracking payments in spreadsheets.
• Lack of visibility – No real-time cash position available.
• Time-consuming reconciliations – Matching payments manually to invoices.

TreasuryBot solves these problems by automating key treasury tasks inside Excel and Google Sheets, without expensive software or IT involvement.

---

Step-by-Step Guide to Building TreasuryBot

The goal is to create a TreasuryBot that:

✅ Automates cash flow tracking (real-time balances, transactions).
✅ Prepares payment files for bank uploads.
✅ Performs bank reconciliation automatically.
✅ Sends alerts for low balances and due payments.

---

Step 1: Set Up the Treasury Dashboard in Excel

You’ll use Excel (or Google Sheets) to create a centralized dashboard for cash visibility.

1️⃣ Create an Excel File for TreasuryBot

Open Excel and create a new workbook named TreasuryBot.xlsx.
Create the following worksheets:

• Bank_Balances – Stores real-time bank balances.
• Bank_Transactions – Logs incoming/outgoing transactions.
• Invoices_Payables – Tracks supplier payments.
• Payment_Files – Generates payment files (CSV/XML).
• Reconciliation – Matches bank transactions to invoices.

---

Step 2: Automate Bank Balance & Transaction Import

You need real-time cash visibility. TreasuryBot can pull bank data automatically using APIs (if available) or CSV-based imports.

🔹 Option 1: Fetch Bank Data via API (Preferred)

If your bank provides API access, you can automate bank data retrieval into Excel using Power Automate.

1️⃣ Check if your bank supports Open Banking APIs
Most banks provide API access. Common providers:

• Plaid (US, EU, UK)
• Nordigen (EU, Free)
• TrueLayer (UK, EU)
• Tink (Europe)

2️⃣ Register for API Access
Your bank will provide:

• Endpoint URL (e.g., https://api.bank.com/balances)
• Authentication method (OAuth, API Key)

3️⃣ Use Power Automate to Fetch Bank Balances

• Open Power Automate → New Flow → Scheduled Flow (Daily).
• Trigger: Set to run at 8 AM.
• Action: Use the HTTP request method:json{ "Authorization": "Bearer YOUR_API_KEY", "Content-Type": "application/json" }
• Parse JSON Response to extract balances.
• Update Excel Row in Bank_Balances sheet.

✅ Result: TreasuryBot automatically updates real-time balances in Excel without manual intervention.

🔹 Option 2: Semi-Automated CSV Upload (For Banks Without APIs)

Some banks send daily CSV/Excel statements via email. TreasuryBot can automatically import them.

1️⃣ Set Up an Email Rule in Outlook

• Create a rule to auto-save bank statements to a folder.

2️⃣ Use Power Query to Load CSV Automatically

• Excel → Data → Get Data → From Folder
• Select Bank Statement Folder where CSVs are saved.
• Power Query cleans and loads data into Bank_Transactions.

✅ Result: TreasuryBot updates bank transactions every time a new file arrives.

---

Step 3: Automate Payment File Generation

Most banks accept CSV/XML payment files for bulk payments. TreasuryBot will generate bank-compatible payment files.

1️⃣ Create a Payment File Format in Excel

In the Payment_Files sheet, create these headers:

Beneficiary Name	IBAN	Amount	Currency	Payment Date	Reference

Use VLOOKUP/XLOOKUP to pull data from Invoices_Payables.

2️⃣ Export Payment Files (CSV/XML) with a VBA Macro

This macro will generate a payment file for bank uploads:

vbaSub Export_PaymentFile()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Payment_Files")
    
    Dim filePath As String
    filePath = ThisWorkbook.Path & "\PaymentFile_" & Format(Now, "YYYYMMDD") & ".csv"

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim rng As Range
    Set rng = ws.Range("A1:F" & lastRow)
    
    Open filePath For Output As #1
    Dim rowContent As String
    
    For Each cell In rng.Rows
        rowContent = Join(Application.Transpose(Application.Transpose(cell.Value)), ",")
        Print #1, rowContent
    Next cell
    
    Close #1
    MsgBox "Payment file exported successfully: " & filePath
End Sub

✅ Result: TreasuryBot exports CSV/XML files ready for bank upload.

---

Step 4: Automate Bank Reconciliation

Reconciliation is time-consuming. TreasuryBot can auto-match payments with invoices.

1️⃣ Load Bank Transactions & Invoices into Power Query

• Go to Excel → Data → Get Data → From Table/Range (Bank_Transactions, Invoices_Payables).
• Merge Queries using LEFT JOIN (match Invoice Number & Amount).
• Enable Fuzzy Matching for partial matches.
• If matched → Mark as PAID
  If unmatched → Flag for review

✅ Result: TreasuryBot auto-reconciles payments, reducing manual work.

---

Step 5: Automate Alerts via Email/Teams

TreasuryBot will send automatic alerts for:

✅ Low cash balances
✅ Upcoming payments

1️⃣ Create a Low Cash Alert

• Open Power Automate → New Flow → Automated Cloud Flow.
• Trigger: “When an Excel row is modified” (Bank_Balances).
• Condition: If balance < $5,000, send an alert.
• Action 1: Send an email via Outlook.
• Action 2: Post a message in Microsoft Teams.

2️⃣ Set Up Payment Due Reminders

• Trigger: “When an invoice due date is 3 days away.”
• Action: Send an email to CFO & Treasury team.

✅ Result: TreasuryBot ensures no payments are missed.

---

Final TreasuryBot Features

✅ Automates cash flow tracking (via API/CSV import).
✅ Prepares bank-compatible payment files.
✅ Performs reconciliation automatically.
✅ Sends real-time alerts.

🚀 Why TreasuryBot Works

✅ No expensive TMS required.
✅ Works inside Excel & Google Sheets.
✅ No /Low IT / coding needed.
✅ Uses Open Banking APIs for real-time data.

---

🚀 Summary: Best Method for Your SME

Method	Pros	Cons
Bank API (Power Automate + Excel)	✅ Real-time data, fully automated	❌ Some banks charge for API access
CSV Upload + Power Query	✅ Free, easy to implement	❌ No real-time updates
Web Scraping (VBA)	✅ Works when API is unavailable	❌ Security risks, can break with website updates

---