Excel vba 读取上证指数

最新推荐文章于 2025-04-06 22:45:00 发布

wentise

最新推荐文章于 2025-04-06 22:45:00 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： vba 文章标签： vba

本文链接：https://blog.youkuaiyun.com/wentise/article/details/84390007

vba 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一种使用VBA编程技术抓取上海股市指数数据的方法。通过创建IE对象并利用其加载新浪财经的历史数据页面，可以实现自动化获取指定年份内的股票市场历史记录。该过程涉及网页导航、数据抓取及在Excel中组织数据等功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >




Public Sub 抓上海指数全部()
'删除上次结果sheet
delectsh


    Dim ie, dmt, an, selectONE
    Set ie = CreateObject("InternetExplorer.Application")    '创建一个IE对象
    With ie
        .Visible = False    '显示它
        .navigate "http://vip.stock.finance.sina.com.cn/corp/go.php/vMS_MarketHistory/stockid/000001/type/S.phtml"    '加载某个页面
        Do Until .ReadyState = 4    '等待页面加载完毕
            DoEvents
        Loop
        Set dmt = .document    '将IE浏览器加载的页面文档，赋予dmt变量
        Set selectONE = dmt.getElementsByName("year")

        With selectONE
        Set option_year = .Item(0)
           For i = 0 To option_year.Length - 1
           Dim year
           year = option_year.Item(i).innerText
              '创建年份sheet
              newsheet (year)
              抓上海指数按年 (year)
           Next i
        End With


    End With
    Set ie = Nothing

End Sub

Function 抓上海指数按年(year As String)
Dim URL, last_row, i
last_row = 0
Cells.Clear
For i = 1 To 4
'http://vip.stock.finance.sina.com.cn/corp/go.php/vMS_MarketHistory/stockid/000001/type/S.phtml?year=2012&jidu=2
URL = "http://vip.stock.finance.sina.com.cn/corp/go.php/vMS_MarketHistory/stockid/000001/type/S.phtml?year=" & year & "&jidu=" & i

Set sheet_year = Worksheets(year)
With sheet_year.QueryTables.Add(Connection:= _
                                     "URL;" & URL, _
                                     Destination:=sheet_year.Range("A" & last_row + 1))
                .WebTables = "FundHoldSharesTable"
                .Refresh BackgroundQuery:=False
            End With
            last_row = sheet_year.[A65536].End(xlUp).Row
Set sheet_year = Nothing

Next
End Function

Public Sub delectsh()
Dim c As Worksheet
For Each c In Worksheets
 If Left(c.name, 3) <> "set" Then '这里的“set”区分大小写
 Application.DisplayAlerts = False '删除时不用确认
 c.Delete
 End If
Next c
End Sub


Function newsheet(name As String)
    sname = name
    'Sheets(Sheets.Count).name
    'abc = DateSerial(Left(sname, 4), Mid(sname, 5, 2), Right(sname, 2)) + 1
    'sname = year(abc) & Format(Month(abc), "00") & Format(Day(abc), "00")
    '===========================================================
    Sheets.Add
    ActiveSheet.name = sname
    '===========================================================
End Function