米株をFirstradeやIB証券などの海外口座で取引した場合は、取引日毎にドル円レートで換算して、円での損益を計算していく必要があります。
今まで私は、為替レートは直接ドル円のサイトから調べて手入力してましたが、今後も海外口座での取引量が多くなるので、それを半自動的に取り込む方法を検討しましたのでご紹介します。
例としては、まずFirstradeのデータを扱います。対象は、オプション取引と、それに関連した株式の売買の取引です。エクセルの基本的な操作(Cut&Pasteや並び替え、セルの移動など)ができる事を前提にしています。
なお、確定申告に触れる内容もありますが、これは私の今までの解釈や問い合わせに基づく内容ですので、保証いたしませんし責任も取れません。あくまで自己で調査や税務署などに問い合わせください。
注) 最寄りの税務署に確認したら、一年の総合的な利益、あるいは損失に対して年末のレートだけかければ済むかもしれません。私は、ここで紹介する方法をやるように過去には指摘されましたが、最近は前者の方法で済んでます。電話でタックスアンサーでなく、最寄りの市町村の担当に確認するのが確実です。
Firstradeからのデータの取得方法
Firstradeのサイトから、以下のように、「Accounts」「TAX Center」とナビゲーションします。
すると次の画面が出てきます。
そこでExcelの箇所をクリックし、1年分の日付をいれます。なお、ここでは試行的に1/1から12/17までで選択していますが実際は12/31と入力が必要です。
そうすると、エクセルの取引データが出てきます。
Firstradeのエクセルデータ
実際のエクセルデータの一部は、以下のような形式です。
ここで株の場合は、Symbolの欄に、「TWTR」のようにティッカーシンボルが出てきます。他の物は、オプション取引ですので、Description欄に表示されています。
そしてSettled Dateを確定申告には使用します。これは、Trade Date、つまりTradeした日の+2日営業日後になっているはずですがこの例を見ると株はそのとおりですが、Optionはそれより早いですね。これは権利が移管した日なのでその日で計算をしています。
つまり、このSettled Dateの欄を使用し、ドル円の値を以降に示すように、自動で取り出していきます。
Firstradeの整形データのイメージ
それでは並び替えて整形したデータのイメージですが、以下です。
Settled Dateを元に、$レートを持ってきて、計算して、一番右の欄の円に換算すると、収益がわかります。ここで、BuyかSellかで、TTSを持ってくるか、TTBを持ってくるか選びます。
この欄では、Option取引きに基づく、現物株の売買(Put Optionで現物株がアサインされた場合)を扱ってます。
同様に、オプション売買の部分についても整理しています。
これが仕上げのイメージです。
ドル円データの取得方法
それでは、その仕上げに持ってくるため、つまりさきほどの図のドル円レートを自動で取り込む準備として、まず為替レートを1年分持ってきます。
自分の場合は、三菱UFJ銀行の以下のサイトからエクセルで持ってきました。
http://www.murc-kawasesouba.jp/fx/past_3month.php
すると、以下のような1年分のエクセルデータが手に入りますので、米ドル以外のユーロ等右側の箇所はすべて削除します。
そして、1月１日～1月５日まではレートが表示されてませんが、この期間に取引がされていることがありますので、空欄には、一番前日に近い値を入れます。この場合は、前年の12月末のデータがないので、例外的にそこについては、先ほどの三菱UFJのサイトから持ってきます。
あとは、この表にある、前日のデータをコピペしていきます。なお、土日についてはNY市場は休みで取引がないはずなので、手間を省くためコピペはしなくて良いです。
土日以外の日本の休日の箇所をコピペしましょう。
すると、以下のようになります。
そして、このシートのタブをさきほどのFirstradeのトレード用のエクセルのタブに移動してもっていき、ひとつりファイルにします。
以下のようなイメージです。
日付に基づくドル円取り込みの関数
上記のような準備ができたら、Firstradeのセルから、為替レートのテーブルを参照し、日付を検索してレートをコピーする関数を使用します。
VLOOKUP関数です。
TTSのシートの箇所を参照してコピーする関数をFirstradeのエクセルのシートにて定義します。
VLOOKUP(A2, data!A:E,3,0)がBの110.56の数値の所に入ってます。
A2は、上の図でAのセルの日付を検索対象としてます。その検索をdata!シートのAからEまでのセルのうち、左から3つめのセル(TTS)から取り出しています。0は判定ロジックです。
このVLOOKUPを下のセルにもすべてコピペしていけば日にち毎にすべて取り込みできます。
次にTTBの欄ですが、同様です。
ここでは、TTBを持ってくるため、元のデータの左から4つめのデータを取ってくるので、"4"だけ変更になってます。同様に下の欄にコピペをドラッグで一括して行います。
ドル円取り込みに基づく売買価格の円換算
これで準備ができました。あとは、株式やオプションのBUYかSELLかに基づいて、TTSかTTBを選択して、円に換算すれば、確定申告用の基礎データがでます。
なお、オプションの権利執行により、現物株を入手した場合など、そのプレミアムを考慮した整理が必要です。(詳細は省略します)
ここで、右側の緑の円貨の欄に次の式を入れます。セルが人によってずれてるかもしれませんので解説します。
=IF(G2="BUY", B2*(J2-K2), C2*(J2-K2)
これは、GのセルがBUYか否かで、TTSかTTBを持っているか選択してます。買う(BUY)時は、円を売り、不利な状況での取引になりますので、少し高く買うことになり、110.56のTTSを選択してます。
逆に売る(SELL)時は、ドルを円に変換のため、円買いですが、これも不利な108.56円のTTBを選択することになります。
そして、条件が真(つまりBUY)の時は、その為替レート(TTS)に価格(Amount)から手数料(Fee)を除いた額で計算すると、円の取引値がわかります。
B2 * (J2-K2)
の箇所です。これがSELLの場合は、逆TTBを持ってくるので、
C2 *(J2-K2)を計算することとなります。
同様に、この式をすべてのセルにドラッグすればすべてコピーすれば良いです。
注意として、このAction欄は、BUY,SELL以外にも金利のInterestやOtherなどの記述があるので、内容を確認して、金利収入や、コスト等で別途処理が必要です。
以上のように集計していけば、株式の分の損益とオプションの分の損益と必要なコストや金利がそれぞれ出てきます。
銘柄毎の整理を行ごとにしていき、足し算してTotal値を各々求めれば確定申告用の基礎資料ができます。
まとめ
海外口座の確定申告用に、米株をFirstradeやIB証券などの海外口座で取引した場合の、取引日毎にドル円レートで換算を一括でする方法について紹介しました。
株式、オプション、追加コスト、金利などでトータル値を集計すれば確定申告用の基礎資料ができます。
最初は大変かもしれませんが、慣れれば機械的に毎年できるようになります。