How to Import Current Stock Prices into Access 2019
Question details
The user needs a method to automatically fetch and update live market prices for stock symbols stored in a Microsoft Access 2019 application.
- Product
- Microsoft Access 2019
- Device & OS
- not provided
- Scenario
- Updating a stock-equity database with the latest market data.
- Observed behavior
- The application requires a reliable integration to pull current stock prices and update existing records automatically.
Before integrating live data, ensure you have signed up for a reliable market-data API service and obtained your authentication API key.
Integrate a Market-Data API via VBA
Calling a REST API directly using VBA is the most reliable way to update stock prices in Access.
Using a direct API integration allows Access to query market data seamlessly. However, be aware of your chosen service's authentication requirements.
Select a stock API provider and review its specific authentication requirements and endpoint documentation.
Use the MSXML2.XMLHTTP object in Access VBA to send HTTP GET requests to the API endpoint, passing your stock symbols and API key.
Implement a JSON parsing library in your VBA project to extract the latest market price from the API's returned data.
Write an SQL UPDATE statement within your VBA script to overwrite the old stored prices with the newly fetched data.
Utilize Excel Automation
If VBA API integration proves too complex, you can leverage Excel's built-in data types and import the results into Access.
Manage Your Stock Portfolio Easily with WPS Office
Complex database setups and VBA coding in Access can be overwhelming. WPS Spreadsheet offers a lightweight, highly compatible alternative for tracking financial data. Manage your portfolios with advanced formulas, dynamic charts, and seamless Microsoft Office format compatibility—all for free.
Frequently Asked Questions
Can I use the Excel 'Stocks' data type directly inside Access 2019?
No, the 'Stocks' data type is an exclusive feature of Excel. To use it with Access, you must set up an Excel workbook and link it to your Access database or use VBA automation to pull the updated data from Excel.
Why does my VBA HTTP request fail when connecting to the stock API?
This usually happens due to unsupported security protocols, missing authentication headers, or CORS restrictions. Ensure your API key is correctly formatted in the request header and that the API doesn't require complex OAuth authentication that VBA natively struggles to process.
Is it better to use an API or Excel automation for updating stock prices?
Using a direct REST API via VBA is generally more reliable, faster, and easier to maintain for databases. Excel automation is a highly functional workaround if you cannot handle API authentication, but it can be slower and occasionally prone to application hanging.
