# Sparrow for Excel — User Guide

*One workbook. Any dataset. Anywhere.*

This guide uses **EnergyScope** — the flagship Sparrow deployment — for
its examples: the ribbon tab, the `ES.*` functions, and the energy-market
datasets all carry that name.

EnergyScope puts energy-market time series directly into Excel formulas.
You type `=ES.Get("PET.RWTC.D")` and the data arrives — from a folder, a
network share, or a server; the workbook doesn't care which. Formulas,
refresh, and a NEW-DATA indicator do the rest. If you've used Bloomberg's
BDH, you already know how this feels.

---

## 1. Installation

You receive a folder with these files — **keep them together**:

| file | what it is |
|------|------------|
| `EnergyScope-AddIn64-packed.xll` | the add-in for **64-bit** Excel |
| `EnergyScope-AddIn-packed.xll` | the add-in for **32-bit** Excel (File → Account → About Excel tells you which you have) |
| `vortex_ffi.dll` | data-file reader |
| `eia_to_dataset.py` + `vx.exe` | the Import feature (needs Python 3 with `polars` + `pyarrow`; optional otherwise) |
| `sheet-recipes\` | the ready-made page designs |
| `user_doc.md` | this guide |

**Install**: File → Options → Add-ins → Manage: Excel Add-ins → Go… →
Browse… → pick the `.xll` **matching your Excel's bitness** → OK. If a
Security Notice appears, it says the digital signature is *valid* —
choose **"Enable all code published by this publisher"**. An
**EnergyScope** tab appears in the ribbon. That's the whole install.

**No tab / `#NAME?` afterwards?** Three look-alike causes, in order of
likelihood: (1) bitness mismatch — Excel will show the add-in as
"Active" yet do nothing; untick it and pick the matching file;
(2) the zip wasn't unblocked before extracting; (3) the .NET 8 Desktop
Runtime for your bitness is missing (x86 runtime for 32-bit Excel).

---

## 2. Connect to your data (the Source)

Everything the add-in shows comes from one **Source** at a time — and the
**Source dropdown** in the Connection group is the one place to set it.
Top to bottom it lists:

- **Servers** — live EnergyScope endpoints (the list comes from
  `servers.json`, below).
- **Your recent sources** — the last 3 folders/URLs that successfully
  connected, one click to return to any of them.
- **Dataset Folder…** — browse to a folder of dataset files on your disk
  or a network share (e.g. `\\NAS\energy`). No server, no account,
  instant.
- **Connect URL…** — a published dataset address, `https://…` or
  `s3://bucket`. Files download on demand and are cached; refreshes only
  transfer what changed.

  **Your own S3/R2 bucket** works too: `s3://bucket/path` is rewritten
  to HTTPS through a template your IT team sets once, in the registry at
  `HKCU\Software\EnergyScope` → `S3Endpoint` (default
  `https://{bucket}.s3.amazonaws.com`; for Cloudflare R2 or MinIO, point
  it at your endpoint or public-bucket domain). Publish the dataset
  folder into the bucket, make it readable (public or presigned — the
  add-in doesn't sign requests), and every analyst connects with the
  same short `s3://…` address. Any S3-compatible store works.
- **`<none>`** — disconnect entirely (and stay disconnected across
  restarts, until you pick a source again).

The **traffic light** beside the dropdown shows the state of whatever is
selected: **green** = connected to a valid source · **orange** =
connecting, or a folder with no data in it yet · **red** = not
connected. Hover it for the reason; click it to re-check. The dropdown
always highlights the active source — a server by its entry, a folder or
URL by its recent-sources row.

The dropdown's server list lives in `servers.json` next to the add-in —
your IT team (or data publisher) can edit that file to add or rename
servers; no reinstall needed. If the file is missing, the add-in's
built-in list is used.

**Credentials…** stores an access key if your source needs one (servers
do; folders and shares don't — the button only appears while a server is
your source). It's encrypted into your Windows profile — never saved in
workbooks.

Your choice is remembered — next Excel session reconnects automatically.

---

## 3. The refresh model (read this once)

- **Opening a workbook does not fetch data.** Formulas show blank until
  you click **Refresh All** — so a 500-formula workbook never hammers a
  source just because you opened it. Click Refresh All once; everything
  fills.
- **A formula you type fills immediately.** New or edited formulas fetch
  on entry, no refresh needed.
- **Refresh** re-fetches and re-checks everything; it also advances the
  "what have I already seen" baselines that drive the NEW-DATA indicator.
- Data functions never freeze Excel — they fill in asynchronously, and
  errors appear as readable `ERROR: …` text in the cell, not `#VALUE!`.

---

## 4. Getting data into cells

**Start with the trick that shows the whole point.** On a blank sheet,
put this in A4:

```text
=ES.Table(1:1, "2015-01-01", , "ffill", "ME")
```

Now type series IDs into **row 1**. Each ID becomes a dated column within
a second — no dialogs, no refresh, the table just grows as you type.
Leave gaps between IDs and they become empty spacer columns; type into a
gap later and that column fills in place. Label it with the same trick:

```text
=ES.Name(1:1)     descriptions, aligned under each ID
=ES.Units(1:1)    units, same alignment
```

Four formulas and a header row you type into — that's a working,
self-labelling dashboard. (One rule: keep that row free of titles — only
series IDs.)

The classic forms, when you want them:

```text
=ES.Get("PET.RWTC.D", "2024-01-01", "2024-12-31")   dates optional
=ES.Latest("PET.RWTC.D")                             latest value + name + units
=ES.Table(B1:F1, "2015-01-01", , "ffill", "ME")      an explicit range of IDs
```

**ES.Table** options:

- **fill**: `ffill` (carry last value forward — the default, like BDH),
  `blank`, `NA`, or any number.
- **freq**: `D W M ME Q A` — `ME` gives month-end dates; leave empty to
  auto-detect from the series.

---

## 5. Ready-made pages (New Sheet menu)

The **Build → New Sheet** menu creates complete, styled pages in one click:

- **ColX Demo** — a full analyst page (US PADD1 distillates): series with
  hover-comments describing each one, change tracking, and grey "your
  work" columns that refreshes never touch — the data columns update
  around your own formulas.
- **Table Demo** — the self-building table: parameter cells with
  dropdowns, then type series IDs into the header row and watch it grow.
- **Series Catalog** — see §6.
- **Find Series** — keyword search page; also one click from the Build
  group's Find Series button. See §6.
- **ColX Wizard…** — builds a monitoring page from series you name in
  prompts: monthly/weekly header pairs, a NEW DATA row that lights amber,
  date-only ColX columns, and spare reserved columns that wake when you
  drop an id in their header (Insert Series works nicely for that).

---

## 6. What's available? (discovery)

```text
=ES.Catalog("PET.WDIST*")    inventory matching a pattern
=ES.Search("crude oil")      keyword search (wildcards work too)
=ES.Name("PET.RWTC.D")       description for one ID
=ES.Units("PET.RWTC.D")      units for one ID
```

**ES.Catalog** spills a reference table — `series_id, name, units, freq,
last data date` — for every series matching a wildcard pattern (`*` = any
text, `?` = one character; case doesn't matter). Big result sets cap at
500 rows with an honest "… N more — refine the pattern" final row (raise
the cap with the second argument). The **Series Catalog** page in the New
Sheet menu wraps this in a sheet with an editable pattern cell.

The **Find Series** button (Build group, keytip `F`) builds the search
version of the same page: type keywords in the orange cell, press Enter,
and matching series spill below — id, name, frequency, units. Wildcard
patterns work here too (`PET.MDIST*`), and results put id matches first
so a prefix like `PET` leads with the `PET.*` series. In folder mode the
search runs against the folder's own catalog, entirely offline. Copy any
id straight into a series row.

**Insert Series** (Build group, keytip `S`) is the same search as a
floating tool window, for composing without leaving your sheet: click
the cell where an id should go, search, and double-click a result (or
press Enter) — the series_id lands in that cell, and "step →" advances
one cell right so consecutive inserts fill a header row. With the search
box empty the window lists the catalog itself — every dataset prefix
with its series count — and double-clicking a prefix drills into it.
Esc closes the window.

---

## 7. Knowing when data is new

- The ribbon's big button doubles as an indicator: it flips to
  **⚠ NEW DATA (n)** when your source has newer data than your last
  refresh. Checks run automatically every 30 seconds (the **Auto-check**
  box turns that off), and once when a workbook opens.
- **The count is series you actually use.** Each refresh takes note of the
  series in your workbook; *n* is how many of those now have newer data —
  on every source type (server, folder, share, URL). A published file that
  changed without touching your series stays silent.
- **The check is a preview — a refresh without touching your work.** It
  compares the source against what your workbook currently holds and never
  changes a cell. That works across source switches too: pick a different
  source and within seconds the indicator tells you whether *that* source
  has anything newer than what you're looking at — you decide when to
  actually load it.
- The ribbon's **New Data Pre-Check** group shows the check itself: which
  source it ran against, when, how long it took, how many series it
  watches, and how many have new data right now.
- Per-series, in-sheet:

```text
=ES.NewData(B1:B2,,300)               TRUE when these series have unseen data (re-checks every 300 s)
=ES.NewData(B1:B2,,300,"NEW DATA")    same check, but the cell says NEW DATA / blank — dashboard-ready
=ES.NewData(B1:B2,,300,"NEW","ok")    your own text for both states
=ES.LastDate(B1:F1)       latest data date across series
=ES.NewDataStats()        the New Data Pre-Check group as a spill (when/how long/source/watched/new)
=ES.NewList()             WHICH series are new: id, name, date you have, date available
=ES.Registered()          the whole watch universe: every series this workbook tracks + last date
=ES.Registered(TRUE)      the EXACT list the Pre-Check counts (the last refresh's declared list)
=ES.Report("eia_weekly")             the standing weekly dashboard — stocks, supply, retail and spot
                                     prices, each with week-on-week and year-ago changes
=ES.Report("eia_weekly","2025-10-27") the same table as it stood on any past date: nothing published
                                     after that date is read, so it reproduces that week's edition
=ES.Brief("eia_weekly")              the WRITTEN brief for that same report: headline, lede, supporting
                                     lines, extremes, the signals firing and the numbers behind them
=ES.Brief("eia_weekly","2025-10-27") pass the same date as the table and the prose rewrites itself to
                                     what was true that week — the brief is computed FROM the table,
                                     not written about it, so the two cannot disagree
=ES.Events(14)            upcoming market catalysts: WPSR, CoT, STEO, OPEC/IEA reports — when & days out
=ES.Signals(A1:B20)       the narrative layer: a 2-col key|value metrics range in, NAMED signals out —
                          severity, the trigger sentence ("crude stocks seasonal percentile of 0 is
                          below the 20 threshold"), contradiction narratives, and transitions when you
                          pass a previous snapshot as the 2nd range
```

  `ES.NewData`'s second argument, when you give one, is a **baseline date
  cell**: the check then compares against THAT date, at its granularity.
  Point it only at a cell that shares one frontier with the series — a
  plain single-series column's date cell. For multi-series ranges or
  synthesized ColX columns, leave it out (as in the examples above): auto
  mode compares each series at full granularity against your last
  refresh. A month-granular baseline like `2026-08` silently swallows a
  new weekly print inside the same month — and a weekly-extended column's
  date sits months past the monthly series' own frontier, so the monthly
  print could never fire it at all.

  `ES.Brief`'s first row names the LAYER the prose came from. `measured`
  means every sentence was generated from the table by fixed rule with no
  model in the path — run it twice on the same date and the text is
  identical. Rows marked `not evaluated` tell you why a metric is MISSING
  (e.g. too little history for a percentile to mean anything), because
  silence would read as "we looked and it was unremarkable".

  `ES.Signals` speaks the report engine's catalog (53 signals, 16
  contradictions — the rules are published, ask for `schema=True` docs or
  see energyscope.io/methodology). Your sheet computes the metrics with
  functions you already use; the engine names the regime.

  With a label set, the brief `#N/A` while a re-check is in flight shows
  the last known answer instead — no flicker. Real errors still show as
  `ERROR: …` text: a freshness cell that goes quietly blank when the
  server is unreachable would be worse than no cell at all. (No more
  `=IFERROR(IF(ES.NewData(...),"NEW DATA",""),"")` wrappers.)

  Label mode is also drag-friendly: dragged across a dashboard row with
  spacer columns, a column with no series above it shows blank — not
  `#VALUE!`, and not the "up to date" text (an empty slot isn't up to
  date, it's unwatched).

- **ColX pages track changes visibly**: a refreshed cell gets a green
  border if it's new, and a *revised* historical value gets a comment —
  "Before: 128,941 / Now: 129,014". Real data gets revised; your
  spreadsheet should admit it. (`Ctrl+Shift+K` clears comments when
  you're done reviewing.)

---

## 8. Building analyst pages by hand

```text
=ES.Dates("2024-01-01", , "ME")            date column (end empty = today)
=ES.ColX(B$1, B$2, $A$9, , $A$8, , ROW(A9))  one tracked column per series
=ES.Monthly("PET.MDISTUS1.M", "PET.WDISTUS1.W")  monthly + synthesised current month
```

`ES.ColX(monthly, weekly, …)` writes a column aligned to your `ES.Dates`
grid, with the change tracking described above — and when a month isn't
published yet, it synthesises it from the weekly series (shown in blue so
you know it's synthesised). The New Sheet pages are built from exactly
these functions — open one and read its yellow `FORMULATEXT` cells to see
how.

Series IDs in ColX/Col accept two display-conversion suffixes: `.days`
(days-of-supply conversion) and `.neg` (sign flip) — e.g.
`PET.MD1MX_R20-R10_1.M.days`. The suffix changes how the numbers render,
not which series is fetched — and `ES.NewData` / `ES.LastDate` understand
this too: pointed at a suffixed ID they watch the underlying series.

The formula cell itself shows **the last data date** — one line, nothing
written below. That cell is deliberately useful: point an `ES.NewData` at
it and the column announces its own freshness. If you want the classic
diagnostics layout (the formula cell shows the datapoint count, then
new/revised and the date on the two rows below), turn on **Column
details** in the ribbon's Build group, or pass `TRUE` as the 10th argument
to pin it per formula (`FALSE` pins date-only). The wizard pages pass
`TRUE` themselves — their labels expect the full layout.

---

## 9. Analytics

Every analytics function takes a series ID (or several) and spills a
labelled result — e.g. `=ES.Stats("PET.RWTC.D")` or
`=ES.CrackSpread("3-2-1")`.

### Everyday analysis (works even offline against a folder)

`ES.Stats` · `ES.Corr` · `ES.RollingCorr` · `ES.Returns` ·
`ES.Volatility` · `ES.Transform` · `ES.Interpolate` · `ES.Outliers` ·
`ES.ACF` · `ES.Percentile` · `ES.Seasonal`

### Market analysis

`ES.Spread` (pair z-score, half-life) · `ES.CrackSpread` (3-2-1,
gasoline, diesel) · `ES.TermStructure` (futures curve) · `ES.Changes`
(significant moves, ranked)

### Forecasting & econometrics (server required)

`ES.Forecast` (multi-factor) · `ES.ARIMA` · `ES.Prophet` · `ES.Theta` ·
`ES.Smooth` · `ES.Seasonality` · `ES.VAR` · `ES.ElasticNet` ·
`ES.QuantileReg` · `ES.BubbleTest` · `ES.LPIRF` · `ES.MIDAS` ·
`ES.Cointegration` · `ES.UnitRoot` · `ES.Breakpoints` · `ES.HPFilter` ·
`ES.BandPass`

---

## 10. Updating datasets (Import)

If your dataset folder ships refresh recipes, **Build → Import** lists
them — e.g. *Update EIA Petroleum*, with a "Last imported: …" line telling
you who refreshed it last and when. One click pulls the publisher's
latest file and converts it in place; progress streams in the status bar,
and the NEW-DATA indicator announces the result like any other update.
`=ES.ImportLog()` shows the full run log. Unchanged data is never
rewritten — no false "new data" signals, ever.

---

## 10a. Help & updates (Support group)

The ribbon's **Support** button, at the right end of the tab: the main
click opens the product website; the dropdown arrow holds the menu:

- **About** — version, build time, your current source, and the support
  address (hello@sparrowxl.com).
- **Getting Started** — builds the Welcome quickstart sheet. The same
  page a first run lands on, available any time.
- **User Guide** — opens this document (shipped beside the add-in, so
  it works offline).
- **Check for Updates** — one small request to dl.sparrowxl.com
  comparing your build against the latest release. It only tells you;
  nothing downloads or installs by itself.

---

## 11. Keyboard shortcuts

| keys | action |
|------|--------|
| `Ctrl+Shift+N` | format selection as `#,##0` — press again to undo |
| `Ctrl+Shift+D` | cycle date format: `yyyy-mm-dd` → `yyyy-mm` → back to General |
| `Ctrl+Shift+K` | clear comments/notes from the selection |

Ribbon keytips (press `Alt`, then the tab's letters, then): `R` Refresh ·
`A` Refresh All · `C` Check New Data · `E` EIA Datasets · `U` Update EIA ·
`W` WPSR Day · `N` New Sheet · `I` Import · `H` Support/Help.

---

## 12. Tips & gotchas

- **Blank cells after opening a workbook** = the refresh model (§3), not
  a fault. Click Refresh All.
- Dates in formulas: type `"2024-01-01"` or point at a date cell — both
  work.
- `ME` vs `M`: month-END dates vs first-of-month. House preference is ME.
- In a whole-row series list, text that isn't a series ID becomes an
  empty column — handy as a "type here" prompt, confusing as a title.
  Numbers and TRUE/FALSE are ignored entirely.
- The same series ID twice in a list gives you the column twice.
- VBA: use `Application.Run "ES.RefreshNow"` and the synchronous
  `ES.Fetch` / `ES.FetchSearch`.

## 13. If something looks wrong

- **Ribbon → Health** shows the source status; the **Stats** group shows
  what the last refresh actually did (rows, series, timing).
- `=ES.Version()` — version and build when reporting an issue.
- `=ES.BatchLog()` — the technical log of recent activity.
- `=ES.Feedback("message")` — sends a note straight to us.
- Restarting Excel is always safe: your source, credentials, and
  baselines are all remembered.
