WeblineIndia This workflow corresponds to n8n.io template #16367 — we link there as the canonical source.
This workflow follows the Gmail → Googlegemini recipe pattern — see all workflows that pair these two integrations.
The workflow JSON
Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →
{
"id": "z8qeXNB9lCqWSZjn",
"name": "Commodity Diversification Analyzer with Risk Distribution Insights",
"tags": [],
"nodes": [
{
"id": "95024459-1c19-41d8-bed1-a1a98faf066c",
"name": "Set Trend UP",
"type": "n8n-nodes-base.set",
"position": [
1632,
416
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "edaa74c6-889e-42fc-8ea0-b775f5e8ce59",
"name": "gold_trend",
"type": "string",
"value": "UP"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "bea71374-2e7c-47f0-8c9b-b0255230d9ef",
"name": "Set Trend DOWN",
"type": "n8n-nodes-base.set",
"position": [
1632,
608
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "5741de0f-2761-476e-aceb-c8593bab0495",
"name": "gold_trend",
"type": "string",
"value": "DOWN"
}
]
},
"includeOtherFields": true
},
"typeVersion": 3.4
},
{
"id": "e6bd1f77-45c9-4c53-827e-5847033a2887",
"name": "Extract Gold Price Value",
"type": "n8n-nodes-base.set",
"position": [
416,
160
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "4cc6d123-6e75-48fd-bef9-6e9e44b74206",
"name": "gold price",
"type": "number",
"value": "={{ $json.price }}"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "b6d975b7-8267-4dfb-b60f-c7e1c32eda05",
"name": "Extract Silver Price Value",
"type": "n8n-nodes-base.set",
"position": [
416,
352
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "96c88389-b294-4c68-a4a5-6736e042e11f",
"name": "silver price",
"type": "number",
"value": "={{ $json.price }}"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "17b79622-ae84-4f84-92b7-76445dcc1108",
"name": "Merge Commodity & Trend Data",
"type": "n8n-nodes-base.merge",
"position": [
768,
480
],
"parameters": {
"numberInputs": 4
},
"typeVersion": 3.2
},
{
"id": "a0b4458f-c458-430c-81b1-7e4589a3c7f6",
"name": "Evaluate Market Trend Direction",
"type": "n8n-nodes-base.if",
"position": [
1456,
512
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 3,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "strict"
},
"combinator": "and",
"conditions": [
{
"id": "97584422-25cb-4423-8811-d141f176d3ad",
"operator": {
"type": "number",
"operation": "gt"
},
"leftValue": "={{ $('Aggregate Prices & Trend Data').item.json.latest_price_trend }}",
"rightValue": "={{ $('Aggregate Prices & Trend Data').item.json.previous_price_trend }}"
}
]
}
},
"typeVersion": 2.3
},
{
"id": "71dc4994-9f3c-4361-9b98-84ddd4735a15",
"name": "Generate AI Portfolio Analysis (Gemini)",
"type": "@n8n/n8n-nodes-langchain.googleGemini",
"position": [
2432,
512
],
"parameters": {
"modelId": {
"__rl": true,
"mode": "list",
"value": "models/gemini-2.5-flash",
"cachedResultName": "models/gemini-2.5-flash"
},
"options": {},
"messages": {
"values": [
{
"content": "=You are a professional commodity portfolio risk analyst \nwith expertise in metals and energy markets.\n\nAnalyze the portfolio strictly based on the provided data.\nUse actual numbers and percentages. Do not give generic answers.\n\n---\n\nPORTFOLIO DATA:\n- Gold Allocation: {{ $json.gold_pct }}%\n- Silver Allocation: {{ $json.silver_pct }}%\n- Crude Oil Allocation: {{ $json.oil_pct }}%\n- Gold Market Trend: {{ $json.gold_trend }}\n- HHI Concentration Score: {{ $json.hhi_score }}\n\n---\n\nRULES:\n- If any allocation > 60% \u2192 HIGH concentration risk\n- If HHI > 0.6 \u2192 Highly concentrated\n- If HHI 0.4-0.6 \u2192 Moderate concentration\n- If HHI < 0.4 \u2192 Well diversified\n- ALWAYS include % values in analysis\n- NEVER leave any field empty\n\n---\n\nOUTPUT FORMAT (STRICT \u2014 FOLLOW EXACTLY):\n\nASSESSMENT:\nWrite 2\u20133 sentences including % values, HHI score, and risk level.\n\nRISK1:\nWrite one clear risk with %.\n\nRISK2:\nWrite second risk.\n\nRISK3:\nWrite third risk.\n\nREC1:\nAction with % adjustment.\n\nREC2:\nAction with % adjustment.\n\nREC3:\nAction with % adjustment.\n\nREC4:\nAction with % adjustment.\n\nTREND:\nExplain impact of gold trend in 2 sentences.\n\nVERDICT:\nONLY one line: IMMEDIATE REBALANCING / MODERATE ADJUSTMENT / WELL DIVERSIFIED\n---\n\nFORMAT RULES (CRITICAL):\n- Each label must be on its own line\n- Content must start from the next line\n- Leave one line gap between sections\n- Do not hallucinate values\n- Only use provided numbers\n\nExample:\n\nASSESSMENT:\nThe portfolio is balanced...\n\nRISK1:\nGold concentration is...\n\nRISK2:\n...\n\n- Do NOT combine everything into one paragraph\n- Follow structure strictly"
}
]
},
"simplify": false,
"builtInTools": {}
},
"credentials": {
"googlePalmApi": {
"name": "<your credential>"
}
},
"typeVersion": 1.1
},
{
"id": "aba57051-4902-4699-b67f-b49ff79fb7f7",
"name": "Parse AI Response into Structured Fields",
"type": "n8n-nodes-base.code",
"position": [
2896,
512
],
"parameters": {
"jsCode": "const item = $input.first().json;\n\nconst text =\n item.candidates?.[0]?.content?.parts?.[0]?.text ||\n item.text ||\n \"\";\n\n// Normalize text (IMPORTANT FIX)\nconst normalizedText = text.replace(/([A-Z]+):/g, '\\n$1:\\n');\n\nconst lines = normalizedText\n .split('\\n')\n .map(l => l.trim())\n .filter(l => l.length > 0);\n\nfunction getField(prefix) {\n const index = lines.findIndex(l => l.startsWith(prefix + ':'));\n if (index === -1) return '';\n\n let value = lines[index].replace(prefix + ':', '').trim();\n\n if (!value && lines[index + 1]) {\n value = lines[index + 1].trim();\n }\n\n return value;\n}\n\nreturn [\n {\n json: {\n ...item,\n\n ai_assessment: getField('ASSESSMENT'),\n\n ai_risk1: getField('RISK1'),\n ai_risk2: getField('RISK2'),\n ai_risk3: getField('RISK3'),\n\n ai_rec1: getField('REC1'),\n ai_rec2: getField('REC2'),\n ai_rec3: getField('REC3'),\n ai_rec4: getField('REC4'),\n\n ai_trend: getField('TREND'),\n ai_verdict: getField('VERDICT')\n }\n }\n];"
},
"typeVersion": 2
},
{
"id": "cbd1d75c-a18d-4ce4-b05f-ef3b4fecfb25",
"name": "Generate HTML Report Template",
"type": "n8n-nodes-base.set",
"position": [
3136,
512
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "6c1df9f5-9d5a-49b7-98d8-9e2e81bfa93f",
"name": "report_html",
"type": "string",
"value": "=<html>\n<body style=\"font-family: Arial, sans-serif; padding: 30px; background-color: #f9f9f9;\">\n\n <!-- HEADER -->\n <div style=\"background-color: #2c3e50; padding: 20px; border-radius: 8px;\">\n <h2 style=\"color: white; margin:0;\">Commodity Diversification Report</h2>\n <p style=\"color: #bdc3c7; margin:5px 0 0 0;\">Generated: {{ $now }}</p>\n </div>\n\n <br/>\n\n <!-- PRICES TABLE -->\n <div style=\"background:white; padding:20px; border-radius:8px; margin-bottom:20px;\">\n <h3 style=\"color:#2c3e50;\"> Current Prices</h3>\n <table width=\"100%\" border=\"1\" cellpadding=\"10\" style=\"border-collapse:collapse; font-size:14px;\">\n <tr style=\"background:#2c3e50; color:white;\">\n <th>Commodity</th><th>Price (INR)</th><th>Category</th><th>Portfolio %</th>\n </tr>\n <tr style=\"background:#f2f2f2;\">\n <td> Gold</td>\n <td>{{ $('Aggregate Prices & Trend Data').item.json.gold_price }}</td>\n <td>Metals</td>\n <td>{{ $('calculate Portfolio Allocation & HHI Risk Score').item.json.gold_pct }}%</td>\n </tr>\n <tr>\n <td> Silver</td>\n <td>{{ $('Aggregate Prices & Trend Data').item.json.silver_price }}</td>\n <td>Metals</td>\n <td>{{ $('calculate Portfolio Allocation & HHI Risk Score').item.json.silver_pct }}%</td>\n </tr>\n <tr style=\"background:#f2f2f2;\">\n <td> Oil</td>\n <td>{{ $('Aggregate Prices & Trend Data').item.json.oil_price_inr }}</td>\n <td>Energy</td>\n <td>{{ $('calculate Portfolio Allocation & HHI Risk Score').item.json.oil_pct }}%</td>\n </tr>\n </table>\n </div>\n\n <!-- TREND & HHI score -->\n <div style=\"background:white; padding:20px; border-radius:8px; margin-bottom:20px;\">\n <h3 style=\"color:#2c3e50;\"> Trend & Risk Summary</h3>\n <table width=\"100%\" cellpadding=\"8\" style=\"font-size:14px;\">\n <tr>\n <td><strong>Gold Trend</strong></td>\n <td>\n <span style=\"background:#27ae60; color:white; padding:4px 10px; border-radius:4px;\">\n \n </span>\n </td>\n </tr>\n <tr>\n <td><strong>HHI Score</strong></td>\n <td>{{ $('calculate Portfolio Allocation & HHI Risk Score').item.json.hhi_score }}</td>\n </tr>\n </table>\n </div>\n\n <!-- ASSESSMENT -->\n <div style=\"background:white; padding:20px; border-radius:8px; margin-bottom:20px;\">\n <h3 style=\"color:#2c3e50;\"> Portfolio Assessment</h3>\n <p style=\"font-size:14px; line-height:1.8; color:#333;\">\n {{ $('Parse AI Response into Structured Fields').item.json.ai_assessment }}\n </p>\n </div>\n\n <!-- RISK FINDINGS -->\n <div style=\"background:white; padding:20px; border-radius:8px; margin-bottom:20px;\">\n <h3 style=\"color:#2c3e50;\"> Key Risk Findings</h3>\n <table width=\"100%\" border=\"1\" cellpadding=\"10\" style=\"border-collapse:collapse; font-size:14px;\">\n <tr style=\"background:#e74c3c; color:white;\"><th>#</th><th>Risk Finding</th></tr>\n <tr><td>1</td><td>{{ $('Parse AI Response into Structured Fields').item.json.ai_risk1 }}</td></tr>\n <tr style=\"background:#f2f2f2;\"><td>2</td><td>{{ $('Parse AI Response into Structured Fields').item.json.ai_risk2 }}</td></tr>\n <tr><td>3</td><td>{{ $('Parse AI Response into Structured Fields').item.json.ai_risk3 }}</td></tr>\n </table>\n </div>\n\n <!-- RECOMMENDATIONS -->\n <div style=\"background:white; padding:20px; border-radius:8px; margin-bottom:20px;\">\n <h3 style=\"color:#2c3e50;\"> Rebalancing Recommendations</h3>\n <table width=\"100%\" border=\"1\" cellpadding=\"10\" style=\"border-collapse:collapse; font-size:14px;\">\n <tr style=\"background:#27ae60; color:white;\"><th>#</th><th>Action</th></tr>\n <tr><td>1</td><td>{{ $('Parse AI Response into Structured Fields').item.json.ai_rec1 }}</td></tr>\n <tr style=\"background:#f2f2f2;\"><td>2</td><td>{{ $('Parse AI Response into Structured Fields').item.json.ai_rec2 }}</td></tr>\n <tr><td>3</td><td>{{ $('Parse AI Response into Structured Fields').item.json.ai_rec3 }}</td></tr>\n <tr style=\"background:#f2f2f2;\"><td>4</td><td>{{ $('Parse AI Response into Structured Fields').item.json.ai_rec4 }}</td></tr>\n </table>\n </div>\n\n <!-- TREND IMPACT -->\n <div style=\"background:white; padding:20px; border-radius:8px; margin-bottom:20px;\">\n <h3 style=\"color:#2c3e50;\"> Market Trend Impact</h3>\n <p style=\"font-size:14px; line-height:1.8; color:#333;\">\n {{ $('Parse AI Response into Structured Fields').item.json.ai_trend }}\n </p>\n </div>\n\n <!-- VERDICT -->\n <div style=\"background:#2c3e50; padding:20px; border-radius:8px; margin-bottom:20px;\">\n <h3 style=\"color:white; margin:0;\"> Final Verdict</h3>\n <p style=\"font-size:14px; font-weight:bold; color:#f1c40f; margin:10px 0 0 0;\">\n {{ $('Parse AI Response into Structured Fields').item.json.ai_verdict }}\n </p>\n </div>\n\n <!-- FOOTER -->\n <div style=\"text-align:center; color:gray; font-size:12px;\">\n <p>Auto-generated by your n8n Commodity Workflow</p>\n </div>\n\n</body>\n</html>\n```"
}
]
},
"includeOtherFields": true
},
"typeVersion": 3.4
},
{
"id": "56712e47-876d-4b9f-ad07-388a0e68c608",
"name": "Send Portfolio Report via Email",
"type": "n8n-nodes-base.gmail",
"position": [
3472,
512
],
"parameters": {
"sendTo": "={{ $('Initialize Configuration & API Keys').item.json.report_recipient_email }}",
"message": "={{ $json.report_html }}",
"options": {},
"subject": "=Commodity Diversification Report - {{ $now }}"
},
"credentials": {
"gmailOAuth2": {
"name": "<your credential>"
}
},
"typeVersion": 2.2
},
{
"id": "9ee09370-9eab-4568-9465-48d8326e4c01",
"name": "Sticky Note2",
"type": "n8n-nodes-base.stickyNote",
"position": [
0,
0
],
"parameters": {
"color": 7,
"width": 272,
"height": 704,
"content": "## Commodity Price Fetch\nFetches real-time prices for gold, silver, and crude oil from external APIs."
},
"typeVersion": 1
},
{
"id": "acaead55-c28a-4aa7-bb5e-3ad1aa664005",
"name": "Sticky Note3",
"type": "n8n-nodes-base.stickyNote",
"position": [
320,
0
],
"parameters": {
"color": 7,
"width": 304,
"height": 736,
"content": "## Data Processing\nExtracts price values and converts oil price from USD to INR."
},
"typeVersion": 1
},
{
"id": "0a1c01d9-e679-47ba-a2bc-2b4e4f4abb4e",
"name": "Sticky Note4",
"type": "n8n-nodes-base.stickyNote",
"position": [
32,
784
],
"parameters": {
"color": 7,
"width": 624,
"height": 272,
"content": "## Trend Data Processing\nRetrieves latest and previous values for analysis."
},
"typeVersion": 1
},
{
"id": "e6b76de4-a539-4ea8-bc4b-a5e9aa13bb10",
"name": "Sticky Note5",
"type": "n8n-nodes-base.stickyNote",
"position": [
720,
368
],
"parameters": {
"color": 7,
"width": 656,
"height": 416,
"content": "## Data Merge & Structuring\nCombines all data into a structured dataset with labels and categories."
},
"typeVersion": 1
},
{
"id": "3d29a7a6-80d2-4e40-9d4d-4a00e2205784",
"name": "Sticky Note6",
"type": "n8n-nodes-base.stickyNote",
"position": [
1408,
352
],
"parameters": {
"color": 7,
"width": 624,
"height": 416,
"content": "## Trend Analysis Logic\nCompares price values to determine whether the market trend is upward or downward."
},
"typeVersion": 1
},
{
"id": "fce0fa24-3d13-4641-8aa3-14c0fb4ed93e",
"name": "calculate Portfolio Allocation & HHI Risk Score",
"type": "n8n-nodes-base.code",
"position": [
2256,
1024
],
"parameters": {
"jsCode": "const item = $input.first().json;\n\n// Always fetch gold_trend explicitly\nconst gold_trend =\n $('Finalize Trend Output').first().json.gold_trend || 'UNKNOWN';\n\nlet gold_value = 0, silver_value = 0, oil_value = 0;\n\nconst rows = $('Load Portfolio Data from Sheets').all();\n\nfor (const row of rows) {\n const asset = (row.json.commodity_name || '').toLowerCase();;\n const value = Number(row.json.buy_price) || 0;\n\n if (asset.includes('gold')) gold_value = value;\n if (asset.includes('silver')) silver_value = value;\n if (asset.includes('oil')) oil_value = value;\n}\n// Step B \u2014 Total portfolio value\nconst total = gold_value + silver_value + oil_value;\n\n// Step C \u2014 Percentage allocation\nconst gold_pct = (gold_value / total) * 100;\nconst silver_pct = (silver_value / total) * 100;\nconst oil_pct = (oil_value / total) * 100;\n\n// Step D \u2014 HHI Score (STANDARD SCALE 0\u201310000 )\nconst hhi = ((gold_pct/100)**2 + (silver_pct/100)**2 + (oil_pct/100)**2);;\n\n// Step E \u2014 Correct Risk Logic (MATCHES GEMINI PROMPT )\nlet risk_flag;\nif (hhi > 0.6) risk = \"HIGH\";\nelse if (hhi > 0.4) risk = \"MEDIUM\";\nelse risk = \"LOW\";\n\n// Step F \u2014 Return everything\nreturn [{\n json: {\n ...item,\n gold_trend, \n\n gold_value,\n silver_value,\n oil_value,\n total_value: total,\n\n gold_pct: gold_pct.toFixed(2),\n silver_pct: silver_pct.toFixed(2),\n oil_pct: oil_pct.toFixed(2),\n\n hhi_score: hhi.toFixed(2), \n\n risk_flag\n }\n}];"
},
"typeVersion": 2
},
{
"id": "0645660f-7e50-4a11-a659-ced941242d1d",
"name": "Sticky Note8",
"type": "n8n-nodes-base.stickyNote",
"position": [
2384,
368
],
"parameters": {
"color": 7,
"width": 368,
"height": 416,
"content": "## AI Insights Generation\nGenerates structured portfolio analysis including risks, recommendations, and verdict."
},
"typeVersion": 1
},
{
"id": "dd841be5-8009-4cf6-8cf3-fc4187cd5f01",
"name": "Sticky Note9",
"type": "n8n-nodes-base.stickyNote",
"position": [
2816,
368
],
"parameters": {
"color": 7,
"width": 512,
"height": 416,
"content": "## AI Response Parsing & Report Generation\n\nExtracts structured sections like assessment, risks, and recommendations from AI output.Builds a formatted HTML report with portfolio data, insights, and analysis."
},
"typeVersion": 1
},
{
"id": "60facb8e-9e2e-4df1-b7fa-c1e95b08c3f0",
"name": "Sticky Note10",
"type": "n8n-nodes-base.stickyNote",
"position": [
3376,
368
],
"parameters": {
"color": 7,
"width": 288,
"height": 416,
"content": "## Email Delivery\nSends the final generated report to the user via email."
},
"typeVersion": 1
},
{
"id": "039879db-8132-4205-96e0-204eaf34b12f",
"name": "Start Workflow",
"type": "n8n-nodes-base.manualTrigger",
"position": [
-1040,
448
],
"parameters": {},
"typeVersion": 1
},
{
"id": "3186ea2e-30af-4ce4-83d9-b65f699e31d4",
"name": "Initialize Configuration & API Keys",
"type": "n8n-nodes-base.set",
"position": [
-816,
448
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "d3db1d28-91e6-4942-b5cd-1e6d63c3f492",
"name": "goldapi_key",
"type": "string",
"value": ""
},
{
"id": "+1234567890f6-87b0-0664bab3c5e8",
"name": "eia_api_key",
"type": "string",
"value": ""
},
{
"id": "efebe445-3693-4ff9-aa77-39f599ccedc5",
"name": "=usd_to_inr",
"type": "string",
"value": " 83.5"
},
{
"id": "1ffba0e5-01ab-49ac-bb75-c6b0b50550f3",
"name": "twelvedata_key",
"type": "string",
"value": ""
}
]
}
},
"typeVersion": 3.4
},
{
"id": "eda1d4fa-39c1-4902-826f-8ba5a4ee1483",
"name": "Load Portfolio Data from Sheets",
"type": "n8n-nodes-base.googleSheets",
"position": [
-576,
1056
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1WQgUOcCn1fiGihSN2S7z5OvwhtsiIJYR48mBdhoLzr8/edit#gid=0",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1WQgUOcCn1fiGihSN2S7z5OvwhtsiIJYR48mBdhoLzr8",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1WQgUOcCn1fiGihSN2S7z5OvwhtsiIJYR48mBdhoLzr8/edit?usp=drivesdk",
"cachedResultName": "Read_Portfolio"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"name": "<your credential>"
}
},
"typeVersion": 4.7
},
{
"id": "54640662-8ce8-4406-bfcc-e8d66fcd5da4",
"name": "Get Gold Price (INR)",
"type": "n8n-nodes-base.httpRequest",
"position": [
80,
160
],
"parameters": {
"url": "https://www.goldapi.io/api/XAU/INR",
"options": {
"timeout": 15000
},
"sendHeaders": true,
"headerParameters": {
"parameters": [
{
"name": "x-access-token",
"value": "={{ $('Initialize Configuration & API Keys').item.json.goldapi_key }}"
},
{
"name": "Content-Type",
"value": " application/json"
}
]
}
},
"executeOnce": true,
"typeVersion": 4.4
},
{
"id": "e811d7dc-4963-42b0-92e2-4c5d01738c4a",
"name": "Get Silver Price (INR)",
"type": "n8n-nodes-base.httpRequest",
"position": [
80,
352
],
"parameters": {
"url": "https://www.goldapi.io/api/XAG/INR",
"options": {},
"sendHeaders": true,
"headerParameters": {
"parameters": [
{
"name": "x-access-token",
"value": "={{ $('Initialize Configuration & API Keys').item.json.goldapi_key }}"
},
{
"name": "Content-Type",
"value": " application/json"
}
]
}
},
"executeOnce": true,
"typeVersion": 4.4
},
{
"id": "51a986e4-9c61-4065-98af-922637bdd788",
"name": "Get Crude Oil Price (USD)",
"type": "n8n-nodes-base.httpRequest",
"position": [
80,
544
],
"parameters": {
"url": "=https://api.eia.gov/v2/petroleum/pri/spt/data",
"options": {},
"sendQuery": true,
"queryParameters": {
"parameters": [
{
"name": "api_key",
"value": "={{ $('Initialize Configuration & API Keys').item.json.eia_api_key }}"
},
{
"name": "data[0]",
"value": "value"
}
]
}
},
"executeOnce": true,
"typeVersion": 4.4
},
{
"id": "5598b569-01e4-41f7-a674-04ffef75c008",
"name": "Get Gold Trend Time Series",
"type": "n8n-nodes-base.httpRequest",
"position": [
112,
864
],
"parameters": {
"url": "=https://api.twelvedata.com/time_series",
"options": {},
"sendQuery": true,
"sendHeaders": true,
"queryParameters": {
"parameters": [
{
"name": "apikey",
"value": "={{ $('Initialize Configuration & API Keys').item.json.twelvedata_key }}"
},
{
"name": "symbol",
"value": "GLD"
},
{
"name": "interval",
"value": "1day"
},
{
"name": "outputsize",
"value": "2"
},
{}
]
},
"headerParameters": {
"parameters": [
{
"name": "Content-Type",
"value": "application/json"
}
]
}
},
"executeOnce": true,
"typeVersion": 4.4
},
{
"id": "0546c93f-d57e-420f-b095-3f496fadaed1",
"name": "Convert Oil Price USD to INR",
"type": "n8n-nodes-base.set",
"position": [
432,
544
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "a3317090-cf73-4f4f-bde7-004491f1231a",
"name": "oil_price_usd",
"type": "number",
"value": "={{ $json.response.data[0].value }}"
},
{
"id": "8438f9a9-8895-455f-b10b-eb8c5da04160",
"name": "oil_price_inr",
"type": "number",
"value": "={{ Number($json.response.data[0].value) * Number($(\"Initialize Configuration & API Keys\").item.json.usd_to_inr) }}"
}
]
}
},
"executeOnce": true,
"typeVersion": 3.4
},
{
"id": "e645bea6-29a5-492f-b54a-3c0aa643a81b",
"name": "Extract Trend Values (Latest vs Previous)",
"type": "n8n-nodes-base.set",
"position": [
416,
864
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "28b2cb3e-8f5b-4716-9a05-9d36d7a8eb19",
"name": "latest_price_trend",
"type": "string",
"value": "={{ $json.values[0].close }}"
},
{
"id": "733943b2-b85f-4ae3-b3a3-ef9e75c3de77",
"name": "previous_price_trend",
"type": "string",
"value": "={{ $json.values[1].close }}"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "74f8fc98-d43a-416c-8860-3759b4786899",
"name": "Aggregate Prices & Trend Data",
"type": "n8n-nodes-base.code",
"position": [
976,
512
],
"parameters": {
"jsCode": "const items = $input.all();\n\nlet gold_price, silver_price, oil_price_usd, oil_price_inr;\nlet latest_price_trend, previous_price_trend;\n\nfor (const item of items) {\n const d = item.json;\n\n // Prices\n if (d['gold price'] != null) {\n gold_price = d['gold price'];\n }\n if (d['silver price'] != null) {\n silver_price = d['silver price'];\n }\n if (d['oil_price_usd'] != null) {\n oil_price_usd = d['oil_price_usd'];\n }\n if (d['oil_price_inr'] != null) {\n oil_price_inr = d['oil_price_inr'];\n }\n\n // FIXED: Use TwelveData extracted fields\n if (d.latest_price_trend != null) {\n latest_price_trend = Number(d.latest_price_trend);\n }\n\n if (d.previous_price_trend != null) {\n previous_price_trend = Number(d.previous_price_trend);\n }\n\n // OPTIONAL FALLBACK (if future API changes)\n if (latest_price_trend == null || previous_price_trend == null) {\n const timeSeries = d['Time Series (Daily)'];\n if (timeSeries) {\n const dates = Object.keys(timeSeries).sort().reverse();\n latest_price_trend = Number(timeSeries[dates[0]]['4. close']);\n previous_price_trend = Number(timeSeries[dates[1]]['4. close']);\n }\n }\n}\n\nreturn [\n {\n json: {\n gold_price,\n silver_price,\n oil_price_usd,\n oil_price_inr,\n latest_price_trend,\n previous_price_trend,\n timestamp: new Date().toISOString()\n }\n }\n];"
},
"executeOnce": false,
"typeVersion": 2
},
{
"id": "dbc0604d-a718-4167-b444-377f2e55c90a",
"name": "Tag Commodities (Category + Symbol)",
"type": "n8n-nodes-base.set",
"position": [
1216,
512
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "2f06ae57-9406-4e6a-9897-7be095480e7b",
"name": "gold_category",
"type": "string",
"value": "Metals"
},
{
"id": "988ec5ff-d90b-4504-b36e-cf31922d908e",
"name": "silver_category",
"type": "string",
"value": "Metals"
},
{
"id": "827eaa6c-d81c-491a-9ef2-d2993de1c508",
"name": "oil_category",
"type": "string",
"value": "Energy"
},
{
"id": "c1f10940-6c65-4ae4-bc41-d3834c82e236",
"name": "gold_label",
"type": "string",
"value": "XAU"
},
{
"id": "dd47a633-a2d6-4cd8-8e40-e5b036dd56ca",
"name": "silver_label",
"type": "string",
"value": "XAG"
},
{
"id": "7aff7f4d-f0ca-4b40-9ffb-707809468d65",
"name": "oil_label",
"type": "string",
"value": "CRUDE"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "010c932b-17af-4484-86f3-7b7e5eb41a2c",
"name": "Finalize Trend Output",
"type": "n8n-nodes-base.merge",
"position": [
1872,
512
],
"parameters": {},
"typeVersion": 3.2
},
{
"id": "78adcb72-0939-49fb-ab31-5577f608f51a",
"name": "Merge Portfolio + Market Data",
"type": "n8n-nodes-base.merge",
"position": [
2048,
1024
],
"parameters": {},
"typeVersion": 3.2
},
{
"id": "3724a71b-5315-49bb-a7f0-7111f6f176d7",
"name": "Sticky Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1088,
240
],
"parameters": {
"color": 7,
"width": 448,
"height": 416,
"content": "## Initialize Config & API Keys\nStores all required API keys, thresholds, and global configuration values.Centralizes settings like API credentials, risk thresholds, currency conversion rate, and email recipient for easy management."
},
"typeVersion": 1
},
{
"id": "fa5455da-b22b-497f-aaa7-8eb5bf45e4a1",
"name": "Sticky Note1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-672,
912
],
"parameters": {
"color": 7,
"width": 304,
"height": 320,
"content": "## Load Portfolio Data from Sheets\nProvides the base input data required to calculate portfolio allocation, diversification, and risk analysis."
},
"typeVersion": 1
},
{
"id": "b3de132a-fc91-4e06-933d-55e847674b46",
"name": "Sticky Note7",
"type": "n8n-nodes-base.stickyNote",
"position": [
2000,
880
],
"parameters": {
"color": 7,
"width": 464,
"height": 432,
"content": "## Merge Portfolio + Market Data & Calculate Risk\nCombines portfolio data with real-time market prices and trends, then calculates commodity allocation percentages and HHI risk score."
},
"typeVersion": 1
},
{
"id": "6fb3c7e8-abe0-4119-9677-b3774f9fd667",
"name": "Sticky Note11",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2480,
-256
],
"parameters": {
"width": 1200,
"height": 1296,
"content": "# How It Works\n### This workflow automatically performs commodity portfolio diversification and risk analysis using real-time market data. Once the workflow starts, it fetches live Gold, Silver, and Crude Oil prices from external APIs and retrieves portfolio holdings from Google Sheets. The workflow then calculates portfolio allocation percentages, HHI concentration score, and market trend direction. Using AI, it generates a detailed portfolio assessment, risk findings, rebalancing recommendations, market trend impact analysis, and a final investment verdict. After the analysis is completed, the workflow creates a professional HTML report and automatically sends it to the configured recipient via email. This makes the entire portfolio monitoring and risk assessment process fully automated from market data collection to investor reporting.\n\n\n# Setup Steps\n\n## Prepare Google Sheets\nCreate a portfolio sheet containing commodity holdings such as Gold, Silver, and Crude Oil with purchase values used for allocation calculations.\n\n## Connect Accounts in n8n\nAdd credentials for Google Sheets, Google Gemini, Gmail, GoldAPI, TwelveData, and EIA API.\n\n## Add Trigger\nStart the workflow using a Manual Trigger or Scheduler to run portfolio analysis automatically.\n\n## Add Market Data Collection Nodes\nFetch real-time Gold and Silver prices from GoldAPI, Crude Oil prices from EIA API, and Gold trend data from TwelveData.\n\n## Process & Aggregate Data\nExtract commodity prices, convert Oil prices from USD to INR, aggregate market data, and calculate portfolio allocation percentages and HHI concentration score.\n\n## Analyze Market Trend\nCompare latest and previous Gold prices to determine whether the market trend is moving upward or downward.\n\n## Generate AI Risk Assessment\nUse Gemini AI to create a structured portfolio assessment, risk analysis, diversification recommendations, trend impact explanation, and final portfolio verdict.\n\n## Build HTML Report\nGenerate a professional HTML report containing commodity prices, allocation percentages, risk findings, recommendations, trend insights, and final verdict.\n\n## Send Email Report\nDeliver the generated portfolio diversification report automatically to stakeholders through Gmail with complete analysis and recommendations."
},
"typeVersion": 1
}
],
"active": false,
"settings": {
"binaryMode": "separate",
"executionOrder": "v1"
},
"versionId": "ec7ad257-36f1-4ba9-bb30-c1957490db0d",
"nodeGroups": [],
"connections": {
"Set Trend UP": {
"main": [
[
{
"node": "Finalize Trend Output",
"type": "main",
"index": 0
}
]
]
},
"Set Trend DOWN": {
"main": [
[
{
"node": "Finalize Trend Output",
"type": "main",
"index": 1
}
]
]
},
"Start Workflow": {
"main": [
[
{
"node": "Initialize Configuration & API Keys",
"type": "main",
"index": 0
}
]
]
},
"Get Gold Price (INR)": {
"main": [
[
{
"node": "Extract Gold Price Value",
"type": "main",
"index": 0
}
]
]
},
"Finalize Trend Output": {
"main": [
[
{
"node": "Merge Portfolio + Market Data",
"type": "main",
"index": 0
}
]
]
},
"Get Silver Price (INR)": {
"main": [
[
{
"node": "Extract Silver Price Value",
"type": "main",
"index": 0
}
]
]
},
"Extract Gold Price Value": {
"main": [
[
{
"node": "Merge Commodity & Trend Data",
"type": "main",
"index": 0
}
]
]
},
"Get Crude Oil Price (USD)": {
"main": [
[
{
"node": "Convert Oil Price USD to INR",
"type": "main",
"index": 0
}
]
]
},
"Extract Silver Price Value": {
"main": [
[
{
"node": "Merge Commodity & Trend Data",
"type": "main",
"index": 1
}
]
]
},
"Get Gold Trend Time Series": {
"main": [
[
{
"node": "Extract Trend Values (Latest vs Previous)",
"type": "main",
"index": 0
}
]
]
},
"Convert Oil Price USD to INR": {
"main": [
[
{
"node": "Merge Commodity & Trend Data",
"type": "main",
"index": 2
}
]
]
},
"Merge Commodity & Trend Data": {
"main": [
[
{
"node": "Aggregate Prices & Trend Data",
"type": "main",
"index": 0
}
]
]
},
"Aggregate Prices & Trend Data": {
"main": [
[
{
"node": "Tag Commodities (Category + Symbol)",
"type": "main",
"index": 0
}
]
]
},
"Generate HTML Report Template": {
"main": [
[
{
"node": "Send Portfolio Report via Email",
"type": "main",
"index": 0
}
]
]
},
"Merge Portfolio + Market Data": {
"main": [
[
{
"node": "calculate Portfolio Allocation & HHI Risk Score",
"type": "main",
"index": 0
}
]
]
},
"Evaluate Market Trend Direction": {
"main": [
[
{
"node": "Set Trend UP",
"type": "main",
"index": 0
}
],
[
{
"node": "Set Trend DOWN",
"type": "main",
"index": 0
}
]
]
},
"Load Portfolio Data from Sheets": {
"main": [
[
{
"node": "Merge Portfolio + Market Data",
"type": "main",
"index": 1
}
]
]
},
"Initialize Configuration & API Keys": {
"main": [
[
{
"node": "Load Portfolio Data from Sheets",
"type": "main",
"index": 0
},
{
"node": "Get Gold Price (INR)",
"type": "main",
"index": 0
},
{
"node": "Get Silver Price (INR)",
"type": "main",
"index": 0
},
{
"node": "Get Crude Oil Price (USD)",
"type": "main",
"index": 0
},
{
"node": "Get Gold Trend Time Series",
"type": "main",
"index": 0
}
]
]
},
"Tag Commodities (Category + Symbol)": {
"main": [
[
{
"node": "Evaluate Market Trend Direction",
"type": "main",
"index": 0
}
]
]
},
"Generate AI Portfolio Analysis (Gemini)": {
"main": [
[
{
"node": "Parse AI Response into Structured Fields",
"type": "main",
"index": 0
}
]
]
},
"Parse AI Response into Structured Fields": {
"main": [
[
{
"node": "Generate HTML Report Template",
"type": "main",
"index": 0
}
]
]
},
"Extract Trend Values (Latest vs Previous)": {
"main": [
[
{
"node": "Merge Commodity & Trend Data",
"type": "main",
"index": 3
}
]
]
},
"calculate Portfolio Allocation & HHI Risk Score": {
"main": [
[
{
"node": "Generate AI Portfolio Analysis (Gemini)",
"type": "main",
"index": 0
}
]
]
}
}
}
Credentials you'll need
Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.
gmailOAuth2googlePalmApigoogleSheetsOAuth2Api
Pro
For the full experience including quality scoring and batch install features for each workflow upgrade to Pro
About this workflow
This workflow reads a gold/silver/oil portfolio from Google Sheets, pulls current commodity prices and a recent gold trend from GoldAPI, EIA, and Twelve Data, calculates allocation and HHI concentration risk, generates a Gemini analysis, and emails an HTML diversification report…
Source: https://n8n.io/workflows/16367/ — original creator credit. Request a take-down →
Related workflows
Workflows that share integrations, category, or trigger type with this one. All free to copy and import.
Generate three recipes using AI for quick meal options. Uses formTrigger, googleGemini, httpRequest, googleSheets. Event-driven trigger; 18 nodes.
Form Trigger, Google Gemini, HTTP Request +2
This workflow automatically generates and sends personalized client emails using portfolio data and live market trends. It fetches client details from Google Sheets, retrieves market news via API, fil
HTTP Request, Google Sheets, Google Gemini +1
This workflow is a complete outbound automation system that discovers local businesses, extracts contact emails, generates personalized cold emails using AI, and runs a multi-step follow-up sequence —
Stop And Error, Google Sheets, HTTP Request +2
This intelligent email automation workflow helps you maximize engagement through domain-based outreach. It utilizes AI-powered personalization and strategic follow-ups to increase response rates. The
Gmail, HTTP Request, Google Sheets +1
Sales Lead Qualifier. Uses telegramTrigger, googleSheets, telegram, googleGemini. Event-driven trigger; 41 nodes.
Telegram Trigger, Google Sheets, Telegram +3