JVData To SQLite

JRA-VANデータラボの競馬データJVDataをSQLiteデータベースに変換するExcel VBAプロジェクト
Last update: 2017-12-10

ユーザーへのお願い

ユーザー向けの情報は、/tag/jvdata-to-sqlite/にあります。バグ修正、データベースの使い方などを投稿しています。

もし、不具合を見つけたら、オンラインFeedbackまたはメール[email protected]で開発者である私(まえだこうすけ)に報告してください。対処できると思います。

あなたが使っているバージョンよりも新しいバージョンがリリースされているかもしれません。http://jra-van.jp/dlb/sft/lib/jvdata_to_sqlite.html
/tag/jvdata-to-sqlite/をチェックしてください。

目次

  1. イントロダクション
  2. 目的
  3. 準備
    1. ダウンロード
    2. Excelのインストールと設定
    3. JVLinkのインストールと設定
    4. SQLite ODBC Driverのインストール
  4. 使い方
    1. 基本的なこと
    2. 作成するデータベース
    3. 注意事項
  5. 仕様詳細
  6. 開発履歴
  7. サポート
  8. ライセンス

1. イントロダクション

JVData To SQLiteはJRA-VAN データラボの登録ソフトウェアです。データラボの会員(月会費2,058円)は無料で使うことができます。データラボは1ヶ月の無料トライアルがあるので、データベースを更新しなくていいなら無料で使うことができます。

JVData To SQLiteは競馬データJVDataをSQLiteデータベースに変換するExcel VBAプロジェクト(Excelマクロ有効ブック)です。JVData To SQLiteを使えば、1986年から現在までに開催された競馬レースのSQLiteデータベースをローカルマシン上に持つことができ、SQLやRを使って自在に競馬データを解析することができます。

2. 目的

JVData To SQLiteの目的は、ボタンを押すだけで、競馬データJVDataをダウンロードして、読み込み、パースして、SQLiteデータベースを構築し、競馬のデータ解析に必要なデータベースをユーザーに提供することです。

3. 準備

3.1. ダウンロード

http://jra-van.jp/dlb/sft/lib/jvdata_to_sqlite.html

から自己解凍式圧縮ファイルJVDataToSQLite???.exeをダウンロードして展開してください。

JVData To SQLiteの実体はExcelマクロ有効ブックJVDataToSQLite.xlsmです。

また、JVData To SQLiteは次の3つのソフトウェアに依存しているので、それぞれWindows 7/8/10にインストールしてください。

  • Microsoft Excel
  • JRA-VAN JVLink
  • SQLite ODBC Driver

3.2. Microsoft Excelのインストールと設定

JVData To SQLiteはExcel 2010で開発しました。2010以降のバージョンのExcelで動作すると思います。

Excelは32bit版を使ってください。JVLinkは64bit版では動作しません。Excelのインストール時に、ユーザーが自ら64bit版を選ぼうとしなければ、32bit版がインストールされるので、ほとんどの方は32bit版のExcelを使っていると思います。

Excelはマクロ機能を有効にしてください。Excelのファイル > オプション > セキュリティセンター > マクロの設定から次のチェックを入れてください。

  • デジタル署名されたマクロを除き、すべてのマクロを無効にする
  • VBAプロジェクトオブジェクトモデルへのアクセスを信頼する

マクロの設定

上図は、Excel 2010の場合の参考画面です。

JRA-VANデータラボの動作環境の説明に
従ってインストールし、利用キーの入力を行ってください。JVLinkの実体はActiveXコントロール(dllファイル)で、Windowsでのみ動作します。

3.4. SQLite ODBC Driverのインストール

SQLite ODBC DriverはSQLiteデータベースを操作する機能を提供するドライバです。VBAから(正確にはADODBオブジェクト)SQLiteデータベースを操作するのに必要なソフトウェアです。

http://www.ch-werner.de/sqliteodbc/

からダウンロードしてインストールしてください。

リンク先のCurrent versionの下にあるsqliteodbc.exe(32bit版)を選んでください。sqliteodbc_w64.exe(64bit版)もあるので間違えないようにしてください。

4. 使い方

4.1. 基本的なこと

JVDataToSQLite.xlsmファイルをExcelで開くと、Topワークシートが開きます。

初回実行の場合、次のような「ActiveXコントロールが無効にされました。」というセキュリティの警告が表示されることがあります。このActiveXコントロールとはJVLinkのことです。この警告が表示されたら、コンテンツの有効化ボタンを押してください。

TopワークシートのActiveXコントロール無効画面

また、JVLinkをインストールしていない場合や、上記のActiveXコントロールが無効になっている場合、「コントロールは作成されていないため、デザインモードを終了できません。」というエラーメッセージが表示されることがあります。

xコントロールは作成されていないため、デザインモードを終了できません。エラー

このエラーはJVLinkライブラリファイルがPCに存在しない場合、ActiveXコントロールが無効になっている場合にコンパイルが完了せず、コントロールのインスタンスが作られないために生じます[1]

エラーが表示されたら、OKボタンを押して、エラーメッセージを閉じてください。ActiveXコントロールが有効なら(コンテンツの有効化ボタンを押す)エラーは生じません。

また、JVDataToSQLite.xlsmには私がkosukemaeda.comの名前で作成したデジタル証明書でデジタル署名してあります。

デジタル著名に関連して「マクロが無効にされました。これらのマクロには、ウイルスが含まれているか、その他セキュリティ上の危険性があります。このファイルの発行元が信頼出来ない場合は、このコンテンツを有効にしないでください。」のようなセキュリティ警告が表示されたら、

  • このコンテンツを有効にする
  • この発行者のドキュメントをすべて信頼する

のどちらかを選択してください。

これらは初めてJVDataToSQLite.xlsmを開いたときのみの設定です。

TopワークシートのStartボタンを押すとデータベースの構築が始まります。

スタートしてすぐに、JVLinkのセットアップ画面が表示されます。

JVLinkセットアップ

これは、競馬データJVDataのダウンロード元を指定する画面です。

JVDataの実体は拡張子jvdのファイルで、1つのjvdファイルの中に数百から数千個の固定長文字列形式のレコードが入っています。このjvdファイルは1986年から現在までで3000個以上あり、新しいレースが開催されると新しいjvdファイルがJRA-VANから発信されます。

jvdファイルをDVD(スタートキット、トライアルキットで配られる)からダウンロードするか、オンラインでサーバーからダウンロードするかを選択します。

オンラインでも数十分でjvdファイルのダウンロードが完了すると思います。2つの内どちらか選択してOKボタンを押してください。

OKボタンを押すと、jvdファイルのダウンロードが始まり、ダウンロードが完了するとjvdファイルごとにレコードを読み込み、パースし、データベースにデータを追加していきます。

途中で止めたい場合は、Stopボタン(Startボタンを押すとStopボタンに変わります)を押します。

再度スタートすると未取得のデータから取得を再開します。

もし、JVData To SQLiteを使っている最中にWindowsやExcelがフリーズしたり、停電などが起きた場合も、再スタートすれば途中からデータベースの構築を再開できます。

レコードの取りこぼしがないか調べるチェックボックスは、念のための機能で、チェックを入れてスタートすると取得済みのデータも読み込みます。取得していないjvdファイルが見つかれば取得しデータベースに追加します。

データベースの構築には時間がかかるため、WindowsやExcelが途中で終了してしまうことはありえます。そういうときは慌てずにJVDataToSQLite.xlsmを開き直し、再スタートしてください。データが大きいので時間はかかりますが、ちゃんとデータベースを作ります。

データベースが完成したら、一度、レコードの取りこぼしがないか調べるチェックボックスにチェックを入れて再スタートしてみてください。この作業は必須ではありませんが、データベースが完全か確かめることができます。

何か不具合があり、Startボタンが押せなくなった場合はResetボタンを押してください。Startボタンが元に戻ります。

4.2. 作成するデータベース

プログラムが始まると、データベースファイルsetup.sqlite3JVDataToSQLite.xlsmと同じフォルダに新規作成します。

setup.sqlite3に作成するテーブルのリストは次のとおりです。

  • レース
  • 賞金
  • ラップタイムラップタイム合計
  • コーナー通過順位
  • 馬毎レース
  • 不成立レース返還馬番枠番
  • 払戻単勝払戻複勝払戻枠連払戻馬連払戻ワイド払戻馬単払戻三連複払戻三連単
  • 発売レース
  • 票数単勝票数複勝票数枠連票数馬連票数ワイド票数馬単票数三連複票数三連単票数合計
  • オッズ単勝オッズ複勝オッズ枠連オッズ枠連オッズ馬連オッズ馬連オッズワイドオッズ馬単オッズ三連複オッズ三連単

テーブル設計は、JVDataToSQLite.xlsmテーブル設計ワークシートにまとめてあります。テーブルスキーマや各フィールドの定義、コードの説明があります。データベースを使用するには必読です。

テーブル設計ワークシート

または、DB Browser for SQLiteなどのビューワーでsetup.sqlite3を開けば、テーブル設計がわかります。

4.3. 注意事項

4.3.1 設定ワークシート

初回実行に24時間以上かかります。初回実行では、過去30年分のデータの取得とデータベース追加をするからです。

三連単データが大きく処理に時間がかかるため、初期設定ではデータを取得しないようにしています。

もし、三連単データがほしい場合は、設定ワークシートの取得非対象レコードのH6(三連単票数のレコード種別)とO6(三連単オッズのレコード種別)を消してください。

設定ワークシート

三連単データは非常に大きいデータです。三連単データなしではデータベースのファイルサイズは7GBですが、三連単データがあると50GB以上になります。またデータのパースにも時間がかかるため、三連単データを取得するにはプラス24時間以上かかります。

三連単データを取得するなら、一度、初回設定(三連単データなし)でデータベースを作ってからにしたほうがよいです。

また、取得対象の期間を1986年よりも最近に設定することでデータベース構築の時間は短縮します。同じく設定ワークシートの取得対象データ期間始めの値を1986/1/1から現在までの任意の日付yyyy/m/dに変更してください。

設定ワークシートの値を変更したら、JVDataToSQLite.xlsmブックを上書き保存して、データベースファイルsetup.sqlite3は削除すると、Startボタンを押した後にsetup.sqlite3を新規作成して、設定した値を反映したデータベースを構築します。

すでにあるデータベースにデータを追加したいなら、setup.sqlite3は消さずに、レコードの取りこぼしがないか調べるチェックボックスにチェックを入れてから、Startボタンを押してください。

4.3.2. データベース構築高速化の裏技

取得対象期間が短いほどJVLinkは1レコードを高速に読み込むので、 一度ストップしてから再スタートするとデータの取得時間が短くなります。これはJVLinkのバグとまではいえないまでもちょっとした不具合です。

4.3.3. フリーズするとExcelの自動回復機能が働く

Excelがフリーズすることがあります。その場合はExcelの自動回復機能が働きます。Excel回復ブック(xlsbファイル)が起動している場合はそのファイルを閉じて、JVDataToSQLite.xlsmを開いて再スタートしてください。未取得のデータから取得を再開します。

自動回復ブック

このようにStartボタンを押して、何時間か経過している間にフリーズして自動回復してxlsbファイルが起動していたら、保存しないを選択してファイルを閉じてください。その後、再度、JVDataToSQLite.xlsmを開いて再スタートしてください。大切なことなので2度書きました。

なぜフリーズすることがあるのかわかっていません。JRA-VANの掲示板にJVLinkがフリーズすることがあるという開発者からの報告があります。JVData To SQLiteの場合も、JVLinkを操作したとき(JVGets()メソッドをコールしたとき)に応答がなく、フリーズして、自動回復機能が働いています。

このフリーズは頻繁に起こるわけではありません。一度、データベースを構築してしまえば、アップデートは短時間で済みますし、フリーズしても途中から再開するようにプログラムしていますので、大きな問題ではないと思います。

4.3.4. プログラムが止まっているように見える場合

プログラムが1分以上止まっているように見えることはふつうは起こりません。

しかし、データベースにデータを追加中に異常終了した場合、その後に再スタートすると、プログラムが止まって見えることがあります。

止まって見えるだけで、つまり、プログラムは通知はしないのですが、プログラムは正常に動作しています。

JVData To SQLiteは次のサイクルを繰り返すようにプログラムしています。

  1. 1つのjvdファイル内のレコードをメモリに蓄積する
  2. 蓄積したレコードをパースしてテーブルにする
  3. 作成したテーブルをSQL文に成形する
  4. 成形したSQL文をRDBMSにクエリする

この1と2は随時Topワークシートに通知するのですが、3と4は通知しません。3は全く通知しませんし、4はクエリが終了したら「xテーブルにデータを追加しました」と通知します。

もし、データベースにデータを追加中に異常終了してしまうと、次回スタートしたときに同じデータ(同じjvdファイルのレコードから作成したテーブル)を追加しようと試みます。jvdファイル単位でレコードを管理しているからです。しかし、データはデータベースにあるので、追加できません。そこで、今度は1行ずつデータを更新しようと試みます。

こういったことをデータの数だけ行うので、時間がかかり、3と4は通知をしないので、止まっているように見えます。プログラムは正常に動作しているので、数分か、数十分の間、待ってみてください。

もし、本当に応答がない場合はExcelの自動回復機能が働きます。そういう場合は、回復されたブックを閉じて、再スタートしてください。

5. 仕様詳細

Version 1.0.2

  • エラー「レーステーブルの操作に失敗しました」「オブジェクトが存在しません」を修正しました。

Version 1.0.1

  • パースして作成したテーブルのデータベースの追加するルーチンを、テーブル一括追加 > 失敗なら1行ずつ更新から、テーブル一括追加 > 失敗なら1行ずつ追加 > 失敗なら1行ずつ更新に変更しました。
  • Topワークシートにプログラム実行中を示すためのワーキングサイン(くるくる回る記号)を追加しました。
  • 票数三連単とオッズ三連単データは、初期設定では取得非対象に含めました。
  • ソフトウェアIDを設定しました。

Version 1.0.0

  • 結果が確定したレースのデータを取得します。レースの対象期間は1986年から直近までです。
  • 新しいレースが開催されたら、結果が確定した後(普通は月曜日)にソフトウェアを再実行するとデータベースを更新します。
  • エラーが起きてデータベース構築・更新が失敗したら、再実行したときに失敗したデータから取得を再開します。
  • 特別登録馬、開催予定など未来のレースのデータは取得しません。バージョンアップで対応予定です。
  • 時系列オッズなど現在進行中のレースのデータは取得しません。バージョンアップで対応予定です。
  • 競走馬、騎手、調教師、馬主のマスターデータは取得しません。バージョンアップで対応予定です。

6. 開発履歴

  • 2017-04-10 開発スタート
  • 2017-09-07 メイン機能の開発が完了しました。
  • 2017-11-11 Version 1.0.0をJRA-VANデータラボに登録申請しました。
  • 2017-11-25 Version 1.0.1をJRA-VANデータラボにてリリースしました。
  • 2017-12-10 Version 1.0.2にアップデートしました。

7. サポート

オンライン https://www.kosukemaeda.com/feedback/ またはメール [email protected] で開発者である私(まえだこうすけ)に連絡してください。

8. ライセンス

JVData To SQLiteはオープンソースです。ライセンスはMITライセンスとします。

JVDataToSQLite.xlsmは意図しないソースコードの編集を防ぐためにロックをかけています。パスワードはjvdatatosqliteです。

MIT License

Copyright (c) 2017 Kosuke Maeda / まえだこうすけ

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

  1. コントロールは作成されていないため、デザイン モードを終了できません - Microsoft MSDNより ↩︎