A Complete Guide to OptionSymbolConvert: Converting Option Tickers Between Brokers

A Complete Guide to OptionSymbolConvert: Converting Option Tickers Between Brokers

=OptionSymbolConvert(OptionSymbol, from_format, to_format) is a MarketXLS Excel function that converts an option ticker from one broker or data provider format to another. It supports 8 format codes: QuoteMedia ("qm", the format MarketXLS uses), Fidelity ("fd"), Charles Schwab ("cs"), Yahoo Finance ("yf"), eSignal ("es"), TradeStation ("ts"), E*Trade ("et"), and MetaStock ("ms"). For example, =OptionSymbolConvert("AAPL 12/20/24 C150", "fd", "qm") turns a Fidelity symbol into the QuoteMedia symbol that MarketXLS price functions accept. Leave from_format blank to let the function detect the source format.

Converting matters because the same contract appears as @AAPL 241220C00150000 in QuoteMedia and AAPL 12/20/24 C150 at Fidelity, which makes it hard to price broker positions in Excel or compare data across platforms.

The Problem: Incompatible Option Symbol Formats

Different brokers and data providers use their own proprietary formats for option symbols:

• QuoteMedia (MarketXLS): @AAPL 241220C00150000
• Fidelity: AAPL 12/20/24 C150
• Charles Schwab: Different format variations
• Yahoo Finance: Yet another format
• And so on...

This creates headaches when you need to:

• Compare options data from multiple sources
• Import positions from your broker into Excel
• Use streaming functions with broker-specific tickers
• Share analysis with colleagues using different platforms

The Solution: OptionSymbolConvert()

MarketXLS converts between formats with one function.

Availability

Function: OptionSymbolConvert(). Some functions differ between the Windows desktop add-in and the Microsoft 365 add-in; check the MarketXLS formulas page for your platform.

Basic Syntax

=OptionSymbolConvert(OptionSymbol, from_format, to_format)

Formula documentation: OptionSymbolConvert, OptionSymbol

Parameters:

• OptionSymbol - The option ticker you want to convert
• from_format - Source format code (or leave blank for auto-detection)
• to_format - Destination format code

Supported Broker Formats

OptionSymbolConvert supports 8 major formats:

Working with multiple brokers? See the guide to the top 10 brokers for option traders.

Practical Examples

Example 1: Converting MarketXLS to Fidelity

You have option symbols from MarketXLS and need them in Fidelity format:

=OptionSymbolConvert("@AAPL 241220C00150000", "qm", "fd")

Formula documentation: OptionSymbolConvert

Result: Converts the QuoteMedia format to Fidelity format

Example 2: Auto-Detection

Don't know the source format? Let MarketXLS detect it automatically:

=OptionSymbolConvert("@AAPL 241220C00150000", "", "fd")

Formula documentation: OptionSymbolConvert

Note: Leave the from_format parameter empty ("") and the function will automatically detect the source format.

Example 3: Converting from Fidelity to Yahoo Finance

=OptionSymbolConvert("AAPL 12/20/24 C150", "fd", "yf")

Formula documentation: OptionSymbolConvert

Example 4: Batch Conversion

Convert an entire column of option symbols:

Setup:

• Column A: Your broker's option symbols
• Column B: Formula for conversion

Cell B2:

=OptionSymbolConvert(A2, "", "qm")

Formula documentation: OptionSymbolConvert

Drag the formula down to convert all symbols to MarketXLS format.

Example 5: Converting Multiple Strikes

Convert a list of different strikes for the same underlying:

=OptionSymbolConvert("@TSLA 241220C00200000", "qm", "fd")
=OptionSymbolConvert("@TSLA 241220C00210000", "qm", "fd")
=OptionSymbolConvert("@TSLA 241220C00220000", "qm", "fd")

Formula documentation: OptionSymbolConvert

Using with MarketXLS Streaming Functions

OptionSymbolConvert can feed a converted symbol straight into MarketXLS streaming functions. Streaming option prices require the Advanced or Business plan; each streamed contract uses one live symbol subscription.

Streaming Last Price from Different Formats

If you have option symbols from Fidelity but want to stream live data using MarketXLS functions:

=QM_Stream_Last(OptionSymbolConvert("AAPL 12/20/24 C150", "fd", "qm"))

Formula documentation: QM_Stream_Last, OptionSymbolConvert

What this does:

1. Converts Fidelity format to QuoteMedia format
2. Feeds the converted symbol to QM_Stream_Last()
3. Streams the last price while streaming is on

For more on streaming live option prices in Excel, see the dedicated guide.

Common Streaming Function Combinations

Stream Bid Price

=QM_Stream_Bid(OptionSymbolConvert(A2, "", "qm"))

Formula documentation: QM_Stream_Bid, OptionSymbolConvert

Stream Ask Price

=QM_Stream_Ask(OptionSymbolConvert(A2, "", "qm"))

Formula documentation: QM_Stream_Ask, OptionSymbolConvert

Stream Volume

=QM_Stream_Volume(OptionSymbolConvert(A2, "", "qm"))

Formula documentation: QM_Stream_Volume, OptionSymbolConvert

Building a Multi-Broker Options Dashboard

Scenario: You have positions at multiple brokers and want a unified view.

Cell Setup:

The dashboard now shows streaming prices for positions from every broker in one sheet. Want to pull complete option chains? See our guide on getting live option chain data in Excel.

Advanced Use Cases

1. Options Chain Analysis Across Platforms

Compare options chains from different data sources:

' Get the MarketXLS option chain
=QM_GetOptionChain("AAPL")

' Convert to compare with your broker's format
=OptionSymbolConvert(A2, "qm", "fd")

Formula documentation: QM_GetOptionChain, OptionSymbolConvert

2. Portfolio Import Automation

Create a template that automatically converts imported broker statements:

1. Import your broker's option positions
2. Use OptionSymbolConvert to standardize all symbols
3. Feed standardized symbols into MarketXLS analysis functions

3. Cross-Platform Trade Verification

Verify that your broker executed the right option:

' Cell A1: Symbol from broker confirmation
' Cell B1: Symbol you intended to trade
=IF(OptionSymbolConvert(A1,"fd","qm")=B1, "Match ✓", "MISMATCH ✗")

Formula documentation: OptionSymbolConvert

Tips and Best Practices

1. Use Auto-Detection for Flexibility

When building templates that might receive data from multiple sources:

=OptionSymbolConvert(A2, "", "qm")

Formula documentation: OptionSymbolConvert

The same formula then works whichever broker the symbols came from.

2. Combine with IFERROR for Clean Output

Handle conversion errors gracefully:

=IFERROR(OptionSymbolConvert(A2, "", "qm"), "Invalid Symbol")

Formula documentation: OptionSymbolConvert

3. Create Named Ranges for Format Codes

For better readability, create named ranges:

• Name: FORMAT_FIDELITY, Value: "fd"
• Name: FORMAT_MARKETXLS, Value: "qm"

Then use:

=OptionSymbolConvert(A2, FORMAT_FIDELITY, FORMAT_MARKETXLS)

Formula documentation: OptionSymbolConvert

4. Build a Conversion Reference Table

Create a quick reference showing the same option in all formats:

Cell A1: @AAPL 241220C00150000

Common Error Messages and Solutions

"Invalid Symbol Format"

Cause: The source symbol doesn't match any recognized format Solution: Check for typos or manual formatting issues

"#VALUE!"

Cause: Missing or incorrect format code Solution: Verify you're using correct format codes ("qm", "fd", etc.)

"Conversion Not Supported"

Cause: Direct conversion between certain format pairs might not be available Solution: Convert through QuoteMedia format as an intermediate step

Real-World Workflow Example

Scenario: You're a trader using Fidelity with option positions you want to analyze in Excel.

Step 1: Export from Fidelity

Export your positions to Excel (or copy/paste from Fidelity's website)

Step 2: Convert to MarketXLS Format

In column B:

=OptionSymbolConvert(A2, "fd", "qm")

Formula documentation: OptionSymbolConvert

Step 3: Add Real-Time Data

In column C (Last Price):

=QM_Stream_Last(B2)

Formula documentation: QM_Stream_Last

In column D (Delta), using the stock price, option price, expiry, type, and strike:

=opt_Delta(Last("AAPL"), C2, DATE(2024,12,20), "Call", 150)

Formula documentation: opt_Delta, Last

In column E (Implied Volatility), with the same inputs:

=opt_ImpliedVolatility(Last("AAPL"), C2, DATE(2024,12,20), "Call", 150)

Formula documentation: opt_ImpliedVolatility, Last

Step 4: Calculate Portfolio Metrics

Now you have a live-updating options portfolio with:

• Current prices
• Greeks (Delta, Gamma, Theta, Vega)
• Implied volatility
• Position P&L

Prices update while streaming is on (Advanced or Business plan).

Compatibility Notes

• Excel Versions: Compatible with Excel 2013 and later
• Platforms: Windows desktop add-in (=OptionSymbolConvert(...)) and the Microsoft 365 add-in for Mac and Excel for the web, where formulas use the mxls. prefix (for example =mxls.OptionSymbolConvert(...)). Check the formulas page for platform availability.
• Data Refresh: Works with both manual refresh and auto-refresh modes

Frequently Asked Questions

Q: Can I convert symbols in bulk? A: Yes! Simply drag the formula down to convert entire columns of symbols.

Q: Does auto-detection work with all formats? A: Auto-detection works with most common formats, but specifying the source format explicitly is more reliable for edge cases.

Q: Will this work with weekly options? A: Yes, the function handles standard monthly options, weekly options, and quarterly options.

Q: Can I convert index options (SPX, NDX)? A: Yes, the function supports all option types including index options.

Q: What if my broker isn't on the supported list? A: Contact MarketXLS support at support@marketxls.com to request a new format.

Conclusion

OptionSymbolConvert translates option symbols between 8 broker and data provider formats inside Excel, so you can price and compare positions from any supported broker with MarketXLS functions.

Key Takeaways: ✓ Supports 8 major broker and data provider formats ✓ Auto-detection feature for unknown source formats ✓ Feeds converted symbols into MarketXLS streaming functions ✓ Enables multi-broker portfolio analysis ✓ Replaces manual symbol translation

Get Started Today

Ready to simplify your options data workflow?

1. Open Excel and activate MarketXLS
2. Try the examples above with your actual broker symbols
3. Build your custom multi-broker options dashboard

Need help? Our support team is standing by to assist with your specific broker integration needs.

---

Have questions about OptionSymbolConvert or want to share how you're using it? Leave a comment below or contact our support team!