Retrieve ETF Holdings Directly in Excel

The ETFHoldings function (internally referred to as “fn_01238”) is designed to fetch and display an ETF's underlying holdings in your Excel workbook via MarketXLS. It automatically retrieves up-to-date holdings data for a specified ETF ticker and places it right into Excel. This helps investors and analysts see what's inside an ETF, explore its allocation, and make more informed decisions—right from within their favorite spreadsheet software.

Why Use This Function?

• Gain Transparency: Quickly see every underlying holding in an ETF without leaving Excel.
• Simplify Research: Evaluate the ETF composition, sector exposure, and weighting with a few clicks.
• Real-Time Convenience: Whenever you run the function, the latest holdings data is pulled directly into Excel.
• Fully Customizable Placement: Choose to output the results in a new sheet, in your current selection, or have the function prompt you for a range.
• Streamlined Workflow: No need to open multiple web pages or aggregator tools—everything is accessible within your existing MarketXLS setup.

How to Use in Excel

Although no direct worksheet formula syntax is currently available (it is not called via a typical “=FunctionName()” style), you can invoke this function through MarketXLS add-in options or any custom macro/VBA that references “ETFHoldings(Symbol)”.

Below is a conceptual call in VBA (or through MarketXLS custom methods):

Sub GetETFData()
    ' Example usage in a VBA-like environment
    ETFHoldings "SPY"  ' Fetches and places SPY’s holdings in Excel
End Sub

When you run this procedure or action in MarketXLS:

1. ETFHoldings requests the holdings data for the specified symbol.
2. It downloads the data into an internal DataTable.
3. The data is then placed in Excel based on your “DownloadedDataPlacement” preference set in MarketXLS.

Parameters Explained

• No additional parameters are required.
• The function retrieves the holdings data internally and outputs it into your Excel sheet or selection range.

Example Usage

Basic Examples

1. Retrieve SPY Holdings on a New Sheet

   • Configure MarketXLS to place downloaded data on a new sheet.
   • Call ETFHoldings "SPY".
   • A new sheet is automatically added with the entire SPY holdings table: columns for each holding, weight, and more.

2. Place QQQ Holdings in the Current Selection

   • Set “DownloadedDataPlacement” to “Selection.”
   • Select a cell to start placing data.
   • Call ETFHoldings "QQQ".
   • The table is placed starting at the top-left of your current selection, with column headers, rows, and data values.

3. Prompt for Where to Put VOO Holdings

   • Set “DownloadedDataPlacement” to “Ask.”
   • Call ETFHoldings "VOO".
   • The add-in prompts you to specify a cell in Excel.
   • Once you pick a location, the ETF holdings data table is placed starting from that cell.

Advanced Scenarios

• Bulk ETF Analysis

• Loop through a list of tickers (e.g., "SPY", "DIA", "IVV") in a VBA macro or power query-like approach.
• Automate calling ETFHoldings for each ticker to gather holdings inside separate worksheets.
• Perform side-by-side comparisons of holdings to identify shared stocks.

• Integrating with Other Excel Functions

• After the holdings are placed, you can combine them with other MarketXLS or Excel formulas to calculate performance statistics, sector distribution, or weighting analysis.

• Trading Strategy Examples

• For sector rotation strategies, swiftly retrieve holdings of multiple ETFs to see their top components, then filter or compare the cyclical exposures directly in Excel.

Common Questions and Troubleshooting

1. What happens if I pass an invalid symbol?

   • The function returns “NA” and does not place any table in Excel. Ensure you are using a correct and supported ticker symbol.

2. How can I control where data appears in Excel?

   • MarketXLS has a “DownloadedDataPlacement” setting with three modes: “Sheet,” “Selection,” or “Ask.” Adjust this in your MarketXLS settings.

3. Why am I seeing an error or blank table?

   • Check if you have a valid license. If your license is invalid, the function returns a message or raises an exception.
   • Some ETF data may not be included in certain subscription plans.

4. Can I edit the data after it’s placed?

   • Yes, once placed in Excel, treat it like any data range. Remember to re-run the function to update if needed.

Remember:

• ETFHoldings retrieves the entire holdings for a single ETF ticker.
• Ensure your MarketXLS plan supports the data for that ETF.
• The function is designed for US market scenarios but can be used with any ETF data available on MarketXLS.
• Choose your data placement method (new sheet, selection, or prompt) to integrate seamlessly with your existing workflows.
• For deeper analysis, combine with Excel’s built-in features (e.g., pivot tables) or other MarketXLS functions to create more powerful insights.