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.

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



-- 
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what 
is helpful. 


"talkintotim" wrote:

> 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.

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!

"John Bundy" wrote:

> 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
> 
> 
> 
> -- 
> -John http://www.jmbundy.blogspot.com/
> Please rate when your question is answered to help us and others know what 
> is helpful. 
> 
> 
> "talkintotim" wrote:
> 
> > 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.