Script for Real Time Quotes from Fidelity

Real-Time Stock Quotes in Excel VBA from Fidelity

Learn how to create a VBA script to fetch real-time stock quotes directly into your Excel spreadsheets using Fidelity's Active X controls.

Integrating real-time financial data into Excel can significantly enhance your analytical capabilities. This article provides a comprehensive guide on how to leverage Fidelity's Active X controls within VBA to pull live stock quotes directly into your spreadsheet. While Fidelity offers robust tools for trading and analysis, accessing this data programmatically through VBA allows for custom dashboards, automated tracking, and personalized alerts. This method is particularly useful for users who actively manage their portfolios with Fidelity and wish to extend Excel's functionality with live market data.

Prerequisites and Setup

Before you can begin scripting, ensure you have the necessary components installed and configured. This process involves enabling the Developer tab in Excel, adding the Fidelity Active X control, and understanding its basic properties. Fidelity's Active X control acts as a bridge, allowing your VBA code to communicate with Fidelity's data services. It's crucial to have a Fidelity account and the necessary software (like Fidelity Active Trader Pro) installed, as the Active X control typically relies on these underlying applications for data access.

1. Enable Developer Tab in Excel

Go to File > Options > Customize Ribbon. Under 'Main Tabs', check the 'Developer' box and click OK.

2. Insert Fidelity Active X Control

In the Developer tab, click 'Insert' in the 'Controls' group, then select 'More Controls' (the hammer and wrench icon). Scroll down and find 'Fidelity Active X Control' (or similar, depending on your Fidelity software version). Select it and click OK. Draw the control anywhere on your worksheet.

3. Verify Control Properties

With the control selected, open the 'Properties' window (Developer tab > Controls > Properties). Note down the 'Name' property (e.g., FidelityActiveXControl1). This name will be used in your VBA code.

Understanding the Fidelity Active X Control

The Fidelity Active X Control exposes various methods and properties that allow you to interact with Fidelity's data services. For real-time quotes, the primary method you'll be interested in is typically related to requesting quote data for a given ticker symbol. The control often provides events that fire when new data becomes available, which is essential for real-time updates. You'll need to identify the specific method for requesting quotes and the event that signals data arrival.

flowchart TD
    A[Excel VBA Macro] --> B{"Fidelity Active X Control"}
    B --> C[Request Quote (e.g., 'MSFT')]
    C --> D[Fidelity Data Service]
    D --> E[Return Real-time Data]
    E --> F{"Fidelity Active X Control Event (e.g., OnQuoteUpdate)"}
    F --> G[VBA Event Handler]
    G --> H[Update Excel Cells]

Workflow for fetching real-time quotes using Fidelity Active X Control

VBA Script for Real-Time Quotes

The core of this solution lies in the VBA code. You will write a macro that initializes the Fidelity Active X control, requests quotes for specified ticker symbols, and then handles the incoming data to update your Excel sheet. This typically involves setting up an event handler for the control's data update event. For simplicity, we'll focus on a basic implementation that fetches the last price, but the control often provides a wealth of other data points like bid/ask, volume, and more.

Private WithEvents FidelityQuote As FidelityActiveXControl

Private Sub Worksheet_Activate()
    ' Set the FidelityQuote object to the control on the sheet
    Set FidelityQuote = Me.FidelityActiveXControl1 ' Adjust control name if different
    
    ' Request quotes for specific symbols
    ' The exact method name might vary (e.g., RequestQuote, SubscribeQuote)
    ' You might need to consult Fidelity's documentation for the precise method signature.
    ' For demonstration, let's assume a method called 'RequestQuote' that takes a ticker.
    FidelityQuote.RequestQuote "MSFT"
    FidelityQuote.RequestQuote "AAPL"
    FidelityQuote.RequestQuote "GOOGL"
End Sub

' Event handler for when a quote update is received
' The event name and parameters will depend on the Fidelity Active X Control's API.
' For demonstration, let's assume an event 'OnQuoteUpdate' with Ticker and LastPrice.
Private Sub FidelityQuote_OnQuoteUpdate(ByVal Ticker As String, ByVal LastPrice As Double)
    Dim r As Long
    
    ' Find the row for the ticker and update the price
    For r = 2 To Me.Cells(Rows.Count, 1).End(xlUp).Row ' Assuming tickers start in A2
        If Me.Cells(r, 1).Value = Ticker Then
            Me.Cells(r, 2).Value = LastPrice ' Assuming price goes in column B
            Exit For
        End If
    Next r
End Sub

Private Sub Worksheet_Deactivate()
    ' Clean up when leaving the sheet
    Set FidelityQuote = Nothing
End Sub

VBA code to request and update real-time stock quotes from Fidelity.

Enhancing the Script and Error Handling

To make the script more robust, consider adding features like dynamic ticker lists, more comprehensive error handling, and formatting for the displayed data. You might want to read tickers from a specific range in your worksheet, allowing users to easily add or remove symbols without modifying the VBA code. Error handling is crucial to manage scenarios where the control might not initialize correctly, or if a requested ticker symbol is invalid. Additionally, formatting the output cells (e.g., currency format for prices) will improve readability.

Private WithEvents FidelityQuote As FidelityActiveXControl

Private Sub Worksheet_Activate()
    On Error GoTo ErrorHandler
    Set FidelityQuote = Me.FidelityActiveXControl1
    
    ' Clear previous data (optional)
    Me.Range("B2:B" & Rows.Count).ClearContents
    
    ' Request quotes for symbols listed in Column A, starting from A2
    Dim r As Long
    For r = 2 To Me.Cells(Rows.Count, 1).End(xlUp).Row
        Dim tickerSymbol As String
        tickerSymbol = Trim(Me.Cells(r, 1).Value)
        If Len(tickerSymbol) > 0 Then
            FidelityQuote.RequestQuote tickerSymbol
        End If
    Next r
    Exit Sub
    
ErrorHandler:
    MsgBox "Error initializing Fidelity Active X Control: " & Err.Description, vbCritical
End Sub

Private Sub FidelityQuote_OnQuoteUpdate(ByVal Ticker As String, ByVal LastPrice As Double)
    Dim r As Long
    For r = 2 To Me.Cells(Rows.Count, 1).End(xlUp).Row
        If Me.Cells(r, 1).Value = Ticker Then
            With Me.Cells(r, 2)
                .Value = LastPrice
                .NumberFormat = "$#,##0.00"
            End With
            Exit For
        End If
    Next r
End Sub

Private Sub Worksheet_Deactivate()
    Set FidelityQuote = Nothing
End Sub

Enhanced VBA script with dynamic ticker loading and basic error handling.