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
