AI-Powered Spreadsheet Macro Security Analysis with Claude SDK

Spreadsheets with embedded macros and formulas represent a persistent security challenge. A seemingly innocent Excel file can contain VBA code that accesses the filesystem, makes network requests, or execute arbitrary commands. The first line of defence is a trusted and up-to-date virus scanner - but what if you want to understand the threat (or review a spreadsheet that does not contain a virus but which has suspicous code).

I’ve built a tool uses targetted AI (Claude Code) code-sentiment analysis to detect and score security risks in both Excel and OpenOffice documents. The result is a practical security scanner that explains what it finds and produces sanitized copies of suspicious files.

The Problem: Hidden Code in Spreadsheets

Spreadsheets can contain several types of potentially dangerous code:

VBA Macros

Macro-enabled Excel files (.xlsm) can include Visual Basic for Applications code that runs when the file is opened or when certain actions are triggered. This code can do almost anything - read files, download payloads, execute system commands.

Suspicious Formulas

Even without macros, Excel formulas can be weaponized. Functions like HYPERLINK, WEBSERVICE, FILTERXML, and INDIRECT can fetch external data, parse XML, or create dynamic references that bypass normal security boundaries.

The Challenge

Manual inspection doesn’t scale, and signature/virus-based detection might miss novel attacks or legitimate code that happens to match patterns. This application provides a different perspective when considering an untrusted spreasheet.

The Solution: Traditional Parsing + AI Analysis

The macro-checker tool takes a hybrid approach:

Traditional parsing to extract VBA macros and identify suspicious formulas
AI-powered analysis using Claude to score each finding from 1-10 based on security risk
Detailed reporting with explanations of each finding
Automated sanitization that removes or highlights risky code

Architecture

Spreadsheet File (.xlsx, .xlsm, .ods)
    ↓
[Python Parsing Layer]
├── openpyxl: Excel file reading
├── oletools: VBA macro extraction
└── odfpy: OpenOffice support
    ↓
[AI Analysis Layer]
├── claude-agent-sdk: Code analysis
└── Security scoring (1-10)
    ↓
[Output Generation]
├── Markdown report
└── Sanitized spreadsheet copy

Implementation Details

The tool is built in Python and uses async/await patterns to efficiently handle AI queries.

Code Detection

For Excel files, the tool uses openpyxl to iterate through all cells looking for formulas:

for sheet_name in workbook.sheetnames:
    sheet = workbook[sheet_name]
    for row in sheet.iter_rows():
        for cell in row:
            if cell.value and isinstance(cell.value, str) and cell.value.startswith('='):
                # Found a formula - send to AI for analysis

For VBA macros, it leverages the oletools library to extract macro code from .xlsm files:

vba_parser = VBA_Parser(str(input_file))
if vba_parser.detect_vba_macros():
    for (filename, stream_path, vba_filename, vba_code) in vba_parser.extract_macros():
        # Process each macro module

AI-Powered Scoring

Each piece of code is sent to Claude with a structured prompt asking for two things:

A security score from 1-10 where:
- 1-3: Definitely malicious (file access, network calls, obfuscation)
- 4-6: Suspicious (external references, dynamic execution)
- 7-9: Potentially risky but may be legitimate
- 10: Safe (simple calculations)
A brief explanation of the score

async def analyze_code_with_claude(self, code: str, location: str) -> Tuple[int, str]:
    prompt = f"""Analyze the following code/macro found in a spreadsheet for security risks.

Location: {location}

Code:
```
{code}
```

Provide:
1. A security score from 1-10
2. A brief analysis explaining the score (2-3 sentences)

Format your response EXACTLY as:
SCORE: <number>
ANALYSIS: <your analysis here>
"""

    options = ClaudeAgentOptions(
        max_turns=1,
        system_prompt="You are a security analyst specializing in spreadsheet macro analysis."
    )

    async for message in query(prompt=prompt, options=options):
        # Parse the response and extract score and analysis

The structured prompt format ensures consistent, machine-parseable responses while still getting natural language explanations.

Output Generation

The tool generates two outputs:

Markdown Report: A detailed analysis with summary statistics and findings sorted by risk level. Each finding includes the location, score, analysis, and code snippet.

Sanitized Copy: A modified version of the original spreadsheet where cells scoring below a threshold (default: 5) are replaced with “CODE REMOVED: Item #N” and highlighted in bright yellow. This reduces the risk of opening the spreadsheets (though caution is still required for any untrusted source).

Using the Tool

Basic usage is straightforward:

spreadsheet-safety-check suspicious_file.xlsm

This scans the file, analyzes all findings, and produces two output files:

suspicious_file_report_20251113_143022.md
suspicious_file_sanitized_20251113_143022.xlsm

You can adjust the removal threshold to be more or less aggressive:

# Conservative: only remove definitely malicious (< 3)
spreadsheet-safety-check file.xlsx --remove-threshold 3

# Aggressive: remove anything potentially risky (< 8)
spreadsheet-safety-check file.xlsx --remove-threshold 8

Real-World Results

Testing the tool with various spreadsheets reveals interesting patterns:

Simple Formulas: Score 10

=SUM(A1:A10)
=IF(B5>100, "High", "Low")

Claude correctly identifies these as safe calculations.

Suspicious But Potentially Legitimate: Score 4-6

=HYPERLINK("https://example.com", "Click here")
=INDIRECT("Sheet"&A1&"!B2")

These could be legitimate (documentation links, dynamic references) or malicious (phishing links, obfuscated references). The tool flags them for review but doesn’t automatically remove them with default settings.

Definitely Malicious: Score 1-3

Sub AutoOpen()
    Shell "powershell.exe -ExecutionPolicy Bypass -File C:\temp\payload.ps1"
End Sub

VBA macros that execute shell commands, access the filesystem outside normal paths, or download content from the internet consistently score in the malicious range.

The Claude Agent SDK

This project uses the claude-agent-sdk which provides a Python interface to Claude AI. The SDK makes it straightforward to integrate AI analysis into traditional Python applications.

Key features that made this project practical:

Async support: Process multiple findings concurrently
Structured responses: Easy parsing of AI outputs
Session management: Automatic handling of API credentials

The query() function provides a simple interface for sending prompts and receiving responses:

from claude_agent_sdk import query, ClaudeAgentOptions

async for message in query(prompt=my_prompt, options=options):
    if isinstance(message, AssistantMessage):
        for block in message.content:
            if isinstance(block, TextBlock):
                # Process the response text

Limitations and Future Work

Current Limitations

The tool has some known limitations:

AI costs: Claude Code has a quota (blocking access for some time when the quota is reached)
False positives: Legitimate but complex code may be flagged as suspicious
Performance: Large spreadsheets with many formulas can take time to process

Future Enhancements

Several improvements would make the tool more robust:

Batch processing: Scan entire directories of spreadsheets
Custom patterns: Allow users to define specific patterns to flag
Caching: Remember scores for identical code snippets
HTML reports: More interactive output format
Older formats: Support for legacy .xls files
Integration: API for incorporating into automated workflows

Closing Thoughts

This project demonstrates a practical pattern for augmenting traditional code analysis with AI capabilities. The combination of deterministic parsing (which finds all the code) with probabilistic analysis (which understands what it does) allows for targeted AI usage where it’s needed (and faster, local efficient code where it’s not).

The approach isn’t limited to spreadsheet security. Any domain that involves analyzing code or content for intent could benefit from this hybrid pattern: traditional tools to extract and structure the data, AI to understand and explain it.

Source Code: https://github.com/bjdean/spreadsheet-safety-check

Dependencies: claude-agent-sdk, openpyxl, oletools

The Problem: Hidden Code in Spreadsheets#

VBA Macros#

Suspicious Formulas#

The Challenge#

The Solution: Traditional Parsing + AI Analysis#

Architecture#

Implementation Details#

Code Detection#

AI-Powered Scoring#

Output Generation#

Using the Tool#

Real-World Results#

Simple Formulas: Score 10#

Suspicious But Potentially Legitimate: Score 4-6#

Definitely Malicious: Score 1-3#

The Claude Agent SDK#

Limitations and Future Work#

Current Limitations#

Future Enhancements#

Closing Thoughts#