Generate a daily multi-asset market report with TwelveData, Groq and Google Sheets

{"id":"kn5HELmvtLeSYzsu","meta":{"instanceId":"9f24379abd1ca05a3f590fdaa874ca82fbc5e1188798d609d51d2a8f6bacb662","templateCredsSetupCompleted":true},"name":"Multi-Asset Daily Snapshot","tags":[],"nodes":[{"id":"108301be-fe1b-4b1a-b273-00213e3dc28d","name":"Set Market Assets","type":"n8n-nodes-base.set","position":[-320,912],"parameters":{"options":{},"assignments":{"assignments":[{"id":"03778b5d-2d63-46e6-beb3-b3f83ceeabcb","name":"Indices","type":"string","value":"SPY,QQQ,DIA"},{"id":"57bed606-a6d6-495b-8119-8afcb9da4704","name":"Forex","type":"string","value":"EUR/USD,USD/INR,GBP/USD,USD/JPY"},{"id":"1de3c6c9-121b-4e5e-b05c-136d0b33079f","name":"Commodities","type":"string","value":"XAU/USD,USO"}]},"includeOtherFields":true},"typeVersion":3.4},{"id":"ecbce792-bd83-43b1-89d2-33dcc4e3f0cf","name":"AI Market Insights","type":"@n8n/n8n-nodes-langchain.agent","position":[720,432],"parameters":{"text":"==Here is today's normalized multi-asset market data:\n\n{{ JSON.stringify($json.market_data, null, 2) }}\n\nAnalyze the data and generate a professional daily market report.\n\nImportant:\nFollow the exact output format specified in the system instructions.\nUse the provided percentage moves and trend data to determine sentiment and outlook.","options":{"systemMessage":"You are a professional multi-asset financial market strategist and macro analyst.\n\nYour task is to analyze normalized market data across equities, forex and commodities and generate a concise institutional-grade daily market snapshot.\n\nSTRICT RULES:\n- Output plain text only\n- No markdown\n- No JSON\n- No emojis\n- No bold text\n- No bullet symbols except hyphen (-) under Key Movers\n- Use the EXACT section headings below\n- Keep each section concise and professional\n- Mention actual percentage changes from provided data\n- Focus on trend, sentiment and actionable market insight\n- Avoid generic filler statements\n\nMANDATORY OUTPUT FORMAT:\n\nMarket Summary:\n2-3 lines summarizing the overall market direction and major trends.\n\nKey Movers:\n- Always include SPY\n- Always include XAU/USD\n- Always include USO\n- Add 2 to 3 other biggest movers\n\nRisk Sentiment:\nState one of:\nRisk-on\nRisk-off\nMixed\n\nActionable Insight:\n1-2 lines about what traders/investors should watch next.\n\nOutlook:\n1-2 lines on expected next-session bias."},"promptType":"define"},"typeVersion":3.1},{"id":"63ed006d-39e2-4e6c-8e77-cb9fbe38b0e4","name":"Insights","type":"@n8n/n8n-nodes-langchain.lmChatGroq","position":[720,608],"parameters":{"model":"llama-3.3-70b-versatile","options":{}},"credentials":{"groqApi":{"id":"QyoqMLjpMrMq0JV2","name":"harshal chaudhari groq acc"}},"typeVersion":1},{"id":"a9100ef5-e4b2-47ae-bef1-7dd4dd9ab61a","name":"Parse AI Output","type":"n8n-nodes-base.code","position":[1056,432],"parameters":{"jsCode":"let text = $json.output || \"\";\n\n// Clean raw text\ntext = text.replace(/\\\\n/g, '\\n').trim();\n\n// Extract sections safely\nconst summaryMatch = text.match(/Market Summary\\s*:\\s*([\\s\\S]*?)(?=Key Movers\\s*:|$)/i);\nconst moversMatch = text.match(/Key Movers\\s*:\\s*([\\s\\S]*?)(?=Risk Sentiment\\s*:|Actionable Insight\\s*:|$)/i);\nconst sentimentMatch = text.match(/Risk Sentiment\\s*:\\s*([\\s\\S]*?)(?=Actionable Insight\\s*:|$)/i);\nconst insightMatch = text.match(/Actionable Insight\\s*:\\s*([\\s\\S]*?)(?=Outlook\\s*:|$)/i);\nconst outlookMatch = text.match(/Outlook\\s*:\\s*([\\s\\S]*)/i);\n\n// Assign values\nlet summary = summaryMatch?.[1] || \"\";\nlet moversRaw = moversMatch?.[1] || \"\";\nlet sentiment = sentimentMatch?.[1] || \"\";\nlet insight = insightMatch?.[1] || \"\";\nlet outlook = outlookMatch?.[1] || \"\";\n\n// Common cleanup\nconst cleanText = (t) => {\n  if (!t) return \"\";\n\n  return t\n    .replace(/\\*\\*/g, \"\")\n    .replace(/\\s+/g, \" \")\n    .trim();\n};\n\nsummary = cleanText(summary);\nsentiment = cleanText(sentiment);\ninsight = cleanText(insight);\noutlook = cleanText(outlook);\n\n// Clean movers list\nconst movers = moversRaw\n  .split(/\\r?\\n+/)\n  .map(line =>\n    line\n      .replace(/^[-*•]\\s*/, \"\")\n      .replace(/\\s+/g, \" \")\n      .trim()\n  )\n  .filter(Boolean);\n\n// FIXED sentence trim (safe for decimals like 1.46%)\nconst trimToTwoSentences = (text) => {\n  if (!text) return \"\";\n\n  const sentences =\n    text.match(/(?:[^.!?]|\\d\\.\\d)+[.!?]+/g) || [text];\n\n  return sentences\n    .slice(0, 2)\n    .join(\" \")\n    .trim();\n};\n\nsummary = trimToTwoSentences(summary);\ninsight = trimToTwoSentences(insight);\noutlook = trimToTwoSentences(outlook);\n\n// Final output\nreturn [\n  {\n    json: {\n      market_summary: summary,\n      key_movers: movers,\n      risk_sentiment: sentiment,\n      insight,\n      outlook\n    }\n  }\n];"},"typeVersion":2},{"id":"e2f7f2da-f675-4778-b2e6-1f93129bffcb","name":"Send Today's market summary","type":"n8n-nodes-base.gmail","position":[1504,432],"webhookId":"a42c4067-aeaf-493c-a427-889ba6a71d9c","parameters":{"message":"=Market Summary:\n{{ $('Parse AI Output').item.json.market_summary }}\n\nKey Movers:\n{{ $('Parse AI Output').item.json.key_movers.join('\\n') }}\n\nRisk Sentiment:\n{{ $('Parse AI Output').item.json.risk_sentiment }}\n\nInsight:\n{{ $('Parse AI Output').item.json.insight }}\n\noutlook:\n{{ $json.outlook }}","options":{},"subject":"=today's market summary","emailType":"text"},"credentials":{"gmailOAuth2":{"id":"2bGhHtXnBefU5pfI","name":"harshal chaudhari gmail Acc"}},"typeVersion":2.2},{"id":"2f41c0a1-1cab-47ce-990c-53ed16e227df","name":"Sticky Note","type":"n8n-nodes-base.stickyNote","position":[-1504,448],"parameters":{"width":624,"height":576,"content":"# Multi-Asset Daily Market Snapshot\n\nThis workflow automates the **end-to-end process** of collecting, analyzing and delivering **daily financial market insights** across multiple asset classes including **equities, forex and commodities**. It integrates **real-time market data**, processes it into a **structured format** and uses **AI-powered analysis** to generate a **professional-grade market report** for faster and smarter decision-making.\n\n## How it Works:\nThe workflow begins by defining **asset groups** and fetching **live market data via API**. It then performs **data normalization and transformation** to create a clean dataset, which is analyzed using an **AI model** to generate insights like **market summary, key movers and sentiment**. Finally, the report is **stored and automatically delivered via email**.\n\n## Setup Steps:\n- **TwelveData API** – Add your API key in Environment Config to enable market data fetching  \n- **Groq API (Llama Model)** – Configure credentials to power AI-based market insights  \n- **Gmail OAuth** – Connect your Gmail account to send automated reports  \n- **Asset Configuration** – Customize symbols in \"Set Market Assets\" as per your needs  "},"typeVersion":1},{"id":"ae89094e-0088-4343-9db8-81dd3d57fc80","name":"Generate Daily Market Report","type":"n8n-nodes-base.manualTrigger","position":[-768,912],"parameters":{},"typeVersion":1},{"id":"0efb9155-073c-4075-832c-93d3d57dca97","name":"Environment Config","type":"n8n-nodes-base.set","position":[-544,912],"parameters":{"options":{},"assignments":{"assignments":[{"id":"2da88a5b-8b65-4617-a022-3f6aa09131e3","name":"api_base_url","type":"string","value":"https://api.twelvedata.com/time_series"},{"id":"559dc6bb-e3f6-4960-aa9f-f76aae65f4d1","name":"twelve_api_key","type":"string","value":""}]},"includeOtherFields":true},"typeVersion":3.4},{"id":"502630b9-18b5-4479-9426-312f60e524bf","name":"Rate Controlled Queue","type":"n8n-nodes-base.splitInBatches","position":[176,880],"parameters":{"options":{},"batchSize":"={{ 1 }}"},"typeVersion":3},{"id":"89789f28-94a0-4ad1-88f8-74e5e0395233","name":"API Throttle","type":"n8n-nodes-base.wait","position":[416,896],"webhookId":"e6191fff-9547-41fe-8452-ce2c74431cba","parameters":{"amount":15},"typeVersion":1.1},{"id":"9bdb2163-8645-4acc-a7cd-224167857e6d","name":"Log API Error","type":"n8n-nodes-base.code","position":[1152,944],"parameters":{"jsCode":"return [\n  {\n    json: {\n      error_time: new Date().toISOString(),\n      error_status: $json.status,\n      error_message: $json.message || \"Unknown API error\",\n      failed_symbol: $json.meta?.symbol || \"N/A\"\n    }\n  }\n];"},"typeVersion":2},{"id":"0ece6cab-e490-4552-a052-617846dbc6aa","name":"Normalize Market Data","type":"n8n-nodes-base.code","position":[496,432],"parameters":{"jsCode":"const result = [];\nlet runIndex = 0;\n\n// Loop through every execution run of the Fetch node\nwhile (true) {\n  try {\n    // Grab the data for the specific loop iteration\n    const rawData = $items(\"Fetch Asset Prices\", 0, runIndex);\n    \n    // Break if we've reached the end of the runs\n    if (!rawData || rawData.length === 0) break;\n\n    for (const item of rawData) {\n      const data = item.json;\n      const latest = data.values?.[0];\n\n      // If data is missing (e.g. rate limit hit), record the error instead of skipping\n      if (!latest) {\n        result.push({\n          symbol: data.meta?.symbol || data.symbol || \"Unknown\",\n          status: data.status || \"error\",\n          message: data.message || \"No data received (likely rate limit)\"\n        });\n        continue;\n      }\n\n      const open = parseFloat(latest.open);\n      const close = parseFloat(latest.close);\n      const changePercent = ((close - open) / open) * 100;\n\n      result.push({\n        symbol: data.meta.symbol,\n        asset_type: data.meta.type,\n        price: close,\n        open,\n        high: parseFloat(latest.high),\n        low: parseFloat(latest.low),\n        date: latest.datetime,\n        change_percent: Number(changePercent.toFixed(2)),\n        trend: changePercent > 0 ? \"Bullish\" : \"Bearish\",\n        status: \"success\"\n      });\n    }\n    runIndex++; // Move to the next execution run\n  } catch (error) {\n    // When runIndex exceeds the total number of runs, it throws an error. Break the loop.\n    break;\n  }\n}\n\nreturn [\n  {\n    json: {\n      market_data: result\n    }\n  }\n];"},"typeVersion":2},{"id":"a6fbae73-0e5c-4221-aef5-4b40e432fcc6","name":"Alert API Faliure","type":"n8n-nodes-base.gmail","position":[1552,944],"webhookId":"95d68b1f-70c7-4f1c-9f2d-d9a0ebcc9d3e","parameters":{"message":"=Twelve Data API failed.\nStatus: {{ $json.status }}\nMessage: {{ $json.message }}","options":{},"subject":"API Failure Alert","emailType":"text"},"credentials":{"gmailOAuth2":{"id":"2bGhHtXnBefU5pfI","name":"harshal chaudhari gmail Acc"}},"typeVersion":2.2},{"id":"bac68de1-64f7-4985-bc6d-6fe469f65a64","name":"Log Daily Market Report","type":"n8n-nodes-base.googleSheets","position":[1264,432],"parameters":{"columns":{"value":{"status":"={{ \"SUCCESS\" }}","insight":"={{ $json.insight }}","outlook":"={{ $json.outlook }}","key_movers":"={{ $json.key_movers.join(', ') }}","total_assets":"={{ $items(\"Normalize Market Data\")[0].json.market_data.length }}","execution_date":"={{ $now }}","market_summary":"={{ $json.market_summary }}","risk_sentiment":"={{ $json.risk_sentiment }}","workflow_version":"v2.0-production"},"schema":[{"id":"execution_date","type":"string","display":true,"required":false,"displayName":"execution_date","defaultMatch":false,"canBeUsedToMatch":true},{"id":"market_summary","type":"string","display":true,"required":false,"displayName":"market_summary","defaultMatch":false,"canBeUsedToMatch":true},{"id":"key_movers","type":"string","display":true,"required":false,"displayName":"key_movers","defaultMatch":false,"canBeUsedToMatch":true},{"id":"risk_sentiment","type":"string","display":true,"required":false,"displayName":"risk_sentiment","defaultMatch":false,"canBeUsedToMatch":true},{"id":"insight","type":"string","display":true,"required":false,"displayName":"insight","defaultMatch":false,"canBeUsedToMatch":true},{"id":"outlook","type":"string","display":true,"required":false,"displayName":"outlook","defaultMatch":false,"canBeUsedToMatch":true},{"id":"total_assets","type":"string","display":true,"required":false,"displayName":"total_assets","defaultMatch":false,"canBeUsedToMatch":true},{"id":"status","type":"string","display":true,"required":false,"displayName":"status","defaultMatch":false,"canBeUsedToMatch":true},{"id":"workflow_version","type":"string","display":true,"required":false,"displayName":"workflow_version","defaultMatch":false,"canBeUsedToMatch":true}],"mappingMode":"defineBelow","matchingColumns":[],"attemptToConvertTypes":false,"convertFieldsToString":false},"options":{},"operation":"append","sheetName":{"__rl":true,"mode":"list","value":"gid=0","cachedResultUrl":"https://docs.google.com/spreadsheets/d/1CVqkBpVaJ-OervAfC5TCPkfu-1Ys2LgFQbAZIeu4GNo/edit#gid=0","cachedResultName":"Sheet1"},"documentId":{"__rl":true,"mode":"list","value":"1CVqkBpVaJ-OervAfC5TCPkfu-1Ys2LgFQbAZIeu4GNo","cachedResultUrl":"https://docs.google.com/spreadsheets/d/1CVqkBpVaJ-OervAfC5TCPkfu-1Ys2LgFQbAZIeu4GNo/edit?usp=drivesdk","cachedResultName":"Multi Asset Daily Snapshot"}},"credentials":{"googleSheetsOAuth2Api":{"id":"YLNSloXJIWPMHBvp","name":"harshal chaudhari sheet acc"}},"typeVersion":4.7},{"id":"cf600bcc-c58d-4a61-8c86-36e9ed21d41b","name":"Append row in sheet","type":"n8n-nodes-base.googleSheets","position":[1344,944],"parameters":{"columns":{"value":{"status":"={{ 'FAILED' }}","error_code":"={{ $json.code || 'N/A' }}","error_message":"={{ $json.message || 'Unknown API error' }}","failed_symbol":"={{ $json.meta?.symbol || $json.symbol || 'N/A' }}","execution_date":"={{ $now }}","workflow_version":"v2.0-production"},"schema":[{"id":"execution_date","type":"string","display":true,"required":false,"displayName":"execution_date","defaultMatch":false,"canBeUsedToMatch":true},{"id":"error_message","type":"string","display":true,"required":false,"displayName":"error_message","defaultMatch":false,"canBeUsedToMatch":true},{"id":"failed_symbol","type":"string","display":true,"required":false,"displayName":"failed_symbol","defaultMatch":false,"canBeUsedToMatch":true},{"id":"status","type":"string","display":true,"required":false,"displayName":"status","defaultMatch":false,"canBeUsedToMatch":true},{"id":"error_code","type":"string","display":true,"required":false,"displayName":"error_code","defaultMatch":false,"canBeUsedToMatch":true},{"id":"workflow_version","type":"string","display":true,"required":false,"displayName":"workflow_version","defaultMatch":false,"canBeUsedToMatch":true}],"mappingMode":"defineBelow","matchingColumns":[],"attemptToConvertTypes":false,"convertFieldsToString":false},"options":{},"operation":"append","sheetName":{"__rl":true,"mode":"list","value":1278586321,"cachedResultUrl":"https://docs.google.com/spreadsheets/d/1CVqkBpVaJ-OervAfC5TCPkfu-1Ys2LgFQbAZIeu4GNo/edit#gid=1278586321","cachedResultName":"Error logs"},"documentId":{"__rl":true,"mode":"list","value":"1CVqkBpVaJ-OervAfC5TCPkfu-1Ys2LgFQbAZIeu4GNo","cachedResultUrl":"https://docs.google.com/spreadsheets/d/1CVqkBpVaJ-OervAfC5TCPkfu-1Ys2LgFQbAZIeu4GNo/edit?usp=drivesdk","cachedResultName":"Multi Asset Daily Snapshot"}},"credentials":{"googleSheetsOAuth2Api":{"id":"YLNSloXJIWPMHBvp","name":"harshal chaudhari sheet acc"}},"typeVersion":4.7},{"id":"7fe0942c-51e6-49f6-857f-c67a134b34a8","name":"Fetch Asset Prices","type":"n8n-nodes-base.httpRequest","position":[640,896],"parameters":{"url":"={{ $('Environment Config').first().json.api_base_url }}","options":{},"sendQuery":true,"queryParameters":{"parameters":[{"name":"symbol","value":"={{ $json.symbol }}"},{"name":"interval","value":"1day"},{"name":"outputsize","value":"1"},{"name":"apikey","value":"={{ $('Environment Config').first().json.twelve_api_key }}"}]}},"typeVersion":4.3},{"id":"e08ad6f6-e72f-447f-a236-443d94dd502a","name":"Sticky Note4","type":"n8n-nodes-base.stickyNote","position":[-816,752],"parameters":{"color":7,"width":640,"height":400,"content":"## Workflow Trigger & Configuration\n\nThis section initializes the workflow execution and sets up all required environment variables such as API base URL and authentication keys. It ensures that the workflow has the correct configuration before starting the data processing pipeline."},"typeVersion":1},{"id":"e6573189-685e-4df7-a899-b85cde1a733f","name":"Sticky Note5","type":"n8n-nodes-base.stickyNote","position":[-128,816],"parameters":{"color":7,"width":1184,"height":448,"content":"\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n## Asset Splitting & API Fetch Pipeline\nThis section handles **asset breakdown and controlled data fetching** by splitting grouped assets into **individual symbols** for processing. It ensures smooth execution using **rate-controlled batching** and **API throttling**, preventing overload or failures while fetching real-time market data.\nThe pipeline guarantees **efficient API utilization**, maintains **data consistency** and enables reliable retrieval of **latest asset prices** for further analysis."},"typeVersion":1},{"id":"f707ee9a-1228-40a3-802b-e71bece6cd81","name":"Sticky Note6","type":"n8n-nodes-base.stickyNote","position":[1104,768],"parameters":{"color":7,"width":640,"height":448,"content":"## Error Logging & Failure Alerts\n\nThis section manages **API failure handling and error tracking** by capturing failed responses and recording them for monitoring. It logs detailed error information into **Google Sheets** to maintain a history of issues for debugging and analysis.\nAdditionally, it triggers **real-time email alerts via Gmail** to notify about failures instantly, ensuring quick action and maintaining **workflow reliability and transparency**."},"typeVersion":1},{"id":"f9668280-cb6d-43aa-a4f6-44f30563e2cc","name":"Sticky Note7","type":"n8n-nodes-base.stickyNote","position":[432,256],"parameters":{"color":7,"width":1264,"height":496,"content":"## AI Analysis & Report Generation\n\nThis section transforms structured market data into **actionable financial insights** using an **AI-powered model**. It generates key outputs like **market summary, key movers, risk sentiment and outlook**, providing a clear understanding of market conditions.\n\nThe AI response is then **parsed and structured**, stored in **Google Sheets for tracking** and delivered via **automated email notifications**, ensuring consistent and timely reporting."},"typeVersion":1},{"id":"14c3b771-838e-4f1a-850c-37ea32ddd87e","name":"Validate API Response","type":"n8n-nodes-base.if","position":[864,896],"parameters":{"options":{},"conditions":{"options":{"version":3,"leftValue":"","caseSensitive":true,"typeValidation":"strict"},"combinator":"and","conditions":[{"id":"5f62f53f-e04d-4c78-8256-6814021efa44","operator":{"type":"string","operation":"equals"},"leftValue":"={{ $json.status }}","rightValue":"ok"}]}},"typeVersion":2.3},{"id":"7bd9a42f-4788-44ab-947c-9d22c6cd5805","name":"split assests & Symbols","type":"n8n-nodes-base.code","position":[-80,880],"parameters":{"jsCode":"const data = items[0].json;\nconst output = [];\n\n// Define all asset classes\nconst assetGroups = [\n  { asset_class: \"Indices\", symbols: data.Indices },\n  { asset_class: \"Forex\", symbols: data.Forex },\n  { asset_class: \"Commodities\", symbols: data.Commodities }\n];\n\n// Split each asset group into individual symbols\nfor (const group of assetGroups) {\n  const symbols = group.symbols.split(',');\n\n  for (const symbol of symbols) {\n    output.push({\n      json: {\n        asset_class: group.asset_class,\n        symbol: symbol.trim()\n      }\n    });\n  }\n}\n\nreturn output;"},"typeVersion":2}],"active":false,"pinData":{},"settings":{"availableInMCP":false,"executionOrder":"v1"},"versionId":"723be7a3-5ce7-48da-9d7a-ebfebade5dc5","connections":{"Insights":{"ai_languageModel":[[{"node":"AI Market Insights","type":"ai_languageModel","index":0}]]},"API Throttle":{"main":[[{"node":"Fetch Asset Prices","type":"main","index":0}]]},"Log API Error":{"main":[[{"node":"Append row in sheet","type":"main","index":0}]]},"Parse AI Output":{"main":[[{"node":"Log Daily Market Report","type":"main","index":0}]]},"Alert API Faliure":{"main":[[{"node":"Rate Controlled Queue","type":"main","index":0}]]},"Set Market Assets":{"main":[[{"node":"split assests & Symbols","type":"main","index":0}]]},"AI Market Insights":{"main":[[{"node":"Parse AI Output","type":"main","index":0}]]},"Environment Config":{"main":[[{"node":"Set Market Assets","type":"main","index":0}]]},"Fetch Asset Prices":{"main":[[{"node":"Validate API Response","type":"main","index":0}]]},"Append row in sheet":{"main":[[{"node":"Alert API Faliure","type":"main","index":0}]]},"Normalize Market Data":{"main":[[{"node":"AI Market Insights","type":"main","index":0}]]},"Rate Controlled Queue":{"main":[[{"node":"Normalize Market Data","type":"main","index":0}],[{"node":"API Throttle","type":"main","index":0}]]},"Validate API Response":{"main":[[{"node":"Rate Controlled Queue","type":"main","index":0}],[{"node":"Log API Error","type":"main","index":0}]]},"Log Daily Market Report":{"main":[[{"node":"Send Today's market summary","type":"main","index":0}]]},"split assests & Symbols":{"main":[[{"node":"Rate Controlled Queue","type":"main","index":0}]]},"Generate Daily Market Report":{"main":[[{"node":"Environment Config","type":"main","index":0}]]}}}