こんにちは、タマです。
今回はスプレッドシートでヒートマップを作成する方法を公開・配布します。
興味のある方はぜひ使ってみてください。
- スプレッドシートでヒートマップを再現したい！
- 自分でオリジナルのヒートマップを作成したい！
- ETFの値動きをヒートマップで確認したい！
- ヒートマップと一緒にチャートも確認したい！
スプレッドシートでヒートマップ！
今回配布するスプレッドシートは、主要ETFの値動きをヒートマップで確認できるようになっています。ETFの値動きを視覚的にわかりやすくウォッチしたい方におすすめです。
なお、記事後半でカスタマイズ方法を紹介しているので、オリジナルのヒートマップを作成したい方はそちらを参考にしてください。
実装機能
このスプレッドシートには次の機能が実装されています。
- ティッカーシンボルに対応した株価を表示
- ティッカーシンボルに対応した株価変動率を表示
- 株価変動率を基準にヒートマップを作成
- 表示期間を調整できるチャートを表示
上記機能を実現するにあたってスクレイピングは行いません。スプレッドシートに用意されている関数だけを使っています。
他に追加したい機能がある場合は各自でお好きなように導入してください。
スプレッドシートの配布
今回のスプレッドシートはこちらから入手できます。
開いただけでは権限が「閲覧者」になっているので何も操作できません。
下記手順でシートのコピーを作成してください。
- Googleアカウントにログインする。
- ログインが完了したら「ファイル」→「コピーを作成」をクリックする。
ヒートマップの仕組み
私が作成したヒートマップの仕組みは非常に単純です。
まず各銘柄ごとに4つの要素が割り当てられています。
- 1行目：ティッカーシンボル
- 2行目：銘柄の説明
- 3行目：リアルタイムの株価
- 4行目：前取引日の終値からの株価の変動率
ヒートマップは4行目の「株価変動率」を基準に変化し、プラス幅が大きいほど緑色に、マイナス幅が大きいほど赤色になります。
ただし、この色は株価変動率のセルのみに設定されているため、他のセルは色が変化しません。したがって、その銘柄の全体の色が変わるようにスクリプトを組んでいます。
スクリプトの中身については記事後半の「スクリプトの設定」を確認してください。
ヒートマップの使い方
このヒートマップを更新するためにはスクリプトの実行許可が必要です。
下記手順で実行許可を与えてください。
①スクリプトの実行許可
スクリプト実行時に下記のようなアラートが表示されます。
ここでは「続行」をクリックしてください。
次の画面ではご自身のアカウントを選択してください。
下記のような画面が表示されたら左下の「詳細」をクリックし、「スプレッドシートでヒートマップ（安全ではないページ）に移動」をクリックしてください。
ここまでできたら次の画面で「許可」をクリックすることで完了です。
②ヒートマップの使い方
銘柄の登録
すでに主要なETF名が入っていますが、それを変更したい場合はティッカーシンボルが書いてあるセルに新たな銘柄のティッカーシンボルを入力してください。また、その銘柄にあった説明を下の行に入力してください。
銘柄を新たに追加する場合はすでに書かれているところからコピペすると楽に反映できます。
- 銘柄の説明行（各2行目のセル）が不要な場合は削除していただいて構いません。ただし、スクリプトの修正が別途必要になるため、後述するカスタマイズ方法を参考に修正してください。
- 基本的にはコピペで良いですが、場合によっては枠線の設定や書式等がズレる可能性があります。その場合は適宜修正してください。
ヒートマップの表示
ティッカーシンボルを入力するだけでは株価や株価変動率しか反映されません。
ヒートマップとして色を表示させたい場合は「更新ボタン」をクリックしてください。このとき、更新ボタンは最後に1回クリックすればOKです。入力の都度やる必要はありません。
今回のスクリプトはデータが入っている最終行までチェックして色を反映させようとします。したがって、銘柄数が多いほど色の反映に時間がかかるため、ある程度銘柄数を絞るか使わないデータは削除しておきましょう。
※私の環境では150銘柄で約1分程度かかりました。
カスタマイズ方法
ここからスプレッドシートのカスタマイズ方法を紹介します。
下記内容を参考にしてオリジナルのヒートマップを作ってみてください。
色の設定
ヒートマップとして利用する色は次のように設定しています。
- 最大値（+3%）のカラーコード：#2ecb5e
- 中央値（±0%）のカラーコード：#424453
- 最小値（-3%）のカラーコード：#ff0000
これらは株価変動率のセルに「条件付き書式」として設定されています。
色の修正や対象箇所の追加・削除を行う場合は条件付き書式の設定から修正してください。
株価変動率のセルを選択後、「表示形式」→「条件付き書式」から設定できます。
チャートの設定
チャートは次の書式で表示しています。
=SPARKLINE(GOOGLEFINANCE([ティッカーシンボル],“price”,[開始日],[終了日]))
ティッカーシンボル、開始日、終了日はそれぞれ該当するセルを参照しているため、その参照元の情報を変えれば自動的に反映されるようになっています。
- 開始日と終了日はヒートマップ右側のP列にて設定できるようになっています。
- チャートの読み込みが遅い場合は、一度開始日や終了日を別の日に設定すると反映されやすくなります。
スクリプトの設定
このスプレッドシートには次のスクリプトが組み込まれています。
function myFunction() {
var bk = SpreadsheetApp.getActiveSpreadsheet();
var sh = bk.getActiveSheet();
const lastRaw = sh.getLastRow();
for(let i = 8; i <= lastRaw; i++){ //8行目から最終行までをチェックする
for(let j = 2; j <= 13; j++){ //2列目から13列目までをチェックする
let str = sh.getRange(i,j).getFormula() //セルの書式を取得する
if(str.slice(-3) == "100"){ //書式の最後が「100」で終わっているかをチェックする
var rng = sh.getRange(i,j).activate(); //アクティブ状態のセルを取得する
var rgb = rng.getBackground(); //セルの背景色を取得する
sh.getRange(i-3,j,3).setBackground(rgb); //上3行に同じ背景色を適用する
}
}
}
}動作の流れは下記のようになっています。
- B列8行目からチェック開始
- セルの書式を取得
- 書式の最後が「100」で終わっているかを確認
- TRUEの場合はそのセルの背景色を取得し上3行に適用
※FALSEの場合は次の列へ移動
右にあるコメントを見ればすぐに理解できるくらい、非常に単純なスクリプトになっています。
いくつか固定の数値を入れている箇所があるので、ヒートマップを拡大する場合や情報を追加・削除する場合はそれに合わせて数値を修正してください。
※スクリプトの書き方が下手くそだというクレームは受け付けません。
カスタマイズ例
今回のデータは主要ETFの値動きを確認するものですが、当然ながら個別株のヒートマップにカスタマイズすることも可能です。
銘柄数が多くなるほど色の反映に時間がかかりますが、1分前後待てる方は上記のようなヒートマップを作成することができます。
注意事項
下記内容に注意して利用してください。
- データの取得はGOOGLEFINANCE関数で行っているためGOOGLEFINANCE関数の不具合によってデータが正しく表示されないことがあります。
- GOOGLEFINANCE関数で取得するデータは最大20分の遅延の可能性があります。
- 配布データに関するご質問は私のTwitterアカウント(@p_boy_tama)までお問い合わせください。
- 配布データによって生じた損害等の一切の責任を負いかねますのでご了承ください。
二次配布等自由ですが、このスプレッドシートを広めたい場合は本記事を共有していただけると嬉しいです。
まとめ
今回はスプレッドシートでヒートマップを再現する方法を紹介しました。
スプレッドシートであればデータを自由に組み合わせて表やグラフを作ることができるので、各自の使い方に合わせてお好きなようにカスタマイズしてください。
もし気に入っていただけれたらSNS等で「私のオリジナルヒートマップはこれだ！」といった感じでツイートしていただけると嬉しいです。
