Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Stock Portfolio < Dashboards | Loan Payments >
Back to Stock Portfolio    Comments List
Stock Price Directly in Access Upload Images   Link   Email  
Kerry Helmer       
14 months ago
Is there a way to pull stock quotes directly into Access without making use of Excel?
Richard Rost             
14 months ago
Not that I know of. I use Excel automation (basically open a copy of Excel in the background using VBA, update the cells, save the sheet, import into Access). However this is time consuming (takes 3 to 5 seconds) and doesn't reliably work 100% of the time. I'm sure there is probably an API out there you can pay for, but I haven't found a good free one. If you find one, let me know.
Kevin Yip        
14 months ago
If the stock quotes are on a webpage, you can do something called "web-scraping", which is inspecting HTML tags and values of the webpage and gleaning (or scraping) the info from them.  The complexity of VBA depends on how complex the tags are.  For instance, a webpage of a movie's detail like this:

    https://www.imdb.com/title/tt4846340/reference/

The code pictured below will obtain the movie's genres, runtime, country, language, and plot summary.  The immediate window shows the results.  This is advanced VBA stuff, and also quite far removed from Access topics, so I don't know if Richard would cover it.  But he would cover whatever that is highly requested (this is not it, I don't think).
Kevin Yip        
14 months ago

Richard Rost             
14 months ago
I covered something similar to this in a developer lesson years ago but I tend to not rely on web scraping because every time the website owner makes a change in the page you have to update your code so if it's not something I can get via an API then I generally don't bother.
Kevin Yip        
14 months ago
You're right that it isn't ideal -- and it's even less ideal if you have tons of webpages you need to scrape info from, because you'll have to code for every single page.  But as more and more users and businesses rely on online data, web-scraping may become essential for many people.  Short of having a direct connection to someone else's database server, the only way for most people to grab someone's online data is via web-scraping.  Suppose my boss is researching Computer Learning Zone and tasks me to get a list of all the posted items here: https://599cd.com/blog/display-articles2.asp .  To avoid spending hours going through page after page of headlines, I would have to write a VBA procedure to web-scrape the info.  

P.S. There are commercial plugins, 3rd party tools, online services, etc., that add web-scraping abilities, such as Microsoft "Power" services.  I've  seen a YouTube demo of Microsoft Power Automate setting up a (semi-)automatic workflow for scraping website info.
Kevin Yip        
14 months ago
Access can import an HTML table from a webpage into an Access table, but this feature doesn't work with all webpages.  First you need to save the webpage as an HTML file into your PC, because Access can only accept locally-saved HTML files.  Then you go to External Data -> New Data Source -> From File -> HTML document.  If it works well, you get a perfect import as shown in the picture below.  If the import doesn't work, Access gives you an error and/or blank data.  In my years using Access, I haven't had a great success rate with this.  So I often just wrote VBA procedures for web-scraping.
Kevin Yip        
14 months ago

Kerry Helmer       
14 months ago
Using an API sounded like the best method to achieve this to me.  I did some searching and found a few of them for stock data.  The Finnhub API seemed like it might be a good option.  Using an API is completely new to me and my VBA skills are very rusty and limited, but it looks like it could work. I'm still struggling to figure it out, but I'm not giving up yet!
Richard Rost             
14 months ago
That looks interesting. I've been looking for an alternative to Excel myself. Maybe I'll play around with this and put something together. I have a VERY long to-do list right now, so I can't promise it anytime soon... but tinkering with the market has been a hobby of mine for a little while now. :)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Stock Portfolio.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 6/24/2024 4:34:56 PM. PLT: 1s