Use Gemini in Google Sheets to build a Thesis Reviews spreadsheet that flags overdue reviews

What This Does

Every holding has a thesis, and every thesis has a review date that slips when earnings season gets busy. This guide builds a small spreadsheet called "Thesis Reviews" that holds one row per holding code, works out the next review date and flags each row as OVERDUE, DUE SOON or MISSING DATE. A Summary tab turns those flags into counts and a short list. Gemini helps with the formulas, the formatting and the checks. You paste every formula in full and compare it with what Gemini offers, so the sheet does the arithmetic and you can see exactly what it does.

The Level 4 weekly thesis review digest reads the Summary tab of this exact spreadsheet. Build it as written here, with the same tab names, columns and cells, and that digest will find what it needs.

Before You Start

• A Google account with access to Gemini in Google Sheets. Google's help page says the feature needs an eligible Google Workspace or Google AI plan, such as Business Standard ($14/user/month) for a business account or Google AI Pro ($19.99/month) for a personal one. Open any spreadsheet and look for the Ask Gemini button at the top right. If it is missing, ask your Workspace admin whether Gemini is turned on for you.
• A short list of holding codes in your own scheme (H-01, H-02 and so on) and the last review date and review interval for each. The sheet holds codes and dates only. No company names, tickers, weights, thesis text or trade ideas.
• A word with your CCO about which AI tools your firm has approved and how chats in them are kept. Check the tool's own data settings too: whether chats are used for training and how long they are retained.

Data caution. Material non-public information never goes into any AI tool, and that includes the Ask Gemini panel and any cell Gemini can read. Undisclosed holdings, trade ideas, your models, investor and client names, account numbers and fee terms stay in your firm's systems unless the firm approved this tool for that data and your CCO allows it. Content from a terminal, data provider or research provider is licensed, so read the licence terms before any of it goes into a sheet. This workflow needs none of that. It runs on codes, dates and column names, and the practice rows below are invented.

Steps

1. Create the spreadsheet and the two tabs

Create a new Google Sheets file named "Thesis Reviews". Rename the first tab "Tracker" and add a second tab named "Summary". Spell both names exactly, because the formulas refer to them.

On Tracker, type these headers in row 1:

• A1: Holding code
• B1: Last review date
• C1: Review every (days)
• D1: Next review date
• E1: Flag

Format columns B and D as dates. Gemini can do this if you ask: open Ask Gemini at the top right and enter "Set the number format of B2:B301 and D2:D301 to date". Gemini shows an "Action preview card". Click Apply, and click Undo if the result is wrong.

2. Set up the Summary tab

On Summary, type these headers in row 1:

• A1: Key
• B1: Due Soon Count
• C1: Due Soon Items
• D1: Overdue Count
• E1: Overdue Items
• F1: Missing Date Count

In A2 type the word summary, all lowercase. The Level 4 digest looks for exactly that word in column A, so do not rename it later.

In A4 type "Alert days" and in B4 type 7. This is your own choice of how many days ahead a review counts as due soon. Change it whenever you like. It is not a rule.

3. Add the Tracker formulas

Gemini writes formulas from a description, and you can compare its answer with the one below. In Ask Gemini, enter:

"On the Tracker tab, D2 should be the last review date in B2 plus the number of days in C2, and blank if either is empty. Give me the formula."

Click Insert if you want to see it land in the cell, or copy it and compare. Then paste this formula into D2:

=IF(OR(B2="",C2=""),"",B2+C2)

Next ask Gemini for the flag: "In E2, return nothing if A2 is empty, MISSING DATE if B2 or C2 is empty, OVERDUE if D2 is before today, DUE SOON if D2 minus today is no more than the alert days in Summary!B4, and nothing otherwise." Compare its answer with this. Then paste this version into E2:

=IF(A2="","",IF(OR(B2="",C2=""),"MISSING DATE",IF(D2<TODAY(),"OVERDUE",IF(D2-TODAY()<=Summary!$B$4,"DUE SOON",""))))

If a formula cell shows an error, hover over the cell and click Fix. Gemini then reviews the formula and explains the problem. Treat its suggestion as a proposal and re-read it before you accept.

4. Fill the formulas down to row 301

Copy D2 and E2, select D3:E301, and paste. Every row to 301 now holds the formulas. An empty row returns blank and is not counted anywhere.

5. Add the Summary formulas

Paste each of these into the cell named. They count and list the flags on Tracker.

B2:

=COUNTIF(Tracker!E2:E301,"DUE SOON")

C2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Tracker!A2:A301&" (next review "&TEXT(Tracker!D2:D301,"yyyy-mm-dd")&")",Tracker!E2:E301="DUE SOON")),"none")

D2:

=COUNTIF(Tracker!E2:E301,"OVERDUE")

E2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Tracker!A2:A301&" (next review "&TEXT(Tracker!D2:D301,"yyyy-mm-dd")&")",Tracker!E2:E301="OVERDUE")),"none")

F2:

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

6. Ask Gemini for the colours

On Tracker, ask Gemini: "Apply conditional formatting to A2:E301 so a row turns red when column E says OVERDUE, amber when it says DUE SOON and grey when it says MISSING DATE." Review the Action preview card and click Apply. If a rule is off, click Settings on the result to adjust the conditional formatting rules.

7. Test with the practice rows

Type these invented rows into Tracker, with Summary!B4 set to 7. The expected results are written for an invented Monday, 2026-10-26, because the Flag column compares against TODAY(). On any other day, add the number of days between 2026-10-26 and today to every date you type. The flags come out the same, and the dates inside the Summary text shift by the same amount.

Expected Summary values:

• B2: 3
• C2: H-01 (next review 2026-10-30), H-03 (next review 2026-10-31), H-04 (next review 2026-11-02)
• D2: 1
• E2: H-02 (next review 2026-10-18)
• F2: 1

If a value differs, check the tab names first, then the alert days in B4, then whether column B holds real dates rather than text.

8. Keep Calculation Mode on Automatic

Click File > Settings > Calculation. Under Calculation Mode, choose Automatic and click Save settings. Google's help page for calculation settings lists only Automatic and Manual, and describes TODAY as a function whose value changes every new day. It lists no scheduled recalculation option, so open the sheet before relying on the counts if they look stale.

9. Clear the practice rows

Delete the invented values in columns A to C (not the formulas in D and E). Enter your own holding codes, last review dates and review intervals. When you finish a review, type the new date in column B and the sheet recalculates.

Real Example

Scenario: You run Strategy S, an invented equity strategy with seven coded holdings. It is the Monday before an investment committee meeting and you want to know which reviews are due without reading down a calendar.

What you type: The seven practice rows above. Then, in Ask Gemini: "Using the Tracker tab, how many reviews have a next review date in each week from 2026-10-26 to 2026-11-23? Show the analysis steps."

What you get: Gemini returns weekly counts and, if you click Analysis steps, shows how it got them. For the practice rows, the weeks starting 2026-10-26 and 2026-11-02 should each hold two next review dates, the weeks starting 2026-11-09 and 2026-11-16 none, and the week starting 2026-11-23 one. H-02 (already past) and H-06 (no date) fall outside every week. Work the same counts out from the Next review date column yourself. The Summary tab remains the number you rely on.

Tips

• Gemini's history disappears when you reload the browser or close the file. Insert useful output into the sheet if you want to keep it.
• Ask Gemini a question about the dates themselves, such as how many reviews cluster in one week, so you can spread the work before it piles up. Then check the answer with a COUNTIFS or a filter. The check comes from the sheet, not from a second question to Gemini.
• Keep one row per holding code and nothing else. If you start adding names or notes, the sheet becomes a document you have to protect. A code that means something only in your firm's systems is the safer habit.
• Summary C2 and E2 list holding codes in row order, not by urgency. Read the dates in the text.

Tool interfaces change. If Ask Gemini has moved, look for a Gemini or sparkle icon near the top right of Google Sheets.