Problem with internet data download (1 Viewer)

[PC User]

I cannot get any data to download from this code. There are no errors and the best that I can tell the syntex is correct. So, why doesn't it work. Can anyone see why it doesn't download data?

' Retrieve the file from the specified URL
Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
XMLHTTP.Open "GET", DownloadURL, False
XMLHTTP.Send
byteData = XMLHTTP.responseBody
Set XMLHTTP = Nothing

 

[ByteMyzer]

How can you tell that it doesn't work if there are no errors?

What is the value in DownloadURL?

Do you have the statement Dim byteData() As Byte in your declarations?

What is the size of byteData after you execute the statement byteData = XMLHTTP.responseBody? (hint: ?UBound(byteData) )

 

[PC User]

If seeing the code for the entire function helps, here it is. Using the start and ending dates for requested data, the function is to loop through a table of ticker symbols and download all historical data for each symbol within that date range.

Public Function HistoricalQuotes(StartDate As String, EndDate As String)
            On Error GoTo Whoops
    'Parameters for date range
    Dim StartMonth, StartDay, StartYear As String
    Dim EndMonth, EndDay, EndYear As String
    'Parameters for recordset
    Dim db As DAO.Database, tblDef As DAO.TableDef
    Dim qdf As DAO.QueryDef, prm As DAO.Parameter
    Dim strSQL As String, rst As DAO.Recordset
    'Parameters for web data
    Dim XMLHTTP As Object, byteData() As Byte
    'Other parameters
    Dim DownloadURL As String
    Dim strTargetPath As String, strFileName As String, strFilePath As String
    Dim strTable As String, strSymbol As String
    Dim FileNumber As Integer
 
    StartMonth = Format(Month(StartDate) - 1, "00")
    StartDay = Format(Day(StartDate), "00")
    StartYear = Format(Year(StartDate), "00")
 
    EndMonth = Format(Month(EndDate) - 1, "00")
    EndDay = Format(Day(EndDate), "00")
    EndYear = Format(Year(EndDate), "00")
'  ******* Start Symbol Loop
   Set db = CurrentDb()
   strSQL = "SELECT tblSymbols.Symbol FROM tblSymbols;"
   Set rst = db.OpenRecordset(strSQL)
            rst.MoveFirst
            Do Until rst.EOF
 
            strSymbol = rst![Symbol]
            'Debug.Print strSymbol
    DownloadURL = "[URL]http://ichart.finance.yahoo.com/table.csv[/URL]?" & _
                    "s=" & strSymbol & _
                    "&a=" & StartMonth & _
                    "&b=" & StartDay & _
                    "&c=" & StartYear & _
                    "&d=" & EndMonth & _
                    "&e=" & EndDay & _
                    "&f=" & EndYear & _
                    "&g=d&ignore=.csv"
    ' Retrieve the file from the specified URL
    Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    XMLHTTP.Open "GET", DownloadURL, False
    XMLHTTP.Send
    byteData = XMLHTTP.responseBody
    Set XMLHTTP = Nothing
 
            'Write data to Symbol CSV file
            strTargetPath = CurrentDir()
            strFileName = strSymbol
            strFilePath = strTargetPath & strFileName
 
    FileNumber = FreeFile    ' Get unused file number.  
    Open strFilePath & ".csv" For Binary Access Write As #FileNumber
    Put #FileNumber, , byteData    ' Output text.
    Close #FileNumber    ' Close file.
            'Move to the next record
            rst.MoveNext
            Loop
            'Close the target recordset
            rst.Close
            'Clear the instances of the recordsets
            Set rst = Nothing
            db.Close
            Set db = Nothing
'  ******* End Symbol Loop
 
MsgBox "Download Completed." & vbCrLf & "Open " & strTargetPath & " to view files ?", vbYesNo
 
OffRamp:
   Exit Function
Whoops:
   MsgBox "Error #" & Err & ": " & Err.Description
   Resume OffRamp
End Function

Ultimately I would like to export this data into three different formats with csv to start with. See attached file for sample outputs.

 

[ByteMyzer]

You still haven't explained how you can tell the code doesn't work if there are no errors. Please tell us, step by step, what happens when you run the code.

 

[PC User]

There were no errors to give me any indication of what was wrong; however, after tinkering with the code - checking the looping, the path and file format, etc. - I did get it to work. Why it didn't work before is a mystery. I wouldn't have suspected that the small changes that I made would get it to work, but they did.

 

[ByteMyzer]

I'm glad to hear you got it to work. Would you care to share the changes you made to the code that got it to work, for the benefit of the other Users here?