Construire un backtest simple dans un tableur
Construisez dans un tableur un backtest complet, colonne par colonne, d'une règle de moyenne mobile et vérifiez-le pour détecter les erreurs.
Ce que vous allez apprendre
- La disposition standard des colonnes d'un backtest dans un tableur.
- Les formules du signal, de la position, du rendement de la stratégie, de la courbe des capitaux et du drawdown.
- Comment décaler la position d'une ligne pour éviter le biais d'anticipation.
- Cinq vérifications qui détectent la plupart des erreurs de tableur.
Explication
Un backtest dans un tableur est lent pour les tests de grande taille, mais chaque nombre est visible, ce qui en fait le meilleur endroit pour apprendre. Vous allez tester la règle de moyenne mobile de la leçon 2, ici avec une moyenne mobile simple (SMA) sur 3 mois afin que le tableau reste court.
La disposition des colonnes
La ligne 1 contient les en-têtes ; le mois 1 se trouve en ligne 2, donc le mois m se trouve en ligne m+1. La cellule K1 contient le coût par changement de position (ici 0.2%).
| Colonne | Contenu | Formule (ligne 5 = mois 4) |
|---|---|---|
| A | Mois (ou date) | données |
| B | Clôture ajustée | données |
| C | SMA sur 3 mois | =AVERAGE(B3:B5) |
| D | Signal à la clôture (1 = investi, 0 = liquidités ; une égalité conserve le signal précédent) | =IF(B5>C5,1,IF(B5<C5,0,D4)) |
| E | Position détenue pendant le mois | =D4 |
| F | Rendement de l'actif | =B5/B4-1 |
| G | Rendement de la stratégie après coûts | =E5*F5-IF(E5<>E4,$K$1,0) |
| H | Capital (départ 100) | =H4*(1+G5) |
| I | Plus haut courant | =MAX($H$4:H5) |
| J | Drawdown | =H5/I5-1 |
Dans les versions de tableur en langue allemande, les noms de fonctions et les séparateurs diffèrent, par exemple MITTELWERT au lieu de AVERAGE, WENN au lieu de IF, et des points-virgules au lieu des virgules.
L'idée clé : le décalage d'une ligne
La colonne E copie le signal de la ligne précédente. Le signal n'est connu qu'à la clôture du mois m ; il ne peut donc rapporter, au plus tôt, que le rendement du mois m+1. Si vous multipliez le signal d'une ligne par le rendement de cette même ligne, le test « connaît » le résultat du mois avant de décider. Cette seule erreur fait paraître de nombreux backtests sur tableur bien trop bons.
Capital et drawdown
La courbe des capitaux (equity curve) est la valeur du compte au fil du temps, ici sous forme d'indice partant de 100. Les rendements sont composés : chaque mois multiplie la valeur précédente. Le drawdown est la baisse en pourcentage par rapport au capital le plus élevé atteint jusque-là (le plus haut courant) ; les calculs détaillés de drawdown et de récupération ont été traités dans le cours « Gestion du risque et des trades ».
Exemple chiffré
Exemple hypothétique : clôtures mensuelles €100, €104, €108, €106, €101, €97, €99, €104, €109, €112. Coût : 0.2% par changement de position (une approximation simple du spread et des frais, leçon 8). Les impôts et les intérêts sur les liquidités sont exclus. Le capital part de 100 à la clôture du mois 3, premier mois disposant d'une SMA ; la position avant cette date est 0.
| Mois | Clôture | SMA | Signal | Position | Rendement de l'actif | Rendement de la stratégie | Capital | Drawdown |
|---|---|---|---|---|---|---|---|---|
| 3 | 108 | 104.00 | 1 | 0 | – | – | 100.00 | 0.00% |
| 4 | 106 | 106.00 | 1 (égalité) | 1 | −1.85% | −2.05% | 97.95 | −2.05% |
| 5 | 101 | 105.00 | 0 | 1 | −4.72% | −4.72% | 93.33 | −6.67% |
| 6 | 97 | 101.33 | 0 | 0 | −3.96% | −0.20% | 93.14 | −6.86% |
| 7 | 99 | 99.00 | 0 (égalité) | 0 | +2.06% | 0.00% | 93.14 | −6.86% |
| 8 | 104 | 100.00 | 1 | 0 | +5.05% | 0.00% | 93.14 | −6.86% |
| 9 | 109 | 104.00 | 1 | 1 | +4.81% | +4.61% | 97.43 | −2.57% |
| 10 | 112 | 108.33 | 1 | 1 | +2.75% | +2.75% | 100.11 | 0.00% |
Vérifications pour trois lignes :
- Mois 4 : position 1 (issue du mois 3), le coût d'entrée s'applique : −1.85% − 0.20% = −2.05%. Capital : 100 × (1 − 0.0205) = 97.95.
- Mois 6 : la position passe à 0, seul le coût de sortie s'applique : −0.20%. Capital : 93.33 × 0.998 ≈ 93.14. Drawdown : 93.14 ÷ 100 − 1 = −6.86%.
- Mois 9 : nouvelle entrée : +4.81% − 0.20% = +4.61%. Capital : 93.14 × 1.0461 ≈ 97.43.
Résultat : capital de 100.11 (+0.11%) avec 3 changements de position et un drawdown maximal de −6.86%. Achat-conservation (buy-and-hold) du mois 3 au mois 10 : €112 ÷ €108 − 1 = +3.70%. Avec la formule plus simple =IF(B5>C5,1,0), l'égalité du mois 4 aurait entraîné une sortie un mois plus tôt, et le résultat aurait été d'environ +5.07%. Un seul cas particulier a modifié le résultat d'environ 5 points de pourcentage. Huit mois ne prouvent rien sur la règle ; l'objectif ici est la mécanique.
Erreur fréquente
=D5*F5 au lieu de =E5*F5 permet à la stratégie d'encaisser le rendement du mois au cours duquel le signal s'est formé. Demandez-vous toujours : « Cette cellule était-elle connue avant le rendement par lequel elle est multipliée ? »Exercice pratique
Cinq vérifications pour tout backtest sur tableur. Appliquez-les sur papier au tableau ci-dessus et notez ce que vous devriez observer si le test était correct.
- Comptez les changements de position et comparez-les aux coûts imputés.
- Recalculez à la main une ligne choisie au hasard.
- Mettez le coût à 0 et la position à 1 partout : le résultat doit être égal à celui de l'achat-conservation.
- Vérifiez que la première ligne de position utilise un signal provenant d'une ligne antérieure.
- Vérifiez que le drawdown n'est jamais positif.
Afficher la solution
1. Changements aux mois 4, 6 et 9 = 3 ; coûts imputés 3 × 0.20% = 0.60% au total. Cela concorde.
2. Exemple du mois 5 : position 1 × (€101 ÷ €106 − 1) = −4.72%, aucun changement, donc aucun coût ; 97.95 × 0.9528 ≈ 93.33. Cela concorde.
3. Avec la position à 1 et sans coût, le capital devient 100 × 112 ÷ 108 ≈ 103.70, soit le résultat de l'achat-conservation de +3.70%.
4. La position du mois 4 (1) provient du signal du mois 3. C'est correct.
5. Tous les drawdowns sont de 0.00% ou négatifs. C'est correct.
Limites : le tableau suppose une négociation à la clôture de fin de mois à un coût fixe ; les exécutions réelles ont lieu à l'ouverture suivante, avec des spreads variables et du slippage (leçon 8).
Points clés à retenir
- Utilisez des colonnes distinctes pour le signal et la position, la position étant décalée d'une ligne vers le bas.
- Composez les rendements en une courbe des capitaux et calculez le drawdown à partir du plus haut courant.
- Imputez les coûts à chaque changement de position, pas chaque mois.
- Effectuez les cinq vérifications avant de vous fier à un résultat de tableur.
Prochaine étape
Dans la leçon suivante, vous transformerez une courbe des capitaux comme celle-ci en indicateurs de performance standard : CAGR, drawdown, ratio de Sharpe et profit factor.
Auto-évaluation
Répondez aux questions, puis vérifiez vos réponses. Vous pouvez recommencer autant de fois que vous le souhaitez.
Auto-évaluation automatique pour vos propres révisions. Vos réponses sont vérifiées dans votre navigateur ; nous ne les voyons pas et ne les évaluons pas. Ce n'est ni un examen ni une qualification.
Votre progression
« Terminée » signifie seulement que vous avez marqué la leçon comme lue. Ce n'est pas un test de votre compréhension. La progression et les notes sont enregistrées uniquement dans ce navigateur, sur cet appareil — pas dans un compte.