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.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[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.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-31 was changed to 2014-12-31 23:59:59.999989 , and Excel automatically convert it to 2015-1-1.

    So why the time 23:59:59.999989 was added? Is it possible to add 00:00:00 instead?

    Thanks.


  • administrators

    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.0 timestamp 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 using date() (as seen one line above) you will get a date-only print out, which will be correctly loaded by Excel.



  • @backtrader

    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.0
    

    Actually 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!


Log in to reply
 

Looks like your connection to Backtrader Community was lost, please wait while we try to reconnect.