Value At Risk (VaR) API

FastAPI-based backend to calculate daily returns and Value at Risk (VaR) for stock portfolios. It supports data import from an Excel workbook (multi-sheet), Historical Simulation VaR, and Parametric (Normal Distribution) VaR.

• Framework: FastAPI
• Language: Python 3.11
• Database: PostgreSQL (SQLAlchemy)
• Containerization: Docker, Docker Compose

Features

• Import historical price data from an Excel file (.xlsx) with multiple sheets (each sheet name = stock symbol)
• Compute daily returns for a given symbol
• Compute VaR using:
  • Parametric (Normal Distribution)
  • Historical Simulation
• Built-in CORS middleware
• Interactive API documentation via Swagger UI (/docs) and ReDoc (/redoc)

Project Structure

backend/
  app/
    core/
      config.py           # App settings (DATABASE_URL)
      database.py         # SQLAlchemy engine/session/Base
    models/
      price.py            # Price model/table
    repositories/
      price_repository.py # DB operations for Price
    routers/
      import_router.py    # /api/import
      returns_router.py   # /api/returns
      var_router.py       # /api/var
    schemas/
      value_at_risk_schema.py
    services/
      import_service.py
      returns_service.py
      var_service.py
    utils/
      functions.py
    __init__.py
    main.py               # FastAPI app, startup import (when run as module)
  config/
    settings.py           # Host/Port and .env loading
  data/
    datasheet.xlsx        # Example dataset
  tests/
    test_financial.py
  .env.example
  docker-compose.yml
  Dockerfile
  requirements.txt
  README.md

Environment Variables

Copy .env.example to .env and update values as needed.

• DATABASE_URL: SQLAlchemy connection string for PostgreSQL (psycopg2). Example:
  • If running DB from docker-compose on your host: postgresql+psycopg2://varisk:varisk-999@localhost:5442/value_at_risk
• HOST: Host binding for API server (default 0.0.0.0)
• PORT: Port for API server (default 3333)
• TZ: Timezone (e.g., Asia/Jakarta)
• ALLOWED_ORIGINS: CORS origin(s), e.g. "http://localhost:5173"

Quick Start (Docker)

1. Ensure Docker and Docker Compose are installed.
2. From the backend directory, run:

   docker compose up --build -d

3. The API will be available at http://localhost:3333
4. Documentation: http://localhost:3333/docs and http://localhost:3333/redoc

Notes:

• The compose file starts two services:
  • API: var_api (port 3333)
  • PostgreSQL: var_db_postgre (host port 5442 -> container 5432)
• On first start, if the database is empty, the API will import data from ./data/datasheet.xlsx automatically.

Run Locally (without Docker)

Prerequisites: Python 3.11, PostgreSQL

1. Create and activate a virtual environment:

   python3 -m venv .venv
   source .venv/bin/activate

2. Install dependencies:

   pip install -r requirements.txt

3. Prepare a PostgreSQL database and set DATABASE_URL in .env. Example (using the DB launched by docker-compose):
   • Start only the DB with Docker: docker compose up -d db_var
   • DATABASE_URL: postgresql+psycopg2://varisk:varisk-999@localhost:5442/value_at_risk
4. Run the app (as a module) so initial Excel import is executed if DB is empty:

   python -m app.main

   The server will bind to HOST:PORT provided in .env (default 0.0.0.0:3333).

Alternative run via uvicorn (note: this will NOT perform the automatic Excel import block in main.py):

uvicorn app.main:app --host 0.0.0.0 --port 3333 --reload

Data Import

• Automatic import: When running python -m app.main, if the DB is empty, the service will load ./data/datasheet.xlsx into the prices table.
• Manual import endpoint: POST /api/import/ with form-data file field named file containing a .xlsx workbook (each sheet named as the stock symbol). The endpoint returns an import summary.

Example cURL:

curl -X POST \
  -F "file=@data/datasheet.xlsx" \
  http://localhost:3333/api/import/

API Endpoints

Base URL: http://localhost:3333

• GET /api/returns/{symbol}

  • Description: Compute daily percentage returns for a symbol (based on stored historical prices).
  • Response: Array of objects with date, price, return_pct.
  • Example:

    curl http://localhost:3333/api/returns/BBRI

• GET /api/var/{symbol}?level=95&value=100000000

  • Description: Compute VaR using both Parametric (Normal) and Historical Simulation methods.
  • Query params:
    • level: Confidence level, default 95 (percent)
    • value: Portfolio value in Rupiah, default 100000000
  • Example:

    curl "http://localhost:3333/api/var/BBRI?level=95&value=100000000"

  • Sample response (shape):

    {
      "symbol": "BBRI",
      "confidence_level": 95,
      "portfolio_value": 100000000,
      "var": {
        "parametric": {
          "method": "Parametric",
          "portfolio_var_percent": -1.2345,
          "portfolio_var_rupiah": 1234500.0,
          "z_score": 1.6449,
          "description": "..."
        },
        "historical": {
          "method": "Historical",
          "portfolio_var_percent": -1.1111,
          "portfolio_var_rupiah": 1111100.0,
          "z_score": null,
          "description": "..."
        }
      }
    }

• POST /api/import/

  • Description: Upload an Excel file (.xlsx) with multiple sheets to import historical price data. Sheet names are treated as symbols. Field name: file.

Documentation UIs:

• Swagger UI: /docs
• ReDoc: /redoc

Database Schema

Table: prices

• id (Integer, PK)
• symbol (String, indexed)
• date (Date)
• price (Float)
• open (Float)
• high (Float)
• low (Float)
• volume (Float)

Tables are auto-created on startup via Base.metadata.create_all(bind=engine).

CORS

Configure ALLOWED_ORIGINS in .env. Example: "http://localhost:5173" for a frontend running on Vite at port 5173.

Troubleshooting

• Connection refused to DB: ensure the database is running and DATABASE_URL is correct. If using docker-compose DB, connect to localhost:5442 from host.
• No data for symbol: import data using /api/import/ or ensure the symbol matches a sheet name in the Excel file; symbols are upper-cased in queries.
• Automatic data import did not run: ensure you started the app with python -m app.main (the import block runs only when executed as a module and __name__ == "__main__").

License

This project is intended for technical assessment and educational purposes.

Author

Doni Syahroni