Attribute VB_Name = "gy04_day" ' 各銘柄の日足データと財務情報を取得するためのクエリファイルを作成する Option Base 1 Sub gy04_day() '警告の表示を停止 Application.DisplayAlerts = False ' CSVファイル高速読み込み。CSVファイルをTXTファイルでSheet1に開く Dim corp_list(5000, 3) As String Const csv_dir As String = "C:\mysql\mysqldata\csv" ' CSVファイルのディレクトリ Const read_csv_name As String = "gy01_corpgrp.csv" ' CSVファイル名 If Dir(csv_dir & "\" & read_csv_name) <> read_csv_name Then MsgBox "ファイル「" & read_csv_name & "」はありません" Exit Sub End If Application.StatusBar = "( " & read_csv_name & " )読み込み中" ' CSVファイル読み込み ix = 1 Open csv_dir & "\" & read_csv_name For Input As #1 Do Until EOF(1) Input #1, corp_list(ix, 1), corp_list(ix, 2), corp_list(ix, 3) ix = ix + 1 Loop Close #1 ' セルに書き込み Sheets("Sheet1").Range(Cells(1, 1), Cells(ix - 1, 1)).Value = corp_list ' 空白セルの検索(最後のセル) Dim end_point01 As Integer Dim current_point01 As Integer current_point01 = 0 Sheets("Sheet2").Range("A3500:A3800").Select Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate end_point01 = ActiveCell.Row '最初の空白の行を取得 Sheets("Sheet3").Cells(1, 1).Value = "WEB" Sheets("Sheet3").Cells(2, 1).Value = "1" Do While (current_point01) * 30 < end_point01 ' 各銘柄の日足データを取得するためのクエリファイルを作成する Sheets("Sheet3").Cells(3, 1).Value = "http://quote.yahoo.co.jp/q?s=" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 1, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 2, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 3, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 4, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 5, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 6, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 7, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 8, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 9, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 10, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 11, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 12, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 13, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 14, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 15, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 16, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 17, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 18, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 19, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 20, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 21, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 22, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 23, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 24, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 25, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 26, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 27, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 28, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 29, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 30, 1) & "&d=v2" ' 各銘柄の日足用WEBクエリをIQY(TXT形式)ファイルとして保存 Sheets("Sheet3").Select ChDir "C:\mysql\mysqldata\excel\webquery" ActiveWorkbook.SaveAs Filename:= _ "C:\mysql\mysqldata\excel\webquery\" _ & "gy04_day" & Right("00" & (current_point01 + 1), 3) & ".iqy", FileFormat:=xlText, CreateBackup:=False Sheets("gy04_day" & Right("00" & (current_point01 + 1), 3)).Name = "Sheet3" ' 各銘柄の財務情報を取得するクエリファイルを作成する Sheets("Sheet3").Cells(3, 1).Value = "http://quote.yahoo.co.jp/q?s=" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 1, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 2, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 3, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 4, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 5, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 6, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 7, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 8, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 9, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 10, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 11, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 12, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 13, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 14, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 15, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 16, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 17, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 18, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 19, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 20, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 21, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 22, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 23, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 24, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 25, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 26, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 27, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 28, 1) & "+" _ & Sheets("Sheet2").Cells(current_point01 * 30 + 29, 1) & "+" & Sheets("Sheet2").Cells(current_point01 * 30 + 30, 1) & "&d=t" ' 各銘柄の日足用WEBクエリをIQY(TXT形式)ファイルとして保存 Sheets("Sheet3").Select ChDir "C:\mysql\mysqldata\excel\webquery" ActiveWorkbook.SaveAs Filename:= _ "C:\mysql\mysqldata\excel\webquery\" _ & "y_grp_finance" & Right("00" & (current_point01 + 1), 3) & ".iqy", FileFormat:=xlText, CreateBackup:=False Sheets("y_grp_finance" & Right("00" & (current_point01 + 1), 3)).Name = "Sheet3" ' 次の検索ポイントへ current_point01 = current_point01 + 1 Loop End Sub