Download Historical Stock Quotes

[traderhome]

This is what I did to download Historical Stock Quotes from Yahoo
Finance.

Just specify the period you want to download in the occUrl below and
you also need to create a folder in your C:\ call "Historical" as in
teh occLocalFile below.

********************************************************************
Sub DownloadData()

Set occXMLHTTP = CreateObject("Microsoft.XMLHTTP")

For x = 1 To 300
fn = Worksheets("Portfolio").Cells(x, 1)
fname = Worksheets("Portfolio").Cells(x, 1) & ".txt"
occUrl = "http://ichart.finance.yahoo.com/table.csv?s=" & fn &
"&d=2&e=7&f=2007&g=d&a=2&b=7&c=2002&ignore=.csv"
occLocalFile = "c:\historical\" & fname
occXMLHTTP.Open "GET", occUrl, False
occXMLHTTP.send
occArray = occXMLHTTP.ResponseBody
occfile = 1

Open occLocalFile For Binary As #occfile
Put #occfile, , occArray
Close #occfile
Next

End Sub
********************************************************************

With the script above, you got a individual text file with the name of
the stock quote in your C:\Historical. You can now run the scipt below
to convert it in to excel readable format (csv).

********************************************************************
Sub Convert()

Set oFSO = CreateObject("Scripting.FileSystemObject")

DeleteLine = 1
sTemp = "Date,Open,High,Low,Close,Volume,Adj Close" & vbCrLf

For x = 1 To 300
fname = Worksheets("Portfolio").Cells(x, 1) & ".txt"
fname_path = "c:\historical\" & fname
If oFSO.FileExists(fname_path) Then
Set oFSTR = oFSO.OpenTextFile(fname_path)
lCtr = 1
Do While Not oFSTR.AtEndOfStream
sLine = oFSTR.ReadLine
If lCtr <> DeleteLine Then
sTemp = sTemp & sLine & vbCrLf
Else
bLineFound = True
End If
lCtr = lCtr + 1
Loop

oFSTR.Close
Set oFSTR = oFSO.CreateTextFile(fname_path, True)
oFSTR.Write sTemp
Set oFSTR = Nothing
End If
oFSO.MoveFile fname_path, "c:\historical\" &
Worksheets("Portfolio").Cells(x, 1) & ".csv"
Next

Set oFSO = Nothing
End Sub
********************************************************************

You can download the executable script above and others scripts on how
to get Stock Quotes from the web here --> http://excelstock.googlepages.com

cheers,