Resolving CSV Output Issues in Your Options Data Script

Comprehensive Guide to Fixing Empty CSV Outputs Due to Strict Filtering

Key Takeaways

Strict Filtering Criteria: High thresholds for open interest and volume, along with limited price deviation, are preventing any options from being written to the CSV.
Debugging Enhancements: Implementing detailed logging can help identify which specific filters are excluding the options.
Adjustable Filters: Relaxing or modifying the filtering parameters can increase the number of options passing through to the CSV export.

Understanding the Issue

Your script is designed to fetch options data for large-cap tickers, apply specific filtering criteria, and export the filtered results to a CSV file. However, despite identifying valid expiration dates, no options meet the criteria to be written to the CSV. The primary reason for this behavior lies in the strictness of the filtering conditions set within your script.

Detailed Analysis of Filtering Criteria

The filtering criteria in your script are defined as follows:

OPEN_INTEREST_THRESHOLD = 10: Only options with an open interest of 10 or more are considered.
VOLUME_THRESHOLD = 1: Only options with a trading volume of at least 1 are considered.
PRICE_WITHIN_PERCENT = 0.50: The strike price must be within 50% of the current stock price.

These conditions are quite restrictive, especially when looking at options with low liquidity or those far from the current stock price. As a result, most options do not satisfy all three conditions simultaneously, leading to an empty filtered_options list.

Impact of Each Filter

Filter	Current Setting	Impact
`OPEN_INTEREST_THRESHOLD`	10	Excludes options with open interest below 10, eliminating those with minimal trading interest.
`VOLUME_THRESHOLD`	1	Only includes options with at least one volume, filtering out inactive options.
`PRICE_WITHIN_PERCENT`	0.50 (50%)	Restricts options to those whose strike price is within 50% of the stock price, narrowing the range significantly.

Step-by-Step Solutions

1. Relax the Filtering Criteria

By adjusting the filtering parameters, you can increase the number of options that pass through to the CSV export. Here’s how you can modify each filter:

a. Lower Open Interest Threshold

Setting OPEN_INTEREST_THRESHOLD to a lower value, such as 0 or 1, allows options with minimal or no open interest to be included.

OPEN_INTEREST_THRESHOLD = 0  # Lowered from 10
VOLUME_THRESHOLD = 1  # Adjust if necessary
PRICE_WITHIN_PERCENT = 0.50  # Adjust as needed

b. Adjust Volume Threshold

Reducing VOLUME_THRESHOLD to 0 includes options with no trading volume, which can be useful for comprehensive data collection.

VOLUME_THRESHOLD = 0  # Lowered from 1

c. Increase Price Within Percent

Expanding PRICE_WITHIN_PERCENT to a higher value, such as 1.0 (100%), broadens the range of acceptable strike prices.

PRICE_WITHIN_PERCENT = 1.0  # Increased from 0.50

2. Implement Detailed Logging

Enhancing your script with more granular logging will help you identify precisely which filters are causing options to be excluded.

a. Log Reasons for Skipping Options

Add print statements before each filtering condition to track which specific condition is not met.

if abs(stock_price - strike_price) / stock_price > PRICE_WITHIN_PERCENT:
    print(f"Skipping option due to price range: Strike={strike_price}, Stock Price={stock_price}")
    continue
if open_interest < OPEN_INTEREST_THRESHOLD:
    print(f"Skipping option due to low open interest: {open_interest} < {OPEN_INTEREST_THRESHOLD}")
    continue
if volume < VOLUME_THRESHOLD:
    print(f"Skipping option due to low volume: {volume} < {VOLUME_THRESHOLD}")
    continue

b. Log Total Options Processed

Tracking the total number of options processed and how many pass each filter can provide insights into data distribution.

total_options = 0
options_passed = 0

for option in chain.get(option_type, []):
    total_options += 1
    # Existing filtering logic
    if <filter_conditions_passed>:
        options_passed += 1
        # Add to filtered_options

print(f"Total options processed: {total_options}")
print(f"Options passed filters: {options_passed}")

3. Validate API Data Integrity

Ensure that the API is returning valid and up-to-date data. Inconsistent or outdated data can lead to unexpected filtering outcomes.

a. Check API Response Structure

Confirm that the API response contains all necessary fields and that the data types are as expected.

def fetch_options_chain(ticker):
    # Existing code
    data = response.json()
    if "data" in data and isinstance(data["data"], list):
        return data
    else:
        print("Invalid data structure received from API.")
        return None

b. Ensure Correct Ticker Symbol

Verify that the ticker symbol used in the API call is correct and that it corresponds to an active stock with available options data.

4. Optimize CSV Export Conditions

If you prefer to have an empty CSV file even when no options meet the criteria, you can modify the export function accordingly.

a. Always Export CSV with Headers

Modify the export_to_csv function to always create a CSV file with headers, regardless of whether filtered_options is empty.

def export_to_csv(filtered_options):
    with open("options_research.csv", mode="w", newline="") as file:
        writer = csv.DictWriter(file, fieldnames=[
            "ticker", "expiration_date", "strike_price", "option_type",
            "bid", "ask", "last_price", "open_interest", "implied_volatility",
            "delta", "underlying_price"
        ])
        writer.writeheader()
        if not filtered_options:
            print("No options data matched the criteria. Created an empty CSV file.")
            return
        print(f"Exporting {len(filtered_options)} options to CSV...")
        for option in sorted(filtered_options, key=lambda x: x["ask"]):
            writer.writerow(option)

Revised Code Example

Incorporating the above recommendations, here's an updated version of your script with relaxed filters and enhanced logging:

import requests
import csv
from datetime import datetime
import time

# Finnhub.io API key
API_KEY = "your_api_key_here"
BASE_URL = "https://finnhub.io/api/v1"

# Large-cap tickers
LARGE_CAP_TICKERS = ["AAPL"]

# Define relaxed filters
OPEN_INTEREST_THRESHOLD = 1  # Lowered from 10
EXPIRY_MIN_DAYS = 15
EXPIRY_MAX_DAYS = 95
PRICE_WITHIN_PERCENT = 1.0  # Increased from 0.50
VOLUME_THRESHOLD = 0  # Lowered from 1
RATE_LIMIT_DELAY = 0.5  # Delay between API calls

def get_stock_price(ticker):
    """Get current stock price from Finnhub."""
    url = f"{BASE_URL}/quote"
    params = {"symbol": ticker, "token": API_KEY}

    try:
        response = requests.get(url, params=params)
        response.raise_for_status()
        data = response.json()
        stock_price = data.get("c")  # Current price
        if stock_price:
            print(f"Stock price for {ticker}: {stock_price}")
        else:
            print(f"No stock price data available for {ticker}.")
        return stock_price
    except Exception as e:
        print(f"Error fetching price for {ticker}: {e}")
        return None

def fetch_options_chain(ticker):
    """Fetch options chain data from Finnhub."""
    url = f"{BASE_URL}/stock/option-chain"
    params = {"symbol": ticker, "token": API_KEY}

    try:
        response = requests.get(url, params=params)
        response.raise_for_status()
        data = response.json()
        if "data" in data and isinstance(data["data"], list):
            print(f"Fetched options data for {ticker}.")
            return data
        else:
            print("Invalid options data structure received.")
            return None
    except Exception as e:
        print(f"Error fetching options chain for {ticker}: {e}")
        return None

def parse_expiry_date(date_str):
    """Parse expiry date string to datetime object."""
    try:
        return datetime.strptime(date_str, "%Y-%m-%d")
    except ValueError:
        print(f"Unrecognized date format: {date_str}")
        return None

def fetch_and_filter_options(tickers):
    filtered_options = []
    today = datetime.now()

    for ticker in tickers:
        print(f"\nFetching options for {ticker}...")

        # Get current stock price
        stock_price = get_stock_price(ticker)
        if not stock_price:
            print(f"Could not get price for {ticker}, skipping...")
            continue

        # Get options chain
        options_data = fetch_options_chain(ticker)
        if not options_data:
            print(f"No options data available for {ticker}")
            continue

        # Process each expiration date
        for chain in options_data.get("data", []):
            expiry_date = parse_expiry_date(chain.get("expirationDate"))
            if not expiry_date:
                continue

            days_to_expiry = (expiry_date - today).days
            print(f"Checking expiration date: {expiry_date.strftime('%Y-%m-%d')} (Days to expiry: {days_to_expiry})")

            # Check if expiration is within range
            if days_to_expiry < EXPIRY_MIN_DAYS or days_to_expiry > EXPIRY_MAX_DAYS:
                print(f"Skipping options with expiration date {expiry_date.strftime('%Y-%m-%d')} (Outside range).")
                continue

            print(f"Valid expiration date: {expiry_date.strftime('%Y-%m-%d')} (Days to expiry: {days_to_expiry})")

            # Process calls and puts
            for option_type in ["calls", "puts"]:
                for option in chain.get(option_type, []):
                    try:
                        strike_price = float(option.get("strike", 0))
                        open_interest = option.get("openInterest", 0)
                        volume = option.get("volume", 0)
                        bid = option.get("bid", 0)
                        ask = option.get("ask", 0)

                        # Log details for every option
                        print(f"Option details: {option_type[:-1].capitalize()} | "
                              f"Strike={strike_price}, Open Interest={open_interest}, Volume={volume}, "
                              f"Bid={bid}, Ask={ask}")

                        # Filter options based on criteria
                        if abs(stock_price - strike_price) / stock_price > PRICE_WITHIN_PERCENT:
                            print(f"Skipping option due to price range: Strike={strike_price}, Stock Price={stock_price}")
                            continue
                        if open_interest < OPEN_INTEREST_THRESHOLD:
                            print(f"Skipping option due to low open interest: {open_interest} < {OPEN_INTEREST_THRESHOLD}")
                            continue
                        if volume < VOLUME_THRESHOLD:
                            print(f"Skipping option due to low volume: {volume} < {VOLUME_THRESHOLD}")
                            continue

                        # Add matching options
                        filtered_options.append({
                            "ticker": ticker,
                            "expiration_date": expiry_date.strftime("%Y-%m-%d"),
                            "strike_price": strike_price,
                            "option_type": option_type[:-1],  # 'calls' -> 'call'
                            "bid": bid,
                            "ask": ask,
                            "last_price": option.get("lastPrice", 0),
                            "open_interest": open_interest,
                            "implied_volatility": option.get("impliedVolatility", 0),
                            "delta": option.get("delta", 0),
                            "underlying_price": stock_price,
                        })

                    except Exception as e:
                        print(f"Error processing option: {e}")
                        continue

        time.sleep(RATE_LIMIT_DELAY)

    return filtered_options

def export_to_csv(filtered_options):
    with open("options_research.csv", mode="w", newline="") as file:
        writer = csv.DictWriter(file, fieldnames=[
            "ticker", "expiration_date", "strike_price", "option_type",
            "bid", "ask", "last_price", "open_interest", "implied_volatility",
            "delta", "underlying_price"
        ])
        writer.writeheader()
        if not filtered_options:
            print("No options data matched the criteria. Created an empty CSV file.")
            return
        print(f"Exporting {len(filtered_options)} options to CSV...")
        for option in sorted(filtered_options, key=lambda x: x["ask"]):
            writer.writerow(option)

def main():
    print("Fetching and filtering options...")
    filtered_options = fetch_and_filter_options(LARGE_CAP_TICKERS)
    print(f"\nFound {len(filtered_options)} options matching criteria.")
    export_to_csv(filtered_options)

if __name__ == "__main__":
    main()

Additional Recommendations

Beyond adjusting the filters and enhancing logging, consider the following best practices to further improve your script:

1. Utilize Environment Variables for API Keys

Storing API keys directly in your script poses a security risk. Instead, use environment variables to manage sensitive information.

import os

API_KEY = os.getenv("FINNHUB_API_KEY")

Ensure that you set the FINNHUB_API_KEY environment variable in your system or use a package like python-dotenv to manage environment variables from a .env file.

2. Implement Error Handling for API Rate Limits

API providers often impose rate limits. Implement exponential backoff strategies or monitor the X-RateLimit-Remaining headers to prevent throttling.

3. Optimize Data Storage and Retrieval

For larger datasets, consider using databases instead of CSV files for more efficient data storage and querying capabilities.

Conclusion

The absence of data in your CSV file is primarily due to stringent filtering criteria that exclude all fetched options. By relaxing these filters, enhancing your logging mechanisms, and ensuring the integrity of the API data, you can successfully export relevant options data. Additionally, adopting best practices such as secure API key management and handling API rate limits will make your script more robust and secure.