感情分析ロボット

• •ターゲットAPIの認証情報が必要な場合あり
• •Google Sheets API認証情報
• •Google Gemini API Key

{
  "meta": {
    "instanceId": "34d07950904120624117fd89d9d9b4f13d9fa466720a0972ac5aa843f9eb8cb8",
    "templateCredsSetupCompleted": true
  },
  "name": "Sentiment Analysis Bot",
  "tags": [],
  "nodes": [
    {
      "id": "0d063cec-e739-48b6-9698-0ac586f147ad",
      "name": "AIエージェント",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        1040,
        1620
      ],
      "parameters": {
        "text": "=You are a stock sentiment analyzer. Your task is to evaluate news content for their potential impact on a specific stock.\n\nInput Format:\n    User input is in the following format:\n        Symbol: (The stock symbol also called ticker symbol)\n        title: (News headline that you have to analyze for sentiment of the given stock)\ncontent:(the content of the news to analyze)\nAnalysis Guidelines:\n    Evaluate how the news might affect the price of only the stock specified by the user in the input and generate a sentiment score between -1 and 1.\n    A score close to -1 indicates a strong negative impact, suggesting the news could significantly drive the stock price down.\n    A score near 0 represents a neutral impact, implying little to no effect on the stock price.\n    Conversely, a score close to 1 reflects a strong positive impact, likely driving the stock price up.\n    When generating the score, consider whether the news is surprising i.e., if it contains new information - or already priced in.\n    Explain in detail the rationale behind the score, highlighting why the news is positive, negative, or neutral for the given stock's price.\nOutput Format:\n    Return the result as JSON in the following format:\n\n        { symbol: (The stock symbol also called ticker symbol),\"sentiment_score\": (The sentiment score - float between -1 and 1), \"rationale\": (Your explanation for the score)}\nProvide the JSON output only. Do not include any other text.\n\nReal stock Symbol:\n{{$('loop_over_tickers').all()[0].json.ticker}}\n{{ $('join_articles_into_1').all()[0].json.fullString }}\n",
        "options": {},
        "promptType": "define"
      },
      "executeOnce": false,
      "typeVersion": 2
    },
    {
      "id": "a9bec4e8-b9df-4868-b6c3-072316fe1ed9",
      "name": "Google Gemini Chat Model1",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        1028,
        1840
      ],
      "parameters": {
        "options": {
          "maxOutputTokens": 2048
        },
        "modelName": "models/gemini-2.0-flash"
      },
      "credentials": {
        "googlePalmApi": {
          "id": "ynRNNwts1fakC7X4",
          "name": "Google Gemini(PaLM) Api account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "0b197810-0002-4f2c-8122-1db9a6a81558",
      "name": "EODHDから記事を取得",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        160,
        1520
      ],
      "parameters": {
        "url": "https://eodhd.com/api/news",
        "options": {},
        "sendQuery": true,
        "authentication": "genericCredentialType",
        "genericAuthType": "httpQueryAuth",
        "queryParameters": {
          "parameters": [
            {
              "name": "s",
              "value": "={{ $json.ticker }}"
            },
            {
              "name": "offset",
              "value": "0"
            },
            {
              "name": "limit",
              "value": "10"
            },
            {
              "name": "fmt",
              "value": "json"
            }
          ]
        }
      },
      "credentials": {
        "httpQueryAuth": {
          "id": "p1dJbEo98pH5SVih",
          "name": "Query Auth account"
        }
      },
      "executeOnce": false,
      "typeVersion": 4.2,
      "alwaysOutputData": false
    },
    {
      "id": "392f7ccd-aba3-4911-859a-556535f45eea",
      "name": "loop_over_tickers",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        -200,
        1640
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "6e5b9817-b6ec-4c0a-98fd-b41340ab6230",
      "name": "Read_tickers_from_Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -420,
        1640
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 470128021,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1tQDBVDqn5v08GOsjupjV8o3Jzqd4-fKoSoGhWLEOTww/edit#gid=470128021",
          "cachedResultName": "stocks"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1tQDBVDqn5v08GOsjupjV8o3Jzqd4-fKoSoGhWLEOTww",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1tQDBVDqn5v08GOsjupjV8o3Jzqd4-fKoSoGhWLEOTww/edit?usp=drivesdk",
          "cachedResultName": "Stock Sentiment"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "Bpltco7Lqc7P73Qp",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "e68be220-53b9-4b3d-a543-98a916382d42",
      "name": "If_ticker_not_valid",
      "type": "n8n-nodes-base.if",
      "position": [
        380,
        1520
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "770d6272-1cde-44d7-9e15-fe5d7c28ba36",
              "operator": {
                "name": "filter.operator.equals",
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $ifEmpty($input.all().toJsonString(),'True') }}",
              "rightValue": "True"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "bfc2cc77-5fc2-41ae-9e2a-22fed368c852",
      "name": "Write_in_google_sheets_invalid_ticker",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        600,
        1420
      ],
      "parameters": {
        "columns": {
          "value": {
            "date": "={{$today}}\n",
            "stock": "={{ $('loop_over_tickers').item.json.ticker }}",
            "sentimentScore": "Invaild Ticker"
          },
          "schema": [
            {
              "id": "date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "stock",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "stock",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "sentimentScore",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "sentimentScore",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "rational",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "rational",
              "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/1tQDBVDqn5v08GOsjupjV8o3Jzqd4-fKoSoGhWLEOTww/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1tQDBVDqn5v08GOsjupjV8o3Jzqd4-fKoSoGhWLEOTww",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1tQDBVDqn5v08GOsjupjV8o3Jzqd4-fKoSoGhWLEOTww/edit?usp=drivesdk",
          "cachedResultName": "Stock Sentiment"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "Bpltco7Lqc7P73Qp",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "81a16d3a-cdc4-4ad3-8769-b858a999f0ed",
      "name": "join_articles_into_1",
      "type": "n8n-nodes-base.code",
      "position": [
        600,
        1620
      ],
      "parameters": {
        "jsCode": "// --- n8n Code Node (JavaScript) ---\n// This script takes an array of objects and converts it into a single JSON string.\n\n// 1. Extract the JSON data from all incoming n8n items.\nconst allArticlesArray = items.map(item => item.json);\n\n// 2. Convert the entire array into a single JSON string.\n// The 'null, 2' arguments format the string with indentation for readability.\nconst combinedString = JSON.stringify(allArticlesArray, null, 2);\n\n// 3. Return the result as a single item.\n// The output will have one property, 'fullString', containing the combined text.\nreturn [\n  {\n    json: {\n      fullString: combinedString\n    }\n  }\n];"
      },
      "typeVersion": 2
    },
    {
      "id": "9c05ff30-59c7-45e0-8079-e3a0f01547bd",
      "name": "format_output_as_json",
      "type": "n8n-nodes-base.code",
      "onError": "continueRegularOutput",
      "position": [
        1600,
        1520
      ],
      "parameters": {
        "jsCode": "// Loop over input items and add a new field called 'myNewField' to the JSON of each one\nfor (const item of $input.all()) {\n// The input is an array of items. We'll work with the first item.\n\n\n// Access the string value from the 'output' field in the input JSON.\nconst rawStringWithMarkdown = item.json.output;\nconsole.log(item.json.output)\n// The JSON we want is nested inside a markdown code block (```json ... ```).\n// We need to extract just the JSON part.\n\n// Find the first occurrence of '{' to locate the start of the JSON object.\nconst jsonStartIndex = rawStringWithMarkdown.indexOf('{');\n\n// Find the last occurrence of '}' to locate the end of the JSON object.\nconst jsonEndIndex = rawStringWithMarkdown.lastIndexOf('}');\n\n// Slice the string from the start to the end to get only the clean JSON string.\n// We add +1 to jsonEndIndex because substring's second argument is exclusive.\nconst cleanJsonString = rawStringWithMarkdown.substring(jsonStartIndex, jsonEndIndex + 1);\n\n// Parse the cleaned string into a proper, usable JSON object.\nconst parsedJson = JSON.parse(cleanJsonString);\n\n// Return the parsed JSON object. The keys (symbol, sentiment_score, etc.)\n// will become individual fields in the n8n output for the next node to use.\nreturn parsedJson;}\n\n"
      },
      "executeOnce": false,
      "retryOnFail": false,
      "typeVersion": 2,
      "alwaysOutputData": true
    },
    {
      "id": "c5817f1f-8e79-4c13-9807-fd5176c2773f",
      "name": "if_format_succesful",
      "type": "n8n-nodes-base.if",
      "position": [
        1840,
        1620
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "770d6272-1cde-44d7-9e15-fe5d7c28ba36",
              "operator": {
                "type": "string",
                "operation": "exists",
                "singleValue": true
              },
              "leftValue": "={{ $json.error }}",
              "rightValue": "True"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "0dc93919-fb1a-46df-b316-1044f11cd791",
      "name": "write_sentiment_to_sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2360,
        1740
      ],
      "parameters": {
        "columns": {
          "value": {
            "date": "={{$today}}\n",
            "stock": "={{$('loop_over_tickers').all()[0].json.ticker.replace(\".US\",\"\")}}",
            "rational": "={{ $json.rationale }}",
            "sentimentScore": "={{ $json.sentiment_score }}"
          },
          "schema": [
            {
              "id": "date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "stock",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "stock",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "sentimentScore",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "sentimentScore",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "rational",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "rational",
              "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/1tQDBVDqn5v08GOsjupjV8o3Jzqd4-fKoSoGhWLEOTww/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1tQDBVDqn5v08GOsjupjV8o3Jzqd4-fKoSoGhWLEOTww",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1tQDBVDqn5v08GOsjupjV8o3Jzqd4-fKoSoGhWLEOTww/edit?usp=drivesdk",
          "cachedResultName": "Stock Sentiment"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "Bpltco7Lqc7P73Qp",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "3cbf7b55-50ae-4d1c-8f9e-56941441ef00",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -640,
        1640
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 16
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "ce8d9b54-2026-4faa-9ee1-c261170c8426",
      "name": "付箋",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -620,
        660
      ],
      "parameters": {
        "width": 480,
        "height": 440,
        "content": "# Workflow Overview \n**This workflow automates the process of analyzing the sentiment of stock market news.**\n\n- retrieves a list of stock tickers from a Google Sheet \n- fetches recent news articles for each ticker\n- uses a large language model to perform sentiment analysis on the articles\n- records the sentiment scores and rationale back into a Google Sheet."
      },
      "typeVersion": 1
    },
    {
      "id": "efaee1f9-e57a-4640-a1d6-eb5bc5f341af",
      "name": "付箋1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -680,
        1200
      ],
      "parameters": {
        "color": 4,
        "width": 640,
        "height": 640,
        "content": "# 1. Daily Trigger and Stock Ticker Retrieval\n- **Schedule Trigger:** This workflow is set to run automatically every day at 4:00 PM (Asia/Jerusalem time). This ensures that the script runs just before the markets open and you get a daily update on the sentiment of the stocks you are tracking.\n\n- **Read_tickers_from_Sheet:** This node connects to a Google Sheet named \"Stock Sentiment\" and reads the list of stock tickers from the \"stocks\" sheet. This is the source of the stocks that the workflow will analyze.\n\n- **loop_over_tickers:** This node takes the list of tickers from the Google Sheet and processes them one by one. This allows the workflow to perform the same set of actions for each stock ticker individually."
      },
      "typeVersion": 1
    },
    {
      "id": "eaca666d-2830-4e98-bfe4-44c15d452939",
      "name": "付箋2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        80,
        1020
      ],
      "parameters": {
        "color": 5,
        "width": 680,
        "height": 940,
        "content": "# 2. News Article Retrieval and Validation\n\n- **Get articles from EODHD:** For each ticker, this node makes an HTTP request to the EODHD API to fetch the 10 most recent news articles.\n\n- **If_ticker_not_valid:** This is a conditional node that checks if the EODHD API returned any articles and if the ticker from the google sheets is valid. If no articles are found (i.e., the ticker is likely invalid or has no recent news), the workflow proceeds to the error handling path.\n\n- **Write_in_google_sheets_invalid_ticker:** If the ticker is invalid, this node logs the ticker in the \"Sheet1\" of the \"Stock Sentiment\" Google Sheet with a \"sentimentScore\" of \"Invalid Ticker\". This helps in keeping track of which tickers are not yielding results.\n\n- **join_articles_into_1:** This node takes the multiple news articles for a valid ticker and combines them into a single text string. This is done to prepare the data for the AI model, which will analyze all the articles at once."
      },
      "typeVersion": 1
    },
    {
      "id": "92a8b9e8-cdca-4584-938f-b478b8136867",
      "name": "付箋3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        880,
        1180
      ],
      "parameters": {
        "color": 6,
        "width": 500,
        "height": 800,
        "content": "# 3. Sentiment Analysis with AI\n\n- **AI Agent & Google Gemini Chat Model:** This is the core of the sentiment analysis. The \"AI Agent\" node is configured with a detailed prompt that instructs the \"Google Gemini Chat Model\" to act as a stock sentiment analyzer. The prompt specifies the input format (stock symbol, news title, and content), the analysis guidelines (sentiment score from -1 to 1 and rationale), and the desired JSON output format. The combined text of the news articles and the current stock ticker are passed to the model.l analyze all the articles at once."
      },
      "typeVersion": 1
    },
    {
      "id": "aa4c0dfb-0e02-4f74-b908-27b6b81bbef1",
      "name": "付箋4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1500,
        980
      ],
      "parameters": {
        "color": 2,
        "width": 560,
        "height": 860,
        "content": "# 4. Output Formatting and Error Handling\n\n- **format_output_as_json:** The output from the AI model is a raw string that includes a JSON object. This code node extracts the clean JSON from the string and prepares it for the next steps.\n\n- **if_format_succesful:** This conditional node checks if the previous step of formatting the AI's output into a clean JSON was successful. If there was an error, it sends the workflow back to the \"AI Agent\" to try again."
      },
      "typeVersion": 1
    },
    {
      "id": "cd441252-7867-40e4-9fb9-aa1e6fd53fb9",
      "name": "付箋5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2160,
        1300
      ],
      "parameters": {
        "color": 3,
        "width": 520,
        "height": 600,
        "content": "# 5. Storing the Results\n\n\n- **write_sentiment_to_sheets:** Once a valid sentiment analysis result is obtained and formatted, this node appends the data to \"Sheet1\" of the \"Stock Sentiment\" Google Sheet. It records the current date, the stock ticker, the sentiment score, and the rationale provided by the AI. After this step, the workflow loops back to process the next ticker from the initial list.\n\n\n\n\n\n\n"
      },
      "typeVersion": 1
    }
  ],
  "active": true,
  "pinData": {},
  "settings": {
    "timezone": "Asia/Jerusalem",
    "callerPolicy": "workflowsFromSameOwner",
    "executionOrder": "v1"
  },
  "versionId": "28688d53-2f5a-4653-bd71-bca83d087647",
  "connections": {
    "0d063cec-e739-48b6-9698-0ac586f147ad": {
      "main": [
        [
          {
            "node": "9c05ff30-59c7-45e0-8079-e3a0f01547bd",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3cbf7b55-50ae-4d1c-8f9e-56941441ef00": {
      "main": [
        [
          {
            "node": "6e5b9817-b6ec-4c0a-98fd-b41340ab6230",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "392f7ccd-aba3-4911-859a-556535f45eea": {
      "main": [
        [],
        [
          {
            "node": "0b197810-0002-4f2c-8122-1db9a6a81558",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e68be220-53b9-4b3d-a543-98a916382d42": {
      "main": [
        [
          {
            "node": "bfc2cc77-5fc2-41ae-9e2a-22fed368c852",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "81a16d3a-cdc4-4ad3-8769-b858a999f0ed",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c5817f1f-8e79-4c13-9807-fd5176c2773f": {
      "main": [
        [
          {
            "node": "0d063cec-e739-48b6-9698-0ac586f147ad",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "0dc93919-fb1a-46df-b316-1044f11cd791",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "81a16d3a-cdc4-4ad3-8769-b858a999f0ed": {
      "main": [
        [
          {
            "node": "0d063cec-e739-48b6-9698-0ac586f147ad",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9c05ff30-59c7-45e0-8079-e3a0f01547bd": {
      "main": [
        [
          {
            "node": "c5817f1f-8e79-4c13-9807-fd5176c2773f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "0b197810-0002-4f2c-8122-1db9a6a81558": {
      "main": [
        [
          {
            "node": "e68be220-53b9-4b3d-a543-98a916382d42",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6e5b9817-b6ec-4c0a-98fd-b41340ab6230": {
      "main": [
        [
          {
            "node": "392f7ccd-aba3-4911-859a-556535f45eea",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a9bec4e8-b9df-4868-b6c3-072316fe1ed9": {
      "ai_languageModel": [
        [
          {
            "node": "0d063cec-e739-48b6-9698-0ac586f147ad",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "0dc93919-fb1a-46df-b316-1044f11cd791": {
      "main": [
        [
          {
            "node": "392f7ccd-aba3-4911-859a-556535f45eea",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}