Building an Automated Financial Report Dashboard with Streamlit and n8n: From Data Collection to Visualization

Stop manually creating financial reports buried in Excel. Use n8n to automatically collect financial data from various sources and build an interactive dashboard with Streamlit to visualize key metrics in real-time. This combination maximizes efficiency and is a game-changer for data-driven decision-making.

1. The Challenge / Context

Many startups, small businesses, and sole proprietors struggle with creating financial reports. They spend a lot of time manually collecting, organizing, and analyzing data using Excel spreadsheets. This is not only inefficient but also prone to errors. Furthermore, the lack of real-time updated data makes it difficult to respond appropriately to rapidly changing market conditions. For example, integrating data from various online sales platforms (Shopify, Amazon Seller, Google Ads, etc.) and collecting bank account information and card transaction details to understand the overall financial status is a very complex and time-consuming task.

2. Deep Dive: n8n and Streamlit

n8n is a node-based low-code automation platform. It allows you to connect various services and applications to build and automate workflows. It is particularly powerful for extracting, transforming, and loading (ETL) data using APIs. Streamlit is an open-source framework based on Python, which helps you easily and quickly build interactive web applications for data science and machine learning models. You can create beautiful dashboards with just Python scripts, without complex HTML, CSS, or JavaScript coding.

n8n builds the backend data pipeline, and Streamlit handles the frontend visualization, making them an ideal combination.

3. Step-by-Step Guide / Implementation

The following is a step-by-step guide to building an automated financial report generation dashboard using n8n and Streamlit.

Step 1: Building an n8n Workflow - Collecting Shopify Data

Create an n8n workflow to collect sales data using the Shopify API. First, you need to create a private app in your Shopify account and secure your API key and password. Next, use an HTTP Request node in n8n to send a request to the Shopify API endpoint.


{
  "nodes": [
    {
      "parameters": {
        "requestMethod": "GET",
        "url": "https://{your_shop_name}.myshopify.com/admin/api/2023-10/orders.json",
        "options": {
          "authentication": "basicAuth",
          "basicAuthUser": "{your_api_key}",
          "basicAuthPassword": "{your_api_password}"
        },
        "responseFormat": "json"
      },
      "name": "Shopify - Get Orders",
      "type": "n8n-nodes-http-request",
      "typeVersion": 1,
      "position": [
        200,
        200
      ]
    },
        {
      "parameters": {
          "jsCode": "items.forEach(item => {\n  item.json.created_at_formatted = new Date(item.json.created_at).toLocaleDateString('ko-KR');\n});\n\nreturn items;"
      },
      "name": "Format Dates",
      "type": "n8n-nodes-function",
      "typeVersion": 1,
      "position": [
          400,
          200
      ]
    }
  ],
  "connections": {
      "Shopify - Get Orders": {
          "main": [
              [
                  "Format Dates",
                  0
              ]
          ]
      }
  }
}

In the code above, `{your_shop_name}`, `{your_api_key}`, and `{your_api_password}` should be replaced with actual values. The "Format Dates" node processes the data and converts it to the Korean date format.

Step 2: Building an n8n Workflow - Saving Data to Google Sheets

Save the collected Shopify data to Google Sheets. Use a Google Sheets node in n8n to add the data. First, you need to enable the Google Sheets API and create a service account key. Then, in the Google Sheets node, authenticate using the service account key and specify the spreadsheet ID and sheet name to add the data.


{
  "nodes": [
    {
      "parameters": {
        "spreadsheetId": "{your_spreadsheet_id}",
        "sheetName": "{your_sheet_name}",
        "operation": "append",
        "rawData": "={{ $json[\"line_items\"] ? $json[\"line_items\"].map(item => [item.name, item.quantity, item.price]).flat() : [] }}",
        "options": {}
      },
      "name": "Google Sheets - Append Data",
      "type": "n8n-nodes-google-sheets",
      "typeVersion": 1,
      "position": [
        600,
        200
      ]
    }
  ],
  "connections": {
    "Format Dates": {
      "main": [
        [
          "Google Sheets - Append Data",
          0
        ]
      ]
    }
  }
}

In the code above, `{your_spreadsheet_id}` and `{your_sheet_name}` should be replaced with actual values. The "Google Sheets - Append Data" node adds Shopify order data to the Google Sheets sheet. The `rawData` parameter transforms the data to fit the spreadsheet format.

Step 3: Building a Streamlit App - Data Visualization

Use Streamlit to fetch Google Sheets data and create a dashboard. The `streamlit` and `pandas` libraries must be installed.


import streamlit as st
import pandas as pd
import gspread
from google.oauth2.service_account import Credentials

# Google Sheets API credentials
scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly']
creds = Credentials.from_service_account_file('path/to/your/service_account.json', scopes=scopes)
gc = gspread.service_account(filename='path/to/your/service_account.json') # Using gspread.service_account

# Spreadsheet ID and sheet name
spreadsheet_id = "{your_spreadsheet_id}"
sheet_name = "{your_sheet_name}"

# Function to fetch data from Google Sheets
@st.cache_data
def load_data():
    sheet = gc.open_by_key(spreadsheet_id).worksheet(sheet_name)
    data = sheet.get_all_records()
    return pd.DataFrame(data)

# Load data
df = load_data()

# Calculate total revenue
total_revenue = df['price'].sum()

# Display total revenue
st.title("Financial Report Dashboard")
st.metric("Total Revenue", f"${total_revenue:,.2f}") # Format as currency

# Display data table
st.subheader("Sales Data")
st.dataframe(df)

# Create a bar chart of product sales (example)
product_sales = df.groupby('name')['quantity'].sum()
st.bar_chart(product_sales)

In the code above, `path/to/your/service_account.json`, `{your_spreadsheet_id}`, and `{your_sheet_name}` should be replaced with actual values. The `load_data` function reads data from Google Sheets and converts it into a pandas DataFrame. Streamlit features are used to display total revenue, a data table, and a bar chart of product sales.

To run the Streamlit app, execute the command `streamlit run your_app_name.py` in the terminal.

Step 4: n8n Scheduling

Schedule the n8n workflow to regularly collect and update data. You can set the workflow execution frequency using the "Cron" trigger in the n8n interface. For example, you can set the workflow to run at midnight every day to reflect the latest data on the dashboard.


{
  "nodes": [
    {
      "parameters": {
        "cronExpression": "0 0 * * *"
      },
      "name": "Cron",
      "type": "n8n-trigger",
      "typeVersion": 1,
      "position": [
        100,
        200
      ]
    }
  ],
  "connections": {
    "Cron": {
      "main": [
        [
          "Shopify - Get Orders",
          0
        ]
      ]
    }
  }
}

In the code above, `cronExpression` indicates the workflow execution frequency. `0 0 * * *` means midnight every day.

4. Real-world Use Case / Example

I personally built this workflow to integrate data from various online sales platforms. Previously, I spent over 5 hours a week on Excel tasks, but now, by using n8n and Streamlit to automatically collect and visualize data, I have significantly saved time. Furthermore, by understanding real-time changing financial data, I have been able to adjust marketing strategies more effectively.

For example, I was able to quickly detect a sharp decline in sales of a specific product through the real-time dashboard and immediately modify advertising campaigns to prevent further sales reduction.

5. Pros & Cons / Critical Analysis

Pros:
- Time savings through automated data collection and processing
- Faster decision-making through real-time data visualization
- Ability to integrate various data sources
- Dashboard construction with minimal code
- Excellent scalability and flexibility
Cons:
- Learning curve for n8n and Streamlit exists
- Potential Google Sheets API usage limits
- Need for API key and service account key management
- Data security considerations (especially for sensitive financial data)
- Feature limitations of n8n's free plan (number of executions, nodes, etc.)

6. FAQ

Q: I'm new to n8n, how do I get started?
A: Sign up on the official n8n website and refer to the documentation to learn how to build basic workflows. There are also many n8n tutorials on YouTube.
Q: How do I deploy a Streamlit app?
A: You can deploy it on various platforms such as Streamlit Cloud, Heroku, or AWS EC2. Streamlit Cloud is the easiest and most convenient deployment method.
Q: What measures should I take for data security?
A: Securely store API keys and service account keys, apply data encryption, and grant only the minimum necessary permissions. Additionally, it is recommended to conduct regular security audits.
Q: Can I use other databases instead of Google Sheets?
A: Yes, n8n supports various databases such as MySQL, PostgreSQL, and MongoDB. You can use appropriate database nodes to store and retrieve data.

7. Conclusion

By combining n8n and Streamlit, you can fully automate the financial report generation process and make real-time data-driven decisions. We hope this guide helps you build your own automated financial report dashboard, save time and effort, and achieve better business results. Install n8n and Streamlit right now and try using this code! Refer to the official documentation to explore more features and create your own workflows.

Building an Automated Financial Report Generation Dashboard with Streamlit and n8n: From Data Acquisition to Visualization