在 Excel 自動記錄即時價格歷史資料 20091210-Tick Hisotry.xls

在交易時經常需要 Tick 的歷史資料, 但在 Excel 裡要將資料留下歷史記錄需要一點小技巧.這個範例從 Yahoo!Finance取得最新的台灣加權指數, 並設定為每分鐘更新一次. 在更新的時候會自動插入到 History 工作表的最上方, 讓資料自動保留下來.

從Yahoo!Finance取得外部資料 

在範例中並沒有使用即時的資訊源, 所以需要多這個步驟. 如果有看盤軟體可以用DDE連線就可以省去這個步驟.

1. 選擇取得外部資料, 出現選擇網頁的視窗
   
2. 輸入 Yahoo!Finance 網址 http://tw.finance.yahoo.com/
3. 選擇想要匯入的資料, 在此我們選擇台灣加權指數
   

4. 將資料匯入到Excel, 並將工作表更名為 Yahoo!Finance

資料匯入到Excel
5. 修改查詢屬性, 在資料上按右鍵, 選擇 資料範圍內容. 將更新頻率更改為1分鐘一次. 虛擬的即時資料就算完成了.
   

建立 Monitor 和 History 工作表
6. 將空白工作表更名為Monitor, 輸入要監控的資料, 包含代碼, 時間, 以及即時價格. 時間使用 NOW() 函數, 即時價格連結到 Yahoo!Finance 的價格資料, 如果有即時資訊源的話輸入DDE公式.
Monitor 工作表
7. 在 History 輸入標題名稱即可.

History 工作表
8. 打開 Visual Basic 編輯器, 在左方的 VBA 專案選擇 Sheet3(Yahoo!Finance) 按右鍵後 檢視程式碼.
9. 選擇 Worksheet/Change 事件, 輸入程式碼就完成了

實作問題

1. 程式碼是由錄製的巨集修改而來的, 你可以自行錄製巨集並找出其中的差異嗎?
2. 範例中並不會檢查資料數量, 但 Excel 工作表的資料量是有限的. 可以限制資料數量成長嗎? 或是在實務上如何處理?
3. 當你要儲存超過一個商品的價格時該如何處理?
4. 如果資料更新速度很快, 用 複製/插入 的方式處理來得及嗎? 有沒有其他更好的方法?
   

範例檔案 20091210-Tick History.xls 在 Excel Examples by Alvin Cho.

明年度在證券基金會開課的一些想法

十一月以來在證基會的三堂課裡, 學員們最多的問題竟然是: 有沒有 Excel 的進階課程?

其實這個問題經常有學員提出來. 我原本不傾向開 Excel 的進階課, 因為從 IT人員的角度來看, Excel 的功能即使發揮到極致, 仍有一些不足的地方. 並不是說  Excel 不適合財務或交易應用, 對一般使用者而言它仍是最容易上手的工具, 但它功能的限制還是讓我在推薦給我的學員時會有一些顧慮.

今年在證基會開了八門課, 除了一門 IRS 評價之外, Excel 幾乎都只是輔助工具, 並沒有成為真正的主角. 但是由於學員多是財務背景, 資料庫和程式設計課程的確對某些人而言有難度. 所以很多學員要求開 Excel 進階與 VBA 的課程, 其實在現在的金融業是很需要的.

這幾天在規劃明年度的課程, 目前的想法大致上分三個部份:

1. Excel進階操作及設計: 主要目標在工作簿的設計和Excel基本功能的運用, 可能會包含一些簡單的VBA. 範例為投資組合管理.
   
2. Excel VBA 設計: 主要目標為 VBA 程式設計. 範例為程式交易策略.
3. Excel 財工應用: 利用 Excel 和 VBA 解決財務工程上的一些問題.

我會陸續將課程內容發表在 blog 上. 如果有任何建議歡迎 email 給我. 謝謝.

將網站從 mojoPortal 改為 DotNetNuke

這兩天將公司的網站從 mojoPortal 改為 DotNetNuke (DNN). 原本是想 mojoPortal 是 C# 做的, 雖然功能沒那麼多, 但是我比較喜歡用 C# 而不是 VB.NET. 後來用了這一陣子, 感覺還是沒有很好, 最主要的原因是, mojoPortal有以下幾個缺點:

• 速度太慢
• 無法多語言
• Google Analytics 和 SiteMeter 等網站流量統計必須以修改 skin 來完成.

DNN的功能很強, 比較之後還是將網站改為DNN. 目前也將增加英文網頁的內容, 並且把 RightPairs 和 DerivativePower 的網站整合進來. 歡迎大家光臨新的巨傑資訊網站.

使用正交迴歸在統計套利 Orthogonal Regression in Statistical Arbitrage

一般我們使用的迴歸是以 X 為自變數 Y 為因變數的迴歸 Ordinary Linear Regression, 簡單的說 Y 的變動主要是由 X 所引起的. 在 Excel 裡面有兩個函數: SLOPE 和 INTERCEPT 是用來算這類迴歸線的斜率與截距. 所求得的迴歸線是每一個點與迴歸線在Y軸垂直方向的距離平方和最小. 但是在我們要用來分析配對交易時, 不能使用這條迴歸線, 必須要使用正交迴歸 Orthogonal Regression. 垂直迴歸線與每一個點的垂直距離最小.

圖中藍色的垂直線就是一般線性迴歸到迴歸線的距離, 紅色的線是與正交迴歸線的垂直距離. 正交迴歸線表示 X Y 兩者都是自變數, 我們期望價格移動的路徑是紅色的線, 以垂直的方向朝著迴歸線移動.

Excel 並沒有提供正交迴歸的函數, 不過參考一般統計的書可以很容易在Excel計算正交迴歸線和垂直距離. 我將 RightPairsCalc 更新為正交迴歸的版本, 並且做了一些小修正, 放在 RightPairs 下載區 的 RightPairsCalc-O-11.xls.

實作 用Excel計算兩檔商品的相關係數

Excel是功能很強的計算工具, 對一般投資人而言, 通常資料的來源是比較大的問題. 有許多人是每天去下載交易所的資料存到Excel檔或是文字檔, 也有些資訊廠商有提供歷史資料下載. 在此我示範一下從 Yahoo!Finance 下載資料. 台灣的Yahoo!奇摩已經不提供歷史價格下載了, 美國的Yahoo仍有全球主要股市的歷史價格下載功能, 而且很方便.

1. 進入Yahoo!Finance, http://finance.yahoo.com
不 懂英文無所謂, 我們只需要幾個功能. 首先在標籤下方有 GET QUOTES 這個按鈕, 旁邊有一個框框可以輸入股票代碼. 由於Yahoo!Finance提供的是全球的股票, 所以要找台灣股票要在代碼後面加 .TW. 要找台積電則輸入 2330.TW 按 GET QUOTES

2. 2330.TW的資料出現之後, 在左方功能中有 Historical Prices, 按這個連結會顯示 2330.TW的歷史資料.

3. 預設的資料長度是5年, 每頁會顯示大約66天的資料. 將畫面移到最下方, 按一下 Download To Spreadsheet

4. 選擇 開啟檔案, Excel會自動開啟顯示2330.TW的五年歷史資料. 我們需要的是G欄的 Adj. Close, 這表示有調整過的價格.

5. 在 H欄輸入報酬率的公式. 報酬率的算法是 (當日-昨日)/昨日. 注意! 日期順序為新的在上. 例如H2的公式是 =(G2-G3)/G3

6. 將公式複製到一年份, 大約是262筆資料

7. 打開一份新的Excel檔案, 將剛剛計算的報酬率用 選擇性貼上/貼上值 貼到新的工作表

8. 把原來的歷史價格Excel關閉. 重複一次從Yahoo!Fiance下載資料, 將 2303.TW 的報酬率計算出來, 跟2330.TW貼在一起.

9. 利用Excel函數 CORREL 計算報酬率的相關係數.

注意: 所計算出來的報酬率略有偏差, 依不同狀況仍需做適當調整. 例如某一檔股票暫停交易期間, 應將報酬率資料刪除再行計算相關性.

用線性迴歸來決定兩組商品的關係

要用統計的方法來決定兩組商品價格的關係, 最簡單的方式就是用線性迴歸. 利用過去一段時間的歷史資料, 求出這兩組商品的迴歸線, 在這個線上的點, 我們就認為是這兩組商品的正常關係. 只要有偏離這個迴歸線, 我們就假設這只是短期的偏移, 在短時間內會回到迴歸線上.

我製作了一個簡單的線性迴歸的計算工具, 功能包括

1. 從Yahoo!Finance下載三個月的歷史資料
2. 計算迴歸線
3. 計算每一個資料點與迴歸線的距離, 以及距離標準差
4. 計算目前市價的距離標準差

直接在藍色區域輸入商品的代碼, 黃色區域可以自行連結即時資訊源. 在右方的 XY 圖顯示每一個資料點的位置和迴歸線. 目前的市價則以粉紅色的點表示.
這個檔案可以在 RightPairs 下載區 免費下載, 檔名開頭是 RightPairsCalc-R.

在這裡使用的是Y為因變數的簡單線性迴歸, 但實際操作時不應該用這個迴歸線, 這個工具僅供參考, 不建議用來實際操作.