VBA: 创建Web查询获取基金历史净值

2022-09-20 14:29:48 浏览数 (1)

文章背景：工作生活中，有时需要将网页中的数据导入Excel，然后进行数据处理。为了便于数据更新，可以创建Web查询。下面以获取某只基金的单位净值为例，查看该基金近一个月的单位净值。

网址：http://jingzhi.funds.hexun.com/database/jzzs.aspx?fundcode=270010

在查询基金净值的按钮中添加如下的宏代码：

代码语言：javascript复制
Option Explicit

Sub QueryStarter()

    'This is an adaptation from some code found at:
    'https://stackoverflow.com/questions/19306832/web-query-vba-refresh
    
    Dim time_ini As Date, ws As Worksheet, url As String
    
    '计时
    time_ini = Timer
    
    Set ws = Worksheets.Add
    
    url = "URL;" & Worksheets("Web查询").Range("B1")
    
    With ws.QueryTables.Add(Connection:=url, Destination:=ws.Range("A1"))
    
        .Name = "My Query"
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        
    End With
    
    MsgBox "Done!  " & vbCrLf & "用时：" & Format(Timer - time_ini, "0.0s")

End Sub

代码运行效果：http://mpvideo.qpic.cn/0bf2naaccaaaqiandnc6ynqfa2gdefuaaiia.f10002.mp4?dis_k=5dea541298abe82e64b6e62700b75b78&dis_t=1663655364&vid=wxv_1799230978578513923&format_id=10002&support_redirect=0&mmversion=false

参考资料：

[1] Excel VBA编程实践宝典[M]

[2] Excel/VBA for Creative Problem Solving, Part 3 (Projects)(https://www.coursera.org/learn/excel-vba-for-creative-problem-solving-part-3-projects)

[2] Web Query VBA Refresh(https://stackoverflow.com/questions/19306832/web-query-vba-refresh)

vba https http 网络安全 网站

0 人点赞