在 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.