SENTIMENT
The SENTIMENT function provides a unified interface for several sentiment analysis models, ranging from fast lexicon-based analyzers to state-of-the-art transformer models.
The function returns an Excel Data Type containing scores and classifications.
Available Analyzers:
Lexicon & ML Engines:
- Pattern (Default): Fast, lexicon-based. Returns Polarity (-1 to 1) and Subjectivity (0 to 1).
- Naive Bayes: A machine learning model trained on movie reviews. Returns a Classification (Positive/Negative) and probabilities.
- VADER: Specifically tuned for social media, slang, and emojis. Returns a Compound score (-1 to 1).
Transformer (BERT) Engines:
These models run entirely in the browser using transformers.js: 4. Movie Reviews (DistilBERT): Fast and accurate for general English sentiment. 5. Product Reviews (Multilingual BERT): Optimized for reviews across multiple languages. 6. Finance News (FinBERT): Specifically trained for financial sentiment. 7. Twitter Messages (RoBERTa): Tuned for social media, slang, and emojis.
Usage:
- The primary value shown in the cell depends on the analyzer (e.g., Polarity for Pattern, Label for BERT).
- Access detailed scores using the
.operator (e.g.,=A1.Score,=A1.Subjectivity).
Excel Usage
=SENTIMENT(text, sentiment_model)text(list[list], required): Single text cell or 2D text range to analyze.sentiment_model(str, optional, default: “pattern”): The sentiment analysis engine to use.
Returns (list[list]): 2D array of sentiment data types.
Example 1: Pattern (Default) - Opinionated Text
Inputs:
| text | sentiment_model |
|---|---|
| I absolutely love the new design! | pattern |
Excel formula:
=SENTIMENT("I absolutely love the new design!", "pattern")Expected output:
{"type":"Double","basicValue":0.335227,"properties":{"Polarity":{"type":"Double","basicValue":0.335227},"Subjectivity":{"type":"Double","basicValue":0.527273}}}
Example 2: VADER - Social Media Style
Inputs:
| text | sentiment_model |
|---|---|
| The service was GREAT!!! :) | vader |
Excel formula:
=SENTIMENT("The service was GREAT!!! :)", "vader")Expected output:
{"type":"Double","basicValue":0.8661,"properties":{"Compound":{"type":"Double","basicValue":0.8661},"Positive":{"type":"Double","basicValue":0.744},"Neutral":{"type":"Double","basicValue":0.256},"Negative":{"type":"Double","basicValue":0}}}
Example 3: Movie Review (BERT)
Inputs:
| text | sentiment_model |
|---|---|
| This film is a masterpiece of modern cinema. | bert_movie |
Excel formula:
=SENTIMENT("This film is a masterpiece of modern cinema.", "bert_movie")Expected output:
{"type":"String","basicValue":"POSITIVE","properties":{"Label":{"type":"String","basicValue":"POSITIVE"},"Score":{"type":"Double","basicValue":0.999829}}}
Example 4: Financial News (BERT)
Inputs:
| text | sentiment_model |
|---|---|
| Company shares plummeted after the disappointing earnings report. | bert_finance |
Excel formula:
=SENTIMENT("Company shares plummeted after the disappointing earnings report.", "bert_finance")Expected output:
{"type":"String","basicValue":"negative","properties":{"Label":{"type":"String","basicValue":"negative"},"Score":{"type":"Double","basicValue":0.906089}}}
Python Code
Show Code
import nltk
from textblob import TextBlob
from textblob.sentiments import PatternAnalyzer, NaiveBayesAnalyzer
from vaderSentiment.vaderSentiment import SentimentIntensityAnalyzer as VaderSIA
from transformers_js_py import import_transformers_js
async def sentiment(text, sentiment_model='pattern'):
"""
Multi-engine sentiment analysis (Lexicon, ML, or Transformer-based).
See: https://textblob.readthedocs.io/en/dev/
This example function is provided as-is without any representation of accuracy.
Args:
text (list[list]): Single text cell or 2D text range to analyze.
sentiment_model (str, optional): The sentiment analysis engine to use. Valid options: Pattern, Naive Bayes, VADER, Movie Reviews (BERT), Product Reviews (BERT), Finance News (BERT), Twitter Messages (BERT). Default is 'pattern'.
Returns:
list[list]: 2D array of sentiment data types.
"""
try:
def to2d(value):
return [[value]] if not isinstance(value, list) else value
def validate_grid(value):
if not isinstance(value, list) or not value or not all(isinstance(row, list) for row in value):
return None, "Error: text must be a non-empty 2D list"
if len({len(row) for row in value}) != 1:
return None, "Error: text must be a rectangular 2D list"
return value, None
text_grid, error = validate_grid(to2d(text))
if error:
return error
# Mapping for BERT models
bert_models = {
"bert_movie": "Xenova/distilbert-base-uncased-finetuned-sst-2-english",
"bert_product": "Xenova/bert-base-multilingual-uncased-sentiment",
"bert_finance": "Xenova/finbert",
"bert_twitter": "Xenova/twitter-roberta-base-sentiment-latest"
}
# Setup engines
engine = None
pipe = None
if sentiment_model == "vader":
engine = VaderSIA()
elif sentiment_model == "naive_bayes":
nltk.download("movie_reviews", quiet=True)
nltk.download("punkt", quiet=True)
nltk.download("punkt_tab", quiet=True)
engine = NaiveBayesAnalyzer()
elif sentiment_model == "pattern":
engine = PatternAnalyzer()
elif sentiment_model in bert_models:
transformers = await import_transformers_js()
pipeline = transformers.pipeline
pipe = await pipeline("sentiment-analysis", bert_models[sentiment_model])
result = []
for row in text_grid:
out_row = []
for cell in row:
if cell is None or (isinstance(cell, str) and not cell.strip()):
out_row.append("")
continue
if not isinstance(cell, str):
out_row.append("Error: text must contain only strings")
continue
data = {}
if sentiment_model == "vader":
scores = engine.polarity_scores(cell)
data = {
"type": "Double",
"basicValue": float(scores["compound"]),
"properties": {
"Compound": {"type": "Double", "basicValue": float(scores["compound"])},
"Positive": {"type": "Double", "basicValue": float(scores["pos"])},
"Neutral": {"type": "Double", "basicValue": float(scores["neu"])},
"Negative": {"type": "Double", "basicValue": float(scores["neg"])}
}
}
elif sentiment_model == "naive_bayes":
blob = TextBlob(cell, analyzer=engine)
sent = blob.sentiment
data = {
"type": "String",
"basicValue": str(sent.classification),
"properties": {
"Classification": {"type": "String", "basicValue": str(sent.classification)},
"P-Positive": {"type": "Double", "basicValue": float(sent.p_pos)},
"P-Negative": {"type": "Double", "basicValue": float(sent.p_neg)}
}
}
elif sentiment_model == "pattern":
p, s = engine.analyze(cell)
data = {
"type": "Double",
"basicValue": float(p),
"properties": {
"Polarity": {"type": "Double", "basicValue": float(p)},
"Subjectivity": {"type": "Double", "basicValue": float(s)}
}
}
elif sentiment_model in bert_models:
raw_result = await pipe(cell)
if hasattr(raw_result, "to_py"):
prediction = raw_result.to_py()[0]
else:
prediction = raw_result[0]
label = str(prediction["label"])
score = float(prediction["score"])
data = {
"type": "String",
"basicValue": label,
"properties": {
"Label": {"type": "String", "basicValue": label},
"Score": {"type": "Double", "basicValue": score}
}
}
out_row.append(data)
result.append(out_row)
return result
except Exception as e:
return f"Error: {str(e)}"