Excelで株式分析をしよう！
こんにちは！私はとあるホワイト企業で毎日定時退社している者です。
定時退社ばかりしていると退屈なので株式投資を小口でしています。
この前知ったSTOCKHISTORY関数が良かったので解説しようと思いこの記事を作りました
1. STOCKHISTORY 関数の基本
STOCKHISTORY 関数は、株式や通貨ペアなどの金融商品の過去のデータを取得するための強力なツールです。この関数を使用することで、指定した期間の株価や為替レートなどの履歴データをExcelシート上に直接取り込むことができます。
基本的な構文は以下の通りです：
=STOCKHISTORY(銘柄, 開始日, [終了日], [間隔], [ヘッダー], [プロパティ1], [プロパティ2], ...)
例えば、トヨタ自動車の株価データを取得する場合：
=STOCKHISTORY("7203.T", DATE(2024,1,1), DATE(2024,3,31), 0, TRUE, 2, 3, 4, 5)
この例では、2024年1月1日から3月31日までのトヨタ自動車の日次データを、始値、高値、安値、終値を含めて取得します。
2. 銘柄の指定方法
STOCKHISTORY 関数で銘柄を指定する際は、証券コードや銘柄コードを使用します。日本株の場合、通常は証券コードの後に ".T" を付けます。
例：
- ソニー：6758.T
- 任天堂：7974.T
- 日産自動車：7201.T
海外株の場合は、ティッカーシンボルを使用します：
=STOCKHISTORY("AAPL", DATE(2024,1,1), DATE(2024,3,31))
この例では、アップル社の株価データを取得しています。
3. 日付の指定
開始日と終了日は、DATE 関数を使用して指定するのが一般的です。これにより、地域設定に関係なく正確な日付を指定できます。
=STOCKHISTORY("7203.T", DATE(2024,1,1), DATE(2024,12,31))
この例では、2024年の1年間のデータを取得します。終了日を省略すると、現在日付までのデータが取得されます：
=STOCKHISTORY("7203.T", DATE(2024,1,1))
4. データの間隔設定
間隔引数を使用して、データの頻度を指定できます：
- 0：日次データ（デフォルト）
- 1：週次データ
- 2：月次データ
例えば、月次データを取得する場合：
=STOCKHISTORY("7203.T", DATE(2024,1,1), DATE(2024,12,31), 2)
5. ヘッダーの表示
ヘッダー引数を TRUE に設定すると、結果にヘッダー行が含まれます：
=STOCKHISTORY("7203.T", DATE(2024,1,1), DATE(2024,12,31), 0, TRUE)
これにより、各列の意味がわかりやすくなります。
6. データプロパティの選択
プロパティ引数を使用して、取得したいデータの種類を指定できます：
- 0：日付
- 1：終値（デフォルト）
- 2：始値
- 3：高値
- 4：安値
- 5：出来高
例えば、日付、始値、高値、安値、終値を取得する場合：
=STOCKHISTORY("7203.T", DATE(2024,1,1), DATE(2024,12,31), 0, TRUE, 0, 2, 3, 4, 1)
7. 複数銘柄の取得
STOCKHISTORY 関数を使って複数の銘柄のデータを取得するには、各銘柄に対して関数を個別に使用します：
=STOCKHISTORY("7203.T", DATE(2024,1,1), DATE(2024,12,31))
=STOCKHISTORY("6758.T", DATE(2024,1,1), DATE(2024,12,31))
=STOCKHISTORY("7974.T", DATE(2024,1,1), DATE(2024,12,31))
これらの関数を別々のセルに配置することで、複数銘柄のデータを同時に表示できます。
8. 為替レートの取得
STOCKHISTORY 関数は為替レートの取得にも使用できます。通貨ペアをコロン（:）で区切って指定します：
=STOCKHISTORY("USD:JPY", DATE(2024,1,1), DATE(2024,12,31), 0, TRUE)
この例では、2024年の米ドル対日本円の為替レートデータを取得します。
9. データの加工と分析
STOCKHISTORY 関数で取得したデータは、他のExcel関数と組み合わせて様々な分析が可能です。例えば、平均株価を計算する場合：
=AVERAGE(STOCKHISTORY("7203.T", DATE(2024,1,1), DATE(2024,12,31), 0, FALSE, 1))
この関数は、2024年のトヨタ自動車の平均終値を計算します。
10. チャート作成
STOCKHISTORY 関数のデータを使用して、株価チャートを簡単に作成できます：
- STOCKHISTORY 関数でデータを取得
- データ範囲を選択
- 「挿入」タブから「チャート」を選択
- 「株価」チャートタイプを選択
これにより、プロフェッショナルな株価チャートが簡単に作成できます。
11. 条件付き書式の適用
STOCKHISTORY 関数で取得したデータに条件付き書式を適用することで、視覚的な分析が可能になります。例えば、株価が前日比で上昇した日を緑色、下落した日を赤色で表示する場合：
- データ範囲を選択
- 「ホーム」タブから「条件付き書式」を選択
- 「新しいルール」を選択
- 「数式を使用して、書式設定するセルを決定」を選択
- 以下の数式を入力：
=B2>B3 - 緑色の塗りつぶしを選択
同様の手順で、下落した日に対して赤色の条件付き書式を設定します。
12. ボラティリティの計算
STOCKHISTORY 関数で取得したデータを使用して、株価のボラティリティ（変動性）を計算できます。以下は日次リターンの標準偏差を計算する例です：
=STDEV.P(LN(OFFSET(STOCKHISTORY("7203.T",DATE(2024,1,1),DATE(2024,12,31),0,FALSE,1),1,0)/STOCKHISTORY("7203.T",DATE(2024,1,1),DATE(2024,12,31),0,FALSE,1)))
この関数は、2024年のトヨタ自動車の株価の日次ボラティリティを計算します。
13. 移動平均の計算
STOCKHISTORY 関数と組み合わせて移動平均を計算することができます。以下は20日移動平均を計算する例です：
=AVERAGE(OFFSET(STOCKHISTORY("7203.T",DATE(2024,1,1),DATE(2024,12,31),0,FALSE,1),ROW(A1)-1,0,20))
この関数を下方向にドラッグすることで、各日の20日移動平均が計算されます。
14. 相対強度指数（RSI）の計算
STOCKHISTORY 関数を使用して、テクニカル指標の一つである相対強度指数（RSI）を計算できます。以下は14日間のRSIを計算する関数です：
=100 - (100 / (1 + AVERAGEIF(OFFSET(B2,1-ROW(B2),0,14,1), ">0") / ABS(AVERAGEIF(OFFSET(B2,1-ROW(B2),0,14,1), "<0"))))
ここで、B列にSTOCKHISTORY 関数で取得した日次の価格変化（当日終値 - 前日終値）を入力しておく必要があります。
15. マクロを使用したデータ更新
STOCKHISTORY 関数のデータを自動的に更新するマクロを作成することができます。以下は、指定したセル範囲のSTOCKHISTORY 関数を更新するVBAコードの例です：
Sub UpdateStockData()
Dim cell As Range
Dim dataRange As Range
' 更新するセル範囲を指定
Set dataRange = Sheet1.Range("A1:A10")
Application.ScreenUpdating = False
For Each cell In dataRange
If Left(cell.Formula, 13) = "=STOCKHISTORY" Then
cell.Calculate
End If
Next cell
Application.ScreenUpdating = True
MsgBox "株価データが更新されました。", vbInformation
End Sub
このマクロを実行すると、A1:A10の範囲内のSTOCKHISTORY 関数が再計算され、最新のデータに更新されます。
以上、STOCKHISTORY 関数の詳細な使用方法と応用例を15章にわたって解説しました。この関数を活用することで、Excelを使った株価分析や投資判断の効率が大幅に向上するでしょう。データの取得から高度な分析まで、STOCKHISTORY 関数は幅広いニーズに対応できる強力なツールです。