媳婦兒從事金融行業的，聽說我最近在學 Python，要我幫她寫一個回測用的代碼。
數據集有兩個 excel
第一個 excel 裡的數據有過去 1000 天中，每天開始的 open 點，最高點，最低點和 close 點，還有媳婦兒設參數值 f1,f2,f3。
第二個 excel 裡的數據有過去某些天，24 個小時或者部分小時的 open 點，最高點，最低點和 close 點。
結構如下圖：
data_day.xlsx
data_hour.xlsx
需求是當找到兩個表格日期相同的一天後，
按照第一個表格中前一天的四個點和三個參數求出當天需要用的 Bbreak 和 Sbreak。
然後比較當天中所有小時的數據，如果最高點大於 Bbreak，買點數量加一。如果最低點小於 Sbreak，賣點數量加一。
前面還要加一個選項，是否設置一個 base line。
如果設置 base line 了的話，如果當天最高點減去 Bbreak 大於 base line 的值，base
line 買點加一。如果 Sbreak 減去最低點大於 base line 的值，那麼 base line 賣點加一。
最後把一共四個買賣點用直方圖表示出來。
當然我媳婦兒說我這個其實是不嚴謹的。要不斷完善。
好了，代碼如下：
#!/usr/bin/env python2
# -*- coding: utf-8 -*-
"""
Created on Tue Jun 13 10:24:50 2017
@author: hans
"""
import matplotlib.pyplot as plt
import xlrd
currency = str(raw_input("請輸入貨幣類型: "))
if currency == "eur":
cur_index = 0
elif currency == "xau":
cur_index = 1
elif currency == "GBP":
cur_index = 2
elif currency == "jpy":
cur_index = 3
elif currency == "cad":
cur_index = 4
else:
print "哎呀，你輸入了一個錯誤的類型"
exit()
opt = str(raw_input("你想設置基準線嗎？ (yes/no)\n"))
if opt == 'yes':
bl = float(raw_input("請輸入基準線: "))
bp_day = []
blbp_day = []
sp_day = []
blsp_day = []
date = []
index = []
data_day = xlrd.open_workbook('./data/data_day.xlsx')
table_day = data_day.sheets()[cur_index]
#table_day = data_day.sheet_by_name(u'currency') # load sheet by name
nrows_day = table_day.nrows
data_hour = xlrd.open_workbook('./data/data_hour.xlsx')
table_hour = data_hour.sheets()[cur_index]
nrows_hour = table_hour.nrows
def getdate(table, row):
date = xlrd.xldate_as_tuple(table.cell(row, 2).value, 0)
short_date = date[0:3]
return short_date
def getbenchmark(row):
Close = table_day.cell(row, 6).value
Low = table_day.cell(row, 5).value
High = table_day.cell(row, 4).value
f1 = table_day.cell(row, 7).value
# f2 = table_day.cell(row, 8).value
f3 = table_day.cell(row, 9).value
Bsetup = Low - f1 * (High - Close)
Ssetup = High + f1 * (Close - Low)
# Benter = (1 + f2) / 2 * (High + Low) - f2 * High
# Senter = (1 + f2) / 2 * (High + Low) - f2 * Low
Bbreak = Ssetup + f3 * (Ssetup - Bsetup)
Sbreak = Bsetup - f3 * (Ssetup - Bsetup)
return Bbreak, Sbreak
def autolabel(hists, local):
for hist in hists:
num = hist.get_height()
if local == 1:
plt.text(hist.get_x() + hist.get_width()/10., num + 0.1, '%s' %int(num))
elif local == -1:
plt.text(hist.get_x() + hist.get_width()/10., -(num + 1.3), '%s' %int(num))
i = 0
for r_day in range(nrows_day-1, 1, -1):
bp = 0
blbp = 0
sp = 0
blsp = 0
short_date_day = getdate(table_day,r_day)
for r_hour in range(nrows_hour-1-i, 1, -1):
short_date_hour = getdate(table_hour, r_hour)
if (short_date_day[0] > short_date_hour[0]):
continue
elif (short_date_day[0] == short_date_hour[0]) and \
(short_date_day[1] > short_date_hour[1]):
continue
elif (short_date_day[0] == short_date_hour[0]) and \
(short_date_day[1] == short_date_hour[1]) and \
(short_date_day[2] > short_date_hour[2]):
continue
elif (short_date_day[0] == short_date_hour[0]) and \
(short_date_day[1] == short_date_hour[1]) and \
(short_date_day[2] == short_date_hour[2]):
i += 1
if r_day+1 >= nrows_day:
continue
else:
Bbreak, Sbreak = getbenchmark(r_day+1)
cur_high = table_hour.cell(r_hour, 4).value
if (cur_high > Bbreak):
bp += 1
if 'bl' in dir() and (cur_high - Bbreak > bl):
blbp += 1
cur_low = table_hour.cell(r_hour, 5).value
if (cur_low < Sbreak):
sp += 1
if 'bl' in dir() and (Sbreak - cur_low > bl):
blsp += 1
else:
break
if bp != 0 or sp != 0:
bp_day.append(bp)
sp_day.append(sp)
date.append('%s/%s' %(short_date_day[1], short_date_day[2]))
if 'bl' in dir():
blbp_day.append(-blbp)
blsp_day.append(-blsp)
for i in range(len(bp_day)):
index.append(i)
bp_hist = plt.bar(tuple(index), tuple(bp_day), color = ('red'), \
label = ('bp'), width = 0.3)
sp_hist = plt.bar(tuple(index), tuple(sp_day), color = ('green'), \
label = ('sp'), width = -0.3)
autolabel(bp_hist, 1)
autolabel(sp_hist, 1)
if 'bl' in dir():
blbp_hist = plt.bar(tuple(index), tuple(blbp_day), color = ('blue'), \
label = ('blbp'), width = 0.3)
blsp_hist = plt.bar(tuple(index), tuple(blsp_day), color = ('yellow'), \
label = ('blsp'), width = -0.3)
autolabel(blbp_hist, -1)
autolabel(blsp_hist, -1)
plt.xticks(tuple(index), tuple(date))
plt.title('套利點')
plt.legend()
plt.show()