Compare gold and equity performance with Google Sheets, Groq, QuickChart and Gmail

{"id":"ttXnqdr96a8QyBU6","meta":{"instanceId":"9f24379abd1ca05a3f590fdaa874ca82fbc5e1188798d609d51d2a8f6bacb662"},"name":"Gold vs Equity Performance Comparison Tracker","tags":[],"nodes":[{"id":"16a00a3b-a760-4c26-ae35-a9d164a531e0","name":"Parse AI Output","type":"n8n-nodes-base.code","position":[656,256],"parameters":{"jsCode":"let raw = items[0].json.output;\n\ntry {\n  const parsed = JSON.parse(raw);\n  return [{ json: parsed }];\n} catch (e) {\n  return [{\n    json: {\n      error: \"Invalid JSON from AI\",\n      rawOutput: raw\n    }\n  }];\n}"},"typeVersion":2},{"id":"7e2bcf9a-e322-40c7-b883-370a60bfbcaa","name":"Check Performance Gap","type":"n8n-nodes-base.if","position":[1552,416],"parameters":{"options":{},"conditions":{"options":{"version":3,"leftValue":"","caseSensitive":true,"typeValidation":"loose"},"combinator":"and","conditions":[{"id":"e86c4ce0-92b1-4fb9-8ea9-4bdb3c8a55bc","operator":{"type":"number","operation":"gt"},"leftValue":"={{Math.abs(Number($('Calculate Performance Metrics').first().json.difference))}}","rightValue":"={{ $('Set Analysis Parameters').first().json.threshold }}"}]},"looseTypeValidation":true},"typeVersion":2.3},{"id":"d54e3d99-4c85-4125-ad14-bc8bb1fc6601","name":"Run Report","type":"n8n-nodes-base.manualTrigger","position":[-1088,416],"parameters":{},"typeVersion":1},{"id":"d0735fac-a6f2-4f50-b523-9d5da49180f2","name":"Set Analysis Parameters","type":"n8n-nodes-base.set","position":[-896,416],"parameters":{"options":{},"assignments":{"assignments":[{"id":"6c7a77ee-197d-4bfc-ac6c-c41be689edce","name":"startDate","type":"string","value":"2026-03-01"},{"id":"d63b3ae8-7c13-407a-901b-d163273a24b5","name":"endDate","type":"string","value":"2026-03-10"},{"id":"acb42113-ccd5-4148-8f46-da86b416d19b","name":"threshold","type":"number","value":5}]}},"typeVersion":3.4},{"id":"b763b4a7-ba69-4b2e-9a38-6b29f84b742a","name":"Fetch Gold Prices","type":"n8n-nodes-base.googleSheets","position":[-688,416],"parameters":{"options":{},"sheetName":{"__rl":true,"mode":"list","value":"gid=0","cachedResultUrl":"https://docs.google.com/spreadsheets/d/1ib5m4oUA__-HZvGMcRagCnOoiFmgKZPs8nBoCeoF3X0/edit#gid=0","cachedResultName":"Gold"},"documentId":{"__rl":true,"mode":"list","value":"1ib5m4oUA__-HZvGMcRagCnOoiFmgKZPs8nBoCeoF3X0","cachedResultUrl":"https://docs.google.com/spreadsheets/d/1ib5m4oUA__-HZvGMcRagCnOoiFmgKZPs8nBoCeoF3X0/edit?usp=drivesdk","cachedResultName":"Gold Data"}},"credentials":{"googleSheetsOAuth2Api":{"id":"YLNSloXJIWPMHBvp","name":"harshal chaudhari sheet acc"}},"executeOnce":true,"typeVersion":4.7},{"id":"01d47023-9b3e-4d8f-8d1f-6e43d8de1558","name":"Fetch Equity Prices","type":"n8n-nodes-base.googleSheets","position":[-448,416],"parameters":{"options":{},"sheetName":{"__rl":true,"mode":"list","value":1996490410,"cachedResultUrl":"https://docs.google.com/spreadsheets/d/1ib5m4oUA__-HZvGMcRagCnOoiFmgKZPs8nBoCeoF3X0/edit#gid=1996490410","cachedResultName":"Equity "},"documentId":{"__rl":true,"mode":"list","value":"1ib5m4oUA__-HZvGMcRagCnOoiFmgKZPs8nBoCeoF3X0","cachedResultUrl":"https://docs.google.com/spreadsheets/d/1ib5m4oUA__-HZvGMcRagCnOoiFmgKZPs8nBoCeoF3X0/edit?usp=drivesdk","cachedResultName":"Gold Data"}},"credentials":{"googleSheetsOAuth2Api":{"id":"YLNSloXJIWPMHBvp","name":"harshal chaudhari sheet acc"}},"executeOnce":true,"typeVersion":4.7},{"id":"414e6d7c-f2e3-4fc3-824c-3e1bf05077a2","name":"Merge Market Data","type":"n8n-nodes-base.code","position":[-208,416],"parameters":{"jsCode":"const seen = new Set();\n\nconst gold = $items(\"Fetch Gold Prices\");\nconst equity = items;\n\n// Get date parameters\nconst params = $(\"Set Analysis Parameters\").first().json;\nconst startDate = new Date(params.startDate);\nconst endDate = new Date(params.endDate);\n\nconst combined = [];\n\nfor (let i = 0; i < equity.length; i++) {\n  if (!gold[i]?.json?.Price || !equity[i]?.json?.Price) continue;\n\n  const cleanDate = gold[i].json.Date.replace(\",\", \"\").trim();\n  const currentDate = new Date(cleanDate);\n\n  if (seen.has(cleanDate)) continue;\n  seen.add(cleanDate);\n\n  if (currentDate < startDate || currentDate > endDate) continue;\n\n  combined.push({\n    json: {\n      date: cleanDate,\n      goldPrice: Number(gold[i].json.Price),\n      equityPrice: Number(equity[i].json.Price)\n    }\n  });\n}\n\n// Important safeguard\nif (combined.length === 0) {\n  return [{\n    json: {\n      error: \"No market data found in selected date range\"\n    }\n  }];\n}\n\nreturn combined;"},"typeVersion":2},{"id":"56f6f07f-ec62-4db8-b8c9-67ed81aa9922","name":"Calculate Performance Metrics","type":"n8n-nodes-base.code","position":[80,240],"parameters":{"jsCode":"if (!items.length || items[0].json.error) {\n  return [\n    {\n      json: {\n        error: items[0]?.json?.error || \"No data available for selected date range\"\n      }\n    }\n  ];\n}\n\nconst first = items[0].json;\nconst last = items[items.length - 1].json;\n\n// Calculate returns\nconst goldReturn =\n  ((last.goldPrice - first.goldPrice) / first.goldPrice) * 100;\n\nconst equityReturn =\n  ((last.equityPrice - first.equityPrice) / first.equityPrice) * 100;\n\n// Difference\nconst diff = goldReturn - equityReturn;\n\n// Decide winner\nlet winner = \"Equal\";\nif (diff > 0) winner = \"Gold\";\nelse if (diff < 0) winner = \"Equity\";\n\nreturn [\n  {\n    json: {\n      startDate: first.date,\n      endDate: last.date,\n      goldReturn: goldReturn.toFixed(2),\n      equityReturn: equityReturn.toFixed(2),\n      difference: diff.toFixed(2),\n      winner\n    }\n  }\n];"},"typeVersion":2},{"id":"c3c3b910-5e26-4d3a-88a6-3e9a33409080","name":"Generate AI Investment Insights","type":"@n8n/n8n-nodes-langchain.agent","position":[304,160],"parameters":{"text":"=Analyze the following asset performance:\n\nStart Date: {{ $json.startDate }}\nEnd Date: {{ $json.endDate }}\n\nGold Return: {{ $json.goldReturn }}%\nEquity Return: {{ $json.equityReturn }}%\nDifference: {{ $json.difference }}%","options":{"systemMessage":"You are a financial analyst and investment advisor.\n\nYour job:\n1. Compare gold vs equity performance\n2. Explain why one performed better\n3. Suggest portfolio allocation based on returns\n\nRules:\n- Use exact numbers provided\n- Do NOT give generic advice\n- Allocation must total 100%\n- Keep reasoning realistic and data-driven\n\nOutput ONLY valid JSON:\n\n{\n  \"summary\": \"clear comparison with numbers\",\n  \"winner\": \"Gold or Equity\",\n  \"reason\": \"why one outperformed\",\n  \"investmentAdvice\": \"specific actionable advice\",\n  \"goldAllocation\": \"percentage\",\n  \"equityAllocation\": \"percentage\",\n  \"strategy\": \"why this allocation makes sense\"\n}"},"promptType":"define"},"typeVersion":3.1},{"id":"4cf1120f-c476-4ef7-a180-1271df45bc87","name":"Combine AI + Chart Data","type":"n8n-nodes-base.merge","position":[960,416],"parameters":{},"typeVersion":3.2},{"id":"55bb3894-00d0-44d6-b998-a6257b6eca44","name":"Send Report Email","type":"n8n-nodes-base.gmail","position":[1776,320],"webhookId":"5a5f48c6-3d10-41af-8c38-c1c44af278fd","parameters":{"message":"={{ $json.html }}","options":{},"subject":"=Gold vs Equity Report ({{ $now }})"},"credentials":{"gmailOAuth2":{"id":"2bGhHtXnBefU5pfI","name":"harshal chaudhari gmail Acc"}},"typeVersion":2.2},{"id":"126c0c14-bb6d-4b9c-b038-1965577ecacf","name":"Send Alert Email","type":"n8n-nodes-base.gmail","position":[1776,512],"webhookId":"8dde3586-eb2f-4438-85de-b149abe2e3d4","parameters":{"message":"= Alert Triggered!\n\nA significant difference in asset performance detected.\n{{ $json.html }}","options":{},"subject":"=ALERT: Significant Performance Gap"},"credentials":{"gmailOAuth2":{"id":"2bGhHtXnBefU5pfI","name":"harshal chaudhari gmail Acc"}},"typeVersion":2.2},{"id":"0c8e904f-a026-42d9-8605-549f7bef540d","name":"Store Report History","type":"n8n-nodes-base.googleSheets","position":[2000,416],"parameters":{"columns":{"value":{"Date":"={{ $now }}","Report":"={{ $('Parse AI Output').first().json.reason }}","Winner":"={{ $('Parse AI Output').first().json.winner }}","Summary":"={{ $('Parse AI Output').first().json.summary }}"},"schema":[{"id":"Date","type":"string","display":true,"required":false,"displayName":"Date","defaultMatch":false,"canBeUsedToMatch":true},{"id":"Winner","type":"string","display":true,"required":false,"displayName":"Winner","defaultMatch":false,"canBeUsedToMatch":true},{"id":"Summary","type":"string","display":true,"required":false,"displayName":"Summary","defaultMatch":false,"canBeUsedToMatch":true},{"id":"Report","type":"string","display":true,"required":false,"displayName":"Report","defaultMatch":false,"canBeUsedToMatch":true}],"mappingMode":"defineBelow","matchingColumns":[],"attemptToConvertTypes":false,"convertFieldsToString":false},"options":{},"operation":"append","sheetName":{"__rl":true,"mode":"list","value":450031385,"cachedResultUrl":"https://docs.google.com/spreadsheets/d/1ib5m4oUA__-HZvGMcRagCnOoiFmgKZPs8nBoCeoF3X0/edit#gid=450031385","cachedResultName":"Sheet3"},"documentId":{"__rl":true,"mode":"list","value":"1ib5m4oUA__-HZvGMcRagCnOoiFmgKZPs8nBoCeoF3X0","cachedResultUrl":"https://docs.google.com/spreadsheets/d/1ib5m4oUA__-HZvGMcRagCnOoiFmgKZPs8nBoCeoF3X0/edit?usp=drivesdk","cachedResultName":"Gold Data"}},"credentials":{"googleSheetsOAuth2Api":{"id":"YLNSloXJIWPMHBvp","name":"harshal chaudhari sheet acc"}},"typeVersion":4.7},{"id":"28724d7d-0a8e-46c3-b0af-34ee4835d5e5","name":"Sticky Note","type":"n8n-nodes-base.stickyNote","position":[-1680,128],"parameters":{"width":512,"height":656,"content":"# Gold vs Equity Performance Comparison Tracker with Visual Insights\n\nThis workflow automates financial performance analysis between gold and equity using historical market data. It fetches price data from Google Sheets, processes and compares returns, generates AI-driven investment insights, visualizes performance trends and sends a formatted report via email.\n\n## How it works:\nThe workflow starts with user-defined date parameters, **retrieves gold and equity data**, merges and cleans it and calculates returns. AI then analyzes performance and suggests portfolio allocation. A **chart is generated for visualization** and a final HTML report is prepared and emailed. Alerts are triggered if performance differences exceed a threshold.\n\n## Setup steps:\n1. Google SheetsConnect Google Sheets (Gold + Equity data)\n2. Configure Groq API for AI insights\n3. Set Gmail credentials for report delivery\n4. Adjust date range and threshold in parameters\n5. Execute workflow manually or via trigger\n"},"typeVersion":1},{"id":"698b4964-549e-4730-8a6d-d8457c181461","name":"Sticky Note1","type":"n8n-nodes-base.stickyNote","position":[-944,240],"parameters":{"color":7,"width":928,"height":384,"content":"## Setup & Market Data Ingestion\nThe workflow begins by setting the analysis parameters (start/end dates and threshold limits). It then connects to Google Sheets to fetch historical daily price data for both Gold and Equity. The data is merged and cleaned by date to ensure an accurate, day-by-day head-to-head comparison before moving to the analysis phase."},"typeVersion":1},{"id":"c9d1d6be-3dab-4719-bb65-ec828ccdd4c6","name":"Sticky Note2","type":"n8n-nodes-base.stickyNote","position":[0,0],"parameters":{"color":7,"width":800,"height":448,"content":"## AI Insights & Data Parsing\nPerformance metrics are passed directly to a Groq-powered AI Agent which analyzes the financial returns. The AI evaluates the data to determine the winning asset, provide realistic market context and recommend a strategic portfolio allocation. The resulting raw text from the AI is then parsed into structured JSON, ensuring the generated insights can be seamlessly mapped into the final HTML email report."},"typeVersion":1},{"id":"37961394-b59f-4c9f-a33e-bf73368e2c89","name":"Sticky Note3","type":"n8n-nodes-base.stickyNote","position":[176,560],"parameters":{"color":7,"width":432,"height":304,"content":"\n\n\n\n\n\n\n\n\n\n\n\n\n\n## Chart Data & Visualization\nStructures Gold and Equity price data into arrays and generates a QuickChart line graph URL for performance comparison."},"typeVersion":1},{"id":"b31e4be0-a06b-4add-b7e0-a1b0bb2e17fd","name":"Sticky Note4","type":"n8n-nodes-base.stickyNote","position":[832,208],"parameters":{"color":7,"width":656,"height":384,"content":"## Report Generation\nIt synchronizes the two separate data streams back together. It takes the structured JSON insights from the AI agent and the generated chart URL, combining them into a single data object. Finally, it injects this combined data into a stylized HTML template to build the final comparative report."},"typeVersion":1},{"id":"041b9977-6fb8-4ce0-b89b-f61fa42c0914","name":"Sticky Note5","type":"n8n-nodes-base.stickyNote","position":[1504,160],"parameters":{"color":7,"width":720,"height":544,"content":"## Conditional Email Delivery & Auditing\n\nThis final section evaluates the calculated performance gap against a predefined threshold. Depending on the outcome, it branches to send either a standard \"Report Email\" or an urgent \"Alert Email\" via Gmail. After the notification is dispatched, the workflow appends the AI's summary, the winning asset and the execution date into a Google Sheet to maintain a historical log of the generated reports."},"typeVersion":1},{"id":"3aee49eb-6ad1-4286-a360-2358c46c23d0","name":"Insights","type":"@n8n/n8n-nodes-langchain.lmChatGroq","position":[304,320],"parameters":{"model":"llama-3.3-70b-versatile","options":{"temperature":0.7}},"credentials":{"groqApi":{"id":"yW0n4l0FjwBVJcJ8","name":"PT - Gaurav Patil Groq account"}},"typeVersion":1},{"id":"8ded1c1b-7547-4994-8d1e-6ba6c5dabd39","name":"Generate Final Report","type":"n8n-nodes-base.code","position":[1216,416],"parameters":{"jsCode":"const ai = items[0].json;\nconst chart = items[1].json;\n\n// Merge both streams\nconst d = {\n  ...ai,\n  chartUrl: chart.url || chart.chartUrl\n};\n\n// Create final HTML report\nconst html = `\n<div style=\"font-family: Arial, sans-serif; background:#f5f7fa; padding:20px;\">\n  \n  <div style=\"max-width:700px; margin:auto; background:white; border-radius:10px; padding:20px; box-shadow:0 4px 10px rgba(0,0,0,0.1);\">\n    \n    <h1 style=\"text-align:center; color:#2c3e50;\"> Gold vs Equity Report</h1>\n\n    <h2 style=\"text-align:center; color:${d.winner === 'Gold' ? '#d4af37' : '#3498db'};\">\n       Winner: ${d.winner}\n    </h2>\n\n    <hr/>\n\n    <h3> Summary</h3>\n    <p>${d.summary}</p>\n\n    <h3> Market Insight</h3>\n    <p>${d.reason}</p>\n\n    <h3> Investment Advice</h3>\n    <p>${d.investmentAdvice}</p>\n\n    <hr/>\n\n    <h3> Portfolio Allocation</h3>\n    <div style=\"display:flex; justify-content:space-between;\">\n      <div style=\"background:#f1c40f; padding:10px; border-radius:8px; width:45%; text-align:center;\">\n        <b>Gold</b><br/>${d.goldAllocation}%\n      </div>\n      <div style=\"background:#3498db; color:white; padding:10px; border-radius:8px; width:45%; text-align:center;\">\n        <b>Equity</b><br/>${d.equityAllocation}%\n      </div>\n    </div>\n\n    <h3 style=\"margin-top:20px;\"> Strategy</h3>\n    <p>${d.strategy}</p>\n\n    <hr/>\n\n    <h3> Performance Chart</h3>\n    <div style=\"text-align:center;\">\n      <img src=\"${d.chartUrl}\" style=\"width:100%; border-radius:8px;\" />\n    </div>\n\n    <p style=\"text-align:center; color:gray; font-size:12px; margin-top:20px;\">\n      Auto-generated financial insights report\n    </p>\n\n  </div>\n</div>\n`;\n\nreturn [\n  {\n    json: {\n      ...d,\n      html\n    }\n  }\n];"},"typeVersion":2},{"id":"b912394c-8703-4015-9824-1b146f8c7237","name":"Generate Chart","type":"n8n-nodes-base.code","position":[320,608],"parameters":{"jsCode":"if (!items.length || items[0].json.error) {\n  return [\n    {\n      json: {\n        chartUrl: \"\",\n        error: items[0]?.json?.error || \"No chart data available\"\n      }\n    }\n  ];\n}\n\nconst labels = items.map(i => i.json.date);\nconst gold = items.map(i => i.json.goldPrice);\nconst equity = items.map(i => i.json.equityPrice);\n\nconst chartConfig = {\n  type: \"line\",\n  data: {\n    labels,\n    datasets: [\n      {\n        label: \"Gold\",\n        data: gold,\n        borderColor: \"#D4AF37\",\n        backgroundColor: \"rgba(212,175,55,0.15)\",\n        borderWidth: 3,\n        fill: true,\n        tension: 0.45\n      },\n      {\n        label: \"Equity\",\n        data: equity,\n        borderColor: \"#2E86DE\",\n        backgroundColor: \"rgba(46,134,222,0.15)\",\n        borderWidth: 3,\n        fill: true,\n        tension: 0.45\n      }\n    ]\n  }\n};\n\nconst chartUrl =\n  \"https://quickchart.io/chart?width=900&height=450&backgroundColor=white&c=\" +\n  encodeURIComponent(JSON.stringify(chartConfig));\n\nreturn [\n  {\n    json: {\n      labels,\n      gold,\n      equity,\n      chartUrl\n    }\n  }\n];"},"typeVersion":2}],"active":false,"pinData":{},"settings":{"availableInMCP":false,"executionOrder":"v1"},"versionId":"e9ea7c53-6611-43a6-bdf9-5152feb58fbe","connections":{"Insights":{"ai_languageModel":[[{"node":"Generate AI Investment Insights","type":"ai_languageModel","index":0}]]},"Run Report":{"main":[[{"node":"Set Analysis Parameters","type":"main","index":0}]]},"Generate Chart":{"main":[[{"node":"Combine AI + Chart Data","type":"main","index":1}]]},"Parse AI Output":{"main":[[{"node":"Combine AI + Chart Data","type":"main","index":0}]]},"Send Alert Email":{"main":[[{"node":"Store Report History","type":"main","index":0}]]},"Fetch Gold Prices":{"main":[[{"node":"Fetch Equity Prices","type":"main","index":0}]]},"Merge Market Data":{"main":[[{"node":"Calculate Performance Metrics","type":"main","index":0},{"node":"Generate Chart","type":"main","index":0}]]},"Send Report Email":{"main":[[{"node":"Store Report History","type":"main","index":0}]]},"Fetch Equity Prices":{"main":[[{"node":"Merge Market Data","type":"main","index":0}]]},"Check Performance Gap":{"main":[[{"node":"Send Report Email","type":"main","index":0}],[{"node":"Send Alert Email","type":"main","index":0}]]},"Generate Final Report":{"main":[[{"node":"Check Performance Gap","type":"main","index":0}]]},"Combine AI + Chart Data":{"main":[[{"node":"Generate Final Report","type":"main","index":0}]]},"Set Analysis Parameters":{"main":[[{"node":"Fetch Gold Prices","type":"main","index":0}]]},"Calculate Performance Metrics":{"main":[[{"node":"Generate AI Investment Insights","type":"main","index":0}]]},"Generate AI Investment Insights":{"main":[[{"node":"Parse AI Output","type":"main","index":0}]]}}}