VBA运用3_股票_Module1

Public Sub get_指数_info()

Dim start_cow As Integer
Dim start_clo As Integer
Dim code As Double

Dim time_now
Dim time_list1
Dim time_list2
Dim time_list3

start_cow = 4
start_clo = 5

time_now = TimeValue(Now())
time_list1 = TimeValue("4:00")
time_list2 = TimeValue("16:12")
time_list3 = TimeValue("16:00")


'---------------------------------------------国内指数一覧[S]------------------------------------------------------

    date_check = Worksheets("指数一覧").Cells(start_cow, start_clo + 6).Value
    If Date <> date_check Then

'S---------------------sz--------------------------S
        code = 1
        Call data_edit(code, start_cow, start_clo)
'E---------------------sz--------------------------E

'S---------------------sh--------------------------S
        code = 399001
        Call data_edit(code, start_cow + 2, start_clo)
'E---------------------sh--------------------------E

'S---------------------cyb--------------------------S
        code = 399006
        Call data_edit(code, start_cow + 4, start_clo)
'E---------------------cyb--------------------------E

'S---------------------hwgf--------------------------S
        code = 757
        Call data_edit(code, start_cow + 26, start_clo)
'E---------------------hwgf--------------------------E

        If time_now > time_list2 Then
            Worksheets("指数一覧").Cells(start_cow, start_clo + 6).Value = Date
        End If

End If

'---------------------------------------------国内指数一覧[E]------------------------------------------------------

'---------------------------------------------国際指数一覧[S]------------------------------------------------------

'   Call get_inter_info(start_cow + 12, start_clo - 1, "^IXIC", "NASDAQ")
'   Call get_inter_info(start_cow + 12, start_clo - 1, "^GSPC", "S&P 500")
'   Call get_inter_info(start_cow + 12, start_clo - 1, "^FCHI", "CAC 40")
'   Call get_inter_info(start_cow + 12, start_clo - 1, "^FTSE", "FTSE 100")
'   Call get_inter_info(start_cow + 12, start_clo - 1, "^GDAXI", "DAX")
'   Call get_inter_info(start_cow + 12, start_clo - 1, "^N225", "NIKKEI 225")
'   Call get_inter_info(start_cow + 12, start_clo - 1, "^HSI", "HANG SENG")

    
    start_cow = start_cow - 3
'   Part1
'   Update(S)
'   date_check = Worksheets("指数一覧").Cells(start_cow + 12, start_clo + 6).Value
    date_check = Date
'   Update(E)
    If Date <> date_check Then

        Call get_inter_info(start_cow + 12, start_clo - 1, "^IXIC")
        Call get_inter_info(start_cow + 14, start_clo - 1, "^GSPC")
        Call get_inter_info(start_cow + 20, start_clo - 1, "^FCHI")
        Call get_inter_info(start_cow + 22, start_clo - 1, "^FTSE")
        Call get_inter_info(start_cow + 24, start_clo - 1, "^GDAXI")
        Call get_inter_info(start_cow + 16, start_clo - 1, "^N225")
        Call get_inter_info(start_cow + 18, start_clo - 1, "^HSI")
    
        If time_now > time_list1 Then
            Worksheets("指数一覧").Cells(start_cow + 12, start_clo + 6).Value = Date
        End If
        
    End If
    
'   Part2
'   Update(S)
'   date_check = Worksheets("指数一覧").Cells(start_cow + 16, start_clo + 6).Value
    date_check = Date
'   Update(E)
    If Date <> date_check Then
        
'        Call get_inter_info(start_cow + 16, start_clo - 1, "^N225")
'        Call get_inter_info(start_cow + 18, start_clo - 1, "^HSI")
    
        If time_now > time_list3 Then
            Worksheets("指数一覧").Cells(start_cow + 16, start_clo + 6).Value = Date
        End If
        
    End If

'---------------------------------------------国際指数一覧[E]------------------------------------------------------


End Sub


Public Sub data_edit(code, start_cow, start_clo)
    
    Dim flag_up As String
    
    flag_up = "↑"

    
    If (code < 600000 And code > 1) Then
    
        URL = "http://qt.gtimg.cn/q=sz" & Format(code, "000000")
        
    Else
        
        URL = "http://qt.gtimg.cn/q=sh" & Format(code, "000000")
        
    End If

    With CreateObject("msxml2.xmlhttp")

        .Open "GET", URL, False

        .send

        sp = Split(.responsetext, "~")

        If UBound(sp) > 3 Then

            Worksheets("指数一覧").Cells(start_cow, start_clo).Value = sp(3)
            If sp(31) < 0 Then flag_up = "↓"
            Worksheets("指数一覧").Cells(start_cow, start_clo + 1).Value = flag_up & sp(31)
            Worksheets("指数一覧").Cells(start_cow, start_clo + 2).Value = sp(32) & "%"
            Worksheets("指数一覧").Cells(start_cow, start_clo + 4).Value = sp(33)
            Worksheets("指数一覧").Cells(start_cow + 1, start_clo + 4).Value = sp(34)
            Worksheets("指数一覧").Cells(start_cow, start_clo + 5).Value = Format(sp(37) / 10000, "0.00") & "億"
            
            If (code = 757) Then
                start_cow1 = 169
                start_clo1 = 13
                
                Worksheets("FBC備考").Cells(start_cow1 - 1, start_clo1).Value = sp(19)
                Worksheets("FBC備考").Cells(start_cow1 - 1, start_clo1 + 1).Value = sp(20)
                
                Worksheets("FBC備考").Cells(start_cow1 - 2, start_clo1).Value = sp(21)
                Worksheets("FBC備考").Cells(start_cow1 - 2, start_clo1 + 1).Value = sp(22)
                
                Worksheets("FBC備考").Cells(start_cow1 - 3, start_clo1).Value = sp(23)
                Worksheets("FBC備考").Cells(start_cow1 - 3, start_clo1 + 1).Value = sp(24)
                
                Worksheets("FBC備考").Cells(start_cow1 - 4, start_clo1).Value = sp(25)
                Worksheets("FBC備考").Cells(start_cow1 - 4, start_clo1 + 1).Value = sp(26)
                
                Worksheets("FBC備考").Cells(start_cow1 - 5, start_clo1).Value = sp(27)
                Worksheets("FBC備考").Cells(start_cow1 - 5, start_clo1 + 1).Value = sp(28)
                
                '1
                Worksheets("FBC備考").Cells(start_cow1, start_clo1).Value = sp(9)
                Worksheets("FBC備考").Cells(start_cow1, start_clo1 + 1).Value = sp(10)
                '2
                Worksheets("FBC備考").Cells(start_cow1 + 1, start_clo1).Value = sp(11)
                Worksheets("FBC備考").Cells(start_cow1 + 1, start_clo1 + 1).Value = sp(12)
                '3
                Worksheets("FBC備考").Cells(start_cow1 + 2, start_clo1).Value = sp(13)
                Worksheets("FBC備考").Cells(start_cow1 + 2, start_clo1 + 1).Value = sp(14)
                '4
                Worksheets("FBC備考").Cells(start_cow1 + 3, start_clo1).Value = sp(15)
                Worksheets("FBC備考").Cells(start_cow1 + 3, start_clo1 + 1).Value = sp(16)
                '5
                Worksheets("FBC備考").Cells(start_cow1 + 4, start_clo1).Value = sp(17)
                Worksheets("FBC備考").Cells(start_cow1 + 4, start_clo1 + 1).Value = sp(18)


            
            End If

        End If

    End With

    If flag_up = "↑" Then
    
        Worksheets("指数一覧").Cells(start_cow, start_clo).Font.Color = vbRed
        Worksheets("指数一覧").Cells(start_cow, start_clo + 1).Font.Color = vbRed
        Worksheets("指数一覧").Cells(start_cow, start_clo + 2).Font.Color = vbRed
        Worksheets("指数一覧").Cells(start_cow, start_clo + 3).Font.Color = vbRed
        Worksheets("指数一覧").Cells(start_cow + 1, start_clo + 3).Font.Color = vbRed
        Worksheets("指数一覧").Cells(start_cow, start_clo + 4).Font.Color = vbRed
        Worksheets("指数一覧").Cells(start_cow + 1, start_clo + 4).Font.Color = vbRed
        Worksheets("指数一覧").Cells(start_cow, start_clo + 5).Font.Color = vbRed
    Else
    
        Worksheets("指数一覧").Cells(start_cow, start_clo).Font.Color = vbGreen
        Worksheets("指数一覧").Cells(start_cow, start_clo + 1).Font.Color = vbGreen
        Worksheets("指数一覧").Cells(start_cow, start_clo + 2).Font.Color = vbGreen
        Worksheets("指数一覧").Cells(start_cow, start_clo + 3).Font.Color = vbGreen
        Worksheets("指数一覧").Cells(start_cow + 1, start_clo + 3).Font.Color = vbGreen
        Worksheets("指数一覧").Cells(start_cow, start_clo + 4).Font.Color = vbGreen
        Worksheets("指数一覧").Cells(start_cow + 1, start_clo + 4).Font.Color = vbGreen
        Worksheets("指数一覧").Cells(start_cow, start_clo + 5).Font.Color = vbGreen
        
    End If

End Sub


Public Sub get_inter_info(start_cow, start_clo, code)

    Dim flag_up As String
    
    flag_up = "↑"

        
    URL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & code & "&f=sl1c1p2l"

    With CreateObject("msxml2.xmlhttp")

        .Open "GET", URL, False

        .send

        sp = Split(.responsetext, ",")

        If UBound(sp) > 3 Then
            
            Cells(start_cow, start_clo + 1).Value = sp(1)

            If sp(2) < 0 Then flag_up = "↓"
            Cells(start_cow, start_clo + 2).Value = flag_up & sp(2)
            
            n = Len(sp(3))
            Cells(start_cow, start_clo + 3).Value = Mid(sp(3), 2, n - 2)

        End If

    End With


    If flag_up = "↑" Then
    
        Worksheets("指数一覧").Cells(start_cow, start_clo).Font.Color = vbRed
        Worksheets("指数一覧").Cells(start_cow, start_clo + 1).Font.Color = vbRed
        Worksheets("指数一覧").Cells(start_cow, start_clo + 2).Font.Color = vbRed
        Worksheets("指数一覧").Cells(start_cow, start_clo + 3).Font.Color = vbRed

    Else
    
        Worksheets("指数一覧").Cells(start_cow, start_clo).Font.Color = vbGreen
        Worksheets("指数一覧").Cells(start_cow, start_clo + 1).Font.Color = vbGreen
        Worksheets("指数一覧").Cells(start_cow, start_clo + 2).Font.Color = vbGreen
        Worksheets("指数一覧").Cells(start_cow, start_clo + 3).Font.Color = vbGreen

    End If

End Sub