Moving Average Crossover Stock Alert Bot

{
  "name": "Moving Average Crossover Stock Alert Bot",
  "tags": [],
  "nodes": [
    {
      "id": "f3acef6a-a25d-4f8a-ba03-62731eb2caff",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -480,
        0
      ],
      "parameters": {
        "color": 4,
        "width": 700,
        "height": 200,
        "content": "## 📊 Stock Market Analysis Workflow\n\nThis workflow monitors **Golden Cross** and **Death Cross** signals for stocks using moving averages.\n\n**Golden Cross**: 60-day SMA crosses above 120-day SMA (Bullish)\n**Death Cross**: 60-day SMA crosses below 120-day SMA (Bearish)\n\nRuns daily at 5 PM on weekdays to analyze market close data."
      },
      "typeVersion": 1
    },
    {
      "id": "5860551a-7e28-4a2e-9b57-9c9eee052027",
      "name": "Trigger - Daily Close",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -420,
        260
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "triggerAtDay": [
                1,
                2,
                3,
                4,
                5
              ],
              "triggerAtHour": 17
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "db84cf85-94dd-401e-996c-30961d12d496",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        220,
        460
      ],
      "parameters": {
        "width": 400,
        "height": 220,
        "content": "## 📈 Data Collection Phase\n\n1. **Stock Selection**: Define ticker symbols for analysis\n2. **API Fetch**: Get daily price data from Alpha Vantage\n3. **Data Processing**: Extract yesterday's closing prices\n4. **Database Storage**: Store historical data for analysis\n\nCurrently monitoring: NVDA, JPM, PG, SPY"
      },
      "typeVersion": 1
    },
    {
      "id": "694bb8c3-5c80-44db-831d-70dce4ba872b",
      "name": "Compute 60/120 SMAs",
      "type": "n8n-nodes-base.code",
      "position": [
        1120,
        260
      ],
      "parameters": {
        "jsCode": "try {\n  // 1. Configurable windows\n  const SMA_SHORT = 60;\n  const SMA_LONG  = 120;\n  const NEEDED    = SMA_LONG + 1;  // long window + one extra day\n  // 2. Pull in all rows (each item.json has { id, symbol, Date, Close })\n  const rows = items.map(i => i.json);\n  // 3. Group rows by symbol\n  const bySymbol = {};\n  for (const r of rows) {\n    if (!bySymbol[r.symbol]) bySymbol[r.symbol] = [];\n    bySymbol[r.symbol].push(r);\n  }\n  // 4. SMA helper\n  function sma(arr, n, offset = 0) {\n    const slice = arr.slice(offset, offset + n);\n    return slice.reduce((sum, v) => sum + v, 0) / n;\n  }\n  // 5. Compute per‐symbol\n  const output = [];\n  for (const symbol of Object.keys(bySymbol)) {\n    const group = bySymbol[symbol]\n      .sort((a, b) => new Date(b.Date) - new Date(a.Date))  // newest→oldest\n      .slice(0, NEEDED);\n    if (group.length < NEEDED) {\n      output.push({\n        json: {\n          symbol,\n          error: `Not enough data for ${symbol}: ${group.length}/${NEEDED} days`\n        }\n      });\n      continue;\n    }\n    const closes = group.map(r => parseFloat(r.Close));\n    output.push({\n      json: {\n        symbol,\n        sma60_current:   sma(closes, SMA_SHORT, 0),\n        sma120_current:  sma(closes, SMA_LONG,  0),\n        sma60_previous:  sma(closes, SMA_SHORT, 1),\n        sma120_previous: sma(closes, SMA_LONG,  1),\n      }\n    });\n  }\n  return output;\n} catch (err) {\n  // global error fallback\n  return [{ json: { error: err.message } }];\n}"
      },
      "retryOnFail": false,
      "typeVersion": 2,
      "alwaysOutputData": false
    },
    {
      "id": "f6aae56c-2129-4435-8895-89759829cdcf",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        940,
        460
      ],
      "parameters": {
        "color": 5,
        "width": 380,
        "height": 260,
        "content": "## 🧮 Technical Analysis Engine\n\n**Simple Moving Averages (SMA)**:\n- 60-day SMA (short-term trend)\n- 120-day SMA (long-term trend)\n\n**Crossover Detection**:\n- Compares current vs previous day values\n- Identifies trend reversals in real-time\n- Requires 121 days of historical data"
      },
      "typeVersion": 1
    },
    {
      "id": "c9101e92-f8af-4ef6-b28e-18a1ccc932b5",
      "name": "Split - Tickers",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        20,
        260
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "symbol"
      },
      "typeVersion": 1
    },
    {
      "id": "c31e3d37-1926-46bc-86a9-f89e3927704b",
      "name": "Fetch Daily History",
      "type": "n8n-nodes-base.httpRequest",
      "onError": "continueRegularOutput",
      "position": [
        240,
        260
      ],
      "parameters": {
        "url": "https://www.alphavantage.co/query",
        "options": {},
        "sendQuery": true,
        "queryParameters": {
          "parameters": [
            {
              "name": "function",
              "value": "TIME_SERIES_DAILY"
            },
            {
              "name": "symbol",
              "value": "={{$json[\"symbol\"]}}"
            },
            {
              "name": "apikey",
              "value": "YOURKEYHERE"
            },
            {
              "name": "outputsize",
              "value": "compact"
            }
          ]
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "f9b56387-fef9-4efc-943b-ac0971f9d522",
      "name": "Set - Ticker List",
      "type": "n8n-nodes-base.set",
      "notes": "* IWM: Russell 2000 small-cap ETF – captures breadth/volatility outside the large-cap space\n\n* INTC: Intel – value-oriented semiconductor play that often lags/drifts differently than NVDA\n\n* JPM: JP Morgan – bellwether for the financial sector (banks & credit), interest-rate sensitivity\n\n* META: Meta Platforms – mega-cap digital advertising/social media momentum outside pure semis\n\n* NVDA: NVIDIA – leading-edge GPU/AI growth driver, often the pace-setter in tech rallies\n\n* PG: Procter & Gamble – defensive consumer staples, counter-cyclical when risk assets wobble\n\n* SPY: S\\&P 500 ETF – broad large-cap benchmark, anchors the overall market trend\n\n* TSLA: Tesla – high-beta auto/EV hybrid, adds extra swing-intensity vs. other tech names\n\n* XLB: Materials ETF – pure play on chemicals, metals & mining, driven by commodity cycles\n\n* XLE: Energy ETF – oil & gas sector, sensitive to crude swings, low correlation with semis\n\n* XLI: Industrials ETF – aerospace, transport & machinery, captures industrial-cycle turns\n\n* XLU: Utilities ETF – defensive “bond-like” sector, shines in risk-off/stress regimes\n\n* XLV: Health Care ETF – pharma/biotech/devices, often moves independently of the wider market",
      "position": [
        -200,
        260
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "ec70e7cd-3ee8-45d9-bf2e-c53483296ec9",
              "name": "symbol",
              "type": "array",
              "value": "[\"NVDA\",\"JPM\",\"PG\",\"SPY\"]"
            }
          ]
        }
      },
      "notesInFlow": false,
      "typeVersion": 3.4
    },
    {
      "id": "2aec21da-cbba-4574-9794-98ccdafb268b",
      "name": "Getting today's data",
      "type": "n8n-nodes-base.code",
      "position": [
        460,
        260
      ],
      "parameters": {
        "jsCode": "return items.map(item => {\n  const payload = item.json;\n  const ts      = payload[\"Time Series (Daily)\"];\n  \n  // ← GUARD against missing or bad TS object\n  if (!ts || typeof ts !== 'object') {\n    return {\n      json: {\n        symbol: payload[\"Meta Data\"]?.[\"2. Symbol\"] || null,\n        error:  payload.Note \n             || payload[\"Error Message\"] \n             || \"No time series returned\",\n      }\n    };\n  }\n\n  const dates = Object.keys(ts).sort((a,b) => new Date(a) - new Date(b));\n  if (dates.length < 2) {\n    throw new Error(\"Not enough data to pick the day before last\");\n  }\n\n  const date = dates[dates.length - 2];\n  const day  = ts[date];\n\n  return {\n    json: {\n      symbol: payload[\"Meta Data\"][\"2. Symbol\"],\n      date,\n      close: day[\"4. close\"],\n    }\n  };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "825fab86-cdea-49f3-b162-65b2d4e26ea0",
      "name": "Insert rows in a table",
      "type": "n8n-nodes-base.postgres",
      "position": [
        680,
        260
      ],
      "parameters": {
        "table": {
          "__rl": true,
          "mode": "list",
          "value": "historical_stocks",
          "cachedResultName": "historical_stocks"
        },
        "schema": {
          "__rl": true,
          "mode": "list",
          "value": "public"
        },
        "columns": {
          "value": {
            "Date": "={{ $json.date }}",
            "Close": "={{ $json.close }}",
            "symbol": "={{ $json.symbol }}"
          },
          "schema": [
            {
              "id": "id",
              "type": "number",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "id",
              "defaultMatch": true,
              "canBeUsedToMatch": true
            },
            {
              "id": "symbol",
              "type": "string",
              "display": true,
              "required": true,
              "displayName": "symbol",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Date",
              "type": "dateTime",
              "display": true,
              "required": true,
              "displayName": "Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Close",
              "type": "number",
              "display": true,
              "required": true,
              "displayName": "Close",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "id"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {
          "skipOnConflict": true
        }
      },
      "retryOnFail": false,
      "typeVersion": 2.6,
      "alwaysOutputData": false
    },
    {
      "id": "6389ccdd-02e0-48b8-93e8-730cbdfda1b6",
      "name": "HTTP Request",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        2200,
        260
      ],
      "parameters": {
        "url": "https://discord.com/api/webhooks/YOURWEBHOOKHERE",
        "method": "POST",
        "options": {},
        "sendBody": true,
        "sendQuery": true,
        "bodyParameters": {
          "parameters": [
            {
              "name": "content",
              "value": "={{$json[\"content\"]}}"
            }
          ]
        },
        "queryParameters": {
          "parameters": [
            {}
          ]
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "d0eb4106-9bb0-4752-bb68-59c9d5d15d1a",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2080,
        0
      ],
      "parameters": {
        "color": 7,
        "width": 380,
        "height": 220,
        "content": "## 🔔 Discord Notifications\n\n**Alert Types**:\n- 🟢 Golden Cross: Bullish signal detected\n- 🔴 Death Cross: Bearish signal detected  \n- 🟡 No Signal: Monitoring continues\n\n**Message Format**: Includes emoji, stock symbol, and signal description for easy identification."
      },
      "typeVersion": 1
    },
    {
      "id": "6420c403-0bf5-4de3-a82b-080292be75ec",
      "name": "If (📈)",
      "type": "n8n-nodes-base.if",
      "position": [
        1340,
        260
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "f2b85554-8cec-49f5-8bd4-cccc1e15cc6f",
              "operator": {
                "type": "number",
                "operation": "lte"
              },
              "leftValue": "={{ $json.sma60_previous }}",
              "rightValue": "={{ $json.sma120_previous }}"
            },
            {
              "id": "83d8772f-d217-4b76-84d1-b23bf245357c",
              "operator": {
                "type": "number",
                "operation": "gt"
              },
              "leftValue": "={{ $json.sma60_current }}",
              "rightValue": "={{ $json.sma120_current }}"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "1910f369-5404-45e6-a225-048b5fc34316",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1320,
        -120
      ],
      "parameters": {
        "color": 6,
        "width": 320,
        "height": 260,
        "content": "## 🚦 Signal Detection Logic\n\n**Golden Cross (📈)**: \n- Yesterday: 60-day ≤ 120-day SMA\n- Today: 60-day > 120-day SMA\n\n**Death Cross (📉)**:\n- Yesterday: 60-day ≥ 120-day SMA  \n- Today: 60-day < 120-day SMA\n\n**No Signal**: No crossover detected"
      },
      "typeVersion": 1
    },
    {
      "id": "1ef9e403-7021-45d5-91b6-cea5f7e30a3e",
      "name": "If (📉)",
      "type": "n8n-nodes-base.if",
      "position": [
        1560,
        360
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "f9e8087a-af4d-43e9-b0da-f12d9564b48f",
              "operator": {
                "type": "number",
                "operation": "gte"
              },
              "leftValue": "={{ $json.sma60_previous }}",
              "rightValue": "={{ $json.sma120_previous }}"
            },
            {
              "id": "53c773b1-9740-43d7-b5cd-04e8b6256ace",
              "operator": {
                "type": "number",
                "operation": "lt"
              },
              "leftValue": "={{ $json.sma60_current }}",
              "rightValue": "={{ $json.sma120_current }}"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "77a4c20b-a7a1-46e2-8ffa-70b4310d75f9",
      "name": "Set - No Signal Msg",
      "type": "n8n-nodes-base.set",
      "position": [
        1780,
        260
      ],
      "parameters": {
        "values": {
          "string": [
            {
              "name": "content",
              "value": "={{ `🟡↔️ No crossover today for ${$items().map(i=>i.json.symbol).sort((a,b)=>a.localeCompare(b)).join(\", \")}. Monitoring continues…` }}"
            }
          ]
        },
        "options": {},
        "keepOnlySet": true
      },
      "executeOnce": true,
      "typeVersion": 2
    },
    {
      "id": "c7f0cba2-2d46-483a-9f8e-ca741da5c2e6",
      "name": "Set - Death Cross Msg",
      "type": "n8n-nodes-base.set",
      "position": [
        1780,
        460
      ],
      "parameters": {
        "values": {
          "string": [
            {
              "name": "content",
              "value": "={{ `🔴📉 Death Cross Alert for **${$json[\"symbol\"]}**! The 60-day SMA has crossed below the 120-day SMA.` }}"
            }
          ]
        },
        "options": {},
        "keepOnlySet": true
      },
      "typeVersion": 2
    },
    {
      "id": "3926135c-6308-479d-9e1f-1da7248a01ea",
      "name": "Set - Golden Cross Msg",
      "type": "n8n-nodes-base.set",
      "position": [
        1780,
        60
      ],
      "parameters": {
        "values": {
          "string": [
            {
              "name": "content",
              "value": "={{ `🟢📈 Golden Cross Alert for **${$json[\"symbol\"]}**! The 60-day SMA has crossed above the 120-day SMA.` }}"
            }
          ]
        },
        "options": {},
        "keepOnlySet": true
      },
      "typeVersion": 2
    },
    {
      "id": "34474f1f-9caf-4d6e-bbd9-294aa8a73cfb",
      "name": "Execute a SQL query",
      "type": "n8n-nodes-base.postgres",
      "position": [
        900,
        260
      ],
      "parameters": {
        "query": "WITH numbered AS (\n  SELECT\n    *,\n    ROW_NUMBER() OVER (\n      PARTITION BY symbol\n      ORDER BY \"Date\" DESC\n    ) AS rn\n  FROM public.historical_stocks\nWHERE symbol IN ('NVDA','JPM','SPY','PG')\n)\nSELECT\n  id,\n  symbol,\n  \"Date\",\n  \"Close\"\nFROM numbered\nWHERE rn <= 121\nORDER BY symbol, \"Date\" DESC;",
        "options": {},
        "operation": "executeQuery"
      },
      "executeOnce": true,
      "typeVersion": 2.6
    }
  ],
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "connections": {
    "If (📈)": {
      "main": [
        [
          {
            "node": "Set - Golden Cross Msg",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "If (📉)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "If (📉)": {
      "main": [
        [
          {
            "node": "Set - Death Cross Msg",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Set - No Signal Msg",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split - Tickers": {
      "main": [
        [
          {
            "node": "Fetch Daily History",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set - Ticker List": {
      "main": [
        [
          {
            "node": "Split - Tickers",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Compute 60/120 SMAs": {
      "main": [
        [
          {
            "node": "If (📈)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Execute a SQL query": {
      "main": [
        [
          {
            "node": "Compute 60/120 SMAs",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Daily History": {
      "main": [
        [
          {
            "node": "Getting today's data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set - No Signal Msg": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Getting today's data": {
      "main": [
        [
          {
            "node": "Insert rows in a table",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set - Death Cross Msg": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Trigger - Daily Close": {
      "main": [
        [
          {
            "node": "Set - Ticker List",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Insert rows in a table": {
      "main": [
        [
          {
            "node": "Execute a SQL query",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set - Golden Cross Msg": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}