競馬成績表データベースを作る(事前準備編)

-
1.概要

競馬成績表のデータベースを作成し、過去の成績データから、いろいろ分析するのに役立てようと思います。 筆者は、実際に競馬はやっていません。競馬を題材としてデータ量が豊富なので統計的な分析手法の勉強用に データの収集を行って楽しんでいます。
今回の記事から何回かに分けての投稿を予定しています。データ収集からデータベースを作って活用するためのツール 作りまでを連載形式で紹介していきます。



2.事前準備作業

今後の予定については、以下の通りです。
    ・事前準備編 (今回)
    ・データ取得編
    ・データベース編
    ・ツール－Excel編
    ・ツール－HSP編
とします。一度に全ての記事を整理仕切れないので、分割としました。気長に付き合って下さい。
事前準備編では、競馬レースの成績表を過去分のデータまで遡って取得するために事前に指定年月の 開催日程日と各レースの結果が掲載されているページのURLを取得する必要があります。 取得プログラムは、Excel VBAマクロで作成しています。

3.利用方法

レース結果のリンクページのURLリストを取得するプログラムは、Yahoo競馬結果リンク先取得.xlsmです。 指定範囲の年月で一括でリンクリストを取得し、外部出力します。
プログラムとしては、指定した年月より開催日程表リンク先を取得して配列(listファイル配列)に格納します。 listファイル配列のURLをもとに自動巡回しながら、開催日のレース毎の成績表リンク先を取得するというものです。 取得したリンク先をシート上に表示していきます。ファイル名を日付時刻形式として、カレントディレクトリにテキスト形式で外部出力します。
取得先は、下記サイトのページより取得しています。 利用方法は、ダウンロードしたら
    (1)Yahoo競馬結果リンク先取得.xlsmを起動します。
    (2)プルダウンで開始年と開始月、終了年と終了月で取得期間(範囲)を指定します。
         ※指定範囲は、最大6ヶ月程度として、指定範囲を大きく指定しないで下さい。
             【注意】指定範囲が大きいと取得に時間もかかりサイト様に迷惑となります。
    (3)実行ボタンをクリックします。
    (4)「データ取得処理が完了しました。」のダイアログが出るまで待ちます。

4.ソースコード

ソースコードについて簡単に説明致します。
ブックを開いたときの自動処理をThisWorkbookに記述しています。プルダウンに1990年から2025年までの年月をセットします。 この処理を開始と終了の2つをセットし、デフォルト値として当年当月をセットします。Yahoo!競馬の成績表のデータベースは、1990年の1月から蓄積されています。 そんなに古いものは、いらないと思いますので、データ取得時は2000年位からのもので十分かと思います。 

' ブックを開いたときの自動処理
Private Sub Workbook_Open()
    '***** 開始統計年 (1990年～2025年までを作成) *****
    Dim CBYear(35)
    For StartYear = 0 To 35
        CBYear(StartYear) = 1990 + StartYear
    Next
    Worksheets("MAIN").BSetYear.List = CBYear
    ' 起動時のデフォルト開始年をセット
    For StartYear = 0 To 35
        If CBYear(StartYear) = Year(Date) Then
            Worksheets("MAIN").BSetYear.ListIndex = StartYear
            Exit For
        Else
            Worksheets("MAIN").BSetYear.ListIndex = 0
        End If
    Next

    ' 開始統計月
    Dim CBMonth(11) As String
    For StartMonth = 0 To 11
        CBMonth(StartMonth) = Format(1 + StartMonth, "00")
    Next
    Worksheets("MAIN").BSetMonth.List = CBMonth
    ' 起動時のデフォルト開始月をセット
    For StartMonth = 1 To 12
        If CBMonth(StartMonth) = Right("0" & Month(Date), 2) Then
            Worksheets("MAIN").BSetMonth.ListIndex = StartMonth
            Exit For
        Else
            Worksheets("MAIN").BSetMonth.ListIndex = 0
        End If
    Next

    '***** 終了統計年 (1990年～2025年までを作成) *****
    For StartYear = 0 To 35
        CBYear(StartYear) = 1990 + StartYear
    Next
    Worksheets("MAIN").ESetYear.List = CBYear
    ' 起動時のデフォルト開始年をセット
    For StartYear = 0 To 35
        If CBYear(StartYear) = Year(Date) Then
            Worksheets("MAIN").ESetYear.ListIndex = StartYear
            Exit For
        Else
            Worksheets("MAIN").ESetYear.ListIndex = 0
        End If
    Next

    ' 終了統計月
    For StartMonth = 0 To 11
        CBMonth(StartMonth) = Format(1 + StartMonth, "00")
    Next
    Worksheets("MAIN").ESetMonth.List = CBMonth
    ' 起動時のデフォルト終了月をセット
    For StartMonth = 1 To 12
        If CBMonth(StartMonth) = Right("0" & Month(Date), 2) Then
            Worksheets("MAIN").ESetMonth.ListIndex = StartMonth
            Exit For
        Else
            Worksheets("MAIN").ESetMonth.ListIndex = 0
        End If
    Next
End Sub
Sheet1(MAIN)では、コンボボックスで指定された値の取得と、指定範囲間違いなどのエラー処理をしています。 そして、当月を含む期間の月数計算をし、標準モジュール : IEAutoをコールして自動巡回させて成績表のリンクを取得します。 Sub DataDel_Click()は、データ消去ボタンがクリックされた時にシート上のデータをクリアします。 
Private Sub BRStart_Click()
   '***** 指定期間の月数を求める *****
    
    Dim dBeginDate As Date
    Dim dEndDate As Date
    Dim intMonths As Integer

    ' コンボボックスの値を変数に退避しておく
    Dim BYY As Integer
    Dim EYY As Integer
    Dim BMM As Integer
    Dim EMM As Integer

    '開始年 ... Worksheets("MAIN").BSetYear.Text
    '開始月 ... Worksheets("MAIN").BSetMonth.Text
    '終了年 ... Worksheets("MAIN").ESetYear.Text
    '終了月 ... Worksheets("MAIN").ESetMonth.Text

    BYY = Val(Worksheets("MAIN").BSetYear.Text)
    BMM = Val(Worksheets("MAIN").BSetMonth.Text)
    EYY = Val(Worksheets("MAIN").ESetYear.Text)
    EMM = Val(Worksheets("MAIN").ESetMonth.Text)

    '***** 設定年月のエラー判定 *****

    ' 開始年の判定
    If BYY > Year(Date) Then
       MsgBox "開始年の設定が間違っています。当年以前を設定して下さい。"
       Exit Sub
    End If
    ' 開始年月の判定
    If (BYY = Year(Date)) And (BMM > Month(Date)) Then
       MsgBox "開始月の設定が間違っています。当年、当月以前を設定して下さい。"
       Exit Sub
    End If
    ' 終了年の判定
    If EYY > Year(Date) Then
       MsgBox "終了年の設定が間違っています。当年以前を設定して下さい。"
       Exit Sub
    End If
    ' 終了年月の判定
    If (EYY = Year(Date)) And (EMM > Month(Date)) Then
       MsgBox "終了月の設定が間違っています。当年、当月以前を設定して下さい。"
       Exit Sub
    End If

    ' Beginning date.
    dBeginDate = DateValue(Str(BMM) & "/" & 1 & "/" & Str(BYY))
    ' Ending Date.
    dEndDate = DateValue(Str(EMM) & "/" & 1 & "/" & Str(EYY))

    ' 当月を含む期間の月数計算
    intMonths = (((Year(dEndDate) - Year(dBeginDate)) * 12) + _
       Month(dEndDate) - Month(dBeginDate)) + 1

    '***** IE自動巡回ルーチンへ *****
    Call IE_open(intMonths, BYY, BMM, EYY, EMM)
End Sub

Private Sub DataDel_Click()
    'データエリアを削除する。手抜きで5行から100000行固定です。
    Rows("5:100000").Delete Shift:=xlUp
    Range("B5").Select
End Sub
標準モジュール : Appmodでは、ブラウザの描画処理などの時間待ちの関数と最小化させるために 2つのWindows APIを定義しています。 
'※32bit版Excelを利用の場合は、下記関数のPtrSafe部分を削除して下さい。

' Sleep API宣言
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
' ShowWindow API宣言
Public Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hwindow As Long, ByVal cmdshow As Long) As Long
標準モジュール : IEAutoでは、開始年付と終了年月をパラメータとして、インターネットを自動巡回させます。 ここでは、最初に一旦、開催日程を取得してリストとして配列に格納します。 次にそのリストの配列を元に指定年月をURLのパラメータとして合成して成績表のリンクを取得するという二段構えとなっています。 取得完了後は、ファイル名を合成 (年月日時分秒)してテキスト形式でカレントディレクトリに外部出力します。 ファイル名の変更や任意の場所に出力したい場合は、184行目から189行目にファイル名のパスを指定していますので 任意に変更して下さい。 
'*********************************************************************************
'*
'*      競馬レース成績表リンク先ＵＲＬ一括取得処理
'*
'*      - IEによるインターネットサイト自動巡回 (Yahooスポーツ : 競馬) -
'*        https://keiba.yahoo.co.jp/race/result/
'*
'*        ●成績表のレース日程リンク先ＵＲＬを指定期間で取得する
'*
'*
'*********************************************************************************
'* ☆Yahoo!スポーツ競馬 URL コード体系
'*================================================================================
'*
'* ●出走表の場合
'*   https://keiba.yahoo.co.jp/race/denma/1205020806/
'*
'* ●成績表の場合
'*   https://keiba.yahoo.co.jp/race/result/1205020806/
'*
'* ●開催日程の場合
'*   https://keiba.yahoo.co.jp/schedule/list/2012/?month=1
'*
'* (例)
'*  12 ⇒ 西暦年 YY
'*  05 ⇒ 競馬場コード (東京)
'*  02 ⇒ 開催回 (02回)
'*  08 ⇒ 開催日目(08日目)
'*  06 ⇒ レース番号 (06R)
'*
'* ●パラメータ
'*   yy : 開催年
'*   xx : 競馬場コード
'*   yy : 開催回[第N回]
'*   zz : 開催日目[N日目]
'*   rr : レース番号
'*
'* ●競馬場コード
'*   01 : 札幌競馬場
'*   02 : 函館競馬場
'*   03 : 福島競馬場
'*   04 : 新潟競馬場
'*   05 : 東京競馬場
'*   06 : 中山競馬場
'*   07 : 中京競馬場
'*   08 : 京都競馬場
'*   09 : 阪神競馬場
'*   10 : 小倉競馬場
'*
'* ●過去データ(成績表)の場合
'*   2000年1月29日（土） 10:00　1回東京1日
'*   https://keiba.yahoo.co.jp/race/result/0005010101/
'*
'*********************************************************************************

'***** インターネット自動巡回 *****
Public Sub IE_open(ByVal repnum As Integer, ByVal Byear As Integer, ByVal Bmonth As Integer, ByVal Eyear As Integer, ByVal Emonth As Integer)
    Dim URL  As String
    Dim MainURL As String
    Dim StartYear As String
    Dim StartMonth As String
    Dim EndYear As String
    Dim EndMonth As String
    Dim rop As Integer
    
    On Error Resume Next

    'データエリアを削除する。手抜きで5行から100000行固定です。
    Rows("5:100000").Delete Shift:=xlUp
    Range("B5").Select

    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Visible = True

    '***** 初期起動ページ表示 *****
    URL = "https://keiba.yahoo.co.jp/schedule/list/"
    objIE.Navigate URL
  
    Do While objIE.ReadyState <> 4
       Do While objIE.Busy = True
          DoEvents
       Loop
    Loop

    '***** 最小化 = 2  最大化 =3  *****
    ret = ShowWindow(objIE.Hwnd, 2)

    ' URL設定
    MainURL = URL

    StartYear = Trim(Str(Byear))
    If Bmonth < 10 Then
       StartMonth = Trim(Format(Str(Bmonth), "00"))
       Else
       StartMonth = Trim(Str(Bmonth))
    End If
    
    '▼▼▼▼▼ ここから繰り返し処理開始 ▼▼▼▼▼
    
    Dim p As Integer
    Dim i As Integer
    Dim n As Integer
    Dim nlist(50000) As String

    '***** 自動巡回開始 (listファイルを配列に一旦退避させる) *****
    ' repnum : 指定期間の月数までループさせる
    'https://keiba.yahoo.co.jp/schedule/list/2020/?month=10
    n = 1
    For rop = 1 To repnum
       ' ***** URLを合成 *****
       URL = MainURL _
             & StartYear & "/" _
             & "?month=" & StartMonth
       
       ' 設定期間範囲に従い、順次ナビゲート
       objIE.Navigate URL
    
       ' ページが完全に表示されるまで待つ
       Do While objIE.ReadyState <> 4
          Do While objIE.Busy = True
             DoEvents
          Loop
       Loop

       ' 0.5秒 Wait
       Sleep (500)
       
       ' 開催日程表リンク先を取得して配列に格納
       For i = 0 To objIE.Document.Links.Length - 1
        'For i = 0 To 3
           DoEvents
           If Left(objIE.Document.Links(i).href, 36) = "https://keiba.yahoo.co.jp/race/list/" Then
              nlist(n) = objIE.Document.Links(i).href
              n = n + 1
           End If
       Next i
       
       ' 期間範囲のインクリメント(終了範囲はループ回数で制限されるので省略)
       Bmonth = Bmonth + 1
       If Bmonth > 12 Then
          Bmonth = 1
          Byear = Byear + 1
          StartYear = Trim(Str(Byear))
       End If
       If Bmonth < 10 Then
          StartMonth = Trim(Format(Str(Bmonth), "00"))
          Else
          StartMonth = Trim(Str(Bmonth))
       End If
    Next rop

    p = 5
    '***** 自動巡回開始 (listファイル配列のURLより実行) *****
    For rop = 1 To n - 1
       ' ***** URLを合成 *****
       URL = nlist(rop)
       
       ' 開催期間範囲の配列格納URLに従い、順次ナビゲート
       objIE.Navigate URL
    
       ' ページが完全に表示されるまで待つ
       Do While objIE.ReadyState <> 4
          Do While objIE.Busy = True
             DoEvents
          Loop
       Loop

       ' 0.5秒 Wait
       Sleep (500)
       
       ' 成績表リンク先を取得する
       For i = 0 To objIE.Document.Links.Length - 1
           DoEvents
           If Left(objIE.Document.Links(i).href, 38) = "https://keiba.yahoo.co.jp/race/result/" Then
              Cells(p, 2) = objIE.Document.Links(i).href
              p = p + 1
           End If
       Next i

    Next rop

    '▼▼▼▼ ファイル名を作成 ファイル名は自分のパス+¥SyyyymmddHHMMSS.txtとして自動生成 ▼▼▼▼
    Dim FNAME As String
    '***** ファイル名を合成 (年月日時分秒) *****
    FNAME = "R" & Year(Date) & Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) & _
             Right("0" & Hour(Time), 2) & Right("0" & Minute(Time), 2) & Right("0" & Second(Time), 2) & ".txt"
    
    Dim strFNAME As String   ' ファイル名保存用
    strFNAME = ThisWorkbook.Path & "¥" & FNAME

    '***** テーブルデータからTXT形式ファイルを出力する *****
    Call MAKE_TXT_FILE(strFNAME, Range(Cells(5, 2), Cells(p - 1, 2)))

    '***** IE object解放 *****
    objIE.Quit
    Set objIE = Nothing
    
    Application.StatusBar = False
    
    MsgBox "データ取得処理が完了しました。"
    
    Range("A2").Select
End Sub

'***** ファイルを開きテキストのファイルを作成する *****
Sub MAKE_TXT_FILE(strFNAME As String, objHANI As Range)

    '***** ファイルをオープンする *****
    Dim FNO As Integer                ' ファイル番号
    FNO = FreeFile                    ' 空いてるファイル番号を取出す
    Open strFNAME For Output As #FNO  ' テキストファイルを新規作成

    '***** 行、列でループを作る *****
    Dim Y As Integer
    Dim x As Integer
    For Y = 1 To objHANI.Rows.Count                   ' 行のループ
        Print #FNO, Trim(objHANI.Cells(Y, 1).Value);  ' 先頭項目の出力
        For x = 2 To objHANI.Columns.Count            ' 列のループ
            Print #FNO, Trim(objHANI.Cells(Y, x).Value);
        Next x
        Print #FNO, Trim("")  ' 改行のみ出力
    Next Y

    '***** ファイルクローズ *****
    Close #FNO
End Sub

'***** ブラウザナビゲート *****
Private Sub objIE_BeforeNavigate2(ByVal pDisp As Object, URL As Variant, Flags As Variant, TargetFrameName As Variant, PostData As Variant, Headers As Variant, Cancel As Boolean)
End Sub

'***** ブラウザドキュメント表示 *****
Private Sub objIE_DocumentComplete(ByVal pDisp As Object, URL As Variant)
     dsp_flg = True
     Application.StatusBar = objIE.Document.Title    ' Titleを取得
End Sub

5.ダウンロード

提供するソースコードのライセンスは、CC0 (クレジット表示不要、改変可、商用可) とします。自由に利用して頂いてかまいません。
今回の記事で紹介してるプログラムは、本シリーズの連載最後にまとめてダウンロード提供する予定としています。 少しずつ記事をアップしていきますので、しばらくお付き合いください。

■関連記事
・Excelでデータベース(SQLite3)を扱う
・複数のファイルをマージする
・競馬成績表データベースを作る(データ取得編)
・競馬成績表データベースを作る(データベース編)
・競馬成績表データベースを作る(ツール－Excel編)
・競馬成績表データベースを作る(ツール－HSP編)

コメント

コメントを投稿

このブログの人気の投稿

Excelアドインで日本語形態素解析

-
イメージ
1.概要 日本語形態素解析とは、文法的な情報の注記の無い自然言語のテキストデータ（文）から、対象言語の文法や、辞書と呼ばれる単語の品詞等の情報に基づき、 形態素（Morpheme, おおまかにいえば、言語で意味を持つ最小単位）の列に分割し、それぞれの形態素の品詞等を判別する作業です。（出典:Wikipedia） 形態素解析エンジンとしては、Mecabが有名です。筆者もPythonにインストールして、テキストマイニングに利用しています。しかし、会社などで利用したい 場合は、このような環境を個人的な利用目的で構築するにしても、セキュリティ上の問題などでIT管理者などから利用許可が下りない場合があります。 今回は、普段から利用しているExcelのアドインでテキストマイニングをして見たいと思います。特別なサーバーなどへのインストールなしで、 個人PCにアドインのインストール許可さえもらえれば誰でも簡単にテキストマイニング(ワードクラウド)分析をすることができます。 2.利用方法 (1)Excelのアドインである「E2D3」を追加します。「挿入」→「アドインを入手」をクリックし      「データ分析」を選択して、「E2D3」を探します。見つかったら、「追加」をクリックしま      す。これで、「E2D3」が利用できるようになります。 (2)分析するデータ(テキスト文書)を準備します。 (3)下記サイトの日本語形態素解析サービス(無料ツール)を利用して、文書を解析し品詞毎に分解、      統計データとして出力します。 日本語形態素解析サービス(無料ツール) URL : https://opendata-web.site/tool/parse/ ※ブックマーク等に登録しておくと良いでしょう。 (4)分析するデータ(テキスト文書)をテキストボックスに貼付け、解析ボタンをクリックします。 (5)解析結果が日本語形態素と統計の2種類画面出力されるので、統計の表をコピペして、Excelの      空きシートに一時的に貼り付けます。 (6)フィルタを追加して、品詞で名詞を選択してフィルタリングします。名詞のみのデータになるの      で、フィルタしたシートのB列(出現数)、C列(表記)をコピーして退避し、B列とC列を入れ替え

階層構造JSONファイルの作成

-
イメージ
1.はじめに 仕事や趣味でいろいろなデータを扱いますが、プログラミングで扱うデータ形式も多種多様でデータベース、テキスト、csv、xml、json形式など 思い付くだけで数十種類でてきます。データの見える化 (可視化) をやりたくて階層構造のJSON形式のファイルを扱う必要性があり、 元データ収集・編集 ⇒ Excelでデータ作成 ⇒ CSV出力 ⇒ 階層構造のJSONファイルへ変換出力と言ったステップでJSONファイルを作成して見ましたので紹介致します。 2.階層構造CSVtoJSONコンバータ 最初は、テキストエディタで手作業でJSONファイルを作成しょうと試みましたが、{}の対応付けや[,などの挿入漏れなど ミスすることが多くてデータを読み込むとエラーで動かないことの連続でした。何とか効率良く元データより階層構造の JSON形式のファイルを作成することができないものかとネット検索しても意図したものを見つけ出すことが出来ませんでした。 そこで今回、試行錯誤してExcel VBAで作成した 「階層構造CSVtoJSONコンバータ」 によるデータ作成手順をまとめて見ました。 階層構造のJSONファイルとは、 親、子、孫、曾孫・・・といった 階層構造のデータ形式のァイル です。 2-1.データの収集について まずは、目的とすべき元となるデータを収集しなければなりません。データの入手や収集手段はいろいろあると思いますが、 1回限りでのみ利用する場合や不定期、日次、週次、月次など継続的に利用する場合に応じて効率良い方法を選択する必要があります。 企業では自社内の各種システムにて収集や加工・編集などができるツール類もあると思いますが、個人ではなかなか揃えられません。 尚、今回は小規模なデータを扱うことを前提としているので効率的なデータの収集方法などについては別の機会にでも考えていきたいと 思います。ここでは、 「階層構造CSVtoJSONコンバータ」 の説明のためのテストデータを利用しての手順について説明します。 2-2.作成手順 (1)元データの準備 (テスト用に鳥類目録データの一部を利用) (2)Excelで元データを利用して、階層構造のデータ表を作成する。     ※【お願い】ファイルの終端判定のため、A列の末尾のセルにEOFと追記して下

HSPでコマンドプロンプトを制御する

-
イメージ
1.概要 最近は、Excel VBAやPythonなどによる自動化というキーワードを良く耳にしますが、昔からある自動化で最も安定性が あり、比較的単純な手順の記述とコマンド発行といったコマンドプロンプトによる操作およびバッチファイルによる自動処理 があります。システムの運用管理者やネットワーク管理者の人達も多く利用されています。 筆者も一時期は、良く利用していましたがGUIの操作に慣れ過ぎて最近は利用する機会も減ってきています。 今回は、HSP言語によるコマンドプロンプトを操作して外部から文字列(コマンド)を送信する方法を模索して見たいと思います。 2.コマンドプロンプトに文字列送信 コマンドプロンプトにGUI画面上からキー入力された文字列を送信する方法について説明します。 まず、概略の処理の流れを整理します。 (1) コマンドプロンプトを起動させる。 ただし、環境依存を無くすため環境変数 'comspec' より取得したパス名を、 exec 命令のp1パラメータとして渡す。 (2) 入力ボックス等より入力したコマンド（ テキスト文字列 ）をクリップボードにコピーする。 (3) コマンドプロンプト画面をアクティブにする。 (4) クリップボードよりコマンドを貼り付ける。 整理すると、これらの処理を実現させるためには、 ・環境変数'comspec'よりパス名を取得する処理 ・クリップボードにテキスト文字列をコピーする処理 ・任意の画面をアクティブにする処理 ・クリップボードよりテキスト文字列を貼り付ける処理 となります。ここで、一番難しいのはコンソールに対してクリップボードよりテキスト文字列を貼り付ける処理です。 [ ALT ]+[ SP ]+[ e ]+[ p ]+[ Enter ] キーという操作を keybd_event APIを 利用 して送り、貼り付け処理をしても良いのですが、メニューの操作をエミュレートするので、 [ ALT ]+[ SP ] で メニュー がポップアップし、次に [ e ] で編集がクリックされ、更に [ p ]+[ Enter ] キーといった流れとなって メニューの 操作が丸見えとなります。もちろん、動作的にも遅くスマートではありません。 コマンド

TOP