VBA – Download Historical Stock Prices from Yahoo Finance

This code shows how to use VBA to download a CSV file containing the historical stock information for a particular stock directly from Yahoo Finance and how to open the file in Microsoft Excel.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
'Technical download function
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
 
 'Download CSV containing historical prices from Yahoo Finance
 'and open with Microsoft Excel
 'VBA code by Joshua Radcliffe, www.joshuaradcliffe.com
Sub download()
 
    Dim download
    Dim stocklink As String
    Dim savefile As String
    Dim stock As String
 
    stock = "MCP"
    'stocklink = url to csv file containing stock price from yahoo finance historical prices
    stocklink = "http://ichart.finance.yahoo.com/table.csv?s=" & stock & "&a=6&b=29&c=2010&d=9&e=27&f=2011&g=d&ignore=.csv"
    'savefile = location/name of where to save file
    savefile = "C:\Users\Admin\Desktop\" & stock & ".csv"
    download = URLDownloadToFile(0, stocklink, savefile, 0, 0)
 
     'Test to check if file was downloaded
    If download = 0 Then
        MsgBox "File has been downloaded!"
        Workbooks.Open savefile
    Else
        MsgBox "File not downloaded!"
    End If
 
End Sub
'Technical download function
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

 'Download CSV containing historical prices from Yahoo Finance
 'and open with Microsoft Excel
 'VBA code by Joshua Radcliffe, www.joshuaradcliffe.com
Sub download()

    Dim download
    Dim stocklink As String
    Dim savefile As String
    Dim stock As String

    stock = "MCP"
    'stocklink = url to csv file containing stock price from yahoo finance historical prices
    stocklink = "http://ichart.finance.yahoo.com/table.csv?s=" & stock & "&a=6&b=29&c=2010&d=9&e=27&f=2011&g=d&ignore=.csv"
    'savefile = location/name of where to save file
    savefile = "C:\Users\Admin\Desktop\" & stock & ".csv"
    download = URLDownloadToFile(0, stocklink, savefile, 0, 0)

     'Test to check if file was downloaded
    If download = 0 Then
        MsgBox "File has been downloaded!"
        Workbooks.Open savefile
    Else
        MsgBox "File not downloaded!"
    End If

End Sub

  • Place the URLDownloadToFile function above all sub procedures in the module.
  • The stock variable is set to the ticker symbol you want to download.
  • The stocklink variable recreates the URL used to retrieve the historical data CSV file from Yahoo Finance.  The stock specified is inserted into the URL.  The a=6&b=29&c=2010… pieces of the URL represent the start and end dates of the data you are retrieving and can also be changed.
  • As you need to save the CSV file before it can be opened, you have to specify where on your computer and with what file name you want to save it. The code points to my desktop but you may need to change the location for it to work properly.  The file is currently saved to my desktop as MCP.csv.
  • The function URLDownloadToFile is called using all of the above parameters.  The code checks to make sure the CSV has been downloaded and if so, opens it in Excel.

Video of code in action:

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code lang=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" extra="">