GenericCSVData Data Issue - dates off by one day in Excel
-
Hi,
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.
orcl-2014.txt
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.85Below 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[0]) # 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[0].datetime.date(0) print('%s, %s' % (dt.isoformat(), txt)) def __init__(self): # Keep a reference to the "close" line in the data[0] dataseries self.dataclose = self.datas[0].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[0]) # 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.0output 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 100000The 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 to2014-12-31 23:59:59.999989, and Excel automatically convert it to2015-1-1.So why the time
23:59:59.999989was added? Is it possible to add00:00:00instead?Thanks.
-
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)@derek2017 said in GenericCSVData Data Issue - dates off by one day in Excel:
dt = dt or self.datas[0].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 usingdate()(as seen one line above) you will get a date-only print out, which will be correctly loaded by Excel. -
Thanks for the reply!
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.0Actually the output in question is generated by Writer
cerebro.addwriter(bt.WriterFile, csv=True).So my question is how can I get a date-only print out when using writer?
Thanks!