Bitcoin Cost Averaging ROI

from lxml import html
import requests
import numpy as np
import json, requests
import datetime
from datetime import timedelta
import time
import pandas as pd

sysdate = datetime.datetime.fromtimestamp(int(time.time())).strftime('%Y-%m-%d %H:%M:%S')
print(sysdate)

file_historical = open('../_data/datasets/bitcoin/historical_price.csv', 'w')

#all headers
header_historical = ['id', 'date', 'open', 'high', 'low', 'close', 'volume', 'market_cap']

# Write headers into files
file_historical.write(','.join(str(e) for e in header_historical) + '\n')
2020-02-09 17:48:55





46
print((datetime.datetime.fromtimestamp(int(time.time())) - timedelta(days=1)).strftime('%Y%m%d'))
20200208
id = 'bitcoin'
# Historical Data
historical_data_url = 'https://coinmarketcap.com/currencies/' + id + '/historical-data/?start=20100101&end=' + (datetime.datetime.fromtimestamp(int(time.time())) - timedelta(days=2)).strftime('%Y%m%d')
#historical_data_url = 'https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20200101&end=20200125'

page = requests.get(historical_data_url)
tree = html.fromstring(page.content)
historical_data = tree.xpath('//td/div/text()')
try:
    historical_data_table = np.reshape(np.array(historical_data), (-1, 7))
    for data_historical in historical_data_table:
        date = datetime.datetime.strptime(data_historical[0], "%b %d, %Y").date()
        row_history = [id, date.strftime('%Y-%m-%d'),
                       float(data_historical[1].replace('-', '').replace(',', '') or 0.0),
                       float(data_historical[2].replace('-', '').replace(',', '') or 0.0),
                       float(data_historical[3].replace('-', '').replace(',', '') or 0.0),
                       float(data_historical[4].replace('-', '').replace(',', '') or 0.0),
                       int(data_historical[5].replace('-', '').replace(',', '') or 0),
                       int(data_historical[6].replace('-', '').replace(',', '') or 0)]
        file_historical.write(','.join(str(e) for e in row_history) + '\n')
except Exception as e:
    print("Unexpected error", id, e )
    
file_historical.close()
df = pd.read_csv("../../_data/datasets/bitcoin/historical_price.csv")

Adding Day of the week to the dataframe

df['weekday'] =pd.to_datetime(df['date']).apply(lambda x: x.weekday())

df_invest_period = df[df['date'] >= '2018-09-01']
df_invest_period = df_invest_period.drop(['id','open','high','low','volume','market_cap'], 1)
df_invest_period['investment'] = df_invest_period.apply(lambda row: 100 if row.weekday == 0 else 0, axis=1)
df_invest_period['fee'] = df_invest_period.apply(lambda row: 4 if row.weekday == 0 else 0, axis=1)
df_invest_period['cc_fee'] = df_invest_period.apply(lambda row: 7 if row.weekday == 0 else 0, axis=1)
df_invest_period['gross_investment'] = df_invest_period.apply(lambda row: row.investment + row.cc_fee if row.weekday == 0 else 0, axis=1)
df_invest_period['net_investment'] = df_invest_period.apply(lambda row: row.investment - row.fee if row.weekday == 0 else 0, axis=1)
df_invest_period['bitcoin_balance'] = df_invest_period.apply(lambda row: row.net_investment/row.close if row.weekday == 0 else 0, axis=1)
df_invest_period
date close weekday investment fee cc_fee gross_investment net_investment bitcoin_balance
0 2020-02-08 9865.12 5 0 0 0 0 0 0.000000
1 2020-02-07 9795.94 4 0 0 0 0 0 0.000000
2 2020-02-06 9729.80 3 0 0 0 0 0 0.000000
3 2020-02-05 9613.42 2 0 0 0 0 0 0.000000
4 2020-02-04 9180.96 1 0 0 0 0 0 0.000000
5 2020-02-03 9293.52 0 100 4 7 107 96 0.010330
6 2020-02-02 9344.37 6 0 0 0 0 0 0.000000
7 2020-02-01 9392.88 5 0 0 0 0 0 0.000000
8 2020-01-31 9350.53 4 0 0 0 0 0 0.000000
9 2020-01-30 9508.99 3 0 0 0 0 0 0.000000
10 2020-01-29 9316.63 2 0 0 0 0 0 0.000000
11 2020-01-28 9358.59 1 0 0 0 0 0 0.000000
12 2020-01-27 8909.82 0 100 4 7 107 96 0.010775
13 2020-01-26 8596.83 6 0 0 0 0 0 0.000000
14 2020-01-25 8367.85 5 0 0 0 0 0 0.000000
15 2020-01-24 8445.43 4 0 0 0 0 0 0.000000
16 2020-01-23 8406.52 3 0 0 0 0 0 0.000000
17 2020-01-22 8680.88 2 0 0 0 0 0 0.000000
18 2020-01-21 8745.89 1 0 0 0 0 0 0.000000
19 2020-01-20 8657.64 0 100 4 7 107 96 0.011088
20 2020-01-19 8706.25 6 0 0 0 0 0 0.000000
21 2020-01-18 8942.81 5 0 0 0 0 0 0.000000
22 2020-01-17 8929.04 4 0 0 0 0 0 0.000000
23 2020-01-16 8723.79 3 0 0 0 0 0 0.000000
24 2020-01-15 8807.01 2 0 0 0 0 0 0.000000
25 2020-01-14 8827.76 1 0 0 0 0 0 0.000000
26 2020-01-13 8144.19 0 100 4 7 107 96 0.011788
27 2020-01-12 8192.49 6 0 0 0 0 0 0.000000
28 2020-01-11 8037.54 5 0 0 0 0 0 0.000000
29 2020-01-10 8166.55 4 0 0 0 0 0 0.000000
... ... ... ... ... ... ... ... ... ...
496 2018-09-30 6625.56 6 0 0 0 0 0 0.000000
497 2018-09-29 6601.96 5 0 0 0 0 0 0.000000
498 2018-09-28 6644.13 4 0 0 0 0 0 0.000000
499 2018-09-27 6676.75 3 0 0 0 0 0 0.000000
500 2018-09-26 6495.00 2 0 0 0 0 0 0.000000
501 2018-09-25 6446.47 1 0 0 0 0 0 0.000000
502 2018-09-24 6595.41 0 100 4 7 107 96 0.014556
503 2018-09-23 6710.63 6 0 0 0 0 0 0.000000
504 2018-09-22 6721.98 5 0 0 0 0 0 0.000000
505 2018-09-21 6734.95 4 0 0 0 0 0 0.000000
506 2018-09-20 6519.67 3 0 0 0 0 0 0.000000
507 2018-09-19 6398.54 2 0 0 0 0 0 0.000000
508 2018-09-18 6371.30 1 0 0 0 0 0 0.000000
509 2018-09-17 6281.20 0 100 4 7 107 96 0.015284
510 2018-09-16 6517.18 6 0 0 0 0 0 0.000000
511 2018-09-15 6543.20 5 0 0 0 0 0 0.000000
512 2018-09-14 6512.71 4 0 0 0 0 0 0.000000
513 2018-09-13 6517.31 3 0 0 0 0 0 0.000000
514 2018-09-12 6351.80 2 0 0 0 0 0 0.000000
515 2018-09-11 6321.20 1 0 0 0 0 0 0.000000
516 2018-09-10 6329.70 0 100 4 7 107 96 0.015167
517 2018-09-09 6300.86 6 0 0 0 0 0 0.000000
518 2018-09-08 6225.98 5 0 0 0 0 0 0.000000
519 2018-09-07 6467.07 4 0 0 0 0 0 0.000000
520 2018-09-06 6529.17 3 0 0 0 0 0 0.000000
521 2018-09-05 6792.83 2 0 0 0 0 0 0.000000
522 2018-09-04 7361.66 1 0 0 0 0 0 0.000000
523 2018-09-03 7260.06 0 100 4 7 107 96 0.013223
524 2018-09-02 7272.72 6 0 0 0 0 0 0.000000
525 2018-09-01 7193.25 5 0 0 0 0 0 0.000000

526 rows × 9 columns

#print(df_invest_period.date.max())
#last_date = df_invest_period.date.max()
#print(df_invest_period.loc[df_invest_period['date'] == last_date, 'close'])
last_price = df_invest_period.iloc[0]['close']

#print()
print(df_invest_period['gross_investment'].sum())
print(df_invest_period['gross_investment'].sum()/107)
print(df_invest_period['bitcoin_balance'].sum()*last_price)
print('ROI: ', df_invest_period['bitcoin_balance'].sum()*last_price/df_invest_period['gross_investment'].sum())


8025
75.0
11587.24697375027
ROI:  1.4438937038941146