ホーム / 2022/02/14版 取引明細書 解説 /

2022/02/14版 取引明細書 解説

取引明細書の作成方法

私の方法を紹介します。また、取引明細書のテンプレートファイルも用意しています。
取引明細書の良い作成方法については私も試行錯誤中である上に表計算ソフトも慣れていないため、ベストなアドバイスは出来ないかもしれないことをまずご了承ください。また、私の方法について改善点等あれば教えて頂ければ幸いです。

まず為替レートを入手する必要がありますが、これには「電信売相場、電信買相場及び電信売買相場の仲値については、原則として、その者の主たる取引金融機関のものによることとするが、合理的なものを継続して使用している場合には、これを認める。」（法第57条の3《外貨建取引の換算》関係）という通達があります。
主たる取引金融機関が為替レートを公表していれば良いのですが、公表していないことが多い上にデータシートとしてダウンロードできなければ不便です。
私は、三菱UFJの公表している為替レート表を用いるのが良いと思います。→三菱UFJリサーチ&コンサルティング「1990年以降の為替相場」
このページの下部で、年ごとの為替レート表のファイルをダウンロードできます。

この為替レート表を基にエクセル等の表計算ソフトで取引明細を作成します。私は表計算ソフトとして無料のLibre Officeを使用しており、以下それを使用した例も挙げますが概ねエクセルも同じなので参考にできるかと思います。
この為替レート表を、表計算ソフトのファイルにシートとして挿入するか、コピー＆ペーストします。取引明細を記載するシートとは別のシートにするのが良いと思います。
基本的には申告する年分の為替レートのみ用意すれば良いと思いますが、もし複数年の為替レートが必要な場合は繋ぎ合わせます。
日付は上から古い順に並べるようにします。vlookup関数（後述）で拾い上げられるようにするためです。複数年の為替レートを繋ぎ合わせる際はもちろん表の途中に余計な行が入らないようにします。
また、この三菱UFJ銀行の為替レート表は、休日の行も含まれており、休日の為替レートが空白になっています。vlookup関数を使用するにはこの空白行を削除する必要があります。
そのためには、フィルター機能を使い、為替レートの列を「空白でない」でソートし、その結果をコピーし貼り付けます。または空白でソートし、空白行を削除します。

株式の場合は少し厄介な点があり、確定申告では取得費の合計と売却代金の合計をそれぞれ記載しなければならない点です。空売りの場合は、新規建て時は売却代金、決済時は取得費の扱いになりますので、買建てと売建ての両方がある場合は、新規建て価額の合計と決済金額の合計をそれぞれ記載する訳では無いことに注意ください。ただしこの計算は表計算ソフトを用いれば何とかなります。

支出に記載する数値はマイナス、収入に記載する数値はプラスで統一しておくと便利だと思います。以後、これを前提にします。
株式・現物オプション・現物FXは総平均法に準ずる方法、先物・CFD・オプション(差金決済)・FX(差金決済)は証券会社のステートメントに従って計算します。
株式・現物オプション・現物FXについては、新規建て時と決済時の両方について取引を記載します。
私は試作として表計算ソフトで自動的に計算できるものを作成したので、その方法を説明しようと思います。
現物FXに関しては、外貨の為替損益に含まれますので、外貨の為替損益の計算方法で解説します。
証券会社を複数利用している場合は、シートで分けます。

私の作成した取引明細のテンプレートファイルは以下です。

ダウンロード - 取引明細雛形
※2022/02/14 更新

odsファイルはLibreOffice用、xlsxファイルはExcel用です。ただしxlsxファイルもExcelで作成したものでは無いので書式が一部崩れているかもしれません。ご了承ください。
オレンジのセルは数式が入るセルです。数式の入っていない行にはオートフィルで適用できます。
為替レート表のシートには、参考として三菱UFJから取得した為替レート表を貼り付けていますが、ここは必要な期間のものに変えて下さい。vlookup関数内の、為替レート表への参照範囲は必要であれば変えて下さい（後述）。
その他、基本的な利用方法については「利用方法」シートにまとめていますが、詳しくはこの記事での解説を読んで頂ければと思います。

次に、取引明細の作成方法を一から解説します。テンプレートファイルを利用する場合も、次からの解説を読んで内容を理解した上で利用することを薦めます。また、計算の結果、不自然な値が算出されていないかどうかを確認してください。

株式およびオプション（現物取引）の取引明細

ここでは株式およびオプション（現物取引）の取引明細について、私の作成方法を詳しく解説します。配当については、この後の項目で触れます。
株式とオプション（現物取引）は取引明細の仕様は同一ですが、税区分は異なるためシートは分けた方が良いと思います。

ここでは株式を想定した用語を用いますが、現物オプションの場合は「株式」を「オプション」、「株数」を「数量」、「空売り」を「ショート」などに読み替えて下さい。
「総平均法に準ずる方法」について説明します。以下は株式の購入を行う際の説明になりますが、株式の空売りの際は「取得」を「空売り」、「保有株数」を「空売り株数」、「売却」を「買戻し」などに読み替えて下さい。
同一銘柄の株式について、取得した都度、取得金額(円換算額)を足していき、取得金額の合計(円換算額)を算出します。そして「取得金額合計(円)÷保有株数」がその時点での取得単価となります。
そして、株式の売却を行った際に、「売却金額(円)－(売却株数×取得単価)」が決済損益となります。
この取引後の取得金額合計(円)は、「保有株数残り×取得単価」となります。そして新たな取得単価を「取得金額合計(円)÷保有株数」で算出します。
参考ページ：No.1466 同一銘柄の株式等を2回以上にわたって購入している場合の取得費

以下は私の作成したテンプレートの画像です。

銘柄ごとに取引をまとめ、その中で取引を時系列に記載します。空売りと買建ての両建てが可能な証券会社の場合（Firstrade証券など）で、実際に両建てを行った場合は空売りと買建ては別銘柄としてまとめます。IB証券は両建てができないので別けません。
列として「銘柄名」「取引日」「数量」「約定代金」「手数料」「約定代金(円)」「手数料(円)」「その他の金額(円)」「合計金額(円)」「数量残」「建て価額残(円)」「平均建て単価(円)」「決済」「建て価額(円)」「決済損益(円)」「備考」というようなものを作っています。
前半の「銘柄名」～「手数料」および「その他の金額(円)」に手動で入力します。それぞれの列について説明します。

銘柄名
参考画像のように、銘柄名はその銘柄の欄の一行目、年度内初めの取引または期首残高（前年から持ち越された分）が記載された行のみに記載するようにしています。同じ銘柄の欄の２行目以降には銘柄名は記載しません。私の数式ではその銘柄における最初の取引または期首残高かを判定するのに、同じ行に銘柄名の記載があるかどうかを判定基準にしているためです。別の判定方法を用いる場合は、この辺りは自由にして良いです。
→2022-02-13追記：この方法に加え、銘柄名を全行に記載しても判定できる方法に変更しました。
取引日
西暦を含めて日付を入力します。今年の場合は西暦は省略しても表計算ソフトが自動で補完してくれます。株式については基本は受け渡し日基準なのですが、約定日基準も選択できます。海外証券会社のステートメントの日付に合わせます。IB証券は約定日基準、Firstradeは受け渡し日（決済日）基準になっているようです。受け渡し日（決済日）基準の場合は、この列の名称も「決済日」に変えた方が良いかもしれません。
数量
買いはプラス、売りはマイナスの符号で取引株数を記載します。新規か決済かで列を分けても良いかと思いましたが、現状では一緒にしています。
約定代金
元々の通貨での約定代金を入力します。収入はプラス、支出はマイナスです。
手数料
元々の通貨での手数料を入力します。支出なのでマイナス表記となります。
約定代金(円)
ここでvlookup関数を使い、「約定代金」を円に直します。以下の数式を入力します。
= 約定代金セル * VLOOKUP( 取引日セル, 為替レート表の範囲, 為替レート表内でのTTMまたはTTB/TTS列を示す列番号 ) 参考画像での行3の約定代金(円)セルの数式は
=$D3*VLOOKUP($B3,$為替レート表.$A$2:$E$500,IF($D3<0,3,4)) となっています。
下線部は為替レート表の範囲です。この例では500行までを指定範囲にしていますが、これは適当に多めを指定範囲にしているだけです。
ここでは「為替レート表」という名称の別シートを参照していますが、この別シート参照の式はLibre Officeのもので、エクセルと異なります。LibreOfficeでは「シート名.セル名」ですが、エクセルでは「シート名!セル名」となります。
為替レート表の指定範囲は以下のようになります。指定範囲の一列目を日付列にし、列項目名は含めません。
Photo_20210511120401

為替レートについてTTMを用いる場合は、TTM列を表す列番号を入力します（上記画像の場合、5）。
TTBおよびTTSを用いる場合は条件式としてIF関数を用います↓
IF( 約定代金セル<0, TTS列の番号 ,TTB列の番号 ) これで、約定代金が0未満（支出の場合）であればTTS列の番号を示し、そうで無い場合（収入の場合）はTTB列の番号を示します。

ここではvlookup関数で指定している値は3つですが、４つ目の値として検索の型というのがあります。これは省略またはTrueまたは1にします。このようにすれば、参照する取引日が為替レート表に存在しない場合（休日などで）はその直前の日付の為替レートを返してくれます。この方法を利用するには、為替レート表内の日付は上から古い順に並んでいる必要があります。

また、$が前につく列文字・行番号・シート名は絶対参照となり参照先が固定されます。列文字のみ固定しておけば、セルを横に連続適用（オートフィル）またはコピー＆ペーストしても数式内のセル参照が維持され、縦に連続適用またはコピー＆ペーストした際は行番号が追従するため便利です。参照する為替レート表は全固定にします。

私のテンプレートを使用する場合でvlookup関数内の為替レート表範囲を変更する場合は、「検索と置換」機能を利用すると便利です。以下はLibreOfficeのものですがExcelにも同等のものがあります。
2_20210718124201
標準では「$為替レート表.$A$2:$E$500」（エクセルでは「$為替レート表!$A$2:$E$500」）となっていますのでこれを検索し、置換先に新しい範囲を入力します。「すべてのシート」にチェックを入れ、「検索場所」を「数式」にします。置換処理後は正常に置換されているか確認してください。
また、LibreOfficeのバグなのか数式内に「$為替レート表.$A$2:$E$500」と入力してもいつの間にか「$為替レート表.$A$2:$為替レート表.$E$500」のように無駄に長い表記に変わっていることがありましたので、ご注意ください。

手数料(円)
手数料セルの値を日本円に変換します。上記の数式の、約定代金セルの部分を手数料セルに置き換えます。為替レートについて、手数料は約定代金と合算して受け渡しされるものなので、約定代金がTTBの場合（空売りの場合）に手数料がTTSということは出来ないと思います。そのため個人的に合理的だと思うTTMを採用しています。私の参考シート内では行3は以下になっています。
=$E3*VLOOKUP($B3,$為替レート表.$A$2:$E$500,5)
その他の金額(円)
取引金額に算入するその他の金額が発生することがありますので、この項目を設けています。株式ポジションがオプションの権利行使によって取得したものである場合は、当該オプション代金をここに記載します。金利や貸株料については、ここに記載するのではなく別に計算したほうが良いかもしれません。
合計金額(円)
約定代金(円)＋手数料(円)＋その他の金額(円)を示します。数式はそのまま各セルを足したものです。
数量残
取引後の保有株数を示します。ここから数式が少し複雑になっています。
まず、その銘柄の一行目かどうかを判定し、その銘柄の一行目で無い場合、一行上の数量残セルと今回取引された数量を足します。一行目の場合はそのまま今回取引された数量を数量残とします。
一行上のセル参照についてですが、そのまま指定すると、行の挿入や削除に伴ってセル参照が狂います。なのでそのような操作があっても常に一行上のセルを参照できるようにOFFSET関数を用います。基準セルの一行上のセルを参照する式は以下になります。
OFFSET(基準セル, -1, 0)そして、その銘柄の一行目では無い判定を行う条件式は以下にしています。OR( TRIM(銘柄名セル)="", TRIM(銘柄名セル)=TRIM(一行上の銘柄名セル) )この条件式は、銘柄名をその銘柄の範囲の一行目にのみ記載する方法および全行に記載する方法に両対応した条件式となります（2022-02-13にこの方式へ変更）。もし銘柄名をその銘柄の範囲の一行目にのみ記載する方法を取る場合はこのOR関数内の一番目の引数のみを条件式にして良いですし（2022-02-13以前の方法）、全行に銘柄名を記載するのであれば二番目の引数のみを条件式にして良いです。

ここまでが基本なのですが、私の方法ではこれにエラー判定の条件文を加えています。ドテン注文という、一つの買い取引で売り建玉を決済すると同時に新規買建を行ったり、その逆を行ったりするものがあるのですが、このような取引を記載しようとすると、数式がもっと複雑なことになるのでこれを防ぐためにエラー判定の条件文を加えています。ドテン注文がある場合は、決済取引と新規取引に分けて記載します。手数料等も分けます。

数式は以下となります。IF関数では、括弧内（引数）の1番目に「条件判定式」、2番目に「条件がTrueの時に示す値」、3番目に「条件がFalseの時に示す値」を記述します。分かりやすいように数式の一階層目を整形しています。
= IF(
  その銘柄の一行目ではない条件式, 
  IF( (数量セル+一行上の数量残セル)*数量残の一つ上のセル<0, "数量エラー", 一行上の数量残セル ), 
  0
) + 数量セル
エラー判定式については、今回の数量残と前回の数量残の符号が逆の場合にエラーにするため、「今回の数量残×前回の数量残<0」の場合にエラーにするようにしました。
私の参考シート内では行3の数量残セルは以下になっています。
=IF(OR(TRIM($A3)="",TRIM($A3)=TRIM(OFFSET($A3,-1,0))),IF(($C3+OFFSET($J3,-1,0))*OFFSET($J3,-1,0)<0,"数量エラー",OFFSET($J3,-1,0)),0)+$C3
建て価額残(円)
各取引の建て価額を合計した残高(円)を示します。「建て価額」とはポジションの取得価額のことであり、新規取引時の「合計金額(円)」になります。つまりこの列では現在保有しているポジションの取得価額合計を示します。
※ポジションとは、株式などを買い（ロング）または空売り（ショート）して未決済のものを言います。株式を購入した場合は単に株式の取得・保有と言えば良いですが、空売りした株式については株式を取得・保有しているとは言えません。しかしポジションという言葉を使えば、空売りしたものについてもポジションを取得・保有していると言えます。
まずその行の取引が新規注文か決済注文かで数式が異なるため、それを判断する条件式を入れています。前回の数量残と今回の取引数量の符号が一致しない時は決済注文だと判断します。そのために、両者を掛け合わせて0以上であれば新規注文だと判断しています。
それ以前に、この行の取引がその銘柄の初めの場合は新規注文になります。
新規取引かつその銘柄の初めの取引の場合は、合計金額(円)が建て価額残(円)となります。
新規取引で前回取引がある場合は、前回の建て価額残(円)＋今回の合計金額(円)となります。
決済取引の場合は、平均建て単価×数量残となります。
数式は以下です。
= IF(
  その銘柄の一行目ではない条件式, 
  IF( 一行上の数量残セル*数量セル>=0, 一行上の建て価額残(円)セル+合計金額(円)セル, 数量残セル*平均建て単価セル ), 
  合計金額(円)セル
)
私の例では、以下になります。
=IF(OR(TRIM($A3)="",TRIM($A3)=TRIM(OFFSET($A3,-1,0))),IF(OFFSET($J3,-1,0)*$C3>=0,OFFSET($K3,-1,0)+$I3,$J3*OFFSET($L3,-1,0)),$I3)
平均建て単価
建て価額残(円)を数量残で割ったものです。取引後の値となります。そのままだと数量残が0の場合はエラーとなるため、数量残が0の場合は0を表示する条件式を入れています。また、株式の取得にかかる平均取得単価の計算時には端数の切り上げをするという通達があるため（1単位当たりの取得価額の端数処理）、ここではINT関数を使用して切り上げを行っています。オプションの場合でもこれを適用して良いかと思います。数式は以下となります。
= IF(
  数量残セル = 0, 
  0, 
  INT(建て価額残(円)セル/数量残セル)
)
私の例では以下になります。
=IF($J3=0,0,INT($K3/$J3))
決済
これは分かりやすいように決済取引の場合に○を表示するものです。後ほど集計時に決済取引のみをフィルターで絞り込むのにも使います。数式は以下です。
= IF(
  その銘柄の一行目ではない条件式, 
  IF( 一行上の数量残セル*数量セル>=0, "", "○" ), 
  ""
)
私の例では以下です。
=IF(OR(TRIM($A3)="",TRIM($A3)=TRIM(OFFSET($A3,-1,0))),IF(OFFSET($J3,-1,0)*$C3>=0,"","○"),"")
建て価額(円)
決済された株式にかかる、建て価額（ポジション取得価額）を示します。決済の場合に「-1×取引数量×取引前の平均建て単価」を示します。数式は以下です。
= IF( 決済セル="○", -数量セル*一行上の平均建て単価セル, "" ) この式中の○について、決済セルの○と同じものであることを確認してください。記号と漢数字で異なります。
私の例では以下です。
=IF($M3="○",-$C3*OFFSET($L3,-1,0),"")
決済損益(円)
決済された株式にかかる損益(円)を表示します。決済の場合に合計金額(円)と建て価額(円)を足したものを表示します。数式は以下です。
= IF( 決済セル="○", 合計金額(円)セル+建て価額(円)セル, "" ) 私の例では以下です。
=IF($M3="○",$I3+$N3,"")

これで一通りテンプレートは完成しました。
前半の「銘柄名」～「手数料」および「その他の金額(円)」を手動で入力し、この他の列は自動入力となります。自動入力の列は全ての行にオートフィルで一括適用できます。
オートフィルは、選択したセル範囲の右下の黒い点をドラッグすることで、ドラッグ先に自動入力できる機能です。以下のようになります。
※「その他の金額(円)」が空白なので、これもまとめて選択しています。

しかし期首残高（前年から持ち越された分）がある場合は、その銘柄の初めの行の「数量残」「建て価額残(円)」「平均建て単価(円)」に手動で入力し、そこには数式は入れません。その他の列には入力不要です。

オプションについて、権利行使によって原資産の受け渡しが発生するタイプのものについて権利行使が行われた際は、オプションの取引明細上では便宜的に約定代金0の決済取引を記入し、その決済損益についてはそこから削除して、権利行使によって取得した原資産ポジションの建て価額に算入（「その他の金額(円)」に転記）します。
また、便宜的に決済取引にした行については「決済」セルの「○」も削除します。
権利行使によって差金決済で終了するタイプのものについては、差金決済損益を約定代金とした決済取引を記入すれば良いと思います。

決済損益合計などを計算する時はSUM関数を使います。「=SUM(合計したいセルの範囲)」で表せます。
株式の場合、取得費の合計と収入金額（売却金額）の合計を確定申告書に記載する必要がありますが、これらはフィルター機能を使って計算できます。
フィルターで「決済セル=○」かつ「合計金額(円)セル>0」で絞った結果の合計金額(円)と、「決済セル=○」かつ「建て価額(円)セル>0」で絞った結果の建て価額(円)を足した合計が収入金額合計となります。
取得費についても同じ要領で、「決済セル=○」かつ「合計金額(円)セル<0」で絞った結果の合計金額(円)と、「決済セル=○」かつ「建て価額(円)セル<0」で絞った結果の建て価額(円)を足した合計が取得費合計となります。
フィルター機能以外ではSUMIFS関数やDSUM関数でも算出できます。
テンプレートでは「集計」シート内でDSUM関数を用いて算出しています。
ただし確定申告書には「譲渡のための委託手数料」を記載する項目があり、つまり売却時の手数料を別に申告するようになっているのですが、空売り取引があるとその計算は複雑なことになってしまうため、この項目は使わないで良いと思います。税額の計算には影響しません。
払った貸株料や金利などは、必要経費として記載します。
確定申告書に記入する際はマイナスの符号は除きます。
オプションの場合は、決済損益を合計したものを雑所得（総合課税）として申告するだけで良いと思いますが、決済時の手数料について経費として分けて申告することも出来ます。
※オプションの場合も株式と同じように収入金額合計と取得費合計を算出し、収入金額合計をそのまま収入金額として、取得費合計を経費として申告するのがより正しい方法かもしれません。

外貨を円貨換算した際は端数が出ますが、確定申告書に記載する数値に端数が出た際は納税者有利という原則に従って、収入の場合は切り捨て、支出の場合は切り上げにします。これは収入をプラス、支出をマイナスとしているのであればINT関数で実現できます。

上場株式の取引明細に関しては、確定申告書に添付します（提出用紙である「株式等に係る譲渡所得等の金額の計算明細書」内に全て記載するのであれば別途添付は不要）。別途添付する場合で、e-Taxで申告する場合もこの取引明細は別途印刷して提出する必要があります。
印刷するにあたり、印刷する際の書式は「書式」→「ページスタイル」で変更できます。

デリバティブ（差金決済）の取引明細

差金決済方式による先物・オプション・FX・CFDに関しては決済時の取引のみ記載すれば良く、簡単です。
上記、株式およびオプション（現物取引）の取引明細の中の、「銘柄名」～「合計金額(円)」列を転用し、「取引日」の名称を「決済日」、「約定代金」の名称を「決済損益」に直せば良いと思います。「その他の金額(円)」が必要かどうかは分かりません。
申告の際には、経費である「手数料(円)」と、収入である「決済損益(円)」に分けて申告すると良いと思います。

以下は私のテンプレートの画像です。
Photo_20210710104101

または、証券会社の取引明細をCSVなどでダウンロードし、それを表計算ソフトにコピー＆ペーストまたはシートとして挿入し、そこに数式を加えて計算する方法も出来ると思います。

配当の明細

株式の配当に関しては、確定申告では決められた書式に記載することになりますが、そのための元となる明細を作っておくと便利です。
私のテンプレートでは以下になります。
2_20210719022001

「配当額」には現地での源泉徴収税が課される前の配当額を記入します。
「現地源泉徴収税額」は、現地（外国）で課された税額です。
「差引収入額」は、「配当額」から「現地源泉徴収税額」を引いた、手取り収入です。
その後の列ではそれぞれの項目を円に直しています。
また、最後の「差引収入額(円)」はINT関数で納税者有利方向に切り捨てを行っています。
配当を申告する際は、この明細の「銘柄」「差引収入額(円)」を用います。
確定申告書作成コーナーを利用する時は、配当集計フォームにこれらをコピーすることができます。
この明細は外国税額控除に利用することもできます。

IB証券の場合、アクティビティステートメントに配当および源泉徴収税の欄がありますが、これは使わずに税金レポートにあるDividend Reportを使用したほうが良いです。こちらのほうが最終的で正確なレポートになっています。ただし発行されるのが少し遅く、2月半ばに発行されています。
またこのレポートには、配当金の他に配当金相当額（Payment in Lieu）が記載されていることがあります。配当金相当額には、例えば空売り株式に関して支払う配当金相当額（日本の信用取引における配当落調整額に相当）や、貸し出した株式に関して受け取る配当金相当額があるようです。
空売り株式にかかる配当金相当額については配当所得として申告するのでは無く、株式の譲渡に係る所得（損失）または雑所得の損失として申告すると思われます（要確認）。貸し出した株式についての配当金相当額は雑所得のように思います（要確認）。ただあまり細かいことを気にしていても大変なので、こういう部分は適当で良いかもしれません。税務署に聞くにしても色々と大変です。
またIBのマージン口座では、保有株式を貸し出す手続きを行っていなくてもPayment in Lieuに配当が記載されていることがあったのですが、IBのマージン口座の仕様として自動で貸し出されることがあるそうです。

コピー＆ペーストで一括入力する方法

取引数が多く無ければ取引明細書に手入力していけば良いですが、取引数が多い場合は証券会社からダウンロードしたCSVファイルを元にコピー＆ペーストすることも出来るかと思います。Firstrade証券の場合は少し難しく、IB証券の場合は割と簡単のように思います。
ここでは例として株式および現物オプションの取引明細をコピペで作成する方法を解説します。加えてIB証券の配当明細をコピペで作成する方法も解説します。
また、株式および現物オプションの取引明細について、前年の期末残高データを当年の期首残高へ転記する方法、そしてそれを当年の取引データと組み合わせる方法も後半で紹介します。
また、私の方法では表計算ソフト内で正規表現の置換機能を使用する部分があります。Excelだと標準では正規表現の置換機能が使えないようで、アドインをインストールする等の必要があるようです。LibreOfficeでは標準で使えますので、ここではそれを使っています。

目次

IB証券の取引データ作成方法

IB証券のアクティビティステートメントでは標準で銘柄ごとに時系列で取引がまとめられているため、そこは整形が必要ありません。※もし銘柄ごとにまとめられていない場合は、カスタムステートメントで銘柄ごとに取引をまとめる選択ができます。

年間アクティビティステートメントをCSVでダウンロードします。このCSVファイルをLibreOfficeで開くと、列幅がとても開いており見辛かったので、全選択して列アルファベット（A,B,C...）の部分を右クリック→「列幅」で列幅を調整します。
ここから、必要な行をコピーして新しいファイルに貼り付けます。ここでは例として取引行のみコピーします。諸経費の明細等もコピペする場合はそちらも行うことができます。

次に、合計行が不要なのでフィルターで除去します。Header列に「SubTotal」「Total」とある行を除去します。以下のようにこれらの行をフィルターします。

そして、フィルター結果の行を全て選択し（表を全選択した後、見出し行のみ「Ctrl+クリック」で選択解除）、左の行番号の表記部分を右クリック→「行の削除」を行います。

そしてフィルターを解除します。
※フィルターを解除するには、フィルターボタンが現在押されていることを確認して再度そこをクリックすれば良いですが、時々フィルターボタンが押されておらず、再度クリックしてもフィルターが再度かかってしまう時があります。そういう時は、そのままフィルターをかけてから再度フィルターボタンを押してフィルターを解除すれば上手く行くと思います。

また、不要な列も削除しておけば見やすくなります。「約定価格」「終値」「取得費」「実現損益」「評価損益」が不要です。
次に、「日時」列についてですがIB証券の場合「2021-01-04, 09:30:00」というような形式で書かれています。このままだと表計算ソフトに日時として認識してもらえない可能性が高いため、コンマを削除して「2021-01-04 09:30:00」という形式にします。そのためには日時列を選択し、「検索と置換」で「,」を空文字に置き換えます。

もしこれでも日時として認識しないようであれば、新しい列を１つ作ってVALUE関数で日時に変換します。

また、オプションの権利行使や権利割り当てがある場合は、備考列などにその旨を記載したほうが分かりやすいと思います。備考列を一番右に作ります。
権利行使・権利割り当ては、コード列の値で判別します。
「A」が権利割り当て、「Ex」または「AEx」が権利行使のようです。コードが複数ある場合は、「;」で区切られています。
まずコード列に「A」のサインがある行をフィルターします。

このように、リストの候補から該当するものを選ぶのが早いと思います。絞ったら、備考列を「権利割り当て」の文言で埋めます。そして次は同じように権利行使のサインである「Ex」または「AEx」がある行でフィルターし、備考列を「権利行使」の文言で埋めます。そしてフィルターを解除します。

これでコピー＆ペーストの元となる明細が完成です。

前年からの繰り越し分がある銘柄の場合は、その期首残高行の下から貼り付けます。
また、私の取引明細書では銘柄名はその銘柄の明細の一行目にのみ記載する仕様ですのでご注意ください（2022-02-13追記：銘柄名を全行に記載しても良い仕様に変更）。
これらについてもコピペで行う場合は、後半の項を参照ください。

IB証券の配当データ作成方法

IB証券の配当については別途、「Dividend Report」が発行されているのでこれを基にコピペの元となるデータを作成します。これをCSVでダウンロードし、開きます。
以下のように「DividendDetail」の範囲の見出し行を選択してからフィルター機能をオンにします。

まず「DividendDetail」列で「DividendDetail」のみに絞ります。
次に「DataDiscriminator」列で「Summary」のみに絞ります。
これで必要な行のみに絞れたと思います。

次に日付についてです。日付は「ReportDate」と「ExDate」の２種類があり、どちらを採用すべきかは不明ですがここでは早い日付の「ExDate」を採用することにします。
この日付は「20210610」のような形式で書かれており、このままでは表計算ソフトに日付形式と認識してもらえないので、「検索と置換」で置き換えます。ここでは正規表現を用います。
この範囲を選択し、「検索と置換」ウィザードを立ち上げ、検索対象は「(\d{4})(\d{2})(\d{2})」とし、置換先は「$1-$2-$3」とします。「正規表現」にチェックを入れます。

検索対象には(4桁の数字)(2桁の数字)(2桁の数字)を指定しており、()で囲まれた部分はそれぞれ$1~$3という名前の変数に入ります。そして置換先に$1~$3をハイフン区切りで並べています。

これでコピペの元となるデータが完成しました。
「Symbol」（銘柄）、「ExDate」（日付）、「Gross」（配当額）、「Withhold」（現地源泉徴収税額）各列の内容を上場株式の配当テンプレートへコピペします。
貼り付ける際は「形式を選択して貼り付け」ウィザードから「値のみ」を選択すると良いと思います。
また、仮に通貨の種類が複数ある場合（Currency列に複数の通貨がある場合）はそれぞれの通貨の為替レートで日本円に変換しなければならないのでご注意ください。

Firstrade証券の取引データ作成方法

Firstradeの場合は結構手間がかかります。
Firstradeでは、Account Historyから期間を指定してCSVファイルがダウンロードできます。

このファイルにはアカウント内の全ての履歴が記載されているため、必要なものを抜き出す必要があります。

不要な行をフィルターで除去

まず、どの明細作成にも不要だと思われるものが、Description列に「MARK TO MARKET」「XFER CASH TO MARGIN」「XFER MARGIN TO CASH」とあるものです。これらの行が何を表しているのかは正確には分かりませんが、「MARK TO MARKET」は時価評価（値洗い）のこと、「XFER...」についてはXFERとはTransferのことのようなのでマージン勘定とキャッシュ勘定間の資金の移行のことだと思われます。これらは私が株式のショートポジションを保有していたことで発生したものかもしれませんが良くは分かりません。
他にも、不要そうな行があればここで除去して良いです。
それには見出し行を選択してからフィルター機能をオンにします。以下のようにDescription列のフィルターにて、これらを除去します。

Symbol列の余計な空白を除去

「Symbol」列の各行について、余計な空白が入っていましたので、これを除去します。Symbol列を選択し、「編集」から「検索と置換」ウィザードを立ち上げます。ここでは正規表現で置換してみます。セル内の文字の最後尾から、空白文字が続く限りを検索して空文字に置換します。以下のように検索欄に「\s+$」と入力します。単純に半角スペースを検索して空文字に置換しても良いですが、それだと文字と文字の間にあるスペースまで除去されてしまいます。

そして、この結果の表全体をコピーして、新しいファイルに貼り付け、そちらで作業を継続します。

Amount、Commission、Fee各列について

AmountはCommissionとFeeが引かれた後の最終的な金額を表しているようです。Commissionは証券会社が自社の取り分として徴収する手数料で、Feeはそれ以外の手数料だと思われます。
取引明細書には、手数料が引かれる前の約定代金を記載しますので、約定代金列を新たに作ります。Amount列の右隣りに新しい列を挿入し、列名は「ContractPrice」などとします。また、手数料列が２つに分かれているのも不便なので一つにまとめます。
ContractPrice列の右隣りに新しく「TotalFee」などの名称の列を作ります。また、CommissionとFeeはプラス表記になっているのでTotalFee列でマイナス表記に変えます。TotalFeeセルの数式は「= -1 * (Commissionセル + Feeセル)」となり、
ContractPrice列の数式は「= Amountセル - TotalFeeセル」になります。
青文字のところはセル参照となり、Commissionセルであれば同じ行のCommission列のセルを参照します。
これらの数式を全行にオートフィルで適用します。オートフィルでは、選択したセルの右下の黒い点を下にドラッグすることで下の行に連続適用できますが、隣接する列が入力されている前提であれば黒い点をダブルクリックすることで即時にオートフィルが可能です。

TradeDate、SettledDate各列について

「TradeDate」は取引日、「SettledDate」は決済日（受け渡し日）です。税制上はどちらかを選択できますが、確定申告で提出するFirstradeのステートメント（月間ステートメント）は受け渡し日基準で作成されていますので、ここでは「SettledDate」を使用するのが良いと思います。
この列について、日付として認識されず文字列として認識されている可能性があります。日付表示のボタンを複数回クリックしてみてSettledDateセルに反応が無ければ日付として認識されていません。または、上部の数式表示欄で先頭に「'」が付いていると文字列として認識されています。以下のような状況です。※以下の画像ではTradeDateを選択していますがSettledDateを選択ください。

日付として認識されていない場合は、SettledDate列の隣に新しい列を挿入します。セルには「=VALUE(SettledDateセル)」と入れ、これを全行に適用します。これで新しい列では日付として認識されるはずです。
新しい列全体を選択し、一行目のセル（列名セル）だけ「Ctrl+クリック」で選択解除し、選択範囲をコピーして、SettledDate列の２行目のセルで右クリック→「形式を指定して貼り付け」→「形式を指定して貼り付け」からウィザードを立ち上げ、「日付と時刻」にチェックを入れて貼り付けます。

また、このデータはTradeDate基準で時系列に並んでいるため、SettledDate基準で時系列に並ぶように並び替える必要があります。それにはフィルターでSettledDate列を「昇順でソート」します。

取引行を抽出

ここから、ここでは例として株式や株式オプションの取引行を抽出していきます。
まずAction列についてBUYかSELLとあるものについては全てこれらの取引行だと思います。
他にも、Action列にOtherとあるものでも取引に該当するものがありました。配当の自動再投資（DRIP）の取引と、オプションの権利割り当ての行です。オプションの権利行使についても、私は行った経験がありませんがこれもOtherとして記載されるのではないかと思います。
他にも、取引に該当するものが無いかチェックしてみてください。
配当の自動再投資の行には、Descriptionに「REIN」という言葉がありました。オプションの権利割り当ての行には、Descriptionに「ASSIGNED」という言葉がありました。これらを基にフィルターで絞り込みます。今回は複数条件で検索します。LibreOfficeの場合は「標準フィルター」を使用します。以下のようになります。

これで取引行だけに絞れました。このフィルター結果もコピーして、別シートに貼り付けてそちらで作業を継続します。

オプションの権利行使・割り当てがある場合

もしオプションの権利行使または権利割り当てがある場合は、ここで備考欄を作り、そこにその旨を記載したほうが良いと思います。適当な場所に備考列を作ります。
権利割り当てがある場合は、標準フィルターにて、Description列にASSIGNEDという言葉が含まれている場合で絞ります。そして備考列を「権利割り当て」という文言で埋めます。
権利行使がある場合については、私はDescription列にどういう言葉が記載されるのかは分かりませんが、同じようにフィルターして、備考列を「権利行使」という文言で埋めます。
そしてフィルターを解除します。
※フィルターを解除するには、フィルターボタンが現在押されていることを確認して再度そこをクリックすれば良いですが、時々フィルターボタンが押されておらず、再度クリックしてもフィルターが再度かかってしまう時があります。そういう時は、そのままフィルターをかけてから再度フィルターボタンを押してフィルターを解除すれば上手く行くと思います。

株式取引とオプション取引を分ける

次に、株式とオプションの取引両方がある場合はそれらを分ける必要があります。
また、オプションの行では以下のようにSymbol列が空白になっていることに気づきます。替わりにDescription列にオプションの名称が入っています。

ここは補完しなければならないのですが、取り敢えずはSymbol列が空白かどうかで株式とオプションを分けることが出来そうです。フィルターでSymbol列を空白と空白以外で絞り込み、それぞれの結果を別々のシートにコピーします。

株式のシート

まず株式のシートについて整形をしていきます。もし、同じ銘柄のロングポジションとショートポジションの両建てを行ったことが無ければ、それらを別銘柄として分ける必要は無いと思うので簡単です。
フィルター機能を使い、Symbol列を「昇順でソート」するだけで銘柄ごとに取引がまとまります。これで完成です。

次に、ロングとショートの両建て取引がある場合の方法を説明します。この場合はそれぞれを別銘柄として取引明細書に記載する必要があります。
ショートポジションの株式取引についてはDescription行に「SHORT.」という文言があるので判別できます。ショートカバーも「COVER SHORT.」となっています。
今回は、ショートポジションの取引の場合に、銘柄名の後ろに「(short pos.)」と入れるようにしてみます。
新しい列を、Symbol列の隣りに作成します。
列名は「NewSymbol」などとし、セルには

= Symbolセル & IF(
      COUNTIF( Descriptionセル, "*SHORT.*" ) = 0,
      "",
      "  (short pos.)"
  )

と入力します。この数式を全ての行にオートフィルで適用します。
COUNTIF関数は、参照したセル範囲（第一引数）が指定した条件（第二引数）に合致した時に、合致したセル数を返します。条件として"*SHORT.*"と指定しており、「*」はワイルドカードと言って、0個以上の任意の文字を意味します。つまりこのCOUNTIF関数では、ショートポジションの取引の場合に1を返し、それ以外に0を返します。
そしてこの親のIF関数では、COUNTIF関数 = 0の時に空文字を返し、それ以外の時に「  (short pos.)」の文字を返します。そしてこのIF関数を、Symbolセルと連結しています。

フィルター機能を使い、NewSymbol列を「昇順でソート」します。これで銘柄ごとに取引がまとまり完成です。

オプションのシート

次に、オプションのシートについて整形していきます。
Description列からオプションの名称を抽出し、Symbol列に記入する必要があります。
まずSymbol列にDescription列の内容を丸ごとコピーします。Description列全体を選択し、１行目の列名セルだけ「Ctrl+クリック」で選択解除します。そして選択範囲をコピーしてSymbol列の２行目のセルに貼り付けます。
そのままSymbol列の内容が選択された状態で「検索と置換」ウィザードを立ち上げます。
オプションの名称は「PUT  SLV    10/19/18    14.50」というように記述されていますので、これを検索します。また、文字の間の空白も空きすぎていると思われるので調整します。ここは正規表現を使います。
以下結構難解ですが、検索文字列は
.*(PUT|CALL)\s+(\w+)\s+(\d{2}/\d{2}/\d{2})\s+(\d+(?:\.\d+)?).*とし、置換先文字列は「$1 $2 $3 $4」とします。

検索文字列の正規表現では４つの()括弧が順番に、(PUTまたはCALL)、(銘柄名)、(日付)、(権利行使価格)に対応しています（内部が「?:」で始まる括弧は除く）。注意として、日付は(\d{2}/\d{2}/\d{2})としていますが、日付の書式が仮に将来変わるとこのままでは対応できないので、大雑把に(\S+)でも良いかもしれません。
置換先の「$1 $2 $3 $4」では、４つの()括弧で取得した内容を半角スペース区切りで並べています。もし文字間の余白がもっと欲しいなら「$1  $2  $3  $4」のようにできます。

また、オプションについても全く同一の銘柄でロングとショートの両建てをしたことがある場合はそれぞれに分ける必要がありますが、そうで無い場合はSymbol列を昇順でソートして完成です。

両建てがある場合について解説します。
オプションに関してはDescription列に、「OPEN CONTRACT」と「CLOSING CONTRACT」の記述があります。権利割り当ての場合は「ASSIGNED」の記述があります。そのため、ショートポジションの取引は以下のいずれかのケースです。
・Quantity列がマイナス（売りを表す）で、Description列にOPEN CONTRACTがある
・Quantity列がプラスで、Description列にCLOSING CONTRACTがある
・Description列にASSIGNEDがある

このケースに当てはまる場合に、Symbolセルに「  (short pos.)」という文言を加えることにします。それには株式シートの時と同じように数式を用いても良いですが、今回は条件が少し複雑なので、フィルター機能でショートポジションの取引を絞ってから、Symbol列の内容を書き換える方法を説明します。
見出し行全体を選択してからフィルター機能で「標準フィルター」ウィザードを立ち上げます。以下のように入力します。ただし最後の条件を画像で写せなかったのでそれも追加してください。最後の条件は、演算子「または」フィールド名「Description」条件「...を含む」値「ASSIGNED」です。

演算子（かつ・または）の優先順位が分かり辛いですが、基本的には「かつ」が優先されると思いますので恐らくこれで大丈夫だと思われます。
次に、Symbol列全体を選択し、一行目の列名セルのみ選択を解除してから「検索と置換」ウィザードを立ち上げます。以下のように検索文字列を「.+」とし、置換先を「$0  (short pos.)」とします。

そしてSymbol列を昇順でソートすれば完成です。

データが完成

これで、株式およびオプションについて、取引明細書に転記する元となるデータが完成しました。取引明細書に転記する列は「Symbol（またはNewSymbol）」（銘柄）、「Quantity」（数量）、「SettledDate」（決済日）、「ContractPrice」（約定代金）、「TotalFee」（手数料）、「備考」（備考）となります。
前年からの繰り越し分がある銘柄の場合は、その期首残高行の下から貼り付けます。
また、私の取引明細書では銘柄名はその銘柄の明細の一行目にのみ記載する仕様ですのでご注意ください（2022-02-13追記：銘柄名を全行に記載しても良い仕様に変更）。
これらについてもコピペで行う場合は、次からの項を参照ください。

前年の期末残高を当年の期首残高へ転記する（株式および現物オプション）

この記事で紹介した「株式およびオプション（現物取引）の取引明細」について、前年の期末残高を当年の期首残高へ一括転記する方法について解説します。ただし銘柄数が少ない場合は、手入力したほうが早いと思います。
差金決済のデリバティブに関しては転記の必要はありません。

まず前年の取引明細書から、期末残高行を抽出します。
全ての取引行に銘柄名を記載しない方法を取っている場合、全ての取引行に銘柄名を記載し直します。
そのために銘柄列の右隣りに、新しく列を作成します。数式は
= IF( TRIM(銘柄セル)="", 一つ上のセル, 銘柄セル )とし、全行に適用します。 これで全ての行に銘柄名が表示されたはずです。
以下、全行に銘柄名を記載した列のことを銘柄(新)と呼びます。

次に期末残高を判定します。
期末残高行の条件は以下を全て満たすことです。
・同行の銘柄(新)の内容が、一つ下の行の銘柄(新)の内容と異なる
・数量残が0より大きい

銘柄列の右隣りに新しく列を作り、数式は

= IF(
      AND( TRIM(銘柄(新)セル)<>TRIM(銘柄(新)セルの一つ下のセル), 数量残セル>0 ),
      "期末残高",
      ""
  )

とし、全行に適用します。これで、期末残高になる行には「期末残高」と表示されるはずです。

※この期末残高を表示する列およびそれに必要な銘柄(新)列は、もしかしたら取引明細書に標準で備え付けても良いかもしれません。その場合、数式の中で異なる行を参照する時はOFFSET関数を用います。一つ上のセル参照はOFFSET(基準セル, -1, 0)となり、一つ下のセル参照はOFFSET(基準セル, 1, 0)となります。

そして表をフィルターし、「期末残高」行を抽出します。

これで、当年の期首残高へ転記する元のデータが完成しました。
この表の、「銘柄(新)列」から「残高列」までの内容を選択してコピーします。

これを、当年の取引明細書（未入力のテンプレートファイル）へ貼り付けますが、当年の取引明細書にある数式を全部上書きして消してしまわないように、一行だけ残しておきます。以下のように銘柄列の内容の２行目で右クリック→「形式を指定して貼り付け」→「形式を指定して貼り付け」でウィザードを立ち上げ、「値のみ」をクリックします。

貼り付けたら、貼り付けたデータにおける「取引」各列（取引日～合計金額(円)）の内容を削除します。「銘柄」列および「残高」各列のみを残します。

これでデータの転記が完了しました。
ここに当年の取引を追加していきます。取引を追加したいところへ行を挿入し、数式については表の内容の一行目に残したものをコピペまたはオートフィルで用います。
または、次項に解説する方法で当年の取引データを一括で挿入できます。

期首残高データに取引データを挿入する（株式および現物オプション）

期首残高データに、当年の取引データを一括で挿入する方法を解説します。取引データの作成方法はIB証券の取引データ作成方法またはFirstrade証券の取引データ作成方法で解説しています。

まず、以下のように、期首残高データが記入されたエリアの下（必ず下）に、取引データを貼り付けます。この時、同じ銘柄であれば期首残高データの「銘柄」と取引データの「銘柄」の内容が全く同一であることを確認してください。貼り付ける際は、書式も一緒にコピーしないように「形式を選択して貼り付け」ウィザードにて「値のみ」を選択すると良いです。

次に、取引行について、取引明細テンプレートの数式を補完します。

そして以下のように期首残高データおよび取引データ全体に加えて上に一行含んだ範囲を選択し、フィルターをオンにします。一行目を列の項目行とします。

そして「銘柄」列を「昇順でソート」します。

これで期首残高データと取引データが組み合わさりました。
ただし、私の明細の仕様では、銘柄列について同じ銘柄名は複数記載しないため、そのように整形する必要があります（2022-02-13追記：銘柄名を全行に記載しても良い仕様に変更）。

銘柄列の隣に新しい列を作り、そこに以下の数式を入れ、全行に適用します。
= IF( 銘柄セル=一つ上の銘柄セル, "", 銘柄セル )この部分をコピーし、銘柄列の内容に上書きします。以下のように、貼り付ける範囲の一番上のセルで右クリック→「形式を指定して貼り付け」→「テキスト」を選びます。

そして新しく作った列を削除すれば完成です。完成したデータに不自然な点が無いかはご確認ください。

0 件のコメント:

コメントを投稿

QooQ