An updated overview of the process and the direction we've confirmed together, based on our working sessions and the documentation you've shared with us.
Process at a glance
Today
1
Reports generated
Fund accounting
2
Land in the SFTP
Dropped automatically; fetched by hand today
3
Workbooks updated
By macro, run by hand
4
Reports created
By macro, run by hand
5
Email drafted
By macro, run by hand
6
Reviewed
By your staff
7
Sent & archived
Sent by staff; archived by macro
becomes
New process
1
Reports generated
Fund accounting — no change
2
Renamed & sorted
Into a RAW data folder, then client folders — automatically
3
Workbooks updated
In SharePoint, same formulas
4
Reports created
Saved to SharePoint (PDF / CSV / ZIP)
5
Email drafted
Draft in the shared mailbox
6
Reviewed
By your staff — when set to Manual
7
Sent & archived
Sent per your setting; archived automatically
Dashed circles mark steps that don't change. Everything else moves from manual to automatic.
"Renamed & sorted" means: your fund accounting system's nightly SFTP delivery already lands in Azure Storage on its own (nobody downloads anything by hand). From there, files are copied into a RAW data folder you can see, renamed to your standard convention, and sorted into a per-client folder — all automatically, replacing the rename-and-move macro you run today.
Steps 6 and 7 follow your per-client setting: for clients set to Manual (the default), a person reviews and sends, exactly like today; for clients set to Automatic, the email goes out on its own. Either way, archiving now happens automatically to a SharePoint Archive folder — no more archive macro to run.
"Workbooks updated" means Trial Balance, Positions, NAV Listing, and Capstock Ledger — the same workbooks and the same formulas you use today — get refreshed with the night's new data automatically. Nobody opens Excel or picks a file from a dialog box; the calculations themselves don't change at all.
What's changing
Files move automatically from Azure Storage into SharePoint, sorted into a folder per client.
A cloud workflow (Power Automate) replaces the Excel macros that rename files, refresh workbooks, and build emails.
The Excel formulas you use today for calculations don't change. They still run, just automatically instead of by hand.
Every piece of day-to-day configuration — who gets which report, subject lines, templates — lives in SharePoint, where your team can already see and edit it, not buried in workbook cells.
Nobody's job changes at the review and send step. A person still checks every report and sends every email.
What we've confirmed so far
Based on our working sessions and the documentation you've shared with us:
Source files arrive through Azure Storage, which Trailmark already owns and has access to.
Calculations stay in Excel for now. Moving them into custom code is possible later if there's ever a reason to.
Email recipients, subject lines, and templates are managed entirely in SharePoint — editable by your staff, not by IT.
Reports now send from one shared Trailmark mailbox with one consistent signature, rather than each staff member's own — see "Worth knowing" below for why.
Whether each client's email sends automatically or waits for manual review is your choice, set per client in the list — manual by default.
Everything you configure — recipients, templates, send mode, client list — is a plain SharePoint list or folder your team edits directly, no developer or code involved.
Where things will be stored
Files
Fund accountingSource system
→
Azure StorageRaw files land here
→
SharePointRAW data — originals, you can see them
→
SharePointWorking — renamed & sorted by client
→
SharePointArchive — a dated folder per night, automatically
Files are renamed to your standard convention as they move into the Working library, and once the cycle is done they're archived automatically into a folder named by that night's date (e.g. Archive / 2026-07-14) — the same dated-folder layout you use today, just without running the rename or archive macro. The email draft pulls the finished report straight from the Working library.
Worked example: one night, one fund (USVC)
The complete flow for a single fund, step by step, so we can confirm it matches how USVC works today — the same seven steps as the process above. We picked USVC because it's one of the funds whose share price (NAV) goes right in the subject line, the most complete case. Please check the folder layout, recipients, subject, and attachments against your current setup.
AutomaticYour staff
1
Reports generated. That night's USVC files are produced by fund accounting and arrive in Azure Storage on their own via SFTP (nightly reports PDF and Excel, plus the trade FMV daily file).
2
Renamed & sorted. The files are copied into the RAW data folder in original form (you can see them there), renamed to your standard convention, and sorted into Working / USVC.
3
Workbook updated. The USVC reporting workbook refreshes with the night's data — same formulas as today — and the fund's per-share NAV is calculated.
4
Report created. The clean client report (renamed PDF + Excel, internal columns removed) is produced and saved into Working / USVC.
5
Email drafted. A draft is assembled in the shared mailbox: recipients, subject, and body from the SharePoint list; the day's NAV in the subject; the report files attached; the shared Trailmark signature added.
6
Reviewed. Because USVC is set to Manual, a person opens the draft and checks it. (A client set to Automatic skips this step.)
7
Sent & archived. The email is sent — by your staff for a Manual client, or on its own for an Automatic one — and USVC's files are then archived into a dated folder, Archive / 2026-07-14, automatically.
Where the files sit in SharePoint along the way
📁 RAW data/gryphonprod1.…usvc…created_2026_07_14_21_19_25.xlsx ← original, untouchedgryphonprod1.…usvc…created_2026_07_14_21_19_25.pdf📁 Working/📁 USVC/USVC Nightly Reports 7.14.2026.xlsx ← renamed to your conventionUSVC Nightly Reports 7.14.2026.pdfUSVC Trade FMV 7.14.2026.xlsx📁 Archive/📁 2026-07-14/…the night's USVC files, moved here automatically once done
Folder names and the renamed-file convention shown here are illustrative — please confirm they match your current layout.
How tonight's share price reaches the subject line
Tonight's NAV dataUSVC → $20.46 in the refreshed workbook
→
Your existing formulaUnchanged — the same one you use today
→
Into the subject line… — $20.46
The "workbook refresh" just puts tonight's numbers into the sheet. Your own formulas do the rest — nothing about how the figure is calculated changes.
Please find attached the USVC nightly reports for 7/14/2026. The fund's closing NAV per share is $20.46.
Please let us know if you have any questions.
Thank you, Trailmark Fund Solutions [shared signature]
Status
Draft — waiting for a person to review & send
Recipients and body shown reflect USVC's current setup, for you to verify nothing was dropped — please correct the body wording if it doesn't match what you send today. The $20.46 is an example figure; the real subject and body use that night's actual NAV and date, formatted exactly as they are now.
How we'll roll this out
We won't switch everything over at once. Each phase runs alongside today's process until we've confirmed it produces identical results — nothing gets turned off until its replacement is proven.
0
Setup, before Phase 1
Configuration moves from Excel into SharePoint
Before anything runs automatically, we set up the SharePoint lists and document libraries that replace today's worksheet cells and local folders — who gets which report, subject and body templates, the shared mailbox and signature, and the per-client folder structure. One-time setup work, not something that repeats each night.
1
Phase 1
Files get picked up, renamed, and sorted automatically
Your fund accounting system already delivers files into Azure Storage on its own — that part isn't changing and isn't something we're building. What Phase 1 adds: those files land in a visible RAW data folder, get renamed to your standard convention, and are sorted into a per-client Working folder — all automatically, replacing the rename-and-move macro you run today. Lowest-risk piece, and the foundation everything else builds on.
2
Phase 2
Workbooks refresh, reports generate, files get archived
This is where the night's numbers get calculated, so we'll run it side-by-side with today's manual process for a while and confirm it produces identical figures before relying on it alone — standard practice for anything touching client-facing values. It runs the very same Excel formulas you use today, driven by Microsoft's own Excel automation. Once the cycle is done, files are archived automatically into a dated folder (Archive / that night's date) — the same layout as today, no archive macro to run.
3
Phase 3
Emails get drafted automatically
Happens once the recipient/template lists and shared mailbox are set up. Drafts are built automatically, but still wait for a person to review and send them.
✓
Stays the same, always
Review and sending
No phase changes this. A person checks every report and sends every email, exactly like today.
Sending is a per-client choice you control from the list: Manual (the default — a person reviews and sends, exactly as today) or Automatic (the email goes out on its own). You can flip any single client between the two with one edit, whenever you're comfortable — manual stays the default so nothing changes unless you choose it.
Worth knowing
Emails now come from one shared address, not each person's own
Automated systems can't pull in a person's personal Outlook signature the way it happens when someone writes an email themselves — that only works inside a live desktop session. Rather than storing and maintaining an individual signature for every staff member, we've set this up so all automated reports send from a single shared Trailmark mailbox with one consistent signature, managed in one place. Practical effect: report emails will look consistent no matter who's covering for whom, and there's one signature to keep current instead of several.
The workbook-refresh step signs in as a service account
Everywhere else, the automation authenticates as itself with no stored password. The one exception is the step that refreshes your Excel workbooks: Microsoft's Excel automation only works when it's signed in as an actual account, so this piece runs under a dedicated Trailmark service account rather than a passwordless identity. It's a normal, supported setup — just worth naming, since it's the single spot that keeps a stored sign-in rather than none.
Cost
$180per year
One Power Automate license, assigned to a dedicated automation account rather than a specific staff member, covers this entire nightly workflow. Everything else runs on Microsoft services Trailmark already owns.
A couple of things to confirm
Cadence
Today's reports run on a nightly batch. Does that need to stay a strict once-a-night cycle, or would you want reports to update closer to real time as new data lands?
Folder layout
The layout we're proposing is the one shown in the example above: raw files land in a single RAW data folder (their names already carry the date, so no sub-folders needed there), get renamed and sorted into a Working folder per client, and archive into a dated folder per night. Does that match how you'd like things organized, or would you group them differently?
A feature we could add later
Separately, there's the back-and-forth where a fund or administrator sends their updated positions back to you before the next night's run. Down the road, that could become its own feature — for example a simple form those parties fill in, instead of the email exchange — so their updates land with you directly. It's not part of this build, just a future enhancement we'd be glad to explore when the time's right.
We'd suggest a short call to walk through these together, rather than more back-and-forth over email.
