Building an Automated Financial Report Generation Workflow with Polars, LLM, and Excel: Optimizing Financial Data Analysis and Visualization

The era of manually fiddling with Excel is over. This article introduces how to build a workflow that automates financial data analysis and generates customized reports by combining Polars, LLM, and Excel. From data analysis to report writing, we reveal key strategies to maximize efficiency.

1. The Challenge / Context

Generating financial reports is a time-consuming and repetitive task. Manually extracting, cleaning, analyzing, and visualizing data in Excel is prone to errors and wastes valuable time. Especially when immediate analysis and decision-making are required to adapt to rapidly changing market conditions, such inefficiencies act as a significant constraint. Existing solutions are often complex, expensive, or lack flexibility, failing to meet all requirements. Many businesses and individual investors are currently seeking powerful, flexible, and cost-effective solutions for data analysis and report automation.

2. Deep Dive: Polars

Polars is an extremely fast DataFrame library written in Rust. It offers significantly superior performance compared to traditional Pandas, especially demonstrating its true value when processing large datasets. Polars inherently supports parallel processing and maximizes data processing speed through efficient memory management. Its Lazy Evaluation feature performs only necessary calculations, reducing unnecessary operations and optimizing memory usage. Importantly, Polars provides an API similar to Pandas, making it easy for users familiar with Pandas to transition to Polars.

3. Step-by-Step Guide / Implementation

Now, let's take a detailed look at the steps to build an automated financial report generation workflow by integrating Polars, LLM, and Excel. This workflow consists of 5 stages: data extraction, data analysis, report generation, visualization, and Excel file creation.

Step 1: Data Extraction (Data Extraction)

First, you need to extract the financial data required for analysis. Data sources can vary, including Excel files, CSV files, databases, or APIs. This example assumes data is imported from a CSV file.

import polars as pl

# Read CSV file
df = pl.read_csv("financial_data.csv")

# Check data
print(df.head())
    

Step 2: Data Analysis (Data Analysis)

Analyze data using Polars. For example, you can perform tasks such as calculating revenue changes over a specific period, cost analysis, and return on investment.

# Calculate Profit (Revenue - Expenses)
df = df.with_columns(
    (pl.col("Revenue") - pl.col("Expenses")).alias("Profit")
)

# Calculate average profit
average_profit = df["Profit"].mean()
print(f"Average Profit: {average_profit}")

# Calculate monthly profit sum
monthly_profit = df.group_by("Month").agg(pl.col("Profit").sum())
print(monthly_profit)
    

Step 3: Generating Report Draft using LLM

Now, use an LLM (Large Language Model) to generate a report draft based on the data analysis results. You can use an LLM such as OpenAI's GPT model. Provide the LLM with an appropriate prompt to instruct it to generate a report in the desired format.

import openai

# Set OpenAI API key
openai.api_key = "YOUR_OPENAI_API_KEY"

# Generate prompt
prompt = f"""
Analyze the following data and generate a financial report draft.

Data:
{monthly_profit}

Report Format:
- Summary: Summary of monthly profit trends and key characteristics
- Analysis: Analysis of profit fluctuation factors
- Conclusion: Investment strategy proposal
"""

# Call LLM
response = openai.Completion.create(
    engine="text-davinci-003", # Select GPT-3.5 model
    prompt=prompt,
    max_tokens=500,
    n=1,
    stop=None,
    temperature=0.7,
)

# Output report draft
report_draft = response.choices[0].text.strip()
print(report_draft)
    

Important: You need to obtain an OpenAI API key and set it in your environment variables. Additionally, you should select an appropriate model considering its performance and cost. More powerful models like GPT-4 can be used, but they come with higher costs.

Step 4: Data Visualization (Data Visualization)

Visualize data using libraries like Matplotlib or Plotly. Visualization helps convey reports more effectively.

import matplotlib.pyplot as plt

# Visualize monthly profit
plt.plot(monthly_profit["Month"], monthly_profit["Profit"])
plt.xlabel("Month")
plt.ylabel("Profit")