Integrate Excel Functions into Access VBA for Real-Time Stock Data

Get Stock History into Microsoft Access - A Demonstration of How to Leverage Excel Functions in Access VBA

While Microsoft Access hasn't seen significant updates in recent years, Microsoft Excel continues to evolve with powerful new features. Despite this, Access remains a vital tool for niche solutions and small businesses seeking robust and affordable database management for small scale solutions. Leveraging Access VBA and the Excel 16.0 Object Library, users can now integrate modern Excel functions directly into Access. In this article, we'll demonstrate how to use VBA in Access to retrieve stock trading data by utilizing Excel's STOCKHISTORY function, allowing you to obtain and manipulate stock price arrays seamlessly within your Access applications.

Understanding the Excel 16.0 Object Library

The Excel 16.0 Object Library is a powerful tool that enables Microsoft Access to interact seamlessly with Excel files and data. With this library, you can:

• Manipulate Excel objects such as workbooks, worksheets, and ranges
• Manipute the data within an Excel workbook
• Automate repetitive tasks within Excel
• Utilize a wide range of Excel functionalities, including advanced worksheet functions like STOCKHISTORY
• Access and modify Excel data programmatically

To integrate the Excel 16.0 Object Library into Access, you can either add a reference directly or use late binding. Adding a reference provides easy access to Excel’s context menu and IntelliSense support, enhancing development efficiency. Late binding, on the other hand, offers greater flexibility and reduces dependencies. For simplicity and ease of use, this guide will focus on adding a reference, ensuring a straightforward setup for leveraging Excel’s capabilities within Access VBA.

Important Notes When Using the Excel 16.0 Object Library

When integrating the Excel 16.0 Object Library into Access VBA, proper resource management is crucial to ensure your application runs efficiently. In the provided code example, we create an instance of Excel using Set xlApp = New Excel.Application to utilize the STOCKHISTORY function. Here are some key points to consider:

    xlApp.Quit
	Set xlApp = Nothing

• Memory Management: Creating an Excel application object consumes system memory. If you do not clean up properly, each time the function runs, a new Excel instance remains active in the background. This can lead to multiple Excel processes appearing in the Task Manager, which may slow down your computer.
• Avoiding Multiple Instances: Without proper cleanup, repeated executions of the function will leave additional Excel instances running. This not only consumes more memory but can also cause conflicts and reduce the performance of your system.
• Error Handling: While the example omits error handling for simplicity, it's recommended to implement error handling in production code to ensure that Excel instances are closed even if an error occurs during execution.
• Resource Cleanup: After using Excel, it's essential to release the resources by calling xlApp.Quit and setting xlApp = Nothing. This ensures that the Excel instance is properly closed and memory is freed.

Retrieving the Actual Stock Data - How the GetStockData Function Works

In the GetStockData function, we retrieve historical stock prices for a specified ticker symbol within a given date range by leveraging Excel's STOCKHISTORY function directly in Access VBA. The function initializes an Excel application instance and attempts to evaluate the StockHistory function. Since STOCKHISTORY fetches data from the web, it may take some time to return results. To handle potential delays or temporary errors, the code includes a loop that retries the function up to 10 times, using On Error Resume Next to bypass errors and xlApp.Wait to pause for one second between attempts. Once the stock data is successfully retrieved, the function exits the loop, returns the data array, and ensures that the Excel instance is properly closed and released from memory. This kind of error handling is typically unnecessary for most other Excel functions, making it specific to functions that involve web data retrieval.

Function GetStockData(sTicker As String, dteStart As Date, dteEnd As Date)
' ----------------------------------------------------------------
' Purpose: Retrieves historical stock data for a specified ticker symbol over a specified date range.
' Parameter sTicker (String): The stock ticker symbol for the desired stock data.
' Parameter dteStart (Date): The starting date for the historical data retrieval.
' Parameter dteEnd (Date): The ending date for the historical data retrieval.
' Return type: (Variant)
' Author: Michael Markus Wycisk / Excellence Utvikling AS
' Created Date: 28.10.2024
' Change log:
' ----------------------------------------------------------------

    Dim xlApp As Excel.Application
    Dim vaStockPrices As Variant
    Dim lRetries As Integer
    
    ' Create a new instance of Excel
    Set xlApp = New Excel.Application
    lRetries = 0
    
    ' Since StockHistory receives data from the web, its evaluation takes time
    ' Retry up to 10 times
    For lRetries = 0 To 10
        On Error Resume Next
        ' Evaluate the function until it does not return an error
        ' Convert dates to Long to match Excel's date serial format
        vaStockPrices = xlApp.WorksheetFunction.StockHistory(sTicker, CLng(dteStart), CLng(dteEnd), 0, 0, 0, 1)
        On Error GoTo 0
        
        ' Once the function is evaluated, vaStockPrices will no longer be empty
        If Not IsEmpty(vaStockPrices) Then Exit For
        
        ' Wait for another second
        xlApp.Wait (Now + TimeValue("0:00:01")) ' Wait 1 second between retries
    Next lRetries
    
    ' Return the retrieved stock prices array
    GetStockData = vaStockPrices
    
    ' Clean up: Quit Excel and release the object from memory
    xlApp.Quit
    Set xlApp = Nothing
End Function

Example Usage: Storing Stock Data in Access Tables with DAO Recordsets

The GetStockDatafunction returns stock data as an array, providing a fast and efficient way to handle data directly within VBA. However, you may also want to store this data in an Access table for persistent storage and further analysis. In the PopulateTableWithStockData subroutine, we utilize a DAO recordset to insert the retrieved stock prices into the actblStockHistory table. Using a DAO recordset allows for efficient batch inserts and easy manipulation of database records. The subroutine includes a duplicate check using DLookup to ensure that each ticker-date pair is unique, which is straightforward for small datasets. For larger datasets, more efficient methods such to avoid duplicats should be considered to enhance performance. This could mean reading all pairs from the table into a dictionary to check for existing keys, which is typically quite fast.

Sub PopulateTableWithStockData()
' ----------------------------------------------------------------
' Purpose: Populates the actblStockHistory table with historical stock data for a specified ticker, ensuring no duplicate entries.
' Parameter sTicker (String): The stock ticker symbol for which historical data will be retrieved and stored.
' Parameter dteStart (Date): The start date for the period of stock data to retrieve.
' Parameter dteEnd (Date): The end date for the period of stock data to retrieve.
' Author: Michael Markus Wycisk / Excellence Utvikling AS
' Created Date: 28.10.2024
' Change log:
' ----------------------------------------------------------------
    
    ' Parameters for the function
    Dim sTicker As String
    Dim dteStart As Date
    Dim dteEnd As Date
    Dim vaStockPrices As Variant
    Dim lRow As Long
    
    ' Initialize the parameters
    sTicker = "DDD"
    dteStart = DateSerial(2024, 1, 1)
    dteEnd = DateSerial(2024, 10, 27)
    
    ' Retrieve stock data from the function
    vaStockPrices = GetStockData(sTicker, dteStart, dteEnd)
    
    ' Check if data was returned
    If IsArray(vaStockPrices) Then
        ' Set up DAO Database and Recordset for efficient batch inserts
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset("actblStockHistory", dbOpenDynaset)
        
        ' Start inserting rows into actblStockHistory
        For lRow = LBound(vaStockPrices, 1) To UBound(vaStockPrices, 1)
            ' Check if Ticker-Date pair already exists
            If IsNull(DLookup("Ticker", "actblStockHistory", "Ticker='" & sTicker & "' AND [Date]=#" & Format(vaStockPrices(lRow, 1), "yyyy-mm-dd") & "#")) Then
                ' If not found, add a new record
                rs.AddNew
                rs!Ticker = sTicker
                rs![Date] = vaStockPrices(lRow, 1)
                rs!ClosingPrice = vaStockPrices(lRow, 2)
                rs.Update
            End If
        Next lRow
        
        ' Clean up
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    Else
        ' Display message if no data is retrieved
        MsgBox "No data retrieved for the specified ticker and date range or an error occurred.", vbExclamation
    End If
End Sub

As can be seen in the image below, the actblStockHistory table has been successfully populated with data for the Microsoft Stock that we have just fetched.

Things to Be Aware Of When Using Excel Function in Access

Integrating Excel functions into Microsoft Access using the Excel 16.0 Object Library offers significant advantages, but it's essential to be mindful of certain shortcomings to ensure smooth and efficient application performance.

• Increased Complexity: Integrating Excel functions into Access introduces additional layers of complexity, requiring a good understanding of both platforms.
• Performance Overheads: Using Excel functions within Access can lead to performance slowdowns, especially with large datasets or frequent function calls.
• Resource Management Issues: Improper handling can cause memory leaks and multiple Excel instances running in the background.
• Version Compatibility: The Excel 16.0 Object Library is specific to certain Excel versions, which may cause compatibility issues if different versions are used.
• Limited Formula Support: Not all Excel formulas are directly usable in Access, requiring custom solutions or alternative approaches.

Despite these potential drawbacks, this solution is highly viable for small-scale applications, as Access is primarily designed for such use cases rather than large-scale deployments.

Enhancing Microsoft Access with Excel Functions: A Comprehensive Recap

In this article, we've demonstrated how to utilize Excel functions within Microsoft Access using the Excel 16.0 Object Library. Addressing the common challenge of retrieving stock prices, we showed how Excel's STOCKHISTORY function offers an easy and straightforward solution, providing a free alternative to API-based approaches which are better suited for more robust applications.

Furthermore, the concept of integrating Excel functions into Access extends beyond STOCKHISTORY. Even basic functions like MIN, MAX, and AVERAGE, which are readily available in Excel, can be seamlessly used in Access VBA, eliminating the need to develop complex custom functions. Additionally, many modern Excel functions can enhance the built-in capabilities of Access VBA, enabling more efficient and powerful database solutions. By bridging the strengths of both Excel and Access, you can create versatile applications tailored to your specific needs when applied to small scale projects and carefully mitigating possible challenges with this method.

Partnering for Success: Your Excel Consultant

At Excellence Utvikling AS, we understand the importance of good user experience and the application of best practice techniques when bulidng solutions and integrations between Excel, Access and other database systems for our clients. We will never compromise on quality when we work together with you to solve your challenges.

Our Excel consulting services cover a wide range of services, beyond VBA programming, Access and Excel integration. Whether it's designing business templates, creating powerful and timesaving Excel automations or tackling complex data challenges, our goal is to enable you to make the most out of Excel and the whole Microsoft 365 suite.

If you're ready to unlock the full potential of Excel, Access and the Microsoft 365 in your business, don't hesitate to reach out. We're here to guide you through every step of the process, and we're excited to partner with you to achieve your business goals.