📊 Transaction Data Analyzer

End-to-end financial data pipeline: ingestion → SQL transformation → analysis → Excel reporting

---

✨ Features

Feature	Description
🎲 Data Generation	Realistic synthetic transactions with injected anomalies
🗄️ SQL Pipeline	SQLite ingestion + 6 analytical SQL queries
📊 Excel Reports	7-sheet professional report with charts & conditional formatting
⚠️ Discrepancy Detection	Automated detection of duplicates, negatives, nulls
🧪 Unit Tests	pytest test suite covering core pipeline logic

---

🗂️ Project Structure

transaction_analyzer/
├── main.py                   # ← Run this
├── requirements.txt
├── data/
│   ├── transactions.csv      # auto-generated
│   └── transactions.db       # auto-generated SQLite DB
├── reports/
│   └── transaction_analysis_report.xlsx   # auto-generated
├── scripts/
│   └── generate_data.py      # Synthetic data generator
├── src/
│   ├── pipeline.py           # SQL queries & DB operations
│   └── report_generator.py   # Excel report builder
└── tests/
    └── test_pipeline.py      # pytest unit tests

---

🚀 Quick Start

1. Clone the repo

git clone https://github.com/YOUR_USERNAME/transaction-data-analyzer.git
cd transaction-data-analyzer

2. Install dependencies

pip install -r requirements.txt

3. Run the analyzer

python main.py

That's it. The full pipeline runs automatically:

• Generates 1,000 synthetic transactions
• Loads them into a SQLite database
• Runs 6 SQL analytical queries
• Exports a multi-sheet Excel report to reports/

---

📈 Excel Report Preview

The generated report (reports/transaction_analysis_report.xlsx) contains 7 sheets:

Sheet	Contents
📊 Summary	KPI dashboard — revenue, rates, anomaly count
📈 Monthly Trends	Revenue & transaction volume with line chart
🏷️ By Category	Revenue breakdown by product category + bar chart
🌍 By Region	Geographic revenue distribution + pie chart
💳 Payments	Payment method usage and revenue contribution
👤 Top Customers	Top 20 customers by lifetime value
⚠️ Discrepancies	Data quality issues with severity ratings

---

🗄️ SQL Queries

Six analytical queries are defined in src/pipeline.py:

-- Example: Monthly revenue trend
SELECT
    strftime('%Y-%m', date) AS month,
    COUNT(*) AS transaction_count,
    ROUND(SUM(CASE WHEN status='Completed' THEN net_amount ELSE 0 END), 2) AS revenue,
    ...
FROM transactions
GROUP BY month ORDER BY month

Queries cover: monthly trends, category performance, regional breakdown, payment methods, top customers, and data discrepancies.

---

⚠️ Discrepancy Detection

The pipeline automatically flags:

• Duplicate transaction IDs (HIGH severity)
• Negative transaction amounts (HIGH severity)
• Missing customer IDs (MEDIUM severity)
• Zero-amount completed transactions (MEDIUM severity)
• Excessive discounts > 50% (LOW severity)

---

🧪 Running Tests

pytest tests/ -v

---

🛠️ Tech Stack

• Python 3.9+ — core language
• Pandas — data ingestion, transformation, and analysis
• SQLite (via sqlite3) — embedded SQL database
• OpenPyXL — Excel report generation with charts and formatting
• NumPy — data generation and numerical operations
• pytest — unit testing

---

📄 License

MIT License — see LICENSE