Efficiently Manage Live Data Feeds with QM_Stream_Close

QM_Stream_Close (also known as fn_00012) is a specialized function in MarketXLS that helps you stop or request an end to real-time updates for a specified financial instrument directly within Excel. This prevents unwanted data usage, frees up resources, and ensures cleaner workbooks—particularly useful for traders, analysts, or anyone managing frequent real-time market queries.

By passing a target symbol to QM_Stream_Close, you instruct the backend to interpret “Close” as a trigger for unsubscribing or stopping the data feed for that symbol, depending on the data provider’s logic. Below, learn why you should use it, how to set it up, its parameters, specific examples, and potential troubleshooting tips in various real-world scenarios.

Why Use This Function?

• Control Unwanted Streaming: Prevent extra data charges and reduce clutter by turning off live feeds for symbols you’re no longer monitoring.
• Optimize Workbook Performance: Large Excel files tracking multiple tickers can slow down. Closing streams you don’t need ensures faster performance.
• Simplify Workflow: Easily manage on-demand data. For instance, you can automate morning opens and end-of-day closures for your watchlists.
• Avoid License or Configuration Issues: Only pull real-time data when necessary, reducing the risk of exceeding data entitlements or draining system resources.
• Clear End-of-Day Cleanup: Ideal for automated macros or scripts that systematically close all active data feeds at the end of the trading day.

How to Use in Excel

Use this function in your worksheet just like any other Excel formula. Simply enter the formula in a cell where you want the result (e.g., cell B2):

=QM_Stream_Close("AAPL")

This example attempts to stop streaming real-time data for the “AAPL” ticker symbol. The internal MarketXLS code passes "Close" as a metric to the real-time data provider, which may interpret the request as a command to terminate or unsubscribe from the data feed.

If your provider does not treat “Close” as an unsubscribe instruction, you may need to augment your setup with a dedicated unsubscribe or function call, or verify that the “Close” request is recognized correctly.

Parameters Explained

• Only one parameter is required—symbol—because QM_Stream_Close automatically uses “Close” internally as the metric.

Example Usage

Basic Examples

1. Stopping a Single Stock Stream
   In any cell:
   » =QM_Stream_Close("AAPL")
   This attempts to terminate the Apple Inc. (AAPL) real-time data feed.

2. Closing a Forex Pair
   » =QM_Stream_Close("EUR/USD")
   Ends (or requests to end) active streaming quotes for the Euro vs. U.S. dollar currency pair.

3. Commodity Close-Out
   » =QM_Stream_Close("CL")
   Ceases updates for crude oil futures, freeing resources when no longer needed.

4. Handling Invalid Inputs
   » =QM_Stream_Close("")
   Since the symbol is empty, the function returns an empty string ("").
   » =QM_Stream_Close("UNKNOWN")
   If the CheckSymbol validation fails, it returns “NA,” indicating that the ticker is invalid or not recognized.

Advanced Scenarios

1. Batch Closing with VBA
   A typical scenario might involve many active symbols throughout the day. Use a VBA macro to automate the closure:

   Sub StopAllStreamingData()
   Dim symbols As Variant
   symbols = Array("AAPL", "MSFT", "GOOG", "EUR/USD", "CL")

   Dim i As Long  
   For i = LBound(symbols) To UBound(symbols)  
       Range("A" & (i + 2)).Value = "=QM_Stream_Close(""" & symbols(i) & """)"  
   Next i  
   

   End Sub

   • This macro loops through the listed symbols and inserts QM_Stream_Close calls into corresponding cells (A2, A3, …), terminating each data feed in bulk.

2. End-of-Day Cleanup
   Combine the above macro with task scheduling or a workbook “BeforeClose” event to ensure that all streaming data is stopped by the close of market, minimizing unnecessary overnight data usage.

3. Integrating with Pricing Analysis
   If you run macros that fetch streaming data for real-time calculations (like relationships between multiple symbols), use QM_Stream_Close once you’ve captured the final data for the day or after your analysis is complete, so your workbook remains responsive.

Common Questions and Troubleshooting

• Q: “Why do I still see data updating even after calling QM_Stream_Close?”
  A: The function sends a “Close” metric request, but some data providers may require additional unsubscribe steps. Check your data seller’s documentation or see if MarketXLS’s RTDUnsubscribeAll method is needed to forcibly remove all subscriptions.

• Q: “I got 'NA' instead of stopping the feed. What happened?”
  A: This typically indicates the symbol does not pass validation (CheckSymbol). Verify the spelling of your ticker. Also confirm that your data provider supports that particular symbol.

• Q: “License error or ‘not configured’ messages keep appearing. How can I fix this?”
  A: Ensure your MarketXLS license is valid and that the data seller is configured (IsActive = True). Check Settings > Data Subscriptions in MarketXLS or verify with your data provider.

• Q: “Is there a delay in unsubscribing?”
  A: Yes, there can be network or server-side delays. While typically near-instant, a short lag may occur before data stops streaming.

• Q: “What if I have more than one real-time feed per symbol?”
  A: If your system allows opening multiple feeds (e.g., one for ask price, one for bid price), you may need additional calls or a more robust unsubscribe method. QM_Stream_Close passes the “Close” metric once, so confirm you’ve addressed every feed you want to stop.

Going forward, use QM_Stream_Close to manage real-time streams effectively, but confirm that your environment recognizes “Close” as an unsubscribe request. Where necessary, take advantage of supplementary methods (like RTDUnsubscribeAll) for a guaranteed shut-off if your data provider does not stop upon the “Close” metric alone. This ensures you maintain efficient, streamlined Excel workbooks without incurring unwanted data usage or performance overhead.