Data Ingress: The Import System
How data gets into Portfolio — the XLSX import pipeline, what can go wrong, and how to avoid common pitfalls.
Three Doors In
Data enters Portfolio through three channels. Understanding which to use (and which you can't use for certain data types) is the first thing to get right:
| Channel | Format | Who Uses It | Coverage |
|---|---|---|---|
| XLSX Bulk Import | .xlsx file upload | Fund managers via UI | Full — all 18 entity/transaction types |
| REST API (UI Forms) | JSON via form submission | Fund managers via form submission | Full — one transaction at a time |
| Machine API | JSON from Delio Core | System-to-system automation | Limited — ~7 of 17 types only |
The Machine API only supports: investor create/update, subscriptions, investments, capital calls, and asset show. It cannot create distributions, fees, fund cash movements, asset transfers, valuations, or adjustments. For those, you need the XLSX import or the UI.
This means fully automated onboarding (Delio Core pushing all data into Portfolio) is not currently possible. Some data must be manually imported.
The XLSX bulk import is by far the most important channel for client onboarding. The rest of this module focuses on getting it right.
The XLSX Import Pipeline
Here's what happens when you upload a file, step by step:
Internal user uploads an .xlsx file (max 36 MB) via the Data Import page or POST /api/bulk-import.
The file is stored on S3. A JobAudit record is created with status "queued" and a GUID. The API returns 202 Accepted immediately — processing is async.
The background job reads the XLSX and maps each sheet name to a dedicated importer class. Unknown sheet names are silently skipped.
Each sheet's importer runs prepareForValidation() (column name mapping, type conversion) then rules() (Laravel validation against every row). Validation errors are collected as "Row N. message" strings.
If validation passes, the importer processes rows in a DB transaction — creating domain records through the same Action classes the UI uses. Every created record ID is tracked by ImportTracker.
On success: record IDs saved to JobAudit.metadata, notification sent. On failure: errors formatted, audit marked failed. You can check status via GET /api/bulk-import/{guid}/status.
Completed imports can be reversed via POST /api/bulk-import/{guid}/reverse. This reads the tracked record IDs and deletes everything that was created, then recalculates affected NAVs. You can't reverse an in-progress or already-reversed import.
The 18 Sheet Types
An XLSX workbook can contain any combination of these sheets. The sheet name must match exactly (case-sensitive):
Blue border = commonly used in initial onboarding Grey border = used as needed
You can also download pre-formatted templates from the UI's Data Import page (10 templates available).
Sheet Ordering Matters
Sheets are processed in the order they appear in the workbook. If a sheet references data that hasn't been created yet, it fails.
The required processing order:
The Capital Call Groups sheet populates an in-memory cache (BulkImportCache) that maps group IDs to investor lists. The Capital Calls sheet reads from this cache to know which investors to include in each call.
If Capital Calls appears before Capital Call Groups in your workbook, the cache is empty and every row fails. The same applies to Capital Repayment Groups → Capital Repayments.
Distributions require the investor to already hold units in the asset. Units are created by subscriptions (or investments). If you import distributions before subscriptions, the validation check for "investor holds units" will fail.
If a child asset references a parent via client_reference, the parent must already exist. Within the same Assets sheet, there's a retry loop that handles out-of-order rows. But if the parent is in a different XLSX file, it must have been imported first.
The "Shared" Sheet: Power User Mode
The Shared sheet lets you mix different transaction types in a single sheet. It's the most flexible — and most dangerous — import path.
Each row has a transaction_type column that routes it to the right importer. The Shared sheet handles sorting automatically:
| Priority | Transaction Type | Why This Order |
|---|---|---|
| 0 (first) | Subscription | Creates units that everything else depends on |
| 1 | Transfer | Must process before valuations to get ownership right |
| 2 | Valuation / Valuation-Replace | Sets NAV before distributions reference it |
| 3 | Other types | Default priority |
| 4 (last) | Distribution | Requires units and valuations to exist |
Within each priority, rows are sorted by settlement date ascending.
Unlike dedicated sheets (which either fully commit or fully roll back per-sheet), the Shared sheet has saga compensation. If row 50 fails, it automatically reverses rows 1-49 in reverse order — undoing transfers, deleting transactions, removing valuations, and recalculating NAVs. This is the only sheet with this protection.
Validation Rules: What Gets Checked
Every row passes through Laravel validation before being processed. Here are the most common reasons imports fail:
- Investor
client_referencedoesn't match any existing investor - Asset
client_referencedoesn't match any existing asset - Referenced parent asset doesn't exist
- Group ID not found in the BulkImportCache (groups sheet missing or in wrong order)
All exists rules include deleted_at IS NULL — a soft-deleted investor/asset will fail even though the record exists in the DB.
- Investor
client_referencealready exists (case-insensitive uniqueness viaLowerUniquerule) - Asset
client_referencealready exists - Valuation already exists for the same asset + date + investor combination (use "Valuations - Replace" sheet instead)
- Cash pool name already exists for the asset
- Asset doesn't have subscriptions/distributions enabled (toggle check)
- Investor doesn't hold units for the asset at the specified date (distributions, valuations)
- Asset is not configured for manual valuation (valuation import)
- Non-pro-rata fund asset requires an investor to be specified
- Currency code is not a valid ISO 4217 code
- Monetary values must fit
decimal(28,14)— 28 total digits, 14 after the decimal - Dates must be valid and parseable (Excel date serial numbers are auto-converted)
- Enum values must match exactly (e.g., "drawdown" not "Drawdown")
- Sector/geographic allocation percentages must sum correctly
The Heading Row Trap
Most importers expect the heading row at row 2 (row 1 is typically a title/description row in the templates). But some importers override this:
DistributionImport— heading row 2FundTransactionsImport— heading row 2ValuationsReplaceImport— heading row 1
If your error messages say "Row 5" but the problem is on what you think is row 4, the heading row offset is the reason. Always count from the heading row, not the top of the sheet.
Valuations: "all" vs "all - replace"
The investor column in valuation imports has special behavior:
| Value | Sheet | Behavior |
|---|---|---|
investor_ref |
Either | Creates a valuation for that specific investor only |
all |
Valuations | Creates a valuation for every investor who holds units in the asset. Fails if a valuation already exists for that date. |
all - replace |
Valuations | Same as "all" but replaces existing valuations for the same date |
all |
Valuations - Replace | Creates/replaces for all investors (the Replace sheet always replaces) |
The BulkImportCache Gotcha
Both "Capital Call Groups" and "Capital Repayment Groups" use the same importer (InvestorGroupsImport) and the same singleton cache. When the second Groups sheet is processed, it completely replaces the cache contents from the first.
This works correctly because capital calls are always processed before capital repayments in sheet order. But if you ever reorder your sheets, the groups for capital calls will be gone when the capital calls sheet tries to read them.
Practical Import Checklist
- Sheet order matches the dependency chain — Investors → Assets → Subscriptions → Groups → Calls → Valuations → Distributions
- Sheet names match exactly — "Assets" not "assets", "fund_transactions" not "Fund Transactions"
- client_reference values are unique and consistent across sheets
- Parent assets exist before children reference them
- Dates are valid — Excel date serial numbers will be auto-converted
- Currency codes are ISO 4217 — "USD", "GBP", "EUR", not "$" or "US Dollar"
- No soft-deleted records share client_references with your import data
- Template headings preserved — don't rename columns
Downloading Templates
Portfolio provides 10 downloadable XLSX templates. In the UI, go to Data Import and select a category — each shows a download link. Via API:
Available template names: assets-template, assets-direct-template, valuations-template, investors-template, subscriptions-template, capital-calls-template, distributions-template, investments-direct-template, fund-transactions-template, cash-pools-template.
Download all 10 templates and inspect them in Excel. Notice which columns are required vs optional, what the heading row looks like, and whether there's a description row above the headers. This is the best way to understand the expected format before building your own import files.
Monitoring Import Status
After uploading, you can track progress:
| Method | How |
|---|---|
| UI | Data Import page → "Imports" tab shows history with status (Queued, Processing, Processed, Failed) and error details |
| API | GET /api/bulk-import/{guid}/status — poll the GUID returned from upload |
| History | GET /api/bulk-import/history — paginated list, filterable by status |
If an import fails, the error messages will reference specific rows: "Row 7. The investor field is required." Remember the heading row offset when mapping these back to your spreadsheet.
Knowledge Check
Your XLSX has these sheets in order: Investors, Assets, Capital Calls, Capital Call Groups, Subscriptions. What will happen?
A client's import fails with: "Row 12. The asset must have subscriptions/distributions enabled." What's wrong?
SubscriptionsAllowedValidation trait checks the asset's INVESTOR_SUBSCRIPTION_DISTRIBUTION toggle. If it's not set to true, you can't import subscriptions, capital calls, distributions, or cash pool transactions for that asset. This toggle is set during asset creation or via the asset configuration UI.INVESTOR_SUBSCRIPTION_DISTRIBUTION toggle must be enabled on the asset before subscription/distribution-related data can be imported.You need to import 500 distributions for a fund via automation (no human clicking). The Machine API doesn't support distributions. What are your options?
POST /api/bulk-import) accepts programmatic XLSX uploads — you just need to build the file and POST it with Bearer auth. The MCP API also has ImportDistributions among its 8 import tools, accepting JSON arrays. The MCP path is simpler (no XLSX generation) but requires DELIOTENANT/DELIOSECRET auth.ImportDistributions tool can push distributions as JSON arrays. Both bypass the Machine API limitation.What's Next
Now that you know how data gets in, the next module explains what happens with it — the calculation engine that turns raw transactions into the metrics investors care about: DPI, TVPI, RVPI, and XIRR.