Data Reconciliation with Claude Code

Use Claude Code to auto-detect discrepancies between dashboard and Excel data.

Introduction

30 Second Summary

You pull numbers from the company dashboard. Your colleague pulls numbers from their Excel report. The totals do not match. Now you spend hours manually scanning rows trying to figure out where things went wrong.

In this project, you will create two sample data files with realistic discrepancies, then use Claude Code to generate a Python script that automatically finds and explains every difference between them.

What You'll Build

A data reconciliation system that compares a CSV dashboard export against a manually compiled Excel report using a Python script generated by Claude Code.

By the end of this project, you'll have:

  • Sample data files (CSV and Excel) containing 4 types of realistic discrepancies that mirror real workplace data errors.
  • A Python reconciliation script generated entirely by Claude Code from a natural language prompt in Vietnamese.
  • A formatted summary report showing each discrepancy with its root cause and monetary impact, ready to share with your manager.
  • Secret Mission: Generate an HTML visual report with color-coded tables showing matched vs mismatched data.

Are there any prerequisites?

You need Python installed on your machine, VS Code with the Claude Code extension already installed and authenticated, and an active Claude Pro or Max subscription. If you do not have these set up yet, complete the Claude Code setup guide first.

Set Up the Project

Before you can reconcile any data, you need a workspace with the right tools and some realistic sample data to compare.

In this step, you will create a project folder, install the Python libraries needed to read CSV and Excel files, and generate two sample datasets with realistic discrepancies baked in.

In this step, get ready to:
  • Create the project folder and open it in VS Code.
  • Install pandas and openpyxl for reading CSV and Excel files.
  • Generate sample dashboard and Excel data with planted errors.
Create the project folder
  • Press **Cmd+Space** (macOS) or the **Windows** key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir doi-soat-so-lieu
cd doi-soat-so-lieu
  • Confirm the folder exists by running:
ls

The output should be empty (you just created the folder, so there are no files inside yet).

  • Open this folder in VS Code by running:
code .

VS Code opens with the doi-soat-so-lieu folder loaded in the Explorer sidebar.

VS Code did not open?

On Windows, VS Code should be on your PATH by default. If code . does not work, try reinstalling VS Code and making sure the option to add to PATH is checked during installation.

On macOS, open VS Code manually, press Cmd+Shift+P, type Shell Command: Install 'code' command in PATH, and select it. Then retry code . in your terminal.

help me get VS Code to open from the terminal

Install Python dependencies

Your reconciliation script needs two libraries: pandas for data manipulation and openpyxl for reading Excel files.

  • Open the VS Code integrated terminal by pressing Ctrl+` (macOS and Windows).
  • Install both packages by running this command:
pip install pandas==3.0.3 openpyxl==3.1.5
  • Verify the installation by running:
python -c "import pandas; import openpyxl; print('OK:', pandas.__version__, openpyxl.__version__)"

You should see OK: 3.0.3 3.1.5 in the output.

What are these packages?

pandas is the standard Python library for working with tabular data. It can read CSV files, Excel spreadsheets, and perform comparisons between datasets.

openpyxl is the engine that pandas uses behind the scenes to read and write .xlsx files. You do not import it directly. pandas detects it automatically.

Seeing a 'pip not found' error?

Try pip3 instead of pip. On some systems, the Python 3 package manager uses pip3 as its command name.

If neither works, make sure Python is installed and added to your system PATH.

help me fix my pip installation

Create and run the sample data script

Now you will create a Python script that generates two data files: a clean CSV simulating a dashboard export, and an Excel file simulating a manually compiled report with four types of real-world errors.

  • In VS Code, press Ctrl+N (Windows) or Cmd+N (macOS) to create a new file.
  • Press Ctrl+S (Windows) or Cmd+S (macOS) to save it.
  • Name the file tao_du_lieu_mau.py and save it inside the doi-soat-so-lieu folder.
  • Paste the following code into tao_du_lieu_mau.py:
import pandas as pd
import numpy as np
from datetime import datetime, timedelta

# Tao du lieu dashboard (nguon chinh xac)
np.random.seed(42)

categories = ["Dien tu", "Thoi trang", "Gia dung", "Thuc pham"]
start_date = datetime(2024, 6, 1)
dates = [start_date + timedelta(days=i) for i in range(30)]

rows = []
for date in dates:
    for cat in categories:
        base_revenue = {
            "Dien tu": 15000000,
            "Thoi trang": 8000000,
            "Gia dung": 5000000,
            "Thuc pham": 12000000,
        }[cat]
        daily_revenue = base_revenue + np.random.uniform(-3000000, 3000000)
        daily_revenue = round(daily_revenue, 2)

        # Them don hoan tra (so am) vao mot so ngay
        if np.random.random() < 0.2:
            return_amount = -abs(np.random.uniform(200000, 1500000))
            return_amount = round(return_amount, 2)
        else:
            return_amount = 0

        rows.append(
            {
                "ngay": date.strftime("%Y-%m-%d"),
                "danh_muc": cat,
                "doanh_so": daily_revenue,
                "hoan_tra": return_amount,
                "thuc_thu": round(daily_revenue + return_amount, 2),
            }
        )

df_dashboard = pd.DataFrame(rows)
df_dashboard.to_csv("dashboard_sales.csv", index=False, encoding="utf-8-sig")
print(f"Da tao dashboard_sales.csv: {len(df_dashboard)} dong")

# Tao du lieu Excel (co loi)
df_excel = df_dashboard.copy()

# Loi 1: Thieu 2 ngay cuoi (29-30/6)
df_excel = df_excel[df_excel["ngay"] < "2024-06-29"]

# Loi 2: Go sai ten danh muc "Dien tu" thanh "Dien Tu" (viet hoa chu T)
df_excel["danh_muc"] = df_excel["danh_muc"].replace("Dien tu", "Dien Tu")

# Loi 3: Khong tru don hoan tra (chi lay doanh_so, bo qua hoan_tra)
df_excel["thuc_thu"] = df_excel["doanh_so"]

# Loi 4: Lam tron so (mat phan thap phan)
df_excel["thuc_thu"] = df_excel["thuc_thu"].round(0).astype(int)
df_excel["doanh_so"] = df_excel["doanh_so"].round(0).astype(int)

# Xoa cot hoan_tra (nhan vien khong ghi nhan)
df_excel = df_excel.drop(columns=["hoan_tra"])

# Ghi ra Excel
df_excel.to_excel("bao_cao_excel.xlsx", index=False, sheet_name="Thang 6-2024")
print(f"Da tao bao_cao_excel.xlsx: {len(df_excel)} dong")
print("\nCac loi da cai vao du lieu Excel:")
print("1. Thieu ngay 29-30/6 (8 dong bi mat)")
print("2. 'Dien tu' thanh 'Dien Tu' (loi viet hoa)")
print("3. Khong tru hoan tra (thuc_thu = doanh_so)")
print("4. Lam tron tat ca so ve so nguyen")

What does this script do?

The first half generates a clean "dashboard" CSV with 120 rows of daily sales data across 4 product categories for June 2024. It includes decimal precision and occasional return/refund entries as negative amounts.

The second half copies that data and introduces 4 realistic errors to simulate a manually compiled Excel report: missing the last 2 days, a capitalization typo in one category name, ignoring returns, and rounding all numbers to integers.

  • Save tao_du_lieu_mau.py with Ctrl+S (Windows) or Cmd+S (macOS).
  • Run the script in the VS Code integrated terminal:
python tao_du_lieu_mau.py

You should see output confirming both files were created:

Da tao dashboard_sales.csv: 120 dong
Da tao bao_cao_excel.xlsx: 112 dong

Cac loi da cai vao du lieu Excel:
1. Thieu ngay 29-30/6 (8 dong bi mat)
2. 'Dien tu' thanh 'Dien Tu' (loi viet hoa)
3. Khong tru hoan tra (thuc_thu = doanh_so)
4. Lam tron tat ca so ve so nguyen

Why 120 rows vs 112 rows?

The dashboard has 4 categories x 30 days = 120 rows. The Excel file is missing June 29 and 30 (2 days x 4 categories = 8 rows removed), leaving 112 rows. This is the first planted discrepancy your reconciliation script will detect later.

  • Confirm both files appeared by running:
ls

You should see bao_cao_excel.xlsx, dashboard_sales.csv, and tao_du_lieu_mau.py in the output.

Seeing a ModuleNotFoundError?

If you see ModuleNotFoundError: No module named 'pandas', the packages did not install into the same Python that runs your script. Try running pip3 install pandas==3.0.3 openpyxl==3.1.5 instead, or use python3 tao_du_lieu_mau.py to run the script.

help me fix my ModuleNotFoundError for pandas

✔️ Awesome, I've got everything!

Great. Double check you have saved tao_du_lieu_mau.py and that dashboard_sales.csv and bao_cao_excel.xlsx both appear in your Explorer sidebar.

ⓧ I'd like to double check the full code

Here is the complete tao_du_lieu_mau.py file. Make sure yours matches exactly:

import pandas as pd
import numpy as np
from datetime import datetime, timedelta

# Tao du lieu dashboard (nguon chinh xac)
np.random.seed(42)

categories = ["Dien tu", "Thoi trang", "Gia dung", "Thuc pham"]
start_date = datetime(2024, 6, 1)
dates = [start_date + timedelta(days=i) for i in range(30)]

rows = []
for date in dates:
    for cat in categories:
        base_revenue = {
            "Dien tu": 15000000,
            "Thoi trang": 8000000,
            "Gia dung": 5000000,
            "Thuc pham": 12000000,
        }[cat]
        daily_revenue = base_revenue + np.random.uniform(-3000000, 3000000)
        daily_revenue = round(daily_revenue, 2)

        # Them don hoan tra (so am) vao mot so ngay
        if np.random.random() < 0.2:
            return_amount = -abs(np.random.uniform(200000, 1500000))
            return_amount = round(return_amount, 2)
        else:
            return_amount = 0

        rows.append(
            {
                "ngay": date.strftime("%Y-%m-%d"),
                "danh_muc": cat,
                "doanh_so": daily_revenue,
                "hoan_tra": return_amount,
                "thuc_thu": round(daily_revenue + return_amount, 2),
            }
        )

df_dashboard = pd.DataFrame(rows)
df_dashboard.to_csv("dashboard_sales.csv", index=False, encoding="utf-8-sig")
print(f"Da tao dashboard_sales.csv: {len(df_dashboard)} dong")

# Tao du lieu Excel (co loi)
df_excel = df_dashboard.copy()

# Loi 1: Thieu 2 ngay cuoi (29-30/6)
df_excel = df_excel[df_excel["ngay"] < "2024-06-29"]

# Loi 2: Go sai ten danh muc "Dien tu" thanh "Dien Tu" (viet hoa chu T)
df_excel["danh_muc"] = df_excel["danh_muc"].replace("Dien tu", "Dien Tu")

# Loi 3: Khong tru don hoan tra (chi lay doanh_so, bo qua hoan_tra)
df_excel["thuc_thu"] = df_excel["doanh_so"]

# Loi 4: Lam tron so (mat phan thap phan)
df_excel["thuc_thu"] = df_excel["thuc_thu"].round(0).astype(int)
df_excel["doanh_so"] = df_excel["doanh_so"].round(0).astype(int)

# Xoa cot hoan_tra (nhan vien khong ghi nhan)
df_excel = df_excel.drop(columns=["hoan_tra"])

# Ghi ra Excel
df_excel.to_excel("bao_cao_excel.xlsx", index=False, sheet_name="Thang 6-2024")
print(f"Da tao bao_cao_excel.xlsx: {len(df_excel)} dong")
print("\nCac loi da cai vao du lieu Excel:")
print("1. Thieu ngay 29-30/6 (8 dong bi mat)")
print("2. 'Dien tu' thanh 'Dien Tu' (loi viet hoa)")
print("3. Khong tru hoan tra (thuc_thu = doanh_so)")
print("4. Lam tron tat ca so ve so nguyen")

Your workspace is ready with two data files waiting to be compared. Next up, you will use Claude Code to generate a reconciliation script that automatically finds every discrepancy between them.

Use Claude Code to Generate the Reconciliation Script

Your sample data files are ready. You have a CSV representing the accurate dashboard export and an Excel file containing the kind of errors that happen when reports are compiled manually.

Now comes the exciting part. Instead of writing a comparison script line by line, you will describe what you need in plain language and let Claude Code generate the entire solution for you.

In this step, get ready to:
  • Open Claude Code inside VS Code.
  • Submit a Vietnamese prompt describing the reconciliation analysis you need.
  • Review and accept the generated reconciliation script.
Open Claude Code in VS Code

Claude Code is an AI coding assistant that lives inside your editor. You give it instructions in natural language and it reads your project files, writes code, and creates new files on your behalf.

  • In VS Code, click the Spark icon in the top-right corner of the editor toolbar.

Don't see the Spark icon?

The Spark icon appears when you have a file open in the editor. If you do not see it, open any file (like tao_du_lieu_mau.py) first.

Alternatively, open the Command Palette with Ctrl+Shift+P, type Claude Code, then select Open in New Tab.

The Claude Code panel opens in a new tab inside VS Code. You will see a text input area where you can type prompts.

Give Claude Code the reconciliation prompt

You need Claude Code to read both data files, compare them systematically, find every discrepancy, and explain the likely causes. The prompt below describes all of this in Vietnamese.

  • Type the following prompt into the Claude Code input area and press Enter to submit:
Tôi có 2 file dữ liệu trong folder này:
- dashboard_sales.csv: dữ liệu xuất từ dashboard (nguồn chính xác)
- bao_cao_excel.xlsx: báo cáo Excel do nhân viên tổng hợp thủ công

Cả 2 file đều là doanh số bán hàng tháng 6/2024, theo ngày và theo danh mục sản phẩm.

Hãy viết script Python để:
1. Đọc cả 2 file
2. So sánh tổng doanh số theo từng danh mục
3. So sánh tổng doanh số theo từng ngày
4. Tìm ra TẤT CẢ điểm khác biệt
5. Giải thích nguyên nhân có thể gây ra sự khác biệt
6. In ra báo cáo đối soát chi tiết

Lưu script thành file doi_soat.py

Why is the prompt in Vietnamese?

Claude Code handles Vietnamese prompts well. Using Vietnamese here matches the target audience for this project and demonstrates that you can communicate with AI coding tools in your native language.

What does this prompt ask for?

The prompt tells Claude Code you have two data files in this folder: a CSV from the dashboard (the accurate source) and an Excel report compiled manually by staff. Both cover June 2024 sales by day and product category.

It then asks Claude Code to write a Python script that reads both files, compares totals by category and by day, finds all differences, explains possible causes, and prints a detailed reconciliation report. The script should be saved as doi_soat.py.

Review and accept Claude Code's output

After you submit the prompt, watch the Claude Code panel. Claude Code will work through several steps before producing your script.

  • Watch Claude Code read both data files to understand their structure (column names, row counts, data types).
  • Claude Code will then write a reconciliation script and ask for your permission to create the file.
  • Click Accept when Claude Code asks to create doi_soat.py.

Why does Claude Code ask for permission?

Claude Code proposes edits and asks for permission before modifying or creating files. This safety mechanism lets you review what the AI wants to do before it changes anything in your project.

  • Confirm that doi_soat.py now appears in the Explorer sidebar (left panel) of VS Code, alongside your other files.

Your script may look different

Claude Code generates a fresh solution each time. Your doi_soat.py may differ in variable names, print formatting, or the order of checks. That is perfectly fine. What matters is that it reads both files and identifies discrepancies.

Don't see doi_soat.py in your folder?

Make sure you clicked Accept when Claude Code asked for permission to create the file. If you accidentally dismissed it, submit the same prompt again.

If Claude Code is not responding at all, check that your Claude subscription (Pro, Max, Team, or Enterprise) is active. The free plan does not include Claude Code access.

help me troubleshoot why Claude Code is not generating my file

Your reconciliation script is ready. Next up, you will run it and see exactly where the dashboard and Excel numbers disagree.

Run the Script and Interpret Results

Claude Code just generated a complete reconciliation script for you. Now it is time to run that script and see what discrepancies it uncovers between the dashboard and the Excel report.

The real test is whether the script catches all 4 types of errors that commonly appear in manually compiled reports. If it misses any, you will ask Claude Code to refine its approach.

In this step, get ready to:
  • Run the reconciliation script and read the output report.
  • Interpret the 4 discrepancy types the script identifies.
  • Refine the script with a follow-up prompt if needed.
Run the generated script

The doi_soat.py file is now in your project folder. Running it will read both data files and print a reconciliation report directly to the terminal.

  • Run the reconciliation script in the VS Code terminal by entering this command:
python doi_soat.py

You should see a structured report in the terminal with numbered sections like [1] KIEM TRA SO LUONG DONG, [2] KIEM TRA DANH MUC SAN PHAM, and so on.

Seeing a ModuleNotFoundError?

If the script fails with an import error for pandas or openpyxl, make sure you installed both packages in Step 1. Re-run the install command in the same terminal:

pip install pandas==3.0.3 openpyxl==3.1.5

Then try running python doi_soat.py again.

Help me debug this ModuleNotFoundError when running doi_soat.py

Interpret the findings

The reconciliation report checks 4 dimensions of the data. Each one reveals a different type of error that commonly appears in manually compiled Excel reports.

  • Read through the terminal output section by section.
  • Look for these 4 discrepancy types in the report:

What are the 4 discrepancies?

  • Missing data: The Excel report is missing June 29-30 (2 days of data not included). The script detects this by comparing the row counts and finding which dates are absent.
  • Category typo: "Dien tu" in the dashboard appears as "Dien Tu" in Excel. The capital T causes it to be counted as a separate category entirely.
  • Returns not subtracted: The Excel report shows only positive revenue. Returns and refunds were never deducted, inflating the Excel totals.
  • Rounding errors: Excel rounds all values to whole numbers, losing decimal precision. Each row is off by a tiny amount, but together they add up.

Why do these errors happen in real workplaces?

These 4 issues mirror what happens when someone manually exports data and reformats it in Excel. Dates get cut off when copying a partial range. Text entries get retyped with inconsistent capitalization. Business logic like returns gets left out of summaries. Number formatting strips decimal places.

Automated reconciliation catches all of these instantly, instead of hours of manual cross-checking.

Ask Claude Code to refine the script if needed

Claude Code's first attempt might not catch all 4 issues clearly. If any discrepancy type is missing or unclear in the output, you can send a follow-up prompt to improve the script.

  • Check the terminal output against the 4 discrepancy types listed above.
  • If any are missing, switch back to the Claude Code panel and paste this follow-up prompt:
Script đã tìm ra một số khác biệt. Nhưng tôi biết còn 4 loại lỗi trong dữ liệu:
1. Thiếu ngày (29-30/6)
2. Gõ sai tên danh mục (Dien tu vs Dien Tu)
3. Không trừ đơn hoàn trả
4. Làm tròn số

Hãy cập nhật script để kiểm tra cụ thể từng loại lỗi này và in báo cáo rõ ràng hơn.

What does this prompt do?

This follow-up prompt tells Claude Code exactly which error types to look for. It gives the AI specific targets instead of asking it to "find everything." This is a common pattern when working with AI assistants: start broad, then refine with specifics.

  • Accept the file update when Claude Code asks for permission to modify doi_soat.py.
  • Run the updated script again to confirm all 4 issues are detected:
python doi_soat.py

The output should now clearly identify all 4 discrepancy types with specific numbers showing the monetary impact of each.

Your script may look different

Claude Code generates code dynamically, so your doi_soat.py might use different variable names or formatting than the reference below. That is fine. What matters is that the output identifies all 4 discrepancy types with their monetary impact.

✔️ Awesome, I've got everything!

Great. Make sure your terminal output shows all 4 checks (row count, category names, returns, rounding) with specific numbers for each.

ⓧ I'd like to double check the full code

Here is the reference version of doi_soat.py. Your Claude Code-generated version may differ in style, but should produce similar results.

import pandas as pd

# ============================================================
# SCRIPT DOI SOAT SO LIEU: DASHBOARD vs EXCEL
# ============================================================

print("=" * 60)
print("BAO CAO DOI SOAT SO LIEU")
print("Dashboard vs Bao cao Excel - Thang 6/2024")
print("=" * 60)

# Doc du lieu
df_dash = pd.read_csv("dashboard_sales.csv")
df_excel = pd.read_excel("bao_cao_excel.xlsx")

# --- KIEM TRA 1: So luong dong ---
print("\n[1] KIEM TRA SO LUONG DONG")
print(f"    Dashboard: {len(df_dash)} dong")
print(f"    Excel:     {len(df_excel)} dong")
print(f"    Chenh lech: {len(df_dash) - len(df_excel)} dong")

# Tim ngay bi thieu
dates_dash = set(df_dash["ngay"].unique())
dates_excel = set(df_excel["ngay"].unique())
missing_dates = dates_dash - dates_excel
if missing_dates:
    print(f"    >> Nguyen nhan: Excel THIEU {len(missing_dates)} ngay: {sorted(missing_dates)}")

# --- KIEM TRA 2: Danh muc san pham ---
print("\n[2] KIEM TRA DANH MUC SAN PHAM")
cats_dash = set(df_dash["danh_muc"].unique())
cats_excel = set(df_excel["danh_muc"].unique())
print(f"    Dashboard: {sorted(cats_dash)}")
print(f"    Excel:     {sorted(cats_excel)}")
if cats_dash != cats_excel:
    only_dash = cats_dash - cats_excel
    only_excel = cats_excel - cats_dash
    if only_dash:
        print(f"    >> Chi co trong Dashboard: {only_dash}")
    if only_excel:
        print(f"    >> Chi co trong Excel: {only_excel}")
    print("    >> Nguyen nhan: Loi go ten danh muc (khac viet hoa/thuong)")

# --- KIEM TRA 3: Hoan tra ---
print("\n[3] KIEM TRA DON HOAN TRA")
total_returns = df_dash["hoan_tra"].sum()
has_returns_col = "hoan_tra" in df_excel.columns
print(f"    Dashboard - Tong hoan tra: {total_returns:,.0f} VND")
if not has_returns_col:
    print("    Excel - KHONG CO cot hoan tra!")
    print(f"    >> Nguyen nhan: Excel khong ghi nhan hoan tra, chenh lech {abs(total_returns):,.0f} VND")

# --- KIEM TRA 4: Lam tron so ---
print("\n[4] KIEM TRA LAM TRON SO")
# So sanh tren cung tap du lieu (loai ngay thieu, chuan hoa ten)
df_dash_compare = df_dash[df_dash["ngay"] < "2024-06-29"].copy()
df_dash_compare["danh_muc"] = df_dash_compare["danh_muc"].replace("Dien tu", "Dien Tu")

# Merge de so sanh tung dong
df_merged = df_dash_compare.merge(
    df_excel,
    on=["ngay", "danh_muc"],
    suffixes=("_dash", "_excel"),
)

# So sanh doanh_so (truoc khi xet hoan tra)
rounding_diff = (df_merged["doanh_so_dash"] - df_merged["doanh_so_excel"]).abs()
rounding_errors = rounding_diff[rounding_diff > 0]
print(f"    So dong co chenh lech do lam tron: {len(rounding_errors)}/{len(df_merged)}")
print(f"    Chenh lech trung binh: {rounding_errors.mean():,.2f} VND")
print(f"    Chenh lech tong cong do lam tron: {rounding_errors.sum():,.0f} VND")
print("    >> Nguyen nhan: Excel lam tron tat ca so ve so nguyen")

# --- TONG KET ---
print("\n" + "=" * 60)
print("TONG KET DOI SOAT")
print("=" * 60)

total_dash = df_dash["thuc_thu"].sum()
total_excel = df_excel["thuc_thu"].sum()
diff = total_dash - total_excel
diff_pct = (diff / total_dash) * 100

print(f"\n{'Nguon':<20} {'Tong doanh so':>20}")
print("-" * 42)
print(f"{'Dashboard':<20} {total_dash:>20,.0f} VND")
print(f"{'Excel':<20} {total_excel:>20,.0f} VND")
print(f"{'CHENH LECH':<20} {diff:>20,.0f} VND")
print(f"{'Ty le chenh lech':<20} {diff_pct:>19,.2f}%")

# Phan tich nguyen nhan
print("\n" + "-" * 42)
print("PHAN TICH NGUYEN NHAN:")
print("-" * 42)

# Anh huong cua thieu ngay
missing_days_impact = df_dash[df_dash["ngay"] >= "2024-06-29"]["thuc_thu"].sum()
print(f"  1. Thieu ngay 29-30/6:        {missing_days_impact:>15,.0f} VND")

# Anh huong cua hoan tra
returns_impact = abs(total_returns)
print(f"  2. Khong tru hoan tra:        {returns_impact:>15,.0f} VND")

# Anh huong cua lam tron
print(f"  3. Lam tron so:               {rounding_errors.sum():>15,.0f} VND")

# Anh huong cua loi danh muc (khong mat data, chi sai ten)
print(f"  4. Loi ten danh muc:          {'Khong mat tien, sai phan loai':>15}")

print("\n" + "=" * 60)
print("KET LUAN: So lieu Dashboard va Excel KHONG KHOP.")
print("Can yeu cau bo phan bao cao kiem tra lai quy trinh.")
print("=" * 60)

Your script now identifies every discrepancy and explains its root cause. Next up, you will ask Claude Code to add a polished summary section that you could screenshot and share with a manager.

Create the Final Summary Report

Your reconciliation script now catches all 4 discrepancy types. The next challenge is presenting these findings clearly enough for someone who was not involved in the analysis.

In this step, you will ask Claude Code to add a formatted summary table to your script. The goal is a report you can screenshot and send directly to your manager.

In this step, get ready to:
  • Prompt Claude Code to add a formatted summary section with aligned columns.
  • Run the final script and review the complete output.
  • Verify the total difference equals the sum of individual discrepancy impacts.
Ask Claude Code to add a summary section

A good reconciliation report ends with a summary table that anyone can understand at a glance. This means showing the total from each source, the absolute difference, the percentage gap, and a breakdown of what caused it.

  • Switch back to the Claude Code panel in VS Code.
  • Type the following prompt to ask Claude Code for a formatted summary:
Thêm vào cuối script một phần tóm tắt dạng bảng:
- Tổng doanh số Dashboard vs Excel
- Chênh lệch tuyệt đối và phần trăm
- Danh sách nguyên nhân kèm mức ảnh hưởng (số tiền)

Format đẹp, dễ đọc, để tôi có thể screenshot cho sếp xem.

What does this prompt ask for?

You are asking Claude Code to add a summary table at the end of the script. The table should show Dashboard total vs Excel total, the absolute and percentage difference, and a list of causes with their monetary impact.

The last line tells Claude Code the formatting matters because you need it screenshot-ready for your manager.

  • Press Enter to send the prompt.
  • Watch the Claude Code panel as it updates doi_soat.py with the new summary section.
  • When Claude Code asks for permission to modify the file, click Accept.

Why does Claude Code ask for permission?

Claude Code proposes edits and asks for permission before modifying files. This gives you a chance to review what it plans to change before anything is written to disk.

Run the final script and capture output
  • Run the updated reconciliation script by entering this in the VS Code terminal:
python doi_soat.py

The script should output the full reconciliation report followed by a formatted summary table with aligned columns.

Seeing an error when running the script?

If you see a syntax error, Claude Code may have introduced a formatting issue. Go back to the Claude Code panel and tell it about the error. Paste the error message directly into the chat.

If the script runs but does not show the summary table, send a follow-up prompt asking Claude Code to check that the TONG KET section was added at the end of the file.

Help me debug my doi_soat.py script

Review the complete report

The output should now contain a complete formatted report. Look for these three sections in the summary:

  • A comparison table showing Dashboard total vs Excel total in VND.
  • The absolute difference and percentage between the two sources.
  • A breakdown of each discrepancy type (missing days, returns, rounding, category typo) with its monetary impact.

How do I know the report is correct?

The total difference between Dashboard and Excel should equal the sum of the individual discrepancy impacts (missing days + returns + rounding). The category typo does not cause a monetary loss. It only causes misclassification.

If the numbers do not add up, ask Claude Code to recalculate. The formatted columns should be aligned so the report looks clean in a screenshot.

Your final report should look something like the reference version below. Claude Code may format it slightly differently, but the key data points should match.

✔️ Awesome, I've got everything!

Great. Double check that your report shows all 4 discrepancy causes and that the numbers add up.

ⓧ I'd like to double check the full code

Here is the reference version of doi_soat.py. Your Claude Code-generated version may differ in structure, but it should identify the same issues and produce similar output.

import pandas as pd

# ============================================================
# SCRIPT DOI SOAT SO LIEU: DASHBOARD vs EXCEL
# ============================================================

print("=" * 60)
print("BAO CAO DOI SOAT SO LIEU")
print("Dashboard vs Bao cao Excel - Thang 6/2024")
print("=" * 60)

# Doc du lieu
df_dash = pd.read_csv("dashboard_sales.csv")
df_excel = pd.read_excel("bao_cao_excel.xlsx")

# --- KIEM TRA 1: So luong dong ---
print("\n[1] KIEM TRA SO LUONG DONG")
print(f"    Dashboard: {len(df_dash)} dong")
print(f"    Excel:     {len(df_excel)} dong")
print(f"    Chenh lech: {len(df_dash) - len(df_excel)} dong")

# Tim ngay bi thieu
dates_dash = set(df_dash["ngay"].unique())
dates_excel = set(df_excel["ngay"].unique())
missing_dates = dates_dash - dates_excel
if missing_dates:
    print(f"    >> Nguyen nhan: Excel THIEU {len(missing_dates)} ngay: {sorted(missing_dates)}")

# --- KIEM TRA 2: Danh muc san pham ---
print("\n[2] KIEM TRA DANH MUC SAN PHAM")
cats_dash = set(df_dash["danh_muc"].unique())
cats_excel = set(df_excel["danh_muc"].unique())
print(f"    Dashboard: {sorted(cats_dash)}")
print(f"    Excel:     {sorted(cats_excel)}")
if cats_dash != cats_excel:
    only_dash = cats_dash - cats_excel
    only_excel = cats_excel - cats_dash
    if only_dash:
        print(f"    >> Chi co trong Dashboard: {only_dash}")
    if only_excel:
        print(f"    >> Chi co trong Excel: {only_excel}")
    print("    >> Nguyen nhan: Loi go ten danh muc (khac viet hoa/thuong)")

# --- KIEM TRA 3: Hoan tra ---
print("\n[3] KIEM TRA DON HOAN TRA")
total_returns = df_dash["hoan_tra"].sum()
has_returns_col = "hoan_tra" in df_excel.columns
print(f"    Dashboard - Tong hoan tra: {total_returns:,.0f} VND")
if not has_returns_col:
    print("    Excel - KHONG CO cot hoan tra!")
    print(f"    >> Nguyen nhan: Excel khong ghi nhan hoan tra, chenh lech {abs(total_returns):,.0f} VND")

# --- KIEM TRA 4: Lam tron so ---
print("\n[4] KIEM TRA LAM TRON SO")
# So sanh tren cung tap du lieu (loai ngay thieu, chuan hoa ten)
df_dash_compare = df_dash[df_dash["ngay"] < "2024-06-29"].copy()
df_dash_compare["danh_muc"] = df_dash_compare["danh_muc"].replace("Dien tu", "Dien Tu")

# Merge de so sanh tung dong
df_merged = df_dash_compare.merge(
    df_excel,
    on=["ngay", "danh_muc"],
    suffixes=("_dash", "_excel"),
)

# So sanh doanh_so (truoc khi xet hoan tra)
rounding_diff = (df_merged["doanh_so_dash"] - df_merged["doanh_so_excel"]).abs()
rounding_errors = rounding_diff[rounding_diff > 0]
print(f"    So dong co chenh lech do lam tron: {len(rounding_errors)}/{len(df_merged)}")
print(f"    Chenh lech trung binh: {rounding_errors.mean():,.2f} VND")
print(f"    Chenh lech tong cong do lam tron: {rounding_errors.sum():,.0f} VND")
print("    >> Nguyen nhan: Excel lam tron tat ca so ve so nguyen")

# --- TONG KET ---
print("\n" + "=" * 60)
print("TONG KET DOI SOAT")
print("=" * 60)

total_dash = df_dash["thuc_thu"].sum()
total_excel = df_excel["thuc_thu"].sum()
diff = total_dash - total_excel
diff_pct = (diff / total_dash) * 100

print(f"\n{'Nguon':<20} {'Tong doanh so':>20}")
print("-" * 42)
print(f"{'Dashboard':<20} {total_dash:>20,.0f} VND")
print(f"{'Excel':<20} {total_excel:>20,.0f} VND")
print(f"{'CHENH LECH':<20} {diff:>20,.0f} VND")
print(f"{'Ty le chenh lech':<20} {diff_pct:>19,.2f}%")

# Phan tich nguyen nhan
print("\n" + "-" * 42)
print("PHAN TICH NGUYEN NHAN:")
print("-" * 42)

# Anh huong cua thieu ngay
missing_days_impact = df_dash[df_dash["ngay"] >= "2024-06-29"]["thuc_thu"].sum()
print(f"  1. Thieu ngay 29-30/6:        {missing_days_impact:>15,.0f} VND")

# Anh huong cua hoan tra
returns_impact = abs(total_returns)
print(f"  2. Khong tru hoan tra:        {returns_impact:>15,.0f} VND")

# Anh huong cua lam tron
print(f"  3. Lam tron so:               {rounding_errors.sum():>15,.0f} VND")

# Anh huong cua loi danh muc (khong mat data, chi sai ten)
print(f"  4. Loi ten danh muc:          {'Khong mat tien, sai phan loai':>15}")

print("\n" + "=" * 60)
print("KET LUAN: So lieu Dashboard va Excel KHONG KHOP.")
print("Can yeu cau bo phan bao cao kiem tra lai quy trinh.")
print("=" * 60)

You now have a complete data reconciliation workflow. From two mismatched data sources to a formatted report explaining every discrepancy, all generated by Claude Code from a natural language prompt. Next up, take on the Secret Mission to turn this console output into a visual HTML report you can share with your team.

Secret mission

Generate an HTML Visual Report

Your console report is informative, but it is not exactly something you can email to your manager. In this secret mission, you will ask Claude Code to generate a professional HTML report with color-coded tables and visual bar charts that you can open in any browser and share with your team.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources for future reference or delete them entirely.

Resources you used:

  • The doi-soat-so-lieu folder with all project files (Python scripts, CSV, Excel, and HTML report).
  • Python packages pandas and openpyxl installed via pip.

Keep everything running

No action needed. Choose this if you plan to reuse these scripts with your own real data files.

  • Your scripts and sample data stay in the doi-soat-so-lieu folder on your Desktop.
  • To reuse the workflow, replace dashboard_sales.csv and bao_cao_excel.xlsx with your real data exports and prompt Claude Code again.
  • The pandas and openpyxl packages remain installed in your Python environment.

Pause - I'll come back to this later

Since this project is entirely local with no running processes, there is nothing to pause. Your files will stay where they are until you return.

  • The doi-soat-so-lieu folder remains on your Desktop.
  • No background processes are running from this project.

Delete - I don't want to use this again

Remove all project files and uninstall the Python packages you installed during this project.

  • Delete the project folder by running this command in your terminal:

macOS

cd ~/Desktop
rm -rf doi-soat-so-lieu

Windows

cd ~\Desktop
Remove-Item -Recurse -Force doi-soat-so-lieu
  • Uninstall the Python packages by running:
pip uninstall pandas openpyxl -y
  • This removes both packages from your Python environment.

Nice Work!

Nice Work!

You just built a complete data reconciliation system powered by Claude Code. The next time your dashboard numbers do not match a manual report, you have a reusable workflow that finds and explains every discrepancy in minutes.

You've learned how to:

  • Created realistic sample data with 4 types of common data discrepancies (missing dates, category typos, missing returns, rounding errors) that mirror real workplace data problems.
  • Used Claude Code to generate a complete Python reconciliation script from a Vietnamese natural language prompt.
  • Produced a formatted summary report that automatically identifies every discrepancy between two data sources, explains root causes, and quantifies the monetary impact of each.
  • Secret Mission: Generated a professional HTML visual report with color-coded comparison tables and inline bar charts.

Ready to quiz yourself?