Google Answers Logo
View Question
Q: Automatically Importing Stock info from the web into MS Access ( Answered 5 out of 5 stars,   1 Comment )
Subject: Automatically Importing Stock info from the web into MS Access
Category: Computers > Programming
Asked by: hankthetank-ga
List Price: $20.00
Posted: 24 Apr 2004 00:54 PDT
Expires: 24 May 2004 00:54 PDT
Question ID: 335335
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
From this table, I want to create a query on yahoo that generates a
spreadsheet or text file seperated by commas.

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

I can do this manually now by:
1. URL
2. Enter stock tickers individually from TABLE 1 separated by %2c+

For example: 

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

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:<tickers seperated by

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?


Google Answers Researcher

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

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

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)?


Google Answers Researcher

Clarification of Question by hankthetank-ga on 25 Apr 2004 14:34 PDT
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

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,

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?

Google Answers Researcher

Clarification of Question by hankthetank-ga on 27 Apr 2004 19:43 PDT
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.


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.


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.
Subject: Re: Automatically Importing Stock info from the web into MS Access
Answered By: aht-ga on 29 Apr 2004 18:30 PDT
Rated:5 out of 5 stars

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:

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.


Google Answers Researcher
hankthetank-ga rated this answer:5 out of 5 stars and gave an additional tip of: $10.00
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)!

Subject: Re: Automatically Importing Stock info from the web into MS Access
From: aht-ga on 29 Apr 2004 22:45 PDT
I'm glad you like it, and thanks for the tip!


Google Answers Researcher

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy