Googleスプレッドシート＋ GoogleFinance を使う際の便利なテクニックの備忘録です。
前回は指定した期間の年率リターンを取得する方法を説明しました。
今回は GoogleFinance を使って、任意の銘柄の指定期間の年率リスク（標準偏差）を取得する計算式について説明します。
備忘録も兼ねていますので、計算過程等をわかりやすくするために式を細分化して計算しますが、最終的にはインラインでリスクを取得する計算式を作成します。
ですので、手っ取り早く計算式を知りたい場合は、「結論」を参照してください。
年率リスクの計算方法
前回と同じように、まずは年率リスクの計算方法について説明します。
というか、私事ですがそもそも「リスク」が具体的に何者なのかを理解していなかったので、そこから残しておきます。
「リスク」とは？
リスクとは標準偏差のことなのですね。知りませんでした。
具体的には「毎日の変動率の標準偏差」を「年間の変動率の標準偏差」に換算したもが「年率リスク」ということになるみたいです。（日足データをベースにする場合）
なので、例えば年率リスクが 10% の場合は、（母集団が正規分布すると仮定する場合）１年間で約68%以内の確率で̟変動率が ±10% で収まるということになります。
また、同様に１年間で約95％以内の確率で変動率が ±20%に収まるということになります。
ちなみに、「母集団が正規分布すると仮定する場合」ということですが、試しにマイクロソフトの過去30年間の毎日の変動率を分布図で表してみると、以下のようになりました。
んー、何とも言えないですね。なんとなく正規分布しているようにも見えますし、テイルノイズが大きいようにも見えます。
計算方法
今回もこちらの資料を参考にさせてもらいました。
前回の年率リターンの計算方法の場合と同様、資料では月足データを基にして年率リスクを計算していますが、GoogleFinance では月足データは取得できません（日足or週足）なので、今回は日足で算出します。
日付Xから日付Yまでの年率リスクを求めるとすると、
年率リスク = (日付Xから日付Yまでの期間の毎日の変動率を母集団とした標準偏差) × (365の平方根)
ここで、毎日の変動率は以下のように計算します。
毎日の変動率 = 当日の株価 ÷ 前日の株価 - 1
・・・文字で書くとわけわからんですね。
意味としては、日々の変動率から算出した標準偏差を、年率（365日分）データに変換しているというだけです。
けど、平方根とか出てくるから難しく感じますね。私もなぜ平方根が登場するのか理由をいまいちわかっていませんけど。（標準偏差が分散の平方根だから？？）
例：マイクロソフトの 2010/1/4 から 2020/7/10 までの年率リスクを計算
前回と同じように、具体例としてマイクロソフト（MSFT）の 2010/1/4 から 2020/7/10 までの年率リスクを計算するとします。
この期間の日足データは 2648 個ありますので、ここに全データ載せるのは難しいので省略しますが、標準偏差を求めると 0.01584... となりました。
つまり、約95%以内の確率で、１日あたりの変動率は±3%以内に収まるということです。（あくまで正規分布すると仮定した場合ですが）
次にこの値を年率換算します。
0.01584 * √365 = 0.30272... となりましたので、年率リスクは 約 30.27% ということがわかりました。
GoogleFinance で年率リスクを計算する
「日付Xから日付Yまでの年率リスクを求める」として、ステップを以下の３つに分解します。
- 日付Xから日付Yまでの変動率を計算する
- 上記 1. を母集団とした標準偏差を求める
- 上記 2. に対して √365 を掛けて、年率リスクを算出する
1. 日付Xから日付Yまでの変動率を計算する
はっきり言って、かなりトリッキーなことをしないとインラインで算出できませんでした。
=ARRAYFORMULA(iferror(QUERY(GOOGLEFINANCE(ティッカーコード, "close", 日付X, 日付Y),"select Col2 OFFSET 1")/QUERY(GOOGLEFINANCE(ティッカーコード, "close", 日付X, 日付Y),"select Col2")-1,""))ポイントとしては、 QUERY 関数を使って一日ずらして配列計算しているところです。
"select Col2 OFFSET 1" で、２列目（株価）を抽出し、先頭の１行を削除するということをやっています。
GOOGLEFINANCE 関数では ARRAYFORMULA 型（配列型）で結果が返ってくるので、その配列式に対して QUERY 関数で射影とかしています。
2. 上記 1. を母集団とした標準偏差を求める
ここからは簡単です。関数を使って標準偏差を計算します。
// A1 セルに1.を定義しているとする
=STDEVP(A1)3. 上記 2. に対して √365 を掛けて、年率リスクを算出する
// B1 セルに2.を定義しているとする
=B1 * sqrt(365)[結論]年率リスクを取得するインライン計算式
以下の計算式で取得できます。
日付Xから日付Yまでの年率リスクを求めるとすると、
=STDEVP(ARRAYFORMULA(iferror(QUERY(GOOGLEFINANCE(ティッカーコード, "close", 日付X, 日付Y),"select Col2 OFFSET 1")/QUERY(GOOGLEFINANCE(ティッカーコード, "close", 日付X, 日付Y),"select Col2")-1,"")))*sqrt(365)具体的に、マイクロソフト（MSFT）の 2010/1/4 から 2020/7/10 までの年率リスクを計算するとしたら、
=STDEVP(ARRAYFORMULA(iferror(QUERY(GOOGLEFINANCE("MSFT", "close", "2010/1/4", "2020/7/10"),"select Col2 OFFSET 1")/QUERY(GOOGLEFINANCE("MSFT", "close", "2010/1/4", "2020/7/10"),"select Col2")-1,"")))*sqrt(365)または、マイクロソフト（MSFT）の 2010/1/4 から現時点までの年率リターンを計算するとしたら、
=STDEVP(ARRAYFORMULA(iferror(QUERY(GOOGLEFINANCE("MSFT", "close", "2010/1/4", TODAY()),"select Col2 OFFSET 1")/QUERY(GOOGLEFINANCE("MSFT", "close", "2010/1/4", TODAY()),"select Col2")-1,"")))*sqrt(365)
コメント