# LMG / NCAM Census Translation Engine — User Guide (v1.0) A one-click Excel tool that converts a carrier census into the LaunchMyGroup (LMG) upload format and a derived NCAM quoting sheet, with full validation and an audit trail. It runs **inside Excel** — no install, no Python, no website. --- ## 1. What it does Point it at a messy census file and press **Run**. In a few seconds your workbook is rebuilt into six tabs: | Tab | What it is | |-----|------------| | **LMG** | Your clean enrollment file, ready to upload. Always tab #1. | | **NCAM** | Employees-only sheet in quoting layout. | | **SUMMARY** | Counts by relationship and by plan tier (EE/ES/EC/FAM/W). | | **VALIDATION ISSUES** | Every problem found, rated ERROR / WARNING / INFO. | | **PROCESSING LOG** | A record of every transformation it performed. | | **RAW DATA** | An untouched copy of your original census. Never modified. | | **Other Columns** | Every column kept out of the LMG upload, with the reason — blank LMG fields, unrecognized source headers, and duplicate columns. The data still lives on RAW DATA. | The **LMG** and **NCAM** tabs are colored green; the rest are navy. On the **LMG** tab, any cell with a validation issue is highlighted by severity (red = error, amber = warning, blue = info), so problems jump out at a glance. It standardizes headers, merges split address fields, converts state names to 2-letter codes, cleans phones to 10 digits, formats dates to MM/DD/YYYY, normalizes gender to M/F, converts coverage to EE/ES/EC/FAM/W, abbreviates long job titles, annualizes hourly wages, and more. **It also reshapes the two "nightmare" layouts before cleaning:** if a person is split across several rows (one per line of coverage — Dental, Vision, Life…), it **collapses them into one row**; if dependents are stored sideways as columns (`Dependent 1 Name`, `Dependent 2 …`), it **explodes them into their own Spouse/Child rows**. It only reshapes when it's confident, and flags anything it's unsure about. Try `SAMPLE-stacked-census.xlsx` to see it. --- ## 1b. Rolling this out to your team **The Automate tab (Office Scripts) is enabled on the Humana network** — confirmed — so this is the right primary tool for the team. Everyone uses their Humana M365 account in Excel Online or desktop Excel. **Share one copy so everyone runs the same logic.** Two mechanisms, with an important difference: - **Share into a workbook** (script's **⋯** menu → **Share**): attaches the script to *that workbook only* — anyone opening that workbook can run it, but it doesn't follow them to other files. Fine for a shared template; not a team-wide rollout. - **Save the script to a shared SharePoint/Teams library** (script's **⋯** menu → **Save As** → pick a SharePoint site your team can access): everyone adds that location once in **Automate → All Scripts** and runs the *same file* in any workbook. When the engine updates, you paste the new version into that one script and **the whole team is updated — no re-pasting for anyone else.** This is the recommended way to manage versions for a team. If neither is available on your tenant, everyone maintains their own copy and updates it from the hub page (see section 2c — it takes ~20 seconds). **Universal fallback (off-network / non-Humana users):** the offline HTML cleaner (`CensaScrub/lmg_census_cleaner_offline.html`) runs in any browser with no account and keeps data on the machine. It implements the same `CENSUS-RULES.md`, so output matches. ## 2. One-time setup (≈2 minutes) You need **Excel for the web** or **Excel on desktop with a Microsoft 365 work account** (the **Automate** tab must be visible on the ribbon). 1. Open the census workbook in Excel. 2. Click the **Automate** tab on the ribbon → **New Script**. 3. Delete the sample code in the editor. 4. Open `LMG-NCAM-Census-Engine.ts`, copy **all** of it, and paste it in. 5. Rename the script to `LMG NCAM Census Engine` and click **Save script**. That script now lives in your account. For every future census you only do section 3. --- ## 2b. Confirming you're on the latest version (for testing) The script carries a version number. When it runs, that version is printed on the **SUMMARY** tab (`Engine version`) and as the first line of the **PROCESSING LOG** tab. The hub page (lmg-ncam-engine.netlify.app) shows the latest version at the top. **If the version in your output doesn't match the hub, re-copy the script.** This is how we stay lined up while testing. ## 2c. Updating to a new version (≈20 seconds) The script doesn't update itself. When the hub shows a newer version: 1. On the hub page, click **Copy script**. 2. In Excel: **Automate** tab → click **LMG NCAM Census Engine** → **Edit**. 3. Click inside the code, **Ctrl+A** (select all), **Ctrl+V** (paste), then **Save script**. You can also sign up on the hub page to get an email when a new version ships. ## 3. Running it on a census (every time) 1. Open the census workbook. 2. Make sure the tab with the raw census data is the **active** tab (just click it). The engine reads from the active tab, or from a tab named `RAW DATA` if one already exists. 3. **Automate** tab → find `LMG NCAM Census Engine` → **Run**. 4. When it finishes, you'll land on the **LMG** tab. **Always review the VALIDATION ISSUES tab before you upload.** See section 5. It is safe to run more than once. The first run snapshots your original to `RAW DATA`; every later run reprocesses from that untouched snapshot, so you can fix the source and re-run without losing anything. --- ## 4. What gets cleaned (the rules) - **Headers** — parenthetical text removed (`RELATIONSHIP (EE, SP, CH)` → `RELATIONSHIP`); common variants mapped to LMG field names (`SSN`, `Soc Sec No`, `Tax ID` → the SSN column). - **Relationship** → `Employee`, `Spouse`, `Domestic Partner`, `Child`. Unknown values (e.g. `Retiree`) are flagged, not guessed. - **Names** — Last/First UPPERCASE, Middle Initial trimmed to one letter, Relationship in Proper Case. - **Gender** — `Male`→`M`, `Female`→`F`. Anything else is flagged. - **Dates** (DOB, hire, prior-carrier) → `MM/DD/YYYY`. Handles slashes, dashes, ISO, 2-digit years, and Excel date serials. Unparseable dates flagged. - **SSN** — digits only, must be 9. Employees missing an SSN are flagged. - **Address** — `Address 1` + `Address 2` + `Address 3` merged into one `Member Street Address`, UPPERCASE. - **State** — full names → USPS (`Texas`→`TX`). Invalid states flagged. - **Zip** — first 5 digits; restores a dropped leading zero. - **Phone** — digits only, 10 digits; strips a leading country `1`. Bad phones are flagged and left blank rather than uploaded wrong. - **Salary** — `$50,000` → `50000`. `$25.50/hr` with 40 hours → `25.50 × 40 × 52 = 53040`. A salary under $1,000 is flagged as a possible hourly rate. - **Hours** — blank employee defaults to `40`. - **Occupation** — UPPERCASE, common abbreviations (`SENIOR`→`SR`, `ADMINISTRATOR`→`ADMIN`, …), max 25 characters; over-long titles flagged. - **Disabled?** — blank defaults to `No`. - **Coverage tier** — normalized to `EE / ES / EC / FAM / W`. - On **LMG**, a waived dental/vision tier becomes `Waive with other coverage`; a blank employee tier (when the group carries that product) is also forced to `Waive with other coverage`. - On **NCAM**, those convert back to `W` (because NCAM defaults blanks to EE). - **Primary Beneficiary** — if an employee has Basic or Voluntary Life but no beneficiary, it's set to `ON FILE`. - **Voluntary Life** — employee blank/waive amounts become `0`. --- ## 5. Reading the VALIDATION ISSUES tab | Severity | Meaning | Do before upload? | |----------|---------|-------------------| | **ERROR** | Missing/invalid required data (no SSN, bad DOB, no gender). | **Yes — fix.** | | **WARNING** | Likely wrong but not blocking (odd salary, unknown relationship, **possible duplicate**). | Review. | | **INFO** | FYI only (e.g. spousal vol-life on a dependent row). | Optional. | Each issue lists the row number, member name, column, the offending value, the problem, and a recommended action. Fix the issues on your source tab and re-run, or fix them directly on the LMG tab before uploading. ## 5b. Mapping columns the tool didn't recognize (HEADER MAP tab) When the tool isn't sure where a column belongs, it doesn't guess silently — it puts it on the **HEADER MAP** tab. To include such a column: 1. Open the **HEADER MAP** tab. Each row is a column the tool couldn't match exactly, with a **SUGGESTED** field, a **CONFIDENCE** %, and a blank **MAP TO LMG FIELD** cell. 2. In **MAP TO LMG FIELD**, type the LMG field it should become (copy the exact name from the **LMG** tab header — e.g. `Dental Coverage Tier`). 3. **Re-run** the script. The column is now mapped and included. The tool reads your **MAP TO** entries first (they override everything), and they **persist** — the next file from the same carrier maps cleanly without redoing it. A very close match (≥85%) is applied automatically and marked "verify" so you can check it; clear its **MAP TO** cell to undo. ### Duplicates are flagged, not deleted If two rows share the same last name + first name + DOB + gender, the engine flags them as a WARNING. It does **not** delete a row — you decide which to remove. This is deliberate: an upload file should never silently drop an enrollee. --- ## 6. Uploading to LaunchMyGroup The **LMG** tab is your upload file and is always worksheet #1 (LMG uploads the first sheet only). Either upload the workbook as-is, or right-click the **LMG** tab → **Move or Copy** → *(new book)* to export just that sheet. --- ## 7. Known limits in v1.0 (by design) - **No automatic family-unit merge.** Coverage tiers come from the values in the census, not from re-deriving who belongs to which family. - **Duplicates are flagged, not merged.** - **Voluntary-life roll-ups** (spouse/child amounts moving onto the employee row) are flagged for manual handling. - **NCAM** uses the spec's minimum quoting columns plus Salary/Occupation/STD/ LTD when disability coverage is present. These are the planned next versions. The fastest way to mature them is to run real census files through v1.0 and feed the edge cases back in. --- ## 8. If something looks wrong 1. Check **PROCESSING LOG** — it lists what the engine did. 2. Check **RAW DATA** — confirm the original value. 3. Note the case, the column, the source value, and the wrong output, and bring that example back to your AI assistant with the reference blurb (`AI-REFERENCE-BLURB.md`). Real examples improve the tool far faster than guessing at carrier formats up front.