用IQY匯入網路資料到Excel

上一篇文章其實是我把workbooks的功能誤解了，只是macbook似乎沒有workbooks開啟URL的功能？所以直接在巨集(macro)裡面輸入

Workbooks.Open Filename:= _
    "http://www.twse.com.tw/fund/BFI82U?response=csv&type=day" 

還是會跳出錯誤訊息。

上次我是直接建立iqy檔來取得網路資源，這一部份可以再往下發展。

首先仍要自行建立一份iqy檔，如果是windows可以直接使用Excel的「資料」→「從Web開啟」→「新增Web查詢」然後直接在「新增Web查詢」的視窗中儲存為iqy檔就好，但因為Mac沒有這個功能，所以只好自己把原始碼輸入在.txt中，再存為.iqy了。

 

一樣使用「http://www.twse.com.tw/fund/BFI82U?response=csv&type=day」作為示範

首先把以下原始碼存成iqy

WEB
1
http://www.twse.com.tw/fund/BFI82U?response=html&dayDate=[]&type=day

Selection=EntirePage
Formatting=None 

其中「WEB」表示檔案是Web Query

 「1」表示Web Query的版本

 「Selection=EntirePage」表示選擇網頁表格編號

 「Formatting=None」表示不存取網頁格式，只要純文字

 之後一樣從「資料」→「取得外部資料」→「執行網路查詢」開啟剛剛儲存的.iqy檔案

 之後選擇表格A2的位置，按確定

  

然後會出現輸入參考值的視窗，因為剛剛在網址的「dayDate=」後面加上中括號「[]」的緣故，這個格子可以填入日期，指定要擷取的日期。而我們選取A1作為填寫指定參數的位置，勾選「將來更新時使用這個數值或參照，再按確定。

如果是Windows版本的Excel，會有一個「當儲存格值變更時自動更新」的次選項，但是Mac沒有，所以還要手動重新整理...（漸漸感受到使用Mac製作VBA的崩潰感了...）

填入日期（YYYYMMDD）之後，再按下「（全部）重新整理」就可以了。

使用這個方法製作iqy就可以省下很多csv資料剖析的動作。

最後再整理一下數字，選取數字部分，按右鍵「儲存格格式」

在「自訂」的地方輸入「#0!.00,, ;[紅色]-#0!.00,,」，即可以把單位變成「億」

在這個格式中，驚嘆號代表「小數點往左移一位」，逗號代表「小數點往左移三位」，而兩個逗號代表數值的單位是百萬，0!.0代表小數點往左移一位，單位變成千萬，0!.00代表小數點往左移兩位，單位變成億。而#代表數字，驚嘆號前面的0代表數值如果為零，仍會顯示「0」，如果是「#」且數字為0，那就會顯示為「.1234」。

所以這個格式就代表，省略兩個三位數=單位百萬，再把小數點往左移兩位，顯示的數值單位就變成「億」了，然後再把負數變成紅色，就可以製作出平常看到的以億為單位的三大法人買賣金額！

成果如下

完成！

參考資料：

《Excel VBA實戰技巧：金融數據×網路爬蟲》

小課堂：如何設置Excel中數字由千、萬到億的格式？