Web Query for Fama French Factors (zip file)

[ttbuson]

Is there a way to access a zip file's contents through VBA? I'm hoping to avoid manually downloading the Fama French factors each month, and instead set up a web query or VBA routine to pull them into Excel.

The data is on this web page:
Kenneth R. French - Data Library

And this is the location of the specific zip file:
http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors.zip

I've seen this done in R, but am wondering if it's possible in Excel.

 

[John_w]

To download the .zip file call an XMLhttp GET request. For unzipping it, see Unzip file(s) with the default Windows zip program (VBA). Then you could import the text file into a worksheet by running the Data Import or Text Import wizard in Excel - record a Macro to generate the VBA code as a starting point.

 

[ttbuson]

Thanks for the suggestion. That was very helpful advice. This is the routine I ended up writing. I pulled code from several different sources.

Option Explicit
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
Sub Fama_French_Factors()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'This will save the Fama French factors zip file to your hard drive.
SaveWebFile "[URL]http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors.zip[/URL]", "C:\F-F_Research_Data_Factors.zip"
'Declarations for unzipping procedure.
Dim FSO As Object
Dim oApp As Object
Dim Fname As Variant
Dim FileNameFolder As Variant
Dim DefPath As String
Fname = "C:\F-F_Research_Data_Factors.zip"

If Fname = False Then
'Do nothing
Else
'Root folder for the new folder.
DefPath = "C:\"
End If
'Create the folder name for the unzipped files.
FileNameFolder = DefPath & "MyUnzipFolder" & "\"
'Make the normal folder in DefPath
MkDir FileNameFolder
'Extract the files into the newly created folder
Set oApp = CreateObject("Shell.Application")
oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items
On Error Resume Next
Set FSO = CreateObject("scripting.filesystemobject")
FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
On Error GoTo 0
'Export the text file data into the workbook.
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet
Set wbI = ThisWorkbook
Set wsI = wbI.Sheets("Sheet1") '<~~ Sheet where you want to import
Set wbO = Workbooks.Open("C:\MyUnzipFolder\F-F_Research_Data_Factors.txt")
wbO.Sheets(1).Cells.Copy wsI.Cells
wbO.Close SaveChanges:=False
'Deletes the saved files and folders.
On Error Resume Next
'Delete all files in the folder
Kill "C:\MyUnzipFolder\*.*"
'Delete folder
RmDir "C:\MyUnzipFolder\"
On Error GoTo 0
Kill "C:\F-F_Research_Data_Factors.zip"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub