Back to Community
Issues on history data biweekly resample

Hi everyone

I've been trying to resample the daily history data into a biweekly fashion. To be specific, for any day in the week, get the high, low, and close price of the past two weeks (not including this current week, this weeks HLC will be included next week)

However, while the data is resampled into a two-weekly fashion, the resampled data always changes on different days, sometime Thursday, sometimes Friday. This is very confusing. Can someone help me on this?

Take an example, as the code listed, if you run the backtest from 02/01/2020 to 04/28/2020, you will get the following results (marked in Bold italic). Suddenly during the week, 2020-02-05 biweekly data changes into a different date. Realy confusing.

2020-02-03 15:59 myfunc:27 INFO SPY biweekly close = 2019-12-20 00:00:00+00:00 321.120
2020-01-03 00:00:00+00:00 322.440
2020-01-17 00:00:00+00:00 332.010
2020-01-31 00:00:00+00:00 321.750
2020-02-14 00:00:00+00:00 324.085
Freq: 2W-FRI, Name: Equity(8554 [SPY]), dtype: float64
2020-02-04 15:59 myfunc:27 INFO SPY biweekly close = 2019-12-20 00:00:00+00:00 321.12
2020-01-03 00:00:00+00:00 322.44
2020-01-17 00:00:00+00:00 332.01
2020-01-31 00:00:00+00:00 321.75
2020-02-14 00:00:00+00:00 328.98
Freq: 2W-FRI, Name: Equity(8554 [SPY]), dtype: float64
2020-02-05 15:59 myfunc:27 INFO SPY biweekly close = 2019-12-27 00:00:00+00:00 322.860
2020-01-10 00:00:00+00:00 325.700
2020-01-24 00:00:00+00:00 328.670
2020-02-07 00:00:00+00:00 332.595
Freq: 2W-FRI, Name: Equity(8554 [SPY]), dtype: float64
2020-02-06 15:59 myfunc:27 INFO SPY biweekly close = 2019-12-27 00:00:00+00:00 322.86
2020-01-10 00:00:00+00:00 325.70
2020-01-24 00:00:00+00:00 328.67
2020-02-07 00:00:00+00:00 334.02
Freq: 2W-FRI, Name: Equity(8554 [SPY]), dtype: float64
2020-02-07 15:59 myfunc:27 INFO SPY biweekly close = 2019-12-27 00:00:00+00:00 322.86
2020-01-10 00:00:00+00:00 325.70
2020-01-24 00:00:00+00:00 328.67
2020-02-07 00:00:00+00:00 332.29

Clone Algorithm
3
Loading...
Total Returns
--
Alpha
--
Beta
--
Sharpe
--
Sortino
--
Max Drawdown
--
Benchmark Returns
--
Volatility
--
Returns 1 Month 3 Month 6 Month 12 Month
Alpha 1 Month 3 Month 6 Month 12 Month
Beta 1 Month 3 Month 6 Month 12 Month
Sharpe 1 Month 3 Month 6 Month 12 Month
Sortino 1 Month 3 Month 6 Month 12 Month
Volatility 1 Month 3 Month 6 Month 12 Month
Max Drawdown 1 Month 3 Month 6 Month 12 Month
import quantopian.algorithm as algo
from quantopian.pipeline import Pipeline
from quantopian.pipeline.data.builtin import USEquityPricing
from quantopian.pipeline.filters import QTradableStocksUS
from datetime import date, datetime  
from collections import defaultdict, deque  
import pandas as pd
import numpy as np


def initialize(context):
    context.signal = symbol('SPY')
    schedule_function(myfunc, date_rules.every_day(), time_rules.market_close())

def myfunc(context, data):
    #high = data.history(context.signal, 'high', 30, '1d')
    #low = data.history(context.signal, 'low', 30, '1d')
    close = data.history(context.signal, 'close', 30, '1d')
    
    #prices_biweek_high = high.resample('2W-FRI', closed='right', label='right').apply(max)
    #prices_biweek_low = low.resample('2W-FRI', closed='right', label='right').apply(min)
    #prices_biweek_close = close.resample('2W-FRI', closed='right', label='right').last()
    prices_biweek_close = close.resample('2W-FRI', closed='right', label='right').last()
    
    log.info("SPY biweekly close = " + str(prices_biweek_close))
    
    order_target_percent(context.signal, 0)
There was a runtime error.
8 responses

Can someone help me?

@Vladimir, @Dan Whitnable, @Grant Kiehne

Thank you for your help in advance.

The behavior you are seeing stems from the fact that resample by more than a 1W increment is ambiguous. Resampling 'W-FRI' is easy. It will fetch every Friday. However, resampling '2W-FRI' actually needs one more piece of information. It needs to know which Friday to start counting. Pandas doesn't have an explicit way of setting the 'starting Friday'. So, if one starts resampling with '2W-FRI' this week it will choose one set of Fridays. However, if one starts resampling next week it will choose a different set of Fridays. Unfortunately, one cannot even use 'even' or 'odd' Fridays. Because the last Friday of the year can fall in either the 51, 52, or 53 week, every other Friday is not always even or odd.

So, a workaround is to first define all the possible Fridays one wants to resample on. Use the date_range method something like this

import pandas as pd  
context.resample_dates = pd.date_range(start='1/1/2001',  
                                           end='1/1/2021',  
                                           freq='2W-FRI',  
                                           tz='UTC')

The 'starting Friday' can be set by choosing a different 'start' date. Ensure the 'end' date includes all the resample dates in the backtest. (The attached algo uses the get_environment method to determine this`) .

Now, rather than resample simply select just those dates from close which are in the 'resample_list'. Maybe like this

    close = data.history(context.signal, 'close', 30, '1d')  
    # Use `dropna` to narrow the dates to just those in 'close'.  
    prices_biweek_close = close[context.resample_dates].dropna()

That 'workaround' should resolve the dates abruptly changing issue. However, if markets weren't open on one of the desired Fridays, there won't be any data. This is different from the behavior of the resample method which would fetch the last known price.

There are ways to get prices if markets weren't open but they introduce some other anomalies. Is there a particular reason you wish to check specific 'every other Fridays'?

Clone Algorithm
1
Loading...
Total Returns
--
Alpha
--
Beta
--
Sharpe
--
Sortino
--
Max Drawdown
--
Benchmark Returns
--
Volatility
--
Returns 1 Month 3 Month 6 Month 12 Month
Alpha 1 Month 3 Month 6 Month 12 Month
Beta 1 Month 3 Month 6 Month 12 Month
Sharpe 1 Month 3 Month 6 Month 12 Month
Sortino 1 Month 3 Month 6 Month 12 Month
Volatility 1 Month 3 Month 6 Month 12 Month
Max Drawdown 1 Month 3 Month 6 Month 12 Month
import quantopian.algorithm as algo
from quantopian.pipeline import Pipeline
from quantopian.pipeline.data.builtin import USEquityPricing
from quantopian.pipeline.filters import QTradableStocksUS
from datetime import date, datetime  
from collections import defaultdict, deque  
import pandas as pd
import numpy as np


def initialize(context):
    context.signal = symbol('SPY')
    
    # Create a list of all Fridays one wants to resample
    # Fixed start before earliest backtest and end after this backtests end date
    algo_end = algo.get_environment(field='end').tz_localize(None)
    list_end = algo_end + pd.Timedelta('30D')
    context.resample_dates = pd.date_range(start='1/1/2001', 
                                           end=list_end, 
                                           freq='2W-FRI',
                                           tz='UTC')

    schedule_function(myfunc, date_rules.every_day(), time_rules.market_close())

def myfunc(context, data):
    close = data.history(context.signal, 'close', 30, '1d')
    # select only weeks in the resample_dates list
    prices_biweek_close = close[context.resample_dates].dropna()
                                                      
    log.info("SPY biweekly close = {}".format(prices_biweek_close))
    
There was a runtime error.
Disclaimer

The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by Quantopian. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. No information contained herein should be regarded as a suggestion to engage in or refrain from any investment-related course of action as none of Quantopian nor any of its affiliates is undertaking to provide investment advice, act as an adviser to any plan or entity subject to the Employee Retirement Income Security Act of 1974, as amended, individual retirement account or individual retirement annuity, or give advice in a fiduciary capacity with respect to the materials presented herein. If you are an individual retirement or other investor, contact your financial advisor or other fiduciary unrelated to Quantopian about whether any given investment idea, strategy, product or service described herein may be appropriate for your circumstances. All investments involve risk, including loss of principal. Quantopian makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances.

@Dan Whitnable

Thank you very much for your help. I don't have a reason for checking specific 'every other Fridays'. My aim is to get the ATR data in a biweekly fashion, which would include biweekly close, high and low prices from the past. Say past 20 biweekly HLC price to calculate the past 20 biweekly ATR.

I am actually OK with the data change abruptly on Monday or Friday. It is just weird to me, with the reshape function the biweekly data changes during the week (e.g. Thursday).

Meanwhile, I also tried to use weekly resample for close price. This also generate issues.

For example, if you run the below test from 01/01/2020 to 04/29/2020, in the log you will get the log on 2020-01-14:

2020-01-14 15:59 myfunc:24 INFO SPY weekly close = 2019-08-23 00:00:00+00:00 282.114
2019-08-30 00:00:00+00:00 289.692
2019-09-06 00:00:00+00:00 295.249
2019-09-13 00:00:00+00:00 298.200
2019-09-20 00:00:00+00:00 296.671
2019-09-27 00:00:00+00:00 293.915
2019-10-04 00:00:00+00:00 292.930
2019-10-11 00:00:00+00:00 294.811
2019-10-18 00:00:00+00:00 296.522
2019-10-25 00:00:00+00:00 300.164
2019-11-01 00:00:00+00:00 304.682
2019-11-08 00:00:00+00:00 307.448
2019-11-15 00:00:00+00:00 310.304
2019-11-22 00:00:00+00:00 309.478
2019-11-29 00:00:00+00:00 312.742
2019-12-06 00:00:00+00:00 313.280
2019-12-13 00:00:00+00:00 315.728
2019-12-20 00:00:00+00:00 321.120
2019-12-27 00:00:00+00:00 322.860
2020-01-03 00:00:00+00:00 322.440
2020-01-10 00:00:00+00:00 325.700
2020-01-17 00:00:00+00:00 327.350

However, the log you will get on 2020-03-25 will be:
2020-03-25 15:59 myfunc:24 INFO SPY weekly close = 2019-11-01 00:00:00+00:00 302.906
2019-11-08 00:00:00+00:00 305.657
2019-11-15 00:00:00+00:00 308.496
2019-11-22 00:00:00+00:00 307.675
2019-11-29 00:00:00+00:00 310.920
2019-12-06 00:00:00+00:00 311.454
2019-12-13 00:00:00+00:00 313.888
2019-12-20 00:00:00+00:00 319.248
2019-12-27 00:00:00+00:00 320.978
2020-01-03 00:00:00+00:00 320.561
2020-01-10 00:00:00+00:00 323.802
2020-01-17 00:00:00+00:00 330.075
2020-01-24 00:00:00+00:00 326.754
2020-01-31 00:00:00+00:00 319.875
2020-02-07 00:00:00+00:00 330.264
2020-02-14 00:00:00+00:00 335.632
2020-02-21 00:00:00+00:00 331.576
2020-02-28 00:00:00+00:00 292.157
2020-03-06 00:00:00+00:00 295.677
2020-03-13 00:00:00+00:00 269.351
2020-03-20 00:00:00+00:00 228.950
2020-03-27 00:00:00+00:00 246.220

Please note the week close data for the week of 2020-01-10 (marked as bold and italic). Somehow it changes during weeks even in the same test. This also is very confusing, which makes neither weekly nor biweekly resample function work for me.

Clone Algorithm
0
Loading...
Total Returns
--
Alpha
--
Beta
--
Sharpe
--
Sortino
--
Max Drawdown
--
Benchmark Returns
--
Volatility
--
Returns 1 Month 3 Month 6 Month 12 Month
Alpha 1 Month 3 Month 6 Month 12 Month
Beta 1 Month 3 Month 6 Month 12 Month
Sharpe 1 Month 3 Month 6 Month 12 Month
Sortino 1 Month 3 Month 6 Month 12 Month
Volatility 1 Month 3 Month 6 Month 12 Month
Max Drawdown 1 Month 3 Month 6 Month 12 Month
import quantopian.algorithm as algo
from quantopian.pipeline import Pipeline
from quantopian.pipeline.data.builtin import USEquityPricing
from quantopian.pipeline.filters import QTradableStocksUS
from datetime import date, datetime  
from collections import defaultdict, deque  
import pandas as pd
import numpy as np


def initialize(context):
    context.signal = symbol('SPY')
    schedule_function(myfunc, date_rules.every_day(), time_rules.market_close())

def myfunc(context, data):
    high = data.history(context.signal, 'high', 100, '1d')
    low = data.history(context.signal, 'low', 100, '1d')
    close = data.history(context.signal, 'close', 100, '1d')
    prices = data.history(context.signal, 'price', 5, '1d')

    
    prices_week_close = close.resample('1W-FRI', closed='right', label='right').last()

    log.info("SPY weekly close = " + str(prices_week_close))
    order_target_percent(context.signal, 0)
There was a runtime error.

All prices fetched via the data.history method are adjusted as of the current simulated day in the backtest. So, the adjusted price for 2020-01-10, fetched on 2020-03-25, will include the $1.4056 dividend (ex date 2020-01-10). The adjusted price for 2020-01-10, fetched on 2020-01-14 is not adjusted for this (future) dividend. Generally, one cannot save and compare price/volume data fetched on different days because of splits and dividends.

Comparisons will be correct if you do not compare to saved values.

@Dan Whitnable

Yes Yes, you are right. I did not notice the price adjustment. Thank you for mentioning that.

To follow up on the biweekly close price method. I guess the method you mentioned above is fetching the biweekly date and use those dates to get the biweekly close price, right? Is there any way to get a biweekly high/low price data?

Thank you for your time :-)

Probably the best method to get high/low data is use the resample method but, instead of last, use max or min. Also fetch the associated 'high' and 'low' data. That will get you the highest high and the lowest low for the 2 weeks.

One thing to remember is the latest period (ie the latest 2 weeks or portion of 2 weeks) will be resampled over a lesser period (less than 2 weeks). The value for that last period my also change every day. For example, if every day there is a new high, then that last 2W value will change every day.

Hi Dan

Thank you very much for the help. I'll try this method.

Thank you again.

This is my strategy to solve the problem. Hope it can help others who have the same goal. :-)

Clone Algorithm
3
Loading...
Total Returns
--
Alpha
--
Beta
--
Sharpe
--
Sortino
--
Max Drawdown
--
Benchmark Returns
--
Volatility
--
Returns 1 Month 3 Month 6 Month 12 Month
Alpha 1 Month 3 Month 6 Month 12 Month
Beta 1 Month 3 Month 6 Month 12 Month
Sharpe 1 Month 3 Month 6 Month 12 Month
Sortino 1 Month 3 Month 6 Month 12 Month
Volatility 1 Month 3 Month 6 Month 12 Month
Max Drawdown 1 Month 3 Month 6 Month 12 Month
"""
This is a function to get the high, low and close price of SPY, pooled from a two-week style. The date pooling strategy is to have 2020-04-27 as an anchor date. and calculate the week difference beteern this date and the anchor date. So unlike the strategy on tradingview, it is a aggregational pooling. On Tradingview strategy, it pools biweek data within a calender year. So what often happens is if one year ends with a Dec-30th or Dec-31th as a Monday, both this week and the next week count as a new bar on biweek data on Trading view.
"""
import datetime
from datetime import date,timedelta  
from collections import defaultdict, deque  
import pandas as pd
import numpy as np


def initialize(context):
    context.signal = symbol('SPY')
    context.anchor_date = datetime.date(2020, 4, 27)
    schedule_function(myfunc, date_rules.every_day(), time_rules.market_close())

def myfunc(context, data):
    high = data.history(context.signal, 'high', 50, '1d')
    low = data.history(context.signal, 'low', 50, '1d')
    close = data.history(context.signal, 'close', 50, '1d')
    today_date = close.index[-1].date()
    today_week_of_year = today_date.isocalendar()[1]
    monday1 = (context.anchor_date - timedelta(days=context.anchor_date.weekday()))
    
    biweekpool_close = []
    biweekpool_high = []
    biweekpool_low = []
    biweek_close = pd.Series()
    biweek_high = pd.Series()
    biweek_low = pd.Series()
    for i, value in list(close.iteritems())[::-1]:
        date_i = i.date()
        week_of_year_i = date_i.isocalendar()[1]
        monday2 = (date_i - timedelta(days=date_i.weekday()))
        weeks = (monday2 - monday1).days / 7
        if weeks % 2 == 0: #see if week number is even
            if week_of_year_i == today_week_of_year:
                pass# do nothing until it's the previous week
            elif week_of_year_i == (today_week_of_year-1):
                pass
            else:
                biweek_end_date = i
                biweekpool_close.insert(0,close[i])
                biweekpool_high.insert(0,high[i])
                biweekpool_low.insert(0,low[i])                
        else:
            if week_of_year_i == today_week_of_year:
                pass
            else:
                if len(biweekpool_close) < 9:
                    
                    biweekpool_close.insert(0,close[i])
                    biweekpool_high.insert(0,high[i])
                    biweekpool_low.insert(0,low[i])
                else:
                    biweek_temp_close = pd.Series({biweek_end_date : biweekpool_close[-1]})
                    biweek_temp_high = pd.Series({biweek_end_date : max(biweekpool_high)})
                    biweek_temp_low = pd.Series({biweek_end_date: min(biweekpool_low)})

                    biweek_close = pd.concat([biweek_temp_close, biweek_close])
                    biweek_high = pd.concat([biweek_temp_high, biweek_high])
                    biweek_low = pd.concat([biweek_temp_low, biweek_low])
                    
                    biweekpool_close = []
                    biweekpool_high = []
                    biweekpool_low = []
                    biweekpool_close.insert(0,close[i])
                    biweekpool_high.insert(0,high[i])
                    biweekpool_low.insert(0,low[i])
    #log.info("biweek_low = " + str(biweek_low)) 
There was a runtime error.