2019-10-02: The community is currently in read-only mode
GenericCSVData Data Issue - dates off by one day in Excel
derek2017 last edited by derek2017
I have a strange issue using GenericCSVData.
As you can see, in orc-2014.txt, the dates start from 2014-1-1 till 2014-12-31.
2014-12-22,45.57,46.05,45.41,45.65,21264400,45.53 2014-12-23,45.53,46.50,45.46,46.01,14042400,45.89 2014-12-24,46.36,46.71,46.15,46.23,10238200,46.10 2014-12-26,46.19,46.50,46.07,46.10,6893500,45.98 2014-12-29,46.02,46.09,45.60,45.61,9701400,45.49 2014-12-30,45.55,45.66,45.29,45.34,9968400,45.22 2014-12-31,45.45,45.56,44.97,44.97,13269200,44.85
Below is taken from the tutorial:
from __future__ import (absolute_import, division, print_function, unicode_literals) import datetime # For datetime objects import os.path # To manage paths import sys # To find out the script name (in argv) # Import the backtrader platform import backtrader as bt # Create a Stratey class TestStrategy(bt.Strategy): def log(self, txt, dt=None): ''' Logging function fot this strategy''' dt = dt or self.datas.datetime.date(0) print('%s, %s' % (dt.isoformat(), txt)) def __init__(self): # Keep a reference to the "close" line in the data dataseries self.dataclose = self.datas.close # To keep track of pending orders self.order = None def notify_order(self, order): if order.status in [order.Submitted, order.Accepted]: # Buy/Sell order submitted/accepted to/by broker - Nothing to do return # Check if an order has been completed # Attention: broker could reject order if not enougth cash if order.status in [order.Completed]: if order.isbuy(): self.log('BUY EXECUTED, %.2f' % order.executed.price) elif order.issell(): self.log('SELL EXECUTED, %.2f' % order.executed.price) self.bar_executed = len(self) elif order.status in [order.Canceled, order.Margin, order.Rejected]: self.log('Order Canceled/Margin/Rejected') # Write down: no pending order self.order = None def next(self): # Simply log the closing price of the series from the reference # self.log('Close, %.2f' % self.dataclose) # Check if an order is pending ... if yes, we cannot send a 2nd one if self.order: return if __name__ == '__main__': # Create a cerebro entity cerebro = bt.Cerebro() # Add a strategy cerebro.addstrategy(TestStrategy) cerebro.addobserver(bt.observers.DrawDown) cerebro.addobserver(bt.observers.TimeReturn) # Datas are in a subfolder of the samples. Need to find where the script is # because it could have been called from anywhere data = bt.feeds.GenericCSVData( dataname='datas/orcl-2014.txt', nullvalue=0.0, dtformat=('%Y-%m-%d'), datetime=0, time=-1, high=2, low=3, open=1, close=4, volume=5, openinterest=-1) # Add the Data Feed to Cerebro cerebro.adddata(data) # Set our desired cash start cerebro.broker.setcash(100000.0) cerebro.addwriter(bt.WriterFile, csv=True) # Print out the starting conditions print('Starting Portfolio Value: %.2f' % cerebro.broker.getvalue()) # Run over everything cerebro.run() # Print out the final result print('Final Portfolio Value: %.2f' % cerebro.broker.getvalue())
Output shown in terminal:
249,orcl-2014,249,2014-12-26 23:59:59.999989,46.19,46.5,46.07,46.1,6893500.0,0.0,TestStrategy,249,735593.9999999999,Broker,249,100000.0,100000.0,BuySell,249,,,Trades,249,,,DrawDown,249,0.0,0.0,TimeReturn,249,0.0 2014-12-29, Close, 45.61 250,orcl-2014,250,2014-12-29 23:59:59.999989,46.02,46.09,45.6,45.61,9701400.0,0.0,TestStrategy,250,735596.9999999999,Broker,250,100000.0,100000.0,BuySell,250,,,Trades,250,,,DrawDown,250,0.0,0.0,TimeReturn,250,0.0 2014-12-30, Close, 45.34 251,orcl-2014,251,2014-12-30 23:59:59.999989,45.55,45.66,45.29,45.34,9968400.0,0.0,TestStrategy,251,735597.9999999999,Broker,251,100000.0,100000.0,BuySell,251,,,Trades,251,,,DrawDown,251,0.0,0.0,TimeReturn,251,0.0 2014-12-31, Close, 44.97 252,orcl-2014,252,2014-12-31 23:59:59.999989,45.45,45.56,44.97,44.97,13269200.0,0.0,TestStrategy,252,735598.9999999999,Broker,252,100000.0,100000.0,BuySell,252,,,Trades,252,,,DrawDown,252,0.0,0.0,TimeReturn,252,0.0
output show in excel
249 orcl-2014 249 2014-12-27 46.19 46.5 46.07 46.1 6893500 0 TestStrategy 249 735594 Broker 249 100000 100000 250 orcl-2014 250 2014-12-30 46.02 46.09 45.6 45.61 9701400 0 TestStrategy 250 735597 Broker 250 100000 100000 251 orcl-2014 251 2014-12-31 45.55 45.66 45.29 45.34 9968400 0 TestStrategy 251 735598 Broker 251 100000 100000 252 orcl-2014 252 2015-1-1 45.45 45.56 44.97 44.97 13269200 0 TestStrategy 252 735599 Broker 252 100000 100000
The issue is when i open the results in Excel, date for the last row is showing as 2015-1-1(it should be 2014-12-31 as shown in the source). Actually all the dates are off by one day.
I guess this is because the date
2014-12-31was changed to
2014-12-31 23:59:59.999989, and Excel automatically convert it to
So why the time
23:59:59.999989was added? Is it possible to add
The reason to have the daily with a time is to allow synchronization with sub-daily timeframes. If the timestamp for daily bar is not located at the end of the day, the day would happen before any sub-daily bar (because the default in Python is to have a
00:00:00.0timestamp if no other is specified)
dt = dt or self.datas.datetime.date(0) print('%s, %s' % (dt.isoformat(), txt))
The code is printing with
dt.isoformat()and adding the timestamp. if you prepare the string using
date()(as seen one line above) you will get a date-only print out, which will be correctly loaded by Excel.
derek2017 last edited by derek2017
Thanks for the reply!
Actually the output in question is generated by Writer
So my question is how can I get a date-only print out when using writer?