hansine-ga:
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,
here:
---------------------
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:
http://aht_ga.tripod.com/hansine.htm
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
itself.
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!
Regards,
aht-ga
Google Answers Researcher |
Clarification of Answer by
aht-ga
on
09 Jul 2004 09:30 PDT
hansine-ga:
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!
Regards,
aht-ga
Google Answers Researcher
|
,
1 Comment
)