Weekly mortgage and housing market update draft in Gmail

Build me an agent workflow that puts together a client-ready weekly mortgage and housing market update every Monday at 7am America/New_York and lands it as an unsent draft in my Gmail. I run a mortgage broker and realtor team, and we send this recap to our client mailing list every week.

Trigger: cron, every Monday at 7:00am America/New_York.

Step 1. Pull this week's numbers from FRED using Get Series Observations for these series, requesting the last roughly 60 weekly observations and the last 24 months of monthly observations so we can compute both week-over-week and year-over-year deltas:

- MORTGAGE30US: Freddie Mac 30-year fixed rate, weekly.

- MORTGAGE15US: Freddie Mac 15-year fixed rate, weekly.

- MSPUS: median sales price of houses sold in the US, quarterly.

- EXHOSLUSM495S: existing home sales, monthly, seasonally adjusted annual rate.

- HOUST: new privately-owned housing units started, monthly, seasonally adjusted annual rate.

For each series, capture the latest value, the prior period value, the value from one year ago, and compute the week-over-week (or period-over-period) and year-over-year change in both absolute terms and percent. Freddie Mac publishes MORTGAGE30US and MORTGAGE15US on Thursdays, so Monday morning we should have fresh weekly numbers.

Step 2. Use Perplexity AI Create Chat Completion with a Sonar model to pull this week's mortgage market news, Fed commentary, and any relevant housing policy moves. Ask it to summarize why rates moved the way they did, what the Fed or Treasury said this week, and any legislative or regulatory news affecting mortgages, housing supply, or home affordability. Include citations. This is what lets the note explain the why, not just list numbers.

Step 3. Compose the email body. Structure:

- A short branded intro (one or two sentences) greeting the client list and dating the recap.

- A scannable HTML table with the five metrics as rows and columns for This week, Prior week or period, Week-over-week (or period-over-period) change, and Year-over-year change. Format rates as percentages to two decimals and dollar amounts with thousands separators.

- A two-paragraph plain-English take on what it means for buyers and sellers this week, weaving in the Perplexity context so the reader understands why rates moved.

- A friendly sign-off with placeholder for my name, team, and licensing footer (NMLS number, brokerage name). Leave those as clearly labeled placeholders I can fill in on first run.

Copy rules: warm, professional, non-technical. No em dashes anywhere. Use periods or commas instead. Do not include raw series IDs like MORTGAGE30US in the client-facing copy; use plain names like "30-year fixed rate" and "15-year fixed rate."

Step 4. Use Gmail Create a Draft to save the message as an unsent draft. Address it to my newsletter distribution list (I will supply the email alias or group address on first run). Subject line pattern: "Weekly mortgage and housing update, {{date}}". Leave the draft unsent so I can eyeball it and hit send myself.

Error handling: if a FRED series returns no fresh observation for this week (holiday, delayed release), still generate the recap using the most recent available value and note in the take that fresh data is pending. If Perplexity fails, still create the draft with the numbers and skip the market context paragraph.