Automate Financial Performance Analysis with n8n & MySQL

1. Opening Problem Statement

Meet Sarah, the Finance Manager at Syncbricks LLC, an AI and Automation company. Every month, on the 5th, Sarah faces the daunting task of compiling and analyzing financial data from various departments—cost centers, projects, budgets, and employee statistics—to prepare a clear performance report. She spends hours manually querying data, cross-referencing budgets versus actuals, and trying to summarize insights that her business managers can trust for decision-making.

The complexity grows with each additional cost center, making it nearly impossible to maintain accuracy and timeliness without help. Delays cost the company valuable time in responding to financial risks, and inaccurate reporting could lead to misguided budget decisions harming profitability. Sarah needs a reliable way to automate her monthly financial reporting workflow to save time and reduce errors.

2. What This Automation Does

This n8n workflow tackles Sarah’s problem head-on by fully automating the monthly financial performance report creation for Syncbricks LLC. On the 5th of each month, it triggers and performs a series of automated processes that:

• Dynamically calculates the previous month and fiscal year for accurate reporting periods.
• Fetches relevant cost centers and budget data directly from MySQL ERPNext tables.
• Loops through each cost center to gather detailed Year-To-Date (YTD) and Previous Month (PM) budget versus actual financial data.
• Pulls detailed project and Work In Process (WIP) information, including contract values, costs, and invoice percentages.
• Retrieves employee counts and new hire data to calculate per-employee financial metrics.
• Generates visually rich, consolidated HTML reports combining all these financial insights.
• Leverages Google Gemini AI to analyze the financial data, producing an executive summary, profitability analysis, and recommendations.
• Automatically emails the comprehensive performance report via Microsoft Outlook to key stakeholders.

This automation saves Sarah and her team upwards of 10+ hours monthly, eliminates manual SQL querying errors, and empowers business managers with timely, expert-level financial analysis without any manual intervention.

3. Prerequisites ⚙️

• n8n account with access to the Community or Pro version (for external nodes and AI integration).
• MySQL database access with ERPNext schema (or similar accounting/finance data schema).
• Microsoft Outlook account configured for sending emails.
• Google Gemini AI account set up for advanced natural language analysis.
• Basic credentials for MySQL, Outlook, and AI nodes configured in n8n (🔑 credentials).

4. Step-by-Step Guide

Step 1: Set Monthly Schedule Trigger

Navigate to n8n editor → Add Schedule Trigger node → Set interval to trigger at the 5th day of every month under “rule” → Save.

This node activates the workflow automatically each month, ensuring the financial report is always fresh.

Common mistake: Forgetting to adjust the timezone may cause reports to trigger on the wrong day.

Step 2: Capture Current Date and Calculate Previous Month

Add a Date & Time node → Connect from Schedule Trigger. Then add a Code node named PreviousMonth with JavaScript to calculate the previous month’s number and year from the current date:

// Get the input date from the previous node
const inputDateStr = $input.first().json.currentDate;
const inputDate = new Date(inputDateStr);

// Move to the first day of the current month
inputDate.setDate(1);

// Step back one day to land in the previous month
inputDate.setDate(0);

// Extract previous month and year
const previousMonth = inputDate.getMonth() + 1; // Months are 0-based
const year = inputDate.getFullYear();

return [
  {
    json: {
      previousMonth: previousMonth.toString().padStart(2, '0'),
      year: year.toString()
    }
  }
];

This ensures all SQL queries filter the correct historical period dynamically.

Step 3: Retrieve Cost Centers with Budgets

Add a MySQL node named Get Cost Centers with Budgets → Use SQL query to find cost centers with budget data and GL entries up to the previous month and year from Step 2.

The query filters for cost centers with active budgets in the selected period, reducing unnecessary data processing.

Step 4: Filter for Specific Cost Center (Optional for Testing)

Connect a Filter node to narrow down the workflow to a certain cost center (like “AI DEPARTMENT”). This is useful during setup and previews.

Step 5: Loop Through Each Cost Center

Use the Split in Batches node to iterate over each cost center found. This enables generating reports one-by-one, scalable for multiple divisions.

Step 6: Calculate Previous Month’s and YTD Budget vs Actuals

Add a MySQL node called YTD vs Previous Month1. This node runs a complex SQL query joining budget and GL actual data per cost center, returning grouped financial data such as Budget YTD, Actual YTD, Variance YTD, Budget PM, Actual PM, and Variance PM.

The node references values from the PreviousMonth node and the current cost center dynamically.

Step 7: Fetch Departments (Verticals) Financial Performance

Add another MySQL node Departments to get vertical profit & loss data per cost center for the previous month and year. This helps break down business units further.

Step 8: Fetch Project Data and Calculate WIP

Create a MySQL node named Projects that calculates project counts, contract values, revenue, cost, invoice %, cost %, and WIP for each cost center.

Follow up with a Code node WIP1 to format fetched project data into an HTML table.

Step 9: Retrieve Employee Statistics

Add a MySQL node Employees to get total employees, new hires this year/month grouped by payroll cost center. Convert this into HTML table with a Code node Employees1.

Step 10: Prepare Financial Data Tables in HTML

Use Code nodes CostCenter and verticalPL to transform query results into clean HTML tables for the final formatted report.

Step 11: Merge All Data Tables

Use a Merge node to combine all HTML tables from the previous steps into one data stream.

Step 12: Generate Final HTML Report

Add a Code node that takes all merged tables and wraps them into a beautifully styled, responsive HTML report with headings, CSS styling, and distinct sections for each financial aspect.

Step 13: Analyze Financial Performance with AI

Add the Business Performance AI Agent (Analyst) node using Google Gemini chat with a detailed prompt. It composes an expert-level analysis, executive summary, profit & loss statement calculations, and recommendations, based on the HTML financial input.

Step 14: Clean Up AI-Generated HTML Output

Use a small Code node Financial Performance to remove any markdown artifacts and ensure clean HTML formatting for email.

Step 15: Set Email Content and Subject

Add a Set node to define the email body from AI output and include context like the selected cost center and period.

Step 16: Send Email Report Using Microsoft Outlook

Finally, use Microsoft Outlook node to email the full HTML financial report to the decision makers (e.g., finance team and business managers).

Step 17: Add Wait Node for Scaling

Use a Wait node with a short delay to prevent email spamming and allow scalable processing of multiple cost centers.

5. Customizations ✏️

• Add Multi-Cost Center Reporting: Remove or adjust the Filter node to generate reports for all cost centers instead of just one, enabling company-wide insights across divisions.
• Customize Email Recipients: Change the recipient email address in the Microsoft Outlook node to share reports with different stakeholders or distribution lists.
• Modify SQL Queries to Include Additional Metrics: For example, add columns in the YTD vs Previous Month1 node to fetch sub-accounts or specific expense categories for more granular analysis.
• Adjust Report Styling: Edit the final Code node HTML template to add branding colors, logos, or rearrange sections for your company’s visual identity.
• Change Schedule Trigger Timing: Set the Schedule Trigger to a different day/time to match your internal reporting deadlines.

6. Troubleshooting 🔧

Problem: MySQL node throws “Unknown column” errors

Cause: Column names or table aliases in SQL queries do not match your database schema.

Solution: Check and update your SQL queries in the MySQL nodes to correspond exactly to your ERPNext or financial database schema. Confirm table and column names via your database interface.

Problem: Email not sent or Outlook node shows authentication errors

Cause: Incorrect Microsoft Outlook credentials or permissions.

Solution: Reconnect the Outlook node with proper multi-factor authentication or app password as required. Test sending a simple email first.

Problem: AI analysis returns empty or malformed HTML

Cause: Input HTML to the AI node is incomplete or has formatting issues from earlier nodes.

Solution: Verify the merged HTML tables are correctly formatted, and the Code node sanitizing outputs is present. Use debug mode in n8n to inspect data at each node.

7. Pre-Production Checklist ✅

• Verify all credential nodes (MySQL, Outlook, AI) are correctly configured.
• Test with the filter node enabled for a single cost center before expanding to all.
• Run the workflow manually for the current or previous month and check SQL results for accuracy.
• Ensure the AI node produces a complete HTML report with proper financial analysis sections.
• Confirm the email sends successfully and formatting appears well in recipients’ inbox.
• Back up your existing data and workflows before deploying live.

8. Deployment Guide

Activate the workflow by turning on the Schedule Trigger node in n8n. Confirm the workflow runs automatically on the 5th of each month.

Monitor email delivery logs and use n8n’s execution logs for any errors or performance issues. Adjust the wait time between batch processing if running on multiple cost centers.

For scaling, consider self-hosting n8n to handle higher volumes and secure integrations. You can explore hosting options at buldrr.com/hostinger.

9. FAQs

Can I replace MySQL with PostgreSQL or another database?

Yes. As long as your database schema is compatible, just adjust the SQL syntax accordingly in the MySQL nodes.

Does the AI analysis consume additional quota or credits?

Yes. The Google Gemini chat service uses API credits, so monitor usage especially for large reports.

Is the financial data secure in this process?

All connections should use secure credentials and SSL where supported. Avoid sending sensitive data unencrypted via email.

10. Conclusion

By implementing this detailed n8n workflow, Sarah has automated her monthly financial reporting, turning a 10+ hour manual process into a hands-free, reliable, and insightful monthly ritual. Managers now receive expert-level analysis enhanced by AI, with clear visuals and well-structured reports, enabling better and faster financial decisions.

Try expanding this workflow to include more granular department breakdowns, integrate forecasting, or add alerting based on financial thresholds to further increase value and responsiveness.

Automation and AI are powerful allies in finance — harness them with this workflow and see your business performance insights soar!