Downloading and Formatting Web Data with Excel VBA

Question

Hi,

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.

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

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

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

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!

Answer 1

Check out this SO discussion. Several suggestions that seem worthwhile.

Answer 2

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.