Retrieve Monthly Portfolio Returns with MarketXLS

The MonthlyReturns function allows you to quickly retrieve, display, and analyze monthly returns for any portfolio of stocks or ETFs directly in Excel. Using the MarketXLS system, you can specify a portfolio along with an optional period (default 12 months) to display each month’s total weighted return. This function prints a table of dates and returns to your Excel sheet, helping you evaluate performance, spot trends, and make more informed decisions without leaving Excel.

Why Use This Function?

• Easily analyze portfolio performance over a chosen set of months.
• Compare monthly returns across multiple tickers in a single command.
• Ideal for tracking risk and reward in different market cycles.
• No manual data gathering—MarketXLS handles all retrieval and calculations.
• Great for monitoring short- or long-term investment horizons.
• Useful in real-world financial tasks like building monthly performance dashboards or modeling portfolio returns for clients.

How to Use in Excel

Below is the Excel syntax for the function. You can type this formula directly into a cell:

=MonthlyReturns(Portfolio, [Period])

• Portfolio refers to either a named range or a structured array containing each ticker and its weight (e.g., MSFT=0.4, AAPL=0.6).
• Period (optional) specifies how many months of returns to fetch (defaults to 12 if you leave it blank).

Once entered, the MonthlyReturns function sends a background request to MarketXLS servers to compute the monthly returns for each included ticker, combining them into a single weighted portfolio return for each month. The function then inserts a date and corresponding return value for each row (month) into your Excel sheet.

Parameters Explained

Example Usage

Basic Examples

1. Using the Default 12 Months
   Suppose cells A1:A2 contain:
   • A1: MSFT ? 0.5
   • A2: GE ? 0.5
   Enter the formula in cell C1:
   =MonthlyReturns(A1:A2)
   This returns a table of 12 months of returns for the combined MSFT/GE portfolio.

2. Specifying a Different Period
   If you want to see 6 months instead of 12, just include the second parameter:
   =MonthlyReturns(A1:A2, 6)
   This prints 6 months of returns instead of 12.

Advanced Scenarios

• Multiple Tickers and Adjusted Weights
If you have a portfolio with three tickers—MSFT, GE, and ENPH—and their weights add up to 1, place them in a range (e.g., A1:B3). Then call:
=MonthlyReturns(A1:B3, 18)
This function automatically handles weighting and returns 18 months of monthly returns.
• Integrating with Other Excel Functions
You can combine the output of MonthlyReturns with other MarketXLS statistical or charting functions to visualize the differences in monthly performance, highlight your best or worst months, or feed these values into custom dashboards.

Common Questions and Troubleshooting

• What if my portfolio data is not valid or has missing tickers?
If the portfolio is invalid, the function returns an error like “Portfolio is not valid” or “NA.” Double-check that you’ve entered correct weights and valid US symbols.

• Why do I only see the function name or “NA”?
If MarketXLS licensing is not recognized or if an exception occurs, the cell might display just the function name (MonthlyReturns) or “NA.” Ensure you have an active license and a valid portfolio range.

• Can I pass an empty Period parameter?
Yes. The function defaults to 12 months if Period is not specified or left as an empty argument.

• Edge Cases and Handling

• If any ticker returns no data, that ticker is excluded, and the function auto-adjusts the remaining portfolio weights.
• The function is intended for publicly traded US stocks and ETFs—if you supply a ticker that does not exist or is delisted, it may be removed from the results.

Remember:

• The function prints a date and the portfolio’s return for each month in a table format, making it easy to read and further manipulate in Excel.
• You can specify longer or shorter time horizons to match your analytical needs.
• For large portfolios (up to 500 tickers as indicated), the function will handle them but may take extra time to calculate.

By incorporating the MonthlyReturns function into your Excel workflow, you can seamlessly monitor and review monthly portfolio performance, helping you stay on top of risk-adjusted returns and make proactive investment decisions.