使用Excel VBA下载和格式化Web数据 [英] Downloading and Formatting Web Data with Excel VBA

问题描述

我需要帮助创建一个VBA宏，可以直接从雅虎财经的历史报价网站下载收盘价格数据，并将数据导入Excel电子表格列。

I need help creating a VBA macro that downloads closing price data directly from Yahoo Finance's Historic Quotes website and imports the data into an Excel spreadsheet column.

背景信息：
这是到Yahoo Finance的历史报价数据库的链接 -

Background information: This is the link to Yahoo Finance's Historical Quote database -

要下载作为TXT文件：
http://ichart.finance.yahoo.com /table.txt?s= StockTicker &安培; D = EndingMonth 急症= EndingDay &安培; F = EndingYear &安培; G = D&安培; A = EndingMonth 和b = EndingDay& c =EndingYear& ignore = .txt

To download as a TXT file: http://ichart.finance.yahoo.com/table.txt?s="StockTicker"&d="EndingMonth"&e="EndingDay"&f="EndingYear"&g=d&a="EndingMonth"&b="EndingDay"&c="EndingYear"&ignore=.txt

格式化问题：
默认情况下，即使用Excel的Web数据导入向导，Excel导入整个表，其中包含比需要更多的列。我试图隔离关闭列。我创建了一个格式化表以隔离关闭列的宏，但是此宏需要我手动从雅虎财经下载数据作为txt文件：

Formatting Issue: By default, i.e. using Excel's Web Data Import Wizard, Excel imports the entire table which includes more columns than needed. I am trying to isolate the "close" column. I created a macro that formats the table to isolate the "close" column, but this macro requires me to manually download the data from Yahoo Finance as a txt file:

Sub Test_DownloadTextFile()
    Dim text As String
    'The variables for this URL (in light blue) should be retrieved from the spreadsheet, i.e. "StockTicker" will reference a cell with the ticker symbol "AAPL" in it and that will appear in the URL'
    text = DownloadTextFile("http://ichart.finance.yahoo.com/table.txt?s="StockTicker"&d="EndingMonth"&e="EndingDay"&f="EndingYear"&g=d&a="EndingMonth"&b="EndingDay"&c="EndingYear"&ignore=.txt")

    'At this point I should have the historical quotes table stored in the variable text. How do I select the 4th column and import it into a specific spreadsheet column?'
    Debug.Print text
  End Sub

如何创建一个宏：
1.指向关键变量的电子表格，例如StockTicker，EndingMonth等。
2.从Yahoo Finance
下载相应的历史数据3.将数据收盘价格数据作为单列导入电子表格

How can I create a macro that: 1. Refers to the spreadsheet for key variables, e.g. "StockTicker", "EndingMonth", etc.. 2. Downloads the corresponding historic data from Yahoo Finance 3. Imports the data closing price data as a single column into the spreadsheet

我非常感谢这个问题的实际解决方案。让我知道，如果我需要澄清我的问题或手头的任务。谢谢！

I would very much appreciate a practical solution to this problem. Let me know if I need clarify my question or the task at hand. Thank you!

推荐答案

建议：这似乎是Web查询的完美情况。

你有什么理由不用吗？您可以稍后复制所需的列。
您没有指定Excel版本，但是在2003年它是数据/导入外部数据。

Suggestion: this seems to be the perfect case for a Web Query.
Do you have any reason not to use that ? You can copy just the columns you need afterwards. You did not specify you Excel version, but on 2003 it's on Data/Import External data.

这篇关于使用Excel VBA下载和格式化Web数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！