.. _excel_workflow:

Excel Workflow
==============

The Excel workflow requires no Python coding. Everything is configured through an Excel file. This is the recommended starting point for most users.

Prerequisites
-------------

Complete the :ref:`quick_start` first to install the engine and initialize the project structure.

Project Structure
-----------------

After running ``kaxanuk.backtest_engine init excel``, your project directory will contain:

.. code-block:: text

   project-root/
   ├── __main__.py
   ├── Config/
   │   ├── .env                                 # License key
   │   └── backtest_engine_parameters.xlsx      # All configuration
   ├── Input/
   │   ├── Data/                                # Market data files (one per ticker)
   │   └── Portfolios/                          # Portfolio weight files
   └── Output/                                  # Backtest results

Step 1 — Prepare Market Data
-----------------------------

Place one file per ticker in ``Input/Data/``. Supported formats are CSV and Parquet.

Each file must contain a date column and at least three price columns. Column names are flexible — you will map them in the configuration file.

**Example CSV** (``Input/Data/AAPL.csv``):

.. code-block:: text

   date,vwap,vwap_adjusted,close_adjusted
   2020-01-02,74.06,74.06,73.87
   2020-01-03,74.34,74.34,73.07

Step 2 — Prepare Portfolio Files
---------------------------------

Place portfolio files in ``Input/Portfolios/``. Supported formats are CSV and Excel. The engine auto-detects horizontal or vertical layout.

**Horizontal format** — first column named ``Ticker``:

.. code-block:: text

   Ticker  | 2020-01-02 | 2022-01-03
   AAPL    | 0.60       | 0.55
   MSFT    | 0.40       | 0.45

**Vertical format** — first column named ``date``:

.. code-block:: text

   date       | AAPL | MSFT
   2020-01-02 | 0.60 | 0.40
   2022-01-03 | 0.55 | 0.45

.. note::

   Weights must sum to 1.0 or less per rebalancing date. The remainder is held as cash.

Step 3 — Configure the Excel File
-----------------------------------

Open ``Config/backtest_engine_parameters.xlsx``. Edit the values in **column B** only. Column A contains parameter names (do not modify) and column C contains descriptions.

**Backtest settings**

.. list-table::
   :widths: 35 30 35
   :header-rows: 1

   * - Parameter
     - Example value
     - Description
   * - ``portfolio_name``
     - ``my_portfolio``
     - Filename of your portfolio (without extension)
   * - ``benchmark_file_name``
     - ``SPY``
     - Benchmark ticker; a market data file for it must exist in the data directory
   * - ``start_date``
     - ``2020-01-02``
     - Backtest start date. Use ``auto`` to derive from the portfolio file
   * - ``end_date``
     - ``2024-12-31``
     - Backtest end date. Use ``auto`` to derive from the portfolio file
   * - ``initial_capital``
     - ``1000000``
     - Starting capital in currency units
   * - ``cash_reserve_percentage``
     - ``0.01``
     - Fraction of portfolio held as cash on each rebalance (0.0–0.50)
   * - ``commission_cents``
     - ``0.05``
     - Per-share commission in dollars (0.0–0.10)

**File paths and formats**

.. list-table::
   :widths: 40 25 35
   :header-rows: 1

   * - Parameter
     - Example value
     - Description
   * - ``input_market_data_directory``
     - ``Input/Data``
     - Path to market data directory
   * - ``input_portfolio_directory``
     - ``Input/Portfolios``
     - Path to portfolio directory
   * - ``backtest_results_output_directory``
     - ``Output``
     - Path where results are saved
   * - ``market_data_input_format``
     - ``csv``
     - ``csv`` or ``parquet``
   * - ``portfolio_input_format``
     - ``csv``
     - ``csv`` or ``excel``

**Column name mappings**

These values must exactly match the column headers in your data files.

.. list-table::
   :widths: 40 25 35
   :header-rows: 1

   * - Parameter
     - Example value
     - Purpose
   * - ``commission_price_column``
     - ``vwap``
     - Share count for commission calculation (typically unadjusted VWAP)
   * - ``trade_execution_price_column``
     - ``vwap_adjusted``
     - Price for buying/selling shares
   * - ``mark_to_market_price_column``
     - ``close_adjusted``
     - Daily portfolio valuation between rebalances
   * - ``date_column``
     - ``date``
     - Column containing trading dates

**System settings**

.. list-table::
   :widths: 35 25 40
   :header-rows: 1

   * - Parameter
     - Example value
     - Description
   * - ``logger_level``
     - ``error``
     - Log verbosity: ``debug``, ``info``, ``warning``, ``error``, ``critical``

Step 4 — Run the Backtest
--------------------------

From the project root directory:

.. code-block:: console

   python __main__.py

Or via the CLI:

.. code-block:: console

   python -m kaxanuk.backtest_engine.services.cli autorun

Results are saved in ``Output/``, including an Excel report with performance metrics and an interactive dashboard.

Pre-Execution Checklist
-----------------------

Before running, verify:

- Market data files exist in ``Input/Data/`` for every ticker in the portfolio **and** the benchmark
- Portfolio files are in ``Input/Portfolios/`` with correct format
- Weights sum to 1.0 or less per rebalancing date
- All rebalancing dates in the portfolio exist within the market data date range
- Column names in the Excel config (``commission_price_column``, ``trade_execution_price_column``, ``mark_to_market_price_column``, ``date_column``) match the actual headers in your data files
- ``Config/.env`` contains your license key