Solved

Download ObjXml and store data in Access table

Posted on 2011-03-22
9
457 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:merdeka
  • 5
  • 4
9 Comments
 
LVL 19

Expert Comment

by:darbid73
ID: 35196180
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.
0
 
LVL 19

Expert Comment

by:darbid73
ID: 35196190
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

0
 

Author Comment

by:merdeka
ID: 35199767
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


0
 

Author Comment

by:merdeka
ID: 35204267
darbid73,

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

I hope this helps.

Thanks,
merdeka
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 19

Accepted Solution

by:
darbid73 earned 500 total points
ID: 35204770
here are some observations if it is only looping once.

1. Have you put a break in your code a stepped through it?
2. how many records does your "rstSymbol" return? if only 1 then it will only loop once.
3. what reference are you using for MSXML2.XMLHTTP?  I am not sure if "objXML.Close" is how we should be doing this multiple times
4.  Does Yahoo allow you to do this call multiple time so quickly?

0
 

Author Comment

by:merdeka
ID: 35207377
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  
0
 
LVL 19

Expert Comment

by:darbid73
ID: 35207696
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
0
 

Author Comment

by:merdeka
ID: 35208163
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
0
 

Author Closing Comment

by:merdeka
ID: 35208199
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
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now