backtest avec Excell

On va aller chercher les bougies sur l'API de Kraken: https://api.kraken.com/0/public/OHLC?pair=XBTEUR&interval=1440. les data sont en format JSON, on va passer par python pour le sauvegarder en local en format CSV:

pip install openpyxl

import sys
import os.path
import requests
import pandas as pd

columns = ['time','open','high','low','close','vwap','volume','count']
url = 'ht//api.kraken.com/0/public/OHLC?pair=XBTEUR&interval=1440'
data = requests.get(url).json()
ohlc = data['result']['XXBTZEUR']
df = pd.DataFrame(ohlc, columns = columns).astype(float)
df.to_excel('XXBTZEUR_1440.xlsx',index=False,engine='openpyxl')
print()

Heu... franchement, avec Excell, c'est chiant ;-)

backtest avec Python - comparatif

df = pd.read_csv('XBTEUR_1440.csv',
    names=['time','open','high','low','close','volume','count'])
df['close'] = df.close.replace(to_replace=0, method='ffill')

df['RSI'] = ta.RSI(df.close, timeperiod=14)
df['SIG_achat'] = (df.RSI.shift() < 25) & (df.RSI > 25)
df['SIG_vente'] = (df.RSI.shift() > 75) & (df.RSI < 75)
df['POS'] = (df.SIG_achat.astype(int) - df.SIG_vente.astype(int))
df['POS'] = df.POS.replace(to_replace=0, method='ffill') > 0

df['r_hodl'] = np.log(df['close'] / df['close'].shift() )
df['r_strat'] = np.where(df['POS'].shift(), df['r_hodl'], 0)
df['r_fee'] = np.where(df['POS'] != df['POS'].shift(), 0.0025, 0)
df['r_net'] = df['r_strat'] - df['r_fee']

# Strategy begin
RSI = ta.RSI(df.close, timeperiod=14)
SIG_achat = (RSI.shift() < 25) & (RSI > 25)
SIG_vente = (RSI.shift() > 75) & (RSI < 75)
# Strategy end

POS = (SIG_achat.astype(int) - SIG_vente.astype(int))
POS = POS.replace(to_replace=0, method='ffill') > 0

r_hodl = df.close / df.close.shift()
lr_hodl = np.log(df.close / df.close.shift())

r_strat = np.where(POS.shift() == 1, r_hodl, 1)
logr_strat = np.where(POS.shift() == 1, lr_hodl, 0)
lr_strat = lr_hodl * (POS.shift() != 0)

r_fee = np.where(POS != POS.shift(), 1.0025, 1)
logr_fee = np.where(POS != POS.shift(), 0.0025, 0)
lr_fee = 0.0025 * (POS != POS.shift())

r_netto = r_strat / r_fee
logr_netto = logr_strat - logr_fee
lr_netto = lr_strat - lr_fee

r_ret = r_netto.cumprod()
logr_ret = np.exp(logr_netto.cumsum())
lr_ret = np.exp(lr_netto.cumsum())


print()

Donne 4 résultats différents:

In [9]: np.exp(df.r_net.cumsum())
Out[9]:
0            NaN
1       1.000000
2       1.000000
3       1.000000
4       1.000000
...
4121    2.558979
4122    2.558979
4123    2.558979
4124    2.558979
4125    2.558979
Name: r_net, Length: 4126, dtype: float64
In [5]: r_ret
Out[5]:
array([0.99750623, 0.99750623, 0.99750623, ..., 2.55277239, 2.55277239, 2.55277239])
In [6]: logr_ret
Out[6]: array([0.99750312, 0.99750312, 0.99750312, ..., 2.55258922, 2.55258922, 2.55258922])
In [7]: lr_ret
Out[7]:
0            NaN
1       1.000000
2       1.000000
3       1.000000
4       1.000000
...
4121    2.558979
4122    2.558979
4123    2.558979
4124    2.558979
4125    2.558979
Length: 4126, dtype: float64

So strange!

To be continued...