Skip to content

Weekly Financial Report

Pipeline 3: Weekly Financial Analysis

Retrospective visualization of spending habits over the previous 7 days is generated using a columnar dashboard format.

Trigger: Weekly (Monday at 10:00 AM)

1. Technical Analysis: API Constraint Management

The current methodology is implemented to bypass critical limitations encountered during development:

Error Description
Error 422 (Unprocessable Entity) NocoDB API rejections occurred with ISO 8601 strings and timezone-aware timestamps during created_at filtering.
Type Mismatch date column configured as Text type caused range operator (ge, le) failures due to alphabetical sorting.
SQL Injection Constraints Raw SQL function wrappers (e.g., DATE()) are not supported by the API layer, preventing server-side type casting.

2. Methodology & Logic

2.1 Smart Filter Generation

  • Role: Calculation of specific dates for the preceding 7-day period.
  • Mechanism: Dates are formatted as DD-MM-YYYY to ensure compatibility with text columns. A precise "OR" filter string is generated (e.g., (date,eq,05-02-2026)~or(date,eq,04-02-2026)...) to force exact string matching.

2.2 Data Retrieval

  • Operation: "Get Many" via NocoDB node.
  • Pagination: Enabled to ensure 100% data integrity and scalability beyond standard row limits.

2.3 Aggregation & Payload Construction

  • Processing: Summations by category and percentage weights are calculated.
  • Efficiency: Processing is restricted to rows returned by the smart filter to minimize CPU overhead.
  • Layout: JSON structure for Discord is manually constructed to enforce a 3-column "Inline" layout.

2.4 Delivery

  • Method: POST to Discord Webhook via HTTP Request node.
  • Policy: The standard Discord node UI is bypassed to ensure "Inline" toggles are correctly applied for table rendering.

3. Configuration Scripts

Script A: Filter Generator

const dates = [];
for (let i = 0; i < 7; i++) {
  const d = new Date();
  d.setDate(d.getDate() - i);
  const day = String(d.getDate()).padStart(2, '0');
  const month = String(d.getMonth() + 1).padStart(2, '0');
  const year = d.getFullYear();
  dates.push(`${day}-${month}-${year}`);
}

return {
  filter_formula: dates.map(dateStr => `(date,eq,${dateStr})`).join('~or')
};

Script B: Aggregator & Discord Formatter

const transactions = $input.all();
let categoryMap = {};
let totalSpend = 0;

transactions.forEach(item => {
  const t = item.json;
  const amount = parseFloat(t.amount || 0);
  const cat = t.category || "Uncategorized";
  if (!categoryMap[cat]) categoryMap[cat] = 0;
  categoryMap[cat] += amount;
  totalSpend += amount;
});

let colCategory = [], colAmount = [], colPercent = [];
const sorted = Object.entries(categoryMap).sort((a, b) => b[1] - a[1]);

for (const [cat, amount] of sorted) {
  const percent = totalSpend > 0 ? Math.round((amount / totalSpend) * 100) : 0;
  colCategory.push(cat);
  colAmount.push(`Rp ${amount.toLocaleString('id-ID')}`);
  colPercent.push(`${percent}%`);
}

return {
  "username": "Finance Bot",
  "embeds": [{
    "title": "Weekly Expense Report",
    "description": `**Total Spent:** Rp ${totalSpend.toLocaleString('id-ID')}\n**Transactions:** ${transactions.length}`,
    "color": 3447003,
    "timestamp": new Date().toISOString(),
    "fields": [
      { "name": "Category", "value": colCategory.join("\n") || "-", "inline": true },
      { "name": "Amount", "value": colAmount.join("\n") || "-", "inline": true },
      { "name": "%", "value": colPercent.join("\n") || "-", "inline": true }
    ]
  }]
};