NSF Collaborators and Other Affiliations

Every senior person on an NSF proposal files a COA. It is not a CV and not a credit list. It is a conflict-of-interest disclosure, and NSF uses it for exactly one purpose — keeping conflicted people out of the reviewer pool for that proposal. PAPPG Exhibit II-2 is where the purpose is written down, and it is what settles every judgement call below. Getting the framing backwards produces a document that looks impressive and is wrong.

The work is mechanical and miserable. A mid-career PI has a hundred co-authors inside the 48-month window, and NSF wants each one as Last, First, Middle Initial with a current organizational affiliation. A CV carries neither, because CVs print authors as initials. Doing it by hand means opening every paper from the last four years and typing out the author block.

What this skill produces is a draft. A publication record gives you co-authors. It does not give you a spouse, a Ph.D. advisee, an editorial board seat, or a collaboration that has not been published yet — and all four are on the form. The workbook it writes contains no placeholders: a field it cannot answer is left genuinely empty, and every empty field is named in a separate summary. NSF holds the senior person responsible for the file, not this page.

What NSF requires, and when it was checked

Checked 2026-08-27. PAPPG 24-1 (effective 2024-05-20) is in force, supplemented by NSF 26-200 (effective 2025-12-08) and NSF 26-202 (effective 2026-01-22). COA sits at PAPPG II.D.2.h(iii), and the conflict definitions it points to sit at Exhibit II-2. NSF's senior-personnel-documents page was last updated 2026-04-13. NSF has signalled that the PAPPG will eventually be replaced by Guidance on Financial Assistance; when that lands, re-check every date on this page.

Where the template and the policy disagree, follow the template. It is the thing that gets submitted. Two live examples, both small and both real:

• PAPPG footnote 35 excludes "Editorial Advisory Board, Scientific Editorial Board, or any other subcategory". The template's instruction rows add International Advisory Board to that list. Use the template's longer exclusion.
• The template's instruction rows require mm/dd/yyyy or m/d/yyyy dates. The template's own example date cells carry the number format mm-dd-yy. Follow the instruction.

So the first thing the code below does is read the contract out of the file NSF ships, rather than trusting any secondary description of it — including this one.

The five tables

Table	Codes	Contents	Window
1	—	The senior person's own organizational affiliations	last 12 months
2	R:	Personal, family or business relationships that would preclude someone serving as a reviewer	none stated
3	G: T:	G: Ph.D. advisors, T: all Ph.D. thesis advisees	lifetime
4	A: C:	A: co-authors on any book, article, report, abstract or paper; C: collaborators on projects, funded awards, graduate research	48 months
5	B: E:	B: editorial board and editor-in-chief with journal, E: other co-editors directly interacted with	24 months

An editor-in-chief must list the entire editorial board.

Four things older guides get wrong

All four are stated in the template's own instruction rows, and all four contradict guidance that is still in circulation on university research-administration pages.

1. Postdoctoral sponsors are no longer reported. The template says prior-PAPPG information is no longer requested and gives this as its worked example — a postdoc sponsor appears only "if the individual collaborated on research with their postdoctoral scholar sponsor", in which case they are a Table 4 collaborator, not a Table 3 entry. Over-reporting here is the most common legacy error. The template still ships one orphan dropdown offering a P: code at cell A33, outside every table; it is a leftover, and the writer below drops it rather than carrying a retired code into a live document.
2. Table 3 wants Ph.D. thesis advisees, not all trainees. Master's students and undergraduates do not belong there.
3. Editorial Board has a narrow definition — people who "perform editing duties or manage the editing process". Most named board seats on a CV do not qualify. NSF's guidance page adds a rule the template does not: do not list editors or reviewers you interacted with because you submitted a paper to them.
4. Sorting is not required. The instructions say so outright — "not required to be sorted, alphabetically or otherwise". Do not spend effort on it and do not imply it matters.

Format rules that decide whether it is accepted

• Research.gov: upload the .xlsx directly, as a Collaborators and Other Affiliations Single Copy Document. NSF converts it. Never a PDF — uploading any other format "may delay the timely processing and review of your proposal".
• Grants.gov: the template is uploaded as a PDF attachment, for the PD/PI only. Additional senior personnel still need their .xlsx in Research.gov after the Grants.gov submission.
• Complete it in Excel. Google Sheets is explicitly unsupported.
• Content and format "must not be altered". Specifically: do not change the column widths or the font type. Rows may be inserted, and the instruction rows at the top may be deleted — PAPPG says both.
• Names as Last Name, First Name, Middle Initial.
• Organizational Affiliation may include a location, must not include a street address, and is capped at 255 characters.
• Last Active Date / Last Active in mm/dd/yyyy or m/d/yyyy. Optional. Blank means ongoing or current — it is not a gap.
• Font at 10pt or smaller where a cell needs to fit; PAPPG allows reducing the font size to fit a long name.
• One COA per senior person. This produces one.

What you need

Python 3.9+, openpyxl and requests. No API key and no account, for any of the three sources. The senior person's ORCID iD is optional but close to essential — see the common-surname wall below.

source	what it gives	licence
NSF COA template	the specification, machine-readable	US Government work, not subject to domestic copyright
Crossref REST API	works and author blocks, with given and family kept apart	"almost none of the metadata is subject to copyright, and you may use it for any purpose"
NSF Award Search API	co-PIs on NSF awards	US Government work

ORCID's own API is deliberately not used here. ORCID's annual Public Data File is CC0, but its Public APIs Terms of Service grant only "a limited royalty-free license to make non-commercial use of the Public APIs", and spell out that you "may not make use of the public APIs in connection with any revenue-generating product or service". A researcher at a company, or anyone whose work is commercial, cannot lawfully take that route, so this page does not send them down it. The ORCID identifier is used throughout — as a Crossref filter value and as the field Crossref carries on each author — and nothing about that touches ORCID's terms. Checked 2026-08-27.

Crossref is the only bibliographic source, and adding a mailto= to any request puts you in its polite pool. No key, no sign-up.

The template is not vendored here. It changes on NSF's schedule, and a stale copy of a compliance form is worse than none — the code downloads it fresh every run.

python3 -m venv .venv
.venv/bin/pip install openpyxl requests

Save each block below as the file named in its first line, then run them in the order they appear. Set the subject once:

export COA_FAMILY="Martonosi" COA_GIVEN="Margaret" COA_ORCID="0000-0001-9683-8032"
export COA_ASOF="2026-08-27"        # omit to use today

Read the contract out of NSF's own template

The five tables are not in the order the file stores them, and their internal names are TableA, TableD23, TableC, TableD and TableD5 — which map to displayed tables 1, 2, 3, 4 and 5 respectively. Keying on the name, or on the order openpyxl yields, silently puts co-authors in the advisee table. Key on the number NSF prints in each table's own first header cell.

# coa_spec.py -- read the COA contract out of NSF's own template.
import json, re, warnings, requests, openpyxl
warnings.filterwarnings("ignore", message="Unknown extension is not supported")

TEMPLATE_URL = "https://www.nsf.gov/bfa/dias/policy/coa/coa_template.xlsx"

def fetch(path="coa_template.xlsx"):
    r = requests.get(TEMPLATE_URL, timeout=60, headers={"User-Agent": "nsf-coa/1.0"})
    r.raise_for_status()
    open(path, "wb").write(r.content)
    return path

def read_spec(path="coa_template.xlsx"):
    ws = openpyxl.load_workbook(path).active
    title = re.compile(r"^Table\s+([1-5])\s*:")
    first = min(c.row for c in ws["A"] if isinstance(c.value, str) and title.match(c.value))
    spec = {"sheet": ws.title,
            "instructions": [c.value for c in ws["A"]
                             if c.row < first and isinstance(c.value, str) and c.value.strip()],
            "tables": {}}
    for t in ws.tables.values():
        hdr = int(t.ref.split(":")[0][1:])
        last = int(t.ref.split(":")[1][1:])
        spec["tables"][str(ws.cell(hdr, 1).value).strip()] = {
            "internal_name": t.name, "ref": t.ref, "header_row": hdr, "last_row": last,
            "blank_rows": last - hdr, "n_cols": len(t.tableColumns),
            "columns": [c.name for c in t.tableColumns]}
    spec["codes"] = {str(dv.sqref): dv.formula1[0] if isinstance(dv.formula1, list) else dv.formula1
                     for dv in ws.data_validations.dataValidation if dv.type == "list"}
    return spec

if __name__ == "__main__":
    spec = read_spec(fetch())
    json.dump(spec, open("coa_spec.json", "w"), ensure_ascii=False, indent=1)
    print(f"sheet             {spec['sheet']!r}")
    print(f"instruction rows  {len(spec['instructions'])}")
    print(f"tables            {len(spec['tables'])}")
    for n in sorted(spec["tables"]):
        t = spec["tables"][n]
        print(f"  Table {n}  rows {t['header_row']:>2}-{t['last_row']:<2} "
              f"{t['blank_rows']} blank  {t['n_cols']} cols  internal name {t['internal_name']!r}")
    print("row-code dropdowns the template ships:")
    for sq, f in sorted(spec["codes"].items()):
        print(f"  {sq:<10} {f}")

Observed 2026-08-27 — a change here means NSF revised the template, which is drift to investigate before anything else on this page is trusted:

sheet             'NSF COA Template'
instruction rows  13
tables            5
  Table 1  rows 16-21 5 blank  4 cols  internal name 'TableA'
  Table 2  rows 27-32 5 blank  5 cols  internal name 'TableD23'
  Table 3  rows 37-44 7 blank  4 cols  internal name 'TableC'
  Table 4  rows 51-56 5 blank  5 cols  internal name 'TableD'
  Table 5  rows 63-68 5 blank  5 cols  internal name 'TableD5'
row-code dropdowns the template ships:
  A28:A32    "R:"
  A33        "G:,T:,P:"
  A38:A44    "G:,T:"
  A52:A56    "A:,C:"
  A64:A68    "B:,E:"

Table 3 ships seven blank rows and four columns; the others ship five rows, and only Tables 2, 4 and 5 have a fifth column. The A33 dropdown offering P: sits below Table 2's range and belongs to no table.

Find the works in the 48-month window

Crossref is the source, for two reasons. It keeps given and family in separate fields, so Last, First, Middle Initial is read rather than guessed from a byline. And it needs no key and no budget, which matters because a COA run for one person is a few hundred lookups.

Three things are not what they look like:

• Search on the surname alone. query.author=Margaret Martonosi is an OR over tokens and returned 41,967 works ranked by relevance, led by papers about drug policy. query.author=Martonosi returned 39, all of them genuinely by someone of that name.
• published is the publication date. created is the first deposit and can fall either side of it — on 10.1145/3776585, published 2025-12-13, created 2025-11-12. indexed is when Crossref last touched the record and was 2026-07-11 for the same work. Only published answers the 48-month question.
• Filtering on the ORCID iD alone badly under-retrieves. Crossref's filter=orcid: only finds works whose depositor put the iD in the metadata: 10 works for Margaret Martonosi, against 39 the surname search found. It is precise, not complete — so it anchors the identity rather than defining the list, and it is the only evidence that survives the overflow case below.

The common-surname wall. The surname scan is bounded by how many people share the surname, and for some names it cannot be run at all. Over a four-year window the surname query reports 140,020 matches for González and 2,373,908 for Wang. When the scan overflows, name evidence stops meaning anything, so this code switches it off and attributes only what an identifier vouches for — and refuses outright if no ORCID iD was supplied. That refusal is the correct output. A confidently wrong Table 4 is worse than none.

# coa_harvest.py -- find the senior person's works in the Table 4 window.
import json, os, re, sys, time, unicodedata, datetime as dt, requests

FAMILY = os.environ.get("COA_FAMILY", "Martonosi")
GIVEN  = os.environ.get("COA_GIVEN",  "Margaret")
ORCID  = os.environ.get("COA_ORCID",  "")            # bare iD, e.g. 0000-0001-9683-8032
ASOF   = dt.date.fromisoformat(os.environ.get("COA_ASOF", dt.date.today().isoformat()))
WINDOW, GRACE = 48, 120        # Table 4 lookback in months; days shown either side
MAX_SCAN = int(os.environ.get("COA_MAX_SCAN", 6000))

UA = {"User-Agent": "nsf-coa/1.0 (https://heurekaskills.com)"}
S  = requests.Session(); S.headers.update(UA)

def months_before(d, n):
    y, m = d.year, d.month - n
    while m <= 0: m += 12; y -= 1
    last = [31, 29 if (y % 4 == 0 and (y % 100 != 0 or y % 400 == 0)) else 28,
            31, 30, 31, 30, 31, 31, 30, 31, 30, 31][m - 1]
    return dt.date(y, m, min(d.day, last))

CUTOFF = months_before(ASOF, WINDOW)
EARLY  = CUTOFF - dt.timedelta(days=GRACE)

def fold(s):
    """Compare names without accents, case or punctuation. Aragón == Aragon."""
    s = unicodedata.normalize("NFKD", s or "")
    return re.sub(r"[^a-z]", "", "".join(c for c in s if not unicodedata.combining(c)).lower())

def crossref(**p):
    for attempt in range(5):            # Crossref intermittently serves an HTML error page
        r = S.get("https://api.crossref.org/works", params=p, timeout=60)
        if r.ok and "json" in (r.headers.get("content-type") or ""):
            return r.json()["message"]
        time.sleep(2 * (attempt + 1))
    sys.exit(f"Crossref did not return JSON after 5 tries: {r.status_code} {r.text[:120]}")

def pubdate(item):
    dp = ((item.get("published") or {}).get("date-parts") or [[]])[0]
    if not dp: return None
    y = dp[0]; m = dp[1] if len(dp) > 1 else 12; d = dp[2] if len(dp) > 2 else 28
    try: return dt.date(y, m, d).isoformat()
    except ValueError: return dt.date(y, m, 1).isoformat()

# Crossref tokenises the query, so a compound surname searches for each part separately:
# 'Orenes-Vera' returns 50,503 works because Vera is common, and 'van der Schaar' returns
# 355,714. Probe each non-particle token and search on whichever is rarest -- Orenes returns
# 134, Schaar 326 -- then filter on the whole surname.
PARTICLES = {"van", "von", "de", "del", "della", "der", "den", "di", "da", "dos", "du",
             "la", "le", "ter", "bin", "al", "of", "y"}
cands = [t for t in re.split(r"[\s\-']+", FAMILY) if t and t.lower() not in PARTICLES] or [FAMILY]
if len(cands) > 1:
    cands.sort(key=lambda t: crossref(**{"query.author": t, "rows": 0,
                                         "filter": f"from-pub-date:{EARLY},until-pub-date:{ASOF}"}
                                      )["total-results"])
QUERY = cands[0]

# A depositor may file 'van der Schaar' whole or leave the particles in the given name, so
# accept the surname with its particles stripped as well.
SURNAMES = {fold(FAMILY), fold(" ".join(cands))}

hits, cursor, scanned, total = {}, "*", 0, 0
while True:
    m = crossref(**{"query.author": QUERY, "rows": 200, "cursor": cursor,
                    "filter": f"from-pub-date:{EARLY},until-pub-date:{ASOF}"})
    total = m["total-results"]
    if not m["items"]: break
    for it in m["items"]:
        if any(fold(a.get("family")) in SURNAMES for a in (it.get("author") or [])):
            hits[it["DOI"].lower()] = it
    scanned += len(m["items"]); cursor = m.get("next-cursor")
    if not cursor or scanned >= MAX_SCAN: break
overflow = scanned >= MAX_SCAN and scanned < total

# The surname scan is the only route that finds work the person never claimed, and it is
# also the one that fails on a common surname. Anchor on the ORCID as well, always: it
# costs one call and it is the only evidence that survives an overflow.
if ORCID:
    cur = "*"
    while cur:
        m = crossref(filter=f"orcid:{ORCID},from-pub-date:{EARLY},until-pub-date:{ASOF}",
                     rows=200, cursor=cur)
        for it in m["items"]: hits.setdefault(it["DOI"].lower(), it)
        cur = m.get("next-cursor") if m["items"] else None

def mine(item):
    return [a for a in (item.get("author") or []) if fold(a.get("family")) in SURNAMES]

def gtoks(s): return [t for t in re.split(r"[\s.\-]+", fold(s)) if t]

def given_ok(a):
    """Initial-compatible: 'M.' matches 'Margaret'. Weak evidence on its own."""
    g, me = fold(a.get("given")), fold(GIVEN)
    return bool(g) and bool(me) and g[0] == me[0]

def given_spelled(a):
    """First given name spelled out on both sides and equal, later tokens compatible.
    'Juan L.' and 'Juan Luis' are one person; 'W.' and 'Wei' are not evidence."""
    a_t, me_t = gtoks(a.get("given")), gtoks(GIVEN)
    if not a_t or not me_t or len(a_t[0]) < 2 or len(me_t[0]) < 2 or a_t[0] != me_t[0]:
        return False
    return all(a_t[i].startswith(me_t[i]) or me_t[i].startswith(a_t[i])
               for i in range(1, min(len(a_t), len(me_t))))

def cokey(a): return (fold(a.get("family")), fold(a.get("given"))[:1])

certain, byname, maybe, other = {}, {}, {}, {}
for doi, it in hits.items():
    ms = mine(it)
    if ORCID and any(ORCID in (a.get("ORCID") or "") for a in ms):
        certain[doi] = it                       # identifier evidence
    elif any(given_spelled(a) for a in ms) and not overflow:
        byname[doi] = it                        # full given name, spelled out and equal
    elif any(given_spelled(a) or given_ok(a) for a in ms):
        maybe[doi] = it                         # initial-compatible only
    else:
        other[doi] = it                         # a different person with this surname

# Grow outward from the confirmed set: a surname-and-initial match that shares a co-author
# with a confirmed work is the same person often enough to accept. Only through papers small
# enough for co-authorship to mean something -- sharing one name with a 1000-author
# collaboration is not evidence, and letting it count attributed a 1006-author paper to the
# wrong Patel.
certain.update(byname)
SMALL = 25
network = {cokey(a) for it in certain.values() if len(it.get("author") or []) <= SMALL
           for a in it["author"]}
for _ in range(2 if not overflow else 0):
    for doi in list(maybe):
        au = maybe[doi].get("author") or []
        if len(au) > SMALL: continue
        if network & {cokey(a) for a in au} - {(fold(FAMILY), fold(GIVEN)[:1])}:
            certain[doi] = maybe.pop(doi)
            network |= {cokey(a) for a in certain[doi]["author"]}

# If the scan completed and turned up nobody else with this surname, then within this
# window the surname identifies one person and an initial is enough. Rare surnames --
# Mavalvala, Martonosi -- otherwise lose every work bylined 'N. Mavalvala', and losing
# work under-reports conflicts, which is the failure that matters.
sole = (not overflow) and not other
if sole:
    certain.update(maybe); maybe = {}

accepted = {d: i for d, i in certain.items() if (pubdate(i) or "") >= CUTOFF.isoformat()}
edge     = {d: i for d, i in certain.items() if d not in accepted}
others   = sorted({(a.get("given") or "?") for it in other.values() for a in mine(it)})

print(f"subject            {FAMILY}, {GIVEN}" + (f"  (ORCID {ORCID})" if ORCID else "  (no ORCID iD given)"))
print(f"Table 4 window     {CUTOFF} to {ASOF}   ({WINDOW} months)")
print(f"Crossref query     {QUERY!r}" + ("" if QUERY == FAMILY else f"  (rarest token of {FAMILY!r})"))
print(f"Crossref scanned   {scanned} of {total} works, {len(hits)} carry the surname {FAMILY!r}")
if overflow:
    print(f"!! {QUERY!r} is too common to enumerate: stopped at {MAX_SCAN} of {total}.")
    print(f"!! Name evidence is switched off; only ORCID-anchored works are attributed.")
    if not ORCID:
        sys.exit(f"!! No ORCID iD given, so nothing can be attributed to this person with "
                 f"confidence. Supply COA_ORCID, or build Table 4 from your CV.")
print(f"attributed         {len(certain)}  ({len(accepted)} inside the window,"
      f" {len(edge)} in the {GRACE}-day grace band)")
print(f"  on the spelled-out given name alone  {len(byname)}   -> check these")
if sole:
    print(f"  no other {FAMILY!r} publishes in this window, so initial-only bylines were accepted")
print(f"same surname, initial only, unresolved  {len(maybe)}   -> listed, not counted")
print(f"same surname, other people in this window  {len(other)} works by {others[:12]}")

json.dump({"family": FAMILY, "given": GIVEN, "orcid": ORCID, "surnames": sorted(SURNAMES),
           "asof": ASOF.isoformat(), "cutoff": CUTOFF.isoformat(), "early": EARLY.isoformat(),
           "overflow": overflow, "scanned_total": total, "scanned": scanned,
           "accepted": {d: {"date": pubdate(i), "title": (i.get("title") or [""])[0],
                            "type": i.get("type"), "author": i.get("author") or []}
                        for d, i in accepted.items()},
           "edge": {d: {"date": pubdate(i), "title": (i.get("title") or [""])[0]}
                    for d, i in edge.items()},
           "byname_only": sorted(byname), "other_people": others, "sole_bearer": sole,
           "unresolved": {d: {"date": pubdate(i), "title": (i.get("title") or [""])[0]}
                          for d, i in maybe.items()}},
          open("coa_works.json", "w"), ensure_ascii=False)

Build the Table 4 co-author rows

# coa_rows.py -- accepted works -> Table 4 'A:' rows, in Last, First, Middle Initial form.
import json, re, unicodedata, datetime as dt

W = json.load(open("coa_works.json"))
ME = (W["family"], W["given"])

def fold(s):
    s = unicodedata.normalize("NFKD", s or "")
    return re.sub(r"[^a-z]", "", "".join(c for c in s if not unicodedata.combining(c)).lower())

def tidy(s):
    return re.sub(r"\s+", " ", unicodedata.normalize("NFC", s or "")).strip(" ,;")

STREET = re.compile(r"\b\d{1,6}\s+\w+\s+(street|st|avenue|ave|road|rd|drive|dr|boulevard|"
                    r"blvd|lane|ln|way|court|ct)\b|\b[A-Z]{1,2}\d{1,2}[A-Z]?\s*\d[A-Z]{2}\b"
                    r"|\b\d{5}(-\d{4})?\b", re.I)

def affiliation(raw):
    """NSF: may include a location, must not include a street address, 255 characters."""
    parts = [p for p in (tidy(x) for x in re.split(r"\s*[;|]\s*|,(?=\s*[A-Z])", raw or "")) if p]
    parts = [p for p in parts if not STREET.search(p)]
    return tidy(", ".join(dict.fromkeys(parts)))[:255]

def coa_name(a):
    fam, giv = tidy(a.get("family")), tidy(a.get("given"))
    giv = re.sub(r"\.(?=[^\s.])", ". ", giv)                 # 'S.E.' -> 'S. E.'
    toks = [t for t in re.split(r"\s+", giv) if t]
    first = toks[0] if toks else ""
    mid = " ".join(t if t.endswith(".") else t[0] + "." for t in toks[1:])
    sfx = tidy(a.get("suffix"))
    fam = f"{fam} {sfx}" if sfx else fam
    return tidy(f"{fam}, {first} {mid}") if first else fam

def fullness(given, family):
    g, f = tidy(given), tidy(family)
    return (len(g), sum(1 for c in g + f if ord(c) > 127))

def gtoks(s): return [t for t in re.split(r"[\s.\-]+", fold(s)) if t]
def first_len(s): t = gtoks(s); return len(t[0]) if t else 0
def compatible(x, y):
    a, b = gtoks(x), gtoks(y)
    if not a or not b: return False
    for i in range(min(len(a), len(b))):
        if not (a[i].startswith(b[i]) or b[i].startswith(a[i])): return False
    return True

# The senior person is filed under more spellings than the one you typed: Crossref carries
# 'van der Schaar, M.' and 'Schaar, Mihaela v. d.' for the same author. Recognising only the
# exact form puts the subject into their own Table 4 as their own conflict.
SUR = set(W["surnames"])
def is_subject(a):
    return fold(a.get("family")) in SUR and compatible(a.get("given"), ME[1])

people, groups, selves = {}, {}, set()
for doi, w in W["accepted"].items():
    for a in w["author"]:
        if a.get("name") and not a.get("family"):            # a consortium, not a person
            groups.setdefault(tidy(a["name"]), []).append(doi); continue
        if is_subject(a):
            selves.add(coa_name(a)); continue                 # the senior person themself
        orc = (a.get("ORCID") or "").rsplit("/", 1)[-1]
        key = orc or (fold(a.get("family")), fold(a.get("given")))
        p = people.setdefault(key, {"name": coa_name(a), "orcid": orc, "aff": "", "last": "",
                                    "aff_date": "", "n": 0, "given_raw": tidy(a.get("given"))})
        p["n"] += 1
        if w["date"] and w["date"] > p["last"]:
            p["last"] = w["date"]                             # Last Active: newest joint work
        aff = affiliation((a.get("affiliation") or [{}])[0].get("name", ""))
        # Take the newest affiliation that exists. Overwriting a known affiliation with the
        # blank a later publisher deposited loses the only answer there was.
        if aff and w["date"] >= p["aff_date"]:
            p["aff"], p["aff_date"] = aff, w["date"]
        # Prefer the fullest spelling, and among equals the one that kept its diacritics:
        # 'Aragón' and 'Aragon' are one deposit spelled two ways.
        if fullness(a.get("given"), a.get("family")) > fullness(p["given_raw"], p["name"]):
            p["name"], p["given_raw"] = coa_name(a), tidy(a.get("given"))

# One person still splits into several rows: an ORCID on one deposit and none on the next,
# 'Aragón' against 'Aragon', 'Juan L.' against 'Juan Luis'. Merge inside a surname only when
# the given names are compatible token by token, and never merge two different ORCIDs --
# those are, by assertion, two people.
merged = 0
for fam in {fold(p["name"].split(",")[0]) for p in people.values()}:
    grp = sorted([k for k, p in people.items() if fold(p["name"].split(",")[0]) == fam],
                 key=lambda k: (not people[k]["orcid"], -len(people[k]["given_raw"])))
    for k in list(grp):
        if k not in people: continue
        for j in list(grp):
            if j == k or j not in people or k not in people: continue
            a, b = people[k], people[j]
            if a["orcid"] and b["orcid"] and a["orcid"] != b["orcid"]: continue
            if not compatible(a["given_raw"], b["given_raw"]): continue
            # 'Abbott, B.' and 'Abbott, B. P.' are compatible and may be two people. Merge on
            # initials alone only when an ORCID vouches for one side.
            initials_only = min(first_len(a["given_raw"]), first_len(b["given_raw"])) < 2
            if initials_only and not (a["orcid"] or b["orcid"]): continue
            a["n"] += b["n"]; a["orcid"] = a["orcid"] or b["orcid"]
            if b["last"] > a["last"]: a["last"] = b["last"]
            if b["aff"] and b["aff_date"] > a["aff_date"]: a["aff"], a["aff_date"] = b["aff"], b["aff_date"]
            if not a["aff"] and b["aff"]: a["aff"], a["aff_date"] = b["aff"], b["aff_date"]
            people.pop(j); merged += 1

rows, notes = [], []
for k, p in sorted(people.items(), key=lambda kv: fold(kv[1]["name"])):
    first = p["name"].split(", ")[-1].split(" ")[0] if ", " in p["name"] else ""
    if not first:
        notes.append((p["name"], "no given name in the publisher's deposit"))
    elif len(first.rstrip(".")) == 1:
        notes.append((p["name"], "given name deposited as an initial only"))
    if not p["aff"]:
        notes.append((p["name"], "no organizational affiliation in any deposit"))
    if not p["orcid"]:
        notes.append((p["name"], "no ORCID -- matched on name, may be two people or one"))
    rows.append({"code": "A:", "name": p["name"], "aff": p["aff"], "optional": "",
                 "last": dt.date.fromisoformat(p["last"]).strftime("%m/%d/%Y") if p["last"] else "",
                 "n_works": p["n"]})

# Table 1 is the senior person's own affiliations in the last 12 months. The only thing a
# publication record can offer is the affiliation they printed on recent work, which lags a
# move by a year or more -- so it is a proposal, not an answer.
def a_year_before(d):
    try: return d.replace(year=d.year - 1)
    except ValueError: return d.replace(year=d.year - 1, day=28)   # 29 February

asof = dt.date.fromisoformat(W["asof"])
twelve = a_year_before(asof).isoformat()
own = {}
for doi, w in W["accepted"].items():
    if not w["date"] or w["date"] < twelve: continue
    for a in w["author"]:
        if is_subject(a):
            aff = affiliation((a.get("affiliation") or [{}])[0].get("name", ""))
            if aff:
                # 'Department of Computer Science, Princeton University, Princeton, NJ' and
                # 'Princeton University' are one affiliation. Key on the segment naming the
                # organisation; keep the fullest spelling.
                org = next((p for p in aff.split(", ") if re.search(
                    r"universit|institut|college|laborator|academy|hospital|school|"
                    r"foundation|center|centre|corporation|company|inc\b|llc\b", p, re.I)), aff)
                cur = own.get(fold(org))
                if not cur or len(aff) > len(cur[0]):
                    own[fold(org)] = (aff, max(w["date"], cur[1] if cur else ""))
table1 = [{"name": f"{tidy(ME[0])}, {tidy(ME[1])}" if i == 0 else "", "aff": aff, "last": ""}
          for i, (aff, d) in enumerate(sorted(own.values(), key=lambda v: v[1], reverse=True))]
# Table 1 always carries the senior person's name, even when no recent deposit says where
# they are. A blank affiliation is a compliant answer; a Table 1 with no name is not.
if not table1:
    table1 = [{"name": f"{tidy(ME[0])}, {tidy(ME[1])}", "aff": "", "last": ""}]

sizes = sorted(((len(w["author"]), d) for d, w in W["accepted"].items()), reverse=True)
json.dump({"rows": rows, "notes": notes, "consortia": groups, "table1": table1,
           "self_spellings": sorted(selves),
           "largest_works": [{"doi": d, "n_authors": n, "title": W["accepted"][d]["title"][:90]}
                             for n, d in sizes[:5]]},
          open("coa_table4a.json", "w"), ensure_ascii=False)
print(f"accepted works                {len(W['accepted'])}")
print(f"rows merged as one person     {merged}")
print(f"Table 4 'A:' rows             {len(rows)}")
print(f"  carrying an ORCID           {sum(1 for p in people.values() if p['orcid'])}")
print(f"  with no affiliation         {sum(1 for r in rows if not r['aff'])}")
print(f"consortium author entries     {len(groups)}")
print(f"name spellings treated as you {len(selves)}  {sorted(selves)}")
print(f"Table 1 affiliations, last 12 months  {len(table1)}")
for n, d in sizes[:3]:
    print(f"  largest single contributor: {n:>4} authors  {d}")

Four things this had to be taught, each of which produced a wrong file first:

• Do not overwrite a known affiliation with a later blank. Publishers differ: on 10.1145/3674151 all 33 authors carry an affiliation, and on 10.1016/j.future.2024.04.060 none of the 128 do. Taking "the affiliation on the newest paper" loses everything the earlier deposits knew.
• De-duplicate on the identifier first, then on compatible names, never on initials alone. Keying only on the ORCID left thirteen duplicate rows on one real record, because a person with an iD on one deposit and none on the next becomes two people. Merging Abbott, B. into Abbott, B. P. when neither carries an ORCID would collapse people who may be two. Splitting a person over-reports a conflict, which costs nothing; merging two people under-reports one, which is the actual compliance risk. When in doubt, split.
• Prefer the accented spelling. Aragón and Aragon are the same deposit spelled two ways, and the accented one is the person's name.
• A family with no given is a name, not a defect. Mononyms and deposits that put the whole name in family produce a one-part row. Write it as deposited and flag it; there is nothing to invent.

Table 4 C: — project collaborators, from NSF's own award record

A: is co-authorship. C: is collaboration on projects — funded awards, graduate research — and no bibliographic source has it. NSF's own award API does, for the NSF half of it.

Two traps, both confirmed against the live service:

• offset is 0-based, although NSF documents its default as 1. Starting a paging loop at offset=1, which the documentation invites, silently drops the first award. It cost one award and one collaborator on the worked example here.
• printFields is accepted and ignored. The service returns its full record whatever you ask for.

pdPIName matches the person in either role, so this finds awards where they are the PI and awards where they are a co-PI on someone else's.

# coa_awards.py -- Table 4 'C:' rows from NSF's award record.
import json, re, datetime as dt, requests

W = json.load(open("coa_works.json"))
FAMILY, GIVEN = W["family"], W["given"]
ASOF   = dt.date.fromisoformat(W["asof"]); CUTOFF = dt.date.fromisoformat(W["cutoff"])
S = requests.Session(); S.headers["User-Agent"] = "nsf-coa/1.0 (https://heurekaskills.com)"

def mdy(s):
    try: return dt.datetime.strptime(s, "%m/%d/%Y").date()
    except (TypeError, ValueError): return None

def a_year_before(d):
    try: return d.replace(year=d.year - 1)
    except ValueError: return d.replace(year=d.year - 1, day=28)   # 29 February

awards, offset = [], 0
while True:
    page = S.get("https://api.nsf.gov/services/v1/awards.json",
                 params={"pdPIName": f"{GIVEN} {FAMILY}", "rpp": 25, "offset": offset},
                 timeout=60).json().get("response", {}).get("award", [])
    awards += page
    if len(page) < 25: break
    offset += 25

def person(s):
    """'Julia B Hirschberg (Former) julia@cs.columbia.edu' -> ('Hirschberg, Julia B.', email)"""
    s = re.sub(r"\((?:Former|Principal Investigator)\)", " ", s or "")
    toks = [t for t in s.split() if t]
    email = next((t for t in toks if "@" in t), "")
    toks = [t for t in toks if "@" not in t]
    if not toks: return None, ""
    fam, giv = toks[-1], toks[:-1]
    first = giv[0] if giv else ""
    mid = " ".join(t if t.endswith(".") else t[0] + "." for t in giv[1:])
    return re.sub(r"\s+", " ", f"{fam}, {first} {mid}").strip(" ,"), email

live, seen = 0, {}
for a in awards:
    start, exp = mdy(a.get("startDate")), mdy(a.get("expDate"))
    if exp and exp < CUTOFF: continue                 # the project ended before the window
    if start and start > ASOF: continue
    live += 1
    names = list(a.get("coPDPI") or [])
    if f"{a.get('piLastName','')}".lower() != FAMILY.lower():
        names.append(" ".join(x for x in [a.get("piFirstName"), a.get("piLastName"),
                                          a.get("piEmail")] if x))
    for n in names:
        name, email = person(n)
        if not name or name.split(",")[0].lower() == FAMILY.lower(): continue
        r = seen.setdefault(name, {"code": "C:", "name": name, "aff": "", "optional": email,
                                   "last": "", "awards": []})
        r["awards"].append(a.get("id"))
        if not r["aff"]: r["aff"] = (a.get("awardeeName") or "")[:255]
        end = exp.strftime("%m/%d/%Y") if exp and exp < ASOF else ""   # blank means ongoing
        if end > r["last"]: r["last"] = end

rows = sorted(seen.values(), key=lambda r: r["name"].lower())
own_org = sorted({(a.get("awardeeName") or "") for a in awards
                  if (mdy(a.get("expDate")) or ASOF) >= a_year_before(ASOF)} - {""})
json.dump({"rows": rows, "n_awards": len(awards), "n_live": live, "own_org": own_org},
          open("coa_table4c.json", "w"), ensure_ascii=False)

print(f"NSF awards naming {GIVEN} {FAMILY}   {len(awards)}   ({live} overlapping the window)")
print(f"Table 4 'C:' rows                 {len(rows)}")
print(f"awardee organizations, last 12 months  {own_org}")

Column D is the template's optional email-and-department field, for disambiguating common names. The C: rows carry the address NSF itself published on the award. The A: rows leave it blank — a publisher deposit has no reliable current email, and a stale one in a conflict-of-interest document is worse than an empty cell.

This covers NSF awards only. Collaboration on a DOE, NIH, foundation or internal project is equally reportable and equally invisible here.

Tables 1, 2, 3 and 5 — what only the senior person can supply

Nothing in any bibliographic or award source identifies a spouse, a Ph.D. thesis advisee or an editorial board seat. Left alone, those three tables ship empty — which is honest, and which the summary says out loud. To complete them, write coa_manual.json beside the scripts; the writer merges it. Omit a key and that table stays empty.

{
  "table1": [{"name": "Martonosi, Margaret", "aff": "Princeton University, Princeton, NJ", "last": ""}],
  "table2": [{"code": "R:", "name": "Surname, Given M.", "relationship": "Spouse", "optional": "", "last": ""}],
  "table3": [{"code": "G:", "name": "Surname, Given M.", "aff": "University of Somewhere", "optional": ""},
             {"code": "T:", "name": "Surname, Given M.", "aff": "", "optional": ""}],
  "table5": [{"code": "B:", "name": "Surname, Given M.", "aff": "Some University", "journal": "Journal Name", "last": ""}]
}

A T: row with an empty affiliation is correct where a former student's current employer is genuinely unknown — NSF asks for it "if known". The summary names every blank so none of them reaches a submission unnoticed.

Write the workbook

Three things openpyxl will not do for you, and each one fails silently:

• insert_rows moves neither the table ranges nor the data validations. Insert 200 rows into Table 4 and the table still claims A51:E56, the A:/C: dropdown still covers five cells, and every date validation still points at the old block. Rebuild both from the final geometry.
• Inserted rows carry no formatting. Copy the style cell by cell from the last original data row, or the new rows arrive without the template's borders.
• Clearing a cell's value leaves its hyperlink. The template's Table 3 example carries a live mailto:example@example.com. Clear cell.hyperlink too, or that link ships to a federal agency inside your document.

Insert bottom-up. Rows added below a table never move the tables above it, so every header row computed from the untouched file stays valid until its own turn.

# coa_write.py -- write the filled COA workbook.
import json, os, re, warnings, datetime as dt
from copy import copy
import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation
warnings.filterwarnings("ignore", message="Unknown extension is not supported")

A4 = json.load(open("coa_table4a.json"))
C4 = json.load(open("coa_table4c.json"))
MANUAL = json.load(open("coa_manual.json")) if os.path.exists("coa_manual.json") else {}

# A person who is both a co-author and a co-PI is one row. Co-authorship is the harder
# evidence, so it keeps the row and 'A:' keeps the code.
seen = {r["name"].lower() for r in A4["rows"]}
both = [r["name"] for r in C4["rows"] if r["name"].lower() in seen]
t4 = A4["rows"] + [r for r in C4["rows"] if r["name"].lower() not in seen]

t1 = MANUAL.get("table1") or (A4["table1"] +
     [{"name": "", "aff": o, "last": ""} for o in C4["own_org"]
      if not any(o.lower() in r["aff"].lower() for r in A4["table1"])])
rows = {"1": t1, "2": MANUAL.get("table2", []), "3": MANUAL.get("table3", []),
        "4": t4, "5": MANUAL.get("table5", [])}

FIELDS = {"1": ["", "name", "aff", "last"],
          "2": ["code", "name", "relationship", "optional", "last"],
          "3": ["code", "name", "aff", "optional"],
          "4": ["code", "name", "aff", "optional", "last"],
          "5": ["code", "name", "aff", "journal", "last"]}
DATE_COL = {"1": 4, "2": 5, "4": 5, "5": 5}

wb = openpyxl.load_workbook("coa_template.xlsx")
ws = wb.active
tbl = {str(ws.cell(int(t.ref.split(":")[0][1:]), 1).value).strip(): t for t in ws.tables.values()}
geom = {n: (int(t.ref.split(":")[0][1:]), int(t.ref.split(":")[1][1:]),
            ord(t.ref.split(":")[1][0]) - 64) for n, t in tbl.items()}

links = 0
for n, (hdr, last, ncol) in geom.items():
    for r in range(hdr + 1, last + 1):
        for c in range(1, ncol + 1):
            cell = ws.cell(r, c)
            if cell.hyperlink is not None:
                cell.hyperlink = None; links += 1
            cell.value = None

extra = {n: max(0, len(rows[n]) - (geom[n][1] - geom[n][0])) for n in geom}
for n in sorted(geom, reverse=True):
    if not extra[n]: continue
    hdr, last, ncol = geom[n]
    ws.insert_rows(last + 1, extra[n])
    for r in range(last + 1, last + 1 + extra[n]):
        ws.row_dimensions[r].height = ws.row_dimensions[last].height
        for c in range(1, ncol + 1):
            ws.cell(r, c)._style = copy(ws.cell(last, c)._style)

shift = {n: sum(extra[m] for m in geom if geom[m][0] < geom[n][0]) for n in geom}
final = {n: (geom[n][0] + shift[n], geom[n][1] + shift[n] + extra[n], geom[n][2]) for n in geom}

# Rebuild every range and validation from the final geometry. The orphan dropdown at A33 is
# dropped: it offers 'P:' for postdoctoral sponsors, which the template's own instructions
# retire, it belongs to no table, and after insertion it would land inside Table 2.
CODES = {"2": '"R:"', "3": '"G:,T:"', "4": '"A:,C:"', "5": '"B:,E:"'}
ws.data_validations.dataValidation = []
date_sq = []
for n, (hdr, last, ncol) in final.items():
    t = tbl[n]
    t.ref = f"A{hdr}:{chr(64+ncol)}{last}"
    if t.autoFilter is not None: t.autoFilter.ref = t.ref
    if n in CODES:
        dv = DataValidation(type="list", formula1=CODES[n], allowBlank=True)
        dv.add(f"A{hdr+1}:A{last}"); ws.add_data_validation(dv)
    if n in DATE_COL:
        date_sq.append(f"{chr(64+DATE_COL[n])}{hdr+1}:{chr(64+DATE_COL[n])}{last}")
dv = DataValidation(type="date", operator="between", formula1=1, formula2=402133, allowBlank=True,
                    errorTitle="Invalid Date Format",
                    error="The date entered is invalid. Please enter a date using the "
                          "following format mm/dd/yyyy or m/d/yyyy.")
for sq in date_sq: dv.add(sq)
ws.add_data_validation(dv)

written = 0
for n, (hdr, last, ncol) in final.items():
    for i, row in enumerate(rows[n]):
        r = hdr + 1 + i
        for c, field in enumerate(FIELDS[n][:ncol], start=1):
            v = row.get(field, "") if field else ""
            if not v: continue
            cell = ws.cell(r, c)
            if c == DATE_COL.get(n) and re.fullmatch(r"\d{2}/\d{2}/\d{4}", str(v)):
                cell.value = dt.datetime.strptime(v, "%m/%d/%Y").date()
                # The instruction rows require mm/dd/yyyy; the shipped example cells render
                # mm-dd-yy. Follow the instruction, which is the requirement.
                cell.number_format = "mm/dd/yyyy"
            else:
                cell.value = str(v)[:255]
            if cell.font.sz and cell.font.sz > 10:          # NSF: 10pt or smaller
                f = copy(cell.font); f.sz = 10; cell.font = f
            written += 1

wb.save("COA.xlsx")
print(f"example rows cleared, live hyperlinks removed  {links}")
print("rows inserted   " + "  ".join(f"T{n}+{extra[n]}" for n in sorted(extra) if extra[n]))
print("final ranges    " + "  ".join(f"T{n} {tbl[n].ref}" for n in sorted(final)))
print(f"cells written   {written}")
print("table rows      " + "  ".join(f"T{n}={len(rows[n])}" for n in sorted(rows)))
print(f"co-author and co-PI merged into one row  {len(both)}  {both}")
json.dump({"table_rows": {n: len(rows[n]) for n in rows}, "both": both,
           "final_ranges": {n: tbl[n].ref for n in final}, "hyperlinks_removed": links},
          open("coa_written.json", "w"), ensure_ascii=False)

Check it before uploading

Run this every time. It is the difference between a file that looks right and one that is.

# coa_check.py -- prove the workbook is submittable.
import re, sys, zipfile, warnings, openpyxl
warnings.filterwarnings("ignore", message="Unknown extension is not supported")

z = zipfile.ZipFile("COA.xlsx")
blob = b"".join(z.read(n) for n in z.namelist())
ws = openpyxl.load_workbook("COA.xlsx").active
tbl = {str(ws.cell(int(t.ref.split(":")[0][1:]), 1).value).strip(): t for t in ws.tables.values()}
span = {n: tuple(int(x[1:]) for x in t.ref.split(":")) for n, t in tbl.items()}

def check(label, ok, detail=""):
    print(f"{'PASS' if ok else 'FAIL'}  {label}{'  ' + detail if detail else ''}")
    return ok

# 'TODO' as a word, not as a substring: Synge Todo is a real co-author on a real record, and
# a case-insensitive search for 'todo' flags him as a placeholder.
PLACEHOLDER = re.compile(rb"\bTBD\b|\bTODO\b|\bFIXME\b|\[\s*(fill|insert|enter|your|name)\b"
                         rb"|Surname, Given|Organization, City, ST|Journal Name")

good = True
good &= check("five tables present", len(tbl) == 5,
              " ".join(f"T{n} {tbl[n].ref}" for n in sorted(tbl)))
good &= check("no Alphaman example rows", b"Alphaman" not in blob and b"Samplename" not in blob)
good &= check("no example mailto: relationship", b"mailto:" not in blob)
good &= check("no placeholder text anywhere in the file", not PLACEHOLDER.search(blob))
for n in sorted(tbl):
    if n == "1": continue
    hdr, last = span[n]
    dv = [d for d in ws.data_validations.dataValidation
          if d.type == "list" and str(d.sqref) == f"A{hdr+1}:A{last}"]
    good &= check(f"Table {n} row-code dropdown covers every data row", bool(dv),
                  str(dv[0].sqref) if dv else "missing")
hdr, last = span["4"]
names = [(c.row, c.value) for c in ws["B"][hdr:last] if c.value]
good &= check("Table 4 has rows", bool(names), f"{len(names)} rows")
if names:
    good &= check("Table 4 filled rows all sit inside its range",
                  max(r for r, _ in names) <= last, f"last row {max(r for r, _ in names)}")
    mono = [v for _, v in names if ", " not in v]
    good &= check("every Table 4 name is Last, First or a deposited mononym",
                  all(v.strip() for _, v in names), f"{len(mono)} one-part name(s)")
    good &= check("every Table 4 row carries a code",
                  all(ws.cell(r, 1).value in ("A:", "C:") for r, _ in names))
    dates = [ws.cell(r, 5).value for r, _ in names if ws.cell(r, 5).value]
    good &= check("Last Active cells are real dates formatted mm/dd/yyyy",
                  all(hasattr(d, "year") for d in dates)
                  and all(ws.cell(r, 5).number_format == "mm/dd/yyyy"
                          for r, _ in names if ws.cell(r, 5).value),
                  f"{len(dates)} dated, {len(names) - len(dates)} blank")
    good &= check("inserted rows kept the template border style",
                  ws.cell(last, 2).border.left.style is not None)
sys.exit(0 if good else 1)

Write the summary

The workbook is the deliverable and carries nothing but data. Everything the senior person needs to know about it goes in a file beside it — what was proposed, what was inferred and from where, what was decided for them, and what was left open and why. That is what turns one shot they have to clean up into an exchange they can steer.

# coa_summary.py -- the summary lives beside the workbook, never inside it.
import json, collections, datetime as dt

W  = json.load(open("coa_works.json"))
A4 = json.load(open("coa_table4a.json"))
C4 = json.load(open("coa_table4c.json"))
OUT = json.load(open("coa_written.json"))
by = collections.Counter(n for _, n in A4["notes"])

L = [f"# COA draft for {W['family']}, {W['given']} — what to check before you submit", "",
     f"Built {dt.date.today()} against the NSF COA template downloaded the same day. "
     f"Table 4 window: {W['cutoff']} to {W['asof']} (48 months).",
     "", "**This is a draft. NSF holds you, not this file, responsible for it.**", "",
     "## Proposed", ""]
L += [f"- Table 1 — {OUT['table_rows']['1']} affiliation row(s), read off your own publications "
      f"in the last 12 months and your NSF awardee organization.",
      f"- Table 4 — {OUT['table_rows']['4']} rows: {len(A4['rows'])} co-authors (`A:`) from "
      f"{len(W['accepted'])} works, {OUT['table_rows']['4'] - len(A4['rows'])} project collaborators "
      f"(`C:`) from {C4['n_live']} NSF awards overlapping the window.",
      "", "## Inferred", "",
      f"- Works: Crossref, searching the surname {W['family']!r} and the ORCID iD, and keeping "
      f"the {len(W['accepted']) + len(W['edge'])} that are yours.",
      f"- Attribution: {len(W['accepted']) + len(W['edge']) - len(W['byname_only'])} works matched "
      f"on an identifier; {len(W['byname_only'])} on your spelled-out given name alone."
      + (" Nobody else publishes under this surname in this window, so bylines carrying only "
         "your initial were accepted as well." if W.get("sole_bearer") else ""),
      f"- Names come from the publisher's deposit, which keeps given and family apart, so "
      f"Last, First, Middle Initial is read, not guessed.",
      f"- Affiliations are the newest one a publisher deposited for that person.", "",
      "## Decided on your behalf", ""]
L += [f"- One row per person. Two records were merged only when the given names were compatible "
      f"and no two different ORCIDs were involved.",
      f"- A co-author who is also an NSF co-PI gets one row coded `A:`"
      + (f" — {'; '.join(OUT['both'])}." if OUT["both"] else "."),
      (f"- {len(A4['self_spellings'])} spellings of your own name were recognised as you and "
       if len(A4['self_spellings']) != 1 else "- One spelling of your own name was recognised "
       "as you and ")
      + f"kept out of Table 4: {'; '.join(A4['self_spellings'])}. Confirm each one is really "
      f"you and not a namesake.",
      f"- `Last Active` is the date of your most recent joint work; blank means ongoing.",
      f"- Dates are written mm/dd/yyyy, which the template's instruction rows require. The "
      f"template's own example cells display mm-dd-yy; the instruction wins.",
      f"- The template's example rows were cleared, including the live `mailto:` link behind "
      f"one of them, and its orphan `P:` dropdown was dropped.", "",
      "## Left blank, deliberately", ""]
L += [f"- **Table 2** (relationships precluding review) — nothing in a publication record "
      f"identifies a spouse, relative or business partner. Only you can fill this.",
      f"- **Table 3** (Ph.D. advisors and all Ph.D. thesis advisees) — lifetime scope, in no "
      f"bibliographic source. A blank Table 3 is almost certainly wrong for you. Master's and "
      f"undergraduate students do not belong here; postdoctoral sponsors are no longer reported.",
      f"- **Table 5** (editorial boards and co-editors, 24 months) — in no bibliographic source. "
      f"The template's definition is narrow: editors-in-chief and people who perform or manage "
      f"editing, not Editorial Advisory, International Advisory or Scientific Editorial Boards. "
      f"NSF also says not to list editors you met by submitting a paper to them.",
      f"- **Optional column D** on the `A:` rows — publisher deposits carry no reliable current "
      f"email. The `C:` rows carry the email NSF published on the award.",
      f"- {by['no organizational affiliation in any deposit']} of {len(A4['rows'])} co-author rows "
      f"have no affiliation. NSF asks for it \"if known\"; blank is compliant and a guess is not.",
      *([] if A4["table1"][0]["aff"] else
        ["- **Your own Table 1 affiliation** — no publication in the last 12 months and no "
         "current NSF award carried one. Your name is there; fill the organization in."]), ""]
L += ["## Outstanding — your call", ""]
if W.get("overflow"):
    L += [f"- **The surname search did not complete.** Crossref holds {W['scanned_total']} works "
          f"in this window matching {W['family']!r} and the scan stopped early. Only works "
          f"carrying your ORCID iD were attributed. Table 4 is incomplete; finish it from "
          f"your CV."]
if W["edge"]:
    L += [f"- **{len(W['edge'])} work(s) just outside the 48-month boundary.** The template counts "
          f"collaboration, not publication, and says the publication date may be later. Add them "
          f"if the work happened inside the window:"]
    L += [f"  - {d} — {v['date']} — {v['title'][:80]}" for d, v in sorted(W["edge"].items())]
if A4["largest_works"] and A4["largest_works"][0]["n_authors"] >= 50:
    b = A4["largest_works"][0]
    L += [f"- **Hyperauthorship.** {b['n_authors']} of your co-author rows come from one work "
          f"({b['doi']}). NSF asks for co-authors \"with collaboration\"; whether every author of "
          f"a large multi-institution paper collaborated with you is your judgement. "
          f"Over-reporting costs nothing; under-reporting is the compliance risk."]
if W["unresolved"]:
    L += [f"- **{len(W['unresolved'])} work(s) with your surname and a compatible initial that "
          f"could not be tied to you.** Not counted. Review:"]
    L += [f"  - {d} — {v['title'][:80]}" for d, v in sorted(W["unresolved"].items())]
if W["other_people"]:
    L += [f"- Other people publishing under {W['family']!r} in this window were excluded on their "
          f"given name: {', '.join(W['other_people'][:20])}."]
L += [f"- **Preprint-only collaborations are not covered.** arXiv and Zenodo DOIs are registered "
      f"with DataCite, which Crossref does not index. Where a preprint has a published version "
      f"the co-authors are the same; where it does not, add them by hand.",
      f"- **Non-NSF funded collaboration is not covered.** DOE, NIH, foundation and internal "
      f"projects are equally reportable and are not in NSF's award record.",
      f"- **Thesis and preliminary-exam committee service** is real academic contact that none of "
      f"the five tables names. Defensible either way; the template does not settle it.",
      f"- **Unpublished and in-progress collaboration** is invisible to any bibliographic source, "
      f"and it is squarely inside NSF's 48-month window. Check it against your own records.", ""]
if by:
    L += ["## Per-row caveats", ""] + [f"- {n}: {c} row(s)" for n, c in by.most_common()] + [""]
L += ["## Before you upload", "",
      "- Upload the `.xlsx` to Research.gov as a Collaborators and Other Affiliations Single Copy "
      "Document. Never a PDF — that path is Grants.gov, for the PD/PI only, and additional senior "
      "personnel still need the `.xlsx` in Research.gov afterwards.",
      "- One COA per senior person. This file covers one.",
      "- Do not change column widths or the font type. Rows may be inserted; the instruction rows "
      "at the top may be deleted.", ""]
open("COA-summary.md", "w").write("\n".join(L))
print(f"COA.xlsx          {OUT['table_rows']}")
print(f"COA-summary.md    {len(L)} lines, {sum(1 for l in L if l.startswith('## '))} sections")

Run the whole thing:

for s in coa_spec coa_harvest coa_rows coa_awards coa_write coa_check coa_summary; do
  .venv/bin/python $s.py || exit 1
done

How this behaves on your name

Measured 2026-08-27 across twelve researchers, over a 2022-08-27 to 2026-08-27 window. Which of three regimes you land in is decided by how many people share your surname, not by how well known you are:

• Rare surname, nobody else publishing in the window. Martonosi returns 39 surname hits, Mavalvala 65, and in both cases every hit is the same person. Bylines carrying only an initial are accepted, and Table 4 is as complete as Crossref is.
• Shared surname, scan completes. Tardos returns 75 hits and one other person; Aragón returns 3,336 and many. Works carrying your spelled-out given name are attributed, works carrying only an initial are listed for review rather than counted, and you should expect to add some by hand.
• Surname too common to enumerate. The surname query reports 122,538 matches for Patel in this window, 140,020 for González, 2,373,908 for Wang, and the scan stops at 6,000. Name evidence is switched off and only ORCID-anchored works are attributed; with no ORCID iD the run refuses outright. Both are the correct output — a Table 4 assembled from namesakes is confidently wrong, which is worse than absent.

A compound surname is searched on its rarest part. Crossref tokenises the query, so Orenes-Vera matches 50,503 works because Vera is common, and van der Schaar matches 355,714. Searching Orenes returns 134 and Schaar 326, and filtering those on the whole surname finds the person. Without that step both of these early- and mid-career researchers would hit the overflow refusal and get nothing.

Hyperauthorship is a separate axis, and it is where the row count gets large. The same run gives Martonosi 208 Table 4 rows and Mavalvala 3,637, because 2,207 of hers come from one gravitational-wave paper. The workbook is written correctly either way; whether all 2,207 are collaborators is a question the summary puts to the senior person rather than answering.

What this cannot determine

State these to the senior person every time; they are the reason the output is a draft.

• Tables 2, 3 and 5 in full. Family, personal and business relationships; Ph.D. advisors and thesis advisees; editorial board and co-editor service. None is in any bibliographic source and none can be inferred.
• Unpublished collaboration. The window is 48 months and manuscripts take longer than that. Work in progress, work in revision and work that never published are all reportable and all invisible here.
• Preprint-only collaborations. arXiv and Zenodo DOIs are DataCite registrations, and Crossref does not index them. On the worked example, checked against a preprint-aware index, 13 in-window arXiv preprints carried co-authors who appear on no Crossref-registered work in the window. Where a preprint has a published version the co-authors are the same; where it does not, they have to be added by hand.
• Non-NSF project collaboration. C: here covers NSF awards only.
• Whether a namesake is you. Where the surname is common the code refuses rather than guesses, and the refusal is the answer.
• Current affiliations. What a publisher deposited is where the person was when the paper was accepted, not where they are now.
• Whether a 128-author paper is a collaboration. NSF's wording is co-authors "with collaboration". The skill counts them all and says so; the senior person decides.

Try it

Data. Two public sources, no account and no key:

• NSF's COA template, a US Government work not subject to domestic copyright. Confirmed reachable 2026-08-27.
• The Crossref record for GW150914 — Abbott et al., Observation of Gravitational Waves from a Binary Black Hole Merger, Phys. Rev. Lett. 116, 061102 (2016), 10.1103/physrevlett.116.061102. The paper NSF's LIGO was built to write, and the messiest real author block in the literature: 1012 authors, most of them bylined as initials, and hundreds carrying an invisible U+2009 thin space inside the name. Crossref metadata is distributed under CC0.

Run — cold, in an empty directory:

python3 -m venv .venv
.venv/bin/pip -q --disable-pip-version-check install openpyxl requests
.venv/bin/python - <<'PY'
import re, sys, time, zipfile, unicodedata, warnings, datetime as dt
from copy import copy
import requests, openpyxl
from openpyxl.worksheet.datavalidation import DataValidation
warnings.filterwarnings("ignore", message="Unknown extension is not supported")
UA = {"User-Agent": "nsf-coa/1.0 (https://heurekaskills.com)"}

def get_json(url):                  # Crossref intermittently serves an HTML error page
    for attempt in range(5):
        r = requests.get(url, headers=UA, timeout=90)
        if r.ok and "json" in (r.headers.get("content-type") or ""):
            return r.json()
        time.sleep(2 * (attempt + 1))
    sys.exit(f"{url} did not return JSON after 5 tries: {r.status_code}")

# 1. NSF's template is the specification. Read it out of the file, not out of a guide.
open("coa_template.xlsx", "wb").write(requests.get(
    "https://www.nsf.gov/bfa/dias/policy/coa/coa_template.xlsx", headers=UA, timeout=60).content)
wb = openpyxl.load_workbook("coa_template.xlsx"); ws = wb.active
tbl = {str(ws.cell(int(t.ref.split(":")[0][1:]), 1).value).strip(): t for t in ws.tables.values()}
instr = [c.value for c in ws["A"] if c.row < 15 and isinstance(c.value, str) and c.value.strip()]
print(f"template   {len(tbl)} tables, {len(instr)} instruction rows, sheet {ws.title!r}")
for n in sorted(tbl):
    print(f"  Table {n}  {tbl[n].ref:<9} internal name {tbl[n].name!r}")
assert len(tbl) == 5 and sorted(tbl) == list("12345"), "the template no longer ships five numbered tables"
assert any("no longer required to be reported" in i for i in instr)
assert any("48 months" in i for i in instr) and any("last 12 months" in i for i in instr)

# 2. A real, named, public author block: GW150914, Abbott et al., PRL 116, 061102 (2016).
DOI = "10.1103/physrevlett.116.061102"
m = get_json(f"https://api.crossref.org/works/{DOI}")["message"]
au = m["author"]
thin = [a for a in au if "\u2009" in (a.get("given") or "") + (a.get("family") or "")]
print(f"\ncrossref   {DOI}")
print(f"  authors {len(au)}   initials-only given name "
      f"{sum(1 for a in au if len((a.get('given') or '').rstrip('.')) <= 2)}")
print(f"  non-ASCII author entries {sum(1 for a in au if not (str(a.get('family')) + str(a.get('given'))).isascii())}"
      f", of which {len(thin)} only because a U+2009 THIN SPACE sits inside the name, e.g. {thin[0]['given']!r}")

def tidy(s): return re.sub(r"\s+", " ", unicodedata.normalize("NFC", s or "")).strip(" ,;")
def coa_name(a):
    fam, giv = tidy(a.get("family")), re.sub(r"\.(?=[^\s.])", ". ", tidy(a.get("given")))
    t = [x for x in giv.split() if x]
    mid = " ".join(x if x.endswith(".") else x[0] + "." for x in t[1:])
    return tidy(f"{fam}, {t[0]} {mid}") if t else fam

rows = []
for a in au:
    if not a.get("family"): continue                 # a consortium line, not a person
    aff = tidy((a.get("affiliation") or [{}])[0].get("name", ""))[:255]
    rows.append({"code": "A:", "name": coa_name(a), "aff": aff, "last": "02/11/2016"})
assert not any(re.search("[\u00a0\u2009\u202f]", r["name"]) for r in rows), "invisible space survived"
assert all(re.match(r"^[^,]+(, \S.*)?{{BODY}}quot;, r["name"]) for r in rows)
print(f"  Table 4 rows built {len(rows)}   first {rows[0]['name']!r}   accented {sum(1 for r in rows if not r['name'].isascii())}")

# 3. Write it. Clear every shipped example row and its hyperlink, insert bottom-up, then
#    rebuild the table ranges and the dropdowns -- openpyxl moves neither.
need = {"1": 0, "2": 0, "3": 0, "4": len(rows), "5": 0}
geom = {n: (int(t.ref.split(":")[0][1:]), int(t.ref.split(":")[1][1:]),
            ord(t.ref.split(":")[1][0]) - 64) for n, t in tbl.items()}
links = 0
for n, (h, l, c) in geom.items():
    for r in range(h + 1, l + 1):
        for col in range(1, c + 1):
            if ws.cell(r, col).hyperlink is not None: ws.cell(r, col).hyperlink = None; links += 1
            ws.cell(r, col).value = None
extra = {n: max(0, need[n] - (geom[n][1] - geom[n][0])) for n in geom}
for n in sorted(geom, reverse=True):
    if not extra[n]: continue
    h, l, c = geom[n]
    ws.insert_rows(l + 1, extra[n])
    for r in range(l + 1, l + 1 + extra[n]):
        for col in range(1, c + 1): ws.cell(r, col)._style = copy(ws.cell(l, col)._style)
shift = {n: sum(extra[m] for m in geom if geom[m][0] < geom[n][0]) for n in geom}
final = {n: (geom[n][0] + shift[n], geom[n][1] + shift[n] + extra[n], geom[n][2]) for n in geom}
ws.data_validations.dataValidation = []
for n, (h, l, c) in final.items():
    tbl[n].ref = f"A{h}:{chr(64+c)}{l}"
    if tbl[n].autoFilter is not None: tbl[n].autoFilter.ref = tbl[n].ref
    if n != "1":
        dv = DataValidation(type="list", allowBlank=True,
                            formula1={"2": '"R:"', "3": '"G:,T:"', "4": '"A:,C:"', "5": '"B:,E:"'}[n])
        dv.add(f"A{h+1}:A{l}"); ws.add_data_validation(dv)
h = final["4"][0]
for i, r in enumerate(rows):
    ws.cell(h + 1 + i, 1).value = r["code"]; ws.cell(h + 1 + i, 2).value = r["name"]
    ws.cell(h + 1 + i, 3).value = r["aff"]
    ws.cell(h + 1 + i, 5).value = dt.date(2016, 2, 11)
    ws.cell(h + 1 + i, 5).number_format = "mm/dd/yyyy"
wb.save("COA.xlsx")

blob = b"".join(zipfile.ZipFile("COA.xlsx").read(f) for f in zipfile.ZipFile("COA.xlsx").namelist())
out = openpyxl.load_workbook("COA.xlsx").active
t4 = {str(out.cell(int(t.ref.split(":")[0][1:]), 1).value).strip(): t.ref for t in out.tables.values()}["4"]
lastrow = int(t4.split(":")[1][1:])
dv4 = [d for d in out.data_validations.dataValidation if str(d.sqref) == f"A{h+1}:A{lastrow}"]
print(f"\nworkbook   Table 4 {t4}   {len(rows)} rows written, {links} live hyperlink(s) removed")
for label, ok in [
    ("no Alphaman example rows",            b"Alphaman" not in blob and b"Samplename" not in blob),
    ("no example mailto: relationship",     b"mailto:" not in blob),
    ("no placeholder text",                 not re.search(rb"\bTBD\b|\bTODO\b|\[\s*(fill|insert|your)\b", blob)),
    ("dropdown covers the last data row",   bool(dv4)),
    ("last row is inside the table range",  out.cell(lastrow, 2).value == rows[-1]["name"]),
    ("inserted rows kept the border style", out.cell(lastrow, 2).border.left.style is not None),
    ("accents survived the round trip",     out.cell(h + 1 + rows.index(next(r for r in rows if not r["name"].isascii())), 2).value.isascii() is False),
]:
    print(f"  {'PASS' if ok else 'FAIL'}  {label}")
    assert ok, label
print("\nTables 2, 3 and 5 are empty on purpose. No publication record names a spouse, a")
print("Ph.D. advisee or an editorial board, and a guess in a compliance document is worse")
print("than a blank. They are yours to fill.")
PY

Expect.

Invariants — true regardless of what NSF or Crossref return, so a failure means this page is wrong:

• The template ships five numbered tables and its instruction rows still say postdoctoral and graduate advisors are no longer reported, that Table 4 is 48 months, and that Table 1 is 12 months.
• Table 4's internal name is TableD and Table 3's is TableC — the internal names are not in table order, so keying on them puts co-authors in the advisee table.
• No U+00A0, U+2009 or U+202F survives into a name. Publisher deposits carry them, and a federal submission must not.
• After writing, the file contains no Alphaman, no mailto: and no placeholder token, the A:/C: dropdown covers every inserted row, the last written row sits inside the table range, inserted rows carry the template's borders, and accented names round-trip.

Observed 2026-08-27 against Crossref and NSF — these move if either reindexes, so a mismatch is drift to investigate, not a bug:

template   5 tables, 13 instruction rows, sheet 'NSF COA Template'
  Table 1  A16:D21   internal name 'TableA'
  Table 2  A27:E32   internal name 'TableD23'
  Table 3  A37:D44   internal name 'TableC'
  Table 4  A51:E56   internal name 'TableD'
  Table 5  A63:E68   internal name 'TableD5'

crossref   10.1103/physrevlett.116.061102
  authors 1012   initials-only given name 680
  non-ASCII author entries 337, of which 311 only because a U+2009 THIN SPACE sits inside the name, e.g. 'B.\u2009P.'
  Table 4 rows built 1011   first 'Abbott, B. P.'   accented 27

workbook   Table 4 A51:E1062   1011 rows written, 1 live hyperlink(s) removed
  PASS  no Alphaman example rows
  PASS  no example mailto: relationship
  PASS  no placeholder text
  PASS  dropdown covers the last data row
  PASS  last row is inside the table range
  PASS  inserted rows kept the border style
  PASS  accents survived the round trip

Tables 2, 3 and 5 are empty on purpose. No publication record names a spouse, a
Ph.D. advisee or an editorial board, and a guess in a compliance document is worse
than a blank. They are yours to fill.

The 1012th author entry is the LIGO Scientific Collaboration itself, deposited with a name and no family. It is not a person and does not belong in a COA, which is why the row count is 1011.

Where this ages

• NSF's schedule, not ours. Re-read the template and the senior-personnel-documents page before trusting the table layout, the windows or the upload route. coa_spec.py exists so that check is one command.
• PAPPG 24-1 will be superseded. NSF has signalled a replacement called Guidance on Financial Assistance. When it arrives, every date on this page needs re-checking.
• Crossref's author metadata improves. More ORCIDs and more affiliations mean fewer blank cells and fewer name-only attributions. Nothing here breaks; the summary just gets shorter.

Sources

Read 2026-08-27.

• NSF COA template — the authoritative specification, served from NSF's file host. Where it and the PAPPG disagree, it wins, because it is what gets submitted.
• Senior personnel documents — NSF's guidance page, last updated 2026-04-13. Source of the Research.gov and Grants.gov upload rules and of the instruction not to list editors met through a paper submission.
• PAPPG 24-1 Chapter II — II.D.2.h(iii) is the COA requirement; footnotes 35 and 36 carry the Editorial Board definition and the font-size allowance.
• PAPPG 24-1 Exhibit II-2 — Potentially Disqualifying Conflicts of Interest. Names "current or former collaborator" and "former Ph.D. student/advisor" with no carve-out for large collaborations.
• PAPPG policy page — confirms PAPPG 24-1 in force with supplements NSF 26-200 (2025-12-08) and NSF 26-202 (2026-01-22).
• Crossref REST API — no sign-up, and Crossref states that "almost none of the metadata is subject to copyright, and you may use it for any purpose".
• NSF Award Search API — public award records.
• ORCID Public APIs Terms of Service — the non-commercial licence grant that keeps ORCID's API out of this page.

NSF's template, its instructions and the PAPPG are US Government works and not subject to domestic copyright. Short phrases are quoted where the exact wording is the requirement; the rest of this page is original.