Visual Basic for Financial Professionals
   Home      API Example3
This example uses the UrlDownloadToFile Windows API function to retrieve historical stock data (High, Low, Open, Close, Volume) from Yahoo!Finance. To use the macro as it is you would need to insert a name range "Symbol" where to input the Ticker for which historical data is to be obtained.

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
Sub DownloadData()
Dim URL As String, LocalFile As String, Symbol As String, lngRetVal As Long, StartDate As Date, EndDate As Date
    On Error Goto ErrHdl
    Symbol = Range("Symbol")
    StartDate = Year(DateAdd("y", -23, Date))
'Going 23 years back
    EndDate = Year(Date)
    URL = "http://ichart.finance.yahoo.com/table.csv?s=" &
Symbol & "&a=2&b=13&c=" & _
                 StartDate & "&d=4&e=24&f=" & EndDate & "&g=d&ignore=.csv"
    LocalFile = ThisWorkbook.Path & "" & Symbol & Format(
Now, "mmddyyyyhhmmss") & ".csv"
    lngRetVal = URLDownloadToFile(0, URL, LocalFile, 0, 0)
If lngRetVal <> 0 Or Len(Dir(LocalFile)) = 0 Then
        MsgBox Symbol & " Historical Data Download Failed ...", vbCritical, "Download Data"
        Application.ScreenUpdating = False
        With Range(Range("Symbol").Offset(2, 0), Range("Symbol").Offset(Rows.Count - Range("Symbol").Row, 6))
            .MergeCells = False
            Workbooks.Open LocalFile, 0,
            Range(Range("A1"), Range("G1").End(xlDown)).Copy
            .Range("A1").PasteSpecial xlPasteValues
End With
        Application.CutCopyMode =
        Workbooks(Dir(LocalFile)).Close False
        Kill LocalFile
End If
If Not Application.ScreenUpdating Then Application.ScreenUpdating = True
    If Err.Number Then MsgBox Symbol & " Historical Data Download Failed: " & Err.Description, vbCritical, "Download Data"
End Sub