# Cargo Arrival Schedule Analysis > Analyzes the LA LNG ISBL Critical Cargo / Planning Schedule workbooks in the "Logistics Barge Schedule" Teams channel — arrivals by port (HOU, LCH), MOF barge arrivals, road and domestic barging, and vendor voyages — and builds the readable consolidated workbook from one. Use when the user asks to "analyze the barge schedule", "what's the latest cargo arrival schedule", "what changed between SF28 and SF29", "what arrives at the MOF next month", "which shipments are TBC or delayed", "build the consolidated view", or references an "SFxx Planning Schedule" file. Do NOT use for the line-item vendor equipment table (use vendor-equipment-consolidation), for unrelated spreadsheets (use the xlsx skill), for scheduling meetings, or for other Teams channels. - Skill: `dgusoff/cargo-arrival-schedule-analysis` (Agent Skill, multi-file: 6 files) - Install (CLI): `npx skillmds@latest add dgusoff/cargo-arrival-schedule-analysis` - Raw SKILL.md: https://api.skillmd.com/api/skills/dgusoff/cargo-arrival-schedule-analysis/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: dgusoff (https://skillmd.com/u/dgusoff) - Updated: 2026-09-22 - Page: https://skillmd.com/skills/dgusoff/cargo-arrival-schedule-analysis --- # Critical Cargo Arrival Schedule Analysis Analyzes the weekly LA LNG ISBL critical cargo planning schedule workbooks that the logistics team posts to Teams, and answers questions about vessel/barge arrivals, vendor voyages, delays and TBC items. ## When NOT to Use - General spreadsheet creation or editing unrelated to this schedule → `xlsx` skill - Building a deck or document about the project → `pptx` / `docx` - Scheduling or moving meetings → `schedule-meeting` - Any other Teams channel or SharePoint library ## Where the data lives - Team: **LALNG ISBL Critical Cargo Arrival Schedule** - Channel: **Logistics Barge Schedule** - Files live in the channel's SharePoint document library, named `SF{n}[.{rev}] {DD Mon} Planning Schedule.xlsx` (e.g. `SF28.2 24 Aug Planning Schedule.xlsx`). The `SF` number is the weekly sequence; a `.1` / `.2` suffix is a revision of that week. ## Source hierarchy — READ THIS BEFORE ANSWERING ANY DATE QUESTION **The component tabs are the source of truth. The CONSOLIDATED tab (the "skyline") is a summary view derived from them.** Where they disagree, the component tabs govern. - Source of truth: **Port of HOU**, **Port of LCH**, **MOF ARRIVAL**, **via ROAD**, **Domestic Barging** - Line-item detail: the **13 vendor tabs** — weight, dimensions, Supplier, Port of Loading, RTS/departure/ETA/forecast/preferred dates, transport drawings. See the cargo section below. - Summary only: **CONSOLIDATED {date}** — a month-column wall chart. It lags: shipments are routinely added to the component tabs and not plotted on it (in SF29, 8 of 78). - Never answer a date question from the skyline alone, and never let a skyline date override a component date. A shipment that exists ONLY on the skyline is worth flagging to the user — it is either awaiting component-tab entry or is a stale row. ## Known authorship quirks (recur weekly — always check) These workbooks are hand-maintained. All three have been observed in real files: 1. **Stale in-sheet date.** The file is rolled forward from the prior week without updating the header cell (SF27: tab and header both said 10 Aug; SF29: tab renamed but header still said 24 Aug). `schedule_tools.py` flags this. **Cite the filename date; say the header is stale.** 2. **Mistyped years — the most common error, in two places.** Within the component tabs (a MOF arrival a year before its own port call), and on the skyline independently of them. The skyline is maintained BY HAND in parallel with the component tabs, not generated from them, so it picks up its own typos: SF29's Powell Electrical 01 reads 29 Sept 27 on the skyline against 29 Sept 26 on the MOF tab — correct day and month, wrong year, introduced the same week both were updated. Baker Hughes 01's skyline year error has persisted for weeks. Do not describe a skyline/component disagreement as the summary "lagging" without checking the prior week's file: a lagging value shows the OLD date, a typo shows the NEW date with a wrong year. 3. **Legacy layouts.** Files before ~Feb 2026 (e.g. `SF2. 28 Jan`) use an order-line table with no CONSOLIDATED tab. `build_consolidated.py` refuses these; do not force them. ## Workflow 1. **Locate the library.** `m365_teams-ListTeams` → `m365_teams-ListChannels` for the team, then `graph-QueryGraph` on `/teams/{teamId}/channels/{channelId}/filesFolder` to get the `driveId` and folder `id`. 2. **List the files.** `sharepoint_onedrive-GetDriveChildren` with that drive and folder id (`top` at least 50). Sort by `Modified` to find the newest, but confirm against the filename sequence — a re-saved older `SFxx` can outrank a newer one by timestamp, so when they disagree, prefer the **highest SF number and revision suffix** and say which you used. 3. **Download and parse.** `sharepoint_onedrive-ReadFileContent` for the chosen item, then run the bundled parser — do not hand-read cells or eyeball dates: ```bash python scripts/schedule_tools.py summary "" python scripts/schedule_tools.py arrivals "" --from 2026-10-01 --to 2026-12-31 \ [--tab "MOF ARRIVAL"] [--vendor "Baker Hughes"] python scripts/schedule_tools.py diff "" "" ``` All three print JSON, each with a **`warnings` array — always read it first and repeat any warning to the user** (see "Known authorship quirks" above). `summary` returns the schedule date, tab list, shipment counts and the NOTES block; `arrivals` returns filtered, date-sorted shipments with tags and source tabs; `diff` returns added / removed / date-moved shipments with day deltas. Fall back to reading the workbook with `openpyxl` (`data_only=True`) only for something the parser does not cover. 4. **Answer from the right tab** (typical layout, ~19 sheets): - `CONSOLIDATED {date}` — master "Ship Arrival Schedule – All Ports" timeline, month columns across ~16-17 months plus a **TBC** column, and a NOTES block at the bottom - `Port of HOU`, `Port of LCH` — arrivals by discharge port - `MOF ARRIVAL` — barge arrivals at the jobsite MOF - `via ROAD`, `Domestic Barging` — inland movements - Vendor tabs with voyage counts: Alfa Laval, Bumhan + Hantech, Baker Hughes, CryEng, Industeel, L&T, Peerless, Petrochem, Siemens, SNT, SVT, Verhoef, Zeeco Cells are multi-line: `VENDOR NN \n \n `. Split on newlines to separate shipment name, equipment tags and arrival date. 5. **Always read the NOTES block** on the consolidated tab — it carries the delay and TBC caveats (e.g. dates stated as arrived MOF-jobsite, load types TBC, vendor delays) and changes week to week. 6. **Comparing revisions:** download both workbooks and use `schedule_tools.py diff`. Report the moves it returns; never compute a day delta by eye. A shipment whose `from` or `to` is null gained or lost a stated date — call that out rather than treating it as a slip. 7. **Report** inline in chat by default: headline (file used + schedule date), then arrivals in the requested window, then open items (TBC, delays, unresolved dates). Build the workbook below only when the user asks for a file. ## Building the consolidated workbook When the user asks for the readable/consolidated view as a file: 1. Run `arrivals` to JSON, then aggregate one row per shipment. **Pass the workbook as the third argument** — without it the cargo, vendor and preferred-date columns come out empty: ```bash python scripts/schedule_tools.py arrivals "" > working/arr.json python scripts/build_consolidated.py working/arr.json working/consolidated.csv "" ``` It prints `rows`, `flagged`, `warnings`, `qty_corrupted`, `supplier_resolved`. It **refuses** a legacy layout rather than emitting an empty-milestone table — report the refusal, do not pass `--force`. 2. Create the workbook on the live surface — `CreateArtifact(surface="live", path="LA LNG Critical Cargo - Consolidated View ().xlsx")` — then `insert_tabular_data` the CSV at `A2` and write the title into `A1`. 3. **Sheet 1 `Consolidated Schedule`** — 20 columns in this order, grouped by header colour: | # | Column | Header fill | |---|---|---| | A-E | `Arrival` · `Shipment` · `Vendor` · `Port of Loading` · `Equipment Tags` | `374151` | | F-K | `Items` · `Total wt (t)` · `Heaviest piece (t)` · `L (m)` · `W (m)` · `H (m)` | teal `0F766E` | | L-N | `Port` · `MOF` · `Road` | blue `2563EB` (source of truth) | | O-P | `Preferred at jobsite` · `Days vs preferred` | rust `B7472A` | | Q-S | `Status` · `Data quality` · `Vendor source` | `374151` | | T | `Summary tab (QA)` | grey `9CA3AF` | - `Arrival` is the readable label `03 Jan 2026 (MOF)` — one column, not a date plus a separate label. It names which leg the date belongs to; component tabs govern, so `(Ship)` appears only when no component tab carries a date. - Header colour IS the source cue: blue = source of truth, grey = summary tab, teal = cargo data from the vendor tabs, rust = construction need. `Summary tab (QA)` sits LAST, in grey italic — it is a cross-check field, never a milestone. - Highlight the `O:P` pair rust on rows arriving late; leave the rest of the row to its status colour so the two signals do not compete. - Title row merged across all columns, dark navy (`1F2A5C`); header band `374151`; freeze at `C3`. Never truncate `Equipment Tags` — full text wraps at ~200 chars. - **The title banner must not describe columns.** Keep it to three things: what the sheet is, the source filename plus any stale-header warning, and a pointer to the notes tab. A banner that names a column goes stale the moment the column is renamed and then actively misleads (a "Ship column is the summary tab" note survived the rename to `Summary tab (QA)`). Source-layer meaning belongs in the header colours and the notes tab, which cannot drift. 4. **Sheet 2 `Source & Notes`** — source file + location, a callout if the in-sheet header is stale, live `COUNTA`/`COUNTIF` totals (shipments, needing review, undated, data-quality notes, late vs preferred), the source-hierarchy statement, the flag legend with colours, week-over-week changes, all NOTES rows copied verbatim, column definitions, and the coverage limits below. Never hardcode a count — use formulas. 5. Verify with `GetArtifactModel`, then tell the user the flag counts by category. ### Cargo, vendor and need-date columns (from the vendor detail tabs) The 13 vendor tabs are the line-item source — ~821 rows with `Voyage · Order # · MR# · Supplier · Tag ID · Material Description · Port of Loading · QTY · Length · Width · Height · Total Weight · RTS Date · Updated Ship DEPARTURE · Updated ETA JOBSITE · FORECAST DELIVERY DATE -ETA · Preferred Arrival JOBSITE · Transit Time · HH/HL · Transport Drawing #`. `build_consolidated.py` reads them by matching Equipment Tags to `Tag ID`. - **Vendor comes from the `Supplier` column**, never a string-split of the shipment name. ~50 of 78 shipments resolve; the rest have no vendor tab and fall back to the label, with `Vendor source` stating which applies per row. **Do NOT add a loose prefix-match fallback** — it resolved Verhoef to SVT on a partial tag. An unresolved vendor stated as such beats a wrong one. - **Two weight figures, deliberately separate.** `Total wt` sums distinct tags (what the barge carries); `Heaviest piece` is the largest single item (what the crane picks). L/W/H are the max of each dimension across the shipment, so they may come from different pieces — the largest envelope to handle. Never report a total alone as if it were a lift weight. - **`Days vs preferred` is jobsite-to-jobsite.** Compare `Preferred Arrival JOBSITE` against the **MOF** arrival (or Road where there is no MOF leg) — NEVER against the port call, which would understate every variance by the barge leg. Positive = arrives after construction wants it. - Where a shipment's items carry different preferred dates, take the EARLIEST (binding constraint) and note it under `Data quality`. ### Coverage limits — a blank is not a clean bill State these whenever the columns are used; they are source gaps, not parser failures. | Column group | Coverage in SF29 | A blank means | |---|---|---| | Cargo (weight/dims) | 47 of 78 shipments | no vendor detail tab for this cargo | | `Preferred at jobsite` | **9 of 78** shipments; populated on only 4 of 13 tabs (SNT 74 items, Bumhan+Hantech 41, Peerless 8, CryEng 2 — 125 of 821) | **not recorded — NEVER "on time"** | | `FORECAST DELIVERY DATE -ETA` | 106 of 821 items, absent from 4 tabs | not forecast; not currently surfaced in the view | In SF29, 8 of the 9 measurable shipments arrive late vs preferred — SNT 12 by +326 days, Bumhan 06 by +163, and every measurable Korea shipment. With 69 shipments unmeasurable, treat the absence of a variance as a blind spot worth raising upstream, not as good news. ### Data-quality checks (the `Data quality` column) Beyond date errors, `build_consolidated.py` reports source problems per row: - **QTY corrupted to a date** — an Excel number that got date-formatted, so the real quantity is lost (SF29: `1V-1312A`, `1V-1322A`, `1V-1322C` on the L&T tab read `1900-01-01`). - **Tab name vs Supplier mismatch** — e.g. the `Verhoef x 1` tab's Supplier column reads `SVT`, sharing Order # 132406 / MR# MHLM-00001 with the `SVT x 1` tab. Abbreviations are tolerated by an initials check, so `L&T x 6` vs `LARSEN AND TOUBRO` is not reported. - **Split preferred dates** within one shipment. ### Flag taxonomy (produced by `build_consolidated.py`) | Status | Colour | Meaning | |---|---|---| | `Conflict between component tabs` | red `FFE0DC` | Two source-of-truth tabs disagree >120 days. A real error — fix at source. | | `Summary tab disagrees - check year` | purple `E7E0F5` | Component tabs agree; the skyline does not. NOT a lagging summary — observed cases are the skyline carrying its own year typo with the correct day and month, sometimes persisting for weeks. Component dates govern. | | `Check year - out of voyage sequence` | amber `FFF4CE` | Voyage arrives out of order vs the same vendor's other voyages. Likely year typo; needs a human. | | `Date not stated (TBC)` | pale blue `EEF1F8` | No date in the source. Never inferred. | ### Live-surface constraints (learned the hard way) - `add_conditional_format` and `add_table` are **not available** on the live surface. Emit banding and flag colours as explicit per-row `format_range` patches instead. - Batch roughly 20-25 patches per `EditArtifact` call; a failed batch does not roll back on live, so re-read the model before retrying. - Flag counts to date: SF23 4, SF26 4, SF27 3, SF28 4, SF28.2 5, SF29 6. Files before ~July show 0 because the component tabs were not yet separately maintained — that is correct, not a bug. ## Output format ``` Source: (schedule dated , updated ) Arrivals — -
— — — Open items - Notes from the sheet - ``` ## Guardrails - Report only values read from the workbook. If a date is blank or `TBC`, say so — never infer, interpolate or "reasonably estimate" an arrival date. The parser returns `date: null` with the raw `date_text` preserved for exactly these cases; surface them as unknown. - The parser is read-only and never writes to the workbook. If it errors or returns zero shipments, the layout has changed — inspect the sheet with `openpyxl` and say what you found, rather than reporting an empty schedule as fact. - Name the exact source filename and its schedule date in every answer; multiple revisions of the same week exist and answering from the wrong one is the main failure mode here. - Do not modify or re-upload the source workbook unless the user explicitly asks. - If the file cannot be opened, say so plainly and ask for it to be re-shared — never substitute sample or illustrative shipment data. - Do not evaluate any individual's performance from schedule slippage; report shipment status only. - Component tabs beat the summary tab, always. Never resolve a disagreement by picking the more convenient date, and never silently blend the two — say which tab each date came from. - Never present a flagged row as settled fact. Report the flag and its category alongside the date.