Macro security audit
Harlow & Pine Insurance Agency
Every macro the agency relies on, and how to make it safe to run
In short
Your cyber insurance renewal asks how the agency controls macros. Today the honest answer is that it doesn't: the 12 macro-enabled files the agency relies on run because people unblock them, click Enable, or have switched the protection off, not because anyone checked the code. Four are high risk, including an add-in that loads on every computer from a folder anyone can change, and a mailing workbook with the mailbox password written into its code. The plan: a code-signing certificate in the agency's name, 10 files signed, the carrier's own signature trusted on one, one retired, and six settings your IT provider switches on so that only signed macros run.
12
files the agency relies on, of 37 macro-enabled files found
4
of them rated high risk
4 of 9
computers with the protection partly or fully off
0 → 11
files that run because they are signed and trusted: today, and after the plan
How the 12 files run
How Office treats each file today, and after the plan is in place.
Today
- Protection switched off
- Depends on someone clicking
- Trusted by Office's default
After the plan
Every file that still runs is signed, and the settings make sure nothing unsigned does.
The 9 computers
Nine computers, checked one by one. The settings on four of them switch the protection partly or fully off.
Today
- Rachel (Principal): Office's default: asks each time
- Ken (accounts): Office's default: asks each time
- Account manager 1: Trusts the whole shared drive
- Account manager 2: Trusts the whole shared drive
- Account manager 3: Trusts the whole shared drive
- Account manager 4: Office's default: asks each time
- Producer 1 (laptop): Allows all macros
- Producer 2 (laptop): Office's default: asks each time
- Front desk: Office's default: asks each time
- Office's default: asks each time 5
- Trusts the whole shared drive 3
- Allows all macros 1
After
- Rachel (Principal): Only signed macros run
- Ken (accounts): Only signed macros run
- Account manager 1: Only signed macros run
- Account manager 2: Only signed macros run
- Account manager 3: Only signed macros run
- Account manager 4: Only signed macros run
- Producer 1 (laptop): Only signed macros run
- Producer 2 (laptop): Only signed macros run
- Front desk: Only signed macros run
- Only signed macros run 9
The inventory and risk ratings
The 12 macro-enabled files the agency relies on, most serious first: who uses each one, what it does, how it runs today, and the plan for it. Open a file for its rating.
How each file is rated
Each file is rated on the three things that decide how much harm its code could do. Each scores 1 (low) to 3 (high); the total gives the rating.
What it can touch
- Only its own sheets
- Other files and folders
- Email, every computer, or a stored password
Where it comes from
- Built here, by someone known
- An outside company
- Unknown: downloaded, author not recorded
How it's maintained
- One owner, or the maker updates it
- One person, changes not recorded
- Nobody, or anyone can change it
3 or 4 is Low, 5 or 6 is Medium, 7 to 9 is High.
High Loss_Run_Summary.xlsmCombines carriers' claim histories into one summary for renewals. Used byAccount managersMonthly Runs todayWhole shared drive trusted PlanRetire
Where it is Shared drive, Renewals
What it can touch
Medium
Opens every file in a folder and copies from it
Where it comes from
High
A template downloaded from a website in 2018; author unknown
How it's maintained
High
Nobody
Score 8 of 9: High
Why it runs today Three computers trust the entire shared drive, so anything saved there runs without a check.
The plan Retire it. Its only job is copying claim totals, which formulas can do without macros; the quote for the plan includes a macro-free version.
High Agency_Tools.xlamAn add-in Ken wrote: a ribbon tab that formats policy numbers and dates. Used byEveryoneDaily, on all 9 computers Runs todayInstalled add-in, never checked PlanSign
Where it is Shared drive, Tools
What it can touch
High
Loads on all nine computers and can change any open workbook
Where it comes from
Low
Built by Ken
How it's maintained
High
Kept in a shared folder anyone can overwrite
Score 7 of 9: High
Why it runs today Office runs installed add-ins without asking, so whoever can change the file changes what runs.
The plan Sign it. A changed copy then won't load anywhere, and the setting that lets only signed add-ins load closes the gap.
High Client_Mailing.xlsmSends the renewal season letter to every client by email. Used byAccount managersTwice a year Runs todayWhole shared drive trusted PlanChange, then sign
Where it is Shared drive, Marketing
What it can touch
High
Sends email from the agency mailbox; the mailbox password is written into its code
Where it comes from
Low
Built by a former office manager
How it's maintained
High
Nobody since she left in 2022
Score 7 of 9: High
Why it runs today Three computers trust the entire shared drive, so anything saved there runs without a check.
The plan Take the password out of the code and change the mailbox password, then sign.
High Renewal_Tracker.xlsmLists policies renewing in the next 90 days and drafts the reminder emails. Used byAccount managersDaily Runs todayWhole shared drive trusted PlanChange, then sign
Where it is Shared drive, Renewals
What it can touch
High
Creates and sends emails through Outlook
Where it comes from
Low
Built by an account manager in 2020
How it's maintained
High
Four copies in use; anyone edits any of them
Score 7 of 9: High
Why it runs today Three computers trust the entire shared drive, so anything saved there runs without a check.
The plan Merge the four copies into one, then sign. After that, an edit removes the signature, so a change can't spread unseen.
Medium Premium_Finance_Calc.xlsmWorks out down payments and monthly payments for clients who finance a premium. Used byAccount managers, producersWeekly Runs todayEnable clicked each time PlanSign
Where it is Shared drive, Forms
What it can touch
Low
Only its own sheets
Where it comes from
Medium
From a premium finance company, unsigned
How it's maintained
High
The company no longer supports it
Score 6 of 9: Medium
Why it runs today Office asks, and people click Enable Content, as they would for any file.
The plan We read the code: it only calculates. Sign it, so the agency vouches for it.
Medium Producer_Pipeline.xlsmTracks prospects and quotes, and emails Rachel a summary every Friday. Used byProducersWeekly Runs todayAll macros allowed PlanSign
Where it is A producer's laptop
What it can touch
High
Sends email through Outlook
Where it comes from
Low
Built by a producer
How it's maintained
Medium
The producer alone; changes not recorded
Score 6 of 9: Medium
Why it runs today One laptop is set to run every macro in every file.
The plan Sign it, and move it to the shared drive so it's backed up.
Medium COI_Cover_Letter.dotmA Word template that fills in the cover letter sent with each certificate of insurance. Used byAccount managersDaily Runs todayEnable clicked each time PlanSign
Where it is Shared drive, Templates
What it can touch
Low
Only the letter it creates
Where it comes from
Low
Built by a former office manager
How it's maintained
High
Nobody since she left in 2022
Score 5 of 9: Medium
Why it runs today Office asks, and people click Enable Content, as they would for any file.
The plan We read the code: it only fills in the letter. Sign it; Ken becomes its owner.
Medium Commissions_2026.xlsmMatches carrier commission statements to policies and works out each producer's commission. Used byKen, RachelMonthly Runs todayUnblocked by hand PlanSign
Where it is Shared drive, Accounting
What it can touch
Medium
Reads the carrier statements and saves a copy each month
Where it comes from
Low
Built by Ken
How it's maintained
Medium
Ken alone; changes not recorded
Score 5 of 9: Medium
Why it runs today Arrives by email or download; someone removes Office's block each time, which switches the protection off for that file.
The plan Sign it. Ken stops unblocking it by hand each month.
Medium Trust_Account_Rec.xlsmMatches the premium trust account's bank download to the premiums received. Used byKenMonthly Runs todayEnable clicked each time PlanSign
Where it is Shared drive, Accounting
What it can touch
Medium
Reads the bank's download file
Where it comes from
Low
Built by Ken
How it's maintained
Medium
Ken alone; changes not recorded
Score 5 of 9: Medium
Why it runs today Office asks, and people click Enable Content, as they would for any file.
The plan Sign it.
Low PERSONAL.XLSBRachel's own shortcuts for formatting reports. Used byRachelDaily Runs todayOffice's startup folder PlanSign
Where it is Rachel's computer
What it can touch
Medium
Can change any workbook she has open
Where it comes from
Low
Recorded by Rachel
How it's maintained
Low
Rachel, its only user
Score 4 of 9: Low
Why it runs today Office trusts its own startup folder by default.
The plan Sign it. Under the new settings Office's startup folder is no longer trusted, so it needs a signature like everything else.
Low CommAuto_Rater.xlsmThe commercial auto carrier's own rating tool, for premium estimates. Used byProducersWeekly Runs todayUnblocked by hand PlanTrust the carrier's signature
Where it is Shared drive, Carriers
What it can touch
Low
Only its own sheets
Where it comes from
Medium
The carrier, signed with its own certificate
How it's maintained
Low
The carrier sends a new version each quarter
Score 4 of 9: Low
Why it runs today Arrives by email or download; someone removes Office's block each time, which switches the protection off for that file.
The plan Trust the carrier's certificate. Each new version then runs as it arrives, and anything changed after the carrier signed it doesn't.
Low WC_Class_Lookup.xlsmA workers' compensation carrier's class code lookup. Used byProducersMonthly Runs todayUnblocked by hand PlanSign each new version
Where it is Shared drive, Carriers
What it can touch
Low
Only its own sheets
Where it comes from
Medium
A carrier, unsigned
How it's maintained
Low
The carrier sends a new version each quarter
Score 4 of 9: Low
Why it runs today Arrives by email or download; someone removes Office's block each time, which switches the protection off for that file.
The plan Each quarter's new version is read and signed before it goes on the shared drive.
The other 25 files. 25 more are old copies, backups and one-off files nobody has opened in over a year: 14 old copies of the files above, 8 backups and 3 files nobody recognized. We recommend moving them into one archive file, where their macros can't run.
The signing plan
A signature says "this code was checked, and hasn't changed since." If anyone edits the code, Office removes the signature and the file stops running until it's checked and signed again. That is the control: changed code can't run unseen.
The certificate
- What to buy
- An organization-validated code-signing certificate, from a public certificate authority
- Name on it
- Harlow & Pine Insurance Agency
- Cost
- About $300 to $500 a year, paid to the issuer
- Before it's issued
- The issuer confirms the agency's registration; allow about a week
- Where the key lives
- In the issuer's secure signing service, never in a file on a computer
- Who can sign
- Ken, and Rowhouse Systems under Workbook Care
- Renewal
- Every year, 60 days before it runs out; files are signed again with the new one
Which files
10 signed by the agency, 1 on the carrier's own signature, 1 retired.
- Sign7Agency_Tools.xlam, Premium_Finance_Calc.xlsm, Producer_Pipeline.xlsm, COI_Cover_Letter.dotm, Commissions_2026.xlsm, Trust_Account_Rec.xlsm, PERSONAL.XLSB
- Change, then sign2Client_Mailing.xlsm, Renewal_Tracker.xlsm
- Sign each new version1WC_Class_Lookup.xlsm
- Trust the carrier's signature1CommAuto_Rater.xlsm
- Retire1Loss_Run_Summary.xlsm
In this order
Sign first and switch on the settings last, so nothing stops working on the day.
- Buy the certificate.
- Make the two changes, retire the loss run workbook, and archive the 25 old copies.
- Sign the 10 files, and check that each one still works.
- Switch the settings on for one computer for a week, to catch anything missed.
- Then for all nine. Test each computer and keep the record.
The settings
Six settings, applied by your IT provider through Group Policy so that staff can't change them. If your computers are managed in Intune instead, the same settings are there. We give your IT provider this list and check the result on every computer.
| Setting | Policy name | Set to | Why |
|---|---|---|---|
| Trust the agency's certificate | Windows: Public Key Policies, Trusted Publishers | Add the agency's certificate | Files the agency signs run on every computer. |
| Trust the carrier's certificate | Windows: Public Key Policies, Trusted Publishers | Add the carrier's certificate | The rating tool runs as the carrier sends it. |
| Run signed macros only | Excel and Word: VBA Macro Notification Settings | Disable all except digitally signed macros | Unsigned macros don't run, whoever clicks what. |
| Load signed add-ins only | Excel: Require that application add-ins are signed by Trusted Publisher | Enabled | A changed copy of the agency's add-in won't load. The test week shows whether any other add-in needs its publisher trusted first. |
| No trusted folders | Excel and Word: Disable all trusted locations | Enabled | Ends the whole-drive trust on the three computers, and Office's own startup folder stops being an exception: nothing runs just because of where it is saved. |
| Keep the internet block on | Excel and Word: Block macros from running in Office files from the Internet | Enabled | Files from email and the web stay blocked, and no computer can switch it off. |
Why Trusted Publishers, not Trusted Locations. A trusted folder runs whatever is put in it, so it is only as safe as the folder's permissions; that is how the shared drive came to be trusted today. A signature trusts only the code that was checked, wherever the file is saved, and any change to that code breaks it. With every file signed, the agency needs no trusted folders at all.
Documentation for your auditor
Written for whoever asks: your cyber insurer, a carrier, or an examiner. The statement and answers below are true once the plan is in place.
The control, in one paragraphMacros run on Harlow & Pine computers only when they are signed with the agency's own code-signing certificate, or with the certificate of the commercial auto carrier whose rating tool the agency uses. Group Policy enforces this on all nine computers, and staff cannot change it. Unsigned macros, and macros in files from email or the internet, are blocked. The agency keeps an inventory of every macro-enabled file it relies on, rated for risk, and reviews it every October.
The questions auditors ask
Which files run code, and who uses them?
The inventory: 12 files, each with its users, its purpose and its risk rating. 25 old copies are archived where they can't run.
How do you stop code nobody approved?
Only signed macros run, and Group Policy enforces it on every computer. The six settings, with their policy names, are in the audit file.
Who can change the code?
Anyone can edit a file, but an edit removes the signature, so the file stops running until it is checked and signed again. Only Ken and Rowhouse Systems can sign.
How do you know the controls work?
A test on every computer: a signed file runs, an unsigned copy is blocked, and a file from email is blocked. It is repeated at each yearly review.
What happens when the certificate runs out?
It is renewed every year, 60 days ahead, and the files are signed again with the new certificate.
What's in the audit file
- This inventory and its risk ratings, as of October 26, 2026
- The change rule: who may change a file, and how it is signed again (one page)
- The certificate's details: issuer, serial number, datesWhen the plan is in place
- A report of the six settings, exported from the serverWhen the plan is in place
- The test record from all nine computersWhen the plan is in place
- The next review: October 2027
Next step
Putting the plan in place (the certificate set up, 10 files signed, two of them after a small change, the loss run workbook rebuilt without macros, and the settings checked with your IT provider) is $1,400 fixed. Afterwards, Workbook Care ($150 a month) keeps the files signed, signs the carrier's lookup each quarter, and renews the certificate. Reply "Go ahead" and we'll send the invoice.
hello@rowhousesystems.com · rowhousesystems.com
Sample audit. Harlow & Pine Insurance Agency, its people, files and computers are fictional, and all prices are illustrations.