Building an Automated Stock Option Expiration Date Tracking System with n8n and Google Sheets: Real-time Data Integration, Notification Setup, and Portfolio Management Automation

Stock option investing is complex, and missing expiration dates can lead to significant losses. This article introduces how to build a system that automatically tracks expiration dates, sets up necessary notifications, and automates portfolio management by integrating n8n and Google Sheets. This system will help you save time and effort, and make more accurate investment decisions.

1. The Challenge / Context

Managing stock option expiration dates is a crucial task for investors. Tracking numerous option contracts and remembering each contract's expiration date is not easy. Especially when using multiple brokerage accounts, information is scattered, making management even more difficult. Missing an expiration date can turn an option into a worthless piece of paper, leading to massive losses. To solve these problems, an automated system is needed. Existing spreadsheet-based management relies on manual updates and has a high potential for errors. Furthermore, real-time data integration is difficult, making it hard to reflect the latest information. Therefore, an automated system must integrate real-time data integration, notification features, and portfolio management capabilities.

2. Deep Dive: n8n and Google Sheets

n8n is a node-based workflow automation platform. Even without coding experience, you can build automated workflows by connecting various applications and services using a drag-and-drop method. n8n supports diverse data sources such as Google Sheets, APIs, and databases, and can react to real-time events via webhooks. Additionally, you can implement complex logic using custom functions.

Google Sheets is a cloud-based spreadsheet program. It offers a simple interface and powerful collaboration features, and can be integrated with external systems via API. Google Sheets is used to store and manage data, and serves as both a data source and target for n8n workflows.

3. Step-by-Step Guide / Implementation

The following is a step-by-step guide to building an automated stock option expiration date tracking system using n8n and Google Sheets.

Step 1: Google Sheets Setup

First, create a spreadsheet in Google Sheets to store option contract information. The spreadsheet needs the following columns:

  • Ticker
  • Expiration Date
  • Strike Price
  • Call/Put
  • Quantity
  • Current Price
  • Status - e.g.: "Active", "Expired"

Record the Spreadsheet ID. This will be used by n8n to access the spreadsheet.

Step 2: Create n8n Workflow

Create a new workflow in n8n. The workflow consists of the following nodes:

  1. Cron Node (Trigger): Periodically executes the workflow. For example, set it to run every morning at 9 AM.
  2. Google Sheets Node (Read): Reads option contract information from Google Sheets. Specify the Spreadsheet ID and sheet name.
  3. Function Node: Checks the expiration date of each option contract and filters contracts with an imminent expiration date.
  4. IF Node: Checks if there are any contracts with an imminent expiration date.
  5. Google Sheets Node (Update): Updates the status of expired options to "Expired".
  6. Email Node (Send): Sends a notification email for options with an imminent expiration date.

Step 3: Cron Node Setup

Double-click the Cron node to open its settings. Specify the desired execution frequency in the "Expression" field. For example, to run every day at 9 AM, enter "0 9 * * *"

// Run every day at 9 AM
0 9 * * *

Step 4: Google Sheets Node (Read) Setup

Double-click the Google Sheets node to open its settings. Set "Operation" to "Get All" and enter the Spreadsheet ID in the "Spreadsheet ID" field. Enter the sheet name in the "Sheet Name" field. You need to set up authentication information (Google Sheets API credentials).

Step 5: Function Node Setup (Expiration Date Check and Filtering)

Double-click the Function node to open its settings. Enter the following JavaScript code to check expiration dates and filter contracts with an imminent expiration date.

const today = new Date();
const imminentExpiration = [];

for (let i = 0; i < $input.all().length; i++) {
  const item = $input.all()[i].json;
  const expirationDate = new Date(item["Expiration Date"]); // Use Google Sheets column name
  const daysUntilExpiration = (expirationDate - today) / (1000 * 60 *