Attribute VB_Name = "gy08_n225"

' 各銘柄の日足データと財務情報を取得するためのクエリファイルを作成する
' 入力ファイル："C:\mysql\mysqldata\csv\n225??????.csv"
Option Base 1
Sub gy08_n225()
    '警告の表示を停止
    Application.DisplayAlerts = False
    Const csv_dir As String = "C:\mysql\mysqldata\csv" ' CSVファイルのディレクトリ
    
' -----------------------------------------------------------------------------
    Dim n225_data(10000, 7) As String
    Dim search_flag As Integer
    Dim page_num As Integer
    Dim n225_paste_row As Integer
    n225_paste_row = 1

    Sheets("Sheet1").Select
    
    Do While search_flag = 0
        ' シートのクリア
        Sheets("Sheet1").Cells.ClearContents
        
        ' WEBクエリで各会社の基本情報を取得する
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://chart.yahoo.com/d?a=0&b=1&c=1970&d=10&e=30&f=2003&s=^N225&y=" & page_num & "&g=d", _
            Destination:=Sheets("Sheet1").Range("A1"))
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .Refresh BackgroundQuery:=False
        End With
' -----------------------------------------------------------------------------
        ' WEBデータを加工して"Sheet2"へ


        
        ' 文字列「Date」の検索
        Range("A9:A15").Select
        Selection.Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Activate
        
        start_point = ActiveCell.Row + 1  '最初のデータの行を取得
        
        ' 文字列「Next」の検索
        Range("A9:A230").Select
        Set next_str = Range("A9:A230").Find(What:="Next", _
            After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False)
            
        ' 文字列「Next」がない場合は、終了フラグ search_flag を1にする
        If next_str Is Nothing Then
            search_flag = 1
        End If
            
        day_record = start_point
        Do While day_record <= 200
            n225_data(n225_paste_row, 1) = Cells(day_record, 1).Value
            n225_data(n225_paste_row, 2) = Cells(day_record, 3).Value
            n225_data(n225_paste_row, 3) = Cells(day_record, 4).Value
            n225_data(n225_paste_row, 4) = Cells(day_record, 5).Value
            n225_data(n225_paste_row, 5) = Cells(day_record, 6).Value
            n225_data(n225_paste_row, 6) = Cells(day_record, 7).Value
            n225_data(n225_paste_row, 7) = Cells(day_record, 8).Value

            n225_paste_row = n225_paste_row + 1
            day_record = day_record + 1
        Loop
            
        page_num = page_num + 200
            

    Loop

    ' セルに書き込み
    Sheets("Sheet2").Select
    Range(Cells(1, 1), Cells(n225_paste_row - 1, 7)).Value = n225_data

' ------------------------------------------------------------------------------    ' データ編集

    Dim cut_yyyy As String
    Dim cut_mm As String
    Dim cut_dd As String
    ' カラムAの日付データを8桁の整数型へ変換
    For Each date_data In Range(Cells(1, 1), Cells(n225_paste_row + 10, 1))
        cut_yyyy = Mid(date_data, 8, 2)
        If cut_yyyy = "" Then
            Exit For
        End If
        If Int(cut_yyyy) >= 70 Then
            cut_yyyy = "19" & cut_yyyy
        ElseIf Int(cut_yyyy) < 50 Then
            cut_yyyy = "20" & cut_yyyy
        Else
            date_data.Value = ""
        End If
        cut_mm = Mid(date_data, 1, 3)
        Select Case cut_mm
            Case "Jan"
                cut_mm = "01"
            Case "Feb"
                cut_mm = "02"
            Case "Mar"
                cut_mm = "03"
            Case "Apr"
                cut_mm = "04"
            Case "May"
                cut_mm = "05"
            Case "Jun"
                cut_mm = "06"
            Case "Jul"
                cut_mm = "07"
            Case "Aug"
                cut_mm = "08"
            Case "Sep"
                cut_mm = "09"
            Case "Oct"
                cut_mm = "10"
            Case "Nov"
                cut_mm = "11"
            Case "Dec"
                cut_mm = "12"
        End Select
        cut_dd = Mid(date_data, 5, 2)

        date_data.Value = cut_yyyy & cut_mm & cut_dd
        date_data.NumberFormatLocal = "G/標準"
    Next date_data


    ' ファイル名を日付にしてCSV形式で保存
    Sheets("Sheet2").Select
    ActiveWorkbook.SaveAs Filename:=csv_dir & "\" & "yn225" & Mid(Date$, 1, 4) & Mid(Date$, 6, 2) & ".csv", _
        FileFormat:=xlCSV, CreateBackup:=False

    Sheets("yn225" & Mid(Date$, 1, 4) & Mid(Date$, 6, 2)).Name = "Sheet2"
        
' -----------------------------------------------------------------------------
        


End Sub