Fund analytics — fund_analytics_YYYYMMDD.txt.gz
One file of the SQXray data dictionary. Field list (.csv)
Fund analytics — fund_analytics_YYYYMMDD.txt.gz
One row per share class with a return series: the computed measures — trailing returns, growth of 10,000, deviation, Sharpe, drawdown, investor return — and the SQX Fund Rating computed from them in the same run, with a status naming why a class is unrated. Methodology: /fund-rating-methodology/.
| Field | Type | Nullable | Description | Example |
|---|---|---|---|---|
fund_isin | VARCHAR(12) | never blank (key) | The share class's ISIN, always an ISIN, and the key of every share-class file. Join the fund reference file on it for name, ticker, family and portfolio; the fund class periods file for the periods behind these measures. | JP90C00000B4 |
portfolio_id | BIGINT UNSIGNED | never blank | The portfolio the class belongs to - the same number the fund reference and portfolio holdings files carry, so a class reaches its book in one join. A stable number, not a code to parse. | 20834 |
analysis_date | DATE | never blank | The date the row was computed for. | 2026-08-27 |
return_source | VARCHAR(16) | never blank | Where the one-day to year-to-date returns come from. nav_daily is a change in NAV, which does not add distributions back; nport_monthly is a filed total return. Horizons of a year or more are always filed total returns. | nav_daily |
nav_asof | DATE | may be blank | The NAV close the short-horizon returns are measured to; blank when they come from monthly returns. | 2026-08-27 |
latest_return_month | DATE | may be blank | First day of the newest filed month of total return the row is measured to. | 2026-06-01 |
history_start_month | DATE | may be blank | First month of the unbroken run of monthly returns ending at latest_return_month. | 2026-01-01 |
months_available | SMALLINT UNSIGNED | never blank | Months in that unbroken run; a horizon longer than this is blank. | 0 |
return_1d | DECIMAL(12,6) | may be blank | One-day change to nav_asof, percent. | -0.204841 |
return_1w | DECIMAL(12,6) | may be blank | Change over the trailing 7 calendar days to nav_asof, percent. | -0.148489 |
return_1m | DECIMAL(12,6) | may be blank | Trailing one-month return, percent. | 1.754012 |
return_3m | DECIMAL(12,6) | may be blank | Trailing three-month return, percent. | 1.537611 |
return_ytd | DECIMAL(12,6) | may be blank | Return since the prior year end, percent. On the NAV channel it needs a close in the last week of the prior year, which the NAV history reaches from 2027; on the filed channel every month since January. | 5.933394 |
return_1y | DECIMAL(12,6) | may be blank | Trailing 1-year total return, percent, annualised where the window exceeds one year; from monthly filed returns. | 2.913488 |
return_3y | DECIMAL(12,6) | may be blank | Trailing 3-year total return, percent, annualised where the window exceeds one year; from monthly filed returns. | |
return_5y | DECIMAL(12,6) | may be blank | Trailing 5-year total return, percent, annualised where the window exceeds one year; from monthly filed returns. | |
return_since_start | DECIMAL(12,6) | may be blank | Total return over the unbroken run from history_start_month, percent, annualised where the run exceeds one year; since inception only where the fund is younger than our series. | 1.472153 |
growth_10k_1y | DECIMAL(14,2) | may be blank | Value today of 10,000 invested 1 year(s) ago, from the same filed monthly returns. | 10291.348825 |
growth_10k_3y | DECIMAL(14,2) | may be blank | Value today of 10,000 invested 3 year(s) ago, from the same filed monthly returns. | |
growth_10k_5y | DECIMAL(14,2) | may be blank | Value today of 10,000 invested 5 year(s) ago, from the same filed monthly returns. | |
stdev_1y | DECIMAL(12,6) | may be blank | Annualised standard deviation of monthly total returns over the trailing 1 year(s), percent. | 2.600875 |
stdev_3y | DECIMAL(12,6) | may be blank | Annualised standard deviation of monthly total returns over the trailing 3 year(s), percent. | |
stdev_5y | DECIMAL(12,6) | may be blank | Annualised standard deviation of monthly total returns over the trailing 5 year(s), percent. | |
sharpe_1y | DECIMAL(12,6) | may be blank | Annualised mean monthly return over the risk-free rate divided by the annualised deviation of that excess, trailing 1 year(s). | -0.463620 |
sharpe_3y | DECIMAL(12,6) | may be blank | Annualised mean monthly return over the risk-free rate divided by the annualised deviation of that excess, trailing 3 year(s). | |
sharpe_5y | DECIMAL(12,6) | may be blank | Annualised mean monthly return over the risk-free rate divided by the annualised deviation of that excess, trailing 5 year(s). | |
max_drawdown_3y | DECIMAL(12,6) | may be blank | Worst peak-to-trough decline of the compounded monthly path over the trailing 3 years, negative percent; 0 when the path never fell below a prior high. | |
drawdown_peak_month_3y | DATE | may be blank | The month the trailing 3-year worst drawdown fell from. | |
drawdown_valley_month_3y | DATE | may be blank | The month the trailing 3-year worst drawdown bottomed. | |
drawdown_months_3y | SMALLINT UNSIGNED | may be blank | Months from that peak to that valley in the trailing 3-year window. | |
max_drawdown_5y | DECIMAL(12,6) | may be blank | Worst peak-to-trough decline of the compounded monthly path over the trailing 5 years, negative percent; 0 when the path never fell below a prior high. | |
drawdown_peak_month_5y | DATE | may be blank | The month the trailing 5-year worst drawdown fell from. | |
drawdown_valley_month_5y | DATE | may be blank | The month the trailing 5-year worst drawdown bottomed. | |
drawdown_months_5y | SMALLINT UNSIGNED | may be blank | Months from that peak to that valley in the trailing 5-year window. | |
investor_return_1y | DECIMAL(12,6) | may be blank | Money-weighted (dollar-weighted) annualised return over the trailing 1 year(s): the IRR of the portfolio. | 20.192248 |
investor_return_3y | DECIMAL(12,6) | may be blank | Money-weighted (dollar-weighted) annualised return over the trailing 3 year(s): the IRR of the portfolio. | |
investor_return_5y | DECIMAL(12,6) | may be blank | Money-weighted (dollar-weighted) annualised return over the trailing 5 year(s): the IRR of the portfolio. | |
investor_return_status | VARCHAR(24) | never blank | Why investor_return_1y is blank when it is: no_flows, gap_in_flows, no_net_assets, insufficient_history, no_solution, ok. | no_flows |
stated_benchmark | VARCHAR(255) | may be blank | The benchmark index the newest prospectus names, else the one the sponsor's web disclosure names; stated_benchmark_source says which. Blank where neither names one that reads as an index name. | S&P 500 Index |
stated_benchmark_source | VARCHAR(24) | may be blank | Where stated_benchmark comes from: sec:rr_xbrl for a parsed prospectus, else the web/EMT source stating it for the portfolio's classes (the portfolio analytics file's vocabulary). Blank exactly when stated_benchmark is. | sec:rr_xbrl |
benchmark_proxy | VARCHAR(24) | may be blank | The index key the stated_index rows of the class periods file are measured against, where the stated benchmark resolves to a return series we hold - a name fact, whatever the history depth. Blank where it does not. | US_AGG |
risk_status | VARCHAR(24) | never blank | Why the one-year block is blank when it is, in one value. ok means return_1y and everything a twelve-month window supports are published. | no_monthly_returns |
risk_methodology_version | VARCHAR(24) | never blank | The version of the published methodology the risk-measures rows were computed under. | risk-1.0 |
risk_engine_version | VARCHAR(16) | never blank | The version of the risk-measures engine that computed the row. | risk-1.1 |
category_code | VARCHAR(24) | may be blank | The peer category the rating is ranked inside, from the SQX fund category taxonomy. Blank where the portfolio has no measured category, in which case rating_status says so. | XX-THIN |
cohort | VARCHAR(24) | never blank | The partition of that category the class is compared within. | pooled |
risk_adjusted_return_1y | DECIMAL(12,6) | may be blank | One-year total return less the volatility penalty (gamma/2 x stdev squared / 100), percent; the provisional composite is this alone. | |
risk_adjusted_return_3y | DECIMAL(12,6) | may be blank | Three-year annualised return less the volatility penalty (gamma/2 x stdev squared / 100), percent. | |
risk_adjusted_return_5y | DECIMAL(12,6) | may be blank | Five-year annualised return less the same volatility penalty, percent. | |
composite_score | DECIMAL(12,6) | may be blank | The rated measure: the weighted mean of the risk-adjusted returns over horizons_used, percent. | |
composite_pct_rank | TINYINT UNSIGNED | may be blank | Percentile rank of composite_score inside the cohort, 1 best to 100 worst; blank where the cohort is too small. | |
rating | TINYINT | may be blank | The SQX Fund Rating, 1 to 5: 5 is the best fifth of the cohort by composite_score, 1 the worst fifth. | |
rating_tier | VARCHAR(16) | may be blank | The history behind the composite: provisional (1y alone, 12 to 35 gap-free months) or full (3y or 3y,5y); blank where there is no composite. | provisional |
horizons_used | VARCHAR(16) | may be blank | Which horizons the composite weighs: 3y,5y when both exist, 3y alone for a shorter record, 1y alone for a provisional rating; blank where none. | 1y |
n_portfolios | SMALLINT UNSIGNED | may be blank | Distinct portfolios in the cohort carrying a composite score: the denominator of the rank. | |
rating_status | VARCHAR(24) | never blank | Why the rating is blank when it is, one value in precedence order: unclassified_category, insufficient_history, cohort_below_minimum, provisional, rated. | provisional |
rating_methodology_version | VARCHAR(24) | never blank | The version of the published methodology the rating rows were computed under. | sqx-rating-1.1 |
rating_engine_version | VARCHAR(16) | never blank | The version of the rating engine that computed the row. | rating-1.1 |