Weekly Earnings and Event Prep Digest: One Monday Email of Company Events Needing Prep

What This Builds

An earnings date arrives, and the questions you wanted to ask are still in your head. This build keeps one line per upcoming company event in a small sheet and sends you one email every Monday listing the events inside your alert window where your prep notes are not done, plus the events that have no date yet. The outcome it serves is walking into each event with your questions written down and not assembling them the night before. Past events and events with prep done stay quiet.

Prerequisites

• A Google spreadsheet named "Event Prep", separate from any other tracker (the Level 2 guide builds it, and Part 1 and Part 2 below give the same layout so you can check it)
• A Zapier account on a paid plan, because the Zap has three steps and the free plan allows two-step Zaps only. The plan is Professional ($29.99/month). That is the total ongoing cost, since the Google account is one you already have.
• A Google account connected to Zapier for Sheets and Gmail
• A check of your firm's policy on holding codes and event codes in an outside automation tool, with your CCO

The Concept

A race engineer keeps a pit board: what the next stop needs and how many laps remain. The board does the remembering, so the engineer can watch the track. This build gives you that board for company events, and the Monday email reads it out.

One technical point shapes the design. A Zapier search step that finds no row halts the Zap. Zap History shows the run as safely halted, and no email is sent. A quiet week would then look exactly like a broken Zap. So the Zap searches a Summary tab with one row that always exists. Its Key cell says summary, and formulas fill the rest of the row from the Events tab. A quiet week produces an email that says none and zero. There is no AI step, no Formatter step, no loop, no second lookup and no row trigger, and nothing in this build reads from or writes to your portfolio, order, risk, performance or CRM system.

Event dates come from each company's own announcement and you type them. This guide states no reporting calendar and no rule about when a company reports, and the alert window is a number you choose. The digest is a backstop that never replaces your own calendar.

Data caution. The sheet holds holding codes (such as H-01), event type codes (EARNINGS, INVESTORDAY or AGM), dates and yes or no only. Company names, tickers, your questions, your notes and anything you have not seen in a public announcement stay out of it. Nothing non-public about a company goes into the sheet, the Zap or the email. Material non-public information never goes into any AI tool, and the same discipline applies to Zapier and the sheet. Undisclosed holdings, trade ideas and orders, your models, investor and client names, account numbers, fee terms, side letters and consultant scores stay in your firm's systems. Zapier is an outside tool, so ask your CCO whether your firm's policy allows these codes to pass through it and how business use of outside tools is recorded. Check Zapier's own pages for how long Zap History is kept and for its data settings. If the answer is no, use the sheet as a manual Monday checklist and skip the Zap.

Build It Step by Step

Part 1: Build the Events tab

Name the first tab Events. Lay out the columns first.

Format column C as a date (Format, then Number, then Date, or the closest match). Type dates as yyyy-mm-dd. Add the dropdown to D2 through D301 (Insert, then Dropdown, or Data, then Data validation, with the list yes and no). Menu names change, so look for the closest match.

E2, filled down to row 301:

=IF(A2="","",IF(C2="","MISSING DATE",IF(C2<TODAY(),"",IF(D2="yes","",IF(C2-TODAY()<=Summary!$B$4,"PREP NEEDED","")))))

Read the formula from the outside in, then run each case through it:

• A blank row (no holding code) returns blank, so rows filled down past your last event stay empty.
• An event with a code but no date returns MISSING DATE. It is a reminder to type the date once the company announces it.
• An event dated before today returns blank. The event is past, so it drops off the list.
• An event with Prep notes done set to yes returns blank.
• An event dated today or later, no more than Alert days away, with prep not marked yes returns PREP NEEDED. A blank prep cell counts as not yes, and an event exactly Alert days away counts.
• Everything else returns blank.

The five closing parentheses at the end match the five IF functions still open.

Part 2: Build the Summary tab

Add a second tab named Summary. Type these cells first.

B2:

=COUNTIF(Events!E2:E301,"PREP NEEDED")

C2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Events!A2:A301&" "&Events!B2:B301&" ("&TEXT(Events!C2:C301,"yyyy-mm-dd")&")",Events!E2:E301="PREP NEEDED")),"none")

D2:

=COUNTIF(Events!E2:E301,"MISSING DATE")

Check the parentheses in each formula before you move on. Every range inside the FILTER runs from row 2 to row 301, so the heights match. The dates sit inside TEXT() so they join as 2026-11-05 and not as a serial number. A row only reaches the list when it carries the flag, and that flag needs a date, so TEXT() never sees a blank date for a listed row. IFERROR turns the empty-result error into the word none. The counts look for the exact flag words, so rows the formula fills down past your last entry are never counted.

The value in B4 is a starting choice, and it is yours to change. It is your own working window and not a rule.

Keep Calculation Mode on Automatic (File, Settings, Calculation). Google's calculation settings page offers Automatic and Manual only, with no scheduled recalculation, and TODAY() depends on recalculation. If a count looks stale, open the sheet before relying on the email. The spreadsheet's time zone (File, Settings, General) also decides what TODAY() means.

Part 3: Load the test rows

The rows below are invented. They assume a pretend TODAY() of Monday 2026-10-26 and B4 = 14. On another day, keep the same offsets from your real today and retype the dates.

The Summary row should then read:

H-03 shows the boundary: fourteen days is within a fourteen-day window. H-04 sits at fifteen days and waits. H-02 has prep done, so it drops off even though its date is inside the window. H-05 is past. H-06 has no date yet, and the count of one reminds you to type it once the company announces it.

If C2 shows one item only, or the same date for every item, the first argument of the FILTER is not being read as an array in your sheet. Wrap the text-joining part of the FILTER (the part that joins codes and dates, before the final condition) in ARRAYFORMULA and compare again. The values above are the check.

Part 4: Create the Zap

1. In Zapier, create a new Zap.
2. Choose Schedule by Zapier as the trigger and the Every Week event.
3. Choose Monday for Day of the Week and set the Time of Day for the email. Leave Timezone Override alone unless your spreadsheet uses a different time zone.
4. Add an action. Choose Google Sheets and the Lookup Spreadsheet Row event.
5. Connect your Google account if asked. Pick the spreadsheet Event Prep and the worksheet Summary.
6. For Lookup column choose Key (column A). Labels may be worded a little differently.
7. For Lookup value type summary, in lowercase.
8. Leave Supporting lookup column, Supporting lookup value, Search from last row and Row count at their defaults.
9. Add a second action. Choose Gmail and the Send Email event.
10. In To, type your own address only. The digest goes to you and no one else.
11. Type the subject Event prep needed this week.
12. Type the body below. Replace each bracketed name with the field of that name from the Google Sheets step, using Zapier's field picker.

Prep needed [Prep Needed Count]: [Prep Needed Items]
Missing event dates [Missing Date Count]
Backstop only. Your own calendar is the record.

1. Test the Google Sheets step. It should return the whole Summary row, with a value for each header in row 1.
2. Test the Gmail step and read the email.
3. Turn the Zap on.

The lookup returns the entire matching row, and that row is all Zapier sees. The Events tab never leaves Google. Row 4 holds your window, so it is not part of the row the lookup returns.

What leaves your sheet

Zapier receives the Summary row and the email text. That means the word summary, the Prep Needed Count, the Prep Needed Items text (holding codes, event type codes and event dates) and the Missing Date Count. Zap History stores the data each step received and returned, so those values stay in your Zapier account. The rest of the Events tab stays in Google.

Zapier bills only successful action steps as tasks. The trigger, a search that halts and a failed action are not counted. A normal run has two action steps, the lookup and the email, so check Zapier's own pages for how your plan counts them.

Use codes only. A company name, a ticker, a question or a note does not belong in this sheet.

Real Example: Monday 2026-10-26

Setup: The six invented rows from Part 3 and B4 set to 14. Input: On Monday at the scheduled time, the Zap fires and finds the summary row. Output:

Prep needed 2: H-01 EARNINGS (2026-10-29), H-03 EARNINGS (2026-11-09)
Missing event dates 1
Backstop only. Your own calendar is the record.

Check it by hand. 2026-10-29 is three days after 2026-10-26, and 2026-11-09 is fourteen days after it (five days to the end of October and nine more). 2026-11-10 is fifteen days out, so H-04 waits. H-02 is marked done. 2026-10-22 is past. H-06 has no date. What you do next: You start your questions for H-01 first, since its event is three days out, and you plan H-03 later in the week. You check the company's announcement for the H-06 date and type it in. The rows are invented. Real dates come from each company's own announcement.

What to Do When It Breaks

• A Monday passed with no email → This failure is silent, so build the habit of noticing it. Check that the Zap is switched on. Open Zap History and look for a run that Monday. A "safely halted" run means the lookup found no row, so check that A2 on the Summary tab still says summary, because a renamed Key cell stops the lookup. Check that the spreadsheet and the Summary tab still have their original names. If Zapier reports an expired Google connection, reconnect it. A Monday with no email never means that nothing needs attention.
• An event never appears → Confirm column C holds a real date and not text, and that the event is not past.
• A prepared event still shows → Prep notes done must read yes, spelled exactly that way. The dropdown prevents typos.
• An event moved → Update the date in column C when the company announces the change. The sheet does not learn of it.
• An event type code is misspelled → The code only labels the item in the email. The flag does not depend on it, but a clear code helps you read the digest.
• Dates show as numbers in the email → The TEXT wrapper was removed from the formula in C2. Restore it from Part 2.
• Counts look stale → Open the sheet and keep Calculation Mode on Automatic.

Variations

• Simpler version: Drop the Prep notes done column and flag every event inside the window.
• Extended version: Raise the aim from a flag to a finished prep. Add a column for whether your questions for management are written (yes or no), and add a count and an items cell to the Summary in the same pattern. Every new formula follows the same rules as above.

What to Do Next

• This week: Run the test rows. Then replace them with your live entries and compare the first email with your own calendar.
• This month: Adjust Alert days to the lead time your own prep needs.
• Advanced: Pair it with the thesis review digest, each in its own spreadsheet.

Advanced guide for investment fund manager professionals. This build needs a paid Zapier plan. Zapier and Google update their screens often, so if a label differs from this guide, look for the closest match and check the vendor's help pages.