Invalid daily aggregation of OHLC data with timezone/datetime offset
- Dominant language
- Python
- Stars
- 9k
- Forks
- 1.5k
- PR merge metrics
- No merged PRs in 30d
Description
### Expected Behavior
resample('D') to take in account the right trading day when using timezoneoffset dates (issue with date parsing?)
### Actual Behavior
Resample('D') of hourly candle puts equity sample on weekend instead of friday, when position entry was clearly on friday, equity balance should also be on friday instead of saturday.
### Steps to Reproduce
Added log lines (see below) and ran sample strategy on AAPL1H timeframe exported from tradingview (only way to get correct candles plotted and entries plotted in my timezone +8, is to also add the timezoneoffset to the export) for both plotting and 100% same entry/exits (can see in trades table - except last position entry is using final candle.close instead of final candle.open in backtesting.py)
Comparing with my C# code, where starting equity is at day end of Friday 2022-08-26 - 10077.1, where on backtesting.py it's moved to saturday, leading to incorrect results on lower timeframes.
I have compared daily backtest of 'D' in both my program and backtesting.py, results are equal, so I think backtesting.py is not taking datetimeoffset into account for candles with lower interval
```python
day_returns = np.array(np.nan)
annual_trading_days = np.nan
if isinstance(index, pd.DatetimeIndex):
day_returns = equity_df['Equity'].resample('D').last().dropna().pct_change()
equity_df['Equity'].to_csv("Equity.csv")
equity_df['Equity'].resample('D').last().dropna().to_csv("EquityD.csv")
```
```python
class SmaCross(Strategy):
n1 = 50
n2 = 100
def init(self):
close = self.data.Close
self.sma1 = self.I(SMA, close, self.n1)
self.sma2 = self.I(SMA, close, self.n2)
def next(self):
if crossover(self.sma1, self.sma2):
self.buy()
elif crossover(self.sma2, self.sma1):
self.sell()
bt = Backtest(AAPL1H, SmaCross,
cash=10000, commission=.00,
exclusive_orders=True,)
```
### Additional info
[AAPL1H.csv](https://github.com/kernc/backtesting.py/files/11738972/AAPL1H.csv)
[Equity.csv](https://github.com/kernc/backtesting.py/files/11739023/Equity.csv)
[EquityD.csv](https://github.com/kernc/backtesting.py/files/11739024/EquityD.csv)

Some C# logic I wrote shows first change in portfolio balance on friday 2022-08-26

backtesting.py logic shows first change in portfolio balance on saturday 2022-08-27

- Backtesting version: 0.3.3
- `bokeh.__version__`: 3.1.1
- OS: Win 10
Contributor guide
Research direction
Start by reproducing the issue with AAPL1H.csv and the shown equity_df['Equity'].resample('D').last() calculation. Trace how timezone-offset timestamps reach the daily aggregation in backtesting.py, then verify that the Friday 2022-08-26 equity change remains on Friday and that existing daily results are unchanged.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- numpy, pandas, python
- Domain
- backend, data
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100