The Scenario
You manage a multi-asset portfolio. Fifteen ETFs across US equities, international equities, bonds, commodities, real estate, and alternatives. You're rebalancing next month and before you touch any weights you want to see the full pairwise correlation matrix — all 105 pairs, one year of daily returns, to understand which positions are genuinely decorrelated and which ones have been moving together despite the different labels.
You know what a correlation matrix is. You've never built one from a live data pull in Excel. The idea of fetching price histories for 15 ETFs, calculating daily returns, and running pairwise correlations across all 105 unique pairs sounds like an afternoon project, at minimum.
The bad version:
- Fetch 365 days of daily prices for each ETF from Twelve Data, 15 separate API calls, saving each response.
- Paste each price series into a separate column in a returns calculation sheet, calculate percentage changes, and organize the returns into a format Excel's correlation functions can reference.
- Write 105 CORREL formulas — or attempt an array-formula correlation matrix using MMULT, which requires understanding Excel array mechanics well enough to get the dimensions right.
By the time the matrix is populated, you've spent most of a day on the setup and have no energy left for the rebalancing analysis the matrix was supposed to enable.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook. It reads the 15 ETF tickers in column A and, through its built-in Twelve Data integration, fetches one year of daily price data for each, calculates returns, and builds the full pairwise correlation matrix on a dedicated worksheet.
Calculate the Pearson correlation coefficient from Twelve Data between every pair of tickers in column A using 1-year daily data. Build a full correlation matrix on a new worksheet called Correlation Matrix, with tickers as both row and column headers.
What You Get
- A complete 15x15 symmetric correlation matrix on a dedicated worksheet.
- Tickers as row and column headers, diagonal cells set to 1.00, all other cells rounded to two decimal places.
- Color-coded output: green for correlations above 0.8, white for mid-range, red for correlations below -0.3.
- A header note recording the date range used for the calculation.
What If the Data Is Not Quite Ready
If you want 90-day correlation to capture more recent market behavior
Calculate Pearson correlation coefficients from Twelve Data for every pair of tickers in column A using 90-day daily data. Build the matrix on a new worksheet with the same format and note the 90-day lookback period in the header.
If you want to compare 1-year and 90-day correlations side by side
Calculate Pearson correlations using both 1-year and 90-day daily data from Twelve Data for all ticker pairs in column A. Write the 1-year matrix starting at A1, the 90-day matrix starting at A20, and a difference matrix (90-day minus 1-year) starting at A40 on the Correlation Matrix worksheet.
If your list includes crypto assets alongside equity ETFs
Fetch 1-year daily price data from Twelve Data for each symbol in column A, using the equity endpoint for ETFs and the crypto endpoint for symbols flagged as CRYPTO in column B. Calculate Pearson correlations across all pairs and build the full matrix.
Kill-chain: build the matrix, identify diversifiers, flag redundant positions, and sort the portfolio sheet
Calculate the full Pearson correlation matrix from Twelve Data for all ticker pairs in column A using 1-year daily data. Write the matrix to a Correlation Matrix worksheet. Back in the main sheet, calculate each ETF's average correlation with all others and write into column B. Flag ETFs with average correlation below 0.3 as DIVERSIFIER in column C. Flag ETFs with average correlation above 0.7 as REDUNDANT in column C. Sort the main sheet by average correlation ascending so the strongest diversifiers appear at the top.
Try It
Get the 7-day free trial of SheetXAI and open your multi-asset portfolio workbook with ETF tickers in column A, then ask it to build the full correlation matrix and identify which positions are genuinely diversifying before you rebalance. Also see enriching a stock list with company metadata and the full Twelve Data overview.