MidPrice Function for Quick Quote Retrieval in Excel

The MidPrice function in MarketXLS empowers you to fetch the “mid price” (commonly sourced from bid/ask or a quote response) for a given ticker symbol right within Excel. With a valid license, you can quickly get near real-time data or cached data, making it a handy tool for financial analysis, portfolio monitoring, or developing trading strategies right in your Excel spreadsheets.

Why Use This Function?

• Streamlined Data Retrieval: Quickly pulls mid price data for a specified symbol.
• Live or Cached Data: Uses an internal caching system to speed up repeated requests.
• Integrated Error Handling: Returns "NA" if an invalid symbol is passed, the license is invalid, or an exception occurs during data retrieval.
• Minimal Setup: Simply refer to a symbol directly or link to a cell containing the symbol.
• Real-World Trading Scenarios: Combine MidPrice data with other Excel formulas to build custom watchlists, track spreads, or construct real-time dashboards.
• Automated Refreshing: If the data is refreshing behind the scenes, the function may temporarily return “Refreshing” to indicate ongoing data updates.

How to Use in Excel

=MidPrice(Symbol)

• Replace Symbol with a valid ticker symbol (e.g., "AAPL", "MSFT", or using a cell reference like A2).
• Ensure your MarketXLS license is active.
• If the symbol is invalid or the license check fails, the function returns "NA".
• If data is currently being refreshed, “Refreshing” may be returned temporarily.

Parameters Explained

There is only one parameter, Symbol, which represents the security for which you need the mid price.

Example Usage

Basic Examples

1. Direct Symbol in Formula
   • In any cell, type:
   =MidPrice("AAPL")
   • This returns the current mid price (or “NA” if unavailable).

2. Using a Cell Reference
   • In cell A2, type the symbol, for example, A2: AAPL.
   • Then in another cell:
   =MidPrice(A2)
   • This makes your worksheet more dynamic, allowing you to change A2 to check different symbols.

3. Multiple Symbols in a Table
   • Suppose you have a list of symbols in column A. Use MidPrice(A2), MidPrice(A3), etc., in column B to retrieve mid prices for each ticker quickly.

Advanced Scenarios

1. Real-Time Dashboards
   • Combine MidPrice with other MarketXLS functions (e.g., BidPrice, AskPrice) to compare the spreads directly in Excel for rapid decision-making.

2. Watchlists with Conditional Formatting
   • In a watchlist, highlight cell changes when the MidPrice crosses a threshold, combining functions like IF or conditional formatting rules.

3. Handling “Refreshing” States
   • If the MarketXLS system is busy refreshing data, you might temporarily see “Refreshing” returned. You can wait or re-evaluate the cell later for the updated mid price.

Common Questions and Troubleshooting

• What if I get “NA”?

• This usually indicates an invalid ticker symbol, an expired license, or an error while fetching data. Double-check your symbol and license status.

• Why do I see “Refreshing” for my formula?

• The function queues your request if data is being refreshed. Once the data returns, it should replace “Refreshing” with the updated mid price.

• Does the function support all US market symbols?

• Typically, yes. If a particular symbol is unsupported by your data feed, it may return “NA.” Check if the symbol is valid and recognized by MarketXLS.

• Can I automate re-checks of the MidPrice throughout the day?

• Yes. You can use Excel’s built-in recalculation or macros to refresh your worksheets at set intervals.

By leveraging the MidPrice function, you gain instant access to important quote data right inside Excel. Whether you’re setting up automated watchlists, analyzing spreads, or creating advanced trading dashboards, MidPrice helps you stay on top of your symbols with just one simple function call.