Googleスプレッドシート＋ GoogleFinance を使う際の便利なテクニックの備忘録です。
今回は GoogleFinance を使って、任意の銘柄の指定期間の年率リターンを取得する計算式について説明します。
備忘録も兼ねていますので、計算過程等をわかりやすくするために式を細分化して計算しますが、最終的にはインラインで年率リターンを取得する計算式を作成します。
ですので、手っ取り早く計算式を知りたい場合は、「結論」を参照してください。
年率リターンの計算方法
まずは、そもそも「年率リターン」ってどうやって計算するのか？というのを説明します。
なお今回はキャピタルのみを対象とした年率リターンを算出することとします。
（本来であれば、配当や分配金などのインカムも含めて計算するのでするのですが、GoogleFinance でそこまで計算するのは難しいっぽいのでご容赦ください）
年率リターンの計算方法については、こちらの資料を参考にさせてもらいました。
上記リンクの資料では月足データを基にして年率リターンを計算していますが、GoogleFinance では月足データは取得できません（日足or週足）なので、今回は日足で年率リターンを求めます。
日付Xから日付Yまでの年率リターンを求めるとすると、
年率リターン = (日付Yの株価 ÷ 日付Xの株価) ^ (1 ÷ 日付Xから日付Yまでの期間を年数に換算したもの) - 1
年率リターンは1年間の損益変化量なので、期間内の株価の変化率を年率に平準化するってことですね。
例：マイクロソフトの 2010/1/4 から 2020/7/10 までの年率リターンを計算
例えば、マイクロソフト（MSFT）の 2010/1/4 から 2020/7/10 までの年率リターンを計算するとします。
株価は、2010/1/4 は 30.95 で、2020/7/10 は 213.67 です。(終値ベース)
まず、期間内の変化率は、 213.67 / 30.95 = 6.90371... なので約 690.37% ということになります。（すげー）
次に、2010/1/4 から 2020/7/10 までの期間を年数に換算します。
2010/1/4 から 2020/7/10 までの日数は 3840 日です。
年数に換算するために365日で割って、 3841 / 365 = 10.52... なので10.5年ということになります。
以上より、先ほどの計算式に当てはめると、
年率リターン = 6.9037 ^ (1 / 10.52) - 1 = 0.20147...
となるので、期間内の年率リターンは、約 20.15% であるということがわかりました。（すげー）
GoogleFinance で年率リターンを計算する
「日付Xから日付Yまでの年率リターンを求める」として、ステップを以下の４つに分解します。
- 日付X（開始日）の株価を取得する
- 日付Y（最終日）の株価を取得する
- 日付Xから日付Yまでの期間を年換算する
- 年率リターンの計算式に当てはめる
1. 日付X（開始日）の株価を取得する
現在の株価の取得ならば、 GOOGLEFINANCE(ティッカーコード, "close") でよいのですが、指定した日付の株価を取得するとなると工夫が必要です。
↑の記事でも書きましたが、以下のようにすると、日付Xの株価を取得できます。
=index(GOOGLEFINANCE(ティッカーコード, "close", 日付X),2,2)2. 日付Y（最終日）の株価を取得する
「1.」の場合と同じで、以下のようにして株価を取得します。
=index(GOOGLEFINANCE(ティッカーコード, "close", 日付Y),2,2)3. 日付Xから日付Yまでの期間を年換算する
日足データで計算しているので、365で割ります。
=(((日付X - 日付Y))/365)4. 年率リターンの計算式に当てはめる
// A1 セルに以下を記載
=index(GOOGLEFINANCE(ティッカーコード, "close", 日付X),2,2)
// A2 セルに以下を記載
=index(GOOGLEFINANCE(ティッカーコード, "close", 日付Y),2,2)
// A3 セルに以下を記載
=(((日付X - 日付Y))/365)
// A4 セルに以下を記載して、年率リターンを取得完了
=(A1 / A2) ^ (1 / A3) - 1[結論]年率リターンを取得するインライン計算式
以下の計算式で取得できます。
日付Xから日付Yまでの年率リターンを求めるとすると、
=(index(GOOGLEFINANCE(ティッカーコード, "close", 日付X),2,2)/index(GOOGLEFINANCE(ティッカーコード, "close", 日付Y),2,2))^(1/(((日付X-日付Y))/365))-1具体的に、マイクロソフト（MSFT）の 2010/1/4 から 2020/7/10 までの年率リターンを計算するとしたら、
=(index(GOOGLEFINANCE("MSFT", "close", "2020/7/10"),2,2)/index(GOOGLEFINANCE("MSFT", "close", "2010/1/4"),2,2))^(1/((("2020/7/10"-"2010/1/4"))/365))-1または、マイクロソフト（MSFT）の 2010/1/4 から現時点までの年率リターンを計算するとしたら、
=(GOOGLEFINANCE("MSFT", "price")/index(GOOGLEFINANCE("MSFT", "close", "2010/1/4"),2,2))^(1/(((TODAY()-"2010/1/4"))/365))-1補足
GoogleFinance の株価取得で遡れる期間
GoogleFinance で取得できるデータは、どうやら 1972/6/1 以降のものに限られるようです。
例えば IBM の株価を以下のように取得したとすると、
=GOOGLEFINANCE("IBM", "close", TODAY()-365*50, TODAY())本来であれば50年前の1970年7月ごろのデータが返ってきてほしいですが、上述の通り1972/6/1からのデータしか取得できませんでした。
上場来の年率リターンを取得する方法
上記の通り「1972/6/1以降の」という条件に限られますが、以下の通り記述することで、上場来の年率リターンを取得することができます。
=(GOOGLEFINANCE(ティッカーコード, "price")/index(GOOGLEFINANCE(ティッカーコード, "close", TODAY()-365*50, TODAY()),2,2))^(1/(((today()-index(GOOGLEFINANCE(ティッカーコード, "close", TODAY()-365*50, TODAY()),2,1))+1)/365))-1365*50 は適当です。例えば60年でも70年でも、上述の通り返ってくるのは1972/6/1以降なので同じです。
コメント