The Scenario
You manage a multi-asset portfolio with 15 ETFs representing different asset classes — US equities, international equities, bonds, commodities, real estate, and alternatives. You're rebalancing next week and before you touch anything you want to see the full pairwise correlation matrix using one year of daily returns. You need to know which positions are genuinely diversifying and which ones you thought were diversifying but have been moving together.
You've never built a correlation matrix from a live data pull. You know it involves fetching price histories for all 15 ETFs, calculating daily returns, and then running pairwise correlations across all 105 unique pairs. You have no idea how long that would take to do manually.
The bad version:
- Fetch 365 days of daily closing prices for each ETF using the Twelve Data time series endpoint, saving 15 separate JSON responses.
- Paste each price series into a separate column of a returns calculation sheet, calculate percentage changes, and arrange the returns data into a format your correlation formula can reference.
- Write 105 individual CORREL formulas — or a correlation matrix using MMULT and array functions that require significant Excel or Sheets formula expertise — and format the output as a readable matrix.
This is a legitimate quantitative task. It's also an entire afternoon that was supposed to be the rebalancing analysis, not the data setup.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet. 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.
Calculate the Pearson correlation coefficient from Twelve Data between every pair of tickers in column A using 1-year daily data. Build a correlation matrix with tickers as both row and column headers, starting at cell A1 of a new tab called Correlation Matrix.
What You Get
- A full 15x15 symmetric correlation matrix on a dedicated tab.
- Tickers as row and column headers, with the diagonal set to 1.00 and all off-diagonal cells showing the Pearson correlation coefficient rounded to two decimal places.
- Cells color-coded automatically: dark green for correlations above 0.8 (highly correlated), white for mid-range, red for correlations below -0.3 (negative correlation, potential hedges).
- A note in the corner cell recording the date range used for the calculation.
What If the Data Is Not Quite Ready
If you want 90-day correlation instead of 1-year to capture more recent regime behavior
Calculate Pearson correlation coefficients from Twelve Data for every pair of tickers in column A using 90-day daily data. Build a correlation matrix on a new tab with the same format. In the header, note that the correlation is based on the trailing 90 calendar days.
If you want to compare correlations from two different time periods to see how they've shifted
Calculate Pearson correlations from Twelve Data for all ticker pairs in column A using both the past 1 year and the past 90 days. Build two matrices on the same tab: the 1-year matrix starting at A1, the 90-day matrix starting at A20. In a third block starting at A40, show the difference (90-day minus 1-year) so you can see which pairs have become more or less correlated recently.
If your ticker list includes crypto assets alongside traditional ETFs
Fetch 1-year daily price data from Twelve Data for each symbol in column A. For equity ETF symbols, use the standard equity endpoint. For crypto symbols (flagged in column B as CRYPTO), use the crypto endpoint. Calculate Pearson correlations across all pairs regardless of asset class and build the full matrix.
Kill-chain: build correlation matrix, identify top diversifiers, and flag redundant positions
Calculate the Pearson correlation matrix from Twelve Data for all ticker pairs in column A using 1-year daily data and write the full matrix to a Correlation Matrix tab. In the main sheet, for each ETF, calculate the average correlation with all other ETFs and write into column B. Flag tickers with average correlation below 0.3 as DIVERSIFIER in column C (low average correlation means the position is genuinely diversifying). Flag tickers with average correlation above 0.7 as REDUNDANT in column C (high average correlation means you may hold overlapping exposure). Sort the main sheet by average correlation ascending so the strongest diversifiers appear first.
Try It
Get the 7-day free trial of SheetXAI and open your multi-asset portfolio sheet with ETF tickers in column A, then ask it to build the full correlation matrix and identify which positions are diversifying before you rebalance. Also see enriching a stock list with company metadata and the full Twelve Data overview.