Text Analytics Toolbox

Text mining, TF-IDF, sentiment analysis, and topic modeling workbench for Python for Excel

Extract n-grams, calculate TF-IDF features, perform lexicon-based sentiment analysis with negation handling, discover latent topics with LDA, and compute cosine similarity directly in Excel.

Result preview

The Text Analytics Toolbox is an interactive natural language processing and text-mining workbench for Excel. It analyzes customer reviews, support tickets, research abstracts, and knowledge base documentation without leaving the spreadsheet.

The default Product Reviews — Sentiment model evaluates customer product feedback using a dictionary lexicon with contextual negation handling. On the baseline dataset of 12 customer reviews, the engine ingests 113 total tokens across 80 distinct vocabulary terms, identifying 5 positive, 5 negative, and 2 neutral reviews (mean length 9.42 tokens/doc, matrix sparsity 90.62%), with packaging emerging as the top TF-IDF keyword (weight 5.93%).

Switching Start → Active task recalculates alternative NLP engines:

• Support Tickets — Topic Model (support_tickets_topics): Fits Latent Dirichlet Allocation (LDA) across 8 technical support tickets to discover 3 latent clusters (billing inquiries, application crashes, and account 2FA timeouts) with 92.5% average topic confidence.
• Document Corpus — TF-IDF (keyword_tfidf_extraction): Extracts unigram and bigram features across 8 scientific research abstracts (276 vocabulary terms) using sublinear TF scaling, highlighting salient domain keywords like quantum (mean top TF-IDF 0.2142).
• Content Library — Search (document_similarity_search): Scores 8 IT infrastructure articles against the multi-term search query "multi-factor authentication SSO login security", identifying Doc 1 (Authentication) as a high-relevance match (cosine similarity 0.5774).

For a first run:

1. Leave Start → Active task as customer_reviews_sentiment.
2. Inspect the customer feedback on Corpus.
3. View Results for document polarity scores, sentiment categories, and dominant topic assignments.
4. Review Vocabulary & Topics for top discriminative keywords and sentiment lexicon matches.
5. Check Diagnostics to verify corpus ingestion, lexical diversity, and matrix sparsity.

For a guided tour, see Getting started.

What this toolbox covers

Modules

1. Lexicon Sentiment Analysis — Rule-based and dictionary sentiment polarity scoring with lookback negation handling, classifying texts into Positive, Neutral, and Negative categories.
2. TF-IDF Keyword Extraction — Sublinear term frequency-inverse document frequency weighting (\(1 + \ln(\text{tf})\)) and unigram/bigram n-gram extraction for discriminative terminology discovery.
3. Latent Dirichlet Allocation (LDA) — Unsupervised probabilistic Bayesian topic modeling decomposing discrete bag-of-words count matrices into semantic topic distributions.
4. Corpus Tokenization & Preprocessing — Configurable regex tokenization, case folding, minimum token length thresholds, and combined standard plus custom stopword filtering.
5. Document Similarity & Vector Search — Normalized cosine similarity computation between arbitrary search queries and document TF-IDF vectors with automated relevance tiering.
6. 7-Check Diagnostics Suite — Automated corpus validation evaluating ingestion thresholds, vocabulary filtering, matrix sparsity, type-token ratio (TTR), sentiment/query coverage, topic distribution balance, and preprocessing rule integrity.

Documentation

• Getting started — First run walkthroughs for sentiment scoring, LDA topic modeling, TF-IDF keyword extraction, and vector similarity search.
• Modeling guide — Corpus table schemas, rule configurations (Stopword, MinLength, PositiveWords, NegativeWords, NgramMax, SearchQuery), sizing limits, and custom corpora authoring.
• Methods guide — Mathematical formulations of negation-aware sentiment scoring, sublinear TF-IDF, LDA discrete count modeling, cosine similarity metrics, diagnostics suite definitions, and MATLAB mapping.
• Worked examples — Detailed walkthroughs covering product customer feedback, IT support tickets, scientific research abstracts, and knowledge base search.
• Reference — Complete catalog of worksheet controls, model registry, rule syntax, output schemas, diagnostic pass/warn criteria, and troubleshooting.

Why Python

Spreadsheets are the primary collection point for customer surveys, bug reports, and operational logs. However, native Excel formulas cannot tokenize text, stem morphology, calculate inverse document frequencies across thousands of rows, or run variational Bayes matrix factorizations. Python brings scikit-learn’s optimized machine learning pipelines directly into Excel, calculating sparse matrices and probabilistic topic distributions in sub-second runtimes while leaving the data accessible in familiar tabular formats.

Operating workflow

Author: A data scientist, qualitative researcher, or analytics engineer configures domain-specific stopword lists, defines affective sentiment dictionaries, tunes topic counts (\(K\)), and establishes search queries on Stopwords & Rules.

Workbook user: A product manager, support team lead, or business analyst pastes raw text entries into Corpus, selects the active model on Start, and reviews document-level ratings, dominant topics, and keyword distributions on Results.

Download the Excel template

• Text Analytics Toolbox workbook
• Getting started
• Complete reference

Capability reference

Capability map

Capability	Status	Current implementation
TF-IDF feature extraction	Available	Scikit-learn TfidfVectorizer with sublinear TF scaling (\(1 + \ln(\text{tf})\)) and smooth IDF
N-gram extraction	Available	Configurable unigram and bigram extraction via NgramMax rule
Lexicon sentiment analysis	Available	Normalized polarity scoring with sentence boundary and lookback negation inversion
Latent Dirichlet Allocation	Available	Scikit-learn LatentDirichletAllocation using discrete count matrices from CountVectorizer
Cosine similarity search	Available	Vector space query matching with high, moderate, and low relevance tiering
Stopword filtering	Available	Scikit-learn 318-term English stopword list merged with custom sheet-defined stopwords
Token length filtering	Available	Regex word-boundary length filtering parameterized via MinLength rules
Case normalization	Available	User-controlled Lowercase or Preserve case folding via Start controls
Automated Diagnostics Suite	Available	7 automated checks covering ingestion, vocabulary, sparsity, TTR, coverage, balance, and rules
Part-of-speech (POS) tagging	Planned	Rule-based noun/adjective filtering via spaCy or NLTK
Supervised text classification	Planned	Logistic regression and Naive Bayes document categorization
Dense neural embeddings	Exploring	Sentence-BERT semantic embeddings via WebAssembly/Pyodide

Inputs and assumptions

Workbook map

Sheet	Purpose	Primary user
Start	Task selector, topic count (\(K\)), top terms count, min doc freq, case fold control, and catalog	Author and workbook user
Corpus	Document table (Model, DocID, Category, Text) storing text records	Workbook user
Stopwords & Rules	Preprocessing rules (Stopword, MinLength, PositiveWords, NegativeWords, NgramMax, SearchQuery)	Author
Results	Summary KPI metrics (A5:B17) and document-level scoring output (D5:I45)	Workbook user and reviewer
Vocabulary & Topics	Top TF-IDF keywords, topic term mixtures, and query vector weights (A5:F40)	Workbook user
Diagnostics	7-check automated corpus validation suite (A5:C25)	Reviewer

How the calculation works

1. Workbook Ingestion: bf.inputs reads runtime controls from Start!A5:B10, the model catalog from Start!D5:H20, documents from Corpus!A5:D200, and preprocessing directives from Stopwords & Rules!A5:D50.
2. Corpus & Rule Filtering: Identifies rows where Model matches the active task ID. Rules configured with Model = "ALL" are merged with task-specific rules.
3. Preprocessing & Tokenization:
   • Stopwords are compiled by unioning scikit-learn’s 318 built-in English stopwords with custom tokens.
   • Word boundaries are parsed using the parameterized regular expression (?u)\b\w{min_length,}\b.
   • Text is case-folded according to the Case fold control (Lowercase vs Preserve).
4. Task Routing & Algorithmic Execution:
   • Sentiment Analysis: Tokenizes sentences, applies lookback negation inversion (not, never, no, without, n't) within punctuation clauses, matches against positive/negative affective dictionaries, and projects document scores \(s \in [-1.0, 1.0]\).
   • LDA Topic Discovery: Fits CountVectorizer on discrete term occurrences, passes counts to LatentDirichletAllocation(n_components=K), computes document topic mixtures, and identifies dominant topics and top keywords.
   • TF-IDF Keyword Extraction: Fits TfidfVectorizer(sublinear_tf=True, ngram_range=(1, NgramMax)), calculates mean corpus TF-IDF weights, and extracts top salient n-grams for each document.
   • Cosine Similarity Search: Projects the user search query into the document TF-IDF vector space via vec.transform([query]), calculates pairwise cosine similarities \(\cos(\mathbf{q}, \mathbf{d})\), and assigns relevance tiers.
5. Diagnostics Suite Execution: Evaluates the corpus against 7 diagnostic criteria, assigning PASS or WARN status with explanatory narrative details.
6. Publication: bf.publish delivers structured DataFrames to Excel output blocks on Results, Vocabulary & Topics, and Diagnostics.

Notebook implementation

inputs = bf.inputs(
    controls=bf.ref("Start!A5:B10", headers=True),
    models=bf.ref("Start!D5:H20", headers=True),
    corpus=bf.ref("Corpus!A5:D200", headers=True),
    rules=bf.ref("Stopwords & Rules!A5:D50", headers=True),
)

The marimo notebook executes within a reactive dependency DAG. When a user changes the active task dropdown in Start!B6, the inputs cell re-evaluates, triggering the NLP processing cell:

# Publish outputs back into Excel spill ranges
publication = bf.publish(
    outputs={
        "summary": df_summary,
        "documents": df_documents,
        "vocabulary": df_vocab,
        "diagnostics": df_diag,
    }
)

In interactive App mode, the notebook also renders a matplotlib horizontal bar chart illustrating document sentiment scores, topic prevalence percentages, top TF-IDF weights, or query similarity rankings.

[!NOTE] In Boardflare App mode, the notebook recalculates automatically upon workbook edits. Changes made to controls or text entries propagate reactively through the marimo cell graph.

Validation and expected results

The toolbox outputs have been verified against baseline runs and dedicated unit test scenarios:

Task ID	Task Label	Algorithm	Docs	Tokens	Vocab	Key Metric	Expected Evidence
customer_reviews_sentiment	Product Reviews — Sentiment	Lexicon	12	113	80	Polarity	OPTIMAL; 5 Pos / 5 Neg / 2 Neutral; Top term packaging (0.0593); Sparsity 90.62%
support_tickets_topics	Support Tickets — Topic Model	LDA	8	80	62	Confidence	OPTIMAL; 3 topics; Dominant Topic 3; Avg confidence 92.5%; Doc 1 Topic 1 (0.9244)
keyword_tfidf_extraction	Document Corpus — TF-IDF	Tfidf	8	161	276	Mean TF-IDF	OPTIMAL; Max n-gram 2; Top term quantum (0.0600); Mean top TF-IDF 0.2142
document_similarity_search	Content Library — Search	Cosine	8	120	99	Top Match	OPTIMAL; Top match Doc 1 (0.5774); 1 high match (≥0.4); 5 matched query terms

Limitations

• Corpus Size: Designed for decision-scale text collections up to 1,000 documents in Excel. For corpora exceeding 10,000 documents, batch offline processing is recommended.
• Document Length: Optimized for short to medium-length documents (reviews, tickets, abstracts, articles up to 500 words). Very long documents (> 5,000 words) should be chunked into paragraphs.
• Lexicon Coverage: The rule-based sentiment engine relies on predefined domain lexicons. Nuanced figurative language, sarcasm, or highly specialized jargon requires adding terms to Stopwords & Rules.
• LDA Stochasticity: Latent Dirichlet Allocation uses a fixed random seed (random_state=42) to guarantee reproducible topic assignments across recalculations.

When to use this approach

Use this toolbox when text observations (customer feedback, voice-of-customer surveys, IT incident logs, or internal knowledge documents) reside in Excel and stakeholders need immediate, interpretable quantitative text analytics without setting up external Python scripts, cloud NLP APIs, or command-line pipelines.

Related templates

• Curve Fitting Toolbox — Nonlinear parametric regression workbench.
• Optimization Toolbox — Linear, integer, and nonlinear mathematical programming.
• Monte Carlo Simulation — Probabilistic risk analysis and parameter sampling.

How this compares with MATLAB and Excel

Dimension	Native Excel	MATLAB Text Analytics Toolbox	Boardflare Text Analytics Toolbox
Tokenization	Manual MID / SEARCH formulas	tokenizedDocument object	Configurable regex tokenization with length thresholds
TF-IDF Weighting	Impractical complex array formulas	tfidf function with sparse matrix output	Scikit-learn TfidfVectorizer with sublinear scaling
Topic Modeling	Not supported	fitlda with variational inference	Scikit-learn LatentDirichletAllocation on discrete count matrices
Sentiment Analysis	Not supported natively	vaderSentimentScores	Rule-based lexicon scoring with negation lookback window
Vector Search	Exact text matching only (VLOOKUP)	docsearch cosine similarity index	Pairwise cosine similarity with match tier categorization
Diagnostics	Manual calculation	Interactive diagnostic plots	7-check automated suite with PASS/WARN ratings
User Experience	Manual spreadsheet formulas	Standalone desktop application	Fully integrated Python running inside Excel worksheets

Changelog

2026-10-18 (Issue #100)

• Initial release of the Text Analytics Toolbox for Python for Excel.
• Implemented 4 core text mining engines: Lexicon Sentiment Analysis, Latent Dirichlet Allocation (LDA), TF-IDF Salient Keyword Extraction, and Cosine Similarity Vector Search.
• Added contextual negation inversion logic (not, never, no, without, n't) to the rule-based sentiment classifier.
• Built sublinear term frequency scaling (\(1 + \ln(\text{tf})\)) and configurable unigram/bigram n-gram extraction.
• Developed discrete count matrix pipeline for LDA topic discovery to ensure rigorous multinomial Dirichlet modeling.
• Introduced the 7-check Diagnostics Suite evaluating corpus ingestion, vocabulary thresholds, matrix sparsity, lexical diversity (TTR), affective/query coverage, topic balance, and rule configuration.
• Added interactive horizontal bar chart visualizations in marimo App mode for sentiment distributions, topic weights, keyword importance, and query similarity.
• Published full documentation bundle across Getting Started, Modeling, Methods, Examples, and Reference sub-guides.