Dynamic Dollar Cost Averaging into Bitcoin

Objective of this anlysis is to set the stage for the development of a quantitative appoach to dollar cost averaging into Bitcoin.

The methodology will be based on daily deviations from the 20-week “Simple Moving Average,” which is the average price of an asset over the previous 20 weeks. The basic idea is that prices that are significantly below the 20-week SMA indicate an oversold condition, and prices significantly above it indicate an overbought condition.

import datetime as dt
import pandas as pd
import matplotlib.pyplot as plt
import mplfinance as mpf
import numpy as np
import matplotlib as mpl

The data is taken from a tradingview.com export of Bitcoin pricing from the Bitstamp exchange. The data includes BTC pricing info (Open, High, Low, and Close) and volume (daily, Volume, and 20-day simple moving average, Volume MA) for over 3600 days since August 18, 2011.

The data also includes a 20-week week simple moving average, MA, and exponential moving average, EMA, as calculated by tradingview.com. Since these are calculated weekly and as an average of the past 20 weeks one value is available per week after 20 weeks have elapsed in the raw data.

Open Raw Data

d = pd.read_csv('dynamic-dollar-cost-averaging-into-bitcoin/BITSTAMP_BTCUSD, 1D.csv')
d.info()
d.head()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3605 entries, 0 to 3604
Data columns (total 9 columns):
 #   Column     Non-Null Count  Dtype  
---  ------     --------------  -----  
 0   time       3605 non-null   object 
 1   open       3605 non-null   float64
 2   high       3605 non-null   float64
 3   low        3605 non-null   float64
 4   close      3605 non-null   float64
 5   EMA        502 non-null    float64
 6   MA         502 non-null    float64
 7   Volume     3605 non-null   float64
 8   Volume MA  3586 non-null   float64
dtypes: float64(8), object(1)
memory usage: 253.6+ KB

time open high low close EMA MA Volume Volume MA
0 2011-08-18T00:00:00Z 10.90 10.90 10.90 10.90 NaN NaN 0.489908 NaN
1 2011-08-19T00:00:00Z 11.85 11.85 11.15 11.69 NaN NaN 1.926578 NaN
2 2011-08-20T00:00:00Z 11.70 11.70 11.70 11.70 NaN NaN 0.085470 NaN
3 2011-08-21T00:00:00Z 11.70 11.70 11.70 11.70 NaN NaN 0.085470 NaN
4 2011-08-22T00:00:00Z 11.70 11.70 11.70 11.70 NaN NaN 0.028632 NaN

Basic Cleansing

  • Convert “time” to “date”
  • Capture day of week dow
  • Identify any missing days by merging into a complete list of dates from the start to end date
  • Peel off Open, High, Low, Close data to use for candlesticks
  • Subset to just the minimally necessary columns
d['date'] = pd.to_datetime(d['time'])
complete_date_list = pd.DataFrame(
    pd.date_range(d['date'].min(), 
                  d['date'].max()),
    columns=['date'])
d = (pd.merge(complete_date_list, d,
              on=['date'],
              how='left')
     .reset_index(drop=True))
ohlc = (d[['date','open','high','low','close']]
        .set_index('date'))
ohlc.info()
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 3638 entries, 2011-08-18 00:00:00+00:00 to 2021-08-02 00:00:00+00:00
Data columns (total 4 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   open    3605 non-null   float64
 1   high    3605 non-null   float64
 2   low     3605 non-null   float64
 3   close   3605 non-null   float64
dtypes: float64(4)
memory usage: 142.1 KB
d['dow'] = pd.to_datetime(d['date']).dt.dayofweek
d = (d[['date','dow','open','high','low','close','MA','EMA']]
     .reset_index(drop=True))
d.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3638 entries, 0 to 3637
Data columns (total 8 columns):
 #   Column  Non-Null Count  Dtype              
---  ------  --------------  -----              
 0   date    3638 non-null   datetime64[ns, UTC]
 1   dow     3638 non-null   int64              
 2   open    3605 non-null   float64            
 3   high    3605 non-null   float64            
 4   low     3605 non-null   float64            
 5   close   3605 non-null   float64            
 6   MA      502 non-null    float64            
 7   EMA     502 non-null    float64            
dtypes: datetime64[ns, UTC](1), float64(6), int64(1)
memory usage: 227.5 KB

Exploration

  • A total of 33 days are missing pricing information. This is less than 1% of the total values in the dataset, and 30/33 were in 2011.
  • The MA and EMA values are available for Sundays (day 6).
d[d['close'].isna()]['date'].dt.year.value_counts()
2011    30
2015     3
Name: date, dtype: int64
d[~d['MA'].isna()]['dow'].value_counts()
6    501
0      1
Name: dow, dtype: int64

Calculate a 20-Week Moving Averages using Pandas

  • Use Sundays for moving averages, like TradingView.com
  • Moving averages calculated in Pandas match those calculated by TradingView.com most of the time. Proceed using the Pandas values.
  • Fill the moving averages forward (a given week’s moving average is based on the rolling average price as of the last Sunday).
ma = d[d['dow']==6].copy()
ma['sma_20'] = (ma['close']
                .rolling(window=20)
                .mean())
ma['ema_20'] = (ma['close']
                .ewm(span=20,
                     min_periods=20,
                     adjust=False)
                .mean())
ma.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 520 entries, 3 to 3636
Data columns (total 10 columns):
 #   Column  Non-Null Count  Dtype              
---  ------  --------------  -----              
 0   date    520 non-null    datetime64[ns, UTC]
 1   dow     520 non-null    int64              
 2   open    513 non-null    float64            
 3   high    513 non-null    float64            
 4   low     513 non-null    float64            
 5   close   513 non-null    float64            
 6   MA      501 non-null    float64            
 7   EMA     501 non-null    float64            
 8   sma_20  485 non-null    float64            
 9   ema_20  494 non-null    float64            
dtypes: datetime64[ns, UTC](1), float64(8), int64(1)
memory usage: 44.7 KB
(ma['MA'].round(1)==ma['sma_20'].round(1)).value_counts()
True     485
False     35
dtype: int64
(ma['EMA'].round(1)==ma['ema_20'].round(1)).value_counts()
True     463
False     57
dtype: int64
d = pd.merge(d.drop(columns=['MA','EMA']),
             ma.drop(columns=['MA','EMA']),
             how='left')
d['sma_20'] = d['sma_20'].fillna(method='ffill')
d['ema_20'] = d['ema_20'].fillna(method='ffill')
d.loc[d['ema_20'] <= d['sma_20'],'comp_20'] = d['ema_20']
d['comp_20'] = d['comp_20'].fillna(d['sma_20'])
d['date'] = d['date'].dt.date
d = d.drop(columns=['dow'])
d.info()
d.tail()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 3638 entries, 0 to 3637
Data columns (total 8 columns):
 #   Column   Non-Null Count  Dtype  
---  ------   --------------  -----  
 0   date     3638 non-null   object 
 1   open     3605 non-null   float64
 2   high     3605 non-null   float64
 3   low      3605 non-null   float64
 4   close    3605 non-null   float64
 5   sma_20   3390 non-null   float64
 6   ema_20   3453 non-null   float64
 7   comp_20  3390 non-null   float64
dtypes: float64(7), object(1)
memory usage: 384.8+ KB

date open high low close sma_20 ema_20 comp_20
3633 2021-07-29 40024.80 40662.80 39239.95 40037.51 45470.5640 39427.295350 39427.295350
3634 2021-07-30 40045.21 42335.50 38331.37 42234.89 45470.5640 39427.295350 39427.295350
3635 2021-07-31 42236.72 42411.32 41007.51 41490.13 45470.5640 39427.295350 39427.295350
3636 2021-08-01 41489.16 42614.85 39424.27 39839.00 44511.6945 39466.505317 39466.505317
3637 2021-08-02 39875.20 40459.31 38687.62 39335.62 44511.6945 39466.505317 39466.505317

Calculate Composite % Delta between Daily Price and Weekly Moving Averages

Takeaways from the plots and analysis below:

  • Overall volatility (as measured by deviations from the rolling weekly averages) is decreasing as bitcoin and cryptocurrencies gain market cap.
  • Deviations from the Exponential moving average are smaller than the Simple moving average. This is expected since the Exponential moving average is itself a more dynamic indicator, responding more quickly to changes in market price.
  • There is a significant difference year-to-year in the average delta to the moving average, reflecting the cylical nature of Bitcoin’s pricing.
d['sma_delta'] = ((d['close']-d['sma_20'])/d['sma_20']*100)
d['ema_delta'] = ((d['close']-d['ema_20'])/d['ema_20']*100)
d.tail()

date open high low close sma_20 ema_20 comp_20 sma_delta ema_delta
3633 2021-07-29 40024.80 40662.80 39239.95 40037.51 45470.5640 39427.295350 39427.295350 -11.948508 1.547696
3634 2021-07-30 40045.21 42335.50 38331.37 42234.89 45470.5640 39427.295350 39427.295350 -7.115975 7.120942
3635 2021-07-31 42236.72 42411.32 41007.51 41490.13 45470.5640 39427.295350 39427.295350 -8.753870 5.231996
3636 2021-08-01 41489.16 42614.85 39424.27 39839.00 44511.6945 39466.505317 39466.505317 -10.497678 0.943825
3637 2021-08-02 39875.20 40459.31 38687.62 39335.62 44511.6945 39466.505317 39466.505317 -11.628572 -0.331636
d['year'] = d['date'].astype(str).str[:4]
(d[['year','sma_delta']].groupby('year').mean()
 .rename(columns={'sma_delta':'Avg of Daily % Delta to SMA'}).round(1)
 .join(d[['year','ema_delta']].groupby('year').mean()
       .rename(columns={'ema_delta':'Avg of Daily % Delta to EMA'}).round(1))
 .join(d[['year','sma_delta']].groupby('year').std()
       .rename(columns={'sma_delta':'S.D. of Daily % Delta to SMA'}).round(1))
 .join(d[['year','ema_delta']].groupby('year').std()
       .rename(columns={'ema_delta':'S.D. of Daily % Delta to EMA'})).round(1).dropna())

Avg of Daily % Delta to SMA Avg of Daily % Delta to EMA S.D. of Daily % Delta to SMA S.D. of Daily % Delta to EMA
year
2012 28.7 15.7 25.6 22.8
2013 96.9 73.3 112.8 79.2
2014 -2.7 -2.3 33.9 23.3
2015 0.4 -2.9 22.3 20.6
2016 15.0 14.0 14.2 11.1
2017 58.5 48.9 37.8 31.3
2018 -11.0 -9.8 24.7 19.4
2019 12.7 8.1 37.5 27.3
2020 16.4 13.8 22.9 20.2
2021 35.8 28.9 54.3 40.6
plt.figure(figsize=(16,9))
plt.hist(d['sma_delta'],
         bins=np.arange(-60,560,20))
plt.grid()
for spine in plt.gca().spines.values():
    spine.set_visible(False)
plt.ylabel('Days')
plt.title('Distribution of Deltas to 20-Week Simple Moving Average')
rects = plt.gca().patches
for n, r in enumerate(rects):
    height = r.get_height()
    plt.gca().text(r.get_x() + r.get_width() / 2,
                   height + 22,
                   str(int(height)) + '\n' + str(((height/d.shape[0])*100.).round(1)) + '%',
                   ha='center',
                   va='center')

png

for sub in [d,
            d[d['date']<=dt.date(2014, 12, 12)],
            d[(d['date']>dt.date(2014, 12, 12))&
              (d['date']<=dt.date(2018, 4, 7))],
            d[d['date']>dt.date(2018, 4, 7)]]:
    plt.figure(figsize=(16,9))
    plt.hlines([0], xmin = sub['date'].min(), xmax = sub['date'].max(), color='k', linewidth=0.5)
    plt.plot(pd.to_datetime(sub['date']), sub['sma_delta'], label='Simple M.A.',      alpha=0.8, linewidth=1)
    plt.plot(pd.to_datetime(sub['date']), sub['ema_delta'], label='Exponential M.A.', alpha=0.8, linewidth=1)
    min_date, max_date, delta_date = (str(sub['date'].min()), 
                                      str(sub['date'].max()), 
                                      str(sub['date'].max() - sub['date'].min())[:9])
    plt.title(min_date + ' to ' + max_date + ', ' + delta_date)
    plt.legend()
    plt.grid(True)
    plt.ylabel('% Delta from Daily to Weekly Rolling Average')
    for spine in plt.gca().spines.values():
        spine.set_visible(False);

png

png

png

png

Use mplfinance to create and save figure to file.

mc = mpf.make_marketcolors(up='g',down='r')
s  = mpf.make_mpf_style(marketcolors=mc)
for year in ['2012','2013','2014','2015','2016',
             '2017','2018','2019','2020','2021']:
    sub1 = d[(d['date'].astype(str).str.contains(year))&
             (d['date'].astype(str).str[5:7].isin(['01', '02', '03', '04', '05', '06']))].copy()
    sub2 = d[(d['date'].astype(str).str.contains(year))&
             (d['date'].astype(str).str[5:7].isin(['07', '08', '09', '10', '11', '12']))].copy()
    
    for sub in [sub1, sub2]:
        sub['date'] = pd.to_datetime(sub['date'])

        fname = ('dynamic-cost-averaging-into-bitcoin/mpf_fig_' + 
                 str(d['date'].min())[:10] +
                 str(d['date'].max())[:10] +
                 '.pdf')
        title = ('BTC Pricing, 20-week SMA and EMA\n' 
                 + str(sub['date'].min())[:10] + ' - ' + str(sub['date'].max())[:10])

        ma_plots = [ 
            mpf.make_addplot(sub['sma_20'],
                             title=title),
            mpf.make_addplot(sub['ema_20'],
                             title=title)
        ]

        mpf.plot((sub[['date','open','high','low','close']]
                  .set_index('date')),
                 type='candle',
                 update_width_config=dict(candle_linewidth=0.4),
                 style=s,
                 figsize=(16,12),
                 addplot=ma_plots,
                 savefig=dict(fname=fname,
                              dpi=400,
                              pad_inches=0.25,
                              bbox_inches='tight'))
        mpf.plot((sub[['date','open','high','low','close']]
                  .set_index('date')),
                 type='candle',
                 update_width_config=dict(candle_linewidth=0.4),
                 style=s,
                 figsize=(16,12),
                 addplot=ma_plots)

png

png

png

png

png

png

png

png

png

png

png

png

png

png

png

png

png

png

png

png

Cut into Equally Populated Thresholds

d['qcut sma labels'] = pd.qcut(d['sma_delta'],
                               q=10,
                               labels=[1,2,3,4,5,6,7,8,9,10])
d['qcut sma thresholds'] = pd.qcut(d['sma_delta'],
                                   q=10)

d['lowerbound'] = (d['qcut sma thresholds']
                   .astype(str)
                   .str.replace('(','',regex=False)
                   .str.split(',',
                              expand=True)[0])
d['lowerbound'] = d['lowerbound'].astype(float)
(d[['qcut sma labels','qcut sma thresholds']]
 .sort_values('qcut sma labels')
 .drop_duplicates()
 .dropna()
 .reset_index(drop=True)
 .rename(columns={'qcut sma thresholds' : 'Range of % Daily Deviations from the 20-week SMA',
                  'qcut sma labels'     : '* 10 = Percentile'}))

* 10 = Percentile Range of % Daily Deviations from the 20-week SMA
0 1 (-53.512, -24.413]
1 2 (-24.413, -13.839]
2 3 (-13.839, -4.966]
3 4 (-4.966, 4.005]
4 5 (4.005, 11.651]
5 6 (11.651, 19.45]
6 7 (19.45, 32.078]
7 8 (32.078, 53.195]
8 9 (53.195, 82.132]
9 10 (82.132, 557.602]
sma_th = (d[['lowerbound']]
          .drop_duplicates()
          .dropna()
          .sort_values('lowerbound')
          .reset_index(drop=True))
sma_th = sma_th.T
sma_th.columns += 1
for col in sma_th:
    d[col] = sma_th[col].values[0]
sma_th

1 2 3 4 5 6 7 8 9 10
lowerbound -53.512 -24.413 -13.839 -4.966 4.005 11.651 19.45 32.078 53.195 82.132
for col in sma_th.columns:
    d[col] = d['sma_20'] + (d[col] * 0.01 * d['sma_20'])
d[sma_th.columns].tail(1).round(0).astype(int)

1 2 3 4 5 6 7 8 9 10
3637 20693 33645 38352 42301 46294 49698 53169 58790 68190 81070
sma_th.head()

1 2 3 4 5 6 7 8 9 10
lowerbound -53.512 -24.413 -13.839 -4.966 4.005 11.651 19.45 32.078 53.195 82.132
for year in ['2012','2013','2014','2015','2016',
             '2017','2018','2019','2020','2021']:
    sub = d[d['date'].astype(str).str.contains(year)].copy()
    
    plt.figure(figsize=(16,9))
    mpl.rcParams['axes.prop_cycle'] = mpl.cycler(color=['#309143', '#51b364', '#8ace7e', # Dark -> Light Green
                                                        '#ffda66', '#f0bd27', '#e39802', # Dark -> Light Yellow
                                                        '#ff684c', '#e03531', '#b60a1c', # Dark -> Light Red  
                                                        '#b60a1c'])
    
    for n, col in enumerate(sma_th.columns):
        if sma_th[col].values[0].round(1) >= 0:
            label = '{:2}: + {:4.1f}%: '.format(n+1, sma_th[col].values[0])
        else:
            label = '{:2}: - {:5.1f}%'.format(n+1, abs(sma_th[col].values[0]))
        plt.plot(pd.to_datetime(sub['date']),
                 sub[col],
                 alpha=0.2,
                 label=label,
                 linewidth=2)
    for col in sma_th.columns[1:]:
        plt.fill_between(pd.to_datetime(sub['date']),
                         sub[col],
                         sub[col-1],
                         alpha=0.2)
        
    plt.plot(pd.to_datetime(sub['date']),
             sub['sma_20'],
             color='darkblue',
             alpha=1,
             linewidth=1.5)
    plt.plot(pd.to_datetime(sub['date']),
             sub['ema_20'],
             color='darkred',
             alpha=1,
             linewidth=1.5)
    
    plt.plot(pd.to_datetime(sub['date']),
             sub['close'],
             color='k',
             linewidth=1.5);
    
    for spine in plt.gca().spines.values():
        spine.set_visible(False)
    plt.grid()
    plt.title(year + ' Bitcoin Price, SMA, EMA, and SMA Deviation Ranges')
    plt.legend(title='Delta from SMA')
    plt.ylabel('Price')
    plt.xlabel('Date');

png

png

png

png

png

png

png

png

png

png

d['year'] = d['date'].astype(str).str[:4]
totals = (pd.DataFrame(d[~d['qcut sma labels'].isna()]
                       ['year'].value_counts())
          .rename(columns={'year':'total'}))
category_counts = (pd.pivot_table(d[['year','close','qcut sma labels']],
                                  index='year',
                                  columns='qcut sma labels',
                                  aggfunc=len))
category_counts.columns = category_counts.columns.droplevel()
cats = (totals
        .join(category_counts)
        .reset_index()
        .rename(columns={'index':'year'})
        .sort_values('year')
        .reset_index(drop=True))
cats = cats.drop(columns=['total']).fillna(0)
labels = []
for n, col in enumerate(sma_th.columns):
    if sma_th[col].values[0].round(1) >= 0:
        labels.append('{:2}: + {:4.1f}%'.format(n+1, sma_th[col].values[0]))
    else:
        labels.append('{:2}: - {:5.1f}%'.format(n+1, abs(sma_th[col].values[0])))
plt.figure(figsize=(16,9))
bottoms = [0] * cats.shape[0]
plt.bar(cats['year'], cats[1], label=labels[0])
for col in cats.columns[2:]:
    bottoms += cats[col-1].values
    plt.bar(cats['year'], cats[col], bottom=bottoms, label=labels[col-1])
for spine in plt.gca().spines.values():
    spine.set_visible(False)
plt.title('Bitcoin SMA Deviation Range Distributions by Year')
plt.legend(title='Delta from SMA')
plt.ylabel('Count of Days')
plt.xlabel('Year');

png

th = (d[['qcut sma labels','qcut sma thresholds']]
      .sort_values('qcut sma labels')
      .drop_duplicates()
      .dropna()
      .reset_index(drop=True)
      .rename(columns={'qcut sma thresholds' : 'Range of % Daily Deviations',
                       'qcut sma labels'     : '* 10 = Percentile'}))
th['Range of % Daily Deviations'] = (th['Range of % Daily Deviations'].astype(str)
                                     .str.replace('(','',regex=False)
                                     .str.replace(' ','',regex=False)
                                     .str.replace(']','',regex=False))
th['lower bound'] = (th['Range of % Daily Deviations']
                     .str.split(',',expand=True)[0]).astype(float).round(1)
th['upper bound'] = (th['Range of % Daily Deviations']
                     .str.split(',',expand=True)[1]).astype(float).round(1)
th = (th
      .drop(columns=['Range of % Daily Deviations'])
      .set_index('* 10 = Percentile'))
th

lower bound upper bound
* 10 = Percentile
1 -53.5 -24.4
2 -24.4 -13.8
3 -13.8 -5.0
4 -5.0 4.0
5 4.0 11.7
6 11.7 19.4
7 19.4 32.1
8 32.1 53.2
9 53.2 82.1
10 82.1 557.6
  • 50% of the time, BTC has traded at least 11.7% above the 20-week simple moving average.

Calculate Average Annual 1-Year ROI

one_time_delta = pd.Timedelta(weeks = 1 * 52 )
d['current_day'] = pd.to_datetime(d['date'])
d['one_time_delta_later'] = d['current_day'] + one_time_delta
s = pd.merge(d[['one_time_delta_later','close']], (d[['current_day','close']]
                                                   .rename(columns={'close' : 'one_time_delta_later_close'})),
             left_on='one_time_delta_later',
             right_on='current_day',
             how='left')
s = s.drop(columns=['one_time_delta_later'])
s['1_year_return_percent'] = (((s['one_time_delta_later_close'] / s['close'])-1) * 100)
s['1_year_return_percent'].describe().round(1)
count    3238.0
mean      465.6
std      1008.9
min       -83.4
25%        25.5
50%       146.2
75%       471.7
max      9007.0
Name: 1_year_return_percent, dtype: float64
plt.figure(figsize=(16,9))
plt.hist(s['1_year_return_percent'],
         bins=np.arange(-100,2600,100))
plt.grid()
for spine in plt.gca().spines.values():
    spine.set_visible(False)
plt.ylabel('Days')
plt.title('Distribution of 1-Year Returns')
rects = plt.gca().patches
for n, r in enumerate(rects):
    height = r.get_height()
    plt.gca().text(r.get_x() + r.get_width() / 2,
                   height + 22,
                   str(int(height)) + '\n' + str(((height/d.shape[0])*100.).round(1)) + '%',
                   ha='center',
                   va='center')

png

d['date'] = d['date'].astype(str)
s['current_day'] = s['current_day'].astype(str)
d = pd.merge(d, s[['current_day','1_year_return_percent']],
             left_on='date',
             right_on='current_day',
             how='left',
             indicator=True)
d = d.drop(columns=['current_day_x','one_time_delta_later','current_day_y','_merge'])
d['3-tile'] = pd.qcut(d['sma_delta'],
                      q=3,
                      labels=list(np.arange(1,4,1)))
d['3-tile labels'] = pd.qcut(d['sma_delta'],
                             q=3)
d[['3-tile','3-tile labels']].drop_duplicates().dropna().sort_values('3-tile').reset_index(drop=True)

3-tile 3-tile labels
0 1 (-53.512, -2.844]
1 2 (-2.844, 27.489]
2 3 (27.489, 557.602]
d[['3-tile','1_year_return_percent']].groupby('3-tile').median()

1_year_return_percent
3-tile
1 56.024845
2 113.622033
3 312.150165
d[['3-tile','1_year_return_percent']].groupby('3-tile').mean()

1_year_return_percent
3-tile
1 177.901615
2 256.470660
3 975.509322

Surprisingly, the higher 3-tiles appear to have higher average and median 1-year returns. This may look quite a bit different over longer time spans as trading significantly below the 20-week SMA may indicate BTC is in a long-duration bear market, suppressing the 1-year returns.

Calculate Actual Percentiles

Buy, Sell, Hold

percentiles = (pd.DataFrame(pd.qcut(d['sma_delta'],
                                    q=100,
                                    labels=list(np.arange(1,101,1))))
               .rename(columns={'sma_delta':'percentile'}))
percentiles['thresholds'] = pd.qcut(d['sma_delta'],
                                    q=100)
percentiles = (percentiles[['percentile','thresholds']]
               .sort_values('percentile')
               .drop_duplicates()
               .dropna()
               .reset_index(drop=True))
percentiles['thresholds'] = (percentiles['thresholds'].astype(str)
                             .str.replace('(','',regex=False)
                             .str.replace(' ','',regex=False)
                             .str.replace(']','',regex=False))
percentiles['lower bound'] = (percentiles['thresholds']
                              .str.split(',',expand=True)[0]).astype(float).round(1)
percentiles['upper bound'] = (percentiles['thresholds']
                              .str.split(',',expand=True)[1]).astype(float).round(1)
percentiles = (percentiles
               .drop(columns=['thresholds'])
               .set_index('percentile'))
pd.set_option('display.max_rows', 100)
percentiles

lower bound upper bound
percentile
1 -53.5 -39.3
2 -39.3 -35.6
3 -35.6 -33.5
4 -33.5 -31.9
5 -31.9 -31.2
6 -31.2 -29.9
7 -29.9 -28.3
8 -28.3 -27.0
9 -27.0 -25.9
10 -25.9 -24.4
11 -24.4 -23.1
12 -23.1 -21.5
13 -21.5 -20.5
14 -20.5 -19.3
15 -19.3 -18.4
16 -18.4 -17.3
17 -17.3 -16.4
18 -16.4 -15.6
19 -15.6 -14.7
20 -14.7 -13.8
21 -13.8 -13.0
22 -13.0 -12.0
23 -12.0 -10.9
24 -10.9 -9.5
25 -9.5 -8.5
26 -8.5 -7.9
27 -7.9 -7.1
28 -7.1 -6.1
29 -6.1 -5.6
30 -5.6 -5.0
31 -5.0 -4.5
32 -4.5 -3.9
33 -3.9 -3.2
34 -3.2 -2.0
35 -2.0 -0.7
36 -0.7 0.4
37 0.4 1.6
38 1.6 2.8
39 2.8 3.4
40 3.4 4.0
41 4.0 4.7
42 4.7 5.5
43 5.5 6.3
44 6.3 7.0
45 7.0 7.6
46 7.6 8.4
47 8.4 9.2
48 9.2 10.2
49 10.2 10.9
50 10.9 11.7
51 11.7 12.3
52 12.3 13.2
53 13.2 13.9
54 13.9 14.5
55 14.5 15.2
56 15.2 15.9
57 15.9 16.6
58 16.6 17.5
59 17.5 18.4
60 18.4 19.4
61 19.4 20.3
62 20.3 21.2
63 21.2 22.9
64 22.9 24.1
65 24.1 25.1
66 25.1 26.6
67 26.6 27.8
68 27.8 29.4
69 29.4 30.8
70 30.8 32.1
71 32.1 33.8
72 33.8 35.7
73 35.7 37.6
74 37.6 39.3
75 39.3 41.9
76 41.9 44.0
77 44.0 45.9
78 45.9 48.5
79 48.5 50.6
80 50.6 53.2
81 53.2 55.1
82 55.1 57.2
83 57.2 59.5
84 59.5 61.7
85 61.7 66.2
86 66.2 70.8
87 70.8 73.6
88 73.6 76.7
89 76.7 79.1
90 79.1 82.1
91 82.1 85.9
92 85.9 90.0
93 90.0 96.1
94 96.1 103.0
95 103.0 110.5
96 110.5 120.6
97 120.6 149.9
98 149.9 185.7
99 185.7 254.6
100 254.6 557.6
percentiles.loc[[1,33,67,100],:]

lower bound upper bound
percentile
1 -53.5 -39.3
33 -3.9 -3.2
67 26.6 27.8
100 254.6 557.6

If the space of available actions is:

  1. Take no action (hodl) and store free cash flow as cash reserves,
  2. Buy BTC using any available free cash flow, and
  3. Buy BTC using any available free cash flow + a certain proportion of reserves.

Then, it seems like reasonable guidelines for each of these steps is to:

Action Guideline
Save 1x free cash flow as reserves price more than 27.8% above 20-week SMA
Buy BTC using 1x free cash flow price less than 27.8% above 20-week SMA
Buy BTC using 2x free cash flow price more than 3.2% below 20-week SMA