株価変化率

レポートの解答例

Sub Macro1()
i = 0 '何番目の表を読み込むかを表す変数
Do
　j = 1
　Do While Cells(j, 1) <> ""
　　j = j + 1
　Loop '何行目から貼り付けるかを調べる

　With ActiveSheet.QueryTables.Add(Connection:= _
　　"URL;http://table.yahoo.co.jp/t?c=1991&a=1&b=4&g=d&s=998405.t&z=998405.t&y="& i * 50, Destination:=Cells(j, 1))
　　.Name = "t?s=998405.t&g=d"
　　.FieldNames = True
　　.RowNumbers = False
　　.FillAdjacentFormulas = False
　　.PreserveFormatting = True
　　.RefreshOnFileOpen = False
　　.BackgroundQuery = True
　　.RefreshStyle = xlOverwriteCells
　　.SavePassword = False
　　.SaveData = True
　　.AdjustColumnWidth = True
　　.RefreshPeriod = 0
　　.WebSelectionType = xlSpecifiedTables
　　.WebFormatting = xlWebFormattingNone
　　.WebTables = "19"
　　.WebPreFormattedTextToColumns = True
　　.WebConsecutiveDelimitersAsOne = True
　　.WebSingleBlockTextImport = False
　　.WebDisableDateRecognition = False
　　.WebDisableRedirections = False
　　.Refresh BackgroundQuery:=False
　End With

　If i > 0 Then
　　Rows(j).Delete
　End If '最初以外、日付などの行を消す

　i = i + 1 '何番目の表を読み込むか、を表す変数を一つ増やす

Loop While Cells(j, 1) <> "" '貼り付けた表が空っぽなら終了、そうでないなら繰り返す
Rows(j).Delete '一番最後の週間、月間を削除
Rows(j).Delete
End Sub

'の後はコメントになります。
レポートが完全に出来なかった人は、この解答例を使ってデータを読み込んでください。

騰落率とその分布

株価指数のデータですので、その変化を見てみましょう。
株価の場合、差ではなく変化率に注目します。
つまり「今日の株価－昨日の株価」ではなくて
「（今日の株価－昨日の株価）÷昨日の株価＝今日の株価÷昨日の株価－１」を計算します。
何故なら、例えば10,000円投資する場合に、1,000円の時に10円変化することと、2,000円のときに20円変化することを考えると、
1,000円ならば10株買えて、一株10円上がれば10×10=100円の利益
2,000円ならば5株買えて、一株20円上がれば20×5=100円の利益
となり同等ですから、10円上がったか20円上がったかということではなくて1%上がったということに注目するのです。

では実際に変化率を計算しましょう。表計算ソフトですから、F2セルにE3からE2セルへの変化E2-E3を、率になおすためにE3で割った
=(E2-E3)/E3
を書いて、これを下までずっとコピーすれば計算できます。
但し

• 今回のように長いデータだとコピーするのが大変です。
• このようにセルに入力された式は、何かの操作をするたびに再計算されますので4000以上も式を入力するとExcelが遅くなります。

再計算を止めさせる事も出来ますが、そもそも変化率は一度計算したら再計算する必要が無いのでVBAで計算してしまいましょう。

レポート課題

1. まず最初にデータが何番目の行まで入っているか数えるプログラムを書いてください。
   この値は今後何度も使うので、Cells(1,7)（G1セル）に表示してください。
2. Cells(2,6)に(Cells(2,5) - Cells(3,5)) / Cells(3,5)を計算した値を代入し、同様の計算をデータの一番下まで繰り返すプログラムを書いてください。
3. 次にその変化率の平均を求めて、Cells(1,9)に表示してください。
   Cells(1,8) = "変化率の平均"
   と書くと分かりやすいです。
   平均は全部足して、足した個数で割れば求められます。全部足す方法ですが、例えばcells(1000,6)まで足すとして
   cells(2,6)+cells(3,6)+cells(4,6)+…+cells(1000,6)
   のような沢山の足し算をプログラムに書くことは出来ませんし、このままではFor文で書くことも出来ません。そこでまずFor文で書けるような形に書き直します。
   a=0
   a=a+cells(2,6)
   a=a+cells(3,6)
   a=a+cells(4,6)
   …
   a=a+cells(1000,6)
   と書けばcells(1000,6)まで足すことが出来ますし、これならばFor文を用いて書くことが出来ます。
4. さらに変化率の標準偏差を求めてCells(2,9)に表示してください。標準偏差は分散の平方根で、分散は例えばcells(1000,6)までなら
   (cells(2,6)－平均)^2 + (cells(3,6)－平均)^2 +…+(cells(1000,6)－平均)^2
   で計算できますので、これをデータの最後まで、For文を使って計算してください。
   平方根は、Excelのセルに書くときはSQRTですが、VBAではSqrです。

TOPIXは1740から1400以下まで下がっているので、騰落率の平均はマイナスになると思われます。もしプラスになったら、理由を考えてみましょう。