Back Testing

Backtesting allows you to evaluate the performance of your prediction models using historical data. Inverse Watch provides tools to seamlessly analyze prediction results and implement basic trading strategies.

Accessing Predictions

While prediction results are available through the predictions page, it's often more convenient to access them programmatically. Inverse Watch also provides an efficient way to retrieve prediction results using the Query Results query runner.

Query Results data source

You can access prediction results using SQL-like queries in the Query Results data source. There are two main ways to retrieve predictions:

1. By Prediction ID:

   SELECT * FROM prediction_{prediction_id}

   Replace {prediction_id} with the specific ID of the prediction you want to retrieve.

2. Latest Prediction for a Model:

   SELECT * FROM prediction_m_{model_id}

   Replace {model_id} with the ID of your model. This query will return the most recent prediction for that model.

Example Usage

Let's say your model ID is 11 (as shown in the earlier Discord notification). To get the latest prediction results, you would use:

SELECT * FROM prediction_m_11

Correspondingly if your generated prediction id was 148 you would use :

SELECT * FROM prediction_148

Back Testing

After training and evaluating your models, backtesting helps analyze the prediction results and simulate trading strategies. Inverse Watch’s data visualization capabilities allow for insightful analysis of model performance.

Data Preparation

For each model (Linear, Random Forest, Neural Network, Gradient Boosting, and AdaBoost), we create a CTE (Common Table Expression) to prepare the prediction data:

WITH prediction_data_linear AS (
    SELECT 
        timestamp,
        price AS actual_price,
        pred_price as pred_price_linear,
        LEAD(price) OVER (ORDER BY timestamp) AS next_actual_price,
        pred_log_price_change AS pred_log_price_change_linear,
        LAG(price) OVER (ORDER BY timestamp) AS previous_price
    FROM prediction_{{linear}}
),

• LEAD and LAG functions fetch the next and previous prices.

• This CTE fetches actual prices, predicted prices, and predicted log price changes from the prediction table.

Price Prediction and Trade Signal

Next, calculate predicted prices and generate trade signals for each model:

predicted_prices_linear AS (
    SELECT 
        timestamp,
        actual_price,
        pred_price_linear,
        next_actual_price,
        previous_price * exp(pred_log_price_change_linear) AS pred_price_log_linear,
        CASE 
            WHEN previous_price * exp(pred_log_price_change_linear) > actual_price THEN 1
            ELSE 0
        END AS trade_linear,
        CASE 
            WHEN previous_price * exp(pred_log_price_change_linear) > actual_price THEN 1
            WHEN previous_price * exp(pred_log_price_change_linear) < actual_price THEN -1
            ELSE 0
        END AS short_linear
    FROM prediction_data_linear
    WHERE previous_price IS NOT NULL
),

• Trade Signals:

  • Simple buy and sell: If the predicted price is higher than the actual price, generate a buy signal (1 for buy, 0 for no action).

  • Long/Short strategy: If the predicted price is higher or lower than the actual price, generate long (1) or short (-1) signals.

Combining Results

You can join the results from different models into a unified result set:

full_results AS (
    SELECT 
        DATETIME(lin.timestamp, 'unixepoch') as timestamp,
        -- Fields for each model...
    FROM predicted_prices_linear lin
    LEFT JOIN predicted_prices_rf rf ON lin.timestamp = rf.timestamp
    LEFT JOIN predicted_prices_nn nn ON lin.timestamp = nn.timestamp
    LEFT JOIN predicted_prices_gb gb ON lin.timestamp = gb.timestamp
    LEFT JOIN predicted_prices_ada ada ON lin.timestamp = ada.timestamp
),

full_results: This combines the predictions from all models (e.g., Linear Regression, Random Forest, Neural Network) into a single dataset for comparison.

PnL Calculation

The pnl_calculation CTE calculates the profit and loss for each trade:

pnl_calculation AS (
    SELECT 
        timestamp,
        CASE 
            WHEN LAG(trade_linear) OVER (ORDER BY timestamp) = 1 THEN (next_actual_price - actual_price) / actual_price * 10000
            ELSE 0
        END AS pnl_linear,
        -- Similar calculations for other models...
    FROM full_results
    WHERE row_num <= total_rows  * {{test_size}}
),

• PnL is calculated only when a trade signal was generated in the previous period.

• The calculation assumes a fixed position size of 10,000 units.

Balance Calculation

Keep track of account balance over time using a cumulative sum of PnL:

balance_calculation AS (
    SELECT 
        timestamp,
        pnl_linear,
        10000 + SUM(pnl_linear) OVER (ORDER BY timestamp) AS balance_linear,
        -- Similar calculations for other models...
    FROM pnl_calculation
),

• Starting balance is assumed to be 10,000 units.

• Running total is calculated using a cumulative sum of PnL.

Final Results

The final SELECT statement combines all the calculated fields and orders the results by timestamp:

SELECT *
FROM full_results_with_balance
ORDER BY timestamp ASC;

Interpreting the Results

This query allows us to compare the performance of different models:

1. Prediction Accuracy: Compare pred_price_* and pred_price_log_* with actual_price and next_actual_price.

2. Trade Signals: Analyze the trade_* short_* columns to see how often each model generates buy signals.

3. PnL: The pnl_* columns show the profit or loss for each trade.

4. Overall Performance: The balance_* columns provide a running total of the account balance, indicating overall performance of each model.

Key Insights

• Prediction Accuracy: By comparing predicted prices and log price changes with actual data, you can determine the most accurate model.

• Trade Signals: Analyze the frequency and success rate of trade signals generated by each model.

• Profitability: Use the PnL and balance columns to evaluate the profitability of each trading strategy. This simplified backtest does not account for transaction costs, slippage, or market impact, so keep these factors in mind when interpreting results.