Integrating ChatGPT with Excel (Part 2)

Welcome to the “Illumined Insights” newsletter! Thank you so much for subscribing. This weekly newsletter touches on all things analytics and data science with a focus on areas such as data visualization, AI, and sports analytics.

This week we continue our series of posts exploring the integration of ChatGPT and Excel. This week we look at a specific application of ChatGPT in Excel: sentiment analysis of text data. For a detailed step-by-step outline describing how to build an interface between ChatGPT and Excel see Part 1 of this series.

Stephen Hill, Ph.D.

We don’t always think of Excel as a best-in-class tool for working with text based data. Numeric data? Sure. Excel excels (see what I did there) with many numeric data-related tasks. Despite this, Excel does include a variety of functions for working with text data. For example, we can clean text data with the CLEAN function or identify specific characters in a text string with functions such as LEFT, MID, and RIGHT. What if we want to conduct more sophisticated work with text data? One example of such analysis might be sentiment analysis.

Sentiment analysis is an area of natural language processing (NLP) that involves the extraction of information from text data. It is primarily used to gauge the attitudes, opinions, and emotions of a writer with respect to some topic or to the overall context. The analysis is executed by evaluating text data and identifying various emotions such as happiness, sadness, anger, etc., expressed in the words, sentences, or paragraphs. Sentiment analysis is widely used across various domains like marketing and customer service to analyze customer feedback, reviews, and comments, allowing organizations to gain insights into consumer opinions and adjust strategies or address concerns accordingly.

One common approach to sentiment analysis is to examine a short piece of text and assign an overall sentiment (e.g., positive, negative, neutral). For example, we might examine the text: “I love this place” and assign a sentiment of “Positive”. Here the word “love” has a positive connotation and drives the sentiment of the entire phrase. If we change the sentence to “I hate this place”, the sentiment of the text switches to “Negative”. Changing the text to “This place is OK” might give the text a “Neutral” sentiment.

There are a variety of software tools that can help us perform sentiment analysis. These range from Excel add-ins, to full-fledged, commercial tools, to open source options. What if we leverage AI (via ChatGPT) within Excel to analyze text sentiment?

We use the CHATX function that we developed in Part 1 of this series. Recall that this function allows us to pass the content of an Excel cell to ChatGPT as a prompt. For example:

CHATX(“What is the capital of Canada?”)

results in a response from ChatGPT of:

“The capital of Canada is Ottawa.”

We create a new function in VBA called CHATSENT that uses CHATX to pass our prompt to ChatGPT. The code for CHATSENT is available on GitHub here: Link.

The core of the CHATSENT function is the creation of a prompt that reads:

“Analyze the sentiment of the text provided. Sentiment should be stated as Positive, Negative, or Neutral. Provide a concise, one word answer. The text is:”

The prompt then incorporates content in an Excel cell. For example, we place the CHATSENT function in cells B2 and B3 referring to the text in cells A2 and A3. The result is shown below:

Let’s test this on a dataset of text samples. The text we use is from IMDB and contains short blurbs of text about various movies. The text has already been labelled as “Positive”, “Neutral”, or “Negative”. Let’s see how well ChatGPT does versus these labels.

For the 51 reviews that we provided to ChatGPT, it assigned “Negative” sentiment to 23, “Neutral” sentiment to 3, and “Positive” sentiment to 25. There is some degree of disagreement between ChatGPT and the pre-existing sentiment labels. ChatGPT and the labels agree in about 69% of the cases.

Significant disagreement only occurs for two of the reviews. For two reviews, ChatGPT assigned a “Negative” sentiment versus the label of “Positive”. Here is the text of the two reviews that ChatGPT assigned a sentiment of “Negative” versus a label of “Positive”:

REVIEW 1: Until it goes off the rails in its final 10 or 15 minutes, Wendigo, Larry Fessenden's spooky new thriller, is a refreshingly smart and newfangled variation on several themes derived from far less sophisticated and knowing horror films . 

REVIEW 2: This rush to profits has created a predictably efficient piece of business notable largely for its overwhelming creepiness , for an eagerness to create images you wish you hadn't seen, which, in this day and age, is of course the point .

I’ll leave it to you to decide on the interpretation of this disagreement.

In short, using ChatGPT within Excel to perform sentiment analysis on text shows promise and should be considered as a viable option for sentiment analysis.

Are you interested in learning more about data visualization using R? Click below to get notified about my upcoming book “Data Visualization in R”.

Feedback?

Did you enjoy this week’s newsletter? Do you have a topic, tool, or technique that you would like to see featured in a future edition? I’d love to hear from you!

Support the Newsletter?

Support this newsletter with a “coffee” (optional, but appreciated).

Start Your Own Newsletter?

This newsletter is created on and distributed via Beehiiv, the world’s best newsletter platform. Want to start your own newsletter? Click below to get started. Please note that this is an affiliate link. I may receive a small commission if you sign up for Beehiiv via this link.