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.



    Below is taken from the tutorial:

    from __future__ import (absolute_import, division, print_function,
    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]
            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
            # 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:
    if __name__ == '__main__':
        # Create a cerebro entity
        cerebro = bt.Cerebro()
        # Add a strategy
        # 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(
        # Add the Data Feed to Cerebro
        # Set our desired cash start
        cerebro.addwriter(bt.WriterFile, csv=True)
        # Print out the starting conditions
        print('Starting Portfolio Value: %.2f' %
        # Run over everything
        # Print out the final result
        print('Final Portfolio Value: %.2f' %

    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?


  • 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]
        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?

Log in to reply

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