Daily holdings and returns time series (backtest and live)

[quant5]

Describe your environment

(if applicable)

• Operating system: Windows
• Python Version: 3.9.12 (python -V)
• CCXT version: 1.92.84 (pip freeze | grep ccxt)
• Freqtrade Version: 2022.9.dev (freqtrade -V or docker-compose run --rm freqtrade -V for Freqtrade running in docker)

Describe the enhancement

I would be interested in examining daily (/ custom frequency) timeseries of holdings and returns. E.g., what is a snapshot of my holdings and the total value of my holdings + cash (e.g., NAV) at the end of each trading day.

I've not discovered this functionality in the current repo - the closest thing I found is the daily_profit series in backtesting, which, while helpful, only tabulates profit on closed trades, and isn't great for lower frequency strategies (since profit is 0 if a position is open). If the functionality is there and I've overlooked, please let me know.

Holdings and returns can either be (1) calculated at each iteration during runtime or (2) derived from an (existing) combination of trades and price data, and would be a useful tool for determining portfolio analytics, as well as allow future integrations with tools such as Pyfolio, Quantstats, and potentially many others.

Thoughts are appreciated - thanks.

[quant5]

Some (poorly tested, illustrative only) sample code that generates aforementioned timeseries. I used a representative lower-frequency strategy with hold time 8 days.

# use opens and closes to approximate daily position amount by pair
opens = trades.groupby(['pair', 'open_date'])['amount'].sum()
opens.index.names = ('pair', 'date')
closes = trades.groupby(['pair', 'close_date'])['amount'].sum() * 0
closes.index.names = ('pair', 'date')
positions = pd.concat([opens, closes]).sort_index()
positions = positions[~positions.index.duplicated(keep='last')]

# use similar logic to add cash balances for sold pairs
cash_open = trades.groupby(['pair', 'open_date'])['amount'].sum() * 0
cash_open.index.names = ('pair', 'date')
cash_close = trades.groupby(['pair', 'close_date']).apply(lambda x: (x.amount * x.close_rate).sum())
cash_close.index.names = ('pair', 'date')
cash_positions = pd.concat([cash_open, cash_close]).sort_index()
cash_positions = cash_positions[~cash_positions.index.duplicated(keep='last')]

# get pair history
candle_df = {}
for pair in positions.index.levels[0].unique():    
    candles = load_pair_history(
        datadir=data_location,
        timeframe='1d',
        pair=pair,
        data_format="jsongz",
        candle_type=CandleType.SPOT,
    )
    candle_df[pair] = candles.set_index('date')['close']
candle_df = pd.concat(candle_df, axis=1)

# turn position and cash position dataframes "square" and merge together
positions = positions.unstack(level=0).resample('1d').ffill() * candle_df
cash_positions = cash_positions.unstack(level=0).resample('1d').ffill()
daily = positions.sum(axis=1) + cash_positions.sum(axis=1)

Below I compare the daily df I made above (first plot) with the daily_profit df (second plot):

Note this method makes quite a few assumptions and has limitations (such as not having knowledge of actual cash balances), again the purpose is only to illustrate the desired return timeseries.

[quant5]

It's worth also mentioning that metrics in freqtrade.data.metrics are calculated off of the trades df, whilst calculating them from the daily returns df I made will yield different results. For example, from eyeballing you can tell that the peak-to-trough drawdowns in the first plot are much more severe than the ones shown by daily_profit, since there are many times when positions moved up sharply before falling back down and being sold.

[xmatthias]

in live - you won't be able to do this - as freqtrade wont have the historical data available.
you therefore only really have a chance to do this historically if you do a daily snapshot (doesn't have to be at midnight - we do usually have more than 1 day of data except for 1m candles).

#3059 (and the corresponding issue #3020) aimed to do something similar (although poorly describing what it's actually for).
If you're interested - have a look at that - it'd be great to have that finished - but in the end, i didn't really find a valid usecase where it actually would've made sense to merge that PR. Merging stuff we ain't gonna use is however also pointless.

you may also wanna read through the discussion in #4497 - while the title suggests otherwise - the discussion then turned towards "daily balance snapshots" at some point - which is what you'd need for live.

---

I don't think your code works correctly though - assuming a 1d frequency - you'll have to make sure trades are replicated once per day (assuming a can be open 4-5 days, it'll have to be counted on each of these days - which i currently don't really see after a quick skim).

It's also definitely wrong when using the trade_adjust feature - where the same trade could be a 100$ stake one day, 1000$ stake the next day, and reduce to 500$the 3rd day - so your holdings will have to be calculated differently for each day (which will make this rather complex to calculate) - otherwise you're double-counting (or not counting) parts of your wallet.

[quant5]

Thanks for the resources. I will see if I have some capacity to build + share something like this. I'll probably start with backtesting as the usecase would primarily be to drive analytics. But please let me know if you (and/or others) agree that this functionality would even be useful / worth having. For example, in a (once) popular backtesting framework called backtrader, the resulting frames of the result analyzer can be imported straight into Pyfolio or Quantstats: https://github.com/mementum/backtrader/blob/master/backtrader/analyzers/pyfolio.py

Quick q: for backtesting, is there any way to see the portfolio's outstanding cash balance, or does it have to be reverse engineered after the fact by the trades table?

On the code, I think the resample + ffill lines in the code do the first thing you're worried about. I agree that I didn't consider trade_adjust and will look into it more.

[xmatthias]

for backtesting, is there any way to see the portfolio's outstanding cash balance

well - in your strategy, you can use the .wallets just as you would in live (at least in callbacks).