NEC退稅稅務資料抽取試算表 (2013~2023)快速取得六家主要ETF發行公司的稅務資料

當NEC退稅試算表實用化確立之後，輸入基金公司的稅務資料變成了NEC退稅中相對麻煩的部分，也是造成錯誤的主要來源之一。對於熟悉試算表操作的人來說，利用試算表的公式和剪貼、填滿功能也可以省不少事，但對初學者來說，這似乎還挺難理解的，特別是一些經由PDF檔案轉換過來的試算表，由於表格有時會錯位，更容易出錯。這裡針對有在關注的6家知名ETF公司的稅務文件，做成系列自動化稅務資料抽取試算表放在雲端硬碟分享：

• 稅務資料抽取試算表 (資料夾)(2023/3/20更新)

本系列試算表使用了很多Excel沒有的功能，只能配合Google雲端硬碟使用。使用時請先建立副本，就能輸入自己的資料，無須試圖取得編輯權限。如無Google帳號，需先建立帳號，才能在自己的雲端硬碟建立副本。

使用方式很簡單，只要選擇對應基金公司和年份的試算表，在淺綠色格子填入需要的ETF代碼，就會列出對應稅務資料，只需選取、複製，然後到NEC退稅試算表，按右鍵>選擇性貼上>僅貼上值，就OK了。



手動輸入ETF代碼如果查無資料，會用紅色粗體提示，請確認是否輸入錯誤。此外，2015年有些債券ETF整年都沒有配息，會用刪除線提示。

其實要更懶的話，連代碼都不需手動輸入，只要從戶頭的Position列表把代碼複製下來，也不用區分，直接把整個列表貼進去各個試算表，試算表會自動跳過不屬於該公司的部分 (沒有的部分用紅字標示)。Vanguard、iShare、Powershare、SPDR (2015~)、WisdomeTree (2019~) 沒有限制代號數目，其他則有50個的限制，不過應該也夠用了 (要增加也很方便，有需要請留言)。

如果發現試算表無法正確解析的基金資料，後文的相關連結中有2012年以來退稅相關稅務資料的系列文章，可找到原始文件，或是直接下載我保存的備份，自行取得所需稅務資料。

2022年SPDR系列ETF沒能拿到QII數據

由於蒐集文件時基金公司已將相關文件下架，詢問客服回應是不會再提供。有相關美國債券ETF的話要自己想辦法，向基金公司或券商取得數據。如果實在沒辦法取得，而券商有提供正確的QII退稅和1042-S再分類的話，是也可以從每筆退稅金額佔原始預扣稅的比例來推估可能的QII比例或者其平均值，這雖然不是正確的數字，但可以用來確認券商提供的1042-S和實際帳目的預扣稅是一致的。如果實在沒有正確數字，要不要就用這個逆推的數字申報，請自行斟酌。

Invesco DB系列原物料期貨ETF要到自己到官網找

因為稅務結構不同，此類ETF在美國人是K-1表格為主要報稅依據，和一般ETF的1099-DIV表格大不相同，所以一般ETF的稅務資料並未涵蓋此類ETF，因此必須自己到Invesco官網個別ETF資料網頁的配息紀錄找。

其他請自行到官網找或利用Google搜尋

例如PIMCO的歷年配息資料到官網各ETF介紹頁找，QII資料則在Resources頁面。搜尋的話可以利用ETF代碼、基金公司名稱，加上Tax、QII (Qualified Interest Income)、ROC (return of capital) 、Foreign Tax Credit 等關鍵字搜尋。

其他不能直接套用的狀況

以下說明一些大部分人不會碰到，但碰到了會需要另外手動處理的狀況。使用上沒問題的話都可以忽略不看：

1. 有些ETF年末配息會出現同一派息日有兩筆資料的情形，一筆是股息，另一筆是長短期資本利得。試算表會以紅色背景色和文字信息提醒關注。本來必須手動將兩筆資料加總整併成一筆，再填入NEC退稅試算表，但因為試算表預設為擷取最大值，所以如果同一欄位的兩筆資料只有一個數值，試算表就可以自動處理，但仍需看實際狀況確認。例如2022年Vanguard的BIV、BND，以及某些PowerShare和Market Vectors ETF。
2. Exempt-interest dividends： 主要發生在美國市政債券 (Municipal bond) ETF。根據2019年網友們提供的報稅資料，Schwab、TD Ameritrade和Firstrade的1042-S都將之歸類於免稅，也沒有預扣稅，但三家公司所得分類方式不同，NEC退稅試算表的處理方式也不相同。Schwab、Firstrade的最新退稅試算表已經有針對性的調整，擷取試算表也做了調整，已經可以直接使用。TD Ameritrade版本沒有提供此功能，他是把此類收入與QII放在一起，但沒有預扣稅，所以如果有碰到，可以把Exempt-interest dividends加入Income Dividend、QII per share兩個欄位，然後手動把預扣稅設為0，計算結果就可以相符，當然，報表的Note能自行增加相關說明會更好。不過，市政債券ETF對於NRA還是有潛在的不利之處，更別提是有些高息的市政債券，其實風險不低，別因為免預扣稅和高配息就貿然投入。
3. Cash liquidation distribution：ETF清算後配發的本金，照說是不該被扣稅，但沒實際碰到過，也不清楚帳目如何記錄，對NEC退稅試算表的影響不明。有碰到的朋友歡迎分享。目標到期債券基金 (defined-maturity bond funds) 到期後返還本金也屬於此類，有網友提供嘉信的實例研究，其到期債券ETF的交易紀錄Action (行動)欄位標示為Final Cash Liquid，和最後一筆配息同時入帳但分別記錄，沒有預扣稅，1042-S的分類是Income Code 37，但實際分類計算有些問題，這裡有詳細的討論。

相關文章

美國國外投資人要報稅嗎? 能退稅嗎?
配息超過(取消)個人免稅額後的報稅考量
美國券商NRA投資人NEC退稅總整理 (含退稅試算表/報表)
2021年NEC退稅相關稅務資料來源整理
2020年NEC退稅相關稅務資料來源整理
2019年NEC退稅相關稅務資料來源整理
2012年NEC退稅相關稅務資料來源初探
再論國際ETF配息是否為國外來源？
Income Code 27與DBC或其他原物料期貨基金的稅務問題 MLP ETF的股息可能含有大量ROC美國市政債基金 (Municipal bond funds) 的稅務結構與潛在問題

[附註] Google試算表的國別設定與日期格式轉換經驗歸納：

1. 如果日期欄位是以日期資料儲存的，無論試算表語言代碼設定為何，均可自由變更日期格式。
2. 如果日期欄位是以文字資料儲存的，如果符合語言代碼對應的格式，就能藉由設定日期格式將其轉換為日期資料。若設為台灣 (台灣地區預設)，必須嚴格遵守【年/月/日】格式才行。若設為美國，則YYYY/M/D和M/D/YYYY都可以接受，但YY/M/D則會被誤判為M/D/YY，產生錯誤日期。
3. Datevalue也依循上述原則運作，但變更語言代碼並不會改變試算表原有的計算結果，必須變更公式或引數的儲存格內容，才會按新的語言代碼規則重新計算。
4. 藉由PDF轉換工具產生的試算表檔案，日期均以文字格式儲存，所以必須將試算表語言代碼設為美國，日期的運算和操作才能正常運作。

2023更新內容：

1. iShare的原始檔案轉換EXCEL格式再次失敗，得比2022更離譜，還好終於又找到一個還能轉的工具，缺點是只能轉成許多分頁需手動整併，而且轉換時誤增的空白欄位比過去更加不規律，無法再用算式判斷，於是只好在整併時順便對齊全部欄位，讓抽取資料的算式簡單化，不過這樣結果應該會比較可靠。這工具轉換後，百分率數字為文字格式，其他數字均為數字格式，兩者需要分開處理。更新為v2 。EFV的ORDINARY DIVIDENDS問題仍與2022相類，原因不明。
2. SPDR 今年總算來得及下載QII資料，。
3. VanEck新增一個章節，且ROC章節欄位變動，更新為v1.23。連帶發現過去幾年版本多有錯漏，2017已經逾期不管，2018從缺，2019起都更新為v1.22。不過好像這家ETF沒甚麼人關注都沒有報錯。

2022更新內容：

1. iShare的原始檔案轉換EXCEL格式失敗，改用其他工具後配息數字也變成文字格式，需要手動轉換資料才能套用公式，表頭資料也需要調整。此外，EFV用ORDINARY DIVIDENDS=DIVIDEND INCOME+SS/T CG+Foreign Tax Paid的計算結果和Total distribution欄位數字不相符，而後者與其他管道的提供的配息數字，只好改用Total distribution扣除其他類別配息來計算，故更新為v1.16，但這個變更使得後面的驗算欄位失去意義。不過檢視了其他幾個國際基金，並沒有此類情形，不知道是否為單一錯誤或另有緣故。
2. SPDR 缺QII資料。
3. VanEck新增一個章節，且ROC章節欄位變動，更新為v1.23。連帶發現過去幾年版本多有錯漏，2017已經逾期不管，2018從缺，2019起都更新為v1.22。不過好像這家ETF沒甚麼人關注都沒有報錯。

2021更新內容：

1. Vanguard Primay Layout的TOTALS列標示改變，更新為v1.13。
2. iShare的日期改為mm/dd/yyyy，故更新為v1.15。DIVIDEND INCOME、SS/T CG從2020年開始沒有直接提供，但今年才做了完整的應對，同時也把2020年更新為v1.14r，所幸對熱門ETF沒有影響。
3. VanEck的ROC章節欄位變動，更新為v1.22。同時Exempt-interest dividends欄位函式新增配息僅有ROC配息的狀況。

2020更新內容：

1. 去年新增Muni支援後，不少人誤把稅務資料貼入Exempt-interest dividends，導致計算錯誤，今年將稅務資料的欄位順序做了調整，以盡量避免誤貼問題。
2. WisdomTree 2020年稅務資料也改為標準的ICI格式，直接移植套用省事不少。
3. iShare的欄位大變樣，正確性也還不確定，使用時要小心注意排除錯誤。
4. VanEck Vectors 2020年其實大致延續2019年的表格編排，但新增一個章節還是導致試算表需要調整，由於原始結構就複雜，花了不少時間才研究解決，不過考慮原理沒有大改變，版本編號只小改為v1.21。

2019更新內容 (如無特別說明，均只針對2019年版的變動，2013-2018無改變)：

1. iShare的錯位型態又有改變，須調整對應函式 (感謝 V.H. 指正)。
2. SPDR的QII又延遲更新，再次調整部分交易代碼須更新QII查詢方式為v2.13
3. Invesco 工作表名稱有小變動，抽取試算表也相應調整為v1.09。
4. VanEck Vectors 2019年的表格編排又有變動，抽取試算表也相應調整為v1.2。原始資料內容雖比過去少，但表格簡化使錯位問題比過去更少，檢索函式也大為簡化。
5. WisdomTree 2019年主要檔案直接以 Excel 格式提供，試算表也相應調整為v2.0，檢索函式大為簡化。

2018更新內容 (如無特別說明，均只針對2018年版的變動，2013-2017無改變)：

1. 微調各試算表市政債ETF的提示格式和文字。
2. iShare的新增欄位造成FSI%右移，須調整對應函式。
3. SPDR：QII轉換表格仍有標註去2016年改名的SPDR portfolio系列的舊代碼，對轉檔效果影響更大，需要用新的條件算式解讀。檔案格式雖為試算表，但日期需要手動將試算表地區設定為美國，設定好日期格式，才能正確讀取。
4. Invesco (原Powershares)系列ETF的品牌改了，名字和CUSIPs也跟著變了，但交易代號不變。檔案格式雖為試算表，但日期需要手動將試算表地區設定為美國，設定好日期格式，才能正確讀取。
5. VanEck Vectors 2018年的檔案有許多資料是掃描圖，無法有效轉換為Excel格式，沒有辦法製作抽取試算表，連搜尋結果不完整，所以只能自己按目錄和排序一一檢視，然後自行輸入。修改2016~2017年的檔名，因為2016年5月系列ETF的品牌改了，名字和CUSIPs也跟著變了，因為交易代號不變不影響試算表功能，所以先前沒注意更新檔名。
6. WisdomTree的錯位方式又有改變，各欄位定位方式需要調整，同時發現部分函式冗贅、邏輯不周延，因為調整後才凸顯出來。過去版本未造成明顯錯誤，故未往前更新。
7. Schwab在2018年終於找到官網連結，只可惜沒有統整的稅務資料檔案，無法製成稅務資料抽取試算表，QII資料也仍待驗證。詳情請參考2018年NEC退稅相關稅務資料來源整理的說明。

2017更新內容 (如無特別說明，均只針對2017年版的變動，2013-2016無改變)：

1. 微調各試算表市政債ETF的提示格式和文字。
2. SPDR：QII轉換表格稍有位移，且由於有標註去年改名的SPDR portfolio系列的舊代碼，影響轉檔效果，需要設定條件算式解讀。另外，17年12月底的配息日期不知何故被標到1月初，需要自己手動改一下才能搭配Tax Summary。
3. Market Vectors 2017年的檔案格式大改，Excel格式錯位的問題大為改善，但將稅務資料拆成幾個不同Section的編排整個解讀方式需要重建。QII資料未再提供，目前無解。
4. WisdomTree的錯位方式和往年不同，位移變化性變大，各欄位定位方式需要調整。

2016更新內容 (如無特別說明，均只針對2016年版的變動，2013-2015無改變)：

1. Vanguard的Secondary layout格式改變，調整對應公式
2. iShare的Distribution Summary Information新增Cash Liquidation Distribution及Exempt Interest Dividends欄位，需調整國外來源%的公式，並增加更多驗證信息
3. SPDR的Primary layout有些空欄位被填空白，篩選引用、附註、條件化格式之公式需調整；日期欄位以文字方式儲存，需先設定試篹表國別為美國，再重新設定為日期格式才能正常讀取為日期；QII轉換表格稍有位移，且QII %去年沒%今年有，故對應公式需微調
4. SPDR在2016年有兩隻市政債券(Municipal bond) ETF曾經變更CUSIP，而不同CUSIP的國外來源%是分開表列的，當試算表利用ETF代碼(Ticker)查詢國外來源%時會不知道要取哪一筆而出現【#REF!】的錯誤信息。碰到的話將國外來源% (0) 手動填入就行了。
5. PowerShare和Market Vectors有些ETF的年末配息會出現同一派息日有兩筆資料的情形，一筆是股息，另一筆是長短期資本利得。這問題過去已經有處理，但2016在QII也同步出現此現象，故需調整QII查詢公式，以避免查詢結果超過一列造成錯誤。
6. Market Vectors的PDF轉檔造成QII標題變形、國外來源標題改變，須調整查詢條件。

2015更新內容 (如無特別說明，均只針對2015年版的變動，2013-2014無改變)：

1. 更新2015年NEC退稅稅務資料以及2013年WisdomTree的QII資料 (抽取公式需調整)
2. SPDR改用Excel格式發布稅務文件，其試算表需大改版，資料正確性已經沒有問題 (其實是改套Vanguard公式，反而更輕鬆)
3. 沒有配息的ETF變多了，現在輸入ETF代碼如果查無資料，會用紅色粗體提示，請確認是否輸入錯誤。整年都沒有配息，會用刪除線提示，以方便找出代碼錯誤。
4. 修正Market Vectors 2013、2014的小錯誤 (不影響資料正確性)
5. Market Vectors 增加2015 QII內容，國外來源抽取公式也有修正，部分配息記錄無加總行也需調整公式
6. WisdomTree原始資料增加不少欄位，公式需調整
7. PowerShare的FSI%以前沒有%，現在有%

SPDR 2013-2014的PDF檔轉換問題比較多

試算表有特別加強驗證計算，把有問題的部分用不同顏色標出來。較可能出問題的是L/T CG、ROC和國外收入百分比，前二者透過總配息 (Total Distribution) 的比對應可察覺大多數錯誤，後者則沒有任何驗證依據，只能請大家自己用原始PDF資料或其備份對照確認。2014年甚至原始PDF檔本身就有部分資料多出一列，完全不知如何對齊，只能自己透過其他資訊來源校正。