Google Spreadsheet で日付から為替を取得する
はじめに
・確定申告で ESPP や RSU の計算をするとき為替の値を効率的に取得する方法の検討メモです
・申告漏れや誤記載に気付けるよう値を残せることが重要
・日付を入力したら TTM が出てくるのが理想形だが、実現できていない（実現されている方、コメント頂けるとありがたいです）
お世話になるサイト
三菱UFJリサーチ&コンサルティング 外国為替相場情報
https://www.murc-kawasesouba.jp/fx/past/index.php
例：2024/02/01の為替相場
https://www.murc-kawasesouba.jp/fx/past/index.php?id=240201
TTM = (TTS+TTB)/2 で求められる。
ある日付の為替相場の取得方法
URLが https://www.murc-kawasesouba.jp/fx/past/index.php?id= + yymmdd となっていることに着目する
スプシ記載例
A1=https://www.murc-kawasesouba.jp/fx/past/index.php?id= A2=2024/02/01 A3=生成されたURL B3=TTS C3=TTB D3=(B3+C3/2)
日付フォーマットの変換
YYYY/MM/DD => yymmdd
=TEXT(A2,"yymmdd")
株書類では YYYY-MMM-DD の場合もあったが MM に変換する方法がわからず。String かも。
曜日の確認
=TEXT(A2,"ddd")
・もし土日の場合、為替相場の値がないためホーム画面へリダイレクトされる
・売買した日付が日曜だった場合、-2、土曜だった場合 -1 などを追加することで直近の平日とする
URL の生成
=$A$1&TEXT(A2,"yymmdd")
この URL をクリックして欲しい値を抜き出して利用するのが当面の運用方法
ボツ案1： IMPORTXML 関数で取得
一つのシートの取得に30秒以上かかる
スプシを開くたびに取得しにいくため値が残らない
TTS =IMPORTXML(C3,"//html/body/div[2]/div/table[1]/tbody/tr[2]/td[4]") TTB =IMPORTXML(C3,"/html/body/div[2]/div/table[1]/tbody/tr[2]/td[5]")
ボツ案2： GOOGLEFINANCE 関数で取得
TTS/TTB/TTM などは取得できなそう（レスポンスは早く、近似値をざっくり取得するなら一番簡単）
=INDEX(GOOGLEFINANCE("currency:USDJPY", "price",A2),2,2)
