|
楼主 |
发表于 2014-11-9 13:53
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 renahu 于 2014-11-9 14:23 编辑
- Sub 自选股查询() '练习八
- Dim url$, STxt1$, STxt2$, arr, brr(), crr(), drr(0 To 9) As String, myObj As Shape
- Cells.Clear
- For Each myObj In ActiveSheet.Shapes
- If Not myObj.Name Like "Button*" Then myObj.Delete
- Next
- [a:z].HorizontalAlignment = xlCenter
- [a20:z20].Font.ColorIndex = 2
- [a20:z20].Interior.Color = RGB(25, 156, 223)
- Top = [{"代码","名称","最新价","涨跌幅","涨跌额","总手","现手","买入价","卖出价","换手","金额","市盈率d","最高","最低","开盘","昨收","涨速","振幅","均价","内盘","外盘","市净率","总股本","总市值","流通股本","流通市值"}]
- [a20].Resize(1, 26) = Top
- [a21:b65536].Font.ColorIndex = 32
- [a21:a65536].NumberFormatLocal = "000000"
- Range("c21:c65536, h21:i65536, m21:o65536, s21:s65536").NumberFormatLocal = "0.00"
- Range("d21:d65536, q21:q65536").NumberFormatLocal = "[红色]#,##0.00%;[颜色10]-#,##0.00%"
- [e21:e65536].NumberFormatLocal = "[红色]#,##0.00;[颜色10]-#,##0.00"
- Range("l21:l65536, p21:p65536, v21:v65536").NumberFormatLocal = "0.00"
- [f21:f65536].NumberFormatLocal = "0.00万"
- Range("j21:j65536, r21:r65536").NumberFormatLocal = "0.00%"
- [t21:t65536].NumberFormatLocal = "[颜色10]0.00万"
- [u21:u65536].NumberFormatLocal = "[红色]0.00万"
- Range("k21:k65536, w21:z65536").NumberFormatLocal = "0.00亿"
- With CreateObject("msxml2.xmlhttp")
- '抓数据
- url = "http://nufm.dfcfw.com/EM_Finance2014NumericApplication/JS.aspx?ps=500&token=64a483cbad8b666efa51677820e6b21c&type=CT"
- url = url & "&cmd=6001981,0008512,0021042,0020172,3002052,0009972,0021612,6005841,0007012,6001001,6007561,0009772,3000772,0005032,6007701,6004601,"
- url = url & "6001711,6000731,6006801,6000501,6007761,6003451,6004981,6002891,6006771,0000632,6008311,6000881,0009172,6001181,6003431,0007682,6001511,"
- url = url & "0005622,6000301,6008371,0005632,6016281,6013181,6010091,6000361,6000161,0000012,6013981,6000281,6018571,0000022,6003831,0000242,6000481,"
- url = url & "6003761,0000312,6001591,6015881,0006162&sty=CTALL&cb=getStockFullInfo&js=([(x)],true)&0.6800919628846551"
- .Open "get", url, False
- .send
- '整理数据
- strText = Replace(Split(Split(.ResponseText, "([""")(1), """]")(0), "%", "")
- arr = Split(strText, """,""")
- ReDim brr(0 To UBound(arr), 0 To 37)
- ReDim crr(0 To UBound(arr), 0 To 26)
- For i = 0 To UBound(arr)
- For x = 0 To 37
- brr(i, x) = Split(arr(i), ",")(x)
- Next
- Next
- For i = 0 To UBound(brr)
- For x = 0 To UBound(brr, 2)
- Select Case x
- Case 1
- crr(i, 0) = brr(i, x)
- Case 2
- crr(i, 1) = brr(i, x)
- Case 3
- crr(i, 2) = IIf(brr(i, x) = "-", " " & brr(i, x), brr(i, x))
- Case 4
- crr(i, 3) = IIf(brr(i, x) = "-", " " & brr(i, x), Val(brr(i, x)) / 100)
- Case 5
- crr(i, 4) = IIf(brr(i, x) = "-", " " & brr(i, x), brr(i, x))
- Case 6
- crr(i, 5) = IIf(brr(i, x) = "-", " " & brr(i, x), Round(Val(brr(i, x)) / 10000, 2))
- Case 7
- crr(i, 6) = IIf(brr(i, x) = "-", " " & brr(i, x), brr(i, x))
- Case 8
- crr(i, 7) = IIf(brr(i, x) = "-", " " & brr(i, x), brr(i, x))
- Case 9
- crr(i, 8) = IIf(brr(i, x) = "-", " " & brr(i, x), brr(i, x))
- Case 11
- crr(i, 9) = IIf(brr(i, x) = "-", " " & brr(i, x), Val(brr(i, x)) / 100)
- Case 12
- crr(i, 10) = IIf(brr(i, x) = "-", " " & brr(i, x), IIf(Round(Val(brr(i, x)) / 100000000, 2) > 1, Round(Val(brr(i, x)) / 100000000, 2) & "亿", Round(Val(brr(i, x)) / 10000, 2) & "万"))
- Case 13
- crr(i, 11) = IIf(brr(i, x) = "-", " " & brr(i, x), brr(i, x))
- Case 14
- crr(i, 12) = IIf(brr(i, x) = "-", " " & brr(i, x), brr(i, x))
- Case 15
- crr(i, 13) = IIf(brr(i, x) = "-", " " & brr(i, x), brr(i, x))
- Case 16
- crr(i, 14) = IIf(brr(i, x) = "-", " " & brr(i, x), brr(i, x))
- Case 17
- crr(i, 15) = IIf(brr(i, x) = "-", " " & brr(i, x), brr(i, x))
- Case 10
- crr(i, 16) = IIf(brr(i, x) = "-", " " & brr(i, x), Val(brr(i, x)) / 100)
- Case 18
- crr(i, 17) = IIf(brr(i, x) = "-", " " & brr(i, x), Val(brr(i, x)) / 100)
- Case 22
- crr(i, 18) = IIf(brr(i, x) = "-", " " & brr(i, x), brr(i, x))
- Case 23
- crr(i, 19) = IIf(brr(i, x) = "-", " " & brr(i, x), Round(Val(brr(i, x)) / 10000, 2))
- Case 24
- crr(i, 20) = IIf(brr(i, x) = "-", " " & brr(i, x), Round(Val(brr(i, x)) / 10000, 2))
- Case 28
- crr(i, 21) = brr(i, x)
- Case 29
- crr(i, 22) = Round(brr(i, x) / 100000000, 2)
- Case 30
- crr(i, 23) = Round(brr(i, x) / 100000000, 2)
- Case 31
- crr(i, 24) = Round(brr(i, x) / 100000000, 2)
- Case 32
- crr(i, 25) = Round(brr(i, x) / 100000000, 2)
- End Select
- Next
- Next
- Cells(1, 1).Select: ActiveSheet.Pictures.Insert "http://hqpiczs.dfcfw.com/em_quote2010pictureproducter/picture/0000011rsindex.png?r=1415412542192"
- Cells(1, 5).Select: ActiveSheet.Pictures.Insert "http://hqpiczs.dfcfw.com/em_quote2010pictureproducter/picture/3990012rsindex.png?r=1415425177569"
- Cells(1, 9).Select: ActiveSheet.Pictures.Insert "http://hqgnqhpic.eastmoney.com/EM_Futures2010PictureProducter/Index.aspx?imagetype=RSIndex&ID=IFDYLX1&r=1415412573131"
- Cells(1, 13).Select: ActiveSheet.Pictures.Insert "http://hqpiczs.dfcfw.com/em_quote2010pictureproducter/picture/3990062rsindex.png?r=1415412627193"
- Cells(1, 18).Select: ActiveSheet.Pictures.Insert "http://hqhkpic.eastmoney.com/EM_Quote2010PictureProducter/Index.aspx?ImageType=RSIndex&ID=1100005&r=1415412669387"
- Cells(12, 1).Select: ActiveSheet.Pictures.Insert "http://hqgbpic.eastmoney.com/mrchart/0021042.gif?=2013-03-17 14:47:43"
- Cells(12, 5).Select: ActiveSheet.Pictures.Insert "http://hqgbpic.eastmoney.com/mrchart/0020172.gif?=2013-03-17 14:47:43"
- Cells(12, 9).Select: ActiveSheet.Pictures.Insert "http://hqgbpic.eastmoney.com/mrchart/3002052.gif?=2013-03-17 14:47:43"
- Cells(12, 13).Select: ActiveSheet.Pictures.Insert "http://hqgjgppic.eastmoney.com/EM_Quote2010PictureProductor/Picture/INDU7RSINDEX.png?r=1415412817336"
- Cells(12, 18).Select: ActiveSheet.Pictures.Insert "http://hqgjgppic.eastmoney.com/EM_Quote2010PictureProductor/Picture/CCMP7RSINDEX.png?r=1415425260273"
- '往表里写数据
- [a21].Resize(UBound(crr) + 1, 26) = crr
- '设置颜色
- For i = 0 To UBound(crr)
- For j = 2 To UBound(crr, 2) - 7
- Select Case j
- Case 2, 7, 8, 12, 13, 14, 18
- If crr(i, j) > crr(i, 15) Then
- Cells(i + 21, j + 1).Font.ColorIndex = 3
- ElseIf crr(i, j) < crr(i, 15) Then
- Cells(i + 21, j + 1).Font.ColorIndex = 10
- End If
- End Select
- Next
- Next
- '画表格线
- Cells.Borders.LineStyle = xlNone
- With Range("a21:z" & Range("a65536").End(xlUp).Row).Borders
- .LineStyle = xlContinuous
- .Weight = xlThin
- .ColorIndex = xlAutomatic
- End With
- End With
- End Sub
复制代码 这是平时炒股习惯看的自选股列表,几乎一模一样,你可以在此基础上自行修改。 |
评分
-
1
查看全部评分
-
|