Accessing Real-Time Stock Quotes in an MS Access Database

Tom_wbi
Tom_wbi used Ask the Experts™
on
I am trying to somehow get real-time (or slightly delayed) stock quotes into an MS Access Database.  Working in the financial industry, we often make our own tools for different types of stock analysis. These tools are usually in Excel and therefore we just use the MSN Stock Quote Add-In for Excel. This is sufficient for our purposes.

Since I am building an analysis tool in MS Access now, I am trying to resolve the best method for getting some real-time (or slightly delayed) stock quotes into an MS Access database.  I have some ideas, such as using Excel with MSN Stock Quotes and then importing this data into an Access table (kind of cumbersome and may need to happen multiple times throughout the day - plus, I don't really want to involve another tool like Excel at all if I can help it)

Does anyone have any knowledge of an MSN Stock Quote type Add-In for MS Access or any other suggestions for how I might accomplish this task?  I always appreciate all of the help and excellent suggestions from the folks on this site and I'm hoping someone else may have tackled this same problem, previously.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Anyone??

In the meantime, I have decided to try to use an Excel Spreadsheet and Link it into my Access project as a 'Linked table'..has anyone done this?
Commented:
I manage real-time datafeeds using MATLAB's Datafeed Toolbox.  It is very easy but costly and a new language... The have made their own functions to connect to Bloomberg, Reuters, Haver, FactSet, FRED, Yahoo, and a few others.  I hate to post something directly against what you asked for, but the boards seem to be stumped.
http://www.mathworks.com/products/datafeed/ 

Author

Commented:
Hi All, thhanks for thos attempting to help - I still may use/investigate the suggestions above.

In the meantime, however I *believe* I have made some progress. I'm just stuck on something that someone who is very proficient at VBA/Excell/Access may be able to help me with.

This is what I have so far:

Excel Spreadsheet with two columns (the stock symbol, and price from MSN stock quote):
goog   436.94
aapl    246.22
msft    23.23

you get the idea.

The above Excel Spreadsheet, let's call it Quotes.xls is a linked table in my Access Project. When this Excel spreadsheet quotes gets updated, these values are reflected in my Access table. So fa, so good.

Column A in the Quotes.xls Spreadsheet (i.e. Linked table) links in a different spreadsheet called 'Symbols.xls' in Column A, which I produce from my MS Access Project.  This Symbols.xls changes as my code loops through what it needs to do.

My issue at this point is that - even though Symbols.xls changes (correctly, as it should) WITHOUT actually Opening Quotes.xls and clicking the 'Enable Button;' after being told that this workbook contains links to other data sources, then clicking OK to the message 'Workbook has links which cannot be updated' - this second message comes from the fact that my Symbols.xls range changes as there are more or less symbols to link in...

So, is there a way from my MS Access /VBA code to get my Quotes.xls spreadsheet to update so that it recognizes the changes to Symbols.xls?

Please help!!

Author

Commented:
One of my issues was that when I attempted an Excel Transfer Spreadsheet that didn't work, it left an Excel Process running in my background, preventing MSN stock quotes from updating.

I fixed my issue and - while I haven't actually achieved what I originally wanted - I can 'sort of' get quotes , update them, and get them into a linked table. I haven't had many suggestions, either, so I will close out this question. Thank you to everyone who chimed in.

Not sure how to close out this question. If anybody can tell me how please do.
If CurrentDb.TableDefs("tablename").Name = "tablename" Then
            DoCmd.DeleteObject acTable, "tablename"
                        End If
            DoCmd.TransferSpreadsheet acLink, , "tablename", excelfilepathhere, True
end if
use the above code to transfer your spreadsheet

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial