MBI Case Study - Financial Data Processing

Data processing pipeline for financial analysis of invoices and expenses from Fakturoid API.

Project Structure

API/

API client configurations for downloading data from Fakturoid API.

• Bruno/Fakturoid API/ - Bruno API client collection with requests for invoices and expenses
  • Invoices Index.bru - Request to fetch invoices
  • Expenses Index.bru - Request to fetch expenses
  • Obtain Access Token.bru - OAuth token request
  • out_data/ - Saved API respones
• Postman/ - Postman collection (alternative to Bruno)

Data/

Data files organized by processing stage.

• 01_Source/ - Raw JSON data downloaded from Fakturoid API
  • Invoices.json - Invoice data
  • Expenses.json - Expense data
• 02_Source CSV/ - CSV files converted from JSON source data
  • Main tables: Invoices.csv, Expenses.csv
  • Related tables: *_lines.csv, *_payments.csv, *_vat_rates_summary.csv
• 03_Transformed Data/ - Final transformed data for analysis
  • Movements_clean.csv - Unified movements table (invoices + expenses)
  • Movements_clean_documentation.md - Complete data model documentation

scripts/

Data processing scripts organized by workflow step.

• 01_download/ - Instructions for downloading data from Fakturoid API
• 02_json_to_csv/ - JSON to CSV conversion script
  • json_to_csv.py - Main conversion script with nested fields support
  • process_source_data.sh - Batch processing script
• 03_data_transformations/ - SQL scripts for data transformation
  • 01_create_table_movements.sql - Create unified Movements table
  • 02_insert_invoices.sql - Insert invoice data
  • 03_insert_expenses.sql - Insert expense data
  • 04_transform_data.sql - Create Movements_clean table

tests/

End-to-end test suite for the JSON to CSV converter.

• json_to_csv/ - Test cases for json_to_csv.py
  • test1/ through test8/ - Individual test scenarios
  • run_all_tests.sh - Run complete test suite
  • Each test includes input JSON, expected output, and comparison script

Assignment/

Project assignment materials and documentation.

• MBI-Data_Analyst_Engineer-Case study - Varianta_A (1).pdf - Case study assignment
• API columns.md - API column reference documentation
• todo.md - Project task list

Requirements

• Python 3.13
• uv package manager (see pyproject.toml and uv.lock)
• SQL database (for data transformations)
• Bruno or Postman (for API data download)

Data Processing Workflow

1. Download - Fetch invoices and expenses from Fakturoid API (since 2021-01-01)
2. Convert - Transform JSON to CSV format using json_to_csv.py
3. Transform - Execute SQL scripts to create unified Movements_clean table
4. Analyze - Use Movements_clean.csv for financial reporting

Documentation

• User Guide: Report Guidline.pdf - Guidelines for creating financial reports
• Data Model: Data/03_Transformed Data/Movements_clean_documentation.md - Complete schema and field descriptions

Testing

Run end-to-end tests for JSON to CSV conversion:

./tests/json_to_csv/run_all_tests.sh

Individual tests can be run from their respective directories.