用 Python 與 Excel 打造簡易的回測系統 (下)

往全自動化回測邁進

感謝各位繼續觀看 Python 與 Excel 這一系列的文章,我們在學會了如何用 Python 與 Excel 這兩個强大的工具,近乎 0 成本的迅速的打造出一個實用的工具,實作出了一個實用而且可以運行的程式固然很有成就感,但是我們這支程式依然還有很多改進的空間,因此我將在這一篇文章裡繼續展示如何進一步完善我們的回測工具。

這邊先劇透一下,讓各位看這篇文章會教你做出來的東西:

雖然我們在上一篇文章實做出了我們要的結果,但是也衍生出了一個新的問題:股價資料是一個時間序列,意味著在試算表上,每一個交易日都會多出一筆資料,照目前的寫法,我們是把迴圈寫死 ,因此,我們的 Python 程式就只會計算到第 175 行的資料,要是今天我們的試算表上多了一筆資料,就會發生少算了一筆三日移動平均的狀況;另外,若少了一筆資料,那程式就直接出錯啦!

當然,一個笨辦法是,我們可以手動修改迴圈,但是由於我們試算表上的資料每一天都在增加,若每一次增加資料就需要修改一次程式,代表我們天天都需要修改我們的程式碼,若我們程式過度頻繁的需要被修改,那我們就失去了自動化的意義。

由此可見,魔鬼都在細節裏,我們勢必需要讓我們的程式能夠動態偵測試算表上資料筆數,然後動態的算出相對應的移動平均的方法。

如同以往,開始前請這邊請先下載我爲各位提供的,並且用 xlwings 開啟 TW2330 試算表。

end 屬性

當我們在處理 Excel 試算表時,經常會遇到不確定該試算表最下面一行資料的行數,或者是最右邊一欄是第幾欄。以便我們在寫程式的時候能夠設定迴圈的終結點。因此,我們可以使用儲存格的 end 屬性,從一個儲存格開始,沿著指定的方向,尋找到該方向連續,有值的最後一個儲存格

為了證明我們的程式找到的正確的儲存格,接下來我們就將其的顏色設定成紅色以方便驗證:

很完美的,現在無論我們的試算表上有多少筆的資料,我們的程式都能夠自動偵測一共有多少筆資料需要做計算!

另外試想一下,一般我們在投資時,都不會只投資一支股票,而是多支股票,或者可以說一個投資組合。若我們想針對不同的股票做回測,一個簡單的方法就是把不同股票的資料分別紀錄在不同的試算表內。另外,由於關注股票的時間點不同,不同的試算表上所記錄的股價資料筆數也會不同。若依照我們目前的寫法,雖然我們的程式現在已經能夠在不同的試算表上進行回測,但是現在依然需要透過手動的方式去切換試算表再執行程式。對於會寫程式的我們來説,不把這個部分自動化,根本就是罪過!

workbook.sheets 動態尋找所有試算表

接下來請下載我為各位準備的另一個并且開啓它。

如同我反覆強調的一個原則,要用程式去操作一個虛擬的物件,首先你得選擇它。今天我若要自動切換試算表,首先需要透過程式動態的選擇它:

sheet.activate() 打開試算表

今天若我希望能夠透過程式把某一個試算表打開,而且開啓會顯示在 Excel 上面,這時 xlwings 就提供了 sheet.activate() 的方法,一開始先截取到名爲 “TW2330” 的試算表,再將該試算表賦值給 sheet 變數,接下來我就可以針對 sheet 物件使用 activate() 屬性:

如同影片所顯示的,當 sheet.activate() 屬性被執行時,我們 Excel 上與 sheet 相對應的試算表也就被動態的開啓了。

接下來我們就把 activate() 方法搭配上一個迴圈,就可以輪流開啟每一個試算表了!

接下來把我們學會的東西整合一下,把之前我們實作出來的回測系統封裝進一個函式,就可以針對每一個試算表上的股價資料進行回測了:

如此一來,我們的回測程式能夠支援針對多個格式一樣的試算表上的股價資料進行運算:

進行投資組合回測的自動化程式就這樣完成了,若你需要完整版程式碼,請參考文章最下面。


Bonus:Msgbox 顯示對話方塊 (Windows 限定)

在成功把所有股票的回測都跑完之後,剩下的事就是實際檢驗到底我們的投資策略賺了多少錢,雖然現在我們的程式在跑完後,就會把該試算表的總收益寫入 K15 儲存格,但是要把每一個試算表上的總收益加起來,還是很麻煩,那還不如就直接透過程式自動把所有的總收益加起來,算出整個投資組合的總收益,但問題是,要如何顯示算出來的總收益呢?當然,你可以把它寫入在任何一個你指定的儲存格裡,但是好像還是不夠直覺,因此,另一個直覺的方法是將總收益直接以對話方塊顯示在使用者眼前:

結論

我們開始注意到,要用Python寫出一個程式不難,但是要把一個工具做到方便又好用,那就有非常多的細節需要考慮。希望能夠藉著這一系列的文章讓初學者見識到如何能夠透過漸近的方式,一步步的把問題與障礙排除,把一個工具打磨到好用的整個過程。

以下是完整的程式碼,供大家參考,你也可以點入直接下載整個 .ipynb 檔:

這邊要是有人對内容有疑問,歡迎將你的問題寫在這篇文章的留言,也歡迎參考我過去發表有關 Python 與 Excel 整合相關的文章:

2.

3.

如果您喜歡這篇文章,請多按下方的「拍手」圖像幾次(最多可到50次)、分享到社群網站以及訂閱 追蹤更多 Python 資訊,請關注 !🙏想看影音教學?🎥
我們將在 Pyradise 的 ,定期跟大家直播分享相關心得!
訂閱我們!👇👇👇

Pyradise

在 Py 樂園輕鬆學習 Python。

Eugene Chang (張有勁)

Written by

Pyrate of Pyradise 一個碼農,講師,與創業者的自白

Pyradise

Pyradise

在 Py 樂園輕鬆學習 Python。