Firstradeのposition一覧から国外財産調書を作成するtips

　憂鬱な確定申告の時期である。

　最近の円安傾向のため、思ったよりFirstradeの日本円換算の資産が増加したので国外財産調書作成が必要になった。預貯金は残高をJPYへ変換するだけで良いのだが、証券類は取得価格と残高両方を記載しろとある。ややこしいのはDRIPで株式取得した分が増加する事である。DRIP毎に取得価格と現在価値をJPYへ変換する必要がある。

　筆者はケチなのでこの手の計算をgoogle spreadsheetで実行している。概要は以下の通り。

　Firstradeでpositionを表示させ、全ての領域をcopyしてspreadsheetにpasteする。

　別シートにみずほ銀行から外為rateのcsvデータをダウンロードしてnumbersで開いて日付とUSDの部分をgoogle spreadsheetへpasteする。日付列を「書式なしテキスト」に設定する。

　positionシートのPurchase列の日付→rateシートの日付列へvlook up、当該日のrateを取り出してUSDのTotal CostとMarket ValueをJPYへ変換する。

　ここで問題はFirstradeのPosition表示のPurchase Dateはアメリカ式カレンダー表示になっている事である。当該はMM/DD/YYYY形式になっている一方、みずほ銀行のrate csvはYYYY/MM/DD形式のため、そのままvlookupすると値が検索出来ない。

　そのため、MM/DD/YYYYのpurchase dateを次の方法でYYYY/MM/DDへ変換する。

　Purchase Date列を「書式なしテキスト」に設定する。

　当該列から以下の関数でYYYY/MM/DD表示列を生成する。

=IF(変換するPurchase Dateのセル="","",TO_DATE(regexreplace(変換するPurchase Dateのセル,"(\d{2})/(\d{2})/(\d{4})", "$3/$1/$2")))

　この関数により、当該セルが空白の場合（ティッカー単位の合計行）はそのまま空白、MM/DD/YYYY形式の日付はYYYY/MM/DDへ変換される。

　このYYYY/MM/DD表示列をvlookupすれば良い。

　

Read More