def meanprofit():
df = pd.read_sql(
'select * from qy_coin_data having(day > "2017-01-01") order by day ',
engine_test())
coin_ids = pd.read_sql(
'select id from qy_coin_info order by marketcap desc limit 500',
engine_test())['id'].tolist()
for coin_id in coin_ids:
df_coin = df[df.coin_id == coin_id]
if len(df_coin) < 100:
continue
df_coin = df_coin.copy()
df_coin[
'profit'] = df_coin['close_p'] / df_coin['close_p'].shift(30) - 1
df_coin[
'future'] = df_coin['close_p'].shift(-30) / df_coin['close_p'] - 1
coef = df_coin[['profit', 'future']].corr().loc['profit', 'future']
print(coin_id, coef)
def benchmark():
sql = "select * from qy_coin_data where coin_id = 1 and day >= '2017-01-01'"
date_range = pd.date_range('2017-01-30', '2018-12-16', freq='D')
df = pd.read_sql(sql, engine_test())
df.index = pd.to_datetime(df['day'])
df = df.reindex(index=date_range, method='ffill')
df['new'] = df['close_p'] / df.iloc[0]['close_p']
price = df['new'].tolist()
#print(df['day'])
return price
def exchange_rate_table():
engine = engine_test()
sql = '''create table if not exists qy_exchange_rate(
id int primary key auto_increment not null,
symbol varchar(6) not null,
date date not null,
rate decimal(10,6) null,
)
'''
cur = engine.execute(sql)
return
def read_data():
sql = 'select * from qy_coin_data where coin_id = 7 and day >= "2016-01-01" and day < "2018-10-10" order by day'
df = pd.read_sql(sql, engine_test())
date_range = pd.date_range('2016-01-01', '2018-10-09', freq='D')
df.index = pd.to_datetime(df['day'])
df = df.reindex(index=date_range, method='ffill')
df.dropna(inplace=True)
df = df['close_p']
df = np.log(df)
return df
def usdt(df):
data = pd.read_sql(
'select day, close_p, volume from qy_coin_data where coin_id = 1343',
engine_test())
data.index = pd.to_datetime(data.day)
del data['day']
date_list = pd.date_range(data.index[0], data.index[-1], freq='D')
data = data.reindex(index=date_list, method='ffill')
data = data.copy()
data['profit'] = data.close_p / data.close_p.shift(1) - 1
data = data[data.index >= df.index[0]]
return data
def RSRS(N, M, coin_ids):
sql = '''select * from qy_coin_data where coin_id in {} order by day'''.format(
coin_ids)
df = pd.read_sql(sql, engine_test())
df['day'] = pd.to_datetime(df['day'])
regr = linear_model.LinearRegression()
df_new = pd.DataFrame(columns=['coin_id', 'gold', 'dead'])
for id in coin_ids:
df_coin = df[df['coin_id'] == id]
if len(df_coin) < M:
print("don't have enough data")
return False
date_list = pd.date_range(df_coin.iloc[0]['day'],
(dt.date.today() -
dt.timedelta(1)).strftime('%Y-%m-%d'))
df_coin = df_coin.copy()
df_coin.index = df_coin['day']
del df_coin['day']
df_coin.sort_index(ascending=True, inplace=True)
df_coin = df_coin.reindex(index=date_list, method='ffill')
df_coin.fillna(0, inplace=True)
df_coin.coin_id = id
coef_N = []
#R2 = []
for i in range(len(df_coin)):
if i < N - 1:
#regr.fit(np.array(df_coin.iloc[0:i+1]['low']).reshape(-1,1), np.array(df_coin.iloc[0:i+1]['high']).reshape(-1,1))
coef_N.append(0)
#R2.append(0)
else:
regr.fit(
np.array(df_coin.iloc[i - N + 1:i + 1]['low_p']).reshape(
-1, 1),
np.array(df_coin.iloc[i - N + 1:i + 1]['high_p']).reshape(
-1, 1))
coef_N.append(regr.coef_[0][0])
# R2.append(regr.score(np.array(df_coin.iloc[i-N+1:i + 1]['low']).reshape(-1, 1),
# np.array(df_coin.iloc[i-N+1:i + 1]['high']).reshape(-1, 1)))
df_coin['coef_N'] = coef_N
df_coin['avg'] = df_coin['coef_N'].rolling(M).mean()
df_coin['std'] = df_coin['coef_N'].rolling(M).std()
df_coin['RSRS'] = (df_coin['coef_N'] - df_coin['avg']) / df_coin['std']
#df_coin['RSRS_R2'] = df_coin['RSRS']
df_coin['gold'] = (df_coin['RSRS'] > 1)
df_coin['dead'] = (df_coin['RSRS'] < 0.1)
#df_coin.to_csv('d://RSRS_bitcoin.csv')
df_coin = df_coin[['coin_id', 'gold', 'dead']]
df_new = pd.concat([df_new, df_coin], join='outer')
return df_new
def data_clean():
coin_ids = (1, 11, 7, 9, 1343, 3, 1347, 4, 1341)
df = pd.read_sql(
'select a.coin_id,a.day,a.close_p,b.symbol from qy_coin_data a left join qy_coin_info b on a.coin_id = b.id where a.day >= "2017-01-01" and a.coin_id in {} order by a.coin_id,a.day'.format(coin_ids),
engine_test())
df_new = pd.DataFrame()
for coin_id in coin_ids:
df_coin = df[df['coin_id'] == coin_id]
date_list = pd.date_range(df_coin.iloc[0]['day'], '2018-12-05')
df_coin.index = pd.to_datetime(df_coin.day)
df_coin = df_coin.reindex(index=date_list, method='ffill')
df_new = pd.concat([df_new, df_coin], join='outer')
return df_new
def read_data(coin_id):
sql = 'select coin_id,day,close_p from qy_coin_data where coin_id = {} and close_p > 0 and day >= "2016-01-01" and day < "2018-10-10" order by day'.format(
coin_id)
df = pd.read_sql(sql, engine_test())
if len(df) < 1:
return df
date_range = pd.date_range(df.iloc[0]['day'], df.iloc[-1]['day'], freq='D')
df.index = pd.to_datetime(df['day'])
df = df.reindex(index=date_range, method='ffill')
df.dropna(inplace=True)
df = df['close_p']
df = np.log(df)
df = df.diff()
df.dropna(inplace=True)
#df['return'] = df['close_p'] / df['close_p'].shift(1) - 1
#df.dropna(inplace=True)
#df = df['return']
return df
def data_clean():
df = pd.read_sql(
'select coin_id,day,close_p,market_cap from qy_coin_data where day >= "2017-01-01" order by coin_id,day',
engine_test())
coin_ids = list(set(df.coin_id))
coin_ids = coin_ids[0:100]
df_new = pd.DataFrame()
for id in coin_ids:
df_coin = df[df.coin_id == id]
if len(df_coin) < 180:
continue
df_coin_copy = df_coin.loc[:, :]
df_coin_copy[
'return'] = df_coin_copy.close_p / df_coin_copy.close_p.shift(1)
df_coin_copy = df_coin_copy.dropna()
#df_coin_copy.fillna(0, inplace=True)
#df_coin_copy['std_down'] = 0
df_coin_copy['std'] = 0.0
df_coin_copy['return_1mon'] = 0.0
for i in range(1, len(df_coin_copy)):
if i <= 90:
df_coin_copy.iloc[i]['std'] = df_coin_copy.iloc[1:i + 1][
'return'].std()
else:
df_coin_copy.iloc[i]['std'] = df_coin_copy.iloc[i - 89:i + 1][
'return'].std()
if i >= len(df_coin_copy) - 30:
df_coin_copy.iloc[i]['return_1mon'] = df_coin_copy.iloc[
len(df_coin_copy) -
1]['close_p'] / df_coin_copy.iloc[i]['close_p'] - 1
else:
df_coin_copy.iloc[i]['return_1mon'] = df_coin_copy.iloc[
i + 30]['close_p'] / df_coin_copy.iloc[i]['close_p'] - 1
df_new = pd.concat([df_new, df_coin_copy], join='outer')
#df_new = df_new.sort_values(by='day', ascending = True)
df_final = df_new.loc[:, ['std', 'return_1mon']]
plt.scatter(df_final['std'], df_final['return_1mon'])
plt.show()
def read_data():
coin_ids = (1, 3, 4, 7, 9, 11, 12, 1341, 1347, 1360)
df = pd.read_sql(
'select a.coin_id,a.day,a.close_p,a.market_cap, b.symbol from qy_coin_data a left join qy_coin_info b on a.coin_id = b.id where a.day >= "2017-01-01" and a.coin_id in {} order by a.coin_id,a.day'
.format(coin_ids), engine_test())
coin_ids = df.drop_duplicates('coin_id').coin_id.tolist()
coin_ids = np.array(coin_ids).astype(str)
date_range = pd.date_range('2017-01-01', '2018-12-16', freq='D')
df_1 = pd.DataFrame(columns=coin_ids, index=date_range)
for id in coin_ids:
if id == 1: continue
df_id = df[df.coin_id == int(id)]
df_id.index = pd.to_datetime(df_id.day)
df_id = df_id.reindex(index=date_range, method='ffill')
# if df_id['close_p'].isna().any():
# del df_1[id]
# continue
df_id['coin_id'] = int(id)
df_1[id] = df_id.close_p
return df_1
def data_clean():
coin_ids = (1, 3, 4, 7, 9, 11, 12, 1341, 1347, 1360, 2915)
df = pd.read_sql(
'select a.coin_id,a.day,a.close_p,a.market_cap, b.symbol from qy_coin_data a left join qy_coin_info b on a.coin_id = b.id where a.day >= "2017-01-01" and a.coin_id in {} order by a.coin_id,a.day'
.format(coin_ids), engine_test())
df_new = pd.DataFrame()
df_btc = df[df['coin_id'] == 1]
date_list = pd.date_range(df_btc.iloc[0]['day'], '2018-12-09')
df_btc.index = pd.to_datetime(df_btc.day)
df_btc = df_btc.reindex(index=date_list, method='ffill')
for coin_id in coin_ids:
if coin_id == 1: continue
df_coin = df[df['coin_id'] == coin_id]
date_list = pd.date_range(df_coin.iloc[0]['day'], '2018-12-09')
df_coin.index = pd.to_datetime(df_coin.day)
df_coin = df_coin.reindex(index=date_list, method='ffill')
btc_price = df_btc[date_list[0]:date_list[-1]]['close_p']
df_coin = df_coin.copy()
df_coin['price_btc'] = df_coin['close_p'] / btc_price
df_new = pd.concat([df_new, df_coin], join='outer')
return df_new
vr = round(
(df_up['volume'].sum() + 0.5 * df_tie['volume'].sum()) /
(df_down['volume'].sum() + 0.5 * df_tie['volume'].sum()) * 100, 2)
df.iloc[i, 5] = vr
return df
if __name__ == '__main__':
'''
测试结果：取10-12之间的数较为合适，选择12
'''
coin_id = 1341
sql = "select day,coin_id,close_p,volume from qy_coin_data where coin_id = {} and close_p > 0 order by day".\
format(coin_id)
df = pd.read_sql(sql, engine_test())
date_list = list(pd.date_range(df.iloc[0]['day'], df.iloc[-1]['day']))
df = df.copy()
df.index = pd.to_datetime(df['day'])
df = df.reindex(index=date_list, method='ffill')
store = [0, 0]
for t in range(7, 21):
df = VR(t, df)
cash = 1000000
cost = 0.002
acount = cash
stock = 0
wealth = cash
from pprint import pprint
import numba
from database import engine_test
from pymongo import MongoClient
# mongo设置
#线上
client = MongoClient('127.0.0.1', 27017)
#测试
#client = MongoClient('10.100.0.113',27017)
#client = MongoClient('127.0.0.1', 27017)
#mongodbUri = 'mongo 172.17.226.30:27017/fof_api_currency'
#client = MongoClient(mongodbUri)
db = client['fof_api_currency']
engine = engine_test()
def component_all():
# 计算每期成分股
startdate = '2017-01-01'
statistic_date = []
while dt.datetime.strptime(startdate, '%Y-%m-%d') <= dt.datetime.today():
statistic_date.append(startdate)
startdate = (dt.datetime.strptime(startdate, '%Y-%m-%d') +
relativedelta(months=1)).strftime('%Y-%m-%d')
component_dict = {}
for standard_date in statistic_date:
component = list(coin_component(standard_date))
if len(component) < 10:
#seasonal.plot(color='blue',label='seasonal')
#residual.plot(color='yellow',label='residual')
plt.show()
def params_select(df):
resid = sm.tsa.arma_order_select_ic(df, max_ar=4, max_ma=4, ic='aic')
print('AIC:{}'.format(resid.aic_min_order))
#print('BIC:{}'.format(resid.bic_min_order))
#print('HQIC:{}'.format(resid.hqic_min_order))
if __name__ == '__main__':
coin = pd.read_sql(
'select id from qy_coin_info where from_unixtime(updatetimestamp) > date_sub(now(),interval 7 day)',
engine_test())
coin_ids = coin['id'].tolist()
data_unstable = []
data_random = []
data_target = []
coin_ids = [7]
for coin_id in coin_ids:
df = read_data(coin_id)
if len(df) < 500:
continue
adf = testStationarity(df)
rand_test = acorr_ljungbox(df)[1][0]
if adf['p-value'] > 0.05:
data_unstable.append(coin_id)
else:
def MACD(n1, n2, n3, v):
#sql = "select coin_id from qy_coin_info_extend where publish < '2015-09-01' and order by coin_id"
sql = '''SELECT a.id FROM qy_coin_info a
inner JOIN
(SELECT coin_id,AVG(market_cap) AS cap FROM qy_coin_data WHERE DAY > DATE_SUB(CURDATE(), INTERVAL 3 MONTH) GROUP BY coin_id ORDER BY cap DESC LIMIT 50) b ON a.id = b.coin_id
WHERE a.cate = 1 ORDER BY a.id '''
basic = pd.read_sql(sql, engine)['id'].tolist()
sql = "select day,coin_id,close_p,volume from qy_coin_data where close_p > 0 and day between '2018-01-01' and '2018-10-31' order by coin_id,day"
df = pd.read_sql(sql, engine)
a1 = 2. / (n1 + 1)
a2 = 2. / (n2 + 1)
a3 = 2. / (n3 + 1)
date_list = list(
pd.date_range(dt.datetime.strptime('2018-01-01', '%Y-%m-%d'),
dt.datetime.strptime('2018-10-31', '%Y-%m-%d')))
df_new = pd.DataFrame()
coin_pool = {}
for coin in basic:
df_coin = df[df['coin_id'] == coin]
df_coin = df_coin.copy()
if len(df_coin) < len(date_list) - 10:
continue
coin_pool.update({coin: [0, 0, 0]})
df_coin.index = pd.to_datetime(df_coin['day'])
df_coin = df_coin.reindex(index=date_list, method='ffill')
df_coin['coin_id'] = coin
EMA12 = []
EMA26 = []
DEA = []
#MACD信号
for i in range(len(df_coin)):
if i == 0:
EMA12.append(df_coin.iloc[0]['close_p'])
EMA26.append(df_coin.iloc[0]['close_p'])
DIF = EMA12[-1] - EMA26[-1]
DEA.append(DIF)
else:
EMA12.append(a1 * df_coin.iloc[i]['close_p'] +
(1 - a1) * EMA12[-1])
EMA26.append(a2 * df_coin.iloc[i]['close_p'] +
(1 - a2) * EMA26[-1])
DIF = EMA12[-1] - EMA26[-1]
DEA.append(a3 * DIF + (1 - a3) * DEA[-1])
df_coin['EMA12'] = EMA12
df_coin['EMA26'] = EMA26
df_coin['DIF'] = df_coin['EMA12'] - df_coin['EMA26']
df_coin['DEA'] = DEA
df_coin['gold'] = (df_coin['DEA'].shift(1) >= df_coin['DIF'].shift(1)
) & (df_coin['DEA'] < df_coin['DIF'])
df_coin['dead'] = (df_coin['DEA'].shift(1) < df_coin['DIF'].shift(1)
) & (df_coin['DEA'] >= df_coin['DIF'])
#交易量与近5天交易量比较
#df_coin['vol5'] = (df_coin['volume']).rolling(5).mean()
df_coin['vol'] = df_coin['volume'] / df_coin['volume'].shift(1)
# df_coin['change'] = df_coin['close_p'] / df_coin['close_p'].shift(1) - 1
# df_coin['rise'] = pd.DataFrame({'rise':df_coin['change'],'zeros':0}).max(1)
# df_coin['rsi'] = df_coin['rise'].rolling(6).mean() / abs(df_coin['change'].rolling(6).mean()) * 100
# df_coin['rsi_gold'] = (df_coin['rsi'].shift(1) <= 20) & (df_coin['rsi'] > 20)
# df_coin['rsi_dead'] = (df_coin['rsi'].shift(1) >= 80) & (df_coin['rsi'] < 80)
df_new = pd.concat([df_new, df_coin], join='outer')
df_new['day'] = df_new.index
df_new.index = df_new['coin_id']
del df_new['coin_id']
#初始化资产
origin = 1000000
cash = origin
cost = 0.002
price = [1]
tag = 0
index = 0
for date in date_list[1:]:
df_day = df_new[pd.to_datetime(df_new['day']) == date]
df_day_before = df_new[pd.to_datetime((df_new['day'])) == date -
dt.timedelta(1)]
backtrace = 1 - price[-1] / max(price[index:])
#最大回撤超过10%，卖出亏损的持仓币种
if backtrace > 0.1:
for coin_id in coin_pool:
if coin_pool[coin_id][0] > 0 and coin_pool[coin_id][
0] * df_day_before.loc[coin_id]['close_p'] < coin_pool[
coin_id][1] * (1 - 0.1):
cash += coin_pool[coin_id][0] * df_day_before.loc[coin_id][
'close_p'] * (1 - cost)
coin_pool[coin_id][2] += coin_pool[coin_id][
0] * df_day_before.loc[coin_id]['close_p'] * (
1 - cost) - coin_pool[coin_id][1]
coin_pool[coin_id][0] = 0
coin_pool[coin_id][1] = 0
index = tag
marketcap = 0
#print(date)
for coin_id in coin_pool.keys():
if coin_pool[coin_id][0] * df_day_before.loc[coin_id][
'close_p'] < (1 - 0.1) * coin_pool[coin_id][1]:
cash += coin_pool[coin_id][0] * df_day_before.loc[coin_id][
'close_p'] * (1 - cost)
coin_pool[coin_id][2] += coin_pool[coin_id][0] * df_day_before.loc[coin_id]['close_p'] * (1 - cost) - \
coin_pool[coin_id][1]
coin_pool[coin_id][0] = 0
coin_pool[coin_id][1] = 0
if df_day.loc[coin_id]['gold'] and cash >= 10000 and df_day.loc[
coin_id]['close_p'] > 0:
coin_pool[coin_id][1] = coin_pool[coin_id][1] + max(
cash / 10, 10000)
coin_pool[coin_id][0] = coin_pool[coin_id][0] + max(
cash / 10, 10000) / df_day.loc[coin_id]['close_p']
#print(coin_id,df_day.loc[coin_id]['close_p'],coin_pool[coin_id])
cash = cash - max(cash / 10, 10000)
elif df_day.loc[coin_id]['dead'] and coin_pool[coin_id][0] > 0 and df_day.loc[coin_id]['close_p'] > 0 \
and df_day.loc[coin_id]['vol'] > v:
cash = cash + coin_pool[coin_id][0] * df_day.loc[coin_id][
'close_p'] * (1 - cost)
coin_pool[coin_id][2] += coin_pool[coin_id][0] * df_day_before.loc[coin_id]['close_p'] * (1 - cost) - \
coin_pool[coin_id][1]
coin_pool[coin_id][0] = 0
coin_pool[coin_id][1] = 0
#print(coin_id, df_day.loc[coin_id]['close_p'],coin_pool[coin_id])
marketcap += coin_pool[coin_id][0] * df_day.loc[coin_id]['close_p']
#if math.isnan(marketcap):print(coin_id,df_day.loc[coin_id]['close_p'],coin_pool[coin_id])
total = cash + marketcap
price.append(total / 1000000.)
tag += 1
bitcoin = pd.read_sql(
"select * from qy_coin_data where day between '2018-01-01' and '2018-10-31' and coin_id = 1 order by day",
engine_test())
bitcoin = (bitcoin['close_p'] / bitcoin.iloc[0]['close_p']).tolist()
print(price)
print(pd.DataFrame(coin_pool))
print(coin_pool)
plt.plot(price)
plt.plot(bitcoin, color='red')
plt.show()
return price[-1]
def trade(df_new,
cash=1000000,
cost=0.002,
date_range=['2018-01-01', '2018-11-30']):
price = [1]
date_list = list(
pd.date_range(dt.datetime.strptime(date_range[0], '%Y-%m-%d'),
dt.datetime.strptime(date_range[-1], '%Y-%m-%d')))
coin_pool = {}
for id in list(set(df_new['coin_id'])):
coin_pool.update({id: [0, 0, 0]})
tag = 0
index = 0
for date in date_list[1:]:
df_day = df_new[pd.to_datetime(df_new['day']) == date]
df_day_before = df_new[pd.to_datetime((df_new['day'])) == date -
dt.timedelta(1)]
backtrace = 1 - price[-1] / max(price[index:])
# 最大回撤超过10%，卖出亏损的持仓币种
if backtrace > 0.1:
for coin_id in coin_pool:
if coin_pool[coin_id][0] > 0 and coin_pool[coin_id][0] * df_day_before.loc[coin_id]['close_p'] < \
coin_pool[coin_id][1] * (1 - 0.1):
cash += coin_pool[coin_id][0] * df_day_before.loc[coin_id][
'close_p'] * (1 - cost)
coin_pool[coin_id][2] += coin_pool[coin_id][
0] * df_day_before.loc[coin_id]['close_p'] * (
1 - cost) - coin_pool[coin_id][1]
coin_pool[coin_id][0] = 0
coin_pool[coin_id][1] = 0
index = tag
marketcap = 0
# print(date)
#根据信号及止损线进行调仓
for coin_id in coin_pool:
# if coin_pool[coin_id][0] * df_day_before.loc[coin_id]['close_p'] < (1 - 0.1) * coin_pool[coin_id][1]:
# cash += coin_pool[coin_id][0] * df_day_before.loc[coin_id]['close_p'] * (1 - cost)
# coin_pool[coin_id][2] += coin_pool[coin_id][0] * df_day_before.loc[coin_id]['close_p'] * (1 - cost) - \
# coin_pool[coin_id][1]
# coin_pool[coin_id][0] = 0
# coin_pool[coin_id][1] = 0
if df_day.loc[coin_id]['gold'] and cash >= 10000:
coin_pool[coin_id][1] = coin_pool[coin_id][1] + max(
cash / 10, 10000)
coin_pool[coin_id][0] = coin_pool[coin_id][0] + max(
cash / 10, 10000) / df_day.loc[coin_id]['close_p']
cash = cash - max(cash / 10, 10000)
elif df_day.loc[coin_id]['dead'] and coin_pool[coin_id][0] > 0:
cash = cash + coin_pool[coin_id][0] * df_day.loc[coin_id][
'close_p'] * (1 - cost)
coin_pool[coin_id][2] += coin_pool[coin_id][0] * df_day_before.loc[coin_id]['close_p'] * (1 - cost) - \
coin_pool[coin_id][1]
coin_pool[coin_id][0] = 0
coin_pool[coin_id][1] = 0
# print(coin_id, df_day.loc[coin_id]['close_p'],coin_pool[coin_id])
marketcap += coin_pool[coin_id][0] * df_day.loc[coin_id]['close_p']
# if math.isnan(marketcap):print(coin_id,df_day.loc[coin_id]['close_p'],coin_pool[coin_id])
total = cash + marketcap
price.append(total / 1000000.)
tag += 1
bitcoin = pd.read_sql(
"select * from qy_coin_data where day between '{}' and '{}' and coin_id = 1 order by day"
.format(date_range[0], date_range[1]), engine_test())
bitcoin = (bitcoin['close_p'] / bitcoin.iloc[0]['close_p']).tolist()
#print(price)
#print(pd.DataFrame(coin_pool))
#print(coin_pool)
plt.plot(price)
plt.plot(bitcoin, color='red')
plt.show()
return price[-1]
def MACD(n1, n2, n3, v, coin_id):
#sql = "select coin_id from qy_coin_info_extend where publish < '2015-09-01' and order by coin_id"
sql = "select day,coin_id,close_p,volume from qy_coin_data where coin_id = {} and close_p > 0 and day >= '2018-01-01' order by day".\
format(coin_id)
df_coin = pd.read_sql(sql, engine_test())
a1 = 2. / (n1 + 1)
a2 = 2. / (n2 + 1)
a3 = 2. / (n3 + 1)
date_list = list(
pd.date_range(df_coin.iloc[0]['day'], df_coin.iloc[-1]['day']))
df_new = pd.DataFrame()
coin_pool = {}
df_coin = df_coin.copy()
df_coin.index = pd.to_datetime(df_coin['day'])
df_coin = df_coin.reindex(index=date_list, method='ffill')
EMA12 = []
EMA26 = []
DEA = []
#MACD信号
for i in range(len(df_coin)):
if i == 0:
EMA12.append(df_coin.iloc[0]['close_p'])
EMA26.append(df_coin.iloc[0]['close_p'])
DIF = EMA12[-1] - EMA26[-1]
DEA.append(DIF)
else:
EMA12.append(a1 * df_coin.iloc[i]['close_p'] +
(1 - a1) * EMA12[-1])
EMA26.append(a2 * df_coin.iloc[i]['close_p'] +
(1 - a2) * EMA26[-1])
DIF = EMA12[-1] - EMA26[-1]
DEA.append(a3 * DIF + (1 - a3) * DEA[-1])
df_coin['EMA12'] = EMA12
df_coin['EMA26'] = EMA26
df_coin['DIF'] = df_coin['EMA12'] - df_coin['EMA26']
df_coin['DEA'] = DEA
df_coin['gold'] = (df_coin['DEA'].shift(1) >= df_coin['DIF'].shift(1)) & (
df_coin['DEA'] < df_coin['DIF'])
df_coin['dead'] = (df_coin['DEA'].shift(1) < df_coin['DIF'].shift(1)) & (
df_coin['DEA'] >= df_coin['DIF'])
#交易量与近5天交易量比较
#df_coin['vol5'] = (df_coin['volume']).rolling(1).mean()
df_coin['vol'] = (df_coin['volume']) / df_coin['volume'].shift(1)
# df_coin['change'] = df_coin['close_p'] / df_coin['close_p'].shift(1) - 1
# df_coin['rise'] = pd.DataFrame({'rise':df_coin['change'],'zeros':0}).max(1)
# df_coin['rsi'] = df_coin['rise'].rolling(6).mean() / abs(df_coin['change'].rolling(6).mean()) * 100
# df_coin['rsi_gold'] = (df_coin['rsi'].shift(1) <= 20) & (df_coin['rsi'] > 20)
# df_coin['rsi_dead'] = (df_coin['rsi'].shift(1) >= 80) & (df_coin['rsi'] < 80)
# df_new = pd.concat([df_new,df_coin],join='outer')
#
# df_new['day'] = df_new.index
# df_new.index = df_new['coin_id']
# del df_new['coin_id']
#初始化资产
origin = 1000000
cash = origin
cost = 0.002
price = [1]
tag = 0
index = 0
coin_pool.update({coin_id: [0, 0, 0]})
for date in date_list[1:]:
df_day = df_coin.loc[date]
df_day_before = df_coin.loc[date - dt.timedelta(1)]
backtrace = 1 - price[-1] / max(price[index:])
#最大回撤超过10%，卖出平仓
# if backtrace > 0.2:
# if coin_pool[coin_id][0] > 0:
# cash += coin_pool[coin_id][0] * df_day_before['close_p'] * (1 - cost)
# coin_pool[coin_id][2] += coin_pool[coin_id][0] * df_day_before['close_p'] * (1 - cost) - coin_pool[coin_id][1]
# coin_pool[coin_id][0] = 0
# coin_pool[coin_id][1] = 0
#
# index = tag
#止损
# if coin_pool[coin_id][0] * df_day_before['close_p'] < (1- 0.1) * coin_pool[coin_id][1]:
# cash += coin_pool[coin_id][0] * df_day_before['close_p'] * (1 - cost)
# coin_pool[coin_id][2] += coin_pool[coin_id][0] * df_day_before['close_p'] * (1 - cost) - \
# coin_pool[coin_id][1]
# coin_pool[coin_id][0] = 0
# coin_pool[coin_id][1] = 0
if df_day['gold'] and cash > 0 and df_day['close_p'] > 0:
coin_pool[coin_id][1] = coin_pool[coin_id][1] + cash
coin_pool[coin_id][
0] = coin_pool[coin_id][0] + cash / df_day['close_p']
cash = 0
elif df_day['dead'] and coin_pool[coin_id][0] > 0 and df_day[
'close_p'] > 0 and df_day['vol'] > v:
cash = cash + coin_pool[coin_id][0] * df_day['close_p'] * (1 -
cost)
coin_pool[coin_id][2] += coin_pool[coin_id][0] * df_day_before['close_p'] * (1 - cost) - \
coin_pool[coin_id][1]
coin_pool[coin_id][0] = 0
coin_pool[coin_id][1] = 0
marketcap = coin_pool[coin_id][0] * df_day['close_p']
#if math.isnan(marketcap):print(coin_id,df_day.loc[coin_id]['close_p'],coin_pool[coin_id])
total = cash + marketcap
price.append(total / 1000000.)
#tag += 1
price_old = df_coin['close_p'] / df_coin.iloc[0]['close_p']
#print(price)
#收益曲线
plt.plot(price)
plt.plot(price_old.tolist(), color='red')
plt.show()
#print(df_coin[['gold','dead']])
return price[-1]
def twitter_index_value():
df = pd.read_sql('select a.*,b.name,b.day7 from qy_twitter a left join qy_coin_info b on a.coin_id = b.id order by b.marketcap limit 50',engine_test())
followers = df['followers']
tweets = df['tweets']
reply = df['reply']
forward = df['forward']
likes = df['likes']
profit = df['day7']
df['r_f'] = df['tweets'] / df['likes']
print(df.corr()['day7'])
