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	Self-hosted	Tingsong Xu
Betterment	CSV	Self-hosted	Kewei Yan
Fidelity	CSV	Vendor-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	Self-hosted	Kewei Yan
Pacific Life	CSV	Self-hosted	Winston Li
Pershing	Excel (.xlsx)	Vendor-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	Self-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	MM/DD/YYYY	12/31/2024
Pershing	YYYY-MM-DD	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	Empty string ""
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: CSV Encoding: UTF-8 Date Format: MM/DD/YYYY

Required Columns:

Column Name	Description	Example
Account Number	Unique account ID	123456789
Account Name	Display name	John’s IRA
Account Type	Account classification	ROTH IRA
Total Value	Current balance	$45,678.90
As of Date	Data date	12/31/2024

Holdings Columns (if present):

Column Name	Description
Symbol	Ticker symbol
Quantity	Number of shares
Price	Price per share
Value	Total position value

Common Issues:

• Multi-sheet files need consolidation
• Some exports include summary rows at bottom

---

Pershing

File Type: Excel (.xlsx) Encoding: UTF-8 Date Format: YYYY-MM-DD

Required Columns:

Column Name	Description	Example
ACCT_NBR	Account number	ABC123
ACCT_NAME	Account name	Smith Family Trust
ACCT_TYPE_CD	Type code	IND, JT, IRA
MKT_VAL	Market value	123456.78
POS_DT	Position date	2024-12-31

Holdings Sheet:

Column Name	Description
CUSIP	Security identifier
SEC_DESC	Security description
QTY	Quantity held
MKT_PRC	Market price
MKT_VAL	Market value

Common Issues:

• Multiple sheets (Accounts, Holdings, Transactions)
• Header row may not be row 1
• Numeric fields sometimes formatted as text

---

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
$parser = new \App\Integrations\Fidelity\Parser();
$rows = $parser->parse('/tmp/test-file.csv');
dd($rows[0]); // View first parsed row

Related Documentation

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