Private Investment Valuation Acknowledgment Loop: Where Things Stand

Status

Where this stands

Discovery, now grounded in the transcript. The 2026-08-23 working session (the project's source of truth) confirmed the loop's existence and shape, so the essentials are no longer speculative — only the design specifics remain open. This is still not a committed design, and the future form is not part of the core build.

Confirmed by transcript

Clients return a sheet with column M updated to the latest values and a trades yes/no confirmation — "the exact sheet they end up returning to us that day." It does nothing unless they confirm a change, then flows in "to give us things to import into the system." A "not listed" trigger flags securities not listed anywhere as needing client input. Today it is an email + Excel round trip. The direction both sides favor is a SharePoint form the client fills in directly — no email exchange, data landing straight in Trailmark's data source.

What we actually know

Everything here is a direct quote or close paraphrase from the 2026-08-23 working session with Brad Jones. Nothing is inferred or filled in.

Brad Jones — on the round-trip timing

"So we would send this out, let's say, tonight. This file would be created now tonight with saying the 25th. They let their day happen tomorrow. They'd send it to us before 5pm tomorrow. We'd upload this portion of it into our system to then be reflected on the following day's reports if there are any changes."

Brad Jones — on frequency and purpose

"Private investments don't change every day... usually it's just once a week, once a quarter sometimes. They just need a place to tell us and acknowledge it. Part of the SOC 1 stuff, largely, for us."

Brad Jones — on today's sender model

"...we've linked everything, all the attachments through the file path into one spot, so long as that's always the same. Then we're able to change who's the sender... It's not going to be dictated by an individual anymore. It's going to be saved in a central location, right?"

What the transcript confirms

Confirmed	What it means
They return the same sheet, column M updated	The client edits column M with the latest values and confirms whether there were any trades (yes/no) — "the exact sheet they end up returning to us that day." It only flows into Trailmark's system once they confirm a change.
It's a round trip, not one-way	Trailmark sends a file out; the recipient has until 5pm the next business day to send something back.
It feeds the following report cycle	What comes back is uploaded and reflected in the next night's reports, not the same night's.
Low frequency	Weekly or quarterly, not nightly — private investments reprice rarely.
Compliance-driven	Brad frames it as largely a SOC 1 acknowledgment requirement, not primarily a data-accuracy mechanism.

A related, separate question — how the file leaves FundGuard

Today Brad opens the FundGuard application and clicks Download to pull this file — a manual portal action, distinct from the already-confirmed automated SFTP feed that delivers the main nightly reports (Trial Balance, Positions, NAV Listing) into Blob Storage. Do not assume these are the same delivery mechanism; nothing so far confirms the private-investment export is included in that existing SFTP feed.

FundGuard's own public materials

FundGuard describes itself as an "API-first" platform with real-time programmatic integration (positions, tax lots, IBOR/ABOR data), and states it integrates with upstream/downstream systems "via APIs or file uploads." This suggests an API path may exist for this export, but nothing found is specific enough to confirm this particular file is exposed that way today. References: The FundGuard Integration Ecosystem, Investment Accounting, IBOR Solution.

Not verifiable from outside

Endpoint specs, authentication, and exact data coverage for FundGuard's API are client-portal-only — this needs a direct question to FundGuard (account contact or support), not something confirmable through public search.

Recommended next step

Since the main nightly SFTP feed from FundGuard already works, ask FundGuard whether the private-investment export can be added to that same feed, or exposed via their API — a reasonable ask given their own stated integration model.

What we checked in the existing code — and ruled out

The hypothesis was that the Email Generator workbook might already handle this. We extracted and read every routine in its VBA project directly (not summarized secondhand) to check.

Module	Routine	What it actually does
Module1	BuildTradeEmailFromSheet	Builds the outbound Outlook draft from worksheet cells. Outbound only — no inbound handling.
Module2	RenameTodaysDownloads_ToFolderFromB38	Renames/organizes files already downloaded from the portal. Not a reply-processing routine.
Module3	CreateAndMove_SourceFolderToArchive	End-of-day archival of the working folder. Not related.
Module4	Import_NAV_Listing_A_to_BY_AllRowsWithData	Imports NAV Listing data from a portal-downloaded file — upstream fund-accounting data, not a private-investment reply.

Result

The Private Investment Valuation Acknowledgment Loop is not implemented anywhere in Email Generator. Today it is a fully manual email exchange, per Brad's description.

Reference only — if the interim email loop were ever automated

This is not the pattern for this feature. The agreed future target is a SharePoint form (next section), not an automated email round-trip. These notes are kept only as a fallback reference — relevant solely if the interim email loop were ever automated instead of the manual hand-upload used today. Checked directly against Microsoft Learn.

Office 365 Outlook connector — shared-mailbox trigger

A dedicated trigger exists specifically for shared mailboxes: "When a new email arrives in a shared mailbox (V2)" — distinct from the owner-scoped "When a new email arrives (V3)." If the interim loop were ever automated, this could receive replies in the same shared Trailmark mailbox already used by Email Generator, without inventing a new mailbox concept. It is not the direction this feature is heading. References: Trigger a cloud flow based on email properties, Attachments from a shared mailbox.

Verified gotcha — not assumed

Both "Only with Attachments" and "Include Attachments" on this trigger default to No. Left at the default, attachment content comes through as null and any downstream action that tries to save it (for example, into a SharePoint library) fails with a 400 Bad Request. Both must be explicitly set to Yes for a working attachment-intake flow.

Filtering fields confirmed available

Folder, To, CC, From, Importance, Has Attachment, and Subject Filter are all real, documented filter properties on the trigger — so a flow can be scoped narrowly (only emails from a known sender, with an attachment, matching a subject pattern) rather than firing on every email that reaches the shared mailbox.

The future target: a SharePoint form — for discussion, not committed

The direction both sides favor is a SharePoint form the fund or administrator fills in directly — they update the latest values and confirm trades yes/no, and the data lands straight in Trailmark's data source with no email exchange, ready for the next night's run. Step 1 records today's manual reality (the email + hand-upload loop this form would replace); the rest sketch the future form intake. This is a shape to react to, not a proposal to build, and it is not part of the core build.

Future — SharePoint form Today — manual

1

Today: manual email round-trip (being replaced). Trailmark emails the sheet out; the client edits column M and confirms trades yes/no; a person hand-uploads the returned file into the next night's run. Part of SOC 1 controls (weekly or quarterly acknowledgment, mostly private investments). This is the current reality the form below would replace, not the future design.

2

Future: the fund or administrator opens a SharePoint form. Instead of receiving an email, the party opens a simple SharePoint form to update the position — no attachment, no reply to parse.

3

They update the values and confirm trades yes/no. The same column-M update and yes/no trades confirmation captured today in the returned sheet, entered straight into the form fields.

4

Data lands in SharePoint automatically — no email. On submit, the update flows straight into a SharePoint list or library in Trailmark's data source. No mailbox trigger, no attachment to save, no email exchange at all.

5

Flows into the next night's run. The captured update — only when the client confirms a change — is picked up by the following night's report run, exactly as the hand-uploaded file is today.

This future SharePoint form — client self-service data entry — is the only future dashboard or self-service item on the roadmap; nothing else of that kind is planned. Exact form fields, how each fund or administrator is granted access, and which import it feeds are open — see the next section. If cost comes up, it is covered in a separate document.

Open items — must be confirmed with Brad, not guessed

The transcript confirmed the essentials — the column-M update, the trades yes/no return, the "not listed" trigger, and the overall loop shape. What remains below is genuinely open, not the whole feature.

Open item	What's needed
Who is the recipient	A private-investment sponsor or administrator, per Brad — but exactly who, how many, and whether they're the same list as Notification Assignments or a wholly separate roster is unknown.
What file goes out, and in what format	Unknown whether it's an Excel file, PDF, or something else, and what data it contains.
Data-completeness: "security not listed anywhere"	The transcript confirms a trigger where a security isn't listed anywhere and staff need client input. How that data-completeness check is surfaced and resolved — in the form and in the flow — is still to design.
Where replies land today	Same mailbox Brad uses for sending, a different one, or a personal inbox? Relevant only to the interim email loop, not to the SharePoint-form target.
How the manual upload works today	Brad says "we'd upload this portion of it into our system" — what system, and what does that upload consist of concretely (paste values into a workbook? re-run an import?)?
Which report or workbook this actually feeds	Confirmed it affects the following night's reports, but not which specific workbook or tab gets updated.
Draft-and-review, or something else	The outbound half might not need the same draft/review/send gate Email Generator uses — unconfirmed.
SOC 1 documentation requirements	Brad frames this as compliance-driven. Any automation must preserve whatever audit trail SOC 1 currently relies on — what that evidence trail looks like today is unknown.
Can FundGuard automate this specific export	FundGuard's public materials describe an API-first model, but nothing found confirms this specific export is available via API or could be added to the existing SFTP feed. Needs a direct question to FundGuard, not an assumption.