Back to Projects
Financial Data PipelineLiveBuilt on n8n

Brokerage Journal Sync

Turning raw brokerage fills into a matched, priced journal without manual entry

Project Snapshot
Brokerage Journal Sync

Overview

A broker reports individual fills, but a journal needs closed positions: entry, exit, fees, result, and holding period on a single row. Reconstructing that by hand means reconciling opens against closes across hundreds of records, which is why most journals are abandoned within weeks. This pipeline does the reconciliation on a schedule instead, pulling the report, parsing it, matching lots, and writing a normalized log that stays correct through partial closes and repeated runs.

Approach & Architecture

The provider generates reports asynchronously, returning a reference code rather than data, so the flow opens with a batch-and-wait retry loop gated on a status check. No XML parsing node was available, so a recursive parser was written by hand to cope with attribute-only elements, repeated records, and self-closing tags. Fills are classified by instrument type with the order action derived from the open-close indicator, then FIFO lot matching pairs each closing fill against the correct open position. Results and holding periods are computed at match time, including for partial closes.

Workflow Topology

REQUESTRETRIEVEPARSEMATCHWRITEnot readyreadyno open lotprior rowsSchedule — Also runnable on demand for a backfillScheduleAsk for Report — The API returns a reference code, not dataAsk for ReportPoll Until Ready — Batch-and-wait loop gated on a status checkPoll Until ReadyBackoff — Bounded retries before the run gives up cleanlyBackoffPull Report — Fetches the generated report once it is readyPull ReportParse XML — Hand-written recursive parser, no library availableParse XMLClassify Fills — Instrument type and derived order action per fillClassify FillsFIFO Matcher — Pairs each closing fill to the correct open lotFIFO MatcherNet + Duration — Gross, fee-adjusted net, weighted exit, holding daysNet + DurationPartial Closes — Tracks closed against original size across runsPartial ClosesIdempotent Write — Keyed on record ID so re-runs never duplicate a rowIdempotent WriteRead Existing — The log itself is the state store; the workflow is statelessRead ExistingFlag Unmatched — Surfaced in place rather than silently droppedFlag Unmatched2 workflows · 18 nodes · Schedule, Manualn8n

A condensed view of the real node graph.

Outcome & Impact

A trade log that stays current with no manual entry. A scheduled run reduces a batch of raw fills to matched, priced rows in about eighty seconds, work that otherwise means reconciling opens against closes by hand. Because writes are keyed on record ID and the matcher is quantity-aware, the job can be re-run or backfilled safely, and partially closed positions stay accurate between runs.

Challenges & Solutions

  • Handling a provider that generates reports asynchronously and returns a reference code instead of data, which required a batch-and-wait retry loop gated on a status check
  • Writing a recursive XML-to-JSON parser by hand because no parsing node or library was available in the sandboxed runtime
  • Staying idempotent without a database, by treating the output log itself as the state store and re-reading it on every run to deduplicate
  • Computing partial-fill accounting correctly, recomputing weighted-average exit and running totals each time another tranche of the same position closes

Key Features

  • Automates the two-step report handshake, requesting a statement and polling for its reference code before fetching
  • Dependency-free XML parser written by hand, handling attributes, nested elements, self-closing tags, and repeated records
  • Normalizes raw fills into a fixed trade schema with instrument classification and derived order actions
  • FIFO lot matching pairs each closing fill to the correct open position, including opens arriving in the same batch
  • Computes gross and fee-adjusted results, weighted-average exit, and holding period at match time
  • Handles partial closes by tracking closed quantity against original size, stamping a close date only once fully unwound
  • Idempotent upsert keyed on record ID, so re-runs and backfills never duplicate a row

Tech Stack

n8nJavaScriptXMLGoogle Sheets APIOAuth 2.0Docker

Automation

Platform
n8n
Workflows
2
Nodes
18
Triggers
Schedule, Manual
Report Sync
Requests a report, polls until it is ready, parses it, matches lots, and upserts the log
Secondary Sync
Mirrors matched rows into a second destination sheet on the same schedule

Tags

AutomationData PipelineXML ParsingIdempotencyReconciliationREST APIs

Status

Live
Back to all projects