グレおやぢの日常

1.概要 競馬成績表データベースを作るの第二弾は、競馬成績表データの一括自動取得を行います。 事前準備編 で取得済みの 各レース毎のレース成績表リンクリストを使います。そのリストのリンクを順次読み込み、サイトを自動巡回して 成績表データを取得します。取得できるデータ項目は、日付、コード、R番号、競馬場、開催、発走時間、天気、馬場、レース名、コース、距離、単勝、枠連、馬連、馬単、3連単、1着、2着、3着、頭数の 計20項目となります。プログラムは、Excel VBAで作成します。 2.データの取得手順 成績表データ取得手順は、次の手順で行います。 (1) Yahoo競馬レース結果取得.xlsm を起動します。(ダウンロードは、本連載記事の最終回で提供) (2)成績表データ取得ボタンをクリックします。 (3)ファイル選択ダイアログが開くので、事前取得済みのレース成績表リンクリストを選択します。 (4)データ取得が開始されますので完了するまで待ちます。 (5)レース成績表リンクリストは分割して準備されていると思うので、続いて取得の場合は、      手順(2)から繰り返し実行します。 データベース作成時は、分割取得されたデータを全てマージして利用します。 コード体系は、以下のような体系で構成されています。データ取得時は、このコード番号が キーとしてURLのパラメータとなります。 (例)2002020806 https://keiba.yahoo.co.jp/race/result/2005020806/ 20 ⇒ 西暦年 YY 05 ⇒ 競馬場コード (東京) 02 ⇒ 開催回 (02回) 08 ⇒ 開催日目(08日目) 06 ⇒ レース番号 (06R) ●パラメータ yy : 開催年 xx : 競馬場コード yy : 開催回[第N回] zz : 開催日目[N日目] rr : レース番号 ●競馬場コード 01 : 札幌競馬場 02 : 函館競馬場 03 : 福島競馬場 04 : 新潟競馬場 05 : 東京競馬場 06 : 中山競馬場 07 : 中京競馬場 08 : 京都競馬場

1.概要 競馬成績表のデータベースを作成し、過去の成績データから、いろいろ分析するのに役立てようと思います。 筆者は、実際に競馬はやっていません。競馬を題材としてデータ量が豊富なので統計的な分析手法の勉強用に データの収集を行って楽しんでいます。 今回の記事から何回かに分けての投稿を予定しています。データ収集からデータベースを作って活用するためのツール 作りまでを連載形式で紹介していきます。 2.事前準備作業 今後の予定については、以下の通りです。 ・ 事前準備編 (今回) ・データ取得編 ・データベース編 ・ツール－Excel編 ・ツール－HSP編 とします。一度に全ての記事を整理仕切れないので、分割としました。気長に付き合って下さい。 事前準備編では、競馬レースの成績表を過去分のデータまで遡って取得するために事前に指定年月の 開催日程日と各レースの結果が掲載されているページのURLを取得する必要があります。 取得プログラムは、Excel VBAマクロで作成しています。 3.利用方法 レース結果のリンクページのURLリストを取得するプログラムは、 Yahoo競馬結果リンク先取得.xlsm です。 指定範囲の年月で一括でリンクリストを取得し、外部出力します。 プログラムとしては、指定した年月より開催日程表リンク先を取得して配列(listファイル配列)に格納します。 listファイル配列のURLをもとに自動巡回しながら、開催日のレース毎の成績表リンク先を取得するというものです。 取得したリンク先をシート上に表示していきます。ファイル名を日付時刻形式として、カレントディレクトリにテキスト形式で外部出力します。 取得先は、下記サイトのページより取得しています。 Yahoo!競馬開催日程表 https://keiba.yahoo.co.jp/schedule/list / Yahoo!競馬成績表結果 https://keiba.yahoo.co.jp/race/result/ 利用方法は、ダウンロードしたら (1) Yahoo競馬結果リンク先取得.xlsm を起動します。 (2)プルダウンで開始年と開始月、終了年と終了月で取得期間(範囲)を指定します。       ※指定範囲は、最大6ヶ月程度として、

1.概要 扱うデータ件数が数十万件となると、Excelのシート内では、読み込みにも時間が掛かるし、場合によってはパソコンの性能に左右されて 固まってしまいます。読みんだ後も動作が緩慢で使い勝手が悪くなります。数十万件のデータでも必要な部分は数百から数千件のデータで ある場合が多いものです。このような場合は、データベースを利用するとページ単位の読み込みや必要なデータの検索などが比較的容易に できるようになります。今回は、手軽に利用できる SQLite3 によるデータベースをExcelで扱う方法について紹介します。 2.事前準備作業 ExcelなどからSQLiteのデータベースを扱うには、本体であるsqlite.exeやsqlite.dllなどが必要な場合もありますが、 今回、紹介するものは、色んな言語で汎用的に利用できるODBCドライバをインストールしてデータベースを扱います。 一度、インストールしてしまえば、Excel上ではDSN(データソース)の設定なども必要ありません。 2-1.ODBCドライバーの入手とインストール まずは、ODBCドライバーの入手とインストールです。ODBCドライバーは海外サイトですが、下記のサイトからダウンロードできます。 海外サイトということで怪しいと思われますが、広告や他のサイトへの誘導などはありません。 ダウンロード先 : http://www.ch-werner.de/sqliteodbc/ このサイトの真ん中あたりにCurrent versionという部分の下に2つのodbcドライバーがあります。 sqliteodbc.exeは32bit版で、sqliteodbc w64.exeは64bit版です。WindowsのOSが32bitのものか64bitのものか、また、Excelのバージョンが 32bit版なのか64bit版なのか判断に迷います。 ※(どれを選択すれば良いか筆者には適切に指示するこはできません) 両方インストールしても問題なさそうですが、たぶん、利用しているExcelのバージョンによるものだと思います。 筆者は、Windows10の64bit版で、Excelも64bit版なので、sqliteodbc w64.exeをダウンロードしてインストールしました。 ダウンロードしたら、

1.概要 国土交通省が情報提供しているリアルタイムレーダー観測雨量の地域ブロック別に分割されている観測画像を一括して取得し、一覧画面として 表示します。地域ブロックは、全国、沖縄地方、九州地方、四国地方、中国地方、近畿地方、中部地方、北陸地方、関東地方、東北地方、北海道地方 で全国を含めて計11の地方ブロックとなっています。データは国土交通省の雨雲レーダーの最新更新時の画像データを取得・表示します。 2.レーダー観測雨量の取得 ※【注意】河川の防災情報がリニューアルされたため、情報の取得ができませんので、提供を中止します。 利用は不可です。 レーダー観測雨量の取得は、Excel VBAにて作成したマクロで取得・表示します。観測データは、画像ファイルとなっていて イメージ情報として、地域エリアコードと時と分を組み合わせたものがURLのパラメータとして渡されます。 取得画像形式は、gif形式となっています。 Excel VBAマクロで作成した、 国土交通省_レーダ観測雨量取得.xlsm を起動すると、自動で取得して一覧表示させています。 再度、時間をおいて実行する場合は、レーダー観測雨量ボタンをクリックします。．また、手動で画面のクリア(消去)する場合は、 消去ボタンをクリックします。取得時には、取得前に消去させていますので、手動消去は必要ありません。 3.ソースコード 下記のリストは、 国土交通省_レーダ観測雨量取得.xlsm のソースコードです。 ALT+F11でVBE(エディタ)を開いて、ソースコードを確認して下さい。 ThisWorkbookは、起動時の処理のため、Workbook_Open()で Worksheets("MAIN")のGetUryouを呼び出して 自動的にレーダー雨量観測画像データを取得しています。 次に標準モジュール : APIModは、Sheet1(MAIN)で利用するWindows APIを3つ定義しています。 Sleep 関数は、待ち時間を指定するものです。 ShowWindow 関数は、ウィンドウを最大化するか最小化するかの動作をさせるためのものです。 URLDownloadToFile 関数は指定URLのファイルを、指定パスにダウンロードするためのものです。 Shee

1.概要 会社や個人レベルの業務や作業にExcelやpdfファイルを利用する機会も多いと思います。多くの企業は、経営情報や生産管理、メールなどの コミュニティ手段、オンライン会議といった企業の基幹となる部分は投資してシステム化を進めていますが、それらのシステムなどから提供される 情報の編集や加工などはExcelが主なツールとなっているのが現状ではないでしょうか？最近では、時短要請や在宅勤務の推進を含めた効率化 が叫ばれています。脱Excelと言われて久しいのですが、当分、難しいと個人的には感じています。 話が少し逸れてしまいましたが、Excelからpdfへの変換はExcelのファイルに名前を付けて保存時に保存形式をpdfに指定してあげれば、簡単に できるので良く利用していると思いますが、逆に pdfからExcelにデータを取り込めることは、意外に知られていません。 今回は、 Excelデータとpdfデータを相互変換する方法 について紹介して行きます。 2.Excelとpdfファイルの相互変換 Excelからpdfへの変換出力について知らない人はいないと思いますが、参考程度に簡単に触れておきます。 画面例は、ご利用のExcelのバージョンによって多少異なりますが、操作として概ね同じと思います。 既に普通に実施しいる 操作なので、ご存じない人のみ読んでみて下さい。 2-1.Excelからpdfへの変換出力 (1)pdf形式として変換出力するExcelを通常通り開きます。(画面例はテスト用のサンプル) (2)印刷時にはみ出さないように印刷プレビューで予め確認して、改ページプレビューします。      (印刷書式を調整します) (3)「ファイル」→「名前を付けて保存」→拡張子選択のプルダウンをクリックして、PDF(*.pdf)を      選択します。ファイルの保存先を指定後、保存ボタンをクリックすれば、pdfファイルとして      出力されます。 (4)指定した保存先にpdfファイルとして保存されます。Adobe Acrobat Readerがインスト―されて      いれば、 PDFReaderで閲欄出来ますが、インストールされていない場合は、デフォルト設定さ      れているブラウザが開いて確認できます。 2-2.pdfか

1.概要 現在から過去に発生した地震の履歴情報をwebサイトから定期的に取得して畜積していますが、年度別、月別、震度別といった区分けで 集計して 統計的にまとめて 見ました。データを集めるだけでは何の役にも立たないので有効活用できないものかと模索しています。 データの取得方法には、色んな手段がありますが、筆者の場合は定期的および継続的にデータの差分を取得する仕組みをExcel VBAで作成 して活用しています。今回は データ取得後の蓄積データの活用方法について紹介 します。 2.地震発生履歴情報の取得方法 2-1.データ取得先の調査 データを取得するに当たり、どのサイト様からデータを入手するのが良いのか調べてみると、下記のサイト様に必要とすべき地震履歴データが掲載されていました。 ■気象庁 : 地震情報（震源・震度に関する情報） URL : https://www.jma.go.jp/jp/quake/quake_singendo_index.html データの表示文字が全て全角表示であり、過去分まで遡って取得できそうにない。 ■tenki.jp : 過去の地震情報 URL : https://earthquake.tenki.jp/bousai/earthquake/entries/ 最大震度の部分が画像で表示されているので、取得処理がめんどくさそうである。 過去分のデータ件数が約30,000件とYahoo!地震と比べて登録数が少ない。 ■goo : 地震一覧 URL : https://weather.goo.ne.jp/earthquake/list/ 過去分のデータ件数が約16,000件とYahoo!地震と比べて登録数が少ない。 また、マグネチュードが掲載されていない。 ■Yahoo! : 履歴一覧 URL : https://typhoon.yahoo.co.jp/weather/earthquake/list/ 過去分のデータ件数が約42,000件と豊富である。 また、必要な項目の並びやデータ項目がある。 ページのソースコードを確認して見ると、取得する箇所がTABLEタグで作られており スクレイピングで取得し易い作りとなっている。 以上の調査結果から、 Yahoo! 履歴一覧から取得 し

1.概要 Web上のページを仮想プリンタへ出力する場合は、ブラウザの印刷出力でPDF出力できます。また、ブラウザの拡張機能でプラグインを インストールしてアイコンをクリックするだけでPDF出力することもできますが、複数ページを連続してまとめて自動出力できれば 便利だと思います。筆者の場合は、個人の学習目的としてExcelやPython等のプログラミング講座やサンプルを掲載 しているサイト様から情報収集しています。少しでも個人レベルで 時間や労力の削減を目的として効率化 を図っています。 今回は、Excel VBAで自作した Web上の複数ページを自動巡回してPDFへ出力する方法 について紹介します。 2.作業手順 まずは、情報収集のための準備作業です。検索エンジンでググったりして自分が必要とすべき情報があるサイトを見付けに行きます。 これは普段から誰でもやっていることですね。この作業がネットにおいては何よりも重要な作業だと思います。 目的とするページが見つかったら、サイトのURLをメモ帳などにメモっておきます。 以降は、Excelのマクロを実行しての作業となります。少しだけ面倒なのですが、2種類のVBAマクロを利用します。 (1)指定URLのリンク取得.xlsmを起動して該当先(Webページ)のリンク一覧を取得します。     リンクの取得が完了したら、指定URLのリンク取得.xlsmを閉じます。 指定URLのリンク取得.xlsmの起動画面 (2)リンク取得が完了したら、日付時刻形式のブックファイルとして出力されるので開いて確認します。 PDF出力しない不要な行の削除とタイトル項目名(PDF出力する時のファイル名) を必要であれば変更します。 リンク取得後の出力済みファイルの不要行の削除とファイル名の調整 (3)項目名の先頭に番号(連番)が付加されているがなければ連番を付加しておくと良いです。 事前に手間でも、この作業をしておくとファイル出力後、番号順にファイルが並ぶので管理し易いです。ファイルは閉じずに上書き保存してこの状態にしておきます。 項目名の先頭に番号(連番)を付与 以上が準備作業です。ここからの作業は、上記で取得編集したリンクファイルを利用しての WebページのPDF出力です。 (4)Webページを仮想プリ

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と追記して下