How to combine broker CSV files for portfolio analysis
Combine broker exports by separating transaction, cash, position, and valuation grains; mapping account and instrument identifiers; and standardizing dates, currency, quantity, price, and fees. Reconcile each account to its broker statement before calculating combined holdings or performance.
The essential checks
- Keep trades, cash movements, positions, income, fees, and valuations as related but distinct tables.
- Use stable identifiers and exchange context, retaining symbol history and corporate actions.
- Align trade signs, quantity, price, fees, currency, trade date, and settlement date.
- Prove cash and positions by broker before producing a consolidated view.
Where errors enter the process
One broker exports signed quantities, another uses buy/sell labels, and a third mixes cash movements with trades. Symbols change after corporate actions and can collide across exchanges.
Evidence worth tracing
- The same ticker refers to different exchanges or share classes.
- Cash and position totals disagree after appending transactions.
- Transfers between brokers appear as withdrawals and deposits that distort performance.
Sign-off questions
- Grains separated
- Instruments mapped
- Trade signs made consistent
- Transfers linked
- Cash and positions reconciled
From the field · Portfolio manager
Consolidate two brokers after an account transfer
A portfolio moves holdings from Broker A to Broker B. Both exports record transfer events, one ticker changed after a merger, and fees are embedded in one trade file but separate in the other.
Columns used in the example
account_id · instrument_id · symbol · exchange · transaction_type · trade_date · quantity · price · fee · currency
Follow it with your own file
- Create canonical transaction types and instrument identifiers.
- Preserve transfer-out and transfer-in links without treating them as investment return.
- make consistent trade signs and fee treatment for each source.
- Reconstruct positions and cash, then compare with broker ending statements.
Check: For every account and instrument, opening position plus transactions must equal closing position after corporate actions. Cash and fee totals must reconcile before portfolio aggregation.
A method you can follow
- Separate source grains: Keep trades, cash movements, positions, income, fees, and valuations as related but distinct tables.
- Map instruments: Use stable identifiers and exchange context, retaining symbol history and corporate actions.
- make consistent economics: Align trade signs, quantity, price, fees, currency, trade date, and settlement date.
- Reconcile accounts: Prove cash and positions by broker before producing a consolidated view.
A consolidated portfolio begins with reconciled ledgers
A broker CSV is not a universal portfolio format. Each export reflects source-specific transaction semantics. Mapping those semantics explicitly prevents a clean-looking consolidated table from misclassifying transfers, income, or corporate actions.
Where this usually goes wrong
Do not use ticker alone as a permanent instrument key. Exchange, currency, share class, and corporate-action history can make identical or changing symbols ambiguous.
Choose the approach that matches the evidence and repetition
| Approach | When it works | What to verify |
|---|---|---|
| Append trade CSVs | Same broker and stable format | Transaction semantics and fees |
| Portfolio spreadsheet | Small manually reviewed holdings | Corporate actions and recurring imports |
| Canonical investment ledger | Multiple brokers and periods | Instrument mapping and reconciliation |
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Combine multiple broker CSV exports into a reconciled portfolio dataset. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Data reconciliation · Version history · Verified analysis · Report generation
A traceable consolidated portfolio dataset whose positions, cash, fees, income, and transactions agree with each broker before performance is calculated.
Questions readers usually ask next
Can I combine broker files by ticker?
Ticker alone is risky. Include exchange or a stable instrument identifier and maintain symbol history for corporate actions.
Do broker transfers count as investment returns?
No. Link transfer-out and transfer-in records as external movements so they do not create artificial gains or losses.