Download a .csv file from a website

[talkintotim]

I was wondering if anyone knew how to code this with vba. I have a list of
stocks in a spreadsheet, and want to download the .csv file from Yahoo
historical quotes for a stock. The code would have to navigate to
http://finance.yahoo.com/q/hp?s=A, navigate to the "Download to Spreadsheet"
button, simulate a right click and save as, and save the .csv file as the
nameofthestock.csv, in this case A.csv, and save it to the directory where I
want it. Is this possible? I've been searching online and in vba reference
books for a few days, and can't find any code like this.

The reason I'm not attaching any code is because I can't even find a
starting point to do this. Any help would be great.

 

[John Bundy]

There are several ways of going about this, but i prefer simple. If you right
click the download button and click properties, you will see that you now
have a url to the csv download which is handy (and you can still plug in the
stock name you want).

Now for the download, XMLHTTP is a cool object that lets you work with teh
web, the following code was borrowed and changed from the following:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=799

I went ahead and did the whole thing because it was kind of fun, you can
play with it to learn from since you seemed to be looking to do it on your
own. You just need a button to run this one.

Private Sub CommandButton1_Click()
Dim sfileName As String
Dim sStockName As String
Dim sUrl As String
sStock = "MSFT"
sUrl = "http://ichart.finance.yahoo.com/table.csv?s=" & sStock &
"&d=11&e=30&f=2009&g=d&a=10&b=18&c=1999&ignore=.csv"
sfileName = "C:\Documents and Settings\john.bundy\Desktop\" & sStock & ".csv"
SaveWebFile sUrl, sfileName
End Sub
Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String)
As Boolean
Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte

'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as
MSXML2.XMLHTTP
Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
oXMLHTTP.Send 'send request

'Wait for request to finish
Do While oXMLHTTP.ReadyState <> 4
DoEvents
Loop

oResp = oXMLHTTP.responseBody 'Returns the results as a byte array

'Create local file and save results to it
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF

'Clear memory
Set oXMLHTTP = Nothing
End Function

 

[talkintotim]

Thanks, this is great. My one question is since the url for the csv is
programmed by the website for a specific date range, is there anyway to have
the date automatically update? So next week if I wanted to look at the
historical data of the stock, I could just click the button without
reprogramming in the url for the csv file.

Thanks again for this info!