Morningstar Security Master

Morningstar is RightCapital’s primary source for Security Master data — the reference database for all securities (equities, funds, ETFs) including prices, classifications, and identifiers.

Repository: gitlab.rightcapital.io/integrations/morningstar Tech Stack: Laravel 12 (PHP 8.4), MySQL (dual-database), AWS S3, FTP Owners: Yan Hu, Tingsong Xu

What Data Does It Provide?

Category	Examples
Identifiers	Morningstar ID, Performance ID, CUSIP (partial), ISIN, Ticker
Basic Info	Name, type (equity/fund), company, exchange
Pricing	Daily close prices
Classifications	Sector, industry, category, holding style
Fund Details	Expense ratio, allocations (asset type, bond quality, equity style, market, sector)
Portfolios	Top equity holdings, bond holdings

CUSIP Coverage

Morningstar removed CUSIP from data files in 2018. The system partially reconstructs CUSIP via:

• A 2018-07-31 historical snapshot (resources/cusip.csv, ~52K records)
• ISIN derivation (extracting middle 9 chars for US/CA securities)
• Fund DataWarehouse files (CUSIP/ISIN fields not fully removed)

Post-2018 securities have ~2-3% CUSIP coverage.

Architecture

Dual-Database Design

The service operates across two MySQL databases:

Database	Purpose	Key Tables
morningstar (default)	Raw extracted data from Morningstar files	securities, companies, funds, prices, files, imports
api	Processed data in RightCapital core-model schema	securities, equities, funds, allocations_by_*, fund_*_portfolios

The api database uses the rightcapital/core-models package schema — the same tables that retail-api reads from.

Data Pipeline

flowchart TD
    FTP["Morningstar FTP Server<br/><code>ftp.morningstar.com</code>"]
    S3["AWS S3 Bucket"]
    MDB["Morningstar DB<br/>(raw data)"]
    ADB["API DB<br/>(core-models)"]
    RAPI["Retail API"]

    FTP -->|"<b>retrieve</b><br/>hourly, prod only<br/>ZIP/GZ files"| S3
    S3 -->|"<b>data:import</b><br/>download & extract"| MDB
    MDB -->|"<b>data:transform</b><br/>parse allocations,<br/>portfolios, styles"| MDB
    MDB -->|"<b>sync:push</b><br/>copy to core-models"| ADB
    ADB -->|"<b>webhook</b><br/>POST /v2/webhooks/"| RAPI

Pipeline Commands

The full daily pipeline runs via php artisan process (4:15 AM ET, Tue-Sat):

1. File Retrieval (retrieve)

Schedule: Every hour (production only), monitored by Cronitor.

Step	Command	What It Does
Download	file:retrieve	Connects to ftp.morningstar.com (user: ondemandrightcapital). Lists files matching patterns in config/morningstar.php. Validates trigger files (.ctrl / _trigger.txt) exist. Uploads ZIP/GZ to S3.
Status	file:update-status	Marks imports table entries from NEW → READY.

2. Data Import (data:import)

Downloads files from S3 to /var/opt/morningstar, extracts archives, and runs extractors.

Data Groups:

Group	Content	Extractors
equity	Stock reference data, company info, prices	SecurityReference, CompanyReference, AssetClassification, Price
fund_FO/FE/FC/FM	Fund data (DataWarehouse XML), prices	DataWarehouse37, AssetClassification30, Price

Each group has daily + monthly file variants. Files are downloaded from specific FTP folders:

• Equity: /Daily/NRA/Reference_v3/, /Daily/NRA/Price/, etc.
• Fund: /Daily/DataWarehouse_v3/, /Modules/daily/Prices/, etc.

3. Data Transform (data:transform)

Processes securities in chunks of 5,000:

1. Runs Transformer on each security
2. Parses allocations across 6 dimensions (asset type, bond quality, category, equity style, market, sector)
3. Maps Morningstar taxonomies to RightCapital’s 14 asset classes
4. Stores result as JSON in securities.derived column
5. Saves prices to morningstar.prices table

Key mapping file: Transformers/Mapping.php — defines:

• 14 categories (Large Growth, Bonds, etc.)
• 9 holding styles (Large Value, Mid Growth, etc.)
• 11 sectors (Technology, Healthcare, etc.)
• Geographic markets and market caps
• Bond quality ratings (AAA through Unrated)

4. Sync Push (sync:push)

Copies processed data from morningstar DB → api DB (core-models):

1. Securities: Insert/update with api_id foreign key mapping
2. Child records: Equities, funds, all allocation types, portfolios
3. Lifecycle: Disables securities without prices (has_price=0), enforces blacklist (e.g., symbol PDRG)
4. Webhook to retail-api: Notifies about security deletions
5. Cache flush: Invalidates Redis keys for prices, security lookups, trashed IDs

Interaction with Retail API

Webhook Endpoints

The service communicates with retail-api via HTTP webhooks:

Endpoint	Purpose	Payload
POST /v2/webhooks/morningstar/security	Notify security deletions	{action: 'delete', security_ids: [...]}
POST /v2/webhooks/cache	Flush Redis cache	{redis: {client: 'Morningstar', models: {security: []}, globs: ['Price:LatestArray:*', 'Security:Lookup:*'], keys: ['Security:TrashedIds']}}

Auth: Basic Auth with user Morningstar, password from env RETAIL_API_KEY. Helper: send_request_to_retail_api(path, body) in app/Support/helpers.php.

Retail API Side

Retail-api receives these webhooks at SecurityController (app/Http/Controllers/Webhooks/Morningstar/SecurityController.php), protected by auth.internal:Morningstar middleware.

On security deletion, retail-api fires a SecurityDeleted event that:

• Detaches positions (copies security metadata to custom_security JSON)
• Deletes target category mix allocations using the security
• Nullifies security references in stock plan accounts

Data Structures

Morningstar DB: securities Table

id                          AUTO_INCREMENT
morningstar_id              CHAR(10), unique  -- e.g., "F00000MLJO"
morningstar_performance_id  CHAR(10), unique  -- e.g., "0P00015GFM"
morningstar_company_id      FK → companies.morningstar_id
symbol                      VARCHAR, indexed
cusip                       CHAR(9), nullable, indexed
isin                        CHAR(12), nullable, indexed
type                        ENUM('equity', 'fund')
api_exchange_id             TINYINT UNSIGNED
name                        VARCHAR, nullable
api_id                      INT, nullable, indexed  -- FK to retail-api securities
api_deleted                 BOOLEAN
has_price                   BOOLEAN
derived                     TEXT (JSON)  -- transformed data for all child models

Security Matching

When vendor integrations sync holdings into retail-api, each holding must be matched to a Security Master record. Matching priority:

1. CUSIP — Most reliable (but limited coverage post-2018)
2. ISIN — International standard
3. Symbol + Name verification — Symbol lookup + name similarity check
4. AI Name Matching (DEV-19528, in progress) — Replaces similar_text with LLM-based name comparison for symbol-matched candidates

AI Security Matching (DEV-19528)

The similar_text name check fails on legitimate matches where vendor names differ from Morningstar names (e.g., “BLACKROCK ETF TRUST II ISHARES FLEXIBLE” vs “iShares Flexible Income Active ETF”). A new AiSecurityNameMatcher uses Claude Sonnet to determine if two names refer to the same security. Results are cached per local security in morningstar_security_id + matched_at fields. ETA: 2026-04-21.

Cron Jobs

Deployed via Kubernetes CronJobs (deploy/cron-jobs.yaml.gotmpl):

Job	Schedule	Environment	Resource
retrieve	0 * * * * (hourly)	Production only	1 CPU
process	15 4 * * 2,3,4,5,6 (4:15 AM ET, Tue-Sat)	All	1 CPU, 1Gi RAM

All jobs monitored via Cronitor with health pings on start/complete/fail.

Configuration

Key Environment Variables

Variable	Purpose
MORNINGSTAR_FTP_PASSWORD	FTP credential for ftp.morningstar.com
DB_* / DB_API_*	Morningstar DB / API DB connection
AWS_BUCKET	S3 bucket for file staging
RETAIL_API_URL	Retail API base URL for webhooks
RETAIL_API_KEY	Basic Auth password for webhook calls
CRONITOR_API_KEY	Monitoring credentials

File Pattern Configuration

config/morningstar.php defines 7 download specs: FTP folders, file name patterns, and trigger file suffixes for equity references, asset classifications, prices, fund data warehouse, and fund prices.

Morningstar Office Sunset (DEV-19366)

Morningstar Office was an OAuth-based integration where advisors synced household/account/holding data directly into retail-api. It was sunset on February 28, 2026.

Data migration was executed in two phases:

• Phase A (completed): Detached 3,778 positions from MO securities, converted 11,571 active accounts to manual (source = NULL), soft-deleted 228 integrations
• Phase B (deferred): Cleanup of MO securities, trashed accounts, and hard-delete of integrations

See Morningstar Office for details.

Related

• Morningstar Office — Data sync integration (sunset 2026/02/28)
• Morningstar Advisor Workstation — Report generation (active)
• Security Matching Enhancement (DEV-19528) — AI name matching proposal
• Notion: Morningstar Home — Team wiki
• Notion: Integration 的组成 — Architecture overview