EXCEL VBAで気象庁ホームページから風向風速，降水量，気温，日照時間のデータをダウンロードする
2011年9月19日の記事に，日照時間をダウンロードするコードを追加しました．更にデータに付加される記号について判別する仕様としました．
http://www.data.jma.go.jp/obd/stats/data/mdrr/man/remark.html
Option ExplicitSub WEBQUERY()
Dim mySht As Worksheet
Dim myAnswer As Variant
Dim myWindAr(52703, 3) As Variant
Dim myRainAr(52703, 2) As Variant
Dim myKionAr(52703, 2) As Variant
Dim mySunAr(52703, 2) As Variant
Dim myURL As String
Dim myYear As Integer
Dim myMonth As Integer
Dim myDay As Integer
Dim myDate As Date
Dim tmpDate As Date
Dim myTime As Single
Dim myRng As Range
Dim i As Integer
Dim j As Long
Dim k As Long
Dim m As Long
Dim n As Long
Dim myPlace As StringApplication.ScreenUpdating = False
j = 0
k = 0
m = 0
n = 0
myDate = Date
myAnswer = Application.InputBox(Prompt:="1994から今年の間の西暦年を4桁で入力してください", Default:=Year(myDate), Type:=1)
If TypeName(myAnswer) = "Boolean" Then Exit Sub
If myAnswer < 1994 Or myAnswer > Year(myDate) Then Exit Sub
myYear = myAnswer
myTime = Timer
For myMonth = 1 To 12
On Error Resume Next
If DateSerial(myYear, myMonth, 1) - DateSerial(Year(myDate), Month(myDate), 1) >= 0 Then
Exit For
End If
On Error GoTo 0
For myDay = 1 To 31
On Error Resume Next
tmpDate = DateValue(myYear & "/" & myMonth & "/" & myDay)
If Err.Number <> 0 Then
Exit For
End If
On Error GoTo 0
myURL = "URL;http://www.data.jma.go.jp/obd/stats/etrn/view/10min_a1.php?prec_no=55&prec_ch=%95x%8ER%8C%A7&block_no=0552&block_ch=%93v%94g&year=" & myYear & "&month=" & myMonth & "&day=" & myDay
Set mySht = Worksheets.Add
Set myRng = mySht.Range("$A$1")
With mySht.QueryTables.Add(Connection:=myURL, Destination:=myRng)
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.Refresh BackgroundQuery:=False
End With
myPlace = Application.WorksheetFunction.Replace(mySht.Range("$A$1"), Application.WorksheetFunction.Find(" ", mySht.Range("$A$1")), Len(mySht.Range("$A$1")), "")
For i = 1 To 144
Select Case True
Case myRng(i + 4, 5) = "///" _
Or myRng(i + 4, 5) = "#" _
Or myRng(i + 4, 5) = "" _
Or Right(myRng(i + 4, 5), 2) = " ]" _
Or myRng(i + 4, 5) = "−" _
Or myRng(i + 4, 5) = "×" _
Or myRng(i + 4, 4) = "///" _
Or myRng(i + 4, 4) = "#" _
Or myRng(i + 4, 4) = "" _
Or Right(myRng(i + 4, 4), 2) = " ]" _
Or myRng(i + 4, 4) = "−" _
Or myRng(i + 4, 4) = "×"
j = j - 1
Case Right(myRng(i + 4, 5), 2) = " )" And Right(myRng(i + 4, 4), 2) = " )"
myWindAr(j, 0) = myPlace
myWindAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myWindAr(j, 2) = Replace(myRng(i + 4, 5), " )", "")
myWindAr(j, 3) = Replace(myRng(i + 4, 4), " )", "")
Case Right(myRng(i + 4, 5), 2) = " )"
myWindAr(j, 0) = myPlace
myWindAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myWindAr(j, 2) = Replace(myRng(i + 4, 5), " )", "")
myWindAr(j, 3) = myRng(i + 4, 4)
Case Right(myRng(i + 4, 4), 2) = " )"
myWindAr(j, 0) = myPlace
myWindAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myWindAr(j, 2) = myRng(i + 4, 5)
myWindAr(j, 3) = Replace(myRng(i + 4, 4), " )", "")
Case Else
myWindAr(j, 0) = myPlace
myWindAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myWindAr(j, 2) = myRng(i + 4, 5)
myWindAr(j, 3) = myRng(i + 4, 4)
End Select
j = j + 1
If j > 52703 Then Exit For
Next i
For i = 1 To 144
Select Case True
Case myRng(i + 4, 2) = "///" _
Or myRng(i + 4, 2) = "#" _
Or myRng(i + 4, 2) = "" _
Or Right(myRng(i + 4, 2), 2) = " ]" _
Or myRng(i + 4, 2) = "−" _
Or myRng(i + 4, 2) = "×"
m = m - 1
Case Right(myRng(i + 4, 2), 2) = " )"
myRainAr(m, 0) = myPlace
myRainAr(m, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myRainAr(m, 2) = myRng(i + 4, 2)
Case Else
myRainAr(m, 0) = myPlace
myRainAr(m, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myRainAr(m, 2) = myRng(i + 4, 2)
End Select
m = m + 1
If m > 52703 Then Exit For
Next i
For i = 1 To 144
Select Case True
Case myRng(i + 4, 3) = "///" _
Or myRng(i + 4, 3) = "#" _
Or myRng(i + 4, 3) = "" _
Or Right(myRng(i + 4, 3), 2) = " ]" _
Or myRng(i + 4, 3) = "−" _
Or myRng(i + 4, 3) = "×"
n = n - 1
Case Right(myRng(i + 4, 2), 3) = " )"
myKionAr(n, 0) = myPlace
myKionAr(n, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myKionAr(n, 2) = myRng(i + 4, 3)
Case Else
myKionAr(n, 0) = myPlace
myKionAr(n, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myKionAr(n, 2) = myRng(i + 4, 3)
End Select
n = n + 1
If n > 52703 Then Exit For
Next i
For i = 1 To 144
Select Case True
Case myRng(i + 4, 8) = "///" _
Or myRng(i + 4, 8) = "#" _
Or myRng(i + 4, 8) = "" _
Or Right(myRng(i + 4, 8), 2) = " ]" _
Or myRng(i + 4, 8) = "−" _
Or myRng(i + 4, 8) = "×"
k = k - 1
Case Right(myRng(i + 4, 8), 2) = " )"
mySunAr(k, 0) = myPlace
mySunAr(k, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
mySunAr(k, 2) = myRng(i + 4, 8)
Case Else
mySunAr(k, 0) = myPlace
mySunAr(k, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
mySunAr(k, 2) = myRng(i + 4, 8)
End Select
k = k + 1
If k > 52703 Then Exit For
Next i
Application.DisplayAlerts = False
mySht.Delete
Application.DisplayAlerts = True
Next myDay
Next myMonth
Set mySht = Worksheets.Add
With mySht
.Name = myYear & "年風向風速"
.Range("$A$1") = "Point"
.Range("$B$1") = "Date_Time"
.Range("$C$1") = "Direction"
.Range("$D$1") = "Average_Speed"
.Range("$A$2:$D$52705") = myWindAr
End With
Set mySht = Worksheets.Add
With mySht
.Name = myYear & "年降水量"
.Range("$A$1") = "Point"
.Range("$B$1") = "Date_Time"
.Range("$C$1") = "Precipitation"
.Range("$A$2:$C$52705") = myRainAr
End With
Set mySht = Worksheets.Add
With mySht
.Name = myYear & "年気温"
.Range("$A$1") = "Point"
.Range("$B$1") = "Date_Time"
.Range("$C$1") = "Temperature"
.Range("$A$2:$C$52705") = myKionAr
End With
Set mySht = Worksheets.Add
With mySht
.Name = myYear & "年日照時間"
.Range("$A$1") = "Point"
.Range("$B$1") = "Date_Time"
.Range("$C$1") = "Sunshine"
.Range("$A$2:$C$52705") = mySunAr
End With
Debug.Print myYear & " " & Timer - myTime
Set myRng = Nothing
Set mySht = Nothing
Application.ScreenUpdating = True
End Sub
気象庁のホームページから風向風速，降水量，気温のデータをダウンロードする
2011年9月6日の記事では，気象庁のホームページから風向と風速をダウンロードするコードを紹介しました．今回は同様に，指定した年の前月までの降水量と気温の10分ごとのデータをダウンロードするコードを紹介します．
Option ExplicitSub WEBQUERY()
Dim mySht As Worksheet
Dim myAnswer As Variant
Dim myWindAr(52703, 3) As Variant
Dim myRainAr(52703, 2) As Variant
Dim myKionAr(52703, 2) As Variant
Dim myURL As String
Dim myYear As Integer
Dim myMonth As Integer
Dim myDay As Integer
Dim myDate As Date
Dim tmpDate As Date
Dim myTime As Single
Dim myRng As Range
Dim i As Integer
Dim j As Long
Dim m As Long
Dim n As Long
Dim myPlace As StringApplication.ScreenUpdating = False
j = 0
m = 0
n = 0
myDate = Date
myAnswer = Application.InputBox(Prompt:="1994から今年の間の西暦年を4桁で入力してください", Default:=Year(myDate), Type:=1)
If TypeName(myAnswer) = "Boolean" Then Exit Sub
If myAnswer < 1994 Or myAnswer > Year(myDate) Then Exit Sub
myYear = myAnswer
myTime = Timer
For myMonth = 1 To 12
On Error Resume Next
If DateSerial(myYear, myMonth, 1) - DateSerial(Year(myDate), Month(myDate), 1) >= 0 Then
Exit For
End If
On Error GoTo 0
For myDay = 1 To 31
On Error Resume Next
tmpDate = DateValue(myYear & "/" & myMonth & "/" & myDay)
If Err.Number <> 0 Then
Exit For
End If
On Error GoTo 0
myURL = "URL;http://www.data.jma.go.jp/obd/stats/etrn/view/10min_a1.php?prec_no=55&prec_ch=%95x%8ER%8C%A7&block_no=0552&block_ch=%93v%94g&year=" & myYear & "&month=" & myMonth & "&day=" & myDay
Set mySht = Worksheets.Add
Set myRng = mySht.Range("$A$1")
With mySht.QueryTables.Add(Connection:=myURL, Destination:=myRng)
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.Refresh BackgroundQuery:=False
End With
myPlace = Application.WorksheetFunction.Replace(mySht.Range("$A$1"), Application.WorksheetFunction.Find(" ", mySht.Range("$A$1")), Len(mySht.Range("$A$1")), "")
For i = 1 To 144
Select Case True
Case myRng(i + 4, 5) = "///" Or myRng(i + 4, 5) = "#" Or myRng(i + 4, 4) = "///" Or myRng(i + 4, 4) = "#"
j = j - 1
Case Right(myRng(i + 4, 5), 2) = " )" And Right(myRng(i + 4, 4), 2) = " )"
myWindAr(j, 0) = myPlace
myWindAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myWindAr(j, 2) = Replace(myRng(i + 4, 5), " )", "")
myWindAr(j, 3) = Replace(myRng(i + 4, 4), " )", "")
Case Right(myRng(i + 4, 5), 2) = " )"
myWindAr(j, 0) = myPlace
myWindAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myWindAr(j, 2) = Replace(myRng(i + 4, 5), " )", "")
myWindAr(j, 3) = myRng(i + 4, 4)
Case Right(myRng(i + 4, 4), 2) = " )"
myWindAr(j, 0) = myPlace
myWindAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myWindAr(j, 2) = myRng(i + 4, 5)
myWindAr(j, 3) = Replace(myRng(i + 4, 4), " )", "")
Case Else
myWindAr(j, 0) = myPlace
myWindAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myWindAr(j, 2) = myRng(i + 4, 5)
myWindAr(j, 3) = myRng(i + 4, 4)
End Select
j = j + 1
If j > 52703 Then Exit For
Next i
For i = 1 To 144
Select Case True
Case myRng(i + 4, 2) = "///" Or myRng(i + 4, 2) = "#"
m = m - 1
Case Right(myRng(i + 4, 2), 2) = " )"
myRainAr(m, 0) = myPlace
myRainAr(m, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myRainAr(m, 2) = myRng(i + 4, 2)
Case Else
myRainAr(m, 0) = myPlace
myRainAr(m, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myRainAr(m, 2) = myRng(i + 4, 2)
End Select
m = m + 1
If m > 52703 Then Exit For
Next i
For i = 1 To 144
Select Case True
Case myRng(i + 4, 3) = "///" Or myRng(i + 4, 3) = "#"
n = n - 1
Case Right(myRng(i + 4, 2), 3) = " )"
myKionAr(n, 0) = myPlace
myKionAr(n, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myKionAr(n, 2) = myRng(i + 4, 3)
Case Else
myKionAr(n, 0) = myPlace
myKionAr(n, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myKionAr(n, 2) = myRng(i + 4, 3)
End Select
n = n + 1
If n > 52703 Then Exit For
Next i
Application.DisplayAlerts = False
mySht.Delete
Application.DisplayAlerts = True
Next myDay
Next myMonth
Set mySht = Worksheets.Add
With mySht
.Name = myYear & "年風向風速"
.Range("$A$1") = "Point"
.Range("$B$1") = "Date_Time"
.Range("$C$1") = "Direction"
.Range("$D$1") = "Average_Speed"
.Range("$A$2:$D$52705") = myWindAr
End With
Set mySht = Worksheets.Add
With mySht
.Name = myYear & "年降水量"
.Range("$A$1") = "Point"
.Range("$B$1") = "Date_Time"
.Range("$C$1") = "Precipitation"
.Range("$A$2:$C$52705") = myRainAr
End With
Set mySht = Worksheets.Add
With mySht
.Name = myYear & "年気温"
.Range("$A$1") = "Point"
.Range("$B$1") = "Date_Time"
.Range("$C$1") = "Temperature"
.Range("$A$2:$C$52705") = myKionAr
End With
Debug.Print Timer - myTime
Set myRng = Nothing
Set mySht = Nothing
Application.ScreenUpdating = True
End Sub
気象庁のホームページから気象データをダウンロードする
2011年8月13日の記事では簡易風配図の作成方法を述べました．今回は気象庁のホームページから特定観測地点の10分ごとの風向と平均風速をダウンロードする方法を述べます．
EXCEL 2010のWebクエリを使用します．
手動で覗いてみたところ，1時間おきのデータは1976年2月29日以降のみです．10分おきのデータは1994年4月1日以降のみです．機器の故障か不明ですが一部データの欠損があり，"///"，"#"というテキストで表現されています．
1994年から2011年まで処理した結果，イミディエイトウィンドウには経過時間が以下のように出力されました．かなり時間のかかる処理です．
Option ExplicitSub WEBQUERY()
Dim mySht As Worksheet
Dim myAnswer As Variant
Dim myAr(52703, 3) As Variant
Dim myURL As String
Dim myYear As Integer
Dim myMonth As Integer
Dim myDay As Integer
Dim myDate As Date
Dim tmpDate As Date
Dim myTime As Single
Dim myRng As Range
Dim i As Integer
Dim j As Long
Dim myPlace As StringApplication.ScreenUpdating = False
j = 0
myDate = Date
myAnswer = Application.InputBox(Prompt:="1994から今年の間の西暦年を4桁で入力してください", Default:=Year(myDate), Type:=1)
If TypeName(myAnswer) = "Boolean" Then Exit Sub
If myAnswer < 1994 Or myAnswer > Year(myDate) Then Exit Sub
myYear = myAnswer
myTime = Timer
For myMonth = 1 To 12
On Error Resume Next
If DateSerial(myYear, myMonth, 1) - DateSerial(Year(myDate), Month(myDate), 1) >= 0 Then
Exit For
End If
On Error GoTo 0
For myDay = 1 To 31
On Error Resume Next
tmpDate = DateValue(myYear & "/" & myMonth & "/" & myDay)
If Err.Number <> 0 Then
Exit For
End If
On Error GoTo 0
myURL = "URL;http://www.data.jma.go.jp/obd/stats/etrn/view/10min_a1.php?prec_no=55&prec_ch=%95x%8ER%8C%A7&block_no=0552&block_ch=%93v%94g&year=" & myYear & "&month=" & myMonth & "&day=" & myDay
Set mySht = Worksheets.Add
Set myRng = mySht.Range("$A$1")
With mySht.QueryTables.Add(Connection:=myURL, Destination:=myRng)
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.Refresh BackgroundQuery:=False
End With
myPlace = Application.WorksheetFunction.Replace(mySht.Range("$A$1"), Application.WorksheetFunction.Find(" ", mySht.Range("$A$1")), Len(mySht.Range("$A$1")), "")
For i = 1 To 144
Select Case True
Case myRng(i + 4, 5) = "///" Or myRng(i + 4, 5) = "#" Or myRng(i + 4, 4) = "///" Or myRng(i + 4, 4) = "#"
j = j - 1
Case Right(myRng(i + 4, 5), 2) = " )" And Right(myRng(i + 4, 4), 2) = " )"
myAr(j, 0) = myPlace
myAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myAr(j, 2) = Replace(myRng(i + 4, 5), " )", "")
myAr(j, 3) = Replace(myRng(i + 4, 4), " )", "")
Case Right(myRng(i + 4, 5), 2) = " )"
myAr(j, 0) = myPlace
myAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myAr(j, 2) = Replace(myRng(i + 4, 5), " )", "")
myAr(j, 3) = myRng(i + 4, 4)
Case Right(myRng(i + 4, 4), 2) = " )"
myAr(j, 0) = myPlace
myAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myAr(j, 2) = myRng(i + 4, 5)
myAr(j, 3) = Replace(myRng(i + 4, 4), " )", "")
Case Else
myAr(j, 0) = myPlace
myAr(j, 1) = DateSerial(myYear, myMonth, myDay) + myRng.Cells(i + 4, 1) - myRng.Cells(5, 1)
myAr(j, 2) = myRng(i + 4, 5)
myAr(j, 3) = myRng(i + 4, 4)
End Select
j = j + 1
If j > 52703 Then Exit For
Next i
Application.DisplayAlerts = False
mySht.Delete
Application.DisplayAlerts = True
Next myDay
Next myMonth
Set mySht = Worksheets.Add
mySht.Name = myYear & "年風向風速"
mySht.Range("$A$1") = "Point"
mySht.Range("$B$1") = "Date_Time"
mySht.Range("$C$1") = "Direction"
mySht.Range("$D$1") = "Average_Speed"
mySht.Range("$A$2:$D$52705") = myAr
Debug.Print Timer - myTime
Set myRng = Nothing
Set mySht = Nothing
Application.ScreenUpdating = True
End Sub
801.2031
758.6914
788.6172
791.7344
776.7383
774.5156
798.3633
793.9727
619.7734
571.7656
572.5703
658.7539
800.5938
712.2578
754.4609
585.2148
816.7813
370.3789
フラット35の団体信用保険の保険料
前回は10年償還国債金利の月別平均値が翌月のフラット35の金利と相関すると述べました．今回は団体信用保険の保険料（特約料とも言いますが）がどのようにして決まるか述べます．
下記サイトで保険料のシミュレーションが出来ます．
http://www.jhf.go.jp/simulation_danshin/index.html
上図を見れば明らかですが，相関係数が1になっており，完全に関数従属性があると断言してよいでしょう．横軸は元金残高の1年分の平均値，縦軸は保険料です．回帰式はy=0.0036x+782.76となっています．標本数を増やすと定額分は変動します．
追記：シミュレーションで借入金額を変更してみると，定率分は0.36%で変化ないものの，定額分が変動します．「定額分は700円に近づきます」の記述を訂正しました．
国債金利とフラット35の金利
前回は10年物国債金利の月別平均値を求めました．今回は私が取引している銀行のフラット35の金利を提示して両者の関係を比較します．
フラット35は住宅金融公庫が各銀行に提供している全期間金利固定型の住宅ローンです．今回私が取引している銀行では2007年10月から20年以下のローンと21年以上35年以下のローンとに金利が分かれました．そのデータを以下に提示します．
| 金利更新日 | 前月国債金利平均 | 20年以下 | 21年以上 |
|---|---|---|---|
| 2007/10/1 | 1.609 | 2.98 | 3.18 |
| 2007/11/1 | 1.66 | 2.76 | 2.97 |
| 2007/12/1 | 1.531 | 2.65 | 2.84 |
| 2008/1/1 | 1.527 | 2.75 | 2.96 |
| 2008/2/1 | 1.427 | 2.66 | 2.9 |
| 2008/3/1 | 1.453 | 2.68 | 2.89 |
| 2008/4/1 | 1.309 | 2.54 | 2.78 |
| 2008/5/1 | 1.409 | 2.82 | 3.09 |
| 2008/6/1 | 1.677 | 2.91 | 3.19 |
| 2008/7/1 | 1.753 | 2.89 | 3.12 |
| 2008/8/1 | 1.612 | 2.83 | 3.06 |
| 2008/9/1 | 1.469 | 2.65 | 2.9 |
| 2008/10/1 | 1.486 | 2.7 | 2.91 |
| 2008/11/1 | 1.506 | 2.8 | 3.03 |
| 2008/12/1 | 1.477 | 2.78 | 3.02 |
| 2009/1/1 | 1.32 | 2.78 | 3.02 |
| 2009/2/1 | 1.258 | 2.91 | 3.16 |
| 2009/3/1 | 1.307 | 2.89 | 3.12 |
| 2009/4/1 | 1.3 | 2.96 | 3.19 |
| 2009/5/1 | 1.431 | 3.06 | 3.31 |
| 2009/6/1 | 1.45 | 2.99 | 3.23 |
| 2009/7/1 | 1.468 | 2.86 | 3.06 |
| 2009/8/1 | 1.354 | 2.8 | 3 |
| 2009/9/1 | 1.39 | 2.73 | 2.93 |
| 2009/10/1 | 1.316 | 2.69 | 2.88 |
| 2009/11/1 | 1.334 | 2.71 | 2.93 |
| 2009/12/1 | 1.367 | 2.64 | 2.84 |
| 2010/1/1 | 1.263 | 2.59 | 2.81 |
| 2010/2/1 | 1.336 | 2.63 | 2.84 |
| 2010/3/1 | 1.349 | 2.58 | 2.79 |
| 2010/4/1 | 1.344 | 2.61 | 2.79 |
| 2010/5/1 | 1.358 | 2.53 | 2.74 |
| 2010/6/1 | 1.293 | 2.41 | 2.62 |
| 2010/7/1 | 1.206 | 2.3 | 2.52 |
| 2010/8/1 | 1.107 | 2.02 | 2.23 |
| 2010/9/1 | 1.002 | 1.87 | 2.06 |
| 2010/10/1 | 1.062 | 2.02 | 2.23 |
| 2010/11/1 | 0.899 | 1.94 | 2.19 |
| 2010/12/1 | 1.053 | 2.19 | 2.44 |
| 2011/1/1 | 1.191 | 2.21 | 2.45 |
| 2011/2/1 | 1.214 | 2.31 | 2.59 |
| 2011/3/1 | 1.29 | 2.3 | 2.58 |
| 2011/4/1 | 1.249 | 2.34 | 2.67 |
| 2011/5/1 | 1.273 | 2.32 | 2.67 |
| 2011/6/1 | 1.154 | 2.18 | 2.53 |
| 2011/7/1 | 1.13 | 2.1 | 2.43 |
| 2011/8/1 | 1.123 | 2.07 | 2.39 |
まず，20年以下の金利を横軸，21年以上の金利を縦軸に散布図を描き，回帰直線を描きます．
標本数と相関係数から，両者の間には強い正相関が見られます．
次に前月国債金利平均値を横軸に，20年以下の金利を縦軸に散布図を描き，同様に回帰直線を描きます．
最後に前月国債金利平均値を横軸に，21年以上の金利を縦軸に散布図を描き，同様に回帰直線を描きます．
国債金利平均値とフラット35の金利の間には正相関がありそうです．この記事を書いているのは8月28日です．既に8月の国債金利は財務省が公開している資料がありますので，上で求めた回帰式から9月の金利を予測してみます．厳密には8月29日から8月31日の金利も必要ですが，多少抜け落ちても大勢に影響はないでしょう．
| 基準日 | 10年 |
|---|---|
| H23.8.1 | 1.095 |
| H23.8.2 | 1.057 |
| H23.8.3 | 1.028 |
| H23.8.4 | 1.033 |
| H23.8.5 | 1.019 |
| H23.8.8 | 1.026 |
| H23.8.9 | 1.061 |
| H23.8.10 | 1.06 |
| H23.8.11 | 1.056 |
| H23.8.12 | 1.065 |
| H23.8.15 | 1.062 |
| H23.8.16 | 1.057 |
| H23.8.17 | 1.044 |
| H23.8.18 | 1.02 |
| H23.8.19 | 1.01 |
| H23.8.22 | 1.012 |
| H23.8.23 | 1.036 |
| H23.8.24 | 1.037 |
| H23.8.25 | 1.062 |
EXCELで以下のように数式を入力します．
| A | B | C | |
|---|---|---|---|
| 1 | 国債金利平均値 | 20年以下 | 21年以上金利 |
| 2 | 1.044 | =1.4157*A2+0.6726 | =1.3549*A2+0.9913 |
答えは以下です．
| A | B | C | |
|---|---|---|---|
| 1 | 国債金利平均値 | 20年以下 | 21年以上金利 |
| 2 | 1.044 | 2.150 | 2.410 |
さて，当たるでしょうか．お断りですが，ここに示した情報は私見です．提示した情報をご利用の際はあくまでも自己責任でお願いします．またフラット35の金利は銀行によって異なります．当たらなかったからと言って私に文句を言わないでください．
国債金利と住宅ローン金利
フラット３５の金利は融資申し込み時ではなく，融資執行時に決まります．毎月第2営業日の正午頃，その月の金利が発表されます．この金利を予測しようと試みました．
住宅ローン金利は10年物の国債金利に最も影響を受けると言われています．国債金利は財務省のホームページにあります．
http://www.mof.go.jp/jgbs/reference/interest_rate/kako.htm
Microsoft EXCEL 2010のWebクエリからこのページを読み込みます．『過去の金利情報』に『平成12年（2000年）〜平成21年（2009年）』および『平成22年（2010年）〜』のリンクがあるのでこれをそれぞれクリックすると，ファイルのダウンロードダイアログが開くので『保存』をクリックしてcsvファイルを保存します．ファイル名は何もしなければそれぞれ『jgbcm_2000-2009.csv』および『jgbcm_2010.csv』となっています．
この2つのcsvファイルを開いて『基準日』『10』以外の列を削除しcsvファイルのまま保存します．『jgbcm_2010.csv』ファイルを開いて全データをコピーし『jgbcm_2000-2009.csv』ファイルの末尾にペーストしcsvファイルのまま保存します．これで準備ができました．
SQL Serverから直接インポートしても良いのですが，なぜか私の環境では上手くいきません．やむを得ずMicrosoft Accessに一旦吸い上げてからアップサイジングウィザードを使用してSQL Serverに取り込みました．この際Access上でカラム名をそれぞれ『BusinessDay』，『Rate』とし，テーブル名を『T_RATE』としておきます．データベース名は『FINANCEDB』としておきます．
さて，ここからが本題です．私は「フラット35の金利は前月の国債金利の平均値に相関する」と予測しました．まず国債金利の平均値を求めます．
以下が結果です．
USE FINANCEDB;
GO
SELECT YEAR(BusinessDay) AS 年
, MONTH(BusinessDay) AS 月
, CONVERT(DECIMAL(6, 3), AVG(Rate)) AS 平均値
, CONVERT(DECIMAL(6, 3), STDEVP(Rate)) AS 標準偏差
FROM T_RATE
GROUP BY YEAR(BusinessDay), MONTH(BusinessDay)
ORDER BY YEAR(BusinessDay), MONTH(BusinessDay);
| 年 | 月 | 平均値 | 標準偏差 |
|---|---|---|---|
| 2000 | 1 | 1.708 | 0.048 |
| 2000 | 2 | 1.799 | 0.036 |
| 2000 | 3 | 1.814 | 0.038 |
| 2000 | 4 | 1.749 | 0.031 |
| 2000 | 5 | 1.697 | 0.025 |
| 2000 | 6 | 1.675 | 0.029 |
| 2000 | 7 | 1.699 | 0.028 |
| 2000 | 8 | 1.749 | 0.077 |
| 2000 | 9 | 1.871 | 0.035 |
| 2000 | 10 | 1.821 | 0.017 |
| 2000 | 11 | 1.749 | 0.063 |
| 2000 | 12 | 1.625 | 0.038 |
| 2001 | 1 | 1.503 | 0.064 |
| 2001 | 2 | 1.402 | 0.045 |
| 2001 | 3 | 1.179 | 0.059 |
| 2001 | 4 | 1.346 | 0.065 |
| 2001 | 5 | 1.282 | 0.036 |
| 2001 | 6 | 1.179 | 0.031 |
| 2001 | 7 | 1.313 | 0.047 |
| 2001 | 8 | 1.339 | 0.031 |
| 2001 | 9 | 1.374 | 0.032 |
| 2001 | 10 | 1.366 | 0.023 |
| 2001 | 11 | 1.341 | 0.024 |
| 2001 | 12 | 1.350 | 0.023 |
| 2002 | 1 | 1.431 | 0.034 |
| 2002 | 2 | 1.520 | 0.019 |
| 2002 | 3 | 1.443 | 0.027 |
| 2002 | 4 | 1.399 | 0.021 |
| 2002 | 5 | 1.379 | 0.014 |
| 2002 | 6 | 1.348 | 0.023 |
| 2002 | 7 | 1.301 | 0.027 |
| 2002 | 8 | 1.251 | 0.045 |
| 2002 | 9 | 1.142 | 0.080 |
| 2002 | 10 | 1.092 | 0.046 |
| 2002 | 11 | 0.987 | 0.023 |
| 2002 | 12 | 0.963 | 0.033 |
| 2003 | 1 | 0.834 | 0.041 |
| 2003 | 2 | 0.832 | 0.029 |
| 2003 | 3 | 0.730 | 0.022 |
| 2003 | 4 | 0.663 | 0.033 |
| 2003 | 5 | 0.579 | 0.026 |
| 2003 | 6 | 0.552 | 0.102 |
| 2003 | 7 | 0.991 | 0.070 |
| 2003 | 8 | 1.147 | 0.216 |
| 2003 | 9 | 1.458 | 0.099 |
| 2003 | 10 | 1.408 | 0.049 |
| 2003 | 11 | 1.393 | 0.083 |
| 2003 | 12 | 1.344 | 0.051 |
| 2004 | 1 | 1.333 | 0.032 |
| 2004 | 2 | 1.255 | 0.030 |
| 2004 | 3 | 1.344 | 0.060 |
| 2004 | 4 | 1.504 | 0.036 |
| 2004 | 5 | 1.497 | 0.023 |
| 2004 | 6 | 1.759 | 0.109 |
| 2004 | 7 | 1.790 | 0.034 |
| 2004 | 8 | 1.660 | 0.084 |
| 2004 | 9 | 1.503 | 0.057 |
| 2004 | 10 | 1.493 | 0.048 |
| 2004 | 11 | 1.479 | 0.035 |
| 2004 | 12 | 1.394 | 0.033 |
| 2005 | 1 | 1.368 | 0.026 |
| 2005 | 2 | 1.389 | 0.057 |
| 2005 | 3 | 1.435 | 0.052 |
| 2005 | 4 | 1.314 | 0.041 |
| 2005 | 5 | 1.278 | 0.023 |
| 2005 | 6 | 1.232 | 0.033 |
| 2005 | 7 | 1.251 | 0.032 |
| 2005 | 8 | 1.413 | 0.040 |
| 2005 | 9 | 1.362 | 0.052 |
| 2005 | 10 | 1.529 | 0.024 |
| 2005 | 11 | 1.519 | 0.057 |
| 2005 | 12 | 1.520 | 0.027 |
| 2006 | 1 | 1.464 | 0.041 |
| 2006 | 2 | 1.564 | 0.024 |
| 2006 | 3 | 1.677 | 0.054 |
| 2006 | 4 | 1.893 | 0.039 |
| 2006 | 5 | 1.910 | 0.058 |
| 2006 | 6 | 1.861 | 0.046 |
| 2006 | 7 | 1.911 | 0.054 |
| 2006 | 8 | 1.819 | 0.076 |
| 2006 | 9 | 1.671 | 0.027 |
| 2006 | 10 | 1.756 | 0.040 |
| 2006 | 11 | 1.710 | 0.027 |
| 2006 | 12 | 1.638 | 0.030 |
| 2007 | 1 | 1.707 | 0.030 |
| 2007 | 2 | 1.711 | 0.032 |
| 2007 | 3 | 1.619 | 0.033 |
| 2007 | 4 | 1.675 | 0.019 |
| 2007 | 5 | 1.682 | 0.040 |
| 2007 | 6 | 1.881 | 0.043 |
| 2007 | 7 | 1.885 | 0.043 |
| 2007 | 8 | 1.672 | 0.077 |
| 2007 | 9 | 1.609 | 0.063 |
| 2007 | 10 | 1.660 | 0.050 |
| 2007 | 11 | 1.531 | 0.051 |
| 2007 | 12 | 1.527 | 0.035 |
| 2008 | 1 | 1.427 | 0.042 |
| 2008 | 2 | 1.453 | 0.031 |
| 2008 | 3 | 1.309 | 0.039 |
| 2008 | 4 | 1.409 | 0.084 |
| 2008 | 5 | 1.677 | 0.066 |
| 2008 | 6 | 1.753 | 0.070 |
| 2008 | 7 | 1.612 | 0.045 |
| 2008 | 8 | 1.469 | 0.035 |
| 2008 | 9 | 1.486 | 0.018 |
| 2008 | 10 | 1.506 | 0.057 |
| 2008 | 11 | 1.477 | 0.044 |
| 2008 | 12 | 1.320 | 0.083 |
| 2009 | 1 | 1.258 | 0.026 |
| 2009 | 2 | 1.307 | 0.023 |
| 2009 | 3 | 1.300 | 0.020 |
| 2009 | 4 | 1.431 | 0.030 |
| 2009 | 5 | 1.450 | 0.024 |
| 2009 | 6 | 1.468 | 0.055 |
| 2009 | 7 | 1.354 | 0.037 |
| 2009 | 8 | 1.390 | 0.051 |
| 2009 | 9 | 1.316 | 0.018 |
| 2009 | 10 | 1.334 | 0.055 |
| 2009 | 11 | 1.367 | 0.062 |
| 2009 | 12 | 1.263 | 0.025 |
| 2010 | 1 | 1.336 | 0.011 |
| 2010 | 2 | 1.349 | 0.017 |
| 2010 | 3 | 1.344 | 0.029 |
| 2010 | 4 | 1.358 | 0.032 |
| 2010 | 5 | 1.293 | 0.028 |
| 2010 | 6 | 1.206 | 0.047 |
| 2010 | 7 | 1.107 | 0.029 |
| 2010 | 8 | 1.002 | 0.044 |
| 2010 | 9 | 1.062 | 0.070 |
| 2010 | 10 | 0.899 | 0.031 |
| 2010 | 11 | 1.053 | 0.086 |
| 2010 | 12 | 1.191 | 0.041 |
| 2011 | 1 | 1.214 | 0.027 |
| 2011 | 2 | 1.290 | 0.037 |
| 2011 | 3 | 1.249 | 0.032 |
| 2011 | 4 | 1.273 | 0.038 |
| 2011 | 5 | 1.154 | 0.019 |
| 2011 | 6 | 1.130 | 0.019 |
| 2011 | 7 | 1.123 | 0.034 |
今回はここまでです．次回は実際に私が取引している銀行の利率を使ってみましょう．
EXCELで風配図を自作する
外構設計に際して注意すべき点の一つに卓越風があります．卓越風は恒常風とも言い，住宅内の風の通り道を考えたり，生垣や樹木の設計に際して考慮する必要があります．
以前は大阪市立大学の三木信博先生の作成した風配図が参照できたのですが，残念ながら現在リンク切れとなっています．そこで気象庁の過去の気象データから作成することにしました．
http://www.data.jma.go.jp/obd/stats/etrn/index.php
このページで住んでいる地域，年月日，データの種類でフィルタを掛けて絞り込みます．結果として5種類の表が抽出されます．主な要素，詳細（降水量），詳細（気温），詳細（風・日照），詳細（雪）です．このうち詳細（風・日照）ボタンを押し，結果をEXCELに展開し，最多風向と平均風速以外削除します．
ここで方角を散布図に変換するために三角関数を使用します．通常私たちが慣れ親しんでいる角度は度数法といって，時計の3時の方向が0度，左回りに一回転して360度ですが，三角関数ではラジアンという弧度法の単位に変換する必要があります．仮に方角がA列，度数法がB列に入力してあり，ラジアンをC列に入力したいとします．さらに風速1m/sの風を東西方向と南北方向の2成分に分解します．
この式をコピーペーストすると，結果は以下のようになります．北と南の東西風速は当然0になる筈ですが計算誤差の結果0になっていません．また西の南北風速が0になっていないのも同じ理由です．
C2=RADIANS(B2)
D2=COS(C2)
E2=SIN(C2)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 方角 | 度数法 | ラジアン | 東西 | 南北 |
| 2 | 東 | 0 | 0 | 1 | 0 |
| 3 | 東北東 | 22.5 | 0.392699082 | 0.923879533 | 0.382683432 |
| 4 | 北東 | 45 | 0.785398163 | 0.707106781 | 0.707106781 |
| 5 | 北北東 | 67.5 | 1.178097245 | 0.382683432 | 0.923879533 |
| 6 | 北 | 90 | 1.570796327 | 6.12574E-17 | 1 |
| 7 | 北北西 | 112.5 | 1.963495408 | -0.382683432 | 0.923879533 |
| 8 | 北西 | 125 | 2.181661565 | -0.573576436 | 0.819152044 |
| 9 | 西北西 | 147.5 | 2.574360647 | -0.843391446 | 0.537299608 |
| 10 | 西 | 180 | 3.141592654 | -1 | 1.22515E-16 |
| 11 | 西南西 | 202.5 | 3.534291735 | -0.923879533 | -0.382683432 |
| 12 | 南西 | 225 | 3.926990817 | -0.707106781 | -0.707106781 |
| 13 | 南南西 | 247.5 | 4.319689899 | -0.382683432 | -0.923879533 |
| 14 | 南 | 270 | 4.71238898 | -1.83772E-16 | -1 |
| 15 | 南南東 | 292.5 | 5.105088062 | 0.382683432 | -0.923879533 |
| 16 | 南東 | 315 | 5.497787144 | 0.707106781 | -0.707106781 |
| 17 | 東南東 | 337.5 | 5.890486225 | 0.923879533 | -0.382683432 |
後は平均風速を東西と南北に分解して散布図にするだけです．1月の平均風速がB22に，風向がH22に入力されているとし，東西風速をK22，南北風速をL22に入力するとします．
この式を12ヶ月分コピーペーストします．データ系列の指定でX軸にK列を指定し，Y軸にL列を指定して散布図を描くと簡易風配図の出来上がりです．
K22=INDEX($A$2:$E$17, MATCH(H22, $A$2:$A$17, 0), 4)*B22
L22=INDEX($A$2:$E$17, MATCH(H22, $A$2:$A$17, 0), 5)*B22
※追記
風配図の正確な書き方は以下のドキュメントに詳しく記されています．p81から始まるドキュメントのp118から記述があります．
www2.kaiyodai.ac.jp/~murayama/research/kisyou_2.doc
