お久しぶりです。Kanchan(@kanchanblog)です。
不動産投資をしていると、家賃相場が知りたいと思うことが度々あります。
SUUMOやat homeなどポータルサイトで最寄り駅や間取り、築年数などを絞って検索し、持っている、あるいは買おうとしている物件と似たような物件の募集家賃を見て判断している方が多いのではないでしょうか。
売り物件の「表面利回り」で騙される主な原因は、想定賃料と実際の埋められる賃料との乖離による場合が多いですから、正確な賃料相場は誰もが知りたいと思うはず。
ということで、SUUMOで検索した賃貸物件の一覧をExcelに入力してみたいと思いました。
地道な手入力
電車の沿線・駅から、エリア(住所)から、地図から、とSUUMOには色々な検索機能が付いています。
駅が近い物件(土地)に評価が出やすく融資が付きやすい現実を見たこともあって、分析に使いやすい駅から徒歩圏内の賃貸物件を集めるツールを作ることにしました。
リスト化したい項目は、物件名、家賃＋共益費、築年数、駅からの距離(徒歩～分)、広さ、間取り、階数、敷金、礼金、住所としました。
集まった情報を、「家賃＋共益費」を目的変数、その他を説明変数として重回帰分析にかけたところ、殆どの駅で「築年数」「駅からの距離(分)」「広さ」が有意な説明変数となっており、情報収集してみるモチベーションがあがりました。
築古より築浅の方が家賃が高く、駅から遠いよりは近い方が家賃が高く、広い家の方が家賃が高い、という考えてみれば当たり前のことが数値化出来た訳です。
最初は手入力で1件1件入力してみましたが、150件入力するのに1時間以上かかり、自動化する必要性を感じました。
それが「SUUMOスクレイピング計画(仮)」を立てるに至ったきっかけです。
SUUMOを読み解く
SUUMOの規約を確認したところ、スクレイピングを禁止する条項はなさそうです。
（サーバーに負荷のかかるスクレイピングを行う時は対象サイトがスクレイピング禁止となっていないことは確認した方が良いです）
Googleで「〜駅 賃貸」と入力するとSUUMOで検索した結果が上位表示されてきます。
そのページに物件情報一覧が一通り載っているので、ここから情報を取得することにしました。
デベロッパーツールを使う
Webページは HTMLというプログラミング言語で書かれていることが多く、どういう構造になっているか調べる一番簡単な方法はデベロッパーツールを開くことです。
Chromeではタブの端にある設定ボタンから、その他のツール＞デベロッパーツールと開けます。ショートカットキーで、Ctrl+Shift+Iでも開けます。
欲しい情報が格納されているタグを探して、どういう法則で入っているかを見つけて、情報抽出に活かします。
セレクトモードにして、Webページをマウスで触れると要素と対応するコードがハイライトされるので見つけやすいです。
エレメントパネル（htmlの書いてある部分）のタグを一つ一つ末梢まで開いて特定してもOKです。やりやすい方法でHTMLの構造を読み解きましょう。
SUUMO賃貸住宅ページの構造
ざっと見て分かったのは、ページ上方のタブで1ページに表示できる物件数が変えられること。
ページ下方にヒットした物件数全てを表示するのに何ページ必要かが出ていること。
1ページに多く表示してある方が抽出は楽なので、50件とします。
総ヒット数と、ページ総数×50件との間に乖離があるのは、自動で重複物件と判断されるものを間引いているためでしょう。それでも同じ物件が別の仲介会社から掲載され重複していることはあるようですけどね。
検索結果を表示させたページのURLは長ったらしくなっていますが、2ページ目を表示して見てみると、1ページ目のURL＋”&page=2”となっています。
試しに1ページ目に”&page=1″を加えても同じ１ページ目が表示されました。
この法則はブラウザに渡すURLを決定するのに使えそうです。
また、デベロッパーツールを使ってサイトを見ていくと、物件の表示されているパターンが見えてきました。
ページ内の要素は主に<class>で区別されています。
検索ヒット数：”paginate_set-hit”
1つの物件を入れる箱：”cassetteitem”
物件名：”cassetteitem_content-title”
住所：”cassetteitem_detail-col1”
駅名および徒歩（分）：”cassetteitem_detail-col2”
築年数：”cassetteitem_detail-col3”
総階数：”cassetteitem_detail-col3”
その物件の階：固有のクラス割り当てがなく、<table class=”cassetteitem_other”>の中の、<tbody>の中の、<tr>の、<td>3つ目。ということで選択する工夫が必要そうです。
家賃：”cassetteitem_price–rent”
共益費：”cassetteitem_price–administration”
敷金：”cassetteitem_price–deposit”
礼金：”cassetteitem_price–gratuity”
間取り：”cassetteitem_madori”
占有面積：”cassetteitem_menseki”
表示したい項目を入れるExcelシートのフォーマットを作っておきます。
これを埋めたらゲームクリア！というのが見えてモチベーションが上がります。
スクレイピングを行う
スクレイピングの方法についてはこちらのYouTubeを参考にさせて頂きました。
VBAを使えるようにするための準備（開発タブを出す、マクロ有効なシート.xlsmで保存する）に加えて、WebBrowserを使えるようにするための準備が必要になります。
WebBrowserを使えるようにする
まずVBAProjectに右クリックで、ユーザーフォームを挿入
ツールボックスを右クリック＞コントロールの追加＞Microsoft Web Browserにチェック
ツールボックスに追加された地球儀マークのWebBrowserを選択して、Userform1の枠内に黒い四角（WebBrowser）を設置します
ここまで準備した上で、モジュールの中にVBAコードを書いていきます。
ここを省くと、コード中のUserForm1や、WebBrowserが定義されていないためエラーとなり動かなくなります。
VBAコードを書く
まず「WebBrowserにURLを渡して、HTMLを読み込むまで」を書きます。
扱いやすいようにD1セルに読み込ませたい検索ページの1ページ目のURL
D2セルに読み込ませたいページ数を入力しておきます。
Option Explicit
Sub suumo()
Dim j As Long
j = 1
Do Until j > Range("D2").Value '検索するページ数
Dim url As String '&はキャラクターコードChr(38)を使って表示
url = Range("D1").Value & Chr(38) & "page=" & j
With UserForm1.WebBrowser1
.Silent = True 'ブラウザを表示させずバックグラウンドで処理させる
.navigate (url)
'読み込み待機時間対策
Do While .Busy Or .readyState <> READYSTATE_COMPLETE
DoEvents
Loop
Dim htmldoc As HTMLDocument
Set htmldoc = .document
ここまででD1に入れた1ページ目のURLのHTMLドキュメントをhtmldocに入れることが出来ました。
続けて見つけておいた欲しい要素とそれに紐付いているclassやtagを参考にして、Excelシートに上から順番に格納していきます。
Dim length As Integer
length = htmldoc.getElementsByClassName("cassetteitem").length '1ページの物件数をlengthに入れる
Dim lastRow As Long '1列目最下行を取得する定石
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(9, 2).Value = htmldoc.getElementsByClassName("paginate_set-hit")(0).innerText 'ヒット件数を入れる
Dim i As Long
For i = 0 To (length - 1) '物件1つをi1つで処理
Cells(lastRow + 1 + i, 1).Value = htmldoc.getElementsByClassName("cassetteitem_content-title").Item(i).innerText '物件名
Cells(lastRow + 1 + i, 5).Value = htmldoc.getElementsByClassName("cassetteitem_detail-col3").Item(i).innerText '築年数&総階数
Cells(lastRow + 1 + i, 6).Value = htmldoc.getElementsByClassName("cassetteitem_detail-col2").Item(i).innerText '駅名、徒歩(分)
Cells(lastRow + 1 + i, 13).Value = htmldoc.getElementsByClassName("cassetteitem_detail-col1").Item(i).innerText '住所
Dim obj As HTMLTable '物件情報下段の表は<table>タグで囲われている
Set obj = htmldoc.getElementsBytagName("table")(i)
Cells(lastRow + 1 + i, 2).Value = obj.getElementsByClassName("cassetteitem_other-emphasis")(0).innerText '家賃。Item(0)は表の一段目ということ。
Cells(lastRow + 1 + i, 3).Value = obj.getElementsByClassName("cassetteitem_price--administration").Item(0).innerText '共益費
Cells(lastRow + 1 + i, 11).Value = obj.getElementsByClassName("cassetteitem_price--deposit").Item(0).innerText '敷金
Cells(lastRow + 1 + i, 12).Value = obj.getElementsByClassName("cassetteitem_price--gratuity").Item(0).innerText '礼金
Cells(lastRow + 1 + i, 8).Value = obj.getElementsByClassName("cassetteitem_madori").Item(0).innerText '間取り
Cells(lastRow + 1 + i, 7).Value = obj.getElementsByClassName("cassetteitem_menseki").Item(0).innerText '面積
Cells(lastRow + 1 + i, 9).Value = obj.getElementsBytagName("td").Item(2).innerText '募集物件(1件目)の階
Next i
End Withこれで一つの物件の必要な情報が一行にまとめられました。
同じ物件で階の違う複数戸の募集がされている場合、1行目だけが抽出されるようにしています。
抽出が面倒というのもありますが、新築で大量に募集がされている場合、その比重が大きくなってデータに偏りが出てしまうからという理由もあります。
一件の抽出が終わったら、For〜Next iの構文で1ページの物件数（50件）の分だけ繰り返すのは楽なのがプログラミングの良いところです。
1ページ目50件分の物件情報が書き込めたら、2ページ目50件、3ページ目50件・・・というようにjの値＝D2セルに入力したページ数になるまで繰り返し処理してくれるようにします。
それはコード冒頭で「Do Until j > Range(“D2”).Value」と書いて、ここまでの処理全体を挟んでj = j + 1と増やしてLoopすれば良いので簡単です。
j = j + 1
Loop
With Cells
.ShrinkToFit = False
.WrapText = False
End With
MsgBox ("完了しました")
End Sub
動かしてみると分かりますが、書き込みが済むと、1つのセルに複数行が入ってセルが変形してしまうので、.ShrinkToFit(文字列を縮小してセル内に収める)、.WrapText(文字列をセル幅で折り返して全体を表示する)をFalse(無効)にしてセルサイズを元に戻します。
これでコードはとりあえず完成です。
ボタンの設置
そして仕上げにボタンを設置します。
開発タブから挿入＞ボタンと押して適当な場所にボタンを設置します。
ボタンを右クリックでボタン上のテキストは自由に変えられます。
適当な駅近物件を検索したページのURLとページ数を入力してボタンを押してみます。
成功しました。
このままでは全て文字列として入力されているので、安い順に並べ替えたり、ソートするのに不便です。
文字列を数値に変換したり、余分な文字列をカットして必要な数字を取り出したりする必要がありますが、それはExcelの数式を組み合わせて使うのが有利と判断しました。
次回は体裁の整え方を解説していきたいと思います。
まとめ
最後に全てのコードを繋げたものを示します。
頻繁に使用してサーバーに負荷がかかり過ぎると企業側が対策してHTML構造を変えてスクレイピングしにくくされてしまうことがあります。
御理解の程、宜しくお願いします。
続き、表の整理とデータ分析はこちら
Option Explicit
Sub SUUMO()
Dim j As Long
j = 1
Do Until j > Range("D2").Value
Dim url As String
url = Range("D1").Value & Chr(38) & "page=" & j
With UserForm1.WebBrowser1
.Silent = True
.navigate (url)
Do While .Busy Or .readyState <> READYSTATE_COMPLETE
DoEvents
Loop
Dim htmldoc As HTMLDocument
Set htmldoc = .document
Dim length As Integer
length = htmldoc.getElementsByClassName("cassetteitem").length
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(9, 2).Value = htmldoc.getElementsByClassName("paginate_set-hit")(0).innerText '総ヒット数
Dim i As Long
For i = 0 To (length - 1)
Cells(lastRow + 1 + i, 1).Value = htmldoc.getElementsByClassName("cassetteitem_content-title").Item(i).innerText '物件名
Cells(lastRow + 1 + i, 5).Value = htmldoc.getElementsByClassName("cassetteitem_detail-col3").Item(i).innerText '築年、階建
Cells(lastRow + 1 + i, 6).Value = htmldoc.getElementsByClassName("cassetteitem_detail-col2").Item(i).innerText '駅徒歩
Cells(lastRow + 1 + i, 13).Value = htmldoc.getElementsByClassName("cassetteitem_detail-col1").Item(i).innerText '住所
Dim obj As HTMLTable
Set obj = htmldoc.getElementsBytagName("table")(i)
Cells(lastRow + 1 + i, 2).Value = obj.getElementsByClassName("cassetteitem_other-emphasis")(0).innerText '家賃
Cells(lastRow + 1 + i, 3).Value = obj.getElementsByClassName("cassetteitem_price--administration").Item(0).innerText '共益費
Cells(lastRow + 1 + i, 11).Value = obj.getElementsByClassName("cassetteitem_price--deposit").Item(0).innerText '敷金
Cells(lastRow + 1 + i, 12).Value = obj.getElementsByClassName("cassetteitem_price--gratuity").Item(0).innerText '礼金
Cells(lastRow + 1 + i, 8).Value = obj.getElementsByClassName("cassetteitem_madori").Item(0).innerText '間取り
Cells(lastRow + 1 + i, 7).Value = obj.getElementsByClassName("cassetteitem_menseki").Item(0).innerText '面積
Cells(lastRow + 1 + i, 9).Value = obj.getElementsBytagName("td").Item(2).innerText '階
Next i
End With
j = j + 1
Loop
With Cells
.ShrinkToFit = False
.WrapText = False
End With
MsgBox ("完了しました")
End Sub
