File-based Vendor Specifics

This document covers format specifications, common issues, and troubleshooting information for file-based integrations.

All 19 File-based Integrations

Vendor	File Format	Host Type	Developer
Allianz	CSV/Excel	Self-hosted	Yan Hu
Altruist	CSV	Self-hosted	Yan Hu
Apex	CSV	Vendor-hosted	Tingsong Xu
Betterment	CSV	Self-hosted	Kewei Yan
Fidelity	Fixed-width (NAM, POS, TAX)	Self-hosted	Qianwei Hao
First Clearing	CSV	Vendor-hosted	Qianwei Hao
Flourish	CSV	Self-hosted	Tingsong Xu
Folio Investing	CSV	Self-hosted	Qianwei Hao
Interactive Brokers	CSV	Self-hosted	Winston Li
Jackson	CSV/Excel	Self-hosted	Winston Li
LPL	CSV	Vendor-hosted	Qianwei Hao
My529	CSV	Vendor-hosted	Kewei Yan
Pacific Life	CSV	Self-hosted	Winston Li
Pershing	Fixed-width (ACCT, GCUS, ECMB, FUND, ISCA)	Self-hosted	Kewei Yan
Raymond James	CSV	Self-hosted	Winston Li
RBC	CSV	Self-hosted	Yan Hu
Schwab (File)	CSV	Self-hosted	Yan Hu
SEI	CSV	Self-hosted	Winston Li
Trust America	CSV	Vendor-hosted	Kewei Yan

Common File Format Issues

1. Encoding Problems

Issue	Symptom	Solution
UTF-8 BOM	Extra characters at file start	Strip BOM before parsing
Latin-1 / ISO-8859-1	Special characters corrupted	Convert to UTF-8
Windows-1252	Smart quotes broken	Convert to UTF-8

// Handle encoding detection and conversion
$content = file_get_contents($filePath);

// Detect encoding
$encoding = mb_detect_encoding($content, ['UTF-8', 'ISO-8859-1', 'Windows-1252']);

// Convert to UTF-8
if ($encoding !== 'UTF-8') {
    $content = mb_convert_encoding($content, 'UTF-8', $encoding);
}

// Strip BOM if present
$content = preg_replace('/^\xEF\xBB\xBF/', '', $content);

2. Date Format Variations

Vendor	Date Format	Example
Fidelity	MMDDYYYY (no separator, in POS header offset 62)	12312024
Pershing	YYYY-MM-DD (in fixed-width files)	2024-12-31
Schwab	M/D/YYYY	1/5/2024
LPL	DD-MMM-YYYY	31-Dec-2024

// Flexible date parsing
private function parseDate(string $value): ?Carbon
{
    $formats = [
        'm/d/Y',      // 12/31/2024
        'n/j/Y',      // 1/5/2024
        'Y-m-d',      // 2024-12-31
        'd-M-Y',      // 31-Dec-2024
        'm-d-Y',      // 12-31-2024
    ];

    foreach ($formats as $format) {
        try {
            return Carbon::createFromFormat($format, trim($value));
        } catch (Exception) {
            continue;
        }
    }

    return null;
}

3. Amount/Currency Formatting

Issue	Example	Clean Value
Currency symbol	$1,234.56	1234.56
Thousands separator	1,234,567.89	1234567.89
Parentheses for negative	(1,234.56)	-1234.56
Space separator	1 234 567.89	1234567.89

private function parseAmount(string $value): float
{
    $cleaned = $value;

    // Handle parentheses for negative
    if (preg_match('/^\((.*)\)$/', $cleaned, $matches)) {
        $cleaned = '-' . $matches[1];
    }

    // Remove currency symbols and separators
    $cleaned = preg_replace('/[^0-9.\-]/', '', $cleaned);

    return (float) $cleaned;
}

4. Null/Empty Value Handling

Vendor	Empty Representation
Fidelity	Space-padded fixed-width fields; closed accounts marked by & prefix in NAM short name
Pershing	NULL string
Schwab	N/A string
LPL	- dash

private function normalizeNull(mixed $value): mixed
{
    if (is_string($value)) {
        $normalized = strtoupper(trim($value));

        if (in_array($normalized, ['', 'NULL', 'N/A', 'NA', '-', '--'])) {
            return null;
        }
    }

    return $value;
}

Major Vendor Format Specifications

Fidelity

File Type: Fixed-width text, delivered as .DAT.ZIP Encoding: UTF-8 Date Format: MMDDYYYY (in POS header offset 62) IBD Code: 570 (DB reference stored as 570:{client_id})

Logical Files:

FileType	Filename Pattern	Purpose
NAM	*NABASE*	Account names, types, owner names
POS	*POSITD*	Positions: CUSIP, quantity, price, market value
TAX	*_TLAOPENDELTA_*	Cost basis (aggregated per CUSIP)

Line Prefixes: H = file header, D = detail row, CH = client header (client_id at offset 3 length 20), CT = client tailer.

Account Reference: BRANCH(3) + ACCOUNT_NUMBER(6) from NAM offset 8 length 9 (e.g., X46629715). Stored masked as BRANCH + XXX + LAST(3).

Common Issues:

• Mismatched CH/CT boundaries throw — usually means truncated upload
• Closed accounts have & prefix on NAM short name and are filtered out
• Fidelity’s own docs incorrectly state date format is MMDDYY; actual is MMDDYYYY

See Fidelity vendor doc for full field offset tables and code locations.

---

Pershing

File Type: Fixed-width text files Encoding: UTF-8 Date Format: YYYY-MM-DD

Logical Files:

File	Purpose
ACCT	Account information (number, name, type, status)
GCUS	Securities positions (CUSIP, quantity, market value)
ECMB	Electronic trading funds — cash balances (new format)
FUND	Money market funds — fund balances (legacy format)
ISCA	Security information (symbol, CUSIP, ISIN, price)

Filtering: Each detail row carries IBD (3-char) and IP (3–4 char) at known offsets per file type. The Reader filters rows by IBD/IP from the integration reference {transmission_id}:{IBD}[:{IP_list}].

Common Issues:

• IBD/IP offsets differ per file type (see vendor doc for the offset table)
• ISCA has no row-level filtering (security catalog applies to all)
• Empty values represented as the literal string NULL

See Pershing vendor doc for IBD/IP offset table, transmission setup SOP, and full account/holding type mappings.

---

Schwab (File)

File Type: CSV Encoding: UTF-8 with BOM Date Format: M/D/YYYY (no leading zeros)

Required Columns:

Column Name	Description	Example
Account Number	Account ID	XXXX-1234
Account Type	Classification	Brokerage
Balance	Total value	50000.00
Date	As of date	1/5/2024

Common Issues:

• BOM at file start needs stripping
• Account numbers may be masked (XXXX-1234)
• Same vendor has API version (Schwab API) - don’t confuse

---

LPL

File Type: CSV Encoding: Windows-1252 Date Format: DD-MMM-YYYY

Required Columns:

Column Name	Description	Example
RepCode	Advisor rep code	ABC123
AccountNumber	Account ID	12345-67890
ClientName	Client name	John Smith
AccountValue	Total value	100,000.00
AsOfDate	Data date	31-Dec-2024

Common Issues:

• Windows-1252 encoding (not UTF-8)
• Large files may need chunked processing
• Rep code filtering required for multi-rep advisors

---

Interactive Brokers

File Type: CSV (Activity Statement export) Encoding: UTF-8 Date Format: YYYY-MM-DD

Structure:

• Multiple sections in single file
• Section headers indicate data type
• Need to parse section by section

Statement,Header,Account,Date
Statement,Data,U1234567,2024-12-31

Positions,Header,Symbol,Quantity,Price,Value
Positions,Data,AAPL,100,150.00,15000.00
Positions,Data,GOOGL,50,140.00,7000.00

Trades,Header,Date,Symbol,Quantity,Price
Trades,Data,2024-12-15,AAPL,10,148.00

Common Issues:

• Multi-section format requires custom parser
• Currency conversion for multi-currency accounts
• Options/futures positions need special handling

---

Raymond James

File Type: CSV Encoding: UTF-8 Date Format: MM/DD/YYYY

Common Issues:

• Advisor hierarchy in export
• Branch code filtering may be needed

---

SEI

File Type: CSV Encoding: UTF-8 Date Format: MM/DD/YYYY

Common Issues:

• Trust account structures
• Multi-custody accounts

Adding a New File-based Integration

Checklist

1. Obtain Sample Files

   • Get at least 3 sample files with real data structure
   • Document file format (CSV/Excel)
   • Document encoding
   • Document date/amount formats

2. Create Directory Structure

   app/Integrations/[NewVendor]/
   ├── Integrator.php
   ├── Parser.php
   ├── Mapper.php
   └── Models/
       ├── Account.php
       └── Holding.php

3. Implement Parser

   • Handle encoding conversion
   • Handle header detection
   • Handle multi-sheet (if Excel)
   • Handle empty/summary rows

4. Implement Mapper

   • Document column mapping
   • Handle date parsing
   • Handle amount parsing
   • Handle null values
   • Map account types

5. Implement Integrator

   • Extend FileBasedIntegrator
   • Wire up Parser and Mapper
   • Implement sync logic

6. Add to IntegrationType Enum

   • Add new enum case
   • Implement isFileBased() return true
   • Set getFileBasedHostType()

7. Testing

   • Unit test Parser with sample files
   • Unit test Mapper with edge cases
   • Integration test full sync flow
   • Test with malformed/edge case files

8. Documentation

   • Add to this vendor-specifics document
   • Update architecture-overview vendor list
   • Add developer ownership

Template: New Vendor Integrator

<?php

namespace App\Integrations\NewVendor;

use App\Integrations\Support\FileBasedIntegrator;
use RightCapital\Core\Enums\Integration\IntegrationType;

class Integrator extends FileBasedIntegrator
{
    protected function getParser(): Parser
    {
        return new Parser();
    }

    protected function getMapper(): Mapper
    {
        return new Mapper();
    }

    protected static function getVendor(): IntegrationType
    {
        return IntegrationType::NEW_VENDOR;
    }
}

Developer Ownership

Developer	Integrations
Qianwei Hao	Fidelity, First Clearing, Folio Investing, LPL
Kewei Yan	Betterment, My529, Pershing, Trust America
Tingsong Xu	Apex, Flourish
Yan Hu	Allianz, Altruist, RBC, Schwab (file)
Winston Li	Interactive Brokers, Jackson, Pacific Life, Raymond James, SEI

Debugging Tips

View Raw File Content

# Check encoding
file -I /path/to/uploaded/file.csv

# View first few lines
head -20 /path/to/uploaded/file.csv

# Check for BOM
hexdump -C /path/to/uploaded/file.csv | head -1

Common Parse Errors

Error	Likely Cause	Solution
”Column X not found”	Wrong column name or encoding	Check actual headers in file
”Invalid date format”	Unexpected date format	Add format to parser
”Number format exception”	Non-numeric characters	Improve amount cleaning
”File too large”	Exceeds memory limit	Implement chunked reading

Test File Locally

// Quick test in tinker (current Reader/Extractor pattern)
use RightCapital\Integrations\FileBased\Fidelity\{Extractor, FileType, Reader};

$reader    = Reader::create('570:CLIENT_ID', FileType::NAM);
$extractor = new Extractor($reader);
dd($extractor->getEntitiesByReference());

Note: legacy file-based integrations used App\Integrations\<Vendor>\Parser. New code lives in the RightCapital\Integrations\FileBased\<Vendor> package and uses the Reader + Extractor + Integrator pattern. See patterns doc.

Related Documentation

• Patterns - Parser and Mapper design
• Architecture Overview - System overview
• API-based Vendor Specifics - Compare with API vendors