2025-01-26

【備忘録】Excel VBA で平均足を算出

VBA, Visual Basic for Applications は、マイクロソフト社が Microsoft Visual Basic を、同社製品の Microsoft Office に搭載したものです。VBA を使用することで、Excel、Access、Word、Outlook、PowerPoint など、Office のアプリケーション・ソフトウェアの機能をカスタマイズしたり、拡張したりすることができます。

Wikipedia より引用、編集

楽天証券に口座を開設してトレーディングをしているので、同社が提供しているトレーディングツール「マーケットスピード II [1]」を利用しています。

マーケットスピード II は高機能なのでトレーディングツールとして申し分ないのですが、データを取得して自分で解析やカスタマイズした処理をしたいので、マーケットスピード II の機能（の一部）を Excel 上で実現できるアドイン「マーケットスピード II RSS [2]」（以下 RSS と呼びます）も利用して、Excel 上でデータの加工などをしています。

RSS の RssChart 関数を利用すれば簡単に 始値-高値-安値-終値 の四本値データ（+出来高）をリアルタイムで取得できます。リアルタイムと言っても、例えば１分足の場合、１分毎に更新されたデータを１行下に追加するという動作です。

マーケットスピード II でチャート上に表示できる平均足をじっくり評価したかったのですが、残念ながら平均足データを取得する関数は RSS に用意されていません。そこで、四本値データから平均足を算出する VBA マクロを作成しました。

Excel VBA で 平均足を算出

RSS の RssChart 関数が Excel シートへリアルタイムで流し込んでくれる東証の銘柄の１分足データから、平均足を算出、右側に追記する VBA マクロです。

下記の OS 環境で動作確認をしています。

Microsoft Windows 11 Home 24H2
Microsoft Excel 2024 MSO 64 ビット

VBA でのアプローチ

下記の赤い領域に新しい平均足の値 (H_Open, H_High, H_Low, H_Close) を算出するために、赤い領域を含む灰色の領域 (Range) を Variant 配列に転記して、平均足を算出処理するマクロへ渡します。

平均足を算出するために使用する範囲

サンプルでは、算出後の配列をそのまま元の領域 (Range) へ戻しています。このやり方はシンプルですが、バグにより本来変更してはならない部分が書き換えられてしまうリスクがあります。

VBA マクロの構成

今回のマクロの構成は以下のようになっています。

 

実装環境から抜き出して、それをサンプル用として１つのモジュールにまとめなかったため、動作確認用サンプルは複数のモジュールにまたがってしまっています。あらかじめご了承ください。

平均足の算出処理

平均足の算出処理は、標準モジュール ModTechnical の Sub プロシージャ CalcHeikinAshi を呼び出します。

平均足の算出は、参考サイト [3] にある平均足の説明に沿っています。説明文を VBA コード内のコメントに流用させていただきました。

標準モジュール ModTechnical
Option Explicit


' ### プライベート定数（モジュールレベル） ####################################

Private Const P_OPEN As Long = 1
Private Const P_HIGH As Long = 2
Private Const P_LOW As Long = 3
Private Const P_CLOSE As Long = 4
Private Const P_VOLUME As Long = 5
Private Const H_OPEN As Long = 6
Private Const H_HIGH As Long = 7
Private Const H_LOW As Long = 8
Private Const H_CLOSE As Long = 9


' _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
'
' 平均足の算出
'
' 【参考サイト】
' https://marketspeed.jp/ms2/onlinehelp/ohm_007/ohm_007_05.html
'
' _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
Sub CalcHeikinAshi(arr As Variant, Optional Is1st As Boolean = False)
    
    If Is1st Then
        ' 2本目の平均足：前日の平均足がないため、始値を下記値より算出する
        ' 始値 = (前日の始値 + 前日の高値 + 前日の安値 + 前日の終値) / 4
        arr(2, H_OPEN) = _
            (arr(1, P_OPEN) + arr(1, P_HIGH) + arr(1, P_LOW) + arr(1, P_CLOSE)) / 4
    Else
        ' 始値=(前日の平均足の始値+前日の平均足の終値)/2
        arr(2, H_OPEN) = (arr(1, H_OPEN) + arr(1, H_CLOSE)) / 2
    End If
    
    ' 終値=(当日の始値+当日の高値+当日の安値+当日の終値)/4
    arr(2, H_CLOSE) = _
        (arr(2, P_OPEN) + arr(2, P_HIGH) + arr(2, P_LOW) + arr(2, P_CLOSE)) / 4
    
    ' 陰線：始値＞＝終値
    ' 陽線：始値＜終値
    ' 平均足の高値と安値は、当日の高値と安値を用いる。
    
    If arr(2, H_OPEN) >= arr(2, H_CLOSE) And arr(2, P_HIGH) < arr(2, H_OPEN) Then
        ' 但し、平均足が陰線で当日の高値＜平均足の始値の場合、
        ' 平均足の高値=平均足の始値とする。
        arr(2, H_HIGH) = arr(2, H_OPEN)
    Else
        ' 当日の高値
        arr(2, H_HIGH) = arr(2, P_HIGH)
    End If
    
    If arr(2, H_OPEN) < arr(2, H_CLOSE) And arr(2, P_LOW) > arr(2, H_OPEN) Then
        ' 但し、平均足が陽線で当日の安値＞平均足の始値の場合、
        ' 平均足の安値=平均足の始値とする。
        arr(2, H_LOW) = arr(2, H_OPEN)
    Else
        ' 当日の安値
        arr(2, H_LOW) = arr(2, P_LOW)
    End If

End Sub

動作確認

動作確認のサンプルを示しました。テスト用のマクロですが、用途の事情で擬似的に１秒間隔でループを回したかったので、少し長くなってしまいました。

標準モジュール ModCommon
Option Explicit


' ### 定　数 ##################################################################

' 取引時間関連
Public Const T_OPEN_MKT As Date = "09:00:00"
Public Const T_HEIKIN_START As Date = "09:02:00"
Public Const T_LUNCH_START As Date = "11:30:00"
Public Const T_LUNCH_END As Date = "12:30:00"
Public Const T_HEIKIN_END As Date = "15:24:00"
Public Const T_CLOSE_MKT As Date = "15:30:00"

市場が開いて 9:00 の１分足データが確定するのは 9:01 以降です。また最初 (9:00) の１分足データに対しては平均足を算出できません。9:01 の１分足データが確定するのは 9:02 以降になります。ですので、平均足を算出するのは T_HEIKIN_START で指定した 9:02 になります。

なお、ここでは処理をシンプルにするために RssChart 関数は、時刻が 0 秒になったら直ちに直前の１分足データをシートに書き込むものとし、かつ、株価は 9:00 に寄り付いているものとしています。

汎用関数は標準モジュール ModFunc にまとめてあります。この動作確認用サンプルでは GetPrevMinute 関数しか使いませんが、依存している関数も含めています。

標準モジュール ModFunc
Option Explicit


' ### Function 関数 ###########################################################


' _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
'
' 現在の時刻 (HH:MM:SS) を HH:MM 形式の文字列で取得
'
' _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
Public Function GetCurrentMinute(timeNow As Date) As String
    Dim StrHH As String
    Dim StrMM As String

    StrHH = Format(Hour(timeNow), "00")
    StrMM = Format(Minute(timeNow), "00")
    
    GetCurrentMinute = StrHH & ":" & StrMM

End Function


' _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
'
' 一分前の時刻を HH:MM 形式の文字列で取得
'
' _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
Public Function GetPrevMinute(timeNow As Date) As String
    Dim timePrev As Date

    timePrev = DateAdd("n", -1, timeNow)
    GetPrevMinute = GetCurrentMinute(timePrev)

End Function

動作確認用のマクロはワークシートに記述しています。

Microsoft Excel Objects Sheet1
Option Explicit


' ### プライベート定数（モジュールレベル） ####################################


' 列関連
Private Const P_TIME As Long = 5
Private Const P_OPEN As Long = 6
Private Const P_HIGH As Long = 7
Private Const P_LOW As Long = 8
Private Const P_CLOSE As Long = 9
Private Const P_VOLUME As Long = 10
Private Const H_OPEN As Long = 11
Private Const H_HIGH As Long = 12
Private Const H_LOW As Long = 13
Private Const H_CLOSE As Long = 14

Private Is1st As Boolean        ' 最初の処理かを判定するフラグ


' ### Sub プロシージャ ########################################################


' _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
'
' 平均足テスト
'
' _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
Sub TestHeikinAshi()
    Dim timeNow As Date
    Dim strTargetTime As String ' 検索するターゲット時刻
    Dim RangeCol As Range       ' 検索レンジ（１列）
    Dim foundObj As Range       ' 検索結果を格納するレンジ
    Dim RangeData As Range      ' データ範囲
    Dim rowLast As Long         ' 最終行
    Dim row1 As Long            ' 平均足算出に使用するレンジの第一行
    Dim row2 As Long            ' 平均足算出に使用するレンジの第二行
    Dim arrHeikin As Variant    ' 平均足算出処理に渡す配列

    ' シートの初期化
    Call SheetInit

    timeNow = T_OPEN_MKT
    Do While timeNow <= T_CLOSE_MKT
        If T_HEIKIN_START <= timeNow And timeNow <= T_HEIKIN_END And Second(timeNow) = 0 Then
            strTargetTime = GetPrevMinute(timeNow)
            
            ' 時刻列の最終行を取得
            rowLast = Cells(1, P_TIME).End(xlDown).Row
            ' 検索レンジ（１列）を設定
            Set RangeCol = Range(Cells(1, P_TIME), Cells(rowLast, P_TIME))
            ' 検　索
            Set foundObj = RangeCol.Find(strTargetTime, LookAt:=xlWhole)
            ' 検索結果による処理
            If Not foundObj Is Nothing Then
                ' 検索結果が有る場合は検索セルの行を取得
                ' ※ 検索する時系列は重複が無いことを前提にしている
                row2 = foundObj.Row
                row1 = row2 - 1

                ' 平均足算出に必要なレンジを配列へ転記
                arrHeikin = Range(Cells(row1, P_OPEN), Cells(row2, H_CLOSE)).Value

                ' 平均足の算出
                If Is1st Then
                    Call CalcHeikinAshi(arrHeikin, Is1st)
                    Is1st = False
                Else
                    Call CalcHeikinAshi(arrHeikin)
                End If

                ' 平均足算出結果を元のレンジへ転記
                Range(Cells(row1, P_OPEN), Cells(row2, H_CLOSE)).Value = arrHeikin

            End If

        End If
        
        ' 現在時刻を１秒進める
        timeNow = DateAdd("s", 1, timeNow)
    
    Loop

End Sub


' _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
'
' シートの初期化
'
' _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
Sub SheetInit()
    Dim xlLastRow As Long  ' Excel自体の最終行
    Dim rowLast As Long    ' 最終行
    Dim RangeData As Range ' データ範囲
    
    ' 以前に算出した平均足データの消去
    xlLastRow = Cells(Rows.Count, P_TIME).Row          ' Excelの最終行を取得
    rowLast = Cells(xlLastRow, P_TIME).End(xlUp).Row ' 時刻列の最終行を取得
    If rowLast > 1 Then
        Set RangeData = Range(Cells(2, H_OPEN), Cells(rowLast, H_CLOSE))
        RangeData.ClearContents
    End If

    ' 最初の処理かを判定するフラグの初期化
    Is1st = True

End Sub

Excel 上で思うようにローソク足チャートを描画できなかったので、JupyterLab 上にチャートをプロットしました。ここでは Python のコードは割愛します。４本値を用いた通常のローソク足チャートと平均足データによるローソク足チャートを並べました。

通常のローソク足チャート

平均足データのローソク足チャート

サンプルシート

以上のコード、シートを含む Excel ファイルを下記からダウンロードできます。ご参考まで。

sample_heikinashi.xlsm

参考サイト

  1. マーケットスピード II | 楽天証券のトレーディングツール
  2. マーケットスピード II RSS | 楽天証券のトレーディングツール
  3. テクニカル指標/チャート形状 | マーケットスピード II オンラインヘルプ | 楽天証券のトレーディングツール

 

ブログランキング・にほんブログ村へ bitWalk's - にほんブログ村 にほんブログ村 IT技術ブログ オープンソースへ
にほんブログ村

オープンソース - ブログ村ハッシュタグ
#オープンソース



このエントリーをはてなブックマークに追加
投稿者 時刻:
ラベル: , ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)