用 Python 與 Excel 打造簡易的回測系統（上）
量化交易起步走
摘要
感謝各位對前兩篇文章的支持。在上一篇文章我們深入淺出了 xlwings 這個强大的套件，這個套件比起 openpyxl 這個傳統 Python 串接 Excel 套件的優勢在於，它能夠讓程式執行的結果可以即時的輸出到開啓的 Excel 試算表上，做到所見即所得，無需先關閉 Excel 檔才能寫入，這樣大幅的提高了開發的效率。 我們也理解了在 Excel 這個以表格構成的世界裏，要操作一個儲存格，首先一件事就是你必須先選擇它，若是透過程式碼來實作，選擇單一儲存格的方法，就是使用 Cells 函數。在這一篇我們將會應用我們所學會的來打造一個實用的工具：簡易股價回測系統。
認識歷史回測
當我們在股票市場上交易時，若是對當前市場的趨勢變動沒有把握時，就需要歷史回測 (Back Testing)，也就是利用股價的歷史資料來模擬真實的開盤狀況，借此來驗證自己的交易策略是否是有效的。回測不僅僅只是數學計量模型的計算，也包含了計量模型以外的市場分析。
以往大家都沒有完整的歷史數據、只有零散在各處的新聞媒體或券商的統計、盤後資料時，是很難進行歷史回測的，另外，就算有了資料，客製化一個程式去分析這些數據的門檻相對也高。而現在由於資料取得容易，Python 與 Excel 等簡單易學的工具又十分普及，能夠快速而且低成本的打造一個計量回測工具不再是一件遙不可及的事。
接下來我將在文章裡跟各位介紹一個簡單的量化指標，以及我們可以如何利用該指標衍生出來的交易策略，加上 Python 與 Excel 兩個強大的工具來實作台積電股價的歷史回測。
計算移動平均
移動平均 (Moving Average) ，又稱均線，是一種量化指標；它的算法有很多種，而在實作上最容易的一種算法就是簡單移動平均 (Simple Moving Average)，簡單移動平均非常好理解，就是每天算出一個股票過去某一段時間收盤價的算術平均值，再用該平均值與當天的股價做對比，判斷該股票是處於上漲或是下跌的趨勢。 接下來請下載我為各位準備好的範例 Excel 檔案，並且切換到名為 “2330” 的試算表。
我們先用 Excel 的公式來算出第一筆3日移動平均，在 D4 儲存格輸入以下公式即可：
=AVERAGE(B2:B4)
很快解讀一下這個 Excel 公式：在 Excel 公式裏，冒號 : 是 Excel 用來表示範圍的符號，代表儲存格的從冒號左邊所代表的儲存格開始，到冒號右邊所代表的儲存格結束的連續範圍，所以 B2:B4 代表從 B2 開始，包含 B4 的三個連續儲存格裏的三個數值。而這三個數值的算術平均值就會被 Average 函數計算出來並且顯示在 D4 儲存格。
若我們在輸入此公式後再使用下拉的方式，很快就可以把每一天的三日移動平均算完：
接下來我們就試試看用 Python，透過程序化的方式算出同樣的 3日移動平均，這邊就來為各位介紹一個 xlwings 的實用功能。
.formula 屬性
簡單來説，在 Excel 的世界，每一個儲存格都有一個相對應的公式，若今天我們希望透過程序化，而非手動的方式去設定該儲存格的公式，我們就可以善用 xlwings 套件中，cells 的 formula 屬性，動態設定該儲存格的公式：
上面的程式碼若執行成功，那 C4 儲存格的公式應該會自動被設定成：
=AVERAGE(B2:B4)接下來 Excel 就會執行此公式，將 B2 到 B4 的平均值算出，並且顯示在 C4 儲存格裡。
若我們再繼續把第二天的公式寫出來，我們注意到了在 C 欄裏，計算移動平均的公式裏其實存在著一個規律：無論在任何一行，代表儲存格起點與結束點的 B2 與 B4 的差距永遠為 2。所以今天若我需要把透過動態設定公式的方法來算出所有的三日移動平均，最麻煩的事就是要如何動態修改每一個公式裏面起點與結束點的數值。這邊我們可以善用 Python 字串的 format() 方法幫字串格式化，用一行程式碼優雅的解決這個問題：
在理解了如何動態改變 C 欄每一行儲存格的公式後，我們就可以用一個簡單的迴圈，透過設定 C 欄儲存格的公式算出試算表上所有資料的 3 日移動平均：
算出了所有的移動平均值後，我們就把它投入實戰，用它來當作我們回測的量化指標，實作出一個簡單的回測系統。
用三日移動平均來打造一個簡單的交易策略
接下來請各位切換到範例 Excel 檔案上名為 “2330 Excel” 的試算表，因爲篇幅有限，我把這篇文章範例的投資投資策略設定的非常簡單：
- 若當日的收盤價是大於當日的移動平均，就買進股票
- 若當日的收盤價是小於當日的移動平均，就賣出股票
*備注：非投資建議，本文提供的資料及交易策略，只可作為參考用途，本文的重點在程式教學而非投資教學，務請各位讀者運用個人獨立思考做出抉擇，如因此招致任何損失，概與本文無涉。
這邊請各位打開範例試算表上的 2330_excel 試算表，這邊簡單敘述一下試算表的格式：
- D 欄顯示的是買入的股數，是指若某一天的股價符合我們的策略，就買入股票，而這裏我們爲了簡化回測的邏輯，買入一律是 1000 股，所以 D 欄的儲存格只會顯示 1000 或 0 兩種不同的可能性。
- E 欄則代表賣出的股數，是指若某一天的股價符合我們的策略，就賣出股票，如同 D 欄，爲了簡化回測的邏輯，賣出一律是 1000 股，所以 D 欄的儲存格只會顯示 1000 或 0 兩種不同的可能性。
- F 欄代表指目前累積的股數，買進會讓股數變多，賣出會讓股數變少，該欄的算法是：
當天的買進股數 - 當天賣出股數 + 前一天的持有股數- G 欄代表目前持有現金，我們的起始資金為 1,000,000 元，買進會讓現金變少，賣出會讓現金變多，該欄的算法是：
(當天賣出股數 - 當天的買進股數) x 當天收盤價 + 前一天的持有現金- H 欄代表總資產，也就是我們目前的現金與持有股票價值的加總，該欄的算法是：
持有股數 x 當天收盤價 + 當天持有現金這些邏輯我已經都轉成 Excel 公式，各位若有興趣可以參考範例 Excel 檔案裡 名為 “2330 Excel” 的試算表，回測執行的過程可以參照以下影片：
最後可以看到，透過我們的交易策略，在回測的最後一天我們總共小賺了 500 元。
用 Python 將整個過程自動化
在看完了要如何用 Excel 的公式實作出我們的回測後，接下來就試試看用 Python 來實作出一樣的功能吧：
在截取到名為 “2330_python” 的試算表後，我們先用之前寫好的程式碼計算出所有的三日移動平均：
接下來，由於範例試算表的格式，我們在做完整的回測時，有一些公式會需要參考前一天的資料，因此，我們先用手動的方式算出第一筆資料：
接下來從第五行開始到最後一行的資料就可以用一個迴圈解決：
大功告成！
詳細的執行過程各位可以參照以下影片:
希望這個簡單的範例能讓大家見識到如何使用 Python 處理複雜的邏輯，並且將運算的結果即時的呈現在 Excel 試算表上。以下是完整的程式碼，供大家參考，你也可以點入以下連接直接下載整個 .ipynb 檔：
若你的電腦是 Mac，由於 xlwings 的 cells 不支援以英文字母的方式表達欄，所以在實作上會有些微的差異，因此請查看以下程式碼，或點入以下連結直接下載整個 .ipynb 檔案：
小結
我們在這篇文章裡學會了如何用 Python 語言與 Excel 打造出了一個簡單又實用的回測工具。一開始我們先理解了回測的概念，以及如何能夠用一些簡單的 Excel 公式在試算表上實作出了回測，當我們理解了回測的運作過程之後，我們就開始用 Python 將整個流程寫成了一支程式，得力於 xlwings 能夠做到即時的讀取與寫入，我們用 Python 寫出的程式能夠以非常視覺化的方式呈現回測的過程與結果。雖然在程式碼的理解和實作上可能耗費掉不少時間，但是別急，一旦當我們能夠將一個複雜的流程寫成一支程式後，我們就往全自動化邁進了一大步。
這邊要是有人對内容有疑問，歡迎將你的問題寫在這篇文章的留言，也歡迎參考我過去發表有關 Python 與 Excel 整合相關的文章：
如果您喜歡這篇文章，請多按下方的「拍手」圖像幾次(最多可到50次)、分享到社群網站以及訂閱 Pyradise！追蹤更多 Python 資訊，請關注 Pyradise 的臉書粉絲專頁！🙏想看影音教學？🎥
我們將在 Pyradise 的 Youtube 頻道，定期跟大家直播分享相關心得！訂閱我們！👇👇👇
