EXCEL 株価ノート

Step1. 【VBA】ボタン無しでプログラムを実行

ご覧の通り、1日の記入欄が、保有株価と資産額の変動を２行で記録するフォーマットになっています。
この２行の記入欄を、毎日追加するのが手間なのでプログラムで自動化。

見出しにある通り ボタンを設置せずにプログラムを実行し、記入欄を自動追加できるようにしました。
ついでに、同じ仕組みを使って、新しい銘柄を記録する列も追加できるようにしています。

セルをダブルクリックして処理を実行：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
　‥‥
End Sub
			

この関数を実装するとセルをダブルクリックした時、EXCELのダブルクリックの処理(セルの編集)が実行される前に この関数の中に書いたプログラムを実行。
こうすると、クリックしたセルを基準にして処理が出来るのがメリット。

特定のセルの場合だけ処理を実行：

If Target.Value = "ここに追加！" Then
　‥‥
End If
			

"Target"は、Worksheet_BeforeDoubleClick関数の引数でダブルクリックされたセルが設定されている。
このセルの値が「ここに追加！」の場合だけ、そのセル位置に対して処理を実行。
それ以外のセルの値の場合は、通常のEXCELのダブルクリックの処理が実行される。

EXCEL本来のダブルクリックの処理をキャンセル：

Cancel = True
			

"Cancel"は、Worksheet_BeforeDoubleClick関数の引数で、これをTRUEに設定すると、ダブルクリックの処理が実行されなくなる。 特定のセルの場合だけ、ダブルクリックでセルの編集モードに入らないように If文の中で設定。

ダブルクリックした行に、2行分をコピーして挿入：

Rows(Target.Row - 2 & ":" & Target.Row - 1).Copy
Rows(Target.Row).Insert

Application.CutCopyMode = False
			

クリックしたセルの上の2行分をコピーして、 クリックしたセルの行にコピーした2行を挿入。
最後に、コピーした範囲を解除。

結合セルに対する2回目以降の処理ですぐに関数終了：

If Target.Count > 1 Then Exit Sub
			

クリックしたセルが結合セルの場合は、"Target.Count"がインクリメントされて 結合したセルの数だけ処理が行われるらしい。
ですので、結合したセルの数だけ処理を繰り返す必要がない場合は、このCountが2以上になったら、関数をExit。

セルの値や書式のクリア：

Cells(Target.Row, Target.Column).ClearContents
Cells(Target.Row, Target.Column).Interior.Color = RGB(255, 255, 255)
			

この例では、クリックしたセルの値をクリア。
そして、クリックしたセルの色を白色に設定。

ループ処理で目的のセルまで繰り返し処理：

Do While Not Cells(Target.Row, Target.Column + cnt).Value = "/"
　‥‥
　cnt = cnt + 1
Loop
			

"Do While ～ Loop"という独特な形でループ処理を行う。
Excelの行や列を追加/削除しても処理が影響を受けないように 処理を修了する目印として、セルに"/"の値を入れておく。 ループの条件式で、セルの値が"/"になったら終了するようにしている。

Step2. 【VBA】ネットから株価を自動収集

ここでは、株価をネットから自動取得し、銘柄ごとの保有株数から前日からの資産の 変動額を算出します。
１つ１つ計算して入力するのが面倒だったので、そこを"半"自動化。
「株価情報取得」セルをダブルクリックして、銘柄ごとの資産額を「前日比」の欄に 出力するプログラムを作りました。

株価をネットから取得する：

Dim code As String
Dim objHttp As Object

code = Cells(Target.Row - 3, Target.Column + 1).Value
str_url = "https://www.google.com/finance/quote/ " + code + ":TYO?hl=ja"

On Error Resume Next

Set objHttp = CreateObject("MSXML2.XMLHTTP")
objHttp.Open "GET", str_url, False
objHttp.Send

buf = objHttp.responseText
            
Err.Clear
On Error GoTo 0
			

まずは、EXCELに入力してある銘柄コードから、googleファイナンスの銘柄ページのURL文字列を作成。
次に、それをCreateObject()関数で生成したHTTPオブジェクトにセットしてHTTPページを取得する。

HTTPページ取得に失敗しても、次のページの取得に進めるように”On Error Resume Next”でエラー無視。
HTTPページ取得処理が終わったら、エラー無視を解除してから、次の処理に進む。

HTTPページ（長い文字列）の中から目的も文字(数字)を抽出：

buf = Mid(buf, InStr(buf, "<div jsname=""ip75Cb"" class=""Bu4oXd""><div class=""YMlKec"">") + Len("<div jsname=""ip75Cb"" class=""Bu4oXd""><div class=""YMlKec"">"))
buf = Left(buf, InStr(buf, "</div>") - 1)
buf = Replace(buf, "￥", "")
buf = Replace(buf, ",", "")
ret = Val(buf)
			

取得したHTTPページは、非常に長い１つの文字列として、bufの中に格納されている。
その中から株価の情報があるところを、文字列検索により特定し、株価の部分だけを抽出。 さらに、"\"や","など不要な文字を取り除き、最後に数字に変換して、retに格納。

※ この株価の抽出のところは、ネット上の情報をそのまま流用させて頂きました。

Step3. 【VBA】株価のグラフを作成

今後は、日々の資産額の遷移を見える化したいと思ったのですが この株価ノートのフォーマットだと、資産額が1行おきにあるので、EXCELのグラフ機能では うまくグラフ化できません。。。

そこで、グラフ化もVBAを使ってやってしまおうと思います。

データ個数が一定ではない動的配列：

Dim dataList() As Variant
ReDim dataList(1 To Int(Target.Row / 2))
			

株ノートは毎日記録するので、日々データの個数が増えていく。
そのため、データの一番下の行をダブルクリックすることにより、 そこを起点にデータ個数を計算し、それを要素数としてリスト（配列）を生成する必要がある。

動的サイズの配列を作成するには、”ReDim”を使う。
プログラムの途中でサイズ（要素数）を増やすこともできるらしい。その場合は、"Reserve"を使うと 配列の内容を保持したまま、サイズ変更が可能。

ワークシートのオブジェクトを取得して別シートを操作：

Dim ws_g As Worksheet: Set ws_g = Worksheets("グラフ")
ws_g.Cells.Clear '前回グラフ作成時の情報をクリア
ws_g.Select 'グラフシートに標示を切り替え
			

別シートにデータを退避し、そこからグラフを作成するため ワークシートのオブジェクトを、"As Worksheet"で定義して、データのクリアやセルの参照などを行う。

グラフを作成：

'グラフを作成（全体の損益用）
With ws_g.Shapes.AddChart.Chart
    .ChartType = xlLine
    .SetSourceData ws_g.Range(ws_g.Cells(1, 1), ws_g.Cells(365, 12))
End With
			

ここでも、「グラフ」シートのオブジェクトを指定する必要がある。 "Range"や"Cells"など、いちいち指定しないとエラーになるので注意！
（別シートでなければ不要。）

"Shapes.AddChart.Chart"という関数を使って、追加したオブジェクト(グラフ)に対して 色々な操作を行う。
同じオブジェクトに対して、連続して操作を行うため"With"ステートメントを使うと便利。
この例では、 　"ChartType"でグラフの種類を指定
　"SetSourceData"にRangeを使ってデータ範囲を指定。
　※ 1行1列（=A1）セルから、365行12列（=K365）セルを、データ範囲とする、という意味。

複数エリアのデータからグラフを作成：

.SetSourceData Union(ws_g.Range(ws_g.Cells(1, 1), ws_g.Cells(365, 1)), _
                     ws_g.Range(ws_g.Cells(1, 5), ws_g.Cells(365, 12)) _
                     )
			

データの範囲の中で、連続しない複数のエリアからグラフを作りたい場合もある。
そのようなときは、この例のように、""Union"を使えば、複数のRangeを指定することが出来る。
ここでは、1つ目のRangeで1列目のセルを見出しとして指定、2つ目のRangeで5列目～12列目をデータ範囲としてしている。

グラフの位置、サイズを指定：

With ws_g.ChartObjects(1)
    .Top = Range("A1").Top
    .Left = Range("A1").Left

    .Width = 800
    .Height = 250
End With
			

追加したグラフのインデックスを指定（ここでは１番目に追加したグラフ=1を指定）して、 グラフに対する操作を行うことできる。
この例では、位置やサイズの指定をおこなっている。
※ "Shapes.AddChart.Chart"を実行したWithステートメントの中で行っても良いが、こういうやり方もあるということで参考までに。

シート内のグラフを探す：

Dim shp As Shape    'オブジェクト
Dim shp_ct As Long  'グラフオブジェクト数

'グラフシート内のオブジェクトを順に処理
For Each shp In ws_g.Shapes
    If shp.Type = msoChart Then 'オブジェクトがグラフの場合
        ws_g.ChartObjects.Delete
        Exit For   '１つでもあれば全部消す、という処理なのでここでループを抜ける
    End If
Next shp
			

ここでも、「グラフ」シートのオブジェクトを使って操作している。
シート内のオブジェクト（"Shapes"）に対して１つずつForループで処理を行う。

この例では、オブジェクトの種別を判定する、ということと行っている。
また、途中でループを抜ける場合は、"Exti For"で.