Phase 2 — Macro Migration Technical Spec
What we are migrating from the report macros, and why — based on a direct inspection of the VBA source in each of the five .xlsm macros (extracted and verified from xl/vbaProject.bin, not inferred), with the technology chosen for each in-scope macro and an hour-level scope estimate.
What we're doing
The in-scope report macros move to Azure-native services and Microsoft 365 automation. The three daily-file macros become Office Scripts driven by Power Automate against the real Excel calculation engine — the same formulas, run in the cloud rather than on a desktop. The Email Generator macro becomes a Power Automate flow that builds the email as a draft in the shared reporting mailbox via Microsoft Graph, with an approval before anything sends. The Modern Capital portal macro is out of scope — the client is retiring it.
Why Office Scripts for the refresh: the workbook formulas have to be evaluated by the real Excel engine, and Excel's app-only Graph API does not support that — so the refresh runs as an Office Script signed in under a dedicated delegated service account. Everything else uses a service account or Managed Identity with no stored secret.
Main risk: Office Scripts and large files
Office Scripts drives the real Excel engine, but its ceiling is
data size — and these reports can be large. Verified limits (Microsoft Learn): 5 MB per request, 5 million cells per range, roughly 28.6 MB per Run-script parameter, a 120-second synchronous timeout, and 1,600 Run-script calls per user per day. The exposure is the cross-file pattern: Power Automate passes the source data into the script as a parameter, so a large workbook — many positions, a heavy recalculation — is exactly what can breach the request, parameter, or cell caps, or hit the timeout.
How we handle it — documented
Four Microsoft-documented ways to stay inside the limits: (1)
process in batches — split the automation into multiple Run-script calls in a Power Automate Do-until loop (Microsoft's "Write a large dataset" sample); (2)
optimise the script — batch the reads and writes and set calculation to manual during the run, then recalculate once; (3)
use Power Query for the heavy import — its data connections don't carry the Power Automate / Excel-web transfer limits, so it pulls the large positions data in, and Office Scripts is kept for the Excel-centric transforms; (4)
split the workbook per fund or report. Before cutover we run a validation spike against the
largest in-scope report to size which of these it needs.
One correction worth stating plainly: an app-only Azure Function is
not a fallback for the calculation itself. The Graph Excel workbook API supports
delegated access only (app-only is unsupported) — which is exactly why the refresh runs as Office Scripts under a delegated service account. App-only code can only manipulate the file's bytes, which does not recalculate formulas.
Where this fits in the nightly pipeline
The seven canonical steps of the nightly run. Phase 2 macro migration covers the compute-and-send steps (3–7); step 6 is the approval gate.
1Reports generated. Fund accounting produces the files, now delivered over SFTP.
2Renamed & sorted. Files move from Azure Storage into the SharePoint libraries, sorted into a folder per client.
3Workbooks updated. The reporting workbooks refresh with the night's data — same formulas — via Office Scripts under Power Automate.
4Reports created. The clean client copies (internal columns hidden) are produced and saved to SharePoint.
5Email drafted. The full email is built as a draft in the shared reporting mailbox via Microsoft Graph.
6Approved. For a Manual client, a person receives a Power Automate approval (preview plus a link to the draft) and approves; an Automatic client skips this step.
7Sent & archived. The system sends via Graph — on approval, or on its own for Automatic clients — then archives the files.
Macro-by-macro breakdown
1
Email Generator — Main Macros/Email Generator_V1.3.FEB26.xlsm.
Current behavior. Calls CreateObject("Outlook.Application") and deliberately calls .Display before setting .HTMLBody and DeferredDeliveryTime — done specifically so desktop Outlook merges the user's real signature into the draft ("Display first so Outlook inserts the default signature"). Never calls .Send — it leaves a draft for human review by design.
Blocker. No cloud API replicates the live desktop-signature merge into an editable draft the way .Display does.
Migration target. Microsoft Graph "Create message" builds the draft in the shared reporting@trailmark.us mailbox, called from a Power Automate cloud flow. One shared footer, stored in the Email Sender Settings SharePoint list, is injected server-side. For Manual clients the flow raises an approval (preview plus a link to the draft); on approval the system sends via Graph. Automatic clients send without approval. No one clicks Send in Outlook by hand.
2
PortalRpts_MC (Modern Capital) — Secondary Macros/PortalRpts_MACRO_MC_NewSync.xlsm.
Status: out of scope — not being migrated. The client is moving off the Modern Capital portal reports themselves (2026-08-23 transcript, L560), so this macro carries no work in this build. Its native-Excel PDF/chart rendering and PowerShell-zip behavior — once the trickiest part of this set — are moot, and the PDF-library choice they drove (PDFsharp/MigraDoc vs QuestPDF) is no longer relevant.
3
Connetic, Sweater & USVC daily-file macros — Connetic Daily File, Sweater FMV Daily File, USVC Trade/FMV Daily File.
Current behavior. All three follow the same pattern: read the newest matching positions file from the local Downloads folder (Environ("USERPROFILE")), import it, hide the internal columns, and save a clean client copy. No Outlook, no shell-out — pure data transformation.
Blocker. None. The only dependency is the local file path, which goes away once input files land in SharePoint/Azure Storage (Phase 1 covers this).
Migration target. Office Scripts running the same import-and-reshape steps against the workbook in SharePoint, orchestrated by Power Automate driving the real Excel engine; the refresh signs in as a delegated service account. These are near-identical, low-risk 1:1 ports of the reshape logic.
Summary table
Macro-specific work in-scope macros only
| Macro | Description | Technology we go with | Hours (low–high) |
|---|
| Email Generator | Drafts a report email in Outlook, deliberately opening it (.Display) so the desktop client merges the signature; never auto-sends. | Graph "Create message" + one shared footer (Email Sender Settings list) + approval, via a Power Automate cloud flow | 9–13 |
| PortalRpts_MC (Modern Capital) | Builds a formatted report plus a native Excel chart, exports to PDF, shells out to PowerShell to zip. | Out of scope — client is retiring the Modern Capital portal reports; not migrated. | — |
| Connetic Daily File | Reads newest file from local Downloads, hides internal columns, saves a clean copy. Pure data transform. | Office Scripts + Power Automate | 4–6 |
| Sweater FMV Daily File | Same pattern as Connetic. | Office Scripts + Power Automate | 4–6 |
| USVC Trade/FMV Daily File | Same pattern as Connetic. | Office Scripts + Power Automate | 4–6 |
| Subtotal, macro-specific work | 21–31 |
Plus 22–34 hours of cross-cutting work not tied to a single macro: solution setup (Power Automate flows / any Azure Functions) and identity & auth setup (8–12h), logging and alerting (4–6h), and side-by-side validation before cutover (10–16h) — broken out below. Total: 43–65 hours.
Scope estimate
Hours are engineering effort only — implementation, unit-level testing, and code review. Side-by-side validation against live nightly data (nothing replaces today's process until proven identical) is scoped separately below.
| Task | Hours (low–high) |
|---|
| Foundation: Power Automate flows + service-account / service-principal auth to Azure Storage/SharePoint (Managed Identity is used by the Function and Automation Account, not the flows), config, deployment | 8–12 |
| Connetic Daily File — port | 4–6 |
| Sweater FMV Daily File — port | 4–6 |
| USVC Trade/FMV Daily File — port | 4–6 |
| Email Generator — Entra app registration + Graph API permissions / admin consent | 2–3 |
| Email Generator — Graph "Create message" draft + approval implementation | 4–6 |
| Email Generator — shared footer (Email Sender Settings SharePoint list) + injection logic | 3–4 |
| Error handling, logging, alerting (Application Insights) across all four in-scope flows | 4–6 |
| Side-by-side validation against real nightly data, all four in-scope flows, before cutover | 10–16 |
| Total | 43–65 hours |
How the compute runs and authenticates
Every service runs as a managed Azure / Microsoft 365 service. The workbook calculations run on the real Excel engine via Office Scripts under Power Automate; the desktop signature merge is replaced by its outcome — one shared footer injected server-side via Graph. Licensing and recurring costs are covered in a separate document.
The workbook-refresh step signs in as a service account
Where the automation runs as an Azure resource — the email Function and the SFTP-window Automation Account — it authenticates with Managed Identity and no stored secret. Power Automate's own connections (Blob, SharePoint, Outlook) can't use Managed Identity; they run under a dedicated service account (Microsoft Entra ID Integrated) or a service principal. The step that refreshes the Excel workbooks is the delegated exception worth naming: Office Scripts under Power Automate drives the real Excel engine, which only runs when signed in as an actual account, so it runs under a dedicated delegated service account — a normal, supported setup.
Where files land
SFTP intake is new. Today there is no SFTP — files are pulled by hand from Fundguard or by email into OneDrive. The new process builds an SFTP endpoint on Azure and stores files in three SharePoint libraries.
Files
Fund accountingSource system
→
Azure Storage (SFTP)New — raw files land here
→
RAW data libraryOriginals, flat — untouched
→
Working libraryRenamed & sorted, a folder per client
→
Archive libraryOne library, year/date folders
Three libraries: RAW data (flat), Working (per client), and a single Archive (year and date folders, e.g. Archive / 2026 / 2026-07-14).