I would like to have MS Access 2002 automatically pull up to date
stock prices from the web based on a table of stock tickers in Access.
I think that this can easily be done with yahoo or a similar website.

Below is what appears to be the process
Step One:
Create web query on yahoo through Access and from an Access table of
stock tickers. For example:
Access TABLE 1
1 GTW
2 DELL
3 MSFT
From this table, I want to create a query on yahoo that generates a
spreadsheet or text file seperated by commas.

STEP Two:
Create a table (TABLE 2) in Access from the Step One data. I would
like for the data to be active and refreshable. For example, if I add
a new ticker to my MS Access TABLE 1, I would like the corresponding
data to be added to TABLE 2 if I click refresh or if I re-open the
database.

I can do this manually now by:
1. URL http://finance.yahoo.com/q/cq?d=v1&s=
2. Enter stock tickers individually from TABLE 1 separated by %2c+

For example:  http://finance.yahoo.com/q/cq?s=GTW%2c+DELL%2c+MSFT 

3. download the spreadsheet of these tickers ("download to
spreadsheet") and save the spreadsheet to my computer.
4. import the spreadsheet into access.

When doing the above manual process, somewhat obviously, if I want to
add a Ticker?s data, I must re run pretty much the whole process?

Please only answer if this can be done. If on a glance this or
something very similar cannot be done please dont answer.

Thanks for the info!

---

Request for Question Clarification by aht-ga on 24 Apr 2004 17:14 PDT

hankthetank-ga:

I've done this in the past within Excel using Visual Basic for
Applications; the easiest way is to target the CSV file directly
instead of asking Yahoo! to generate the web-view before-hand. If you
look at the actual hyperlink for the 'Download Spreadsheet' link,
you'll see that the formatting of the request is similar to the
formatting for the web-view itself:

http://finance.yahoo.com/d/quotes.csv?s=<tickers seperated by
commas>&f=sl1d1t1c1ohgv&e=.csv

This HTTP request will deliver you a CSV file with all of the
specified information (the sl1d1t1... stuff). It's easiest to do this
from within Excel, but since Access is capable of importing a CSV file
directly (at least, Access 2002 is capable of this), you can, from
within Access, ask Internet Explorer to download the CSV file (which
Yahoo! Finance will generate based on your requested tickers in the
URL), then import it directly without using Excel.

Before I can give you any further estimate of how much effort this
would involve, perhaps you can clarify for me how you wish to use the
information... is the data being collected over time, so that you have
a local archive of the stock prices for every trading day that you add
to on a daily basis? How does the data need to be stored? Any other
details you can share (in this publicly accessible forum) for how you
wish to use the data?

Regards,

aht-ga
Google Answers Researcher

---

Request for Question Clarification by aht-ga on 24 Apr 2004 18:01 PDT

hankthetank-ga:

My original clarification request was truncated accidentally, due to
the inclusion of some characters not supported by this interface. My
apologies.

The gist of my RFC, was to inform you that it is possible, using VBA,
to drive this completely from within Access (not as easily as it could
be done from Excel, but still possible). Prior to being able to
provide you with an estimate of the amount of effort required, though,
can you please clarify a few points for me?

1. What exactly are you doing with the data after you have imported
it? ie. are you then moving it to another table, etc.

2. How often do you intend to run this... once a day?

3. This can be done for Yahoo! Finance with minimal problems, are you
willing to accept a solution that works for Y! Finance only (since
every website uses different approaches to generating a downloadable
list, if such an option is even available)?

Thanks,

aht-ga
Google Answers Researcher

---

Clarification of Question by hankthetank-ga on 25 Apr 2004 14:34 PDT

aht-ga, 
Thanks for responding swiftly, my apologies for the delayed response.

Once I have the data in Access, I want to display it in an access
subform. I don?t plan to manipulate it or display it in any other
tables. I think its a matter now of creating a script that creates a
linked table in access that pulls from a query run on yahoo rather. 
Once I get the data into access, I think I?ll be able to work with it.

It would be fine if the data refreshed when I launch access or once
per day. It would be great if I could refresh by push of a button in
access. (I think I?ll be able to set this part up once I get an idea
of the scrip and process.)

I am definitely willing to accept script that will work only with yahoo.

I posted an idea of what I?m going to do
https://home.comcast.net/~accesstester/dev.zip

This sample uses a manually downloaded csv file from yahoo.  In this
example, I had to review the table in access to see which
stocks/tickers I was looking for. Then I manually entered the data in
a way that yahoo could understand. Then downloaded the file. Then
re-launched access. To update the data or add or remove a stock, I
have to do the same process over again.

Thanks for the assistance.

---

Request for Question Clarification by aht-ga on 27 Apr 2004 18:00 PDT

Hi hankthetank-ga:

This time it's my turn to apologize for the delay. I did not have the
opportunity until today to take a closer look at this.

It looks like the simplest approach would be to automate exactly what
you have previously been doing manually; that is, downloading the data
and importing it into a table in MS Access.

The complication I've run into (with MS Access 2002) is that it does
not properly support opening a web-based dynamically generated file in
the way that Excel does. What I mean is that with Excel, I can ask it
to 'open' the URL that actually causes Y! Finance to generate the CSV
file, and it will actually go ahead, send the URL to Y! Finance, and
open up the returned output as an imported CSV file. Quite elegantly,
too.

Access, on the other hand, cannot handle the URL properly it seems.

It might be possible to cook up a work-around with Access opening up
Excel to grab and save the data, then Access imports the saved data.
It won't be as elegant, though... however, it might just work.

Can you wait a little bit longer while I look into this a bit more?

aht-ga
Google Answers Researcher

---

Clarification of Question by hankthetank-ga on 27 Apr 2004 19:43 PDT

aht-ga,
I can wait, especially if you can work out a solution to run it
directly from access. Thats interesting about access 2002, it doesnt
suprise me. I think that was what I was running into when I attempted
to run it from access. I didnt run into similar problems fidling with
excel. While I absolutely would prefer a function that draws directly
from the web through access, the proposed work around solution could
work for me as well.

THANK YOU FOR THE CONTINUED ASSISTANCE.

---

Request for Question Clarification by aht-ga on 29 Apr 2004 00:53 PDT

Well, it took a lot more time than I intended, but I think I've got a
solution working completely within Access. Please allow me another day
or two to clean it up and document it (my schedule is rather full the
next couple of days, so that's why it will take a day or two), and
then I can post it online for you to download.

Regards,

aht-ga
Google Answers Researcher

---

Clarification of Question by hankthetank-ga on 29 Apr 2004 10:44 PDT

Sweet. I'm glad that there is a solution. Two days will be fine. 

Again I sincerely appreciate the assistance. I will consider your hard
work when everything is finalized.

hankthetank-ga:

I have completed my investigation of your requested problem, and have
a working solution for you. Before I provide the link to the finished
file, though, I must first provide you with some instructions on how
to make the software work.

The solution, implemented within Access as a Visual Basic for
Applications module, requires that you have Visual Basic for
Applications available to you in Access, and it requires that you
slightly alter from the default configuration the external libraries
that MS Access VBA references. This last change is actually 'standard'
for most Access VBA modules that need to manipulate external data.

To confirm that you have VBA installed, simply open up the MDB file
from the link that I will provide below, then in the Objects list, go
to Modules and highlight the only module present. If 'Design' in the
toolbar is clickable, then you have VBA installed. If not, you will
need to install it from your original Office or Access installation
CD; by default, basic VBA support is installed so you should be OK.

After clicking on 'Design', you will be in the VBA Editor window.
Click on the Tools menu, and select 'References...'. At the top of the
resulting dialog box, you will see all of the currently-selected
external libraries. Look at the checked items carefully, and uncheck
any that mention "ActiveX Data Objects", "ADO", and "DAO". Then,
scroll down to "Microsoft DAO 3.6 Object Library" and check it to
include it in the list of selected external references, and click OK.

At this point, you can close the Microsoft Visual Basic window (saving
any changes if prompted by the system) and return to MS Access.

Now, you can open up the TickerRef form, and you will see that I have
added a button called Update Info. This button calls the 'RunCSV'
macro, which in turn runs the necessary function in the module. Note
that I have not edited the TickerRef form beyond adding this button.

Please do not alter the layout of the 'Quotes' table; I have already
included more fields in the download than you are currently using. If
you wish to add fields that are not already in the download, it will
be necessary to tweak the Quotes table layout first, then alter the
format list in the module driving the CSV download.

The file can be downloaded from this location:

http://aht_ga.tripod.com/hankthetank.htm

Please let me know if you run into any problems with this, I can help
you through them.

I hope that you like the program! It was a fun challenge.

Regards,

aht-ga
Google Answers Researcher

Fantastic. Congrats on a challenge well beat! AND quickly. Whooh it
was a complicated one. I sincerely appreciate your hard work. I hope
you enjoyed the task as well. You the man (or woman as it may be)!

I'm glad you like it, and thanks for the tip!

Regards,

aht-ga
Google Answers Researcher