VBA技术需求：通过下拉菜单刷新页面并抓取纳斯达克历史行情数据

Here's a practical VBA solution that automates selecting the 10-year timeframe via the dropdown, refreshes the page, and extracts historical price/volume data for multiple stocks into Excel. I'll break down the code and key adjustments you need for your target site:

Full VBA Code #

Drop this into an Excel VBA module (press Alt+F11 to access the editor):

Sub Scrape10YearStockData()
    Dim ie As Object
    Dim ws As Worksheet
    Dim stockSymbols As Variant
    Dim symbol As Variant
    Dim timeframeDropdown As Object
    Dim dataTable As Object
    Dim rowCounter As Integer, colCounter As Integer
    
    ' Set up output worksheet
    Set ws = ThisWorkbook.Sheets("StockData")
    ws.Cells.Clear
    ws.Range("A1:G1") = Array("Symbol", "Date", "Open", "High", "Low", "Close", "Volume")
    rowCounter = 2
    
    ' Add your target stock symbols here
    stockSymbols = Array("AAPL", "MSFT", "GOOGL", "AMZN", "TSLA")
    
    ' Initialize Internet Explorer (visible for debugging; set to False for background runs)
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    
    For Each symbol In stockSymbols
        ' Navigate to the stock's historical data page
        ' Replace this URL with the actual pattern used by your target site
        ie.Navigate "https://your-target-site.com/historical/" & symbol
        
        ' Wait for page to load completely
        Do While ie.Busy Or ie.ReadyState <> 4
            DoEvents
        Loop
        
        ' Locate the timeframe dropdown by its ID
        On Error Resume Next
        Set timeframeDropdown = ie.Document.getElementById("ddlTimeFrame")
        On Error GoTo 0
        
        If Not timeframeDropdown Is Nothing Then
            ' Select the 10-year option (check the HTML source for the correct value! e.g., "10y")
            timeframeDropdown.Value = "10y"
            
            ' Trigger the onchange event to refresh data (matches the site's existing function)
            ie.Document.parentWindow.execScript "getQuotes(false)"
            
            ' Wait for refreshed data to load (adjust delay if site is slow)
            Application.Wait Now + TimeValue("00:00:03")
            Do While ie.Busy Or ie.ReadyState <> 4
                DoEvents
            Loop
            
            ' Locate the historical data table (adjust selector to match your site's table)
            On Error Resume Next
            Set dataTable = ie.Document.querySelector("table.historical-prices") ' Example selector
            On Error GoTo 0
            
            If Not dataTable Is Nothing Then
                ' Extract table rows to Excel
                Dim tableRow As Object
                For Each tableRow In dataTable.Rows
                    colCounter = 2
                    ws.Cells(rowCounter, 1) = symbol
                    Dim tableCell As Object
                    For Each tableCell In tableRow.Cells
                        ws.Cells(rowCounter, colCounter) = tableCell.innerText
                        colCounter = colCounter + 1
                    Next tableCell
                    rowCounter = rowCounter + 1
                Next tableRow
            Else
                MsgBox "Data table not found for " & symbol, vbExclamation
            End If
        Else
            MsgBox "Timeframe dropdown missing for " & symbol, vbExclamation
        End If
    Next symbol
    
    ' Clean up resources
    ie.Quit
    Set ie = Nothing
    Set timeframeDropdown = Nothing
    Set dataTable = Nothing
    
    MsgBox "Scraping complete! Data saved to StockData sheet.", vbInformation
End Sub

Critical Adjustments for Your Target Site #

• URL Pattern: Replace https://your-target-site.com/historical/ with the actual URL structure the site uses for individual stock historical pages.
• 10-Year Dropdown Value: Check the HTML source of the page for the <option> tag labeled "10 Years"—use its value attribute (e.g., if it's <option value="decade">10 Years</option>, change timeframeDropdown.Value = "10y" to "decade").
• Data Table Selector: Use your browser's dev tools to inspect the historical data table. Replace table.historical-prices with the correct CSS selector (e.g., table#historicalTable if the table has an ID).
• Wait Times: If the site loads slowly, increase the Application.Wait duration (e.g., TimeValue("00:00:05")).

How to Run #

1. Create a worksheet named StockData in your Excel file.
2. Open the VBA Editor (Alt+F11), insert a new module, and paste the code.
3. Adjust the sections noted above to match your target site.
4. Run the macro (press F5 in the editor or assign it to a button in Excel).

内容的提问来源于stack exchange，提问作者user9718859