漫步@雲端

經過整整三天的努力終於可以利用Python程式讓台指期2016的原始資料以五分鐘K線圖的方式顯現了!

Python程式的撰寫過程真的有夠折騰人的,上網研究了很多範例及資料,網路上的範例都是在呈現日K線圖,只有我是要用來呈現五分鐘K線圖,一開始就吃了很多苦頭,看別人的程式範例也不複雜或難懂,可是我的就是一直跑不出K線圖來,後來才弄懂因為我的五分鐘K線圖一天也不過才60根K線而已,所以K線的寬度跟別人的不一樣,其實一開始程式寫好後就可以顯示了,只是我的K線很細,細到看不出來而已,想到問題可能出在K線顯示的寬度,剛好程式中有一個width的參數,弔詭的是,我想說我應該把寬度設的比別人的大,因為我的K線比別人少,事實剛好相反,別人設定width=0.5可以顯示,我要設定為width=0.001才能顯示XD.

現在程式終於可以派上用場了! 我在回測2016的資料時不會再像瞎子一樣,連怎麼死的都不知道,不過對這個程式還是不滿意,例如:沒辦法在K線圖上顯示MA,這個問題跟顯示K線的問題一樣,都是因為資料太少會有顯示的問題,目前還無解;另外從圖中可以發現,K線跟成交量的位置沒有完全Match,目前也是無解,但還可以接受 .

完整的程式碼如下:

import pandas as pd
import xlrd
import mpl_finance as mpf
from mpl_finance import candlestick_ochl
import matplotlib.pyplot as plt
from matplotlib import dates as mdates

# 從 Excel 讀入 ticks row data
fileName = input('請輸入台指期原始資料 (D:\HistoryData\MXF\) Excel 檔案名稱 \
                  (不必包含 .xlsx) : ')
title = fileName[-8:]
date_title = title[:4] + '-' + title[4:6] + '-' + title[6:]
book = xlrd.open_workbook('D:\\HistoryData\\MXF\\' + fileName + '.xlsx')
sheet = book.sheet_by_index(0)

# 先開好一個空的 DataFrame
ticks = pd.DataFrame(columns=['date', 'open', 'close', 'high', 'low',
                               'volume', 'time'])

for row in range(sheet.nrows):
     timesecond = sheet.cell_value(row,1)
     price = sheet.cell_value(row,2) / 100
     volume = sheet.cell_value(row,3)
     newRow = [[timesecond, price, price, price, price, volume, timesecond]]
     ticks = ticks.append(pd.DataFrame(newRow, columns=['date', 'open',
                                                        'close', 'high',
                                                        'low', 'volume',
                                                        'time'
                                                        ]), ignore_index=True)

k5 = pd.DataFrame(columns=['date', 'open', 'close', 'high', 'low',
                            'volume', 'time'])
ticks['date'] = pd.to_datetime(ticks['date'], unit='s')
ticks['time'] = pd.to_timedelta(ticks['time'], unit='s')
ticks.index = ticks['date']
k5 = ticks.resample('5T', closed='left', label='left').apply({
         'open':'first', 'close':'last', 'high':'max',
         'low':'min', 'volume':'sum', 'time':'first'})
k5.reset_index(inplace=True)

k5.to_excel('D:\\Anaconda3\\WorkShop\\HistoryFiles\\k5\\k5_' + fileName +
             '.xlsx', sheet_name='sheet1', columns=['date', 'open',
                                                    'close', 'high', 'low',
                                                    'volume', 'time'])

# -----------------------------------------------------------------------------

k5['date'] = k5['date'].apply(mdates.date2num)

fig = plt.figure(figsize=(15,8))

ax = plt.subplot2grid((3,1), (0,0), rowspan=2)

candlestick_ochl(ax, k5.values, width=0.001, colorup='#ff1717',
                  colordown='#53c156', alpha=1.0)

fig.subplots_adjust(bottom=0.2)

ax.set_title('TAIEX Future - ' + date_title)
ax.set_ylabel('Price')
ax.set_xticks(k5['date'])
ax.set_xticklabels(k5['time'], rotation=30, ha='right', fontsize='small')

ax.grid(True)

ax2 = plt.subplot2grid((3,1), (2,0))

mpf.volume_overlay(ax2, k5['open'], k5['close'], k5['volume'],
                    width=0.3, colorup='#ff1717',
                    colordown='#53c156', alpha=1.0)

ax2.set_ylabel('Volume')
ax2.set_xticks(range(0, len(k5['date'])))
ax2.set_xticklabels(k5['time'], rotation=30, ha='right', fontsize='small')
ax2.set_xlabel('Time')
ax2.grid(True)
plt.subplots_adjust(hspace=0.4)

plt.show()