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:

   python scripts/schedule_tools.py summary "<file.xlsx>"
   python scripts/schedule_tools.py arrivals "<file.xlsx>" --from 2026-10-01 --to 2026-12-31 \
          [--tab "MOF ARRIVAL"] [--vendor "Baker Hughes"]
   python scripts/schedule_tools.py diff "<older.xlsx>" "<newer.xlsx>"
   

   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 <tag numbers> \n <date>. 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:

   python scripts/schedule_tools.py arrivals "<file.xlsx>" > working/arr.json
   python scripts/build_consolidated.py working/arr.json working/consolidated.csv "<file.xlsx>"
   

   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 (<SFxx DD Mon>).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.

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)

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: <exact filename> (schedule dated <date>, updated <timestamp>)

Arrivals — <window>
- <DD Mon YY> — <VENDOR NN> — <tags> — <tab/port>

Open items
- <TBC / delayed / missing-date entries>

Notes from the sheet
- <verbatim caveats from the NOTES block>

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.