asked on

Download ObjXml and store data in Access table

I am trying to download Yahoo stock quotes from the web and store the result in an Access table. I can get he data in the browser but my code won't store the data in the table.

The code runs but the last thing it does is:

Debug.Print "Date = " & Split(objXML.responseText, ",")(2)

after which it exits the routine and it does not loop.

Also this downloads one record at a time as a csv in the browser. I know how to download mutiple records, is there a way to step through them in the browser to store them in an acess table?

What am I doing wrong?

Thanks,
merdeka

Public Sub GetYahooQuote()

 Dim objXML As Object
 Dim strSymbol As String
 Dim strURL As String
 Dim strWFormat As String
 Dim Db As Database
 Dim rstQuote As Recordset
 Dim rstSymbol As Recordset
 Dim strYahooTags As String
 Dim strSql As String
 Dim x As Integer
 
 Set Db = CurrentDb
 
 
 Set objXML = CreateObject("MSXML2.XMLHTTP")
 'strURL = "http://ca.finance.yahoo.com/d/quotes.csv?s="
 strURL = "http://download.finance.yahoo.com/d/quotes.csv?s="
 
 strYahooTags = "&f=sl1d1"
 
 'Get unique symbols
 strSql = "SELECT DISTINCT " & _
 "UCase([tbl_Transaction].[Symbol] & ""."" & [tbl_Market].[mktSuffix]) AS Symbol " & _
 "FROM tbl_Market INNER JOIN tbl_Transaction ON tbl_Market.MktID = tbl_Transaction.MktID;"

 'Debug.Print strSql
 
 Set rstSymbol = Db.OpenRecordset(strSql)
 
 If Not rstSymbol.BOF() And Not rstSymbol.EOF() Then
 rstSymbol.MoveFirst
 strSymbol = rstSymbol!Symbol
 Debug.Print strSymbol
 Else
 MsgBox "There are no stock symbols to process!", vbOKCancel + vbCritical, "StockTrak"
 End If
 
 
 strSql = ""
 strSql = "Select * from tbl_YahooQuote;"
 
  Set rstQuote = Db.OpenRecordset(strSql)
 
 objXML.Open "GET", strURL & strSymbol & strYahooTags, False
 objXML.send

 
 Do While Not rstSymbol.EOF()
 
 'Debug.Print strURL & strSymbol & strYahooTags
 
  Debug.Print "Symbol = " & Split(objXML.responseText, ",")(0)
 Debug.Print "Trade  = " & Split(objXML.responseText, ",")(1)
 Debug.Print "Date   = " & Split(objXML.responseText, ",")(2)



 With rstQuote
 .AddNew
    !s = Split(objXML.responseText, ",")(0)
    !l1 = Split(objXML.responseText, ",")(1)
    !d1 = Split(objXML.responseText, ",")(2)
.Update
 End With
 objXML.Close

 
 rstSymbol.MoveNext
 strSymbol = rstSymbol!Symbol
 Debug.Print strSymbol
 
 objXML.Open "GET", strURL & strSymbol & strYahooTags, False
 objXML.send

 Loop
 
 End Sub

Open in new window

darbid73

you get some information from your database. Can you please give an example of this information to test your code. Also to check what you are giving Yahoo.

darbid73

all you have to do is just give me an example of what you are entering in this line.

objXML.Open "GET", strURL & strSymbol & strYahooTags, False

Open in new window

merdeka

ASKER

darbid73,

Thanks for the reply. Here is the result of the above line as you requested which I copied from the Immediate Window in Access.

http://download.finance.yahoo.com/d/quotes.csv?s=APG.V&f=sl1d1

Here is the result of the Debug.Print statements in the code from the Immediate window also:

Symbol = "AGP.V"
Trade = 0.345
Date = "3/22/2011"

As you can see the data is captured but the Do While does not loop.

Thanks,
merdeka

merdeka

ASKER

darbid73,

strURL = "http://download.finance.yahoo.com/quotes.csv?s"
strSymbo l = "APG.V"
strYahooTags = "&f=sl1d1"

I hope this helps.

Thanks,
merdeka

ASKER CERTIFIED SOLUTION

darbid73

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

merdeka

ASKER

darbid73,

Answers to questions:
1. Yes I have done this, it seems like after the Debug.Print statement the sub-routine is exited. I put a number of break points in the code to isolate the line whee the code exits and it seems the code exits immediately after the Debug.Print statement.
2. Three records
3. I'm not sure what you mean by 'references'
4. Yes it does because earlier the Debug.Print was printing all three rcords. I must have changed something (added the sql code etc. I think) that has caused only one record to process.

I can't answer any questios about objXML because this is the first time I have tried to use it. All of the objXML code in the sub-routine I posted was copied of the web.

Thanks,
medeka

darbid73

Ok I think your internet connection is moving slower than your code.

You need to wait for the response from Yahoo.

something like this needs to go after the send

The status is just optional - I am not sure what the possibilities could be but 200 - 299 is good I think.

Do While objXML.ReadyState < 4
DoEvents
DoEvents

Loop

If objXML.Status <> 200 Then
MsgBox "I think this means you have a problem with the service"
End If

merdeka

ASKER

darbid73,

I found the problem.

It seems that the objXML.Cose command my not be a valid command so I removed it and voila the data appeared in the database. I think you suggested this in one of your comments but going back over them I can't find the comment.

There doesn't seem to be any help in the Microsoft Access (almost useless) help for objXML.

Anyway I will award you the ponts for this.

Thanks for the advice,
merdeka

merdeka

ASKER

I found yor objXML.Close comment (I don't know why I couldn't find it prior to adding my last comment).

Thanks again,
merdeka