Google Answers Logo
View Question
Q: Automatically Importing Historically Stock info from Yahoo into MS Access ( Answered 5 out of 5 stars,   1 Comment )
Subject: Automatically Importing Historically Stock info from Yahoo into MS Access
Category: Computers > Programming
Asked by: hansine-ga
List Price: $30.00
Posted: 06 Jul 2004 14:39 PDT
Expires: 05 Aug 2004 14:39 PDT
Question ID: 370490
I found this great solution for importing current stock prices on Google Answers
Automatically Importing Stock info from the web into MS Access).

I need just the same technical solution, just for historical prices.
The difference is just:
- Historical prices for the last trading day (normally yesterday, but
last friday if today's date is saturday, sunday or monday) should be
loaded by default
- The user should be able to choose a date manually (in case the
markeds were closed yesterday due to a holiday)
- The quotes table should contain the following fields

The historical prices are also available for free on yahoo and can be
downloaded for free: see
(on the web) or
for the csv file

Request for Question Clarification by aht-ga on 07 Jul 2004 10:52 PDT

I'm glad you found my Answer for hankthetank-ga useful!

I'll take a look at the historic data sources on Yahoo! Finance that
you've provided above, to see if the same technique will work for you.
I'll get back to you soon.


Google Answers Researcher

Request for Question Clarification by aht-ga on 07 Jul 2004 11:06 PDT

Here's the challenge. Historical data is only available from Y!
Finance for a single ticker in one query. So, it would be necessary to
run a query for each symbol on your list of ticker symbols, to obtain
the data you seek, even when we are only looking for the historical
data for a single date. While this is doable, it will create an extra
workload on your PC versus the single-query approach that is possible
for current data.

Can you clarify for me the following, please:

- Number of tickers you would want to use this for (choose one of:
<10, 11-100, >100)
- Amount of historical data that would be stored in the Quotes table
for each ticker (just the data for the one selected date, or what...)


Google Answers Researcher

Clarification of Question by hansine-ga on 07 Jul 2004 12:17 PDT
Hello Aht-ga,

Particulaily glad that you wanted to respond to this challenge.
And yes, it is true - the data will have to be collected ticker by ticker.

To your questions:
1) number of tickers is 22, but it would be great if it was expandable
a bit (but no more than 10-15) (11-100 according to your categories)
2) amount of data in the Quotes table: I only require data from the
one day - the plan is to copy the data into another Access application
that will do the calculations I need ...

Hope this was helpful



Request for Question Clarification by aht-ga on 07 Jul 2004 13:39 PDT
What sort of timeline are you on for this? I may not be able to get to
the programming side of things until next week (I normally do this
sort of project on the weekend, but summer weekends = weddings). If
you can give me an idea of when you need this by, I can advise


Google Answers Researcher

Clarification of Question by hansine-ga on 07 Jul 2004 13:51 PDT
By next week, say wedneysday, will do fine


Subject: Re: Automatically Importing Historically Stock info from Yahoo into MS Access
Answered By: aht-ga on 08 Jul 2004 00:11 PDT
Rated:5 out of 5 stars

I managed to find the time today to tackle this problem... naturally,
it took longer than I anticipated it would, but once I was into it, I
couldn't stop. :)

I have rewritten the software to work for historical quotes. In order
for the software to work, you will need to ensure that your MS Access
is configured properly. Rather than type it all out again, I'm cutting
and pasting the configuration instructions from the previous Answer,


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 download your customized Access project here:

After you unzip the contents and open up the database in Access, just
hit the Start button that you should see in the "Start" form to launch
the TickerRef form... or just double-click on the TickerRef form

You will see that I have added a validated date selection to allow you
to select an appropriate date. The error-checking on the date is
rudimentary; it will eliminate 'impossible dates', and when you click
on the 'Update Info' button it will check for a weekend (and correct
to the Friday before), but it will not check for a holiday since
that's a much more complex puzzle. If the date results in Y! Finance
rejecting the request, then the Quotes table will simply be empty,
that's all.

Note that when you hit the 'Update Info' button, the caption will
change to 'Updating...' while the PC grinds away. Just be patient, it
will eventually revert back to 'Update Info' when it is done. Then,
just click on any of the ticker symbols to view the retrieved data.

To change the tickers, go to the Tickers table and edit away.

All of the retrieved data can be found in the Quotes table, you are
free to use that data as you see fit. I strongly recommend that you
simply add to this database; the various pieces of code are closely
tied to the design of the tables and forms.

Please give this a try, and let me know if it meets your needs!


Google Answers Researcher

Request for Answer Clarification by hansine-ga on 08 Jul 2004 04:34 PDT

Great you found time to work on the application. 
I like the way I can choose the date and yesterday is the default date.

But there is a bug. First time I tried it out it worked just great,
filled the quotes table with the data I need. But the second time one
of the tickers had wrong data registered - the data was a replicate of
the previous record's data (for instance IBMs data was copied to SBC).
The third time it happened for one more row and now all the data in
the quotes table is alike. It doesn't matter whether I close Access
and start again, or if delete all records in the quotes tables or try
with an different date: all records are filled with the same data (but
not nescesarily the same data that was present before updating).

Hopefully a counter or variabel somewhere that just need to be reset ...

Perhaps it has something to do with the TickerRef query ?


Clarification of Answer by aht-ga on 08 Jul 2004 08:23 PDT

Sigh, that's the problem with writing something like this at night...
there will always be a wrench thrown into the works in the morning! In
this case, the wrench is that the Y! Finance site gets busy during the
day, and sometimes refuses to return the results. So, I've been able
to replicate your experience. I will not be able to work on this
immediately, but rest assured that the problem can be fixed. I'll be
in touch soon.

Request for Answer Clarification by hansine-ga on 09 Jul 2004 00:58 PDT

Thanks. I realise this was more than the quick work-around to the
previous solution with current stock prices, that I anticipated - I'll
throw in a tip of 20$ when it works out.


Clarification of Answer by aht-ga on 09 Jul 2004 09:30 PDT

OK, I think it's fixed now. The problem is that sometimes, when you
make a request of the Y! Finance server to deliver the CSV file for a
historical quote, it comes back with a "404 File Not Found" error,
possibly because the server is busy and has not had the time to create
the CSV file in response to your request. As a fix, I have added the
ability to detect this error, and to retry up to 10 times at 1 second
intervals before moving on to the next ticker. As well, if the program
fails to get a quote for a ticker, it will not include the ticker in
the Quotes table so that there is no possibility of corrupt results.
At the end of an Update Info run, if any tickers failed you will get a
pop-up message letting you know how many failed, and which ones they
are. You can then verify that the ticker is correct and that there is
no other cause (such as a holiday for the exchange that the ticker
trades on) before running Update Info again.

Please try this out by downloading the ZIP file again (using the link
above), and let me know how it goes for you!


Google Answers Researcher
hansine-ga rated this answer:5 out of 5 stars and gave an additional tip of: $20.00
Great job, aht-ga. I've got exactly what I wanted and ahead of the
time frame we agreed upon.

Subject: Re: Automatically Importing Historically Stock info from Yahoo into MS Access
From: aht-ga on 10 Jul 2004 11:03 PDT

I'm glad it works for you, and thank you 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